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.