当前位置：网站首页>Optimization of a piece of JDBC code (Part 1)
Optimization of a piece of JDBC code (Part 1)
2022-08-09 18:21:00 【A lazy cat in the winter】
携手创作,共同成长！这是我参与「掘金日新计划 · 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();
}
}
复制代码在上述的代码中,存在不少问题.The first can be verified by unit testing the code,单元测试代码如下：
@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拼接错误
Execute unit test cases,出现如下异常：
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方法的时候,出现了异常.getCheckDatabaseThere are two methodsfrom:
2.2 ResultSet The field name could not be found in 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 The field name could not be found in 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
复制代码
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);
}
}
复制代码Change to the above code,即可.
2.3 创建DB权限问题
需要注意的是,如果需要创建数据库,在PostgreSQL中,There may be users that were not createdDB权限的问题. 
2.4 连接复用问题
After the above code is executed successfully,日志如下：
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
复制代码可以看到,The database connection is in use,Created at least three times.由于JDBCA database connection is also an invaluable resource.So database connections are reusable.The connection should only be created once,The subsequent operations are reused,After the connection is used up,recycle the connection.
边栏推荐
猜你喜欢
3种特征分箱方法！
一.字符 字符串 指针字符
5G NR Paging
2022钉钉杯A题思路及代码：银行卡电信诈骗危险预测
【燃】是时候展现真正的实力了！一文看懂2022华为开发者大赛技术亮点
ESP8266-Arduino编程实例-MQ-5液化天然气传感器驱动
中科院打脸谷歌：普通电脑追上量子优越性，几小时搞定原本要一万年的计算...
The use of websocket in uni-app Disconnection, reconnection, heartbeat mechanism
Apple Developer Account Apply for D-U-N-S Number
推荐一些面向 Web 开发者的杀手级网站
随机推荐
动态内存管理，触及本质的最详解析
网络——涉及的相关协议和设备汇总
六.数组越界问题引出对栈区内存的探索
Smart Light Pole Gateway Smart Transportation Application
机器学习强基计划1-2：图文详解线性回归与局部加权线性回归+房价预测实例
MySQL 5.5系列安装步骤教程(图解版)
[1413. Stepwise summation to get the minimum value of positive numbers]
利用C＃传输Json数据
【Web渗透】信息收集篇——Google搜索引擎（二）
网络——IPV4地址（三）
Access Characteristics of Constructor under Inheritance Relationship
Chapter 3: Use of GEE Data (3.4-3.11)
巧用Prometheus来扩展kubernetes调度器
如何让button中的内容分两行显示
MySQL进阶学习
How to create DataFrame with feature importance from XGBClassifier made by GridSearchCV?
网络——流量控制＆可靠传输＆滑动窗口
使用SourceTree添加SSH公钥并克隆码云项目（笔记整理篇）
Nacos注册中心 Feign远程调用 Gateway服务网关
1. Introducing GEE and Geemap