This post originated from an RSS feed registered with Java Buzz by Brian McCallister. Original Post: Query By Critera Library For JDBC Feed Title: Waste of Time Feed URL: http://kasparov.skife.org/blog/index.rss Feed Description: A simple waste of time and weblog experiment Latest Java Buzz Posts Latest Java Buzz Posts by Brian McCallister Latest Posts From Waste of Time

All of the recent talk about plans for OJB 1.1 including some thinking about an API and SPI seperation, the Hibernate query-by-criteria stuff, and general itchiness prompted me to finally get a reimplementation of an SQL query-by-criteria library dusted off, pieced together enough to be generally useful. and released. The gist of the library is that it allows for easy to use and flexible (hmm, seeing a theme?) dynamic SQL generation for use with JDBC.

SQLBuilder provides a convenient query-by-criteria style SQL generator in the QueryBuilder. It allows out-of-order addition of elements, will map prepared statement binding values for elements (which is needed when doing outof order generation), etc. There are some fancy things that it doesn't do which I would like it to do, but I get close to stepping on some IP issues with those so they aren't implemented =/ Still, as it exists it is perty useful methinks.

Sample usage:

public List findEmployees(Map constraints) throws SQLException
{
    PreparedStatement stmt = null;
    Connection conn = null;
    ResultSet results = null;
    try
    {
        QueryBuilder qb = QueryBuilder.select().all().from("employees e");
        Map bindings = new HashMap();
        if (constraints.containsKey("firstName"))
        {
            qb.where("e.first_name like {firstName}");
            bindings.put("firstName", "%" + constraints.get("firstName") + "%");
        }
        if (constraints.containsKey("lastName"))
        {
            qb.where("e.last_name like {lastName}");
            bindings.put("lastName", "%" + constraints.get("lastName") + "%");
        }
        if (constraints.containsKey("departmentName")
        {
            qb.leftOuterJoin("employees e", "departments d", 
                             "e.dept_id = d.id and d.name like {deptName}");
            bindings.put("deptName" "%" + constraints.get("departmentName") + "%");
        }
        Connection conn = // Obtain a connection;
        PreparedStatement stmt = conn.prepareStatement(qb.getQueryString());
        qb.bind(stmt, bindings);
        results = stmt.executeQuery();

        List emps = new ArrayList();
        while (results.next())
        {
            emps.add(buildEmployee(ResultSet));
        }
        return Collections.unmodifiableList(emps);
    }
    finally
    {
        if (results != null) results.close();
        if (stmt != null) stmt.close();
        if (conn != null) conn.close();
    }
}

Source code , Binary Distribution and 1000 javadocs are all available. It has no 3rd party runtime dependencies, but the test suite depends on HSQLDB and JUnit It is presently BSD-style licensed.