wiki.xsx.jg.core.CommonService Maven / Gradle / Ivy

Show more of this group Show more artifacts with this name
Show all versions of JG-All Show documentation
a generator, from the database tables convert to the Java classes or from the Java classes convert to the database tables. support mysql, oracle and sqlserver.
There is a newer version: 1.2.1
Show newest version
package wiki.xsx.jg.core;

import wiki.xsx.jg.log.Logger;

import java.sql.*;
import java.util.Properties;
import java.util.Set;

/**
 * 通用数据库实现类
 */
public class CommonService{
    /**
     * 根据数据库服务实现类设置表的信息
     * @param service 数据库服务实现类
     * @throws SQLException 异常信息
     */
    public static void setTableInfo(
            DatabaseService service
    ) throws SQLException {
        Database database = service.getDatabase();
        System.setProperty("jdbc.drivers", database.getDriverClassName());
        Properties properties = new Properties();
        properties.put("user", database.getUserName());
        properties.put("password", database.getPassword());
        properties.put("remarksReporting", "true");
        try (
            Connection connection = DriverManager.getConnection(database.getUrl(), properties)
        ){
            DatabaseMetaData dbmd = connection.getMetaData();
            for (Table table : database.getTableMap().values()){
                ResultSet result = dbmd.getColumns(database.getName(), database.getSchema(), table.getName().toUpperCase(), "%");
                while (result.next()){
                    Column column = new Column();
                    column.setName(result.getString("COLUMN_NAME").toLowerCase());
                    column.setType(result.getString("TYPE_NAME").toUpperCase());
                    column.setLengh(result.getInt("COLUMN_SIZE"));
                    column.setScale(
                            result.getString("DECIMAL_DIGITS")==null?
                            0:result.getInt("DECIMAL_DIGITS")
                    );
                    column.setJavaType(service.getTypeClass(column.getType(), column.getLengh(), column.getScale()));
                    // 此处直接判断为null会抛异常，原因不明
                    column.setDefaultValue(result.getString("COLUMN_DEF"));
                    // 再次设值
                    column.setDefaultValue(column.getDefaultValue()!=null?column.getDefaultValue().trim():null);
                    column.setIsAutoIncrement(result.getString("IS_AUTOINCREMENT").equalsIgnoreCase("YES")?true:false);
                    column.setIsNotNull(result.getInt("NULLABLE")==0?true:false);
                    setColumnRemarks(connection, database.getTypeName(), table.getName(), column, result.getString("REMARKS"));
                    table.getColumnMap().put(column.getName(), column);
                }
                result.close();
            }
        }
    }

    /**
     * 根据数据库表集合创建数据表
     * @param tableSet 数据库对象
     * @param service 数据库服务实现类
     * @throws SQLException 异常信息
     */
    public static void createTables(
            Set tableSet,
            DatabaseService service
    ) throws SQLException {
        Database database = service.getDatabase();
        System.setProperty("jdbc.drivers", database.getDriverClassName());
        try (
            Connection connection = DriverManager.getConnection(database.getUrl(), database.getUserName(), database.getPassword());
            Statement statement = connection.createStatement()
        ){
            for (Table table : tableSet){
                Logger.info("SQL: " + service.getDropTableSQL(table.getName()));
                statement.addBatch(service.getDropTableSQL(table.getName()));

                Logger.info("SQL: " + service.getCreateTableSQL(table));
                statement.addBatch(service.getCreateTableSQL(table));

                statement.executeBatch();
                Logger.info("Execute success\n");
            }
        }catch (SQLException e){
            Logger.info("Execute fail");
            throw e;
        }
    }

    /**
     * 设置列的注释
     * @param connection sql执行对象
     * @param dbTypeName 数据库类型名称
     * @param tableName 表名称
     * @param column 列对象
     * @param value 注释
     * @throws SQLException 异常信息
     */
    private static void setColumnRemarks(
            Connection connection,
            String dbTypeName,
            String tableName,
            Column column,
            String value
    ) throws SQLException{
        if (dbTypeName.equalsIgnoreCase("sqlserver")){
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT ")
                .append("CAST(C.value AS NVARCHAR(255)) AS REMARKS ")
                .append("FROM sys.tables A ")
                .append("INNER JOIN sys.columns B ON B.object_id = A.object_id ")
                .append("LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id ")
                .append("WHERE A.name = ?").append(" AND B.name = ?");
            try (
                PreparedStatement statement = connection.prepareStatement(sql.toString())
            ){
                statement.setString(1, tableName.toUpperCase());
                statement.setString(2, column.getName());
                ResultSet rs = statement.executeQuery();
                while (rs.next()){
                    column.setRemark(rs.getString("REMARKS")==null?"":rs.getString("REMARKS"));
                }
                rs.close();
            }
        }else{
            column.setRemark(value==null?"":value);
        }
    }
}




© 2015 - 2024 Weber Informatics LLC | Privacy Policy