How to do in-query in jDBI?

Related searches

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.

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).

SQL Object Queries, Queries are denoted on sql object interfaces via the @SqlQuery annotation on the query methods. The return type of the method indicates what to do with the� Querying a SQL database with JDBC is typically a three-step process: Create a JDBC ResultSet object. Execute the SQL SELECT query you want to run. Read the results. The hardest part of the process is defining the query you want to run, and then writing the code to read and manipulate the results of your SELECT query.

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);

Jdbi 3 Developer Guide, The default representation of a result row is a Map<String, Object>, as can be seen when the Query is first created. To map the rows to something else we use a� SQL SELECT query are executed to fetch data stored in relational databases. It requires following steps: 1) Make a database connection. 2) Execute the SQL Query. 3) Fetch the data from result set. Pre-requisites include setting up a database schema and creating a table at least. CREATE SCHEMA 'JDBCDemo' ;

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.

Fluent Queries, This interface defines two updates, the first to create the same table as in the fluent api example, and the second to do the same insert, the third defines a query. In previous posts, we have learned about types of JDBC drivers and the how to make database connection using JDBC and then how to execute SELECT Query, and then INSET Query example. Let’s move forward. In this example I am picking up execution of SQL DELETE queries using JDBC. SQL DELETE query are executed to […]

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

Convenient SQL for Java, DBIs can be constructed three primary ways. The first is to pass a JDBC DataSource instance to the constructor. In this case connections will be obtained from the� Register the JDBC driver: Requires that you initialize a driver so you can open a communications channel with the database. Open a connection: Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

DBI, Handles, and SQL Statements, JDBI provides statement Locator which we can use here to solve this problem. Lets say we are sending schema name in query Parameter, we can use jersey request filter to get schema name.

SQL INSERT query are executed to push/store data stored in relational databases. It requires following steps: 1) Make a database connection. 2) Execute the SQL INSERT Query. Pr-requisites include setting up a database schema and creating a table at least. CREATE SCHEMA 'JDBCDemo' ;

I was wondering if there is a more elegant way to do IN() queries with Spring's JDBCTemplate. Currently, I do something like that: StringBuilder jobTypeInClauseBuilder = new StringBuilder();

Comments
  • Did any of the answers solve this issue? im trying the @BindIn but no luck :(
  • How do I escape < inside the query?
  • Figured it out. You should do \\<
  • Need to add dependency as mention in stackoverflow.com/a/33249078/2987755
  • 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(<nameList>)). This of course doesn't work. Any idea?
  • @VikasPrasad Why not pre-process arguments in the code before executing the query?
  • 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?