Java create PreparedStatement with no SQL content
java, jdbc, prepared-statement
Solution
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.
Problem
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 `Constraint`s at once. - `AccountConstraint` can give an array that gives all `Constraint`s 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.