DBUtils 查询使用

DBUtils 查询使用

1. maven 依赖

   <dependency>
    <groupId>commons-dbutils</groupId>
    <artifactId>commons-dbutils</artifactId>
    <version>1.7</version>
   </dependency>

2.数据源（dataSource）

<!-- 配置数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" name="druidDataSource">
   <property name="driverClassName" value="${driverClassName}" />
   <property name="url" value="${url}" />
   <property name="username" value="${username}" />
   <property name="password" value="${password}" />
   <!-- druid 连接池的监控
   <property name="filters" value="stat" />
   -->
   <!--maxActive: 最大连接数量 -->
   <property name="maxActive" value="${maxActive}" />
   <!--initialSize: 初始化连接 -->
   <property name="initialSize" value="${initialSize}" />
   <property name="maxWait" value="${maxWait}" />
   <property name="useUnfairLock" value="${useUnfairLock}" />
   <property name="validationQuery">
      <value>select 1 from dual</value>
   </property>
   <property name="testOnBorrow" value="true" />
   <property name="testOnReturn" value="false" />
</bean>

3.代码

重点：创建一个类实现 ResultSetHandler，实现handle方法，建立查询结果和实体bean的映射关系

public class LogCountMapper {
    private Logger logger = LoggerFactory.getLogger(LogCountMapper.class);
   	//获取数据源
    private DruidDataSource dataSource = (DruidDataSource) BeanUtil.getBean("dataSource");
    private QueryRunner queryRunner = new QueryRunner(dataSource);

    public List<LogCount> getLogCount(String partName) throws SQLException{
        String querySql = sql查询语句;
        logger.debug("QueryRunner:{}", queryRunner);
        logger.debug(querySql);
        try {
            List<LogCount> query = queryRunner.query(querySql,new LogCountHandler());
            return query;
        } catch (SQLException e) {
            logger.error("获取表分区查询结果失败:{}", e);
        }
        return null;
    }
	
	/**
	 * 创建一个类实现 ResultSetHandler，实现handle方法，建立查询结果和实体bean的映	   * 射关系
	 */
    class LogCountHandler implements ResultSetHandler<List<LogCount>> {
        @Override
        public List<LogCount> handle(ResultSet resultSet) throws SQLException {
            logger.debug("resultSet:{}", resultSet);
            List<LogCount> logCounts = Lists.newArrayList();
            while (resultSet.next()) {
                LogCount logCount = new LogCount();
                logCount.setPersonId(resultSet.getLong("personId"));
                logCount.setLogType(resultSet.getString("logType"));
                logCount.setCount(resultSet.getLong("count"));
                logCounts.add(logCount);
            }
            return logCounts;
        }
    }
}