I'm currently working on a web service project that has me running some really large SQL queries. Currently, I'm storing these queries in a separate final class as final Strings of the form:
I was wondering, is there a better way to do this?
My second question is also related to the hugeness of the SQL I'm having to deal with.
When I get the SQL from the DB design team, they give me the queries with bind variables, e.g. SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE EMPLOYEE_ID = :pEmpId, which I then have to convert to the above-mentioned String, for better readability. Is there any way I can use the bind variable :pEmpId to set parameters to my SQL? This is becoming a real problem otherwise, as the same bind variable is used in the query in multiple places, and some of the larger queries have around 10-20 bind variables(ot counting repetitions), and it gets really difficult at times to keep track of the parameter order.
I'm a database developer, not a Java developer, but until the Java experts reply, here's my take.
As I understand your question, your DB people are creating these big SQL queries, giving them to you, then you provide the bind variable values, before passing the SQL back to the DB via JDBC to execute the query. So you could ask them to look at the SQL and see if they can't do something to reduce the repeated use of particular parameters, although they may need to pay attention to possible performance issues if they change comparisons with bind variables to comparisons between table columns, as the query execution path may be affected.
But given the apparent complexity of your queries, why are you passing SQL around and having to manipulate it in your Java code, instead of just passing parameters and fetching results from a stored procedure/function? Depending on your DB, you could encapsulate all this query stuff in a stored proc/function behind a simple interface where you just pass in the distinct parameters you need, and the stored proc will pass back a cursor containing the rows you're after. All the SQL manipulation can take place inside the stored code, where your DB people can maintain it (which is what they're doing anyway) and you don't have to care about it.
No doubt there is some clever pure-Java solution to this, which the good folk at JavaRanch will no doubt be able to help you with, but it sounds to me like you're reaching the point where treating the database like a dumb "black box" store is starting to become unsustainable.
Thanks for the reply, Chris, I've discussed your suggestion with the DB team, and they're willing to go for PL/SQL functions, but only as a last resort. I'm trying to convince them, but I'd still appreciate a Java solution in the meantime.
Most Oracle guys I know would much prefer to put this kind of stuff into a nice maintainable PL/SQL function!
Another alternative might be to effectively convert your input parameters into dummy columns, by using the "WITH ..." clause in the SQL that your DB guys are giving you. You could have a WITH clause at the start of the main SQL query which would map the parameters to dummy columns, then the rest of the query can refer to these dummy columns instead of the bind variable parameters everywhere else. This would at least mean you only have to pass in each distinct parameter once.