4

I've been working with Java JDBC (mainly in desktop apps) for a long time and I'm realizing that many operations that I perform could be improved and simplified. For example:

  • Hardcoding SQL statements directly into Java is not very practical.

  • Replacing variables with "?" in JDBC is nice but it would be much better to use real variable names like "USER-NAME" or something like that.

  • Executing multiple update statements at once would be very cool.

In an effort to improve JDBC, I decided to write my own tool but before I reinvent the wheel I would like to know if there is any Java utility that is able to:

  • Read and execute .sql scripts, preferably stored inside the JAR of my application.

  • Define variables in these scripts, preferably with real names and not with the "?" character.

  • Run query (SELECT) and update (CREATE, INSERT, DELETE, ...) statements from these scripts.

  • Execute multiple update statements in one single method call. This could let me, for example, run DDL and DML scripts to initialize databases.

I know about JDBC ScriptRunner but it's not complete enough. Is there something better out there? I honestly think such a tool like this would be very useful.

Note: I don't mind to import a lib.

Oompa Loompa
  • 41
  • 1
  • 3

2 Answers2

3

I use Warework just for that. It's a big JAR but I think it does what you're looking for. I'll show you how it works:

1- Create this directory structure in the source folder of your project:

/META-INF/system/statement/sql

2- In the "/META-INF/system" directory, create a file named "pool-service.xml" with this content:

<?xml version="1.0" encoding="UTF-8"?> 
<proxy-service xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://repository.warework.com/xsd/proxyservice-1.0.0.xsd"> 
 <clients> 
  <client name="c3p0-client" connector="com.warework.service.pool.client.connector.C3P0Connector"> 
   <parameter name="driver-class" value="com.mysql.jdbc.Driver" /> 
   <parameter name="jdbc-url" value="jdbc:mysql://host:port/database-name" /> 
   <parameter name="user" value="the-user-name" /> 
   <parameter name="password" value="the-password" /> 
   <parameter name="connect-on-create" value="true" /> 
  </client> 
 </clients> 
</proxy-service>

In this file, replace the values of the parameters with the ones you need to connect with your database. Keep "connect-on-create" equals to "true".

3- Write your .sql scripts in the "/META-INF/system/statement/sql" directory. You can write SELECT statements like this (one statement per file):

find-user.sql

SELECT * FROM HOME_USERS A WHERE A.ID = ${USER_ID}

and UPDATE statements like this (one or multiple statements per file):

create-user.sql

INSERT INTO HOME_USERS (ID, NAME) VALUES (${USER_ID}, ${USER_NAME});
INSERT INTO ACTIVE_USERS (ID) VALUES (${USER_ID});

4- To connect with the database, perform this:

// "Test.class" must be any class of your project (the same project where /META-INF/system directory exists).
// Do not change "full" and "relational-database" strings.
// If you change "system" for "test", then the directory will be /META-INF/test.
RDBMSView ddbb = (RDBMSView) ScopeFactory.createTemplate(Test.class, "full", "system").getObject("relational-database");

// Connect with the database.
ddbb.connect();

5- Run a SELECT statement from a .sql file like this: 

// Values for variables in the SELECT statement. 
Hashtable values = new Hashtable(); 

// Set variables to filter the query. 
values.put("USER_ID", new Integer(8375)); 

// Read '/META-INF/system/statement/sql/find-user.sql', replace variables and run. 
// -1 values are for pagination (first -1 is the page, second -1 is the max rows per page).
ResultSet result = (ResultSet) ddbb.executeQueryByName("find-user", values, -1, -1);

6- Run UPDATE statements from a .sql file like this: 

// Values for variables in the UPDATE statements. 
Hashtable values = new Hashtable(); 

// Set variables for the update statement. 
values.put("USER_ID", new Integer(3));
values.put("USER_NAME", "Oompa Loompa");

// Read '/META-INF/system/statement/sql/create-user.sql', replace variables and run. 
// ';' is the character that separates each statement.
ddbb.executeUpdateByName("create-user", values, new Character(';'));

The RDBMSView class provides these methods plus connect/disconnect, commit, rollback, ... You can also run statements directly from String objects.

Chafi
  • 33
  • 3
1

About named parameters here are a couple of solutions I found around

Extracting statements from a script is a quite simple task (but not trivial) if you limit yourself to SELECT/UPDATE/INSERT/DELETE statements. Just split at any ";" character wich isn't between two "'" characters. It becomes a more complex parsing problem if you admit also CREATE TRIGGERS or CREATE PROCEDURE etc. to your script since they have have ";" as part of their sintax.

I personally think it's safer to keep DDL out of the equation and manage database creation with some external tool like Liquibase

Community
  • 1
  • 1
Maxx
  • 1,443
  • 4
  • 22
  • 30