MySQL实体类框架

实现mysql数据库的增删改查功能

import com.mchange.v2.collection.MapEntry;
import lombok.Data;
import org.junit.jupiter.api.Test;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.*;
import java.sql.*;
import java.util.*;/*** 自定义框架* 生成mysql表对应的实体类*/
public class MySqlMapper {/*** 配制参数*/static class Config {//mysql地址,数据库,用户名,密码final static private String address = "127.0.0.1:3306";final static private String dbName = "creative";final static private String username = "root";final static private String password = "123456";//模型保存的位置final static private String modelPath = "C:\\Users\\admin\\IdeaProjects\\mytest1\\src\\main\\java\\com\\libii\\model";//mysql数据类型与java数据类型转换final static MapEntry[] map = {new MapEntry("INT", "Integer"),new MapEntry("VARCHAR", "String"),new MapEntry("TIMESTAMP", "String"),new MapEntry("DOUBLE", "Double")};}/*** 内存参数*/static class Variable {final static private String packagePath = Config.modelPath.split("java\\\\")[1].replace("\\", ".");static private Map<String, String> mapper = new HashMap<>();static {for (MapEntry mapEntry : Config.map) {mapper.put(String.valueOf(mapEntry.getKey()), String.valueOf(mapEntry.getValue()));}}}/*** 获取连接** @return* @throws SQLException* @throws ClassNotFoundException*/public static Connection getConn() throws SQLException, ClassNotFoundException {Class.forName("com.mysql.jdbc.Driver");String url = "jdbc:mysql://" + Config.address + "/" + Config.dbName + "?useUnicode=true&amp;characterEncoding=UTF-8";Connection conn = DriverManager.getConnection(url, Config.username, Config.password);return conn;}public static void main(String[] args) throws Exception {Connection conn = getConn();List<ModelTable> modelTableList = Business.getModelTable(conn);//获取数据库下的表结构conn.close();Business.foreach(modelTableList);//遍历结构数据}static class Business {//业务类/*** 获取数据库中表与字段的集合** @param conn* @return*/public static List<ModelTable> getModelTable(Connection conn) throws Exception {List<ModelTable> modelTableList = new LinkedList<>();Map<String, String> tableMsg = Business.getTableNameList(conn);for (Map.Entry<String, String> line : tableMsg.entrySet()) {ModelTable tableFieldMap = getTableFieldMap(conn, line);modelTableList.add(tableFieldMap);}return modelTableList;}/*** 获取表名称** @param conn* @return* @throws Exception*/public static Map<String, String> getTableNameList(Connection conn) throws Exception {String sql = "select TABLE_NAME,TABLE_COMMENT from information_schema.tables where table_schema=\"" + Config.dbName + "\"";List<Map<String, Object>> tableMap = Util.select(sql, conn);Map<String, String> tableMsg = new HashMap<>();for (Map<String, Object> line : tableMap) {String tableName = String.valueOf(line.get("TABLE_NAME"));String tableComment = String.valueOf(line.get("TABLE_COMMENT"));tableMsg.put(tableName, tableComment);}return tableMsg;}/*** 获取表中的字段信息** @param conn* @return* @throws Exception*/public static ModelTable getTableFieldMap(Connection conn, Map.Entry<String, String> line) throws Exception {DatabaseMetaData meta = conn.getMetaData();String tableName = line.getKey();String tableComment = line.getValue();ResultSet rs = meta.getColumns(null, "%", tableName, "%");Map<String, MapEntry> tableFieldMap = new HashMap<>();ModelTable modelTable = new ModelTable();modelTable.setTableName(tableName);modelTable.setFields(tableFieldMap);modelTable.setTableComment(tableComment);while (rs.next()) {String columnName = rs.getString("COLUMN_NAME");String dataTypeName = rs.getString("TYPE_NAME");String remarks = rs.getString("REMARKS");tableFieldMap.put(columnName, new MapEntry(dataTypeName, remarks));//字段名称,字段类型}return modelTable;}/*** 遍历model信息** @param modelTableList*/public static void foreach(List<ModelTable> modelTableList) throws IOException {//写入接口String filePath = Config.modelPath + "/IModel.java";String msg = getIModelMsg();Util.writer(filePath, msg);for (ModelTable modelTable : modelTableList) {writerModelTable(modelTable);}}private static String getIModelMsg() {String packagePath = "package " + Variable.packagePath + ";\n";String inner = "import com.alibaba.fastjson.JSONObject;\n" +"import java.util.Map;\n" +"\n" +"public interface IModel {\n" +"    String toInsertSql();\n" +"\n" +"    String toDeleteSql();\n" +"\n" +"    String toUpdateSql();\n" +"\n" +"    String toSelectSql();\n" +"\n" +"    void mapToModel(Map<String, Object> model);\n" +"\n" +"    void jsonToModel(JSONObject model);\n" +"\n" +"    Map<String, Object> modelToMap();\n" +"\n" +"    JSONObject modelToJson();\n" +"}\n";return packagePath+inner;}/*** 写入model信息到文件** @param modelTable*/public static void writerModelTable(ModelTable modelTable) throws IOException {String msg = getMsg(modelTable);//计算需要写入的信息String filePath = Config.modelPath + "/" + modelTable.tableName + ".java";Util.writer(filePath, msg);}/*** 生成model的信息** @param modelTable* @return*/public static String getMsg(ModelTable modelTable) {String packagePath = "package " + Variable.packagePath + ";";String fastjson = "import com.alibaba.fastjson.JSONObject;";String javaUtil = "import java.util.*;";String lombok = "import lombok.Data;";String data = "@Data";String headClass = "public class " + modelTable.tableName + " implements IModel{";String tailClass = "}";StringBuilder sbr = new StringBuilder();sbr.append(packagePath).append("\n").append("\n");sbr.append(fastjson).append("\n");sbr.append(lombok).append("\n");sbr.append(javaUtil).append("\n").append("\n");sbr.append("/**").append("\n");//添加类备注sbr.append("/*").append(modelTable.tableComment).append("\n");sbr.append("*/").append("\n");sbr.append(data).append("\n");sbr.append(headClass).append("\n");//添加成员变量与备注for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {String fieldName = entry.getKey();MapEntry value = entry.getValue();String fieldType = String.valueOf(value.getKey());String remakes = String.valueOf(value.getValue());sbr.append("\tprivate ").append(Variable.mapper.get(fieldType)).append(" ").append(fieldName).append(";").append("//").append(remakes).append("\n");}sbr.append("\n");//自定义方法不需要的方式直接注释sbr.append(ModelFunc.modelToJson(modelTable)).append("\n");sbr.append(ModelFunc.modelToMap(modelTable)).append("\n");sbr.append(ModelFunc.jsonToModel(modelTable)).append("\n");sbr.append(ModelFunc.mapToModel(modelTable)).append("\n");sbr.append(ModelFunc.toInsertSql(modelTable)).append("\n");//增sbr.append(ModelFunc.toDeleteSql(modelTable)).append("\n");//删sbr.append(ModelFunc.toUpdateSql(modelTable)).append("\n");//改sbr.append(ModelFunc.toSelectSql(modelTable)).append("\n");//查//结束sbr.append(tailClass).append("\n");return sbr.toString();}}static class ModelFunc {//方法构造public static String modelToJson(ModelTable modelTable) {//将成员变量转化为json对象StringBuilder sbr = new StringBuilder();sbr.append("\t/**\n");sbr.append("\t *将当前Model对象转化为json对象\n");sbr.append("\t */\n");sbr.append("\t @Override\n");sbr.append("\t").append("public JSONObject modelToJson(){\n");Map<String, MapEntry> fields = modelTable.fields;sbr.append("\t\tJSONObject result = new JSONObject();").append("\n");for (Map.Entry<String, MapEntry> entry : fields.entrySet()) {String fieldName = entry.getKey();sbr.append("\t\t").append("result.put(\"").append(fieldName).append("\",").append(fieldName).append(");").append("\n");}sbr.append("\t\treturn result;\n");sbr.append("\t}\n");return sbr.toString();}public static String modelToMap(ModelTable modelTable) {//将成员变量转化为Map对象StringBuilder sbr = new StringBuilder();sbr.append("\t/**\n");sbr.append("\t *将当前Model对象转化为Map对象\n");sbr.append("\t */\n");sbr.append("\t @Override\n");sbr.append("\t").append("public Map<String,Object> modelToMap(){\n");sbr.append("\t\t").append("Map<String,Object> result = new HashMap<>();\n");for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {String fieldName = entry.getKey();sbr.append("\t\t").append("result.put(\"").append(fieldName).append("\",").append(fieldName).append(");").append("\n");}sbr.append("\t\treturn result;\n");sbr.append("\t}\n");return sbr.toString();}public static String jsonToModel(ModelTable modelTable) {//json赋值StringBuilder sbr = new StringBuilder();sbr.append("\t/**\n");sbr.append("\t *将json对象转化为Model对象\n");sbr.append("\t */\n");sbr.append("\t @Override\n");sbr.append("\t").append("public void jsonToModel(JSONObject model){\n");for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {String fieldName = entry.getKey();String fieldType = String.valueOf(entry.getValue().getKey());sbr.append("\t\t").append("this.").append(fieldName).append("=model.get").append(Variable.mapper.get(fieldType)).append("(\"").append(fieldName).append("\");").append("\n");}sbr.append("\t}\n");return sbr.toString();}public static String mapToModel(ModelTable modelTable) {//Map赋值StringBuilder sbr = new StringBuilder();sbr.append("\t/**\n");sbr.append("\t *将Map对象转化为Model对象\n");sbr.append("\t */\n");sbr.append("\t @Override\n");sbr.append("\t").append("public void mapToModel(Map<String,Object> model){\n");for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {String fieldName = entry.getKey();String fieldType = String.valueOf(entry.getValue().getKey());sbr.append("\t\t").append("this.").append(fieldName).append("=").append(Variable.mapper.get(fieldType)).append(".valueOf(model.get(\"").append(fieldName).append("\").toString());").append("\n");}sbr.append("\t}\n");return sbr.toString();}public static String toInsertSql(ModelTable modelTable) {//获取新增sqlStringBuilder sbr = new StringBuilder();sbr.append("\t/**\n");sbr.append("\t *根据成员变量获取插入sql语句\n");sbr.append("\t */\n");sbr.append("\t @Override\n");sbr.append("\t").append("public String toInsertSql(){\n");StringBuilder ifField = new StringBuilder();ifField.append("if(!(");int len = modelTable.fields.size();for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {len--;ifField.append(entry.getKey()).append("!=null");String type = len > 0 ? "||" : "";ifField.append(type);}ifField.append(")) return \"insert into ").append(modelTable.tableName).append(" () value ()\";");sbr.append("\t\t").append(ifField).append("\n");sbr.append("\t\tStringBuilder values = new StringBuilder();\n");sbr.append("\t\tvalues.append(\"insert into ").append(modelTable.tableName).append(" (\");\n");for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {sbr.append("\t\tif (").append(entry.getKey()).append(" != null) values.append(\"").append(entry.getKey()).append("\")");sbr.append(".append(\",\");\n");}sbr.append("\t\tvalues.setLength(values.length()-1);\n");sbr.append("\t\tvalues.append(\") value \").append(\"(\");\n");for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {sbr.append("\t\tif (").append(entry.getKey()).append(" != null) values.append(\"'\").append(").append(entry.getKey()).append(").append(\"'\")");sbr.append(".append(\",\");\n");}sbr.append("\t\tvalues.setLength(values.length()-1);\n");sbr.append("\t\tvalues.append(\")\");\n");sbr.append("\t\tString sql = values.toString();\n");sbr.append("\t\treturn sql;\n");sbr.append("\t}\n");return sbr.toString();}public static String toDeleteSql(ModelTable modelTable) {//获取删除sqlStringBuilder sbr = new StringBuilder();sbr.append("\t/**\n");sbr.append("\t *根据成员变量获取删除sql语句\n");sbr.append("\t */\n");sbr.append("\t @Override\n");sbr.append("\t").append("public String toDeleteSql(){\n");StringBuilder ifField = new StringBuilder();ifField.append("if(!(");int len = modelTable.fields.size();for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {len--;ifField.append(entry.getKey()).append("!=null");String type = len > 0 ? "||" : "";ifField.append(type);}ifField.append(")) return \"delete from ").append(modelTable.tableName).append("\";");sbr.append("\t\t").append(ifField).append("\n");sbr.append("\t\tStringBuilder values = new StringBuilder();\n");sbr.append("\t\tvalues.append(\"delete from ").append(modelTable.tableName).append(" where \");\n");for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {sbr.append("\t\tif (").append(entry.getKey()).append(" != null) values.append(\"").append(entry.getKey()).append("\")").append(".append(\" = \").append(\"'\").append(").append(entry.getKey()).append(")").append(".append(\"'\")");sbr.append(".append(\" and \");\n");}sbr.append("\t\tString sql = values.substring(0,values.length()-5);\n");sbr.append("\t\treturn sql;\n");sbr.append("\t}\n");return sbr.toString();}public static String toUpdateSql(ModelTable modelTable) {//获取修改sqlStringBuilder sbr = new StringBuilder();sbr.append("\t/**\n");sbr.append("\t *根据成员变量获取修改sql语句\n");sbr.append("\t */\n");sbr.append("\t @Override\n");sbr.append("\t").append("public String toUpdateSql(){\n");StringBuilder ifField = new StringBuilder();ifField.append("if(!(");int len = modelTable.fields.size();for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {len--;ifField.append(entry.getKey()).append("!=null");String type = len > 0 ? "||" : "";ifField.append(type);}ifField.append(")) return \"update ").append(modelTable.tableName).append(" set id = id\";");sbr.append("\t\t").append(ifField).append("\n");sbr.append("\t\tStringBuilder values = new StringBuilder();\n");sbr.append("\t\tvalues.append(\"update ").append(modelTable.tableName).append(" set \");\n");for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {sbr.append("\t\tif (").append(entry.getKey()).append(" != null) values.append(\"").append(entry.getKey()).append("\")").append(".append(\" = \").append(\"'\").append(").append(entry.getKey()).append(")").append(".append(\"'\")");sbr.append(".append(\",\");\n");}sbr.append("\t\tvalues.setLength(values.length()-1);\n");sbr.append("\t\tString sql = values.toString();\n");sbr.append("\t\treturn sql;\n");sbr.append("\t}\n");return sbr.toString();}public static String toSelectSql(ModelTable modelTable) {//获取修改sqlStringBuilder sbr = new StringBuilder();sbr.append("\t/**\n");sbr.append("\t *根据成员变量获取查询sql语句\n");sbr.append("\t */\n");sbr.append("\t @Override\n");sbr.append("\t").append("public String toSelectSql(){\n");StringBuilder ifField = new StringBuilder();ifField.append("if(!(");int len = modelTable.fields.size();for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {len--;ifField.append(entry.getKey()).append("!=null");String type = len > 0 ? "||" : "";ifField.append(type);}ifField.append(")) return \"select * from ").append(modelTable.tableName).append("\";");sbr.append("\t\t").append(ifField).append("\n");sbr.append("\t\tStringBuilder values = new StringBuilder();\n");sbr.append("\t\tvalues.append(\"select * from ").append(modelTable.tableName).append(" where \");\n");for (Map.Entry<String, MapEntry> entry : modelTable.fields.entrySet()) {sbr.append("\t\tif (").append(entry.getKey()).append(" != null) values.append(\"").append(entry.getKey()).append("\")").append(".append(\" = \").append(\"'\").append(").append(entry.getKey()).append(")").append(".append(\"'\")");sbr.append(".append(\" and \");\n");}sbr.append("\t\tvalues.setLength(values.length()-5);\n");sbr.append("\t\tString sql = values.toString();\n");sbr.append("\t\treturn sql;\n");sbr.append("\t}\n");return sbr.toString();}}static class Util {//工具类/*** 查询数据** @param sql  请求sql* @param conn 数据库连接* @return 查询的响应数据* @throws Exception*/public static List<Map<String, Object>> select(String sql, Connection conn) throws Exception {List<Map<String, Object>> result = new LinkedList<>();PreparedStatement preparedStatement = null;//支出传递问号,防止sql注入ResultSet resultSet = null;try {preparedStatement = conn.prepareStatement(sql);//获取PreparedStatementresultSet = preparedStatement.executeQuery();//获取查询结果的字段信息ResultSetMetaData metaData = resultSet.getMetaData();int fieldCount = metaData.getColumnCount();//字段数量List<String> fields = new ArrayList<>(fieldCount);for (int i = 1; i <= fieldCount; i++) {fields.add(metaData.getColumnName(i));}//5、从结果集获取结果数据while (resultSet.next()) {Map<String, Object> line = new HashMap();for (String field : fields) {line.put(field, resultSet.getObject(field));}result.add(line);}} catch (SQLException e) {e.printStackTrace();} finally {resultSet.close();preparedStatement.close();}return result;}/*** 写入内容到文件** @param filePath 文件路径* @param msg      写入信息* @throws IOException*/public static void writer(String filePath, String msg) throws IOException {if (msg == null || "".equals(msg)) return;File file = new File(filePath);if (!file.exists()) file.createNewFile();FileOutputStream fileOutputStream = null;try {fileOutputStream = new FileOutputStream(file);fileOutputStream.write(msg.getBytes("UTF-8"));} catch (IOException e) {e.printStackTrace();} finally {fileOutputStream.close();//先打开的后关闭}}/*** 获取json内容的类型转换** @param fieldType* @return*/public static String getType(String fieldType) {String javaFieldType = Variable.mapper.get(fieldType);switch (javaFieldType) {case "Integer":return "getInteger";case "String":case "TIMESTAMP":return "getString";case "DOUBLE":return "getDouble";default:return "getObject";}}}@Datastatic class ModelTable {//数据库中表与字段的集合private String tableName;//表名称private String tableComment;//表备注private Map<String, MapEntry> fields;//字段列表,MapEntry<字段类型,备注>}
}