Binding arrays for SQL IN syntax

[Eric Levine]

Hi all,

I've been trying to implement an aspect of my application where it does a "select ... on table where id in (...)" and I've taken a few stabs at it in jDBI without much luck. I've found that this particular test case exists and implies that this behavior is possible:

https://github.com/brianm/jdbi/blob/master/src/test/java/org/skife/jdbi/v2/TestArgumentFactory.java#L81

But when I try to duplicate that functionality directly into my code:

List<Integer> rs = h.createQuery("select id from users where name in :names order by id")

          .map(IntegerMapper.FIRST)

          .bind("names", Arrays.asList("Eric"))

          .list();

I get this error, implying that it's trying to convert the list into a string which is not what I expected from the test case I pointed out:

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_binary'????\0sr\0java.util.Arrays$ArrayList̪??<???????\0[\0at\0[Ljava/' at line 1 [statement:"select id from users where name in :names order by id", located:"select id from users where name in :names order by id", rewritten:"select id from users where name in ? order by id", arguments:{ positional:{}, named:{names:[Eric]}, finder:[]}]

Any thoughts on why I might be getting this issue?

Thank you,

Eric Levine

[Brian McCallister]

I wrote up a bit about the various options for in clause expansion in
jdbi at http://skife.org/jdbi/java/2011/12/21/jdbi_in_clauses.html --
basically there is no perfect solution, but there are some more or
less palatable options.

-Brian




>
> Thank you,
> Eric Levine

[Eric Levine]

Wow, thanks for your quick response! jDBI seems like a great library. Thanks for all your hard work. :)

[Fredrik Hörte]

I think I have a related problem. Trying to use IN statement in MySQL

@SqlQuery("select term from mydb.terms where id in ( <ids> )")
List<String> findAllTermsWhereIdIn(@BindIn("ids") List<Integer> termIds);


But it doesn't seam to rewrite the query.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<ids> )' at line 1 [statement:"select term from mydb.term where id in ( <ids> )", located:"select term from mydb.term where id in ( <ids> )", rewritten:"/* TermDAO.findAllTermsWhereIdIn */ select term from mydb.term where id in ( <ids> )", arguments:{ positional:{}, named:{__ids_88:204692,__ids_89:173399}, finder:[]}]


Shouldn't it rewrite it to smth like: select term from mydb.term where id in ( :ids_0, :ids_1 )

[Brian McCallister]

On Fri, Aug 10, 2012 at 5:27 AM, Fredrik Hörte <fredri...@gmail.com> wrote:

I think I have a related problem. Trying to use IN statement in MySQL

@SqlQuery("select term from mydb.terms where id in ( <ids> )")
List<String> findAllTermsWhereIdIn(@BindIn("ids") List<Integer> termIds);


But it doesn't seam to rewrite the query.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<ids> )' at line 1 [statement:"select term from mydb.term where id in ( <ids> )", located:"select term from mydb.term where id in ( <ids> )", rewritten:"/* TermDAO.findAllTermsWhereIdIn */ select term from mydb.term where id in ( <ids> )", arguments:{ positional:{}, named:{__ids_88:204692,__ids_89:173399}, finder:[]}]


Shouldn't it rewrite it to smth like: select term from mydb.term where id in ( :ids_0, :ids_1 )

For this to work you *must* be using a templating statement rewriter, such as the StringTemplate 3 one used in TestInClauseExpansion. It looks like you are using the default rewriter used in Dropwizard, which does not do StringTemplate style templating. The @BindIn annotation does two things. First, it registers a list of variables on the statement context for the query, second it binds the contents of the collection to names matching that expansion. The statement rewriter needs to make use of those ids to rewrite the statement to put them into the sql.

 

The "Sql Templating" section of http://skife.org/jdbi/java/2011/12/21/jdbi_in_clauses.html expands on this. Off the top of my head, I am not sure how to make DropWizard use the StringTemplate statement rewriter. I'm using DW for something now so will take a look and write up how to do it when I get a chance, though.

-Brian

[Sathish V]

Hi Brian,

Is there a way where we can use @BindIn without the Externalized string template.

and use it directly like this

@SqlQuery("select term from mydb.terms where id in ( <ids> )")
List<String> findAllTermsWhereIdIn(@BindIn("ids") List<Integer> termIds);

Regards
Sathish

[Cemo]

Hi,

Use annotation @UseStringTemplate3StatementLocator at your Type. Then your example should be perfectly valid.

Cemo

[Sathish V]

Awesome,that worked like a charm.

Thanks Cemo.