Pages

Friday, 17 January 2014

Stripping Java string SQL statements automatically

Do you ever get sick of typing out an SQL statement in Java with all the String syntax?

Then, when the statement doesn't work, you have to copy it back into pure SQL to run it to check it works?

This was a pretty common problem for me, so I made a simple syntax stripper http://idavid.co.uk/sqlstripper/ so I could quickly covert from SQL into a Java string and back again.  Consider the following Java String...

String query = "SELECT * " +
"   FROM USERS u" +

"INNER JOIN CODE cd ON cd.userid = u.id " +
"WHERE u.id in ('1','2') ";

This would parse fine if it was being sent into stmt.executeQuery(query)

But it I wanted to run this on SQL server just to see what it produced, I'd have to manually remove all the syntax etc to make it run and generate the following SQL.

SELECT *
FROM USERS u
INNER JOIN CODE cd ON cd.userid = u.id
WHERE u.id in ('1','2')

This is exactly what my SQL stripper does.  It also gives you the ability to paste in an SQL statement into the right hand textbox and convert it into a nicely formatted Java String, so converting the SQL statement above would give...

"SELECT *"
+ " FROM USERS u"
+ " INNER JOIN CODE cd ON cd.userid = u.id"
+ " WHERE u.id in ('1','2')";