Java create PreparedStatement with no SQL content

Question

Is it possible to create a PreparedStatement in java without setting the initial SQL query?

Example code:

@Override
public List<AccountBean> search(AccountConstraint... c) {
    if (c.length == 0) {
        throw new IllegalArgumentException("dao.AccountDAO.search: c.length == 0");
    }
    try {
        List<AccountBean> beans = new ArrayList<>();
        for (AccountConstraint ac : c) {
            PreparedStatement ps = connection.prepareStatement(null);
            QueryBuilder queryBuilder = new QueryBuilder(ps, "SELECT * FROM accounts");
            queryBuilder.add(ac.getAccountIdConstraint());
            queryBuilder.add(ac.getUsernameConstraint());
            queryBuilder.add(ac.getPasswordConstraint());
            queryBuilder.add(ac.getEmailConstraint());
            //INSERT QUERY INTO PS
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                beans.add(new AccountBean(rs));
            }
        }
        return beans;
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    }
}

The trick is in QueryBuilder, this class is responsible for building parts of a query based on the initial SELECT part and then adds respective WHERE and AND clauses.

However to ensure that all data is safe, the actual arguments must also be put in the PreparedStatement, hence why it is being passed to the QueryBuilder.

Every QueryBuilder.add() adds some arguments into the PreparedStatement and appends a specific string to the end of the query.

I think some workarounds are possible, such as instead of giving a PreparedStatement to the QueryBuilder you would give a List<Object> and then you would write a custom function that puts them in the PreparedStatement later on.

But what are your thoughts, suggestions on this?

Regards.

Solution added

Few key changes first:

• QueryBuilder now implements the Builder pattern properly.
• QueryBuilder.add() accepts multiple Constraints at once.
• AccountConstraint can give an array that gives all Constraints now.

---

@Override
public List<AccountBean> search(AccountConstraint... c) {
    if (c.length == 0) {
        throw new IllegalArgumentException("dao.AccountDAO.search: c.length == 0");
    }
    try {
        List<AccountBean> beans = new ArrayList<>();
        for (AccountConstraint ac : c) {
            try (PreparedStatement ps = new QueryBuilder("SELECT * FROM accounts").add(ac.getConstraints()).build();ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    beans.add(new AccountBean(rs));
                }
            }
        }
        return beans;
    } catch (SQLException ex) {
        throw new RuntimeException(ex);
    }
}

ps. I get two statements in one try{ } because of the try-with-resources.

Answer 1

Preparing a statement means compiling it so you can efficiently execute it many times with different arguments. So, no it does not make sense to compile a query before it is defined.

As I understand, you want to use the Java compiler to assist you in dynamically defining the query. Why don't you just create the prepared statement in a compile() method, thus, as the result of your builder. Also, your code becomes more readable and more resembles a declarative query if you use the builder pattern such that each call to add() returns this. Then you can write your query like this:

PreparedStatement ps = new QueryBuilder()
   .select("*")
   .from("accounts")
   .where()
   .add(yourConstraint())
   ...
   .compile();

However, you must create the prepared statement before the loop. Otherwise, if you keep a reference to the builder and call compile() in your loop you will get a new prepared statement on every call. So you won't get the benefit of reusing a precompiled query. In the loop you only assign values to the variables in your prepared statement.

Answer 2

You can't modify the prepared statement via the API after you crate it. You can't create it without an SQL statement either.

Why not create the query separately and then bind the parameters? You can use a Map to hold the parameter placeholders and their values so they can be set to the prepared statement.

Although I'd just use the Spring's JDBC templates to get the same thing done more quickly.

Answer 3

How to improve your SQL query builder

If you look at how popular query builders like jOOQ and others do it, the idea is that you separate your concerns more thoroughly. You should have:

• An expression tree representation of your SQL statement (and ideally that doesn't directly operate on strings)
• A way to construct that expression tree conveniently, e.g. by using a DSL
• Some sort of execution lifecycle management that generates the SQL string, prepares the statement, binds the variables, etc.

Or in code (jOOQ example, but this could also apply to your own query builder):

Result<?> result =

// This constructs the expression tree through the jOOQ DSL
ctx.selectFrom(ACCOUNTS)
   .where(ac.getAccountIdConstraint())
   .and(ac.getUsernameConstraint())
   .and(ac.getPasswordConstraint())
   .and(ac.getEmailConstraint())

// This internally creates a PreparedStatement, binds variables, executes it, and maps results
   .fetch();

Of course, your AccountConstraint.getXYZConstraint() methods would not return SQL string snippets, but again expression tree elements. In the case of jOOQ, this would be a Condition

(Disclaimer: I work for the vendor of jOOQ)

How to improve your SQL performance

I've noticed that you run N queries for N AccountConstraint values, and you mix the results in a way that it doesn't matter which AccountConstraint value produced which AccountBean. I strongly suggest you move that loop into the generated SQL query, as you're going to get much faster results on pretty much every database. I've blogged about this here.