Recently, I want to write a simple code generation tool to generate entity, mapper interface, mapping file and service class according to the database table structure, so I have looked up some data, such as how to obtain the database table, field in table, field type, field annotation and other information.
Finally, a simple tool class is written, with the code as follows:
package com.lnjecit.generator.util; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.sql.*; import java.util.ArrayList; import java.util.List; public class DatabaseUtil { private final static Logger LOGGER = LoggerFactory.getLogger(DatabaseUtil.class); private static final String DRIVER = "com.mysql.jdbc.Driver"; private static final String URL = "jdbc:mysql://localhost:3306/javaweb?useUnicode=true&characterEncoding=utf8"; private static final String USERNAME = "root"; private static final String PASSWORD = "mysql"; private static final String SQL = "SELECT * FROM ";// Database operations static { try { Class.forName(DRIVER); } catch (ClassNotFoundException e) { LOGGER.error("can not load jdbc driver", e); } } /** * Get database connection * * @return */ public static Connection getConnection() { Connection conn = null; try { conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (SQLException e) { LOGGER.error("get connection failure", e); } return conn; } /** * Close database connection * @param conn */ public static void closeConnection(Connection conn) { if(conn != null) { try { conn.close(); } catch (SQLException e) { LOGGER.error("close connection failure", e); } } } /** * Get all table names under the database */ public static List<String> getTableNames() { List<String> tableNames = new ArrayList<>(); Connection conn = getConnection(); ResultSet rs = null; try { //Get metadata of database DatabaseMetaData db = conn.getMetaData(); //Get all table names from metadata rs = db.getTables(null, null, null, new String[] { "TABLE" }); while(rs.next()) { tableNames.add(rs.getString(3)); } } catch (SQLException e) { LOGGER.error("getTableNames failure", e); } finally { try { rs.close(); closeConnection(conn); } catch (SQLException e) { LOGGER.error("close ResultSet failure", e); } } return tableNames; } /** * Get all field names in the table * @param tableName Table name * @return */ public static List<String> getColumnNames(String tableName) { List<String> columnNames = new ArrayList<>(); //Connection to database Connection conn = getConnection(); PreparedStatement pStemt = null; String tableSql = SQL + tableName; try { pStemt = conn.prepareStatement(tableSql); //ResultSet Metadata ResultSetMetaData rsmd = pStemt.getMetaData(); //Tabular number int size = rsmd.getColumnCount(); for (int i = 0; i < size; i++) { columnNames.add(rsmd.getColumnName(i + 1)); } } catch (SQLException e) { LOGGER.error("getColumnNames failure", e); } finally { if (pStemt != null) { try { pStemt.close(); closeConnection(conn); } catch (SQLException e) { LOGGER.error("getColumnNames close pstem and connection failure", e); } } } return columnNames; } /** * Get all field types in the table * @param tableName * @return */ public static List<String> getColumnTypes(String tableName) { List<String> columnTypes = new ArrayList<>(); //Connection to database Connection conn = getConnection(); PreparedStatement pStemt = null; String tableSql = SQL + tableName; try { pStemt = conn.prepareStatement(tableSql); //ResultSet Metadata ResultSetMetaData rsmd = pStemt.getMetaData(); //Tabular number int size = rsmd.getColumnCount(); for (int i = 0; i < size; i++) { columnTypes.add(rsmd.getColumnTypeName(i + 1)); } } catch (SQLException e) { LOGGER.error("getColumnTypes failure", e); } finally { if (pStemt != null) { try { pStemt.close(); closeConnection(conn); } catch (SQLException e) { LOGGER.error("getColumnTypes close pstem and connection failure", e); } } } return columnTypes; } /** * Get all comments for fields in the table * @param tableName * @return */ public static List<String> getColumnComments(String tableName) { List<String> columnTypes = new ArrayList<>(); //Connection to database Connection conn = getConnection(); PreparedStatement pStemt = null; String tableSql = SQL + tableName; List<String> columnComments = new ArrayList<>();//Column name comment collection ResultSet rs = null; try { pStemt = conn.prepareStatement(tableSql); rs = pStemt.executeQuery("show full columns from " + tableName); while (rs.next()) { columnComments.add(rs.getString("Comment")); } } catch (SQLException e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); closeConnection(conn); } catch (SQLException e) { LOGGER.error("getColumnComments close ResultSet and connection failure", e); } } } return columnComments; } public static void main(String[] args) { List<String> tableNames = getTableNames(); System.out.println("tableNames:" + tableNames); for (String tableName : tableNames) { System.out.println("ColumnNames:" + getColumnNames(tableName)); System.out.println("ColumnTypes:" + getColumnTypes(tableName)); System.out.println("ColumnComments:" + getColumnComments(tableName)); } } }
Before running the code, modify the database configuration, and then run the code to get the detailed structure of the database table.
Later, we can write a simple code generator to generate entity, interface and mapping file through table structure. Reduce the coding work in the development of ssm project.
Make a little progress every day, and encourage!