What is the best-practice for nesting PreparedStatements?

OscarRyz :

>Should I create a PreparedStatement for the var SQL and nest it inside of SQL2\n\nNo\n\n>Or should there be multiple PreparedStatements based on SQL2 without nesting\n\nYes\n\nFurthermore: If you could create one string per query that would be better. I don't really like to mix SQL with code. It makes it harder to debug and to understand, you can't copy/paste to a SQL tool to test it easily. By separating the SQL from your code you'll isolate your query from the operation ( the actual fetch ) and it would be easier to maintain. Plus if the code is not yours it will be a lot easier to understand.\n\nIt doesn't matter it looks like your're repeating strings, the point would be to simplify the statements as much as possible.\n\nI would do something like this:\n\nfinal class DatabaseQueries {\n public final static String SOME_SCENARIO = \"SELECT z WHERE x > y JOIN A, B \";\n public final static String SOME_OTHER_SCENARIO = \"SELECT z WHERE x <= y JOIN A, B\";\n }\n\n\nAnd then use it from your class:\n\n PreparedStatement pstmt = getCon().prepareStatement( getQuery() );\n\n\n private String getQuery() { \n if( x != null ) { \n return DatabaseQueries.SOME_SCENARIO;\n } else { \n return DatabaseQueries.SOME_OTHER_SCENARIO;\n }\n }\n\n\nWhile creating the class \"DatabaseQueries\" you'll find you're repeating a lot of strings, I think it would be fine to susbtitute some part with other constants.\n\nfinal class DataBaseQueries { \n // this one is private\n private final static String JOIN_A_B = \" join A, B \";\n public final static String SOME_SCENARIO = \"SELECT z WHERE x > y \" + JOIN_A_B ;\n public final static String SOME_OTHER_SCENARIO = \"SELECT z WHERE x <= y \" + JOIN_A_B ;\n\n}\n\n\nThe point here is to make things simpler. This is the first step. In a second step you can create a class to create those queries that are really really complex, but probably YAGNI.\n\nIf the queries are too much you can replace it to load them from a ResourceBundle like in this question \n\nI hope this helps.",

2008-12-22T22:37:07

cletus :

Ibatis is very good at this.\n\n<select id=\"queryName\" parameterClass=\"com.blah.X\"><!<[CDATA[\n SELECT z\n FROM a\n JOIN b ON a.id = b.foreign_key\n WHERE\n\n <isNotNull property=\"value\">\n x > y\n </isNotNull>\n\n <isNull property=\"value\">\n x <= y\n </isNull>\n\n]]></select>\n\n\nThis is but a small fraction of what Ibatis can do but its extremely lightweight. Excellent technology.",

2008-12-22T22:33:41

Bill Karwin :

This is not the proper use of prepared statement parameters. Parameters can be used only in place of a literal value in an SQL expression. Not table names, column names, or other SQL syntax.\n\nYou could use some library for building parts of an SQL query. I worked on a library like this in PHP, called Zend_Db_Select.\n\nedit: I googled a bit for a similar library for Java, and I found this option which may be helpful:\n\n\nSquiggle is a little Java library for dynamically generating SQL SELECT statements. [Its] sweet spot is for applications that need to build up complicated queries with criteria that changes at runtime. Ordinarily it can be quite painful to figure out how to build this string. Squiggle takes much of this pain away.\n\n\nIt's free and offered under the Apache License, which is a pretty flexible open-source license.\n\nGoogling for \"java query builder\" found a number of other options, but some were not free. Some were visual query builders, not programmatic query builders.\n\nAnother option is to use a complicated object-relational mapping framework like Hibernate, but this seems overkill for your current task.",

2008-12-22T22:17:16

serg :

Here is similar question\n\nShort answer - there is no best way. You may end up with something like this:\n\nString selectQuery =\n (new SelectQuery())\n .addColumns(t1Col1, t1Col2, t2Col1)\n .addJoin(SelectQuery.JoinType.INNER_JOIN, joinOfT1AndT2)\n .addOrderings(t1Col1)\n .validate().toString();\n\n\nBut to me it is even worse.",

2008-12-22T22:31:19

Steve B. :

I guess on one hand there's a purist object approach, which is probably not going to be terribly helpful to you if you're trying to make sense of legacy code. I've found that in refactoring really nasty legacy code rather than striving for \"perfect\" it's often better, and easier, to simplify small pieces, as modular and well documented as you can make them without rewriting the entire app at once. I've found that the biggest hurdle for me in refactoring bad code is that if I take too large steps I can't any longer be confident that I haven't broken anything - if it's that bad, there are probably no unit tests, and there's likely undefined or undocumented behavior. \n\nI'd at least break out the logic and the sql as a first pass. The thing you don't want is something like this:\n\nString sql = \"yadda yadda yadda ? yadda yadda WHERE \";\nif (mystery condition 1){\n sql = sql + \" page=?\"\n}\nelse if (mystery condition 2)\n{\n sql = sql + \" ORDER BY ? \"\n}\n\n\nAfter a while you won't be able to tell what statements are being built. It's not worth saving the bit of duplicating the initial sql. It might be easier to understand as :\n\nprivate static final String FIND_PAGE_QUERY = \"....\"\nprivate static final String ORDER_BY_QUERY =\" ...\"\n\nif (mystery condition 1){\n return process(FIND_PAGE_QUERY, parameters);\n}\nelse if (mystery condition 2)\n{\n return process(ORDER_BY_QUERY, parameters);\n}\n\n\nand then just create something to wrap your queries and parameters passed like Spring JDBC Row Mappers or something similar. This is still ugly, but it's easy to do as an incremental step from what you've got, and will at least sort out some of the confusion of query generation.",

2008-12-22T22:32:16