java sql interface that makes filtering and limits easy?

Question

I am working on a project in java interfacing with mysql via JDBC. Currently all sql calls are literally inlined sql. The are large sql calls, with lots of left joins, and hideous to read or parse.

I wanted to clean this up to be a little more elegant. I will be adding a limit/offset argument and a WHERE clause that filters on date for multiple columns (I'm thinking a generic method where I can add an arbitrary number of date columns to be filtered on).

I'm trying to figure out a simple tool to avoid inlined sql. Hibernate seems inappropriate for this need, the sql queries are too complex and too optimized to replace with hibernate. I figure I instead want something that simple allows me to add request to a query (LIKE A JOIN or an additional WHERE) without modifying sql calls directly. Can anyone suggest a simple java tool for this?

Answer 1

You can use MyBatis. Its a library that allows you to configure your SQL in a XML or with an annotation (in your case the XML would most likely be more readable). You then can give in parameters and get a rich Java Object that can be easily displayed using a visualization library. I found it useful to tie that together using Spring.

This is a sample mybatis XML file (that I took from the wikipedia site for MyBatis).

<mapper namespace="org.mybatis.example.BlogMapper">
    <select id="selectBlog" parameterType="int" resultType="Blog">
        select * from Blog where id = #{id}
    </select>
</mapper>

You can of course have more than one select element per mapper to store your different queries for the respective use cases. Limit and Offset can be applied to the call from the java code, so you can implement pagination on your selection.

MyBatis is not simple, thats basically because the task at hand is not simple either and while the abstraction of MyBatis helps, you still need to do a lot of work yourself and depending on your situation you now have two problems instead of one.

I have worked with iBatis which is the "parent" of MyBatis and found it useful for tasks like this, I assume that MyBatis will be similar enough.

Additional note:
You can try to use views in the MySQL Server and then apply your additional elements to the view. If that already solves your problem it would be the shortest path for your current position to a better solution. But then you still have to create your where clauses manually in your code.