package com.yirui.supervisor.service.sys.impl;
import com.yirui.supervisor.common.Column;
import com.yirui.supervisor.common.Table;
import com.yirui.supervisor.service.sys.SysService;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Service
public class SysServiceImpl implements SysService {
@Value("${spring.datasource.driver-class-name}")
private String driverName;
@Value("${spring.datasource.username}")
private String userName;
@Value("${spring.datasource.password}")
private String userPwd;
@Value("${spring.datasource.url}")
private String dbURL;
@Override
public void createSqlFile(HttpServletResponse response, String tbName) throws Exception{
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename="+new String((System.currentTimeMillis()+".sql").getBytes("utf-8"),"ISO-8859-1"));
OutputStream out = response.getOutputStream();
Map<String, Object> map = parseTable(tbName);
Table tb = (Table) map.get("tb");
ResultSet re = (ResultSet) map.get("re");
Connection co = (Connection) map.get("co");
List<Column> columns = tb.getColumns();
while (re.next()){
//删除语句生成
String delSql="DELETE FROM "+tbName+" where id= ";
//新增语法生成
String insetSql="insert into "+tbName+" (";
for(Column col :columns){
insetSql+="`"+col.getDbName()+"`,";
}
insetSql=insetSql.substring(0,insetSql.length()-1);
insetSql+=" )values ( ";
for(Column col :columns){
//删除语句生成
if(col.getDbName().equals("id")){
if(col.getDbType().equalsIgnoreCase("int")){
delSql+=re.getInt("id");
}else{
delSql+="'"+re.getString("id")+"'";
}
}
if(col.getDbType().equalsIgnoreCase("Integer")||col.getDbType().equalsIgnoreCase("int")){
insetSql+=re.getObject(col.getDbName())+",";
}else{
if(re.getString(col.getDbName())==null){
insetSql+=""+re.getString(col.getDbName())+",";
}else{
insetSql+="'"+re.getString(col.getDbName())+"',";
}
}
}
insetSql=insetSql.substring(0,insetSql.length()-1)+")";
out.write((delSql+";"+System.getProperty("line.separator")).getBytes());
out.write((insetSql+";"+System.getProperty("line.separator")).getBytes());
}
co.close();
out.flush();
out.close();
}
private Map<String ,Object> parseTable(String tableName) throws Exception {
String column = "%";
Class.forName(driverName);
Connection conn = java.sql.DriverManager.getConnection(dbURL, userName, userPwd);
DatabaseMetaData dmd = conn.getMetaData();
ResultSet rs = dmd.getColumns("", "", tableName, column);
List<Column> columns = new ArrayList<Column>();
while (rs.next()) {
Column c = new Column();
c.setLabel(rs.getString("REMARKS"));
String name = rs.getString("COLUMN_NAME");
c.setDbName(name);
String dbType = rs.getString("TYPE_NAME");
int columnSize = rs.getInt("COLUMN_SIZE");
c.setDbType(dbType);
c.setLength(rs.getInt("COLUMN_SIZE"));
c.setDecimalDigits(rs.getInt("DECIMAL_DIGITS"));
c.setNullable(rs.getBoolean("NULLABLE"));
columns.add(c);
}
List<Column> pkColumns = new ArrayList<Column>();
ResultSet pkrs = dmd.getPrimaryKeys("", "", tableName);
while (pkrs.next()) {
Column c = new Column();
String name = pkrs.getString("COLUMN_NAME");
c.setDbName(name);
pkColumns.add(c);
}
Table t = new Table();
t.setDbName(tableName);
t.setColumns(columns);
t.setPkColumns(pkColumns);
ResultSet resultSet = conn.createStatement().executeQuery("select * from " + tableName);
System.out.println(resultSet);
Map<String, Object> map = new HashMap<>();
map.put("tb",t);
map.put("re",resultSet);
map.put("co",conn);
return map;
}
}
package com.yirui.supervisor.common;
public class Column {
private String name;
private String dbName;
private String label;
private String type;
private String dbType;
private Integer length;
private Boolean nullable;
private Integer decimalDigits;
public String getName() {
return name;
}
public String getNameUpper() {
return name.replaceFirst(name.substring(0, 1), name.substring(0, 1).toUpperCase());
}
public void setName(String name) {
this.name = name;
}
public String getLabel() {
return label;
}
public void setLabel(String label) {
this.label = label;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public Integer getLength() {
return length;
}
public void setLength(Integer length) {
this.length = length;
}
public Boolean getNullable() {
return nullable;
}
public void setNullable(Boolean nullable) {
this.nullable = nullable;
}
public Integer getDecimalDigits() {
return decimalDigits;
}
public void setDecimalDigits(Integer decimalDigits) {
this.decimalDigits = decimalDigits;
}
public String getDbName() {
return dbName;
}
public void setDbName(String dbName) {
this.dbName = dbName;
}
public String getDbType() {
return dbType;
}
public void setDbType(String dbType) {
this.dbType = dbType;
}
}
package com.yirui.supervisor.common;
import java.util.List;
public class Table {
private String name;
private String dbName;
private String tableDesc;
private List<Column> columns;
private List<Column> pkColumns;
public String getName() {
return name;
}
public String getNameUpper() {
return name.replaceFirst(name.substring(0, 1), name.substring(0, 1).toUpperCase());
}
public void setName(String name) {
this.name = name;
}
public String getDbName() {
return dbName;
}
public void setDbName(String dbName) {
this.dbName = dbName;
}
public String getTableDesc() {
return tableDesc;
}
public void setTableDesc(String tableDesc) {
this.tableDesc = tableDesc;
}
public List<Column> getColumns() {
return columns;
}
public void setColumns(List<Column> columns) {
this.columns = columns;
}
public List<Column> getPkColumns() {
return pkColumns;
}
public void setPkColumns(List<Column> pkColumns) {
this.pkColumns = pkColumns;
}
}
