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
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.
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 {
/**
* 获取对应的数据库实现类对象
* @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.getTypeName()){
case "mysql":
service = new MysqlService(database);
break;
case "oracle":
service = new OracleService(database);
break;
case "sqlserver":
service = new SQLServerService(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");
try (Connection connection = DriverManager.getConnection(dbUrl, properties)){
Map dbInitInfo = getDBInitInfo(dbUrl, userName, tableNames, isCreateFile);
Database database = new Database(
dbInitInfo.get("db_name"),
dbInitInfo.get("db_type"),
dbInitInfo.get("db_schema"),
driverClassName,
dbUrl,
userName,
password
);
if (isCreateFile){
try (
PreparedStatement statement = connection.prepareStatement(dbInitInfo.get("t_query_sql"));
ResultSet result = statement.executeQuery()
){
int row = 0;
while (result.next()){
database.getTableMap().put(
result.getString("TABLE_NAME").toLowerCase(),
new Table(
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", "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", "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", "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 {
throw new SQLException("Unsupported database type");
}
return data;
}
}