接收前端数据,进行数据库的更新或创建
@Controller
@RequestMapping("/database")
public class DataBase {
//获取配置文件中数据库配置信息
public static PropertyResourceBundle res = (PropertyResourceBundle) PropertyResourceBundle.getBundle("jdbc");
public static String driver = res.getString("jdbc.driverClassName");
public static String url = res.getString("jdbc.url");
public static String username = res.getString("jdbc.username");
public static String password = res.getString("jdbc.password");
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
logger.error("can not load jdbc driver", e);
}
}
//获取数据库连接
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
logger.error("get connection failure", e);
}
return conn;
}
// 关闭数据库连接
public static void closeConnection(Connection conn) {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.error("close connection failure", e);
}
}
}
@RequestMapping(value = "/savetable.jhtml", produces = "application/json; charset=utf-8", method = RequestMethod.POST)
@ResponseBody
public String datamodelsave(String tablename, String oldtablename, String tableinfostr, String datamodelinfo, ModelMap model) {
//存储主键的集合
List<String> primarykeylist = new ArrayList<>();
//获取前端传递的json数据
JSONObject jsonObj = JSON.parseObject(datamodelinfo);
JSONArray payList=jsonObj.getJSONArray("data");
//存储返回给前端数据的集合
Map<String, String> resultMap = new HashMap<>();
//获取数据库连接
Connection conn = getConnection();
Statement stmt = null;
//判断oldtablename是否为空,不为空则代表为更新已经存在的数据表
if(oldtablename!=null&&!"".equals(oldtablename)) {
try {
stmt = conn.createStatement();
String sql = "";
String[] info_ = tableinfostr.split(",");
//更新字段的语句
String changesql = "";
//删除字段的语句
String dropsql = "";
//添加字段的语句
String addsql = "";
//原数据表为主键的字段
List<String> oldkey = new ArrayList<>();
//新创建为主键的字段
List<String> newkey = new ArrayList<>();
//原数据表是否包含主键标志位
boolean hadkey = false;
String[] info = null;
//判断某个字段在数据表更新后是否还存在
boolean columnexits = false;
//数据表更新前包含的字段
Map<String,String> names = new HashMap<>();
for(int i=0;i<info_.length;i++) {
names.put(info_[i].split("__")[4],info_[i].split("__")[4]);
}
//循环解析字段的信息,并组合为sql语句
for(int i=0;i<payList.size();i++) {
String mark = payList.getJSONObject(i).getString("Mark");
//循环判断之前存在的字段中是否包含主键
for(int j=0;j<info_.length;j++) {
//根据mark标记判断该条字段信息是否为新增字段
if(mark.startsWith("old")) {
if(mark.substring(3, mark.length()).equals(info_[j].split("__")[4])) {
columnexits=true;
info = info_[j].split("__");
names.remove(info_[j].split("__")[4]);
}
}
//获取旧字段信息的主键信息,为ture则改变标志位
if(info_[j].split("__")[5].equals("true"))
hadkey = true;
}
if(mark.startsWith("old")) {
if(columnexits) {
if(!info[4].equals(payList.getJSONObject(i).getString("Columnname"))||
!info[0].equals(payList.getJSONObject(i).getString("Comments"))||
!info[1].equals(payList.getJSONObject(i).getString("Isnull"))||
!info[2].equals(payList.getJSONObject(i).getString("Datatype"))||
!info[3].equals(payList.getJSONObject(i).getString("Length"))) {
changesql += " CHANGE `" + info[4] + "` `"+ payList.getJSONObject(i).getString("Columnname") + "` " + payList.getJSONObject(i).getString("Datatype");
if(!"longblob".equals(payList.getJSONObject(i).getString("Datatype"))
&&!"datetime".equals(payList.getJSONObject(i).getString("Datatype"))) {
if(!payList.getJSONObject(i).getString("Length").equals("")
&&payList.getJSONObject(i).getString("Length")!=null
&&!"-1".equals(payList.getJSONObject(i).getString("Length"))) {
changesql += "(" + payList.getJSONObject(i).getString("Length") + ")";
}else {
changesql += "(50)";
}
}
if(payList.getJSONObject(i).getString("Primarykey").equals("true")) {
changesql += " NOT NULL";
}else {
if(payList.getJSONObject(i).getString("Isnull").equals("false"))
changesql += " DEFAULT NULL";
else
changesql += " NOT NULL";
}
if(payList.getJSONObject(i).getString("Comments")!=null&&!"".equals(payList.getJSONObject(i).getString("Comments")))
changesql += " COMMENT '" + payList.getJSONObject(i).getString("Comments") + "'";
changesql += ",";
}
if(payList.getJSONObject(i).getString("Primarykey").equals("true"))
oldkey.add(payList.getJSONObject(i).getString("Columnname"));
}
}else {
addsql += " ADD COLUMN `"+ payList.getJSONObject(i).getString("Columnname") + "` " + payList.getJSONObject(i).getString("Datatype");
if(!"longblob".equals(payList.getJSONObject(i).getString("Datatype"))
&&!"datetime".equals(payList.getJSONObject(i).getString("Datatype"))) {
if(!payList.getJSONObject(i).getString("Length").equals("")
&&payList.getJSONObject(i).getString("Length")!=null
&&!"-1".equals(payList.getJSONObject(i).getString("Length"))) {
addsql += "(" + payList.getJSONObject(i).getString("Length") + ")";
}else {
addsql += "(50)";
}
}
if(payList.getJSONObject(i).getString("Primarykey").equals("true")) {
newkey.add(payList.getJSONObject(i).getString("Columnname"));
addsql += " NOT NULL";
}else {
if(payList.getJSONObject(i).getString("Isnull").equals("false"))
addsql += " DEFAULT NULL";
else
addsql += " NOT NULL";
}
if(payList.getJSONObject(i).getString("Comments")!=null&&!"".equals(payList.getJSONObject(i).getString("Comments")))
addsql += " COMMENT '" + payList.getJSONObject(i).getString("Comments") + "'";
addsql += ",";
}
}
for(String val : names.values()) {
dropsql += " DROP COLUMN `" + val + "`,";
}
if(!"".equals(changesql)||!"".equals(dropsql)||!"".equals(addsql)||oldkey.size()>0||newkey.size()>0) {
String altersql = "ALTER TABLE `" + oldtablename + "`";
altersql += dropsql;
altersql += changesql;
altersql += addsql;
if(hadkey) {
altersql += " DROP PRIMARY KEY,";
}
if(oldkey.size()>0||newkey.size()>0) {
newkey.addAll(oldkey);
altersql += " ADD PRIMARY KEY (";
for(String val : newkey) {
altersql += "`" + val + "`,";
}
altersql = altersql.substring(0, altersql.lastIndexOf(","));
altersql += ");";
}else {
altersql = altersql.substring(0, altersql.lastIndexOf(","));
altersql += ";";
}
stmt.execute(altersql);
logger.info("执行sql: "+altersql);
}
//数据表名称有变化则更新数据表名称
if(!tablename.equals(oldtablename)) {
if(tablename.startsWith("ax_"))
sql = "RENAME TABLE `" + oldtablename + "` TO `" + tablename + "`;";
else
sql = "RENAME TABLE `" + oldtablename + "` TO `ax_" + tablename + "`;";
stmt.execute(sql);
logger.info("更改数据表名称: "+sql);
}
} catch (SQLException e) {
logger.info("SQLException is: "+e.getMessage());
resultMap.put("success", "false");
resultMap.put("msg",e.getMessage());
return JSONUtils.toJSONString(resultMap);
} finally {
try {
stmt.close();
closeConnection(conn);
} catch (SQLException e) {
logger.error("close Statement failure", e);
}
}
}else {
String sql = "CREATE TABLE `ax_" + tablename + "` ( ";
if(payList.size()>0){
for(int i=0;i<payList.size();i++){
sql += "`" + payList.getJSONObject(i).getString("Columnname") + "` " + payList.getJSONObject(i).getString("Datatype");
if(!"longblob".equals(payList.getJSONObject(i).getString("Datatype"))
&&!"datetime".equals(payList.getJSONObject(i).getString("Datatype"))) {
if(!payList.getJSONObject(i).getString("Length").equals("")&&
payList.getJSONObject(i).getString("Length")!=null&&
!"-1".equals(payList.getJSONObject(i).getString("Length"))) {
sql += "(" + payList.getJSONObject(i).getString("Length") + ")";
}else {
sql += "(50)";
}
}
if(payList.getJSONObject(i).getString("Primarykey").equals("true")) {
primarykeylist.add(payList.getJSONObject(i).getString("Columnname"));
sql += " NOT NULL";
}else {
if(payList.getJSONObject(i).getString("Isnull").equals("false"))
sql += " DEFAULT NULL";
else
sql += " NOT NULL";
}
if(payList.getJSONObject(i).getString("Comments")!=null&&!"".equals(payList.getJSONObject(i).getString("Comments")))
sql += " COMMENT '" + payList.getJSONObject(i).getString("Comments") + "'";
sql += ",";
if(i==payList.size()-1&&primarykeylist.size()<=0)
sql = sql.substring(0, sql.length()-1);
}
if(primarykeylist.size()>0) {
sql += "PRIMARY KEY (";
for(int i=0;i<primarykeylist.size();i++) {
sql += "`" + primarykeylist.get(i) + "`";
if(primarykeylist.size()==1) {
sql += ")";
}else {
if(i==primarykeylist.size()-1)
sql += ")";
else
sql +=",";
}
}
}
sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8";
}
logger.info("SQL="+sql);
try {
stmt = getConnection().createStatement();
stmt.execute(sql);
} catch (SQLException e) {
logger.info("SQLException is: "+e.getMessage());
resultMap.put("success", "false");
resultMap.put("msg",e.getMessage());
return JSONUtils.toJSONString(resultMap);
} finally {
try {
stmt.close();
closeConnection(conn);
} catch (SQLException e) {
logger.error("close Statement failure", e);
}
}
}
resultMap.put("success", "true");
resultMap.put("msg","true");
return JSONUtils.toJSONString(resultMap);
}
//根据数据表名称,获取数据表列表基本信息
public static List<Map<String,String>> getTableinfo(String tablename) {
List<Map<String,String>> list = new ArrayList<Map<String,String>>();
List<String> primarykeylist = new ArrayList<String>();
Map<String,String> map;
List<String> commentlist = new ArrayList<String>();
String sql = "select * from " + tablename;
Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet rs = null;
ResultSet rscomment = null;
try {
stmt = conn.prepareStatement(sql);
rs = conn.getMetaData().getPrimaryKeys(null, null, tablename);
while(rs.next()) {
primarykeylist.add(rs.getString(4));
}
ResultSetMetaData data = stmt.getMetaData();
rscomment = stmt.executeQuery("show full columns from "+tablename);
while(rscomment.next()) {
commentlist.add(rscomment.getString("Comment"));
}
for (int i = 1; i <= data.getColumnCount(); i++) {
map = new HashMap<String,String>();
// 获得指定列的列名
String columnName = data.getColumnName(i);
// 获得指定列的数据类型名
String columnTypeName = data.getColumnTypeName(i);
// 在数据库中类型的最大字符个数
int columnDisplaySize = data.getColumnDisplaySize(i);
// 是否为空
int isNullable = data.isNullable(i);
if(primarykeylist.size()>0) {
boolean primary = false;
for(int j=0;j<primarykeylist.size();j++) {
if(columnName.equals(primarykeylist.get(j))) {
primary = true;
}
}
if(primary)
map.put("primarykey", "true");
else
map.put("primarykey", "false");
}else {
map.put("primarykey", "false");
}
map.put("columnname", columnName);
if(columnTypeName.equals("INTEGER")) {
columnTypeName = columnTypeName.substring(0, 3);
}
map.put("datatype", columnTypeName.toLowerCase());
if(columnTypeName.toLowerCase().equals("longblob")||
columnTypeName.toLowerCase().equals("datetime"))
map.put("length", "");
else
map.put("length", String.valueOf(columnDisplaySize));
if(isNullable==0)
map.put("isnull", "true");
else
map.put("isnull", "false");
map.put("comments", commentlist.get(i-1));
list.add(map);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
stmt.close();
rscomment.close();
closeConnection(conn);
} catch (SQLException e) {
logger.error("close Statement failure", e);
}
}
return list;
}
}