JDBI and PreparedStatements

[Rawat]

Hello

Does JDBI support PreparedStatements ? I am using HikariConnection pool with JDBI and my database is MySQL. While browsing the code i came across


return new CachingStatementBuilder(new DefaultStatementBuilder());

While i do see that in CachingStatementBuilder we check if the statement is in the cache or not, but i also see @Deprecated on top.

So if i run my queries like this

h.execute("insert into something (id, name) values (?, ?)", 1, "Brian");

instead of using parameter names ( :id and :name ), would i still be able to use PreparedStatement caching provided by underlying JDBC driver ?

[Rawat]

I browsed more code and noticed that whether i pass parameters as positional params or named params, they will both work the same way. It looks like JDBI treats every statement as a PreparedStatement, So i should be good.

public void bind(Binding params, PreparedStatement statement) throws SQLException
        {
            if (stmt.positionalOnly) {
                // no named params, is easy
                boolean finished = false;
                for (int i = 0; !finished; ++i) {
                    final Argument a = params.forPosition(i);
                    if (a != null) {
                        try {
                        a.apply(i + 1, statement, this.context);
                        }
                        catch (SQLException e) {
                            throw new UnableToExecuteStatementException(
                                    String.format("Exception while binding positional param at (0 based) position %d",
                                                  i), e, context);
                        }
                    }
                    else {
                        finished = true;
                    }
                }
            }
            else {
                //List<String> named_params = stmt.params;
                int i = 0;
                for (String named_param : stmt.params) {
                    if ("*".equals(named_param)) {
                        continue;
                    }
                    Argument a = params.forName(named_param);
                    if (a == null) {
                        a = params.forPosition(i);
                    }

                    if (a == null) {
                        String msg = String.format("Unable to execute, no named parameter matches " +
                                                   "\"%s\" and no positional param for place %d (which is %d in " +
                                                   "the JDBC 'start at 1' scheme) has been set.",
                                                   named_param, i, i + 1);
                        throw new UnableToExecuteStatementException(msg, context);
                    }

[Brian McCallister]

Correct :-)

The caching thingie was deprecated because most drivers provide prepared statement caching now, and doing it in jdbi was really the wrong place.

-Brian

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Rawat]

Query jdbiQuery = h.createQuery(_stmt.getSQL()); //Returns parameterized sql like : "select A from B where C=?"
//Bind arguments
 int pos = 1;
for(Object obj : _stmt.getParams()) {
    jdbiQuery.bind(pos++, obj);
}

I am doing something wrong. bind isnt working for me. The query gets executed with "?" in it

Whats wrong ?

[Rawat]

Here is the exception i get

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: java.sql.SQLException: No value specified for parameter 1 [statement:"select platform.platform as platform from platform where id= ? ", located:"select platform.platform as platform from platform where id= ? ", rewritten:"select platform.platform as platform from platform where id= ? ", arguments:{ positional:{1:1}, named:{}, finder:[]}]

So its clear that ? is not being replaced by the value i pass as argument.

[Rawat]

Fixed it by starting with position 0. I checked in MyRewrittenStatement class we already do a +1 for the position.