Good way to generate SQL strings in java?

Question

I'm not looking for a persistence layer like Hibernate, I just want to generate SQL-strings and they should be compatible with PreparedStatement. I've tried libraries such as Squiggle, but it only supports SELECT, I would also like to generate insert and updates. An ideal usage would be something like:

generateInsertOn("myTable").addValue("value1").addValue("value2").generate();

that would generate this string:

"INSERT INTO myTable (value1, value2) VALUES(?, ?)"

I know that there exists questions that are a lot like mine, such as this, but they don't quite ask the same thing as I do.

Answer 1

For arbitrary SQL, use jOOQ. jOOQ currently supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE, and MERGE. You can create SQL like this:

// Since you're not executing the SQL, set connection to null
Connection connection = null;
Factory create = new MySQLFactory(connection);
String sql1 = create.select(A, B, C)
                    .from(MY_TABLE)
                    .where(A.equal(5))
                    .and(B.greaterThan(8))
                    .getSQL();

String sql2 = create.insertInto(MY_TABLE)
                    .values(A, 1)
                    .values(B, 2)
                    .getSQL();

String sql3 = create.update(MY_TABLE)
                    .set(A, 1)
                    .set(B, 2)
                    .where(C.greaterThan(5))
                    .getSQL();

The supported syntax is quite rich. You will also find support for clauses such as ON DUPLICATE KEY UPDATE, FOR UPDATE, LOCK IN SHARE MODE, etc.

For more details, see

http://www.jooq.org

(Disclaimer, I work for the company behind jOOQ)

Answer 2

You should definitively take a look at SQLBuilder. It allows simple, yet complete, SQL generation using a very fluent API.