What is the difference between JDBC and JDBI?

I want to know about t开发者_JS百科he differences between JDBC and JDBI in java. In particular, which one is generally better and why?

(I am the primary author of jDBI)

jDBI is a convenience library built on top of JDBC. JDBC works very well but generally seems to optimize for the database vendors (driver writers) over the users. jDBI attempts to expose the same functionality, but in an API optimized for users.

It is much lower level than things like Hibernate or JPA. The closest similar library is probably MyBatis (forked successor to iBATIS).

jDBI supports two style APIs, an older fluent style, which looks like:

List<Something> r = h.createQuery("select * from something where name = :name and id = :id")
                .bind(0, "eric")
                .bind("id", 1)
                .map(Something.class)
                .list();

A newer SQL Object API does much more reflective type stuff and really does start to abstract a bunch of JDBC stuff:

interface TheBasics
{
    @SqlUpdate("insert into something (id, name) values (:id, :name)")
    int insert(@BindBean Something something);

    @SqlQuery("select id, name from something where id = :id")
    Something findById(@Bind("id") long id);
}

@Test
public void useTheBasics() throws Exception
{
    TheBasics dao = dbi.onDemand(TheBasics.class);

    dao.insert(new Something(7, "Martin"));

    Something martin = dao.findById(7);
}

The library has good reference docs (javadoc) and some reasonable tutorial style documentation at http://jdbi.org/. It has been around since 2004, and is used by a relatively small number of folks (some few dozen people I know of personally, and maybe a dozen companies) but it works very well for them. Most of the folks who work on it are A+ folks, and are primarily concerned with building a tool that works well for them -- that it is open source is largely a side effect.

JDBC is a long-established standard used in Java to access SQL databases. DB Vendors implement a JDBC driver so that all DBs can be accessed in a uniform manner. Practically everything done with databases in Java uses JDBC.

JDBI seems to be some sort of abstraction layer on top of JDBC, but it's hard to tell since it's poorly documented. It's certainly not widely used and this is the first time I've heard of it.

Do you mean https://jdbi.org ?

jDBI is designed to provide convenient tabular data access in Java(tm). It uses the Java collections framework for query results, provides a convenient means of externalizing sql statements, and provides named parameter support for any database being used.

JDBI uses JDBC, if you don't know if you need JDBI, I would suggest you don't use it.

jDBI is built on top of JDBC. All Java applications use JDBC to access relational databases, so it's not an either/or choice. They're complimentary. You can't use jDBI without JDBC.

With that said, jDBI is another person's attempt to relieve Java developers from the boilerplate required by JDBC. It's like choosing Hibernate or TopLink or iBatis.

Indeed, JDBI is built on top of JDBC, actually, most likely you will use JDBC to reach the DB, and JDBI will be the one that embraces(or wrapped) JDBC to get the PreparedStatements executed against the DB.

Internally, the JDBC Driver is the one that executes the transaction, JDBI just works as an Intermediary.

It's lighter than an ORM (like Hibernate or Spring), but it really helps to speed up development and have everything more, "nice and clean", since it has a lot of utilities to make coding easier and cleaner, for example:

To define a simple object to insert/read a table, you can do it like this:

import com.sql.poc.data.jDBI.map.AgentMapper;
import com.sql.poc.domain.Agent;
import org.skife.jdbi.v2.sqlobject.Bind;
import org.skife.jdbi.v2.sqlobject.SqlQuery;
import org.skife.jdbi.v2.sqlobject.SqlUpdate;
import org.skife.jdbi.v2.sqlobject.customizers.Mapper;
import org.skife.jdbi.v2.sqlobject.mixins.Transactional;

public interface SqlObjectDataAccess extends Transactional<SqlObjectDataAccess> {

    @SqlUpdate("INSERT INTO pocAgent (LocationId, Name, Country) VALUES (:id, :name, :country)")
    void insertAgent(@Bind("id") String locationId,
                     @Bind("name") String name,
                     @Bind("country") String country);

    @SqlQuery("SELECT LOCATIONID, NAME, COUNTRY, CREATEDON FROM pocAgent WHERE LOCATIONID = :LocationId")
    @Mapper(AgentMapper.class)
    Agent getAgentByLocation(@Bind("LocationId") String locationId);

    void close();    
}

JDBI gives you the facility to have all mapping logic in the same place, such as:

import com.sql.poc.domain.Agent;
import org.skife.jdbi.v2.StatementContext;
import org.skife.jdbi.v2.tweak.ResultSetMapper;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AgentMapper implements ResultSetMapper<Agent> {
    @Override
    public Agent map(int index, ResultSet r, StatementContext ctx) throws SQLException {
        return new Agent(r.getString("LocationId"),
                r.getString("Name"),
                r.getString("Country"),
                r.getDate("CreatedOn"));
    }
}

And then you just need to use the DAO (Data Access Object):

import com.google.inject.Inject;
import com.sql.poc.IConnectionHelper;
import com.sql.poc.domain.Agent;
import org.skife.jdbi.v2.DBI;
import org.skife.jdbi.v2.logging.Log4JLog;

public class SqlObjectRepository {
    IConnectionHelper _connectionHelper;
    DBI _dbiInstance;
    SqlObjectDataAccess _daoHandler;

    @Inject
    SqlObjectRepository() {
        _dbiInstance = new DBI(_connectionHelper.getDataSource());
        _dbiInstance.setSQLLog(new Log4JLog());
    }

    public void openConnection() {
        if (_daoHandler == null)
            _daoHandler = _dbiInstance.open(SqlObjectDataAccess.class);
    }

    @org.skife.jdbi.v2.sqlobject.Transaction
    public Agent insertAgent(String locationId, String name, String country) {
        openConnection();
        Agent agent = _daoHandler.getAgentByLocation(locationId);
        if (agent == null) {
            _daoHandler.insertAgent(locationId, name, country);
        }
        agent = _daoHandler.getAgentByLocation(locationId);
        _daoHandler.commit();
        return agent;
    }
}

Then, if we go a bit deeper, and check how the Connection is done to the DB, you will notice that, for this Proof of Concept sample JDBC is used:

import com.google.inject.Inject;
import com.sql.poc.IConnectionHelper;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.impl.GenericObjectPool;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class TandemMPConnectionHelper implements IConnectionHelper {
    private final DataSource _dataSource;

    @Inject
    TandemMPConnectionHelper() {
        try {
            Class.forName("com.tandem.t4jdbc.SQLMXDriver");
        } catch (ClassNotFoundException e) {
            System.out.println(e.toString());
        }
        _dataSource = setupDataSource("jdbc:t4sqlmx://<server>:<port>/:<username>:<password>:", "user1", "password1");
    }

    @Override
    public DataSource setupDataSource(String connectURI, String userName, String password) {
        GenericObjectPool connectionPool = new GenericObjectPool();
        connectionPool.setMaxActive(20);
        ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
                connectURI,
                userName,
                password);
        new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, false);
        return new PoolingDataSource(connectionPool);
    }

    @Override
    public DataSource getDataSource() {
        return _dataSource;
    }

    @Override
    public Connection getConnection() {
        Connection connection;
        try {
            connection = _dataSource.getConnection();
            connection.setAutoCommit(false);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }
        return connection;
    }
}

In this case, I'm reaching a Tandem Non/Stop database, but the same works for SQL Server, ORACLE, or whatever db you are reaching, you just need the correct JDBC Driver (that you can find easily, just Google it!).

Hope it give you a clearer idea where, conceptually, locate JDBI and JDBC in your code.

Like most of the people already answered, JDBI is a convenience library on top of JDBC which is not particularly user-friendly and is excessively low-level for most applications.

From my personal experience, JDBI sits in a sweet stop between fully-fledged ORMs and do-everything-yourself JDBC. That is, I believe it covers 95% of whatever an average developer might need by striking a balance between control (you can easily tune your SQL) and convenience (you productivity increases dramatically compared to JDBC).

Of course, a picture is worth a thousand words. This is a DAO method fetching a row and converting it into an object hierarchy by means of JDBI:

   @Override
    public Widget fetchWidget(String widgetGuid) {
        Widget widget = jdbi.withHandle(
            handle ->  {
                return handle.createQuery(
                    "SELECT guid, x AS p_x, y AS p_y, width, height, zindex FROM widget WHERE guid = :widgetGuid"
                ).bind("widgetGuid", widgetGuid)
                .registerRowMapper(ConstructorMapper.factory(Widget.class))
                .mapTo(Widget.class)
                .one();
            }
        );
        return widget;
    }

Imagine doing the same with JDBC - doable, but so tedious... And JDBI can do much more than that - check its SqlObject API, for example.

I found jDBI when i search named SQL parameters. I use known competitor Spring JDBC NamedTemplate, but has strange dependencies for 8-10MB. I have already dependency to ANTLR.

I look since few hours, jDBI seems inspiring. Both (jDBI/Spring JDBC) can be compared to some degree with light ORM like iBatis/MyBatis etc.