What is the difference between JDBC and JDBI?

I want to know about the differences between JDBC and JDBI in java. In particular, which one is generally better and why?

Use JDBI to get Postgres Array Data

I have a java program using JDBI (a JDBC wrapper) to access a PostgreSQL database. One of the columns is of the array data type (mycolumn integer[]).

What the heck to I use in my mapper class? I thought resultSet.getArray("mycolumn") would be the right thing to do, but I'm not sure how to get the data out of the java.sql.Array object that gets returned.

Any hints or good links on how to do this?

How to Dymically bind table name in JDBI

I try using "SELECT COUNT(*) FROM :TableName;", then in java I use .bind("Tablename","MyTable").

But the result is always inside single quotes which is " SELECT COUNT(*) FROM 'MyTable'; ", is there a proper way to bind(maybe i use the wrong word) something like TableName to .sql file?

Can I chain @BindBean params?

@BindBean allows me to use properties of bean as params - myObj.a.
Can I use property of property of bean - myObj.a.b?

@SqlQuery("SELECT title FROM user WHERE id = :myObj.a.b")
abstract boolean hasUnmatchedMovie(@BindBean("myObj") MyObject myObject

transactions in jdbi

I execute sql queries as transactions using jdbi inTransaction() function. I would like to know how/what type of locking mechanism is used internally. additionally, is the whole table locked during the transaction or just the record that has to be updated?

JDBI insert issue

I'm using JDBI to insert some data to a mysql table with an auto increment Primary Key. I used indexes to do the insert. The code looks like this:

public void insertWorkout(String[] values) {
    String insertString = "insert into workouts(<column_names>) values(:0,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31)";
    Update pUpdate = handle.createStatement(insertString);

    for(int i=0; i<vals.length;i++) {
        pUpdate.bind(i, vals[i]);
    }

    pUpdate.execute();
}

This has been working fine for awhile because values[] was always the correct count(32) and the arguments were always in the same order. Now though it's going to have to deal with being passed a smaller array(18,19, or 20) and still do the insert correctly; the last 14 or so args can be empty or blank. The order of the values is still static(ie if passed 18 columns it's the first 18 columns in the table) and and the last 20 columns are all int(11) columns.

Right now when it's passed a smaller than 32 array it gives an error like this:

org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Unable to execute, no named parameter matches "20" and no positional param for place 20 (which is 21 in the JDBC 'start at 1' scheme) has been set. 

What is the best way to solve this?

Set the driver for JDBI manually

right now I'm writing a program in Java that will be plugged into an existing application as a plugin. I want to use JDBI in my plugin (as it is very comfortable and I'm used to it) and need to connect to a MySQL database.

Usually that works fine after including the driver, but there is a problem with the existing application. Obviously it already has a mysql-driver, but an obsolete one.

That causes errors and makes it impossible to send a query to the database. (The error is known: The old driver has deprecated methods that cannot be used with the new MySQL versions)

