Why do I need a connection to create PreparedStatements ?

Question

I would like to use prepared statements, for many reasons. But, I would like to create a method that looks like this:

/* This opens a connection, executes the query, and closes the connection */
public static void executeNonQuery(String queryString);

In other words, I want my application logic to only have to formulate the queries and feed in parameters, but not deal with connections & statements. However, PreparedStatements are created from a connection object, so I am currently forced into preparing the query string using String.format() - butt ugly and dangerous.

Is there a way to do what I want without using String.format()?

Answer 1

Why do I need a connection to create PreparedStatements ?

Because the statements are prepared on per-connection basis in most RDBMS's.

Prepared statements are in fact cached execution plans that don't take you permissions, encodings, collation settings etc. into account.

All this is done during query parsing.

Is there a way to do what I want without using String.format()

Don't see why you need String.format() here.

You can implement your query as a class, create a connection and prepare the query in the class constructor and then execute it in a method.

A parametrized query typically looks like this:

SELECT  *
FROM    table
WHERE   col1 = ?
        AND col2 = ?

, where the bound parameters will be substituted for ?'s during the query execution.

If you want a static method:

• Create a static connection handle.
• Create a static hash table of prepared queries using the parametrized query text as a key, and the handle to the prepared query as a value.
• Whenever you want to execute a query, find its handle (or create it if it wasn't found) and use to to bind the parameters and execute the query.

Answer 2

Why not have your "application" logic use a data layer which you create which can present that kind of interface method?

Your data layer can then handle creating connections, preparing statements, etc., all within that executeNonQuery method.

I think that if you are attempting to merge the parameters in your query/statement yourself into a String, then you are shooting yourself in the foot and actually not using the parameter functionality of PreparedStatements. Not sure why you would want to do this.

You might also want to look into using an API such as Spring, which has a series of JdbcTemplate classes that can abstract all of the connection handling away from you, but still allow you to work with parameters in a Map.

Answer 3

I abstract out all of the JDBC stuff by having a class I call QueryRunner that has an execute method that takes the sql, a List of objects that represent the parameters, and an object that will process the ResultSet. If you use the setObject method from JDBC to set your parameters it will figure out the appropriate DB types to use based on the underlying object. Here is a portion of my code. I've got another method that wraps this one and get's the connection.

public void executeNoCommit(Connection conn,
                            String sql, 
                            List params, 
                            ResultSetProcessor processor) throws SQLException {
    PreparedStatement stmt = null;
    ResultSet rs = null;
    int updateCount = 0;
    Iterator it;
    int paramIndex = 1;
    boolean query;

    try {
        stmt = conn.prepareStatement(sql);

        if (params != null) {
            it = params.iterator();
            while (it.hasNext()) {
                stmt.setObject(paramIndex, it.next());
                paramIndex++;
            }
        }

        query = stmt.execute();
        if (query) {
            rs = stmt.getResultSet();
        }
        else {
            updateCount = stmt.getUpdateCount();
        }

        processor.process(rs, updateCount);
    }
    finally {
        if (rs != null) {
            try {
                rs.close();
            }
            catch (SQLException e) {
                log.error(e);
            }
        }

        if (stmt != null) {
            try {
                stmt.close();
            }
            catch (SQLException e) {
                log.error(e);
            }
        }
    }
}

Answer 4

You probably want something like the DbUtils package in the Apache Commons libraries: [http://commons.apache.org/dbutils/index.html][1]

The QueryRunner class lets you execute sql statements without having to manually create PreparedStatements, or even have an open connection for that matter. From the examples page:

QueryRunner run = new QueryRunner( dataSource );
try
{
    // Create an object array to hold the values to insert
    Object[] insertParams = {"John Doe", new Double( 1.82 )};
    // Execute the SQL update statement and return the number of
    // inserts that were made
    int inserts = run.update( "INSERT INTO Person (name,height) VALUES (?,?)",
                              insertParams );

    // Now it's time to rise to the occation...
    Object[] updateParams = {new Double( 2.05 ), "John Doe"};
    int updates = run.update( "UPDATE Person SET height=? WHERE name=?",
                              updateParams );
}
catch(SQLException sqle) {
    // Handle it
}

So it basically handles the creation of prepared statements transparently, and the only thing you really need to know is a DataSource. This also works just as well for non-update/insert statements, i.e. plain-vanilla select queries, and the ability to create ResultSetHandlers gives you the power to convert a ResultSet into something like a fully-prepared bean, or a Map with the keys being the column names, and the values being the actual row values. Very useful for when you can't implement a whole ORM solution.