需求

  • 有了配置后，接下来我们需要把数据库中的表信息和表的字段信息取出来，然后进行循环生成每个表相关代码

实现

  • 先来定义一下用到的实体，表 Table 字段Field

  • Table

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    package com.hantianwei.generator.model;


    /**
     * Created by tianwei on 2017/6/26.
     */
    public class Table {
        private String name;
        private String proName;
        private String description;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getDescription() {
            return description;
        }

        public void setDescription(String description) {
            this.description = description;
        }

        public String getProName() {
            return proName;
        }

        public void setProName(String proName) {
            this.proName = proName;
        }
    }

  • Field

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
package com.hantianwei.generator.model;

/**
 * Created by tianwei on 2017/6/28.
 */
public class Field {
    private String name;
    private String proName;
    private String type;
    private int maxLength;
    private int isNullable;
    private int isIdentity;
    private String description;
    private int isPk;
    private String jdbcType;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public int getMaxLength() {
        return maxLength;
    }

    public void setMaxLength(int maxLength) {
        this.maxLength = maxLength;
    }

    public int getIsNullable() {
        return isNullable;
    }

    public void setIsNullable(int isNullable) {
        this.isNullable = isNullable;
    }

    public int getIsIdentity() {
        return isIdentity;
    }

    public void setIsIdentity(int isIdentity) {
        this.isIdentity = isIdentity;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getProName() {
        return proName;
    }

    public void setProName(String proName) {
        this.proName = proName;
    }

    public String getJdbcType() {
        return jdbcType;
    }

    public void setJdbcType(String jdbcType) {
        this.jdbcType = jdbcType;
    }

    public int getIsPk() {
        return isPk;
    }

    public void setIsPk(int isPk) {
        this.isPk = isPk;
    }
}
  • 需要封装对数据操作类 JdbcUtil
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
package com.hantianwei.generator.util;

import com.hantianwei.generator.model.Config;

import java.sql.*;

/**
 * Created by tianwei on 2017/6/28.
 */
public class JdbcUtil {
    //定义一个连接对象
    private Connection conn = null;
    //定义连接数据库的url资源
    private String url = String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useUnicode=true&&autoReconnect=true&useSSL=false", Config.DB_SERVER, Config.DB_PORT, Config.DB_NAME);

    //定义连接数据库的用户名和密码
    private String userName = Config.DB_USER;
    private String passWord = Config.DB_PASSWORD;

    //加载数据库连接驱动
    public Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, userName, passWord);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭数据库
    public void closeALL(Connection conn, Statement st, ResultSet rs, PreparedStatement pst) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (st != null) {
                st.close();
            }
            if (conn != null) {
                conn.close();
            }
            if (pst != null)
                pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
  • 我们需要对查询的字段或表名进行驼峰式转换
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
package com.hantianwei.generator.util;

import com.hantianwei.generator.model.Field;

import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * Created by tianwei on 2017/6/26.
 */
public class StringUtil {
    /**
     * 把数据库中的字段转换为变量类型
     * 如（user_id ----> userId）
     *
     * @param field
     * @return
     */
    public static String convertField(String field) {
        //分隔符
        char separator = '_';
        //转化为小写
        String variable = field.toLowerCase();

        if (variable.indexOf(separator) > -1) {
            char[] varArray = variable.toCharArray();
            for (int i = 0; i < varArray.length; i++) {
                if (varArray[i] == separator && i < varArray.length - 1) {
                    varArray[i + 1] = Character.toUpperCase(varArray[i + 1]);
                }
            }
            variable = new String(varArray).replaceAll("_", "");
        }

        return variable;

    }

    /**
     * 获取字符串型的类型名
     *
     * @param type
     * @return
     */
    public static String convertToJavaType(String type) {
        Map<String,String> map = getTypesJava();
        if(map.containsKey(type)){
            return map.get(type);
        }else {
            return "String";
        }
    }

    private static Map<String, String> getTypesJava() {
        Map<String, String> map = new HashMap<String, String>();
        map.put("VARCHAR", "String");
        map.put("CHAR", "String");
        map.put("BLOB", "byte[]");
        map.put("TEXT", "String");
        map.put("INTEGER", "Integer");
        map.put("INT", "Integer");
        map.put("SMALLINT", "Short");
        map.put("MEDIUMINT", "Integer");
        map.put("BIT", "Boolean");
        map.put("BIGINT", "Long");
        map.put("FLOAT", "Float");
        map.put("DOUBLE", "Double");
        map.put("DECIMAL", "BigDecimal");
        map.put("BOOLEAN", "Integer");
        map.put("TINYINT", "Byte");
        map.put("DATE", "Date");
        map.put("TIME", "Time");
        map.put("DATETIME", "Date");
        map.put("TIMESTAMP", "Timestamp");
        map.put("YEAR", "Date");
        return map;
    }

    public static String convertToJdbcType(String type) {
        Map<String,String> map = getTypesJdbc();
        if(map.containsKey(type)){
            return map.get(type);
        }else {
            return "String";
        }
    }

    private static Map<String, String> getTypesJdbc() {
        Map<String, String> map = new HashMap<String, String>();
        map.put("VARCHAR", "VARCHAR");
        map.put("CHAR", "CHAR");
        map.put("BLOB", "BINARY");
        map.put("TEXT", "LONGVARCHAR");
        map.put("INTEGER", "INTEGER");
        map.put("INT", "INTEGER");
        map.put("SMALLINT", "SMALLINT");
        map.put("MEDIUMINT", "INTEGER");
        map.put("BIT", "BIT");
        map.put("BIGINT", "BIGINT");
        map.put("FLOAT", "REAL");
        map.put("DOUBLE", "DOUBLE");
        map.put("DECIMAL", "DECIMAL");
        map.put("TINYINT", "TINYINT");
        map.put("DATE", "DATE");
        map.put("TIME", "TIME");
        map.put("DATETIME", "TIMESTAMP");
        map.put("TIMESTAMP", "TIMESTAMP");
        map.put("YEAR", "DATE");
        return map;
    }

