携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第8天，点击查看活动详情
1.优化之后的代码
1.1 PostgreSQLJdbcUtils
public class PostgreSQLJdbcUtils {
//pg 驱动
private static final String POSTGRES_DRIVER_CLASS = "org.postgresql.Driver";
//pg url前缀
private static final String POSTGRES_JDBC_PREFIX = "jdbc:postgresql";
//pg的默认schema
private static final String POSTGRESQL_DEFAULT_SCHEMA = "public";
//日志
private static final Logger LOG = LoggerFactory.getLogger(PostgreSQLJdbcUtils.class);
/**
* Get PostgreSQL connection from the url and user.
*
* @param url jdbc url, such as jdbc:mysql://host:port/database
* @param user Username for JDBC URL
* @param password User password
* @return {@link Connection}
* @throws Exception on get connection error
*/
public static Connection getConnection(String url, String user, String password)
throws Exception {
if (StringUtils.isBlank(url) || !url.startsWith(POSTGRES_JDBC_PREFIX)) {
throw new Exception("PostgreSQL server URL was invalid, it should start with jdbc:postgresql");
}
Connection conn;
try {
Class.forName(POSTGRES_DRIVER_CLASS);
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
String errorMsg = "get PostgreSQL connection error, please check postgresql jdbc url, username or password";
LOG.error(errorMsg, e);
throw new Exception(errorMsg + ": " + e.getMessage());
}
if (conn == null) {
throw new Exception("get PostgreSQL connection failed, please contact administrator");
}
LOG.info("get PostgreSQL connection success, url={}", url);
return conn;
}
/**
* Execute SQL command on PostgreSQL.
*
* @param conn JDBC Connection {@link Connection}
* @param sql SQL string to be executed
* @throws Exception on execute SQL error
*/
public static void executeSql(final Connection conn, final String sql) throws Exception {
try (Statement stmt = conn.createStatement()) {
stmt.execute(sql);
LOG.info("execute sql [{}] success", sql);
}
}
/**
* Execute batch query SQL on PostgreSQL.
*
* @param conn JDBC Connection {@link Connection}
* @param sqls SQL string to be executed
* @throws Exception on get execute SQL batch error
*/
public static void executeSqlBatch(final Connection conn, final List<String> sqls) throws Exception {
conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
for (String entry : sqls) {
stmt.execute(entry);
}
conn.commit();
LOG.info("execute sql [{}] success", sqls);
} finally {
conn.setAutoCommit(true);
}
}
/**
* Create PostgreSQL schema by schemaNama
*
* @param conn JDBC Connection {@link Connection}
* @param schemaName PostgreSQL schema name
* @param userName PostgreSQL user name
* @throws Exception on create schema error
*/
public static void createSchema(final Connection conn, final String schemaName, final String userName)
throws Exception {
if (checkSchemaExist(conn, schemaName)) {
LOG.info("the schema [{}] are exists", schemaName);
} else {
final String sql = PostgreSQLSqlBuilder.buildCreateSchema(schemaName, userName);
executeSql(conn, sql);
LOG.info("execute create schema sql [{}] success", sql);
}
}
/**
* Check whether the schema exists.
*
* @param conn JDBC Connection {@link Connection}
* @param schemaName PostgreSQL schema name
* @return true if schema exist in the table, otherwise false
* @throws Exception on check column exist error
*/
public static boolean checkSchemaExist(final Connection conn, final String schemaName) throws Exception {
boolean result = false;
if (POSTGRESQL_DEFAULT_SCHEMA.equals(schemaName)) {
result = true;
} else {
final String checkColumnSql = PostgreSQLSqlBuilder.getCheckSchema(schemaName);
try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(checkColumnSql)) {
if (Objects.nonNull(resultSet) && resultSet.next()) {
int count = resultSet.getInt(1);
if (count > 0) {
result = true;
}
}
}
}
LOG.info("check schema exist for schema={}, result={}", schemaName, result);
return result;
}
/**
* Check whether the column exists in the table.
*
* @param conn JDBC Connection {@link Connection}
* @param schemaName PostgreSQL schema name
* @param tableName PostgreSQL table name
* @param column PostgreSQL table column name
* @return true if column exist in the table, otherwise false
* @throws Exception on check column exist error
*/
public static boolean checkColumnExist(final Connection conn, final String schemaName, final String tableName,
final String column) throws Exception {
boolean result = false;
final String checkColumnSql = PostgreSQLSqlBuilder.getCheckColumn(schemaName, tableName, column);
try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(checkColumnSql)) {
if (Objects.nonNull(resultSet) && resultSet.next()) {
int count = resultSet.getInt(1);
if (count > 0) {
result = true;
}
}
}
LOG.info("check column exist for table={}, column={}, result={}", tableName, column, result);
return result;
}
/**
* Create Greenplum table by GreenplumTableInfo
*
* @param conn JDBC Connection {@link Connection}
* @param tableInfo Greenplum table info {@link PostgreSQLTableInfo}
* @throws Exception on create table error
*/
public static void createTable(final Connection conn, final PostgreSQLTableInfo tableInfo)
throws Exception {
if (checkTablesExist(conn, tableInfo.getSchemaName(), tableInfo.getTableName())) {
LOG.info("the table [{}] are exists", tableInfo.getTableName());
} else {
final List<String> createTableSqls = PostgreSQLSqlBuilder.buildCreateTableSql(tableInfo);
executeSqlBatch(conn, createTableSqls);
LOG.info("execute sql [{}] success", createTableSqls);
}
}
/**
* Check tables from the Greenplum information_schema.
*
* @param conn JDBC Connection {@link Connection}
* @param schemaName PostgreSQL database name
* @param tableName PostgreSQL table name
* @return true if table exist, otherwise false
* @throws Exception on check table exist error
*/
public static boolean checkTablesExist(final Connection conn, final String schemaName, final String tableName)
throws Exception {
boolean result = false;
final String checkTableSql = PostgreSQLSqlBuilder.getCheckTable(schemaName, tableName);
try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(checkTableSql)) {
if (null != resultSet && resultSet.next()) {
int size = resultSet.getInt(1);
if (size > 0) {
result = true;
}
}
}
LOG.info("check table exist for username={} table={}, result={}", schemaName, tableName, result);
return result;
}
/**
* Query all columns of the tableName.
*
* @param conn JDBC Connection {@link Connection}
* @param schemaName PostgreSQL schema name
* @param tableName PostgreSQL table name
* @return {@link List}
* @throws Exception on get columns error
*/
public static List<PostgreSQLColumnInfo> getColumns(final Connection conn, final String schemaName,
final String tableName) throws Exception {
final List<PostgreSQLColumnInfo> columnList = new ArrayList<>();
final String querySql = PostgreSQLSqlBuilder.buildDescTableSql(schemaName, tableName);
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySql)) {
while (rs.next()) {
columnList.add(new PostgreSQLColumnInfo(rs.getString(1), rs.getString(2),
rs.getString(3)));
}
}
return columnList;
}
/**
* Add columns for Greenpluum table.
*
* @param conn JDBC Connection {@link Connection}
* @param schemaName PostgreSQL schema name
* @param tableName PostgreSQL table name
* @param columns PostgreSQL columns to be added
* @throws Exception on add columns error
*/
public static void addColumns(final Connection conn, final String schemaName, final String tableName,
final List<PostgreSQLColumnInfo> columns) throws Exception {
final List<PostgreSQLColumnInfo> columnInfos = new ArrayList<>();
for (PostgreSQLColumnInfo columnInfo : columns) {
if (!checkColumnExist(conn, schemaName, tableName, columnInfo.getName())) {
columnInfos.add(columnInfo);
}
}
final List<String> addColumnSql = PostgreSQLSqlBuilder.buildAddColumnsSql(schemaName, tableName, columnInfos);
executeSqlBatch(conn, addColumnSql);
LOG.info("execute add columns sql [{}] success", addColumnSql);
}
}
1.2 PostgreSQLSqlBuilder
public class PostgreSQLSqlBuilder {
private static final Logger LOGGER = LoggerFactory.getLogger(PostgreSQLSqlBuilder.class);
/**
* Build SQL to check whether the table exists.
*
* @param schemaName PostgreSQL schema name
* @param tableName PostgreSQL table name
* @return the check table SQL string
*/
public static String getCheckTable(final String schemaName, final String tableName) {
final StringBuilder sqlBuilder = new StringBuilder()
.append("SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '")
.append(schemaName)
.append("' AND TABLE_TYPE = 'BASE TABLE' ")
.append(" AND TABLE_NAME = '")
.append(tableName)
.append("' ;");
LOGGER.info("check table sql: {}", sqlBuilder);
return sqlBuilder.toString();
}
/**
* Build SQL to check whether the column exists.
*
* @param schemaName PostgreSQL table name
* @param columnName PostgreSQL column name
* @return the check column SQL string
*/
public static String getCheckColumn(final String schemaName, final String tableName, final String columnName) {
final StringBuilder sqlBuilder = new StringBuilder()
.append("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '")
.append(schemaName)
.append("' AND TABLE_NAME = '")
.append(tableName)
.append("' AND COLUMN_NAME = '")
.append(columnName)
.append("' ;");
LOGGER.info("check table sql: {}", sqlBuilder);
return sqlBuilder.toString();
}
/**
* Build SQL to check whether the schema exists.
*
* @param schemaName
* @return
*/
public static String getCheckSchema(final String schemaName) {
return new StringBuilder()
.append("SELECT COUNT(1) FROM INFORMATION_SCHEMA.SCHEMATA ")
.append(" WHERE SCHEMA_NAME = '")
.append(schemaName)
.append("';")
.toString();
}
/**
* Build create PostgreSQL schema SQL String
*
* @param schemaName schema name
* @param user user name
* @return SQL String
*/
public static String buildCreateSchema(final String schemaName, final String user) {
return new StringBuilder()
.append(" CREATE SCHEMA "")
.append(schemaName)
.append("" AUTHORIZATION "")
.append(user)
.append("";")
.toString();
}
/**
* Build create table SQL by PostgreSQLTableInfo.
*
* @param table PostgreSQL table info {@link PostgreSQLTableInfo}
* @return the create table SQL String
*/
public static List<String> buildCreateTableSql(final PostgreSQLTableInfo table) {
final List<String> sqls = Lists.newArrayList();
final StringBuilder createSql = new StringBuilder()
.append("CREATE TABLE ").append(table.getSchemaName())
.append("."")
.append(table.getTableName())
.append(""")
.append(buildCreateColumnsSql(table));
sqls.add(createSql.toString());
// column comments
sqls.addAll(getColumnsComment(table.getSchemaName(), table.getTableName(), table.getColumns()));
// table comment
if (StringUtils.isNotEmpty(table.getComment())) {
sqls.add(getTableComment(table));
}
LOGGER.info("create table sql : {}", sqls);
return sqls;
}
/**
* Build columns comment SQLs
*
* @param tableName PostgreSQL table name
* @param columns PostgreSQL colum list {@link PostgreSQLColumnInfo}
* @return the SQL String list
*/
private static List<String> getColumnsComment(final String schemaName, final String tableName,
List<PostgreSQLColumnInfo> columns) {
final List<String> commentList = new ArrayList<>();
for (PostgreSQLColumnInfo columnInfo : columns) {
if (StringUtils.isNoneBlank(columnInfo.getComment())) {
StringBuilder commSql = new StringBuilder();
commSql.append("COMMENT ON COLUMN "")
.append(schemaName)
.append(""."")
.append(tableName)
.append(""."")
.append(columnInfo.getName())
.append("" IS '")
.append(columnInfo.getComment())
.append("' ;");
commentList.add(commSql.toString());
}
}
return commentList;
}
/**
* Build table comment SQL
*
* @param tableInfo PostgreSQL table info {@link PostgreSQLTableInfo}
* @return the SQL String
*/
private static String getTableComment(final PostgreSQLTableInfo tableInfo) {
return new StringBuilder()
.append("COMMENT ON TABLE "")
.append(tableInfo.getSchemaName())
.append(""."")
.append(tableInfo.getTableName())
.append("" IS '")
.append(tableInfo.getComment())
.append("';")
.toString();
}
/**
* Build add columns SQL.
*
* @param schemaName PostgreSQL schema name
* @param tableName PostgreSQL table name
* @param columnList PostgreSQL column list {@link List}
* @return add column SQL string list
*/
public static List<String> buildAddColumnsSql(final String schemaName, final String tableName,
List<PostgreSQLColumnInfo> columnList) {
final List<String> resultList = Lists.newArrayList();
final StringBuilder sqlBuilder = new StringBuilder();
columnList.forEach(columnInfo -> {
sqlBuilder.append("ALTER TABLE "")
.append(schemaName)
.append(""."")
.append(tableName)
.append("" ADD "")
.append(columnInfo.getName())
.append("" ")
.append(columnInfo.getType())
.append(" ");
resultList.add(sqlBuilder.toString());
sqlBuilder.delete(0, sqlBuilder.length());
});
resultList.addAll(getColumnsComment(schemaName, tableName, columnList));
LOGGER.info("add columns sql={}", resultList);
return resultList;
}
/**
* Build create column SQL.
*
* @param table PostgreSQL table info {@link PostgreSQLTableInfo}
* @return create column SQL string
*/
private static String buildCreateColumnsSql(final PostgreSQLTableInfo table) {
final List<String> columnList = getColumnsInfo(table.getColumns());
final StringBuilder sql = new StringBuilder()
.append(" (")
.append(StringUtils.join(columnList, ","));
if (!StringUtils.isEmpty(table.getPrimaryKey())) {
sql.append(", PRIMARY KEY (")
.append(table.getPrimaryKey())
.append(")");
}
sql.append(") ");
return sql.toString();
}
/**
* Build column info by PostgreSQLColumnInfo list.
*
* @param columns PostgreSQL column info {@link PostgreSQLColumnInfo} list
* @return the SQL list
*/
private static List<String> getColumnsInfo(final List<PostgreSQLColumnInfo> columns) {
final List<String> columnList = new ArrayList<>();
final StringBuilder columnBuilder = new StringBuilder();
columns.forEach(columnInfo -> {
columnBuilder.append(""")
.append(columnInfo.getName())
.append("" ")
.append(columnInfo.getType());
columnList.add(columnBuilder.toString());
columnBuilder.delete(0, columnBuilder.length());
});
return columnList;
}
/**
* Build query table's all cloumn SQL.
*
* @param schemaName PostgreSQL schema name
* @param tableName PostgreSQL table name
* @return desc table SQL string
*/
public static String buildDescTableSql(final String schemaName, final String tableName) {
StringBuilder sql = new StringBuilder().append(
"SELECT A.COLUMN_NAME,A.UDT_NAME,C.DESCRIPTION FROM INFORMATION_SCHEMA.COLUMNS A")
.append(" LEFT JOIN (SELECT PC.OID AS OOID,PN.NSPNAME,PC.RELNAME")
.append(" FROM PG_CLASS PC LEFT OUTER JOIN PG_NAMESPACE PN ON PC.RELNAMESPACE = PN.OID ")
.append(" WHERE PN.NSPNAME ='")
.append(schemaName)
.append("' AND PC.RELNAME = '")
.append(tableName)
.append("') B ON A.TABLE_SCHEMA = B.NSPNAME AND A.TABLE_NAME = B.RELNAME")
.append(" LEFT JOIN PG_CATALOG.PG_DESCRIPTION C ")
.append("ON B.OOID = C.OBJOID AND A.ORDINAL_POSITION = C.OBJSUBID")
.append(" WHERE A.TABLE_SCHEMA = '")
.append(schemaName)
.append("' AND A.TABLE_NAME = '")
.append(tableName)
.append("' ORDER BY C.OBJSUBID ;");
LOGGER.info("desc table sql={}", sql);
return sql.toString();
}
}
2.主要改进
对之前代码的主要改进在于，首先定义了一个获取JDBC连接的方法。调用其他的方法时，必须首先获取连接。使用完毕之后，通过try-with-resource自动关闭连接。这样连接可以复用。 调用方式如下：
public void testDbResource() {
String url = "jdbc:postgresql://localhost:5432/testdb";
String username = "pguser";
String password = "123456";
String tableName = "test01";
String schemaName = "public";
try (Connection connection = PostgreSQLJdbcUtils.getConnection(url, username, password)) {
PostgreSQLTableInfo tableInfo = bulidTestPostgreSQLTableInfo(username, schemaName, tableName);
PostgreSQLJdbcUtils.createTable(connection, tableInfo);
List<PostgreSQLColumnInfo> addColumns = buildAddColumns();
PostgreSQLJdbcUtils.addColumns(connection, schemaName, tableName, addColumns);
List<PostgreSQLColumnInfo> columns = PostgreSQLJdbcUtils.getColumns(connection, schemaName, tableName);
Assertions.assertEquals(columns.size(), tableInfo.getColumns().size() + addColumns.size());
} catch (Exception e) {
// print to local console
e.printStackTrace();
}
}
此外，考虑到对于PostgreSQL数据库而言，数据库的dbName在连接字符串中必须携带，否则不能创建连接。 因此不用考虑db是否已创建，只需要考虑schema的创建情况。如果不使用默认的public schema的话，PostgreSQL也可以自定义schema,对于PostgreSQL而言，表建立在schema之下。对于不同的schema可以创建相同的表。
