@Override public List<Pair<String, String>> withHandle(Handle handle) { return handle .createQuery( buildLookupQuery(table, filter, keyColumn, valueColumn) ).map( new ResultSetMapper<Pair<String, String>>() { @Override public Pair<String, String> map( final int index, final ResultSet r, final StatementContext ctx ) throws SQLException { return new Pair<>(r.getString(keyColumn), r.getString(valueColumn)); } } ).list(); } }
@Override public Timestamp withHandle(Handle handle) { final String query = StringUtils.format( "SELECT MAX(%s) FROM %s", tsColumn, table ); return handle .createQuery(query) .map(TimestampMapper.FIRST) .first(); } }
@Test public void testMapEnumValues() throws Exception { Handle h = openHandle(); h.createStatement("insert into something (id, name) values (1, 'eric')").execute(); h.createStatement("insert into something (id, name) values (2, 'brian')").execute(); List<SomethingElse> results = h.createQuery("select * from something order by id") .map(SomethingElse.class) .list(); assertEquals(SomethingElse.Name.eric, results.get(0).name); assertEquals(SomethingElse.Name.brian, results.get(1).name); }
@Test public void testFluentApi() throws Exception { Map<String, Team> teams = handle.createQuery("select t.name as teamName, " + " t.mascot as mascot, " + " p.name as personName, " + " p.role as role " + "from team t inner join person p on (t.name = p.team)") .map(TeamPersonJoinRow.class) .fold(Maps.<String, Team>newHashMap(), new TeamFolder()); assertThat(teams, equalTo(expected)); }
@Test public void testMapEnumValues() throws Exception { Handle h = openHandle(); h.createStatement("insert into something (id, name) values (1, 'eric')").execute(); h.createStatement("insert into something (id, name) values (2, 'brian')").execute(); List<SomethingElse> results = h.createQuery("select * from something order by id") .map(SomethingElse.class) .list(); assertEquals(SomethingElse.Name.eric, results.get(0).name); assertEquals(SomethingElse.Name.brian, results.get(1).name); }
@Test public void testFluentApi() throws Exception { Map<String, Team> teams = handle.createQuery("select t.name as teamName, " + " t.mascot as mascot, " + " p.name as personName, " + " p.role as role " + "from team t inner join person p on (t.name = p.team)") .map(TeamPersonJoinRow.class) .fold(Maps.<String, Team>newHashMap(), new TeamFolder()); assertThat(teams, equalTo(expected)); }
@Override public boolean tableExists(final Handle handle, final String tableName) { return !handle.createQuery( "SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname = 'public' AND tablename ILIKE :tableName" ) .bind("tableName", tableName) .map(StringMapper.FIRST) .list() .isEmpty(); }
@Override public Optional<StatusType> withHandle(Handle handle) throws Exception { byte[] res = handle.createQuery( StringUtils.format("SELECT status_payload FROM %s WHERE id = :id", entryTable) ) .bind("id", entryId) .map(ByteArrayMapper.FIRST) .first(); return Optional.fromNullable( res == null ? null : jsonMapper.readValue(res, statusType) ); } }
@Override public List<TaskInfo<EntryType, StatusType>> getCompletedTaskInfo( DateTime timestamp, @Nullable Integer maxNumStatuses, @Nullable String dataSource ) { return getConnector().retryWithHandle( handle -> { final Query<Map<String, Object>> query = createCompletedTaskInfoQuery( handle, timestamp, maxNumStatuses, dataSource ); return query.map(taskInfoMapper).list(); } ); }
@Test public void testAPIWorks() throws Exception { Spiffy s = SqlObjectBuilder.onDemand(dbi, Spiffy.class); s.insert(7, "Bill"); String bill = handle.createQuery("select name from something where id = 7").map(StringMapper.FIRST).first(); assertEquals("Bill", bill); }
@Test public void testDefines() throws Exception { handle.attach(Kangaroo.class).weirdInsert("something", "id", "name", 5, "Bouncer"); String name = handle.createQuery("select name from something where id = 5") .map(StringMapper.FIRST) .first(); assertThat(name, equalTo("Bouncer")); }
@Override public List<String> inTransaction(Handle handle, TransactionStatus status) { return handle.createQuery(reverseFetchQuery) .bind("val", value) .map(StringMapper.FIRST) .list(); } });
@Override public Optional<EntryType> withHandle(Handle handle) throws Exception { byte[] res = handle.createQuery( StringUtils.format("SELECT payload FROM %s WHERE id = :id", entryTable) ) .bind("id", entryId) .map(ByteArrayMapper.FIRST) .first(); return Optional.fromNullable( res == null ? null : jsonMapper.readValue(res, entryType) ); } }
@Test public void testListWithMaxRows() throws Exception { h.prepareBatch("insert into something (id, name) values (:id, :name)") .add(1, "Brian") .add(2, "Keith") .add(3, "Eric") .execute(); assertEquals(1, h.createQuery("select id, name from something").map(Something.class).list(1).size()); assertEquals(2, h.createQuery("select id, name from something").map(Something.class).list(2).size()); }
@Test public void testBaz() throws Exception { Wombat wombat = handle.attach(Wombat.class); wombat.insert(new Something(7, "Henning")); String name = handle.createQuery("select name from something where id = 7") .map(StringMapper.FIRST) .first(); assertThat(name, equalTo("Henning")); }
@Override public boolean tableExists(final Handle handle, final String tableName) { return !handle.createQuery("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = :tableName") .bind("tableName", tableName) .map(StringMapper.FIRST) .list() .isEmpty(); }
@Override @Nullable public TaskInfo<EntryType, StatusType> getTaskInfo(String entryId) { return connector.retryWithHandle(handle -> { final String query = StringUtils.format( "SELECT id, status_payload, payload, datasource, created_date FROM %s WHERE id = :id", entryTable ); return handle.createQuery(query) .bind("id", entryId) .map(taskInfoMapper) .first(); }); }
@Test public void testBindConstantValue() throws Exception { UsesBatching b = handle.attach(UsesBatching.class); List<Integer> ids = Arrays.asList(1, 2, 3, 4, 5); b.withConstantValue(ids, "Johan"); assertThat(b.size(), equalTo(5)); List<String> names = handle.createQuery("select distinct name from something") .map(StringMapper.FIRST) .list(); assertThat(names, equalTo(Arrays.asList("Johan"))); }