SpringMVC实现动态加表及字段并显示数据

package com.rabbitsoft.controller; import java.io.PrintWriter; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import com.rabbitsoft.model.ColumnProperties; import com.rabbitsoft.model.Table; import com.rabbitsoft.model.TableColumn; /** * Author：Rabbit Email：rabbitsoft@vip.qq.com QQ: 406516761 CreateTime: Dec 24, * 2013 */ @Controller @RequestMapping("/DataBase.do") public class DataBaseController extends BaseController { @RequestMapping(params = "action=GetTables", method = RequestMethod.POST) public void GetTables(HttpServletRequest request, HttpServletResponse response) { try { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); List<Table> tables = Service.getDatabaseService().GetTables(); String result = ""; for (Table table : tables) { result += "{text:'" + table.getTABLES_IN_SOFTWARECONFIG() + "'},"; } if (result.equals("")) { out.write("[]"); } else { out.write("[" + result.substring(0, result.length() - 1) + "]"); } } catch (Exception e) { e.printStackTrace(); } } @RequestMapping(params = "action=DropTable", method = RequestMethod.POST) public void DropTable(HttpServletRequest request, HttpServletResponse response) { try { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); String tableName = request.getParameter("tableName"); if (Service.getDatabaseService().ExecuteSQL( "DROP TABLE `" + tableName + "`")) { out.write("{status:true}"); } else { out.write("{status:false}"); } } catch (Exception e) { e.printStackTrace(); } } @SuppressWarnings("unchecked") @RequestMapping(params = "action=CreateTable", method = RequestMethod.POST) public void CreateTable(HttpServletRequest request, HttpServletResponse response) { try { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); String tableName = request.getParameter("tableName"); String table = request.getParameter("table"); JSONArray jsonarray = JSONArray.fromObject(table); List<TableColumn> list = (List<TableColumn>) JSONArray .toCollection(jsonarray, TableColumn.class); if (Service.getDatabaseService() .ExecuteSQL(getSQL(list, tableName))) { out.write("{status:true}"); } else { out.write("{status:false}"); } } catch (Exception e) { e.printStackTrace(); } } public String getSQL(List<TableColumn> ltc, String tableName) { String sql = "DROP TABLE IF EXISTS `" + tableName + "`;CREATE TABLE `" + tableName + "` ("; boolean autoIncrement = false; for (TableColumn tc : ltc) { sql += "`" + tc.getColumnName() + "` " + this.getDataType(tc) + " " + this.getIsNull(tc) + ","; if (tc.getAutoIncrement() && !autoIncrement) { autoIncrement = tc.getAutoIncrement(); } } if (this.getPrimaryKey(ltc) == "") { sql = sql.substring(0, sql.length() - 1); } sql += this.getPrimaryKey(ltc) + ") ENGINE=MyISAM " + (autoIncrement ? "AUTO_INCREMENT=0" : "") + " DEFAULT CHARSET=utf8;"; return sql; } public String getDataType(TableColumn tc) { if (tc.getLength() != 0) { return tc.getDataType() + "(" + tc.getLength() + ")"; } else { return tc.getDataType(); } } public String getIsNull(TableColumn tc) { if (tc.getIsKey() && tc.getAutoIncrement()) { return "NOT NULL AUTO_INCREMENT"; } if (tc.getIsNull()) { return "DEFAULT NULL"; } else { return "NOT NULL"; } } public String getPrimaryKey(List<TableColumn> ltc) { String pk = "PRIMARY KEY ("; for (TableColumn tc : ltc) { if (tc.getIsKey()) { pk += "`" + tc.getColumnName() + "`,"; } } if ("PRIMARY KEY (".equals(pk)) { return ""; } else { return pk.substring(0, pk.length() - 1) + ")"; } } @RequestMapping(params = "action=GetAllColumn", method = RequestMethod.POST) public void GetAllColumn(HttpServletRequest request, HttpServletResponse response) { try { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); String tableName = request.getParameter("tName"); List<ColumnProperties> columnList = Service.getDatabaseService() .GetAllColumn(tableName); List<TableColumn> tableColumn = convertToTableColumn(columnList); JSONArray jsonArray = new JSONArray(); for (TableColumn tc : tableColumn) { jsonArray.add(JSONObject.fromObject(tc)); } out.write(jsonArray.toString()); } catch (Exception e) { e.printStackTrace(); } } public List<TableColumn> convertToTableColumn(List<ColumnProperties> res) { List<TableColumn> list = new ArrayList<TableColumn>(); for (ColumnProperties cp : res) { TableColumn tc = new TableColumn(); tc.setColumnName(cp.getField()); tc.setIsNull(cp.getNull().equals("NO") ? false : true); tc.setIsKey(cp.getKey().equals("PRI") ? true : false); tc.setAutoIncrement(cp.getExtra().equals("auto_increment") ? true : false); String[] tl = this.getTypeAndLength(cp.getType()); tc.setDataType(tl[0]); tc.setLength(Integer.parseInt(tl[1])); list.add(tc); } return list; } public String[] getTypeAndLength(String dataType) { if (dataType.equals("float")) { dataType = "float(0)"; } if (dataType.equals("text")) { dataType = "text(0)"; } if (dataType.equals("datetime")) { dataType = "datetime(0)"; } String[] result = dataType.split("\\("); result[1] = result[1].toString().substring(0, result[1].toString().length() - 1); return result; } @RequestMapping(params = "action=DelColumn", method = RequestMethod.POST) public void DelColumn(HttpServletRequest request, HttpServletResponse response) { try { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); String column = request.getParameter("column"); String tableName = request.getParameter("table"); if (Service.getDatabaseService().ExecuteSQL( "alter table `" + tableName + "` drop column `" + column + "`;")) { out.write("{status:true}"); } else { out.write("{status:false}"); } } catch (Exception e) { e.printStackTrace(); } } @SuppressWarnings("unchecked") @RequestMapping(params = "action=AlterTable", method = RequestMethod.POST) public void AlterTable(HttpServletRequest request, HttpServletResponse response) { try { request.setCharacterEncoding("utf-8"); response.setContentType("text/html;charset=utf-8"); PrintWriter out = response.getWriter(); String tableColumn=request.getParameter("table"); String tableName=request.getParameter("tableName"); String oldTableName=request.getParameter("oldTableName"); JSONArray jsonarray = JSONArray.fromObject(tableColumn); List<TableColumn> tableList = (List<TableColumn>) JSONArray.toCollection(jsonarray, TableColumn.class); String TableRenameSQL="alter table `" + oldTableName + "` rename `" + tableName + "`;"; if(Service.getDatabaseService().ExecuteSQL(TableRenameSQL)&&Service.getDatabaseService().ExecuteSQL(getAlertSQL(tableList,tableName))){ out.write("{status:true}"); }else{ out.write("{status:false}"); } }catch(Exception e){ e.printStackTrace(); } } public String getAlertSQL(List<TableColumn> res, String tableName) { List<TableColumn> updateRes = new ArrayList<TableColumn>();