Replace the variables in .sql scripts and run them using Java JDBC

Question

I have been working with Java JDBC (mainly in desktop applications) for a long time, and I understand that many of the operations that I perform can be improved and simplified. For instance:

• Coded SQL statements directly in Java are not very practical.

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

• Running multiple update statements at once will be very cool.

In an attempt to improve JDBC, I decided to write my own tool, but before I invent the wheel, I would like to know if there is any Java utility capable of:

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

• Define variables in these scenarios, preferably with real names, rather than using ?? character.

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

• Run multiple update statements in a single method call. This may allow me, for example, to run DDL and DML scripts to initialize the databases.

I know about JDBC ScriptRunner , but this is not complete enough. Is there something better there? I honestly believe that a tool like this will be very useful.

Note. I do not mind importing lib.

Answer 1

I use Warework only for this. This is a big JAR, but I think it does what you are looking for. I will 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 directory "/ META-INF / system" create a file called "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 parameter values ​​with the ones you need to connect to your database. Keep connect-on-create equal to true.

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

find-user.sql

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

and UPDATE like this (one or more statements for each 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 to the database, do the following:

 // "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 the SELECT statement from the .sql file as follows:

 // 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 the UPDATE statements from the .sql file as follows:

 // 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 join / disconnect, commit, rollback, ... You can also run statements directly from String objects.

Answer 2

About the named parameters, here are some solutions I found around

• http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html?page=1
• Named Parameters in JDBC

Retrieving statements from a script is a fairly simple task (but not trivial) if you restrict yourself to SELECT / UPDATE / INSERT / DELETE statements. Just split into any ";" A character that is not between the two "" characters. This becomes a more complex parsing problem if you also allow CREATE TRIGGERS or CREATE PROCEDURE, etc. In your script, since they have a ";" as part of their syntax.

I personally find it safer to leave DDL out of the equation and manage database creation with some external tool like Liquibase