wiki.xsx.jg.core.CommonService 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.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