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

Go to download
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.
The newest version!
package wiki.xsx.jg.core;

import wiki.xsx.jg.util.StrUtil;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

/**
 * 数据库适配器
 */
public class DatabaseAdapter {

    private DatabaseAdapter(){}

    /**
     * 获取对应的数据库实现类对象
     * @param driverClassName 数据库驱动名称
     * @param dbUrl 数据库地址
     * @param userName 数据库用户名
     * @param password 数据库密码
     * @param tableNames 数据库表名称，多个逗号分隔
     * @param isCreateFile 是否为创建文件
     * @return 返回数据库实现类对象
     * @throws SQLException 异常信息
     */
    public static DatabaseService getServiceInstance(
            String driverClassName,
            String dbUrl,
            String userName,
            String password,
            String tableNames,
            boolean isCreateFile
    ) throws SQLException {
        DatabaseService service;
        Database database = getDatabase(driverClassName, dbUrl, userName, password, tableNames, isCreateFile);
        switch (database.getType()){
            case MYSQL:
                service = new MySQLService(database);
                break;
            case ORACLE:
                service = new OracleService(database);
                break;
            case SQLSERVER:
                service = new SQLServerService(database);
                break;
            case POSTGRESQL:
                service = new PostgreSQLService(database);
                break;
            default:
                throw new SQLException("Unsupported database type");
        }
        return service;
    }

    /**
     * 获取数据库对象(创建文件)
     * @param driverClassName 数据库驱动名称
     * @param dbUrl 数据库地址
     * @param userName 数据库用户名
     * @param password 数据库密码
     * @param tableNames 数据库表名称，多个逗号分隔
     * @param isCreateFile 是否为创建文件
     * @return 返回数据库对象
     * @throws SQLException 异常信息
     */
    private static Database getDatabase(
            String driverClassName,
            String dbUrl,
            String userName,
            String password,
            String tableNames,
            boolean isCreateFile
    ) throws SQLException {
        System.setProperty("jdbc.drivers", driverClassName);
        Properties properties = new Properties();
        properties.put("user", userName);
        properties.put("password", password);
        properties.put("remarksReporting", "true");
        Connection connection = DriverManager.getConnection(dbUrl, properties);
        Map dbInitInfo = getDBInitInfo(dbUrl, userName, tableNames, isCreateFile);
        Database database = new Database(
            dbInitInfo.get("db_name").toString(),
            (Database.Type)dbInitInfo.get("db_type"),
            dbInitInfo.get("db_schema").toString(),
            driverClassName,
            dbUrl,
            userName,
            password,
            connection
        );
        if (isCreateFile){
            try (
                PreparedStatement statement = connection.prepareStatement(dbInitInfo.get("t_query_sql").toString());
                ResultSet result = statement.executeQuery()
            ){
                int row = 0;
                while (result.next()){
                    database.getEntityMap().put(
                        result.getString("TABLE_NAME").toLowerCase(),
                        new Entity(
                            result.getString("TABLE_NAME").toLowerCase(),
                            result.getString("REMARKS")==null?"":result.getString("REMARKS")
                        )
                    );
                    row = result.getRow();
                }
                if (row==0){
                    result.close();
                    throw new SQLException("the table \'" + tableNames + "\' is not exists");
                }
            }
        }
        return database;
    }

    /**
     * 根据数据库地址获取数据库初始化信息
     * @param dbUrl 数据库地址
     * @param userName 数据库用户名
     * @param tableNames 数据表名称，多个逗号分隔
     * @param isCreateFile 是否为创建文件
     * @return 返回数据库初始化信息
     * @throws SQLException 异常信息
     */
    private static Map getDBInitInfo(
            String dbUrl,
            String userName,
            String tableNames,
            boolean isCreateFile
    ) throws SQLException{
        Map data = new HashMap<>();
        if (dbUrl.startsWith("jdbc:mysql")){
            if (dbUrl.contains("?")){
                data.put("db_name", dbUrl.substring(dbUrl.lastIndexOf("/")+1, dbUrl.indexOf("?")));
            }else{
                data.put("db_name", dbUrl.substring(dbUrl.lastIndexOf("/")+1));
            }
            data.put("db_type", Database.Type.MYSQL);
            data.put("db_schema", data.get("db_name"));

            if (isCreateFile){
                StringBuilder builder = new StringBuilder();
                builder.append("SELECT ")
                        .append("TABLE_NAME,")
                        .append("TABLE_COMMENT AS REMARKS ")
                        .append("FROM ")
                        .append("INFORMATION_SCHEMA.TABLES ")
                        .append("WHERE ")
                        .append("TABLE_SCHEMA = '").append(data.get("db_schema")).append("' ");
                if (tableNames!=null&&tableNames.trim().length()>0){
                    builder.append("AND TABLE_NAME IN(").append(StrUtil.toSQLString(tableNames)).append(") ");
                }
                data.put("t_query_sql", builder.toString());
            }
        }else if (dbUrl.startsWith("jdbc:oracle")){
            data.put("db_name", dbUrl.substring(dbUrl.lastIndexOf("/")+1));
            data.put("db_type", Database.Type.ORACLE);
            data.put("db_schema", userName.toUpperCase());

            if (isCreateFile){
                StringBuilder builder = new StringBuilder();
                builder.append("SELECT TABLE_NAME,COMMENTS AS REMARKS FROM user_tab_comments ");
                if (tableNames!=null&&tableNames.trim().length()>0){
                    builder.append("WHERE TABLE_NAME IN(").append(StrUtil.toSQLString(tableNames.toUpperCase())).append(") ");
                }
                data.put("t_query_sql", builder.toString());
            }
        }else if (dbUrl.startsWith("jdbc:sqlserver")){
            data.put("db_name", dbUrl.substring(dbUrl.lastIndexOf("=")+1));
            data.put("db_type", Database.Type.SQLSERVER);
            data.put("db_schema", "%");

            if (isCreateFile){
                StringBuilder builder = new StringBuilder();
                builder.append("SELECT ")
                        .append("A.name AS TABLE_NAME,")
                        .append("CAST(C.value AS NVARCHAR(255)) AS REMARKS ")
                        .append("FROM sys.tables A ")
                        .append("LEFT JOIN sys.extended_properties C ON C.major_id = A.object_id AND minor_id=0 ");
                if (tableNames!=null&&tableNames.trim().length()>0){
                    builder.append("WHERE A.name IN(").append(StrUtil.toSQLString(tableNames.toUpperCase())).append(") ");
                }
                data.put("t_query_sql", builder.toString());
            }
        }else if (dbUrl.startsWith("jdbc:postgresql")){
            if (dbUrl.contains("?")){
                data.put("db_name", dbUrl.substring(dbUrl.lastIndexOf("/")+1, dbUrl.indexOf("?")));
            }else{
                data.put("db_name", dbUrl.substring(dbUrl.lastIndexOf("/")+1));
            }
            data.put("db_type", Database.Type.POSTGRESQL);
            data.put("db_schema", "%");

            if (isCreateFile){
                StringBuilder builder = new StringBuilder();
                builder.append("SELECT ")
                        .append("relname AS TABLE_NAME,")
                        .append("CAST(obj_description(relfilenode,'pg_class') AS VARCHAR) AS REMARKS ")
                        .append("FROM pg_class c ")
                        .append("WHERE relkind = 'r' ")
                        .append("AND relname NOT LIKE 'pg_%' ")
                        .append("AND relname NOT LIKE 'sql_%'");
                data.put("t_query_sql", builder.toString());
            }
        }else {
            throw new SQLException("Unsupported database type");
        }
        return data;
    }
}