"Batch" Selects

[Danilo Reinert]

I tried to execute the following object api method:

        SqlQuery("SELECT value FROM params WHERE key = :_key")

        Collection<String> selectByKey(@Bind("_key") Iterable<String> key)

hoping that jDBI were going to transform my query to SELECT value FROM params WHERE key = key1 [OR key = keyN]* but it didn't happen. =(

Does jSBI provides any feature like this?

--

D. Reinert

[Steven Schlansker]

Hi,

Yes, but you need to bind your keys as an array, or do some basic statement templating. There was a thread about this very recently on this very list, just take a look back through the archives and it should be easy to find. It works better on some DBMSes than others, and unfortunately there are some significant caveats on e.g. MySQL…

Best,
Steven

[Danilo Reinert]

Just for reference: https://groups.google.com/forum/?hl=en&fromgroups=#!searchin/jdbi/select$20list/jdbi/OyAoi3_1TA4/vqRuxNUn4OQJ

Here's what I've done:

1) Registered my SQL Object API Interface with @UseStringTemplate3StatementLocator

2) Wrote my method like:

        SqlQuery("SELECT value FROM params WHERE key IN ( <keys> )")

        Collection<String> selectByKey(@BindIn("keys") List<String> keys)

3) Added the dependency org.antlr.stringtemplate:3.2.1 to my project.

And done!

Notes:

* The parameter at the SQL String must follow the pattern <*>. I tried using :* and it didn't work.

* You must use @BindIn instead of conventional @Bind.

* For those who aren't using Dropwizard (like me), you need to add the depency org.antlr.stringtemplate at version 3. Version 4 removed somes classes and caused this jDBI's feature to crash.

Thanks Steven for the tip!

Hope this help others too.

--

D. Reinert