Executing generic SQL in Java

Question

I'm working on a small web app that will use a MySQL DB, with several servlets doing different kinds of SQL - insert, update, delete, etc. Before, I used to write a separate class Database.java, with a separate function for each query. However, this builds up a large and messy file, and now I would like to make generic functions, such as this one:

    static ArrayList rs(String query, List params, int columns) {
    ArrayList result = new ArrayList();
    Connection con = null;
    ResultSet rs = null;
    PreparedStatement stmt = null;
    try {
        con = getConnection();          
        stmt = con.prepareStatement(query);

        for (int i = 0; i < params.size(); i++) {
            stmt.setObject(i+1, params.get(i));
        }

        rs = stmt.executeQuery();
        while(rs.next()) {
            ArrayList thisRow = new ArrayList();

            for (int column = 1; column <= columns; column++) {
                thisRow.add(rs.getObject(column));

            }
            result.add(thisRow);

        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
            stmt.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


    return result;

}

Is this good/bad, efficient/inefficient way of doing things? Are there better ways to achieve what I need? I've read something about "JPA", however, I have no understanding of how it works or is used, and would like the queries to be as generic as possible (typeless and with any number of parameters).

Looks very ugly, with another try-catch in the finally, but if that's the way to go, I'll change it, thanks — Darbininkai Broliai, Sep 11 '15 at 19:14
Alternatively, you can use try-with-resources if you're using jre 1.7+ to close the connection upon going out of scope for the try-catch. — Chris Cullins, Sep 11 '15 at 19:24

score 1 · Answer 1 · answered Sep 11 '15 at 19:22

very close, but to make it a bit more efficient, try doing this

static ArrayList rs(String query, List params, int columns) 
{
    ArrayList result = new ArrayList();
    try (Connection con = getConnection())
    {
        try(PreparedStatement stmt = con.prepareStatement(query))
        {
            for (int i = 0; i < params.size(); i++) 
            {
                stmt.setObject(i+1, params.get(i));
            }
            try(ResultSet rs = stmt.executeQuery())
            {
                 while(rs.next()) 
                 {
                     ArrayList thisRow = new ArrayList();
                     for (int column = 1; column <= columns; column++) 
                     {
                          thisRow.add(rs.getObject(column));
                     }
                     result.add(thisRow);
                 }
            } 
            catch (SQLException e) 
            {
                e.printStackTrace();
            }
        }
    }
    return result;
}

score 1 · Answer 2 · answered Sep 11 '15 at 19:36

Well the solution could depend on the level of "generic" result you want to get. Anyway I recommend not to invent bicycles with plain JDBC API as it's too low-level.

In addition to JPA or MyBatis (which could seem to complex to start with) I think one of the easiest ways to go is to use Spring JDBC template. It provides plenty of ways to access data using SQL. The most trivial and generic example:

List<Map<String,Object>> queryResult = jdbcTemplate.queryForList("**qour sql select here**");

score 0 · Answer 3 · edited May 23 '17 at 10:34

If you are trying to come up with a more efficient way than plain JDBC + homemade utility classes, then there are two good options to consider:

ORM tools (eg. JPA): these tools will let you map your existing tables to objects you create, then give you an awful lot of functionalities that will be available out-of-the-box. It does come free of course, you need to learn how to configure and use these, but after that it will become a very powerful tool.
General persistence framework (eg. MyBatis): these are somehow easier to use (at least MyBatis is), with less functionality. I suggest reading the example and compare it to JPA to see which one suits you best.

Executing generic SQL in Java

3 Answers3