What's the best practice for nesting PreparedStatements?
I have several cases where the legacy sql statements section is dependency based. eg.
if (x !=null)
{
SQL = "SELECT z WHERE x > y";
}
else
{
SQL = "SELECT z WHERE x <= y";
}
SQL2 = SQL + " JOIN a ON b";
I am creating PreparedStatements from this old code. Which is the best practice here. Should I create a PreparedStatement for var SQL and nest it in SQL2, should there be multiple SQL2 based PreparedStatements without nesting, or something else entirely?
The code is much more complex than the example because the SQL var is reused in many long and complex SQL queries.
EDIT: The Project requires the use of PreparedStatements, I have no choice to use libraries at the moment.
source to share
> Should I create PreparedStatement for var SQL and nest it in SQL2
Not
> Or there should be multiple SQL2 based PreparedStatements without nesting
Yes
Also: if you could create one row for each request, that would be better. I don't really like mixing SQL with code. It makes it difficult to debug and understand, you cannot copy / paste the SQL tool to test it easily. By separating the SQL from your code, you isolate your query from the operation (the actual fetch), and it is easier to maintain. Plus, if the code isn't yours, it'll be much easier to understand.
It doesn't matter if you seem to be repeating lines, the point would be to keep the statements as simple as possible.
I would do something like this:
final class DatabaseQueries {
public final static String SOME_SCENARIO = "SELECT z WHERE x > y JOIN A, B ";
public final static String SOME_OTHER_SCENARIO = "SELECT z WHERE x <= y JOIN A, B";
}
And then use it from your class:
PreparedStatement pstmt = getCon().prepareStatement( getQuery() );
private String getQuery() {
if( x != null ) {
return DatabaseQueries.SOME_SCENARIO;
} else {
return DatabaseQueries.SOME_OTHER_SCENARIO;
}
}
When you create the "DatabaseQueries" class, you find yourself iterating over a lot of lines, I think it would be nice to get some part with other constants.
final class DataBaseQueries {
// this one is private
private final static String JOIN_A_B = " join A, B ";
public final static String SOME_SCENARIO = "SELECT z WHERE x > y " + JOIN_A_B ;
public final static String SOME_OTHER_SCENARIO = "SELECT z WHERE x <= y " + JOIN_A_B ;
}
The point here is to make things easier. This is the first step. In the second step, you can create a class to create those queries that are really very complex, but probably YAGNI.
If the requests are too big, you can replace them to load them from the ResourceBundle like in this question
Hope this helps.
source to share
Ibatis is very good at this.
<select id="queryName" parameterClass="com.blah.X"><!<[CDATA[
SELECT z
FROM a
JOIN b ON a.id = b.foreign_key
WHERE
<isNotNull property="value">
x > y
</isNotNull>
<isNull property="value">
x <= y
</isNull>
]]></select>
This is just a small part of what the Ibatis can do, but its extremely light weight. Excellent technology.
source to share
This is not the correct use of prepared statement parameters. Parameters can only be used in place of a literal value in an SQL expression. Not table names, column names, or other SQL syntax.
You can use some library to create parts of the SQL query. I was working on such a library in PHP called Zend_Db_Select
.
edit: I searched a bit for a similar library for Java and I found this option that might be helpful:
- Squiggle is a small Java library for dynamically generating SQL SELECT statements. [Its] sweet spot for applications that have to create complex queries with criteria that change at runtime. It can usually be quite painful to figure out how to construct this line. Squigg takes away most of this pain.
It is free and offered under the Apache License, which is a fairly flexible open source license.
Googling for "java query builder" found a number of other options, but some of them were not free. Some were visual query builders rather than software query builders.
Another option is to use a complex object-relational mapping framework like Hibernate, but that seems overkill for your current task.
source to share
The short answer is there is no better way. You can get something like this:
String selectQuery =
(new SelectQuery())
.addColumns(t1Col1, t1Col2, t2Col1)
.addJoin(SelectQuery.JoinType.INNER_JOIN, joinOfT1AndT2)
.addOrderings(t1Col1)
.validate().toString();
But for me it's even worse.
source to share
I guess on the one hand there is a purists' object approach that probably won't be very helpful to you if you're trying to understand legacy code. I've found that when refactoring really nasty legacy code, rather than striving for "perfect", it often simplifies smaller chunks like modular and well-documented ones better and easier, since you can do them without rewriting the entire application at once. I found that the biggest obstacle for me in refactoring bad code is that if I take too big steps, I can no longer be sure that I have not broken anything - if this is that bad, there are probably no unit tests, and, probably undefined or undocumented behavior.
I would at least rip out the logic and sql as a first pass. What you don't need is something like this:
String sql = "yadda yadda yadda ? yadda yadda WHERE ";
if (mystery condition 1){
sql = sql + " page=?"
}
else if (mystery condition 2)
{
sql = sql + " ORDER BY ? "
}
After a while, you won't be able to tell which statements are being built. It's not worth saving a bit of duplication of the original sql. It might be easier to understand how:
private static final String FIND_PAGE_QUERY = "...."
private static final String ORDER_BY_QUERY =" ..."
if (mystery condition 1){
return process(FIND_PAGE_QUERY, parameters);
}
else if (mystery condition 2)
{
return process(ORDER_BY_QUERY, parameters);
}
and then just create something to wrap your requests and parameters passed in as Spring JDBC Row Mappers or something similar. It's still ugly, but it's easy to do as a step-by-step step from what you have and at least remove some of the query generation confusion.
source to share
Another approach would be to translate the conditional into the SQL statement itself, so that you only need one statement. It will be something like:
SELECT z WHERE (? IS NOT NULL AND x > y) OR (? IS NULL AND x <= y)
and then bind the appropriate value for the parameter.
Not sure if this is the best approach ... people may find the code less clear. But this is an opportunity.
source to share