Complex SQL queries with JDBI

[Kim Kantola]

Hi All,

I am new to JDBI.  I have seen several simple examples of annotating a DAO with a query like "Select * from tableName where id=?".

Could someone point me to documentation for more complex queries such as searching by a dynamic list of parameters?  For example, a user can search by name, age, id, height, etc, but I am unaware until runtime which combination of these search parameters may come in to my search method.  I may not get a search value for age, and so do not need to bind anything to the search query for that.

Any pointers appreciated,

~Kim

[Evan Meagher]

Hello Kim,

Based on my understanding of your situation, I would advise the approach of using the simplest possible DAO methods and keeping the query complexity within your application code. For instance, if a GET endpoint takes name, age, id, etc as query parameters, your resource method could fire off simple `SELECT * FROM table_name WHERE foo = bar` depending on whatever condition(s) you want.

This is simply because I find application code to be easier to test and maintain, in contrast to SQL code inlined as strings in annotations.

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

--

Evan Meagher

[Evan Meagher]

On second thought...

If you're intention is to filter a list of results by name, age, id, etc (rather than to look up individual records), then you'll probably want to perform the filtration in the database. The alternative (retrieving all records in the DB and then filtering in application code) could impose an undesirable amount of transferred data.

In which case your question is really about SQL rather than Dropwizard or jDBI. 

--

Evan Meagher

[Douglas Patriarche]

Hi Kim,

The JDBI documentation briefly mentions dynamic inserts and queries, but the documentation is unfortunately sparse and incomplete. Basically, while most of the examples show SQL queries created using @SqlQuery annotations with @Bind to bind input values, if you need to create the query on the fly you can explicitly makes these calls in regular Java like this:

    public List<String> getNames(final int minCredits, final String type) {

       final DBI dbi = new DBI("jdbc:h2:mem:test");

       try (Handle h = dbi.open()) {

           String sql = "select name from Customers where credits >= :minCredits";

           if (type != null) {

               sql += " and type = :type";

           }

           final Query<Map<String, Object>> q = h.createQuery(sql);

           q.bind("minCredits", minCredits);

           if (type != null) {

               q.bind("type", type);

           }

           final List<String> names = q.map(StringColumnMapper.INSTANCE).list();

           return names;

       }

   }

This allows you to create the query based on a dynamic list of parameters. I hope this helps.

[Kim Kantola]

Thank you so much Evan and Douglas for taking the time to help. 

I did end up going with your approach Douglas, I am using annotated SQL where I can, but for the dynamic queries, I am using the jdbi handle and executing the SQL that way.

In case anyone else finds this thread looking for similar help, I should add that in my DAO class, I imported the following class which comes bundled with dropwizard

org.skife.jdbi.v2.sqlobject.mixins.GetHandlecode here...

Then, I made my class implement GetHandle, by doing that, I can then do 

this.getHandle();

in order to get access to the database connection.

[Tim Bart]

Thanks for sharing your approach Douglas, Kim.

I was wondering what the most elegant way of making the handle available was when 99% of queries are well supported by the @SqlQuery("…");

[Douglas Patriarche]

Hi Tim,

I can't promise this is the most elegant way :-) but here's how one of my Java 8-based projects accesses the handle using lambdas for conciseness:

First, here's a class for wrapping the DBI object and for creating Handles:

public class MyDb {

    /**

     * A functional interface that defines a callback that takes a database handle.

     */

    @FunctionalInterface

    public interface MyHandleCallback {

        /**

         * This callback will be invoked with an open Handle. The handle will be closed when this

         * callback returns. Any exception thrown will be wrapped in a

         * {@link org.skife.jdbi.v2.exceptions.CallbackFailedException}

         *

         * @param handle Handle to be used only within scope of this callback

         *

         * @throws Exception will result in a

         *             {@link org.skife.jdbi.v2.exceptions.CallbackFailedException} wrapping the

         *             exception being thrown

         */

        void withHandle(Handle handle) throws Exception;

    }

    private static DBI _jdbi = null;

    /**

     * Execute a single database request, defined in a callback function.

     *

     * @param callback The database callback function, which takes a

     *            {@code org.skife.jdbi.v2.Handle} as its argument and returns an object of type

     *            {@code ReturnType}

     * @return The return value of the callback.

     */

    public static <ReturnType> ReturnType exec(final HandleCallback<ReturnType> callback) {

        return _jdbi.withHandle(callback);

    }

    /**

     * Execute one or more statements, defined in a callback function.

     *

     * @param callback The database callback function, which takes a

     *            {@code org.skife.jdbi.v2.Handle} as its argument and returns nothing.

     */

    public static void execN(final MyHandleCallback callback) {

        try (final Handle h = _jdbi.open()) {

            callback.withHandle(h);

        }

        catch (final RuntimeException e) {

            throw e;

        }

        catch (final Exception e) {

            throw new CallbackFailedException(e);

        }

    }

    /**

     * Starts the database connection manager.

     *

     * @param config The Dropwizard runtime configuration.

     * @param environment The Dropwizard runtime environment.

     */

    public static void start(final MyConfiguration config, final Environment environment) {

        final DataSourceFactory dsf = config.database;

        /*

         * (Note: If you need to do database initialization or schema migration do it here.)

         */

        _jdbi = new DBIFactory().build(environment, dsf, "database");

    }

}

Then in your Application class you initialize the MyDb class in the run() method:

public class MyApplication extends Application<MyConfiguration> {

    // ...

    @Override

    public void run(final MyConfiguration config, final Environment environment) {

        // ...

        MyDb.start(config, environment);

        // ...

    }

}

And then in your application code you can access to database through either the exec() or execN() static methods. For example here's a call to MyDb.exec():

MyDb.exec(h -> h.attach(MyDbDocuments.class)
    .getDocsForReqNewestVersion(project.getProjectId(), req.getReqId()))
    .stream()
    .forEach(doc -> _visitor.visit(doc, null));

And here's a call to MyDb.execN() where the Handle is used multiple times:

MyDb.execN(h -> {
    _srsHighestVersion = h.attach(MyDbLocalDocs.class)
        .getHighestVersionNumberOfLocalDoc(project.getProjectId(), MyDocType.SRS);
    _srsDoc = h.attach(MyDbLocalDocs.class)
        .getOneLocalDocNewestVersion(project.getProjectId(), MyDocType.SRS);
    });

I like doing it with this way because the Handle management is hidden and can't be messed up, and all database accesses in the program can be easily located, because they are always within an exec() or execN() call. I hope this is useful to you.

Douglas