Throughout the Sql2Java generated code, PreparedStatements are used rather than trying to encode the data into the SQL. This is a good thing(TM).
There is one place however where this is not done, and that is the where clause strings that can be passed into some of the *Manager.java methods.
I was working with a project that passed where clauses around like this, and it did cause some problems, so I changed it to have an object which represented the additional where clause (lets call it DynSql for the moment) to which SQL and arguements could be added. The SQL, with embedded ? place markers, is held in a StringBuffer, and the place markers in an ArrayList<Object>. There are then methods for setting the sql and arg list, and adding new components. There is also a method for appending one DynSql object onto another. Finally there are two methods to convert the object into the String to be put into the SQL, and a method for setting the parameters into a PreparedStatement. The getSql() method also makes sure that the clause is surrounded by whitespace so that it does not get the syntax confused.
So you end up with code that looks like:-
DynSql ds = new DynSql("field = ?", fieldValue);
....
PreparedStatement stmt = connection.prepare("SELECT fielda, fieldb "+
"FROM table WHERE fieldc = ? AND" +
ds.getSql() + "AND fieldd = ? ORDER BY fred");
stmt.setString(1, fieldc);
int param = ds.addParams(stmt, 2);
stmt.setInt(param++, fieldd);
If it would be felt useful by others, I would be happy to contribute the code to Sql2Java. The changes needed in the templates to use this
would be fairly trivial.
David
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Throughout the Sql2Java generated code, PreparedStatements are used rather than trying to encode the data into the SQL. This is a good thing(TM).
There is one place however where this is not done, and that is the where clause strings that can be passed into some of the *Manager.java methods.
I was working with a project that passed where clauses around like this, and it did cause some problems, so I changed it to have an object which represented the additional where clause (lets call it DynSql for the moment) to which SQL and arguements could be added. The SQL, with embedded ? place markers, is held in a StringBuffer, and the place markers in an ArrayList<Object>. There are then methods for setting the sql and arg list, and adding new components. There is also a method for appending one DynSql object onto another. Finally there are two methods to convert the object into the String to be put into the SQL, and a method for setting the parameters into a PreparedStatement. The getSql() method also makes sure that the clause is surrounded by whitespace so that it does not get the syntax confused.
So you end up with code that looks like:-
DynSql ds = new DynSql("field = ?", fieldValue);
....
PreparedStatement stmt = connection.prepare("SELECT fielda, fieldb "+
"FROM table WHERE fieldc = ? AND" +
ds.getSql() + "AND fieldd = ? ORDER BY fred");
stmt.setString(1, fieldc);
int param = ds.addParams(stmt, 2);
stmt.setInt(param++, fieldd);
If it would be felt useful by others, I would be happy to contribute the code to Sql2Java. The changes needed in the templates to use this
would be fairly trivial.
David