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

Go to download
package wiki.xsx.jg.core;

import wiki.xsx.jg.log.Logger;

import java.sql.*;
import java.util.*;

/**
 * 数据库抽象实现类
 */
public abstract class AbstractService implements DatabaseService{
    private Database database;

    public AbstractService(Database database) {
        this.database = database;
    }

    @Override
    public void setEntityInfo() throws Exception {
        Database database = this.getDatabase();
        try (Connection connection = database.getConnection()){
            DatabaseMetaData dbmd = connection.getMetaData();
            for (Entity entity : database.getEntityMap().values()){
                ResultSet result = dbmd.getColumns(database.getName(), database.getSchema(), entity.getName().toUpperCase(), "%");
                while (result.next()){
                    Field field = new Field();
                    field.setName(result.getString("COLUMN_NAME").toLowerCase());
                    field.setType(result.getString("TYPE_NAME").toUpperCase());
                    field.setLengh(result.getInt("COLUMN_SIZE"));
                    field.setScale(result.getString("DECIMAL_DIGITS")==null?0:result.getInt("DECIMAL_DIGITS"));
                    field.setJavaType(this.getTypeClass(field.getType(), field.getLengh(), field.getScale()));
                    // 此处直接判断为null会抛异常，原因不明
                    field.setDefaultValue(result.getString("COLUMN_DEF"));
                    // 再次设值
                    if (database.getType()==Database.Type.SQLSERVER){
                        field.setDefaultValue(field.getDefaultValue()!=null?field.getDefaultValue().trim().replaceAll("\\(|\\)", ""):null);
                    }else{
                        field.setDefaultValue(field.getDefaultValue()!=null?field.getDefaultValue().trim():null);
                    }
                    field.setIsAutoIncrement(result.getString("IS_AUTOINCREMENT").equalsIgnoreCase("YES")?true:false);
                    field.setIsNotNull(result.getInt("NULLABLE")==0?true:false);
                    setFieldComment(connection, database.getType(), entity.getName(), field, result.getString("REMARKS"));
                    entity.getFieldMap().put(field.getName(), field);
                }
                result.close();
            }
        }
    }

    @Override
    public void createTables(Set entitySet) throws Exception {
        Database database = this.getDatabase();
        try (
            Connection connection = database.getConnection();
            Statement statement = connection.createStatement()
        ){
            for (Entity entity : entitySet){
                Logger.info("Create Table: " + entity.getName());
                Logger.info("SQL: " + this.getDropTableSQL(entity.getName()));
                statement.addBatch(this.getDropTableSQL(entity.getName()));

                Map SQLMap = this.getCreateTableSQLMap(entity);
                Logger.info("SQL: " + SQLMap.get("createSQL").toString());
                statement.addBatch(SQLMap.get("createSQL").toString());

                Queue commentQueue = (Queue) SQLMap.get("commentSQL");
                while(commentQueue.size() > 0) {
                    StringBuilder commentSQL = new StringBuilder(commentQueue.poll());
                    Logger.info("SQL: " + commentSQL.toString());
                    statement.addBatch(commentSQL.toString());
                }
                Logger.nextLine();
            }
            statement.executeBatch();
        }
    }

    @Override
    public Database getDatabase() {
        return this.database;
    }

    public abstract Class getTypeClass(String dataTypeName, int length, int scale);

    public abstract String getDataType(Class type);

    public abstract String getDropTableSQL(String className);

    @Override
    public Map getCreateTableSQLMap(Entity entity) {
        Map SQLMap = new HashMap<>();
        Queue commentQueue = new LinkedList<>();
        StringBuilder pk = new StringBuilder();
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE ").append(entity.getName().toUpperCase()).append("(");
        for (Field field : entity.getColumnSet()){
            sql.append(field.getName()).append(" ");
            if (field.getType()!=null){
                sql.append(field.getType());
            }else{
                sql.append(this.getDataType(field.getJavaType()));
            }
            if (field.getDefaultValue()!=null){
                sql.append(" DEFAULT ").append(field.getDefaultValue());
            }
            if (field.getIsNotNull()){
                sql.append(" NOT NULL ");
            }
            sql.append(",");
            if (field.getIsPrimaryKey()){
                pk.append(field.getName()).append(",");
            }
            if (field.getComment()!=null){
                StringBuilder comment = new StringBuilder();
                comment.append(" COMMENT ON COLUMN ")
                        .append(entity.getName().toUpperCase()).append(".").append(field.getName())
                        .append(" IS '").append(field.getComment()).append("' ");
                commentQueue.offer(comment.toString());
            }
        }
        if (pk.length()>0){
            sql.append("PRIMARY KEY (").append(pk.substring(0, pk.length()-1)).append(")");
        }else{
            sql = sql.deleteCharAt(sql.length()-1);
        }
        sql.append(") ");
        if (entity.getComment()!=null){
            StringBuilder comment = new StringBuilder();
            comment.append(" COMMENT ON TABLE ").append(entity.getName().toUpperCase())
                    .append(" IS '").append(entity.getComment()).append("' ");
            commentQueue.offer(comment.toString());
        }
        SQLMap.put("createSQL", sql.toString());
        SQLMap.put("commentSQL", commentQueue);
        return SQLMap;
    }

    /**
     * 设置字段的注释
     * @param connection 数据库连接对象
     * @param dbType 数据库类型
     * @param tableName 表名称
     * @param field 字段对象
     * @param value 注释
     * @throws SQLException 异常信息
     */
    private static void setFieldComment(
            Connection connection,
            Database.Type dbType,
            String tableName,
            Field field,
            String value
    ) throws SQLException {
        if (dbType==Database.Type.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, field.getName());
                ResultSet rs = statement.executeQuery();
                while (rs.next()){
                    field.setComment(rs.getString("REMARKS")==null?"":rs.getString("REMARKS"));
                }
                rs.close();
            }
        }else{
            field.setComment(value==null?"":value);
        }
    }
}