How to do in-query in jDBI?

Question

How can I execute somethings like this in jDBI ?

@SqlQuery("select id from foo where name in <list of names here>")
List<Integer> getIds(@Bind("nameList") List<String> nameList);

Table: foo(id int,name varchar)

Similar to @SelectProvider from myBatis.

Similar questions has been asked How do I create a Dynamic Sql Query at runtime using JDBI's Sql Object API?, but somehow answer is not clear to me.

Did any of the answers solve this issue? im trying the @BindIn but no luck :( — Ali, Mar 17 '16 at 08:01

score 39 · Answer 1 · edited Aug 09 '16 at 13:03

39

This should work:

@SqlQuery("select id from foo where name in (<nameList>)")
List<Integer> getIds(@BindIn("nameList") List<String> nameList);

Don't forget to annotate class containing this method with:

@UseStringTemplate3StatementLocator

annotation (beacuse under the hood JDBI uses Apache StringTemplate to do such substitutions). Also note that with this annotation, you cannot use '<' character in your SQL queries without escaping (beacause it is a special symbol used by StringTemplate).

edited Aug 09 '16 at 13:03

slayton

19,950
8
59
87

answered Oct 23 '13 at 07:20

Deinlandel

1,003
8
25

3

How do I escape ` – Peter Warbo May 08 '14 at 10:34
14

Figured it out. You should do `\\ – Peter Warbo May 08 '14 at 10:50
1

Need to add dependency as mention in https://stackoverflow.com/a/33249078/2987755 – dkb Aug 18 '17 at 07:10
This works cool. But what if I want to apply `lower` function to each item in the list before doing the comparison. I tried `lower(name) in (lower())`. This of course doesn't work. Any idea? – Vikas Prasad Jan 24 '19 at 19:33
@VikasPrasad Why not pre-process arguments in the code before executing the query? – Deinlandel Jan 28 '19 at 05:20

score 8 · Answer 2 · answered Oct 17 '13 at 11:16

8

Use @Define annotation to build dynamic queries in jDBI. Example:

@SqlUpdate("insert into <table> (id, name) values (:id, :name)")
public void insert(@Define("table") String table, @BindBean Something s);

@SqlQuery("select id, name from <table> where id = :id")
public Something findById(@Define("table") String table, @Bind("id") Long id);

answered Oct 17 '13 at 11:16

Raven

134
4

1

I assume that you had to add `@UseStringTemplate3StatementLocator` to the class to support `@Define`, but how did you get it to still do argument binding (`@Bind`) in addition to `@Define`? – Tristan Jul 31 '15 at 19:06

score 8 · Answer 3 · answered Jul 29 '16 at 23:21

With PostgreSQL, I was able to use the ANY comparison and bind the collection to an array to achieve this.

public interface Foo {
    @SqlQuery("SELECT id FROM foo WHERE name = ANY (:nameList)")
    List<Integer> getIds(@BindStringList("nameList") List<String> nameList);
}

@BindingAnnotation(BindStringList.BindFactory.class)
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.PARAMETER})
public @interface BindStringList {
    String value() default "it";

    class BindFactory implements BinderFactory {
        @Override
        public Binder build(Annotation annotation) {
            return new Binder<BindStringList, Collection<String>>() {
                @Override
                public void bind(SQLStatement<?> q, BindStringList bind, Collection<String> arg) {
                    try {
                        Array array = q.getContext().getConnection().createArrayOf("varchar", arg.toArray());
                        q.bindBySqlType(bind.value(), array, Types.ARRAY);
                    } catch (SQLException e) {
                        // handle error
                    }
                }
            };
        }
    }
}

NB: ANY is not part of the ANSI SQL standard, so this creates a hard dependency on PostgreSQL.

score 7 · Answer 4 · answered Apr 30 '19 at 12:53

If you are using the JDBI 3 Fluent API, you can use bindList() with an attribute:

List<String> keys = new ArrayList<String>()
keys.add("user_name");
keys.add("street");

handle.createQuery("SELECT value FROM items WHERE kind in (<listOfKinds>)")
      .bindList("listOfKinds", keys)
      .mapTo(String.class)
      .list();

// Or, using the 'vararg' definition
handle.createQuery("SELECT value FROM items WHERE kind in (<varargListOfKinds>)")
      .bindList("varargListOfKinds", "user_name", "docs", "street", "library")
      .mapTo(String.class)
      .list();

Note how the query string uses <listOfKinds> instead of the usual :listOfKinds.

Documentation is here: http://jdbi.org/#_binding_arguments

How to do in-query in jDBI?

4 Answers4

Linked