org.apache.commons.dbutils.ResultSetHandler Java Examples

/**
 * 将查询结果集转换成键值对列表返回。
 * 
 * @param ds JDBC连接池
 */
public void queryMapList(DataSource ds) {
    String sql = "select userId, userName, gender, age from student";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<List<Map<String, Object>>> handler = new MapListHandler();
    List<Map<String, Object>> result = null;
    try {
        result = run.query(sql, handler);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    if (null == result) {
        return;
    }
    for (Map<String, Object> map : result) {
        System.out.println(map);
    }
}
 

/**
 * 将查询结果集转换成Bean列表返回。
 * 
 * @param ds JDBC连接池
 */
public void queryBeanList(DataSource ds) {
    String sql = "select userId, userName, gender, age from student";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<List<Student>> handler = new BeanListHandler<Student>(Student.class);
    List<Student> result = null;
    try {
        result = run.query(sql, handler);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    if (null == result) {
        return;
    }
    for (Student student : result) {
        System.out.println(student);
    }
}
 

public static ResultSetHandler<List<Map<String, Object>>>
        mkResultSetHandler(String... names) {
    return (rs) -> {
        try {
            int columnCount = rs.getMetaData().getColumnCount();
            List<Map<String, Object>> result = new ArrayList<>();
            while (rs.next()) {
                Object[] data = readOneRow(rs, columnCount);
                result.add(arrayToMap(data, names));
            }
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    };
}
 

public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);
		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------
		ResultSetHandler<Employee> resultHandler = new BeanHandler<Employee>(
				Employee.class);

		try {
			Employee emp = run.query(conn,
					"SELECT * FROM employee WHERE employeename=?",
					resultHandler, "Jose");
			System.out.println(emp.getEmployeeId());
		} finally {
			DbUtils.close(conn);
		}

	}
 

public static void main(String[] args) throws SQLException {

		final String url = "jdbc:h2:./target/test;AUTO_SERVER=TRUE";
		final String driver = "org.h2.Driver";
		final String usr = "sa";
		final String pwd = "";

		QueryRunner run = new QueryRunner();

		DbUtils.loadDriver(driver);

		Connection conn = DriverManager.getConnection(url, usr, pwd);
		// -----------------------------------------------------------------------------------
		ResultSetHandler<List<Employee>> resultListHandler = new BeanListHandler<Employee>(
				Employee.class);

		try {
			List<Employee> empList = run.query(conn, "SELECT * FROM employee",
					resultListHandler);
			System.out.println(empList);
		} finally {
			DbUtils.close(conn);
		}

	}
 

/**
 * Executes the given {@link org.amforeas.jdbc.DynamicFinder} object.
 * @param database database name or schema where to execute the {@link org.amforeas.jdbc.DynamicFinder}
 * @param df an instance of {@link org.amforeas.jdbc.DynamicFinder}
 * @param limit an instance of {@link amforeas.jdbc.LimitParam}
 * @param order an instance of {@link amforeas.jdbc.OrderParam}
 * @param params a vararg of Object instances used as parameters for the QueryRunner.
 * @return a List of {@link amforeas.rest.xstream.Row} with the records found by the DynamicFinder.
 * @throws SQLException from the QueryRunner
 * @see org.apache.commons.dbutils.QueryRunner
 * @see amforeas.sql.dialect.Dialect
 */
public List<Row> find (final String database, final DynamicFinder df, final LimitParam limit, final OrderParam order, Object... params) throws SQLException {
    l.debug(df.getSql());
    l.debug(AmforeasUtils.varargToString(params));

    final DatabaseConfiguration dbconf = this.factory.getConfiguration().getDatabaseConfiguration(database);
    final Dialect dialect = this.factory.getDialectFactory().getDialect(dbconf);
    final String query = dialect.toStatementString(df, limit, order);

    final QueryRunner run = this.factory.getJDBCConnectionFactory().getQueryRunner(dbconf);
    final ResultSetHandler<List<Row>> res = new AmforeasResultSetHandler(true);
    try {
        List<Row> results = run.query(query, res, params);
        l.debug("Received {} results.", results.size());
        return results;
    } catch (SQLException ex) {
        l.debug(ex.getMessage());
        throw ex;
    }
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getResponseStats(java.lang.String, java.lang.String, java.lang.String, io.apiman.manager.api.beans.metrics.HistogramIntervalType, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public ResponseStatsHistogramBean getResponseStats(String organizationId, String apiId, String version,
        HistogramIntervalType interval, DateTime from, DateTime to) {
    ResponseStatsHistogramBean rval = new ResponseStatsHistogramBean();
    Map<Long, ResponseStatsDataPoint> index = generateHistogramSkeleton(rval, from, to, interval, ResponseStatsDataPoint.class, Long.class);
    
    try {
        QueryRunner run = new QueryRunner(ds);
        String gbColumn = groupByColumn(interval);
        String sql = "SELECT " + gbColumn + ", resp_type, count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY resp_type," + gbColumn; //$NON-NLS-1$ //$NON-NLS-2$
        ResultSetHandler<ResponseStatsHistogramBean> handler = new ResponseStatsHistogramHandler(rval, index);
        run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    return rval;
}
 

/**
 * Executes a given {@link amforeas.sql.Select} object and returns the metadata associated to the results.
 * @param select a {@link amforeas.sql.Select} instance which should only retrieve one result.
 * @return a List of {@link amforeas.rest.xstream.Row} with the metadata obtained 
 * with the {@link amforeas.sql.Select} statement
 * @throws SQLException SQLException from the QueryRunner
 * @see org.apache.commons.dbutils.QueryRunner
 * @see amforeas.handler.ResultSetMetaDataHandler
 */
public List<Row> getTableMetaData (final Select select) throws SQLException {
    l.debug("Obtaining metadata from table " + select.toString());

    final ResultSetHandler<List<Row>> res = new ResultSetMetaDataHandler();

    final DatabaseConfiguration dbconf = this.factory.getConfiguration().getDatabaseConfiguration(select.getTable().getDatabase());
    final QueryRunner run = this.factory.getJDBCConnectionFactory().getQueryRunner(dbconf);
    final Dialect dialect = this.factory.getDialectFactory().getDialect(dbconf);

    try {
        List<Row> results = run.query(dialect.toStatementString(select), res);
        l.debug("Received {} results.", results.size());
        return results;
    } catch (SQLException ex) {
        l.debug(ex.getMessage());
        throw ex;
    }
}
 

/**
 * For a given database or schema, execute the statement returned by the {@link amforeas.sql.dialect.Dialect} listOfTablesStatement()
 * method and return a List of {@link amforeas.rest.xstream.Row} with all the tables available.
 * @param database the name of the database to query.
 * @return a List of {@link amforeas.rest.xstream.Row} with all the tables available.
 * @throws SQLException 
 */
public List<Row> getListOfTables (final String database) throws SQLException {
    l.debug("Obtaining the list of tables for the database {}", database);

    final ResultSetHandler<List<Row>> res = new AmforeasResultSetHandler(true);
    final DatabaseConfiguration dbconf = this.factory.getConfiguration().getDatabaseConfiguration(database);
    final Dialect dialect = this.factory.getDialectFactory().getDialect(dbconf);
    final QueryRunner run = this.factory.getJDBCConnectionFactory().getQueryRunner(dbconf);

    try {
        List<Row> results = run.query(dialect.listOfTablesStatement(), res);
        l.debug("Received {} results.", results.size());
        return results;
    } catch (SQLException ex) {
        throw ex;
    }
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getUsage(java.lang.String, java.lang.String, java.lang.String, io.apiman.manager.api.beans.metrics.HistogramIntervalType, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public UsageHistogramBean getUsage(String organizationId, String apiId, String version,
        HistogramIntervalType interval, DateTime from, DateTime to) {
    UsageHistogramBean rval = new UsageHistogramBean();
    Map<Long, UsageDataPoint> index = generateHistogramSkeleton(rval, from, to, interval, UsageDataPoint.class, Long.class);
    
    try {
        QueryRunner run = new QueryRunner(ds);
        String gbColumn = groupByColumn(interval);
        String sql = "SELECT " + gbColumn + ", count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY " + gbColumn; //$NON-NLS-1$ //$NON-NLS-2$
        ResultSetHandler<UsageHistogramBean> handler = new UsageHistogramHandler(rval, index);
        run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
    }
    
    return rval;
}
 

/**
 *
 * Executes the query and reattempt to execute the query, after
 */
protected <T extends XyzResponse> T executeQueryWithRetry(SQLQuery query, ResultSetHandler<T> handler, boolean useReadReplica) throws SQLException {
    try {
        return executeQuery(query, handler, useReadReplica ? readDataSource : dataSource);
    } catch (Exception e) {
        try {
            if (retryCausedOnServerlessDB(e) || canRetryAttempt()) {
                logger.info("{} - Retry Query permitted.", streamId);
                return executeQuery(query, handler);
            }
        } catch (Exception e1) {
            if(retryCausedOnServerlessDB(e1)) {
                logger.info("{} - Retry Query permitted.", streamId);
                return executeQuery(query, handler);
            }
            throw e;
        }
        throw e;
    }
}
 

/**
 * Executes the given query and returns the processed by the handler result using the provided dataSource.
 */

private <T> T executeQuery(SQLQuery query, ResultSetHandler<T> handler, DataSource dataSource) throws SQLException {
    final long start = System.currentTimeMillis();
    try {
        final QueryRunner run = new QueryRunner(dataSource, new StatementConfiguration(null,null,null,null,calculateTimeout()));

        query.setText(SQLQuery.replaceVars(query.text(), config.schema(), config.table(event)));
        final String queryText = query.text();
        final List<Object> queryParameters = query.parameters();
        logger.info("{} - executeQuery: {} - Parameter: {}", streamId, queryText, queryParameters);
        return run.query(queryText, handler, queryParameters.toArray());
    } finally {
        final long end = System.currentTimeMillis();
        logger.info("{} - query time: {}ms", streamId, (end - start));
    }
}
 

public String getCheckedList(String userId) {
	sql = "SELECT checkDate FROM dailycheck WHERE userId = ?;";
	try {
		return queryRunner.query(sql, new ResultSetHandler<String>() {

			@Override
			public String handle(ResultSet rs) throws SQLException {
				StringBuilder sb = new StringBuilder();
				while (rs.next()) {
					sb.append(rs.getString(1)).append(",");
				}
				if (sb.length() > 0){
					return sb.substring(0, sb.length() - 1);
				}
				else {
					return "";
				}
			}
		}, userId);
	} catch (SQLException e) {
		throw new RuntimeException(e);
	}
}
 

private void performSimpleTest(String jdbcUrl) throws SQLException {
    HikariDataSource dataSource = getDataSource(jdbcUrl, 1);
    new QueryRunner(dataSource).query("SELECT 1 FROM dual", new ResultSetHandler<Object>() {
        @Override
        public Object handle(ResultSet rs) throws SQLException {
            rs.next();
            int resultSetInt = rs.getInt(1);
            assertEquals("A basic SELECT query succeeds", 1, resultSetInt);
            return true;
        }
    });
    dataSource.close();
}
 

private <T> T query(String sql, ResultSetHandler<T> rsh) {
    if (log.isDebugEnabled()) {
        log.debug(sql);
    }
    try {
        return queryRunner.query(sql, rsh);
    } catch (SQLException e) {
        log.error("run sql query: " + sql + " have exception", e);
        return null;
    }
}
 

@Test
public void testMySQLWithConnectionPoolUsingSameContainer() throws SQLException, InterruptedException {

    // Populate the database with some data in multiple threads, so that multiple connections from the pool will be used
    for (int i = 0; i < 100; i++) {
        executorService.submit(() -> {
            try {
                new QueryRunner(dataSource).insert("INSERT INTO my_counter (n) VALUES (5)",
                    (ResultSetHandler<Object>) rs -> true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        });
    }

    // Complete population of the database
    executorService.shutdown();
    executorService.awaitTermination(5, TimeUnit.MINUTES);

    // compare to expected results
    int count = new QueryRunner(dataSource).query("SELECT COUNT(1) FROM my_counter", rs -> {
        rs.next();
        return rs.getInt(1);
    });
    assertEquals("Reuse of a datasource points to the same DB container", 100, count);


    int sum = new QueryRunner(dataSource).query("SELECT SUM(n) FROM my_counter", rs -> {
        rs.next();
        return rs.getInt(1);
    });
    // 100 records * 5 = 500 expected
    assertEquals("Reuse of a datasource points to the same DB container", 500, sum);
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getUsagePerClient(java.lang.String, java.lang.String, java.lang.String, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public UsagePerClientBean getUsagePerClient(String organizationId, String apiId, String version,
        DateTime from, DateTime to) {
    try {
        QueryRunner run = new QueryRunner(ds);
        String sql = "SELECT client_id, count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY client_id"; //$NON-NLS-1$
        ResultSetHandler<UsagePerClientBean> handler = new UsagePerClientHandler();
        return run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
        return new UsagePerClientBean();
    }
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getUsagePerPlan(java.lang.String, java.lang.String, java.lang.String, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public UsagePerPlanBean getUsagePerPlan(String organizationId, String apiId, String version,
        DateTime from, DateTime to) {
    try {
        QueryRunner run = new QueryRunner(ds);
        String sql = "SELECT plan, count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY plan"; //$NON-NLS-1$
        ResultSetHandler<UsagePerPlanBean> handler = new UsagePerPlanHandler();
        return run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
        return new UsagePerPlanBean();
    }
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getResponseStatsSummary(java.lang.String, java.lang.String, java.lang.String, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public ResponseStatsSummaryBean getResponseStatsSummary(String organizationId, String apiId,
        String version, DateTime from, DateTime to) {
    try {
        QueryRunner run = new QueryRunner(ds);
        String sql = "SELECT resp_type, count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY resp_type"; //$NON-NLS-1$
        ResultSetHandler<ResponseStatsSummaryBean> handler = new ResponseStatsSummaryHandler();
        return run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
        return new ResponseStatsSummaryBean();
    }
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getResponseStatsPerClient(java.lang.String, java.lang.String, java.lang.String, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public ResponseStatsPerClientBean getResponseStatsPerClient(String organizationId, String apiId,
        String version, DateTime from, DateTime to) {
    try {
        QueryRunner run = new QueryRunner(ds);
        String sql = "SELECT client_id, resp_type, count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY client_id, resp_type"; //$NON-NLS-1$
        ResultSetHandler<ResponseStatsPerClientBean> handler = new ResponseStatsPerClientHandler();
        return run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
        return new ResponseStatsPerClientBean();
    }
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getResponseStatsPerPlan(java.lang.String, java.lang.String, java.lang.String, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public ResponseStatsPerPlanBean getResponseStatsPerPlan(String organizationId, String apiId,
        String version, DateTime from, DateTime to) {
    try {
        QueryRunner run = new QueryRunner(ds);
        String sql = "SELECT plan, resp_type, count(*) FROM gw_requests WHERE api_org_id = ? AND api_id = ? AND api_version = ? AND rstart >= ? AND rstart < ? GROUP BY plan, resp_type"; //$NON-NLS-1$
        ResultSetHandler<ResponseStatsPerPlanBean> handler = new ResponseStatsPerPlanHandler();
        return run.query(sql, handler, organizationId, apiId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
        return new ResponseStatsPerPlanBean();
    }
}
 

/**
 * @see io.apiman.manager.api.core.IMetricsAccessor#getClientUsagePerApi(java.lang.String, java.lang.String, java.lang.String, org.joda.time.DateTime, org.joda.time.DateTime)
 */
@Override
public ClientUsagePerApiBean getClientUsagePerApi(String organizationId, String clientId, String version,
        DateTime from, DateTime to) {
    try {
        QueryRunner run = new QueryRunner(ds);
        String sql = "SELECT api_id, count(*) FROM gw_requests WHERE client_org_id = ? AND client_id = ? AND client_version = ? AND rstart >= ? AND rstart < ? GROUP BY api_id"; //$NON-NLS-1$
        ResultSetHandler<ClientUsagePerApiBean> handler = new ClientUsagePerApiHandler();
        return run.query(sql, handler, organizationId, clientId, version, from.getMillis(), to.getMillis());
    } catch (SQLException e) {
        e.printStackTrace();
        return new ClientUsagePerApiBean();
    }
}
 

/**
 * 将查询结果转换成Bean返回。
 * 
 * @param ds JDBC连接池
 */
public void queryBean(DataSource ds, int userId) {
    String sql = "select userId, userName, gender, age from student where userId=?";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<Student> handler = new BeanHandler<Student>(Student.class);
    Student result = null;
    try {
        result = run.query(sql, handler, userId);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    System.out.println(result);
}
 

/**
 * 将查询结果集转换成键值对列表返回。
 * 
 * @param ds JDBC连接池
 * @param userId 用户编号
 */
public void queryMap(DataSource ds, int userId) {
    String sql = "select userId, userName, gender, age from student where userId=?";
    
    QueryRunner run = new QueryRunner(ds);
    ResultSetHandler<Map<String, Object>> handler = new MapHandler();
    Map<String, Object> result = null;
    try {
        result = run.query(sql, handler, userId);
    } catch (SQLException e) {
        _logger.error("获取JDBC连接出错或执行SQL出错", e);
    }
    
    System.out.println(result);
}
 

public <T> List<T> query(Class<? extends Table> clazz, String sql, LinkedHashMap<String, Object> condition, ResultSetHandler<T> handler) {
    try {
        QueryRunner runner = getJdbcTemplate(clazz);
        //return runner.query(sql, condition.values().toArray(), rowMapper);
        return runner.execute(sql, handler, condition.values().toArray());
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return null;
}
 

public <T> T queryObject(Class<? extends Table> clazz, String sql, ResultSetHandler<T> handler) {
    try {
        QueryRunner runner = getJdbcTemplate(clazz);
        return runner.query(sql, handler);
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return null;
}
 

public <T> T queryObject(Class<? extends Table> clazz, String sql, LinkedHashMap<String, Object> condition, ResultSetHandler<T> handler) {
    try {
        if (condition == null || condition.isEmpty()) {
            return queryObject(clazz, sql, handler);
        }

        QueryRunner runner = getJdbcTemplate(clazz);
        return runner.query(sql, handler, condition.values().toArray());
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return null;
}
 

@Test
public void testSetPathSqlCreation() {
    // In the test setup, we duplicate sB across the JDBC "set path" return result and the schemas defined in the config to ensure that we drop the dupes in the final output
    JdbcHelper jdbc = mock(JdbcHelper.class);
    Connection conn = mock(Connection.class);
    when(jdbc.query(Matchers.any(Connection.class), Matchers.anyString(), Matchers.<ResultSetHandler<Object>>any())).thenReturn("s3,\"s1\",\"sB\",s2");

    // Use LinkedHashSet just to make the test setup easy
    LinkedHashSet<PhysicalSchema> schemas = new LinkedHashSet<PhysicalSchema>(Arrays.asList(new PhysicalSchema("sA"), new PhysicalSchema("sB"), new PhysicalSchema("sC")));

    assertEquals("set path s3,s1,sB,s2,sA,sC", Db2SqlExecutor.getCurrentPathSql(conn, jdbc, SetAdapter.adapt(schemas).toImmutable()));
}
 

public <T> T query(Connection conn, String sql, ResultSetHandler<T> resultSetHandler) {
    Pair<Statement, ResultSet> stmtRsPair = null;
    try {
        stmtRsPair = queryAndLeaveStatementOpen(conn, sql);
        return resultSetHandler.handle(stmtRsPair.getTwo());
    } catch (SQLException e) {
        throw new DataAccessException(e);
    } finally {
        if (stmtRsPair != null) {
            DbUtils.closeQuietly(stmtRsPair.getOne());
            DbUtils.closeQuietly(stmtRsPair.getTwo());
        }
    }
}
 

public <T> T query(String sql, ResultSetHandler<T> rsh, Object... params) {
    try {
        return queryRunner.query(sql, rsh, params);
    } catch (SQLException e) {
        log.error("query error", e);
        return null;
    }
}