How to use

DBI

in

org.skife.jdbi.v2

 @Override
 protected void run(Bootstrap<WhisperServerConfiguration> bootstrap,
           Namespace namespace,
           WhisperServerConfiguration config)
   throws Exception
 {
  DataSourceFactory messageDbConfig = config.getMessageStoreConfiguration();
  DBI               messageDbi      = new DBI(messageDbConfig.getUrl(), messageDbConfig.getUser(), messageDbConfig.getPassword());

  messageDbi.registerArgumentFactory(new OptionalArgumentFactory(messageDbConfig.getDriverClass()));
  messageDbi.registerContainerFactory(new ImmutableListContainerFactory());
  messageDbi.registerContainerFactory(new ImmutableSetContainerFactory());
  messageDbi.registerContainerFactory(new OptionalContainerFactory());

  Messages messages  = messageDbi.onDemand(Messages.class);
  long     timestamp = System.currentTimeMillis() - TimeUnit.DAYS.toMillis(90);

  logger.info("Trimming old messages: " + timestamp + "...");
  messages.removeOld(timestamp);

  Thread.sleep(3000);
  System.exit(0);
 }
}

public void tearDown()
{
 try {
  new DBI(jdbcUri + ";drop=true").open().close();
 }
 catch (UnableToObtainConnectionException e) {
  SQLException cause = (SQLException) e.getCause();
  // error code "08006" indicates proper shutdown
  Assert.assertEquals(StringUtils.format("Derby not shutdown: [%s]", cause.toString()), "08006", cause.getSQLState());
 }
}

  this.valueColumn
);
dbi = new DBI(
  connectorConfig.getConnectURI(),
  connectorConfig.getUser(),
  connectorConfig.getPassword()
);
dbi.registerMapper(new KeyValueResultSetMapper(keyColumn, valueColumn));

@Inject
public SQLServerConnector(Supplier<MetadataStorageConnectorConfig> config, Supplier<MetadataStorageTablesConfig> dbTables)
{
 super(config, dbTables);
 final BasicDataSource datasource = getDatasource();
 datasource.setDriverClassLoader(getClass().getClassLoader());
 datasource.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
 this.dbi = new DBI(datasource);
 this.dbi.setStatementRewriter(new CustomStatementRewriter());
 log.info("Configured Sql Server as metadata storage");
}

dbi.registerArgumentFactory(new GuavaOptionalArgumentFactory(driverClazz));
dbi.registerArgumentFactory(new OptionalArgumentFactory(driverClazz));
dbi.registerArgumentFactory(new OptionalDoubleArgumentFactory());
dbi.registerArgumentFactory(new OptionalIntArgumentFactory());
dbi.registerArgumentFactory(new OptionalLongArgumentFactory());
dbi.registerColumnMapper(new OptionalDoubleMapper());
dbi.registerColumnMapper(new OptionalIntMapper());
dbi.registerColumnMapper(new OptionalLongMapper());
dbi.registerContainerFactory(new ImmutableListContainerFactory());
dbi.registerContainerFactory(new ImmutableSetContainerFactory());
dbi.registerContainerFactory(new GuavaOptionalContainerFactory());
dbi.registerContainerFactory(new OptionalContainerFactory());
dbi.registerArgumentFactory(new JodaDateTimeArgumentFactory(timeZone));
dbi.registerArgumentFactory(new LocalDateArgumentFactory());
dbi.registerArgumentFactory(new LocalDateTimeArgumentFactory());
dbi.registerArgumentFactory(new InstantArgumentFactory(timeZone));
dbi.registerArgumentFactory(new OffsetDateTimeArgumentFactory(timeZone));
dbi.registerArgumentFactory(new ZonedDateTimeArgumentFactory(timeZone));
dbi.registerArgumentFactory(new GuavaOptionalJodaTimeArgumentFactory(timeZone));
dbi.registerArgumentFactory(new GuavaOptionalLocalDateArgumentFactory());
dbi.registerArgumentFactory(new GuavaOptionalLocalDateTimeArgumentFactory());
dbi.registerArgumentFactory(new GuavaOptionalInstantArgumentFactory(timeZone));
dbi.registerArgumentFactory(new GuavaOptionalOffsetTimeArgumentFactory(timeZone));
dbi.registerArgumentFactory(new GuavaOptionalZonedTimeArgumentFactory(timeZone));
dbi.registerArgumentFactory(new OptionalJodaTimeArgumentFactory(timeZone));
dbi.registerArgumentFactory(new OptionalLocalDateArgumentFactory());

@SuppressWarnings({"unchecked", "rawtypes"})
@Override
public void configure(final Env env, final Config config, final Binder binder) {
 Key<DataSource> dskey = Key.get(DataSource.class, Names.named(name));
 DataSource ds = env.get(dskey)
   .orElseThrow(() -> new NoSuchElementException("DataSource missing: " + dskey));
 DBI dbi = new DBI2(ds);
 dbi.setSQLLog(new SLF4JLog());
 dbi.registerArgumentFactory(new OptionalArgumentFactory());
 dbi.registerArgumentFactory(new IterableArgumentFactory());
 dbi.registerContainerFactory(new OptionalContainerFactory());
 dbi.setStatementRewriter(new ExpandedStmtRewriter());
 ServiceKey serviceKey = env.serviceKey();
 serviceKey.generate(DBI.class, name, k -> binder.bind(k).toInstance(dbi));
 serviceKey.generate(Handle.class, name, k -> binder.bind(k).toProvider(() -> dbi.open()));
 sqlObjects.forEach(sqlObject -> binder.bind(sqlObject)
   .toProvider((Provider) () -> dbi.open(sqlObject)));
 if (callback != null) {
  callback.accept(dbi, config);
 }
}