I thought shading my jar would help (I'm using maven) but the error still occurs. But I know the shaded name of my driver, I only need to know how to load it so JDBI will make use of it. Right now I have something like this:

Class.forName("myapp.mysql.jdbc.Driver").newInstance();

DBI dbi = new DBI(String.format("jdbc:mysql://localhost/test", username, password);

What do I have to do to tell jdbi it must use myapp.mysql.jdbc.Driver?

(JDBI/Dropwizard) PSQLException when retrieving auto-incremented id from PostgreSQL

I'm trying to set up a dropwizard project but I'm stuck. When I try to get the auto generated id field with @GetGeneratedKeys then I'm getting the following Exception:

org.postgresql.util.PSQLException: Bad value for type long : foo.

The request is a simple JSON Request

{"name":"foo"}

The INSERT into the database is successful but it seems that the statement returns the value of the name instead of the generated id. How can I solve this?

I use postgresql, and the table project contains a primary key field "id" with nextval('project_id_seq'::regclass). Here are the POJO, DAO and Resource Classes I use:

public class Project {
    private long id;
    private String name;

    public Project() { // Jackson deserialization }
    public Project(long id, String name) {
        this.id = id;
        this.name = name;
    }
    ...
}

@RegisterMapper(ProjectMapper.class)
public interface ProjectDAO {
    @SqlUpdate("insert into project (name) values (:name)")
    @GetGeneratedKeys
    public long insert(@Bind("name") String name);
}

@Path("/project")
@Consumes({MediaType.APPLICATION_JSON})
@Produces({MediaType.APPLICATION_JSON})
public class ProjectResource {
    ProjectDAO projectDAO;

    public ProjectResource(ProjectDAO personDAO) {
        this.projectDAO = personDAO;
    }

    @POST
    @Timed
    public Response add(@Valid Project project) {
        long newId = projectDAO.insert(project.getName());
        project.setId(newId);
        return Response.status(Response.Status.CREATED)
                       .entity(project).build();
    }
}

===============

UPDATE

I just figured out that this relates to the fact that my id column isn't the first column in my table. The column name is. The problem occurs because @GetGeneratedKeys is using org.skife.jdbi.v2.sqlobject.FigureItOutResultSetMapper which is using org.skife.jdbi.v2.PrimitivesMapperFactory which returns org.skife.jdbi.v2.util.LongMapper.FIRST. This mapper is calling java.sql.ResultSet.getLong(1) through the method extractByIndex(...) to retrieve the generated id, which isn't the id in my case...

I'll fix the issue by reorganizing the columns in the database, but I'd like to have a robust implementation if possible: Is there a way to specify the column name of the id column when using the @GetGeneratedKeys Annotation? (The org.skife.jdbi.v2.util.LongMapper class contains a also method called extractByName(...))

Use custom creds while using DropWizard JDBI

I'm developing a webservice using Dropwizard JDBI framework.

Now, instead of having a db configurations in yaml file, I want to use 'user specified params' what i mean to say is, the db configs will be provided through the endpoint url.

• Is having custom creds possible through dropwizard jdbi?

if yes, what changes should i be thinking to do in the code while referring this ? ->

http://dropwizard.readthedocs.org/en/latest/manual/jdbi.html

I understand, in normal flow, the service method gets the config details in the run method -

-- Config Class

public class ExampleConfiguration extends Configuration {
    @Valid
    @NotNull
    @JsonProperty
    private DatabaseConfiguration database = new DatabaseConfiguration();

    public DatabaseConfiguration getDatabaseConfiguration() {
        return database;
    }
}

-- Service Class

@Override
        public void run(ExampleConfiguration config,
                        Environment environment) throws ClassNotFoundException {
            final DBIFactory factory = new DBIFactory();
            final DBI jdbi = factory.build(environment, config.getDatabaseConfiguration(), "postgresql");
            final UserDAO dao = jdbi.onDemand(UserDAO.class);
            environment.addResource(new UserResource(dao));
        }

-- and yaml

database:
  # the name of your JDBC driver
  driverClass: org.postgresql.Driver

  # the username
  user: pg-user

  # the password
  password: iAMs00perSecrEET

  # the JDBC URL
  url: jdbc:postgresql://db.example.com/db-prod

But in this case, I might get the config details in the Resource level...

smthing like -

@GET
@Path(value = "/getProduct/{Id}/{dbUrl}/{dbUname}/{dbPass}")
@Produces(MediaType.APPLICATION_JSON)
public Product getProductById(@PathParam(value = "Id") int Id,
        @PathParam(value = "dbUrl") String dbUrl,
        @PathParam(value = "dbUname") String dbUname,
        @PathParam(value = "dbPath") String dbPass) {

     //I have to connect to the DB here! using the params i have.         
     return new Product(); //should return the Product
}

I'd appreciate if someone can point me a direction.

Can @Bind be used with enums and other arbitrary types using JDBI?

Does JDBI support binding of enum types via annotation?

For example, assuming a DAO that included a method:

@SqlQuery("select count(*) from answer a where a.foo = :foo")
Long someSqlQuery(@Bind("foo") Foo foo);

And, foo equal to Foo.BAR, could I expect a query:

select count(*) from answer a where a.foo = 'BAR'

If so, is toString() utilized to determine what is substituted?

Further, Does JDBI allow for using @Bind with any type that extends Object? And again, if so, is toString() used?

JDBI Object Query

I've used JDBI before for Java persistence stuff before but it's always been the fluent API not the object API. Trying the Object API now.

I've got a DAO Object that is pretty simple:

public interface PersonDAO {

@SqlQuery("insert into person(id,first_name,last_name,position) values(:id,:firstName,:lastName,:position)")
void insertPerson(@Bind("id") Integer id,
                  @Bind("firstName") String firstName,
                  @Bind("lastName") String lastName,
                  @Bind("position") String position);
}

Tested the query in mysql, it works fine, but running it in a unit test:

@Test 
public void testInsertPerson() {
    PersonDAO personDao = dao.getRegHandle().attach(PersonDAO.class);
    personDao.insertPerson(888888,"Tom", "Ford", "Manager");
}

I get an exception:

java.lang.IllegalStateException: Method com.hrweb.dao.PersonDAO#insertPerson is annotated as if it should return a value, but the method is void.

What am I doing wrong here?

How to implement an audit interceptor in jdbi?

Is there a way to implement the Audit Interceptor concept from Hibernate in JDBI ? Audit Interceptor example

performing create-or-update with jdbi

For a small new project, I decided to give JDBI a try (normally I work with hibernate/jpa).

I like the lightweight, annotation based dao creation using @SqlUpdate/@SqlQuery.

But: There are situations where I can't be sure if I want to create an entity or update an existing one. I would place a "select" statement and depending on its return value use the insert or update statement.

Question: is this somehow supported by the "interface-only" dao in jdbi? Or do I have to write a "createOrUpdate" method myself (making the auto generated dao more or less obsolete)?

Thanks for any hints.

Does JDBI accept UUID parameters?

When using SQL Object argument binding, does JDBI work out-of-the-box with UUID parameters?

I have a method such as this:

@SqlQuery("EXECUTE [MyProcedure] :myField")
MyDto myMethod(@Bind("myField") UUID myField);

which is bound to a SQL Server stored procedure that receives a parameter like this:

@myField uniqueidentifier

When executed, this exception is thrown:

! com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from UNKNOWN to UNKNOWN is unsupported.
! at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
! at com.microsoft.sqlserver.jdbc.DataTypes.throwConversionError(DataTypes.java:1117)
! at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setObject(SQLServerPreparedStatement.java:991)

If I change the parameter type on JDBI to String, and call it using the toString() method from the UUID object, it works:

@SqlQuery("EXECUTE [MyProcedure] :myField")
MyDto trash(@Bind("myField") String myField);

Is there a way to write my DAO methods accepting UUID parameters and have them converted to strings before binding?

JDBI in query sql

I'm trying to do a IN query using MYSQL JDBI on Dropwizard (not relevant, I assume).

@SqlQuery("SELECT id FROM table where field in (<list>)")
List<Integer> findSomething(@BindIn("list") List<String> someList);

As suggested here, I've also annotated the class with

@UseStringTemplate3StatementLocator

But when I'm starting the application, I get the following error:

Exception in thread "main" java.lang.annotation.AnnotationFormatError: Invalid default: public abstract java.lang.Class org.skife.jdbi.v2.sqlobject.stringtemplate.UseStringTemplate3StatementLocator.errorListener()

Does anyone have a good idea on how to solve this issue?