    public static Field getFieldPk(List<Field> fieldList){
        Field field = new Field();
        for (Field item:fieldList) {
            if(item.getIsPk()==1){
                field=item;
                break;
            }
        }
        return field;
    }

    //首字母转小写
    public static String toLowerCaseFirstOne(String s)
    {
        if(Character.isLowerCase(s.charAt(0)))
            return s;
        else
            return (new StringBuilder()).append(Character.toLowerCase(s.charAt(0))).append(s.substring(1)).toString();
    }

    //首字母转大写
    public static String toUpperCaseFirstOne(String s)
    {
        if(Character.isUpperCase(s.charAt(0)))
            return s;
        else
            return (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
    }

}
  • 最后是对数据库的查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
package com.hantianwei.generator.dao;

import com.hantianwei.generator.model.Field;
import com.hantianwei.generator.model.Table;
import com.hantianwei.generator.util.JdbcUtil;
import com.hantianwei.generator.util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by tianwei on 2017/6/26.
 */
public class DataDao {
    Connection conn = null;
    Statement st = null;
    ResultSet rs = null;
    PreparedStatement pst = null;
    String sqlstr = null;

    String tableSql = "SELECT \n" +
            "\t              TABLE_NAME as name,\n" +
            "\t              TABLE_COMMENT as description \n" +
            "          FROM \n" +
            "\t              information_schema.TABLES \n" +
            "          WHERE \n" +
            "\t              TABLE_SCHEMA='%s'";

    String fildSql = "SELECT \n" +
            "\t             c.COLUMN_NAME as name,\n" +
            "\t             c.DATA_TYPE as type,\n" +
            "\t             IFNULL(c.CHARACTER_MAXIMUM_LENGTH,0) as maxLength,\n" +
            "\t             (CASE c.IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END) as isNullable,\n" +
            "\t             (CASE WHEN t.AUTO_INCREMENT>0 AND k.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END) as isIdentity,\n" +
            "\t             c.COLUMN_COMMENT as description,\n" +
            "\t             (CASE c.COLUMN_KEY WHEN 'PRI' THEN 1 ELSE 0 END) as isPk\n" +
            "          FROM \n" +
            "\t             information_schema.COLUMNS AS c\n" +
            "\t             INNER JOIN information_schema.`TABLES` AS t ON t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME\n" +
            "\t             LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON k.COLUMN_NAME=c.COLUMN_NAME AND c.TABLE_NAME=k.TABLE_NAME AND c.TABLE_SCHEMA=k.TABLE_SCHEMA\n" +
            "          WHERE \n" +
            "\t             c.TABLE_SCHEMA='%s'" +
            "\t             AND c.TABLE_NAME='%s'" +
            "          ORDER BY\n" +
            "\t             c.ORDINAL_POSITION ASC";

    /**
     * 根据表名获取所有的列信息
     *
     * @param tableSchema
     * @param tableName
     * @return
     */
    public List<Field> getAllColums(String tableSchema, String tableName) {

        List<Field> fieldList = new ArrayList<Field>();
        JdbcUtil dbConn = new JdbcUtil();

        try {
            this.conn = dbConn.getConnection();
            this.sqlstr = String.format(fildSql, tableSchema, tableName);
            this.st = conn.createStatement();
            this.rs = this.st.executeQuery(sqlstr);
            while (rs.next()) {
                Field field = new Field();
                String fieldName = rs.getString("name");
                field.setName(fieldName);
                field.setProName(StringUtil.convertField(fieldName));
                String fieldType = rs.getString("type").toUpperCase();
                String javaType = StringUtil.convertToJavaType(fieldType);
                field.setType(javaType);
                field.setJdbcType(StringUtil.convertToJdbcType(fieldType));
                field.setMaxLength(rs.getInt("maxLength"));
                field.setIsNullable(rs.getInt("isNullable"));
                field.setIsIdentity(rs.getInt("isIdentity"));
                String desc = rs.getString("description");
                field.setDescription(desc == null ? "" : desc);
                field.setIsPk(rs.getInt("isPk"));
                fieldList.add(field);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbConn.closeALL(conn, st, rs, pst);
        }
        return fieldList;

    }

    /**
     * 根据库名聂所有表
     *
     * @param tableSchema
     * @return
     */
    public List<Table> getAllTables(String tableSchema) {
        List<Table> fieldList = new ArrayList<Table>();
        JdbcUtil dbConn = new JdbcUtil();

        try {
            this.conn = dbConn.getConnection();
            this.sqlstr = String.format(tableSql, tableSchema);
            this.st = conn.createStatement();
            this.rs = this.st.executeQuery(sqlstr);
            while (rs.next()) {
                Table table = new Table();
                String tableName = rs.getString("name");
                table.setName(tableName);
                table.setProName(StringUtil.convertField(tableName));
                String desc = rs.getString("description");
                table.setDescription(desc == null ? "" : desc);
                fieldList.add(table);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            dbConn.closeALL(conn, st, rs, pst);
        }
        return fieldList;
    }
}
  • 下一节我们要进行文件生成封装