后台接收前端数据表基本数据，创建数据表

接收前端数据，进行数据库的更新或创建

@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;
    }
}