Hi Tanja,
Wow, that sounds painful! The first thing I would do is add a thin wrapper around the code that runs your queries, that spits out the SQL of all queries to a log file. This will make things a lot easier than having to run the debugger every time. Once you have a wrapper layer in place, you could do other cool stuff like timing query execution and outputting warning logs for slow queries.
Second, running SQL queries using raw strings instead of prepared statements is inviting SQL injections, so
you should work to fix that asap. It would probably be difficult to jump straight from the current state to a full-blown ORM such as Hibernate, so I wouldn't recommend that. As a starting point, I would try implementing some helpers to take care of building PreparedStatements, with a nice fluent interface to make the code easier to read. I'm thinking something like:
If you make this StatementBuilder immutable, you will be able to share a half-built SQL statement between different queries, thus reducing copy-pasted code.
If you have to build a lot of complex WHERE clauses, you might want to create a Condition or Filter class that can take a StatementBuilder and return a new one with the appropriate WHERE clauses added. E.g.:
Of course, any refactoring you do to the DB queries should be accompanied by tests. You will need to set up a
test harness where you can run queries against a real DB, check that the results are what you expect, and setup and teardown the DB data in between tests. You might want to use an in-memory DB such as h2 or HSQLDB for easier setup and faster test execution.