Skip to content

Commit 37d09b0

Browse files
pborissowpborissow
committed
Added new properties when connecting to an H2 database and minor code cleanup in DbUtils
1 parent 05c09dd commit 37d09b0

File tree

1 file changed

+37
-46
lines changed

1 file changed

+37
-46
lines changed

‎src/javaxt/express/utils/DbUtils.java‎

Lines changed: 37 additions & 46 deletions
Original file line numberDiff line numberDiff line change
@@ -49,10 +49,10 @@ public static boolean initSchema(Database database, String schema, String tableS
4949
boolean schemaInitialized = false;
5050

5151
//Split schema into individual statements
52-
ArrayList<String> statements = new ArrayList<String>();
52+
ArrayList<String> statements = new ArrayList<>();
5353
for (String s : schema.split(";")){
5454

55-
StringBuffer str = new StringBuffer();
55+
StringBuilder str = new StringBuilder();
5656
for (String i : s.split("\r\n")){
5757
if (!i.trim().startsWith("--") && !i.trim().startsWith("COMMENT ")){
5858
str.append(i + "\r\n");
@@ -68,14 +68,18 @@ public static boolean initSchema(Database database, String schema, String tableS
6868

6969

7070
//Create database
71-
if (database.getDriver().equals("H2")){
71+
Driver driver = database.getDriver();
72+
if (driver.equals("H2")){
7273

7374
javaxt.io.File db = new javaxt.io.File(database.getHost() + ".mv.db");
7475
if (!db.exists()){
7576

7677
//Set H2 to PostgreSQL mode
77-
java.util.Properties properties = new java.util.Properties();
78+
java.util.Properties properties = database.getProperties();
79+
if (properties==null) properties = new java.util.Properties();
7880
properties.setProperty("MODE", "PostgreSQL");
81+
properties.setProperty("DATABASE_TO_LOWER", "TRUE");
82+
properties.setProperty("DEFAULT_NULL_ORDERING", "HIGH");
7983
database.setProperties(properties);
8084

8185

@@ -84,7 +88,7 @@ public static boolean initSchema(Database database, String schema, String tableS
8488
String str = statement.trim().toUpperCase();
8589
if (arr==null){
8690
if (str.startsWith("CREATE TABLE") || str.startsWith("CREATE SCHEMA")){
87-
arr = new ArrayList<String>();
91+
arr = new ArrayList<>();
8892
}
8993
}
9094

@@ -110,40 +114,36 @@ public static boolean initSchema(Database database, String schema, String tableS
110114
}
111115

112116

113-
Connection conn = null;
114-
try{
115-
conn = database.getConnection();
117+
118+
try (Connection conn = database.getConnection()){
116119
conn.execute("CREATE domain IF NOT EXISTS text AS varchar");
117120
conn.execute("CREATE domain IF NOT EXISTS jsonb AS varchar");
118121
schemaInitialized = initSchema(arr, conn);
119-
conn.close();
120122
}
121-
catch(java.sql.SQLException e){
122-
if (conn!=null) conn.close();
123+
catch(Exception e){
124+
e.printStackTrace();
123125
String fileName = db.getName();
124126
fileName = fileName.substring(0, fileName.indexOf("."));
125127
for (javaxt.io.File file : db.getParentDirectory().getFiles(fileName + ".*.db")){
126128
file.delete();
127129
}
128-
throw new Exception(e.getMessage());
130+
throw e;
129131
}
130-
131132
}
132-
133133
}
134-
else if (database.getDriver().equals("PostgreSQL")){
134+
else if (driver.equals("PostgreSQL")){
135135

136136
//Connect to the database
137-
Connection conn = null;
137+
Connection conn;
138138
try{ conn = database.getConnection(); }
139139
catch(Exception e){
140140

141-
//Try to connect to the postgres database
141+
//Try to connect a new database. First, we'll try to connect to
142+
//a database called "postgres" on the PostgreSQL server. This is
143+
//the default database in most installations.
142144
Database db = database.clone();
143145
db.setName("postgres");
144-
Connection c2 = null;
145-
try{
146-
c2 = db.getConnection();
146+
try (Connection c2 = db.getConnection()) {
147147

148148

149149
//Check if database exists
@@ -161,22 +161,19 @@ else if (database.getDriver().equals("PostgreSQL")){
161161
c2.execute("CREATE DATABASE " + database.getName());
162162
}
163163

164-
c2.close();
165-
166-
167-
conn = database.getConnection();
168164
}
169165
catch(Exception ex){
170166
ex.printStackTrace();
171-
if (c2!=null) c2.close();
172167
throw new Exception("Failed to connect to the database");
173168
}
169+
170+
conn = database.getConnection();
174171
}
175172

176173

177174

178175
//Generate list of SQL statements
179-
ArrayList<String> arr = new ArrayList<String>();
176+
ArrayList<String> arr = new ArrayList<>();
180177
if (tableSpace!=null) arr.add("SET default_tablespace = " + tableSpace + ";");
181178
for (int i=0; i<statements.size(); i++){
182179
String statement = statements.get(i);
@@ -268,19 +265,19 @@ private static boolean initSchema(ArrayList<String> statements, Connection conn)
268265

269266

270267
//Execute statments
271-
java.sql.Statement stmt = conn.getConnection().createStatement();
272-
for (String cmd : statements){
273-
//String tableName = getTableName(cmd);
274-
//if (tableName!=null) console.log(tableName);
275-
try{
276-
stmt.execute(cmd);
277-
}
278-
catch(java.sql.SQLException e){
279-
System.out.println(cmd);
280-
throw e;
268+
try (java.sql.Statement stmt = conn.getConnection().createStatement()){
269+
for (String cmd : statements){
270+
//String tableName = getTableName(cmd);
271+
//if (tableName!=null) console.log(tableName);
272+
try{
273+
stmt.execute(cmd);
274+
}
275+
catch(java.sql.SQLException e){
276+
System.out.println(cmd);
277+
throw e;
278+
}
281279
}
282280
}
283-
stmt.close();
284281
return true;
285282
}
286283

@@ -408,23 +405,20 @@ public static void copyTable(String tableName, String where, Database sourceDB,
408405

409406

410407
//Get min/max row ID
411-
Connection conn = null;
412-
try{
413-
conn = sourceDB.getConnection();
408+
409+
try (Connection conn = sourceDB.getConnection()) {
414410
Recordset rs = new Recordset();
415411
rs.open("select min(id), max(id) from " + t + (where==null ? "" : " where " + where), conn);
416412
if (!rs.EOF){
417413
minID = rs.getValue(0).toLong();
418414
maxID = rs.getValue(1).toLong();
419415
}
420416
rs.close();
421-
conn.close();
422417

423418
//Long id = getLastRowID(tableName, where, destDB);
424419
//if (id!=null && id>minID) minID = id;
425420
}
426421
catch(Exception e){
427-
if (conn!=null) conn.close();
428422
e.printStackTrace();
429423
}
430424

@@ -493,13 +487,10 @@ public void run() {
493487

494488
//Update sequence
495489
if (destDB.getDriver().equals("PostgreSQL")){
496-
try{
497-
conn = destDB.getConnection();
490+
try (Connection conn = destDB.getConnection()) {
498491
conn.execute("SELECT setval('" + tableName + "_id_seq', (SELECT MAX(id) FROM " + t + "));");
499-
conn.close();
500492
}
501493
catch(Exception e){
502-
if (conn!=null) conn.close();
503494
e.printStackTrace();
504495
}
505496
}

0 commit comments

Comments
 (0)