携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第7天，点击查看活动详情
1.源码
有JDBC连接PostgreSQL的代码如下：
public class PostgreSQLJdbcUtils {
private static final String POSTGRES_DRIVER_CLASS = "org.postgresql.Driver";
private static final String SCHEMA_PATTERN = "public";
private static final String TABLE_TYPE = "TABLE";
private static final String COLUMN_LABEL_TABLE = "TABLE_NAME";
private static final String COLUMN_LABEL_COUNT = "count";
private static final String POSTGRES_JDBC_PREFIX = "jdbc:postgresql";
private static final Logger LOG = LoggerFactory.getLogger(PostgreSQLJdbcUtils.class);
/**
* Get PostgreSQL connection from the url and user
*/
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
*
* @return true if execute successfully
*/
public static boolean executeSql(String sql, String url, String user, String password) throws Exception {
try (Connection conn = getConnection(url, user, password)) {
Statement stmt = conn.createStatement();
LOG.info("execute sql [{}] success for url: {}", sql, url);
return stmt.execute(sql);
}
}
/**
* Execute query SQL on PostgreSQL
*
* @return the query result set
*/
public static ResultSet executeQuerySql(String sql, String url, String user, String password)
throws Exception {
try (Connection conn = getConnection(url, user, password)) {
Statement stmt = conn.createStatement();
LOG.info("execute sql [{}] success for url: {}", sql, url);
return stmt.executeQuery(sql);
}
}
/**
* Execute batch SQL commands on PostgreSQL
*/
public static void executeSqlBatch(List<String> sql, String url, String user, String password)
throws Exception {
try (Connection conn = getConnection(url, user, password)) {
Statement stmt = conn.createStatement();
for (String entry : sql) {
stmt.execute(entry);
}
LOG.info("execute sql [{}] success for url: {}", sql, url);
}
}
/**
* Create PostgreSQL database
*/
public static void createDb(String url, String user, String password, String dbName) throws Exception {
String checkDbSql = PostgreSQLSqlBuilder.getCheckDatabase(dbName);
ResultSet resultSet = executeQuerySql(checkDbSql, url, user, password);
if (resultSet != null) {
resultSet.next();
if (resultSet.getInt(COLUMN_LABEL_COUNT) == 0) {
String createDbSql = PostgreSQLSqlBuilder.buildCreateDbSql(dbName);
executeSql(createDbSql, url, user, password);
}
}
}
/**
* Create PostgreSQL table
*/
public static void createTable(String url, String user, String password, PostgreSQLTableInfo tableInfo)
throws Exception {
String createTableSql = PostgreSQLSqlBuilder.buildCreateTableSql(tableInfo);
PostgreSQLJdbcUtils.executeSql(createTableSql, url, user, password);
}
/**
* Get PostgreSQL tables from the PostgreSQL metadata
*/
public static boolean checkTablesExist(String url, String user, String password, String dbName, String tableName)
throws Exception {
boolean result = false;
try (Connection conn = getConnection(url, user, password)) {
DatabaseMetaData metaData = conn.getMetaData();
ResultSet rs = metaData.getTables(conn.getCatalog(), SCHEMA_PATTERN, tableName, new String[]{TABLE_TYPE});
if (rs != null) {
rs.next();
result = rs.getRow() > 0 && tableName.equals(rs.getString(COLUMN_LABEL_TABLE));
LOG.info("check table exist for db={} table={}, result={}", dbName, tableName, result);
}
}
return result;
}
/**
* Query PostgreSQL columns
*/
public static List<PostgreSQLColumnInfo> getColumns(String url, String user, String password, String tableName)
throws Exception {
String querySql = PostgreSQLSqlBuilder.buildDescTableSql(tableName);
try (Connection conn = getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(querySql)) {
List<PostgreSQLColumnInfo> columnList = new ArrayList<>();
while (rs.next()) {
PostgreSQLColumnInfo columnInfo = new PostgreSQLColumnInfo();
columnInfo.setName(rs.getString(1));
columnInfo.setType(rs.getString(2));
columnList.add(columnInfo);
}
return columnList;
}
}
/**
* Add columns for PostgreSQL table
*/
public static void addColumns(String url, String user, String password, String tableName,
List<PostgreSQLColumnInfo> columnList) throws Exception {
List<String> addColumnSql = PostgreSQLSqlBuilder.buildAddColumnsSql(tableName, columnList);
PostgreSQLJdbcUtils.executeSqlBatch(addColumnSql, url, user, password);
}
}
拼接sql的代码如下：
public class PostgreSQLSqlBuilder {
private static final Logger LOGGER = LoggerFactory.getLogger(PostgreSQLSqlBuilder.class);
/**
* Build check database exists SQL
*/
public static String getCheckDatabase(String dbName) {
String sql = "SELECT datname FROM from pg_catalog.pg_database WHERE datname = '" + dbName + "'";
LOGGER.info("check database sql: {}", sql);
return sql;
}
/**
* Build create database SQL
*/
public static String buildCreateDbSql(String dbName) {
String sql = "CREATE DATABASE " + dbName;
LOGGER.info("create db sql: {}", sql);
return sql;
}
/**
* Build create table SQL
*/
public static String buildCreateTableSql(PostgreSQLTableInfo table) {
StringBuilder sql = new StringBuilder();
// Support _ beginning with underscore
String dbTableName = table.getTableName();
sql.append("CREATE TABLE ").append(dbTableName);
// Construct columns and partition columns
sql.append(buildCreateColumnsSql(table.getColumns()));
LOGGER.info("create table sql: {}", sql);
return sql.toString();
}
/**
* Build add column SQL
*/
public static List<String> buildAddColumnsSql(String tableName, List<PostgreSQLColumnInfo> columnList) {
List<String> columnInfoList = getColumnsInfo(columnList);
List<String> resultList = new ArrayList<>();
for (String columnInfo : columnInfoList) {
String sql = "ALTER TABLE " + tableName + " ADD COLUMN " + columnInfo;
resultList.add(sql);
}
LOGGER.info("add columns sql={}", resultList);
return resultList;
}
/**
* Build create column SQL
*/
private static String buildCreateColumnsSql(List<PostgreSQLColumnInfo> columns) {
List<String> columnList = getColumnsInfo(columns);
String sql = " (" + StringUtils.join(columnList, ",") + ") ";
LOGGER.info("create columns sql={}", sql);
return sql;
}
/**
* Build column info
*/
private static List<String> getColumnsInfo(List<PostgreSQLColumnInfo> columns) {
List<String> columnList = new ArrayList<>();
for (PostgreSQLColumnInfo columnInfo : columns) {
// Construct columns and partition columns
String columnStr = columnInfo.getName() + " " + columnInfo.getType();
columnList.add(columnStr);
}
return columnList;
}
/**
* Build query table SQL
*/
public static String buildDescTableSql(String tableName) {
StringBuilder sql = new StringBuilder();
sql.append("SELECT att.attname as filedName, format_type(att.atttypid, att.atttypmod) as filedType"
+ " FROM pg_attribute as att, pg_class as clz"
+ " WHERE att.attrelid = clz.oid and att.attnum > 0 and clz.relname = '")
.append(tableName)
.append("';");
LOGGER.info("desc table sql={}", sql);
return sql.toString();
}
}
在上述的代码中，存在不少问题。首先可以通过单元测试代码进行验证，单元测试代码如下：
@Test
public void testDbResource() {
String url = "jdbc:postgresql://192.168.162.136:5432/testdb";
String user = "jie_li";
String password = "123456";
String dbName = "testdb";
String tableName = "test001";
try {
PostgreSQLJdbcUtils.createDb(url, user, password, dbName);
List<PostgreSQLColumnInfo> columnInfoList = new ArrayList<>();
PostgreSQLColumnInfo info = new PostgreSQLColumnInfo();
info.setType("integer");
info.setName("id");
columnInfoList.add(info);
PostgreSQLColumnInfo info2 = new PostgreSQLColumnInfo();
info2.setType("integer");
info2.setName("age");
columnInfoList.add(info2);
PostgreSQLColumnInfo info3 = new PostgreSQLColumnInfo();
info3.setType("integer");
info3.setName("high");
columnInfoList.add(info3);
PostgreSQLTableInfo tableInfo = new PostgreSQLTableInfo();
tableInfo.setDbName(dbName);
tableInfo.setColumns(columnInfoList);
tableInfo.setTableName(tableName);
boolean tableExists = PostgreSQLJdbcUtils.checkTablesExist(url, user, password, dbName, tableName);
if (!tableExists) {
PostgreSQLJdbcUtils.createTable(url, user, password, tableInfo);
} else {
List<PostgreSQLColumnInfo> existColumns = PostgreSQLJdbcUtils.getColumns(url, user, password,
tableName);
List<String> columnNameList = new ArrayList<>();
existColumns.forEach(columnInfo -> columnNameList.add(columnInfo.getName()));
List<PostgreSQLColumnInfo> needAddColumns = tableInfo.getColumns().stream()
.filter((columnInfo) -> !columnNameList.contains(columnInfo.getName()))
.collect(Collectors.toList());
if (CollectionUtils.isNotEmpty(needAddColumns)) {
PostgreSQLJdbcUtils.addColumns(url, user, password, tableName, needAddColumns);
}
}
} catch (Exception e) {
// print to local console
e.printStackTrace();
}
}
2. 发现的问题
2.1 检查表是否存在sql拼接错误
执行单元测试案例，出现如下异常：
23:20:44.761 - INFO [ main] .m.s.r.s.p.PostgreSQLJdbcUtils:94 - execute sql [SELECT datname FROM from pg_catalog.pg_database WHERE datname = 'testdb'] success for url: jdbc:postgresql://192.168.162.136:5432/testdb
org.postgresql.util.PSQLException: ERROR: syntax error at or near "from"
位置：21
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:243)
at org.apache.inlong.manager.service.resource.sink.postgresql.PostgreSQLJdbcUtils.executeQuerySql(PostgreSQLJdbcUtils.java:95)
at org.apache.inlong.manager.service.resource.sink.postgresql.PostgreSQLJdbcUtils.createDb(PostgreSQLJdbcUtils.java:118)
at org.apache.inlong.manager.service.sink.PostgreSQLSinkServiceTest.testDbResource(PostgreSQLSinkServiceTest.java:120)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at java.util.ArrayList.forEach(ArrayList.java:1259)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at java.util.ArrayList.forEach(ArrayList.java:1259)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
首先可以看出，jdbc拼接的sql中，在方法createDb的executeQuerySql方法的时候，出现了异常。getCheckDatabase方法出现了两个from:
修复这个问题非常简单，就是删除多余的from。
2.2 ResultSet 中找不到栏位名称 count
2022-08-07 23:25:48.464 - INFO [ main] .m.s.r.s.p.PostgreSQLJdbcUtils:94 - execute sql [SELECT datname FROM pg_catalog.pg_database WHERE datname = 'testdb'] success for url: jdbc:postgresql://192.168.162.136:5432/testdb
org.postgresql.util.PSQLException: ResultSet 中找不到栏位名称 count。
at org.postgresql.jdbc.PgResultSet.findColumn(PgResultSet.java:2957)
at org.postgresql.jdbc.PgResultSet.getInt(PgResultSet.java:2835)
at org.apache.inlong.manager.service.resource.sink.postgresql.PostgreSQLJdbcUtils.createDb(PostgreSQLJdbcUtils.java:121)
at org.apache.inlong.manager.service.sink.PostgreSQLSinkServiceTest.testDbResource(PostgreSQLSinkServiceTest.java:120)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)
at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:210)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:66)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:151)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at java.util.ArrayList.forEach(ArrayList.java:1259)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at java.util.ArrayList.forEach(ArrayList.java:1259)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:41)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$6(NodeTestTask.java:155)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:141)
at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$9(NodeTestTask.java:139)
at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:138)
at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:95)
at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:35)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:107)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67)
at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:114)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:86)
at org.junit.platform.launcher.core.DefaultLauncherSession$DelegatingLauncher.execute(DefaultLauncherSession.java:86)
at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:53)
at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
2022-08-07 23:25:48.525 - INFO [SpringApplicationShutdownHook] c.a.d.p.DruidDataSource :2043 - {dataSource-1} closing ...
2022-08-07 23:25:48.541 - INFO [SpringApplicationShutdownHook] c.a.d.p.DruidDataSource :2116 - {dataSource-1} closed
Process finished with exit code 0
在上述代码中，查询的结果集内容中不存在count，由于sql语句中查询的是dbname ,因此在判断ResultSet的过程中存在错误。
此处修改过程也简单，不需要判断返回的count，直接判断是否存在返回结果即可,修复之后的代码如下:
public static void createDb(String url, String user, String password, String dbName) throws Exception {
String checkDbSql = PostgreSQLSqlBuilder.getCheckDatabase(dbName);
ResultSet resultSet = executeQuerySql(checkDbSql, url, user, password);
if(Objects.nonNull(resultSet) && resultSet.next()) {
LOG.info("The database [{}] are exists", dbName);
}else {
String createDbSql = PostgreSQLSqlBuilder.buildCreateDbSql(dbName);
executeSql(createDbSql, url, user, password);
}
}
改为上述代码，即可。
2.3 创建DB权限问题
需要注意的是，如果需要创建数据库，在PostgreSQL中，可能存在用户并没有创建DB权限的问题。
PG的数据库连接字符串必须带DB名，如果创建其他DB，可能会出现问题。而schema在PG中是对同一个库的不同管理手段。因此这个地方可能需要优化为创建schema。而DB默认在连接字符串中，创建DB没有意义。
2.4 连接复用问题
当上述代码执行成功之后，日志如下：
2022-08-07 23:44:53.694 - INFO [ main] m.s.r.s.p.PostgreSQLSqlBuilder:41 - check database sql: SELECT datname FROM pg_catalog.pg_database WHERE datname = 'testdb'
2022-08-07 23:44:53.811 - INFO [ main] .m.s.r.s.p.PostgreSQLJdbcUtils:69 - get postgresql connection success, url=jdbc:postgresql://192.168.162.136:5432/testdb
2022-08-07 23:44:53.811 - INFO [ main] .m.s.r.s.p.PostgreSQLJdbcUtils:95 - execute sql [SELECT datname FROM pg_catalog.pg_database WHERE datname = 'testdb'] success for url: jdbc:postgresql://192.168.162.136:5432/testdb
2022-08-07 23:44:53.843 - INFO [ main] .m.s.r.s.p.PostgreSQLJdbcUtils:121 - The database [testdb] are exists
2022-08-07 23:44:53.875 - INFO [ main] .m.s.r.s.p.PostgreSQLJdbcUtils:69 - get postgresql connection success, url=jdbc:postgresql://192.168.162.136:5432/testdb
2022-08-07 23:44:54.117 - INFO [ main] .m.s.r.s.p.PostgreSQLJdbcUtils:149 - check table exist for db=testdb table=test001, result=true
2022-08-07 23:44:54.117 - INFO [ main] m.s.r.s.p.PostgreSQLSqlBuilder:119 - desc table sql=SELECT att.attname as filedName, format_type(att.atttypid, att.atttypmod) as filedType FROM pg_attribute as att, pg_class as clz WHERE att.attrelid = clz.oid and att.attnum > 0 and clz.relname = 'test001';
2022-08-07 23:44:54.150 - INFO [ main] .m.s.r.s.p.PostgreSQLJdbcUtils:69 - get postgresql connection success, url=jdbc:postgresql://192.168.162.136:5432/testdb
2022-08-07 23:44:54.549 - INFO [SpringApplicationShutdownHook] c.a.d.p.DruidDataSource :2043 - {dataSource-1} closing ...
2022-08-07 23:44:54.549 - INFO [SpringApplicationShutdownHook] c.a.d.p.DruidDataSource :2116 - {dataSource-1} closed
可以看到，数据库连接在使用的过程中，创建了至少三次。由于JDBC的数据库连接也是非常宝贵的资源。因此数据库连接是可以复用的。应该只创建一次连接，后面的操作复用，当连接使用完毕之后，将连接回收。