@Before
public void setUp() throws Exception
{
  JdbcDataSource ds = new JdbcDataSource();
  ds.setURL("jdbc:h2:mem:" + UUID.randomUUID());
  dbi = new DBI(ds);
  handle = dbi.open();
  handle.execute("create table something (id int primary key, name varchar(100))");
}

@Before
public void setUp() {
 dbi = new DBI("jdbc:h2:mem:" + UUID.randomUUID());
 handle = dbi.open();
 handle.createStatement("create table foo (id int, bar varchar(100) default null);").execute();
 dao = dbi.onDemand(MyDAO.class);
}

@Test
public void testFiveMinuteSqlObjectExample() throws Exception
{
  DBI dbi = new DBI("jdbc:h2:mem:" + UUID.randomUUID());
  MyDAO dao = dbi.open(MyDAO.class);
  dao.createSomethingTable();
  dao.insert(2, "Aaron");
  String name = dao.findNameById(2);
  assertThat(name, equalTo("Aaron"));
  dao.close();
}

@Before
public void createTestData() throws Exception {
 Handle handle = dbiProvider.get().open();
 Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(new JdbcConnection(handle.getConnection()));
 Liquibase liquibase = new Liquibase("singularity_test.sql", new FileSystemResourceAccessor(), database);
 liquibase.update((String) null);
 try {
  database.close();
 } catch (Throwable t) {
 }
 handle.close();
}

@Test
public void testRegisterMapperAnnotationWorks() throws Exception
{
  Kabob bob = dbi.onDemand(Kabob.class);
  bob.insert(1, "Henning");
  Something henning = bob.find(1);
  assertThat(henning, equalTo(new Something(1, "Henning")));
}

@Test
public void testNoErrorOnNoData() throws Exception
{
  Kabob bob = dbi.onDemand(Kabob.class);
  Something henning = bob.find(1);
  assertThat(henning, nullValue());
  List<Something> rs = bob.listAll();
  assertThat(rs.isEmpty(), equalTo(true));
  Iterator<Something> itty = bob.iterateAll();
  assertThat(itty.hasNext(), equalTo(false));
}

@Test
public void testBatch() throws Exception
{
  DAO dao = dbi.open(DAO.class);
  int[] ids = dao.insert(Arrays.asList("Burt", "Macklin"));
  assertThat(dao.findNameById(ids[0]), equalTo("Burt"));
  assertThat(dao.findNameById(ids[1]), equalTo("Macklin"));
  dao.close();
}

@Test
@Category(JDBITests.class)
public void testRegisterOnDBI() throws Exception
{
  dbi.registerArgumentFactory(new NameAF());
  Handle h2 = dbi.open();
  h2.createStatement("insert into something (id, name) values (:id, :name)")
   .bind("id", 7)
   .bind("name", new Name("Brian", "McCallister"))
   .execute();
  String full_name = h.createQuery("select name from something where id = 7").map(StringMapper.FIRST).first();
  assertThat(full_name, equalTo("Brian McCallister"));
  h2.close();
}

@Test
public void testRegisterOnDBI() throws Exception
{
  dbi.registerArgumentFactory(new NameAF());
  Handle h2 = dbi.open();
  h2.createStatement("insert into something (id, name) values (:id, :name)")
   .bind("id", 7)
   .bind("name", new Name("Brian", "McCallister"))
   .execute();
  String full_name = h.createQuery("select name from something where id = 7").mapTo(String.class).first();
  assertThat(full_name, equalTo("Brian McCallister"));
  h2.close();
}

@Test
public void testTxActuallyCommits() throws Exception
{
  Handle h2 = dbi.open();
  Dao one = handle.attach(Dao.class);
  Dao two = h2.attach(Dao.class);
  // insert in @Transaction method
  Something inserted = one.insertAndFetch(1, "Brian");
  // fetch from another connection
  Something fetched = two.findById(1);
  assertThat(fetched, equalTo(inserted));
}

  @Override
  public List<MaterializedView> getMaterializedViews(String project) {
    try (Handle handle = dbi.open()) {
      return handle.createQuery("SELECT name, query, table_name, update_interval, last_updated, incremental, real_time, options " +
          "from materialized_views WHERE project = :project")
          .bind("project", project).map(materializedViewMapper).list();
    }
  }
}

public RealTimeReport get(String project, String tableName) {
  try (Handle handle = dbi.open()) {
    return handle.createQuery("SELECT name, measures, table_name, collections, filter, dimensions FROM realtime_reports " +
        " WHERE project = :project AND table_name = :tableName")
        .bind("project", project)
        .bind("tableName", tableName)
        .map(mapper)
        .first();
  }
}

  private static DBI createDbi()
  {
    String user = System.getenv("POSTGRES_USER");
    String pass = System.getenv("POSTGRES_PASS");
    String url = System.getenv("POSTGRES_URL");

    assumeThat(user, notNullValue());
//        assumeThat(pass, notNullValue());
    assumeThat(url, notNullValue());

    org.postgresql.Driver.getVersion();
    return new DBI(url, user, pass);
  }

@Test
public void testTransactionPropagates() throws Exception
{
  Foo foo = dbi.onDemand(Foo.class);
  try {
    foo.insertAndFail(1, "Jeff");
    fail("should have raised an exception");
  }
  catch (Exception e){}
  Something n = foo.createBar().findById(1);
  assertThat(n, nullValue());
}