MySQL导出表结构相关字段以及把字段由下划线转驼峰命名

1SELECT
2  COLUMN_COMMENT 中文名,
3  UPPER(COLUMN_NAME) 字段名,
4  UPPER(DATA_TYPE) 字段类型,
5  CHARACTER_MAXIMUM_LENGTH 长度,
6  IS_NULLABLE 是否为空
7FROM
8 INFORMATION_SCHEMA.COLUMNS
9where
10table_schema ='shwmm'
11AND
12table_name  = 'gis_interface_veh_his_gps_data'

1import java.sql.DriverManager;
2import java.sql.ResultSet;
3import java.sql.SQLException;
4import java.sql.Statement;
5import java.util.LinkedHashMap;
6import java.util.Map;
7
8import com.alibaba.fastjson.JSONObject;
9import com.mysql.jdbc.Connection;
10
11/**
12 * 工具类
13 * MySQL中以“_”分割的列名格式化为驼峰命名的列名
14 */
15public class FormatDbColumns {
16    static final String DB_URL = "jdbc:mysql://localhost:3306/shwmm";
17    // MySQL的JDBC URL编写方式：jdbc:mysql://主机名称：连接端口/数据库的名称
18    static final String USER = "root";
19    static final String PASS = "root";
20
21    public static void main(String[] args) throws SQLException,Exception{
22        Connection conn = null;
23        Statement stat = null;
24
25        // 注册驱动
26        Class.forName("com.mysql.jdbc.Driver");
27
28        // 创建链接
29        conn = (Connection) DriverManager.getConnection(DB_URL,USER,PASS);
30
31        String str = "am_alarm_detail";
32
33        // 执行查询
34        stat = conn.createStatement();
35        String sql = "select LOWER(COLUMN_NAME) as columnName from information_schema.COLUMNS where table_name = '"+str+"'";
36        ResultSet rs = stat.executeQuery(sql);
37        // 输出查询结果
38        int i = 0;
39        Map<String,String> map = new LinkedHashMap<>();
40        while(rs.next()){
41            i++;
42            String a = rs.getString("columnName");
43            String b= camelCaseName(a);
44            map.put(b,"xxx");
45            System.out.println(b);
46        }
47
48        System.out.println(JSONObject.toJSONString(map));
49        System.out.println(i);
50        // 关闭
51        try {
52            if (rs != null) {
53                rs.close();
54            }
55        } catch (SQLException e) {
56            e.printStackTrace();
57        } finally {
58            try {
59                if (stat != null) {
60                    stat.close();
61                }
62            } catch (SQLException e) {
63                e.printStackTrace();
64            } finally {
65                try {
66                    if (conn != null) {
67                        conn.close();
68                    }
69                } catch (SQLException e) {
70                    e.printStackTrace();
71                }
72            }
73        }
74    }
75
76    public static String camelCaseName(String underscoreName) {
77        StringBuilder result = new StringBuilder();
78        if (underscoreName != null && underscoreName.length() > 0) {
79            boolean flag = false;
80            for (int i = 0; i < underscoreName.length(); i++) {
81                char ch = underscoreName.charAt(i);
82                if ("_".charAt(0) == ch) {
83                    flag = true;
84                } else {
85                    if (flag) {
86                        result.append(Character.toUpperCase(ch));
87                        flag = false;
88                    } else {
89                        result.append(ch);
90                    }
91                }
92            }
93        }
94        return result.toString();
95
96    }
97}