Dynamic SQL Java library

Question

We need to generate dynamic SQL in our Java app. Does anyone know a simple library to do this?

In our Java app we have a bunch of where clause filter criteria (database column, operand, value). In other words, we could have a Date instance that we need to use to filter a given datetime Oracle column, a String instance that we need to use to query a varchar2 column, an Integer instance for a number column, etc.

Is there a library that will take a base query (select a,b,c from d) and some filter criteria and output sql in a valid format? By valid format, I'd mean that the String filters would be surrounded by quotes, the Integer values wouldn't be surrounded by quotes, the Date values formatted using to_date, etc. Something like this:

select a, b, c from table --base query
where
date_col > to_date('2003/07/09 13:15', 'yyyy/mm/dd HH24:MM') and --Date 
int_col = 5 and --Integer
string_col = 'abc'

We weren't planning on using PreparedStatement-s as it seems like this would be harder to use to generate dynamic sql.

---

Update

Thanks for the help all. I decided to write my own util class to accomplish this. And yes I'm using binding via PreparedStatements!

I accepted Kjetil's answer as Squiggle seems to be a good little library that you could use (although I didn't actually test this out).

Answer 1

EDIT

I would recommend going the framework route -- Hibernate, et. al -- if possible. You'll gain much more in productivity from using the framework and will be able to take advantage of the baked in security. I realize that this may be a big change and not possible. If so, building the queries dynamically, but using prepared statements to do it might be the easiest way to change your existing code.

Original

Pre-OP edit to remove statement about SQL injection

You do need to worry about SQL injection attacks with all types of queries. If you accept and pass on user input to your query, the user can simply insert the appropriate character sequence to introduce a SQL statement termination, a new SQL command of their own choosing, and comment out the rest of the line. Your choice of statement doesn't affect how the attack is carried out; your choice of how the statement is constructed, however, does.

Use prepared statements. Build your where clause dynamically, if you must, but build it with parameters embedded and pass in the user input to the parameters instead of appending it as strings. Using parameters will help protect you from SQL injection attacks.

Answer 2

Assuming that you don't want to go the Hibernate/iBatis/framework route, you could take a look at OpenHMS SQLBuilder (disclosure: I'm a member of this project, but have not edited nor used SQLBuilder).

Regarding PreparedStatements: yes, you want to use them, even for dynamically generated SQL, for the simple reason that they eliminate all concerns about quoting or format conversions.

Answer 3

If you don't want to use prepared statements, and you're using Oracle, you could use Oracle's PL/SQL Native Dynamic SQL

You still need to worry about SQL injection. Somebody could read arbitrary data from your database if you don't worry about it.

Answer 4

You don't want a library that "wraps strings in quotes." That's the surest way to bugs, security holes, and inefficiency. Whatever you choose, you want it to use bind parameters.

I believe I saw a Stack Overflow question looking for a library like what you suggest...

Answer 5

Squiggle is a library for dynamically generating SELECT statements.

Answer 6

The JDBC framework built into Spring is brilliant. If you don't have a robust object model, and want to stay on the SQL side of things, I'd recommend it. The DataValidation classes are well-done, as is everything in Spring. There's an attention to detail that you'll appreciate.

Answer 7

You are making the biggest mistake you can make with Oracle.

Don't do things in Java code like where name = 'John' . Use parameters instead of string concatenating, bind your variables!!!

Use concatenating makes Oracle much slower (sometimes 10 times). Especially in multi concurrency situations: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7832114438832#19249485268976 and http://awads.net/wp/2008/03/09/oracle-sql-and-plsql-bad-practices-document/ .

Read about the Oracle's library cache and Oracle's query executer.

Answer 8

Ibatis has a lot of functionality for generating dynamic SQL.