Examples with Query org.skife.jdbi.v2.Query used on opensource projects

Example 41 with Query

use of org.skife.jdbi.v2.Query in project airpal by airbnb.

the class JobHistoryStoreDAO method getJobs.

private List<Job> getJobs(long limit, int dayInterval, String outerWhereClauseArg, String innerWhereClauseArg) {
    String outerWhereClause = Strings.isNullOrEmpty(outerWhereClauseArg) ? "true" : outerWhereClauseArg;
    String innerWhereClause = Strings.isNullOrEmpty(innerWhereClauseArg) ? "true" : innerWhereClauseArg;
    try (Handle handle = dbi.open()) {
        Query<Map<String, Object>> query = handle.createQuery("SELECT " + "j.id AS id, " + "j.query AS query, " + "j.user AS user, " + "j.uuid AS uuid, " + "j.queryStats as queryStats, " + "j.state AS state, " + "j.columns AS columns, " + "j.query_finished AS queryFinished, " + "j.query_started AS queryStarted, " + "j.error AS error, " + "t.connector_id AS connectorId, " + "t.schema_ AS \"schema\", " + "t.table_ AS \"table\", " + "t.columns, " + "jo.type, " + "jo.description, " + "jo.location " + "FROM (SELECT * FROM jobs " + "WHERE " + Util.getQueryFinishedCondition(dbType) + " " + "AND " + innerWhereClause + " " + "ORDER BY query_finished DESC LIMIT :limit) j " + "LEFT OUTER JOIN job_tables jt ON j.id = jt.job_id " + "LEFT OUTER JOIN tables t ON jt.table_id = t.id " + "LEFT OUTER JOIN job_outputs jo ON j.id = jo.job_id " + "WHERE " + outerWhereClause + " " + "ORDER BY query_finished DESC").bind("limit", limit).bind("day_interval", dayInterval);
        Map<Long, Job> idToJobMap = query.map(RosettaResultSetMapperFactory.mapperFor(JobTableOutputJoinRow.class)).fold(new HashMap<Long, Job>(), new JobTableOutputJoinRow.JobFolder());
        return new ArrayList<>(idToJobMap.values());
    }
}

Example 42 with Query

use of org.skife.jdbi.v2.Query in project dropwizard by dropwizard.

the class GuavaJDBITest method createsAValidDBI.

@Test
public void createsAValidDBI() throws Exception {
    final Handle handle = dbi.open();
    final Query<String> names = handle.createQuery("SELECT name FROM people WHERE age < ?").bind(0, 50).map(StringColumnMapper.INSTANCE);
    assertThat(names).containsOnly("Coda Hale", "Kris Gale");
}

Example 43 with Query

use of org.skife.jdbi.v2.Query in project dropwizard by dropwizard.

the class JDBITest method createsAValidDBI.

@Test
public void createsAValidDBI() throws Exception {
    final Handle handle = dbi.open();
    final Query<String> names = handle.createQuery("SELECT name FROM people WHERE age < ?").bind(0, 50).map(StringColumnMapper.INSTANCE);
    assertThat(names).containsOnly("Coda Hale", "Kris Gale");
}

Example 44 with Query

use of org.skife.jdbi.v2.Query in project druid by druid-io.

the class JDBCExtractionNamespaceCacheFactory method populateCache.

@Override
@Nullable
public CacheScheduler.VersionedCache populateCache(final JDBCExtractionNamespace namespace, final CacheScheduler.EntryImpl<JDBCExtractionNamespace> entryId, final String lastVersion, final CacheScheduler scheduler) {
    final long lastCheck = lastVersion == null ? JodaUtils.MIN_INSTANT : Long.parseLong(lastVersion);
    final Long lastDBUpdate = lastUpdates(entryId, namespace);
    if (lastDBUpdate != null && lastDBUpdate <= lastCheck) {
        return null;
    }
    final long dbQueryStart = System.currentTimeMillis();
    final DBI dbi = ensureDBI(entryId, namespace);
    final String table = namespace.getTable();
    final String valueColumn = namespace.getValueColumn();
    final String keyColumn = namespace.getKeyColumn();
    LOG.debug("Updating %s", entryId);
    final List<Pair<String, String>> pairs = dbi.withHandle(new HandleCallback<List<Pair<String, String>>>() {

        @Override
        public List<Pair<String, String>> withHandle(Handle handle) throws Exception {
            final String query;
            query = String.format("SELECT %s, %s FROM %s", keyColumn, valueColumn, table);
            return handle.createQuery(query).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();
        }
    });
    final String newVersion;
    if (lastDBUpdate != null) {
        newVersion = lastDBUpdate.toString();
    } else {
        newVersion = String.format("%d", dbQueryStart);
    }
    final CacheScheduler.VersionedCache versionedCache = scheduler.createVersionedCache(entryId, newVersion);
    try {
        final Map<String, String> cache = versionedCache.getCache();
        for (Pair<String, String> pair : pairs) {
            cache.put(pair.lhs, pair.rhs);
        }
        LOG.info("Finished loading %d values for %s", cache.size(), entryId);
        return versionedCache;
    } catch (Throwable t) {
        try {
            versionedCache.close();
        } catch (Exception e) {
            t.addSuppressed(e);
        }
        throw t;
    }
}

Example 45 with Query

use of org.skife.jdbi.v2.Query in project pac4j by pac4j.

the class DbProfileService method query.

protected List<Map<String, Object>> query(final String query, final String key, final String value) {
    Handle h = null;
    try {
        h = dbi.open();
        logger.debug("Query: {} for key/value: {} / {}", query, key, value);
        return h.createQuery(query).bind(key, value).list(2);
    } finally {
        if (h != null) {
            h.close();
        }
    }
}