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) {//获取新增sql
            StringBuilder 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) {//获取删除sql
            StringBuilder 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) {//获取修改sql
            StringBuilder 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) {//获取修改sql
            StringBuilder 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);//获取PreparedStatement
                resultSet = 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";
            }
        }
    }

    @Data
    static class ModelTable {//数据库中表与字段的集合
        private String tableName;//表名称
        private String tableComment;//表备注
        private Map<String, MapEntry> fields;//字段列表,MapEntry<字段类型,备注>
    }
}