commons-dbutils 简单实现按行处理数据

      commons-dbutils作为一个jdbc框架在日常简单的程序开发中简直不要太好用，但是在使用commons-dbutils的时候我发现没有提供一个可以按行处理数据功能。但是我如果我需要逐行扫描并按行处理一个数据量很大的表，不管把全量数据加载到什么样的数据结构中应该都是一个很耗费内存和降低效率的事情，而且有可能会导致内存溢出，于是自己实现了一个简单的按行处理的功能。

测试数据表结构

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(32) DEFAULT NULL,
  `age` double DEFAULT NULL,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

对应的javaBean

import java.util.Date;

public class TestBean {
    int id;
    String userName;
    double age;
    Date createTime;
    Date updateTime;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public double getAge() {
        return age;
    }

    public void setAge(double age) {
        this.age = age;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public Date getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Date updateTime) {
        this.updateTime = updateTime;
    }
    public String toString(String separator){
        return this.id+separator+this.userName+separator+this.age+separator+this.createTime+separator+this.updateTime;
    }
    public String toString(){
        return toString("\t");
    }
}

使用到的接口

public interface MyRowProcessor<T> {
    void rowProcessor(T t);
}

自定义ResultSetHandler

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.RowProcessor;

import java.sql.ResultSet;
import java.sql.SQLException;

public class MyResultSetHandler<T> implements ResultSetHandler<Integer> {
    static final RowProcessor ROW_PROCESSOR = new BasicRowProcessor();

    private final Class<? extends T> type;


    private final RowProcessor convert;

    private final MyRowProcessor myRowProcessor;
    /**
     * Creates a new instance of BeanHandler.
     *
     * @param type The Class that objects returned from <code>handle()</code>
     * are created from.
     */
    public MyResultSetHandler(Class<? extends T> type,MyRowProcessor myRowProcessor) {

        this(type, ROW_PROCESSOR,myRowProcessor);
    }

    /**
     * Creates a new instance of BeanHandler.
     *
     * @param type The Class that objects returned from <code>handle()</code>
     * are created from.
     * @param convert The <code>RowProcessor</code> implementation
     * to use when converting rows into beans.
     */
    public MyResultSetHandler(Class<? extends T> type, RowProcessor convert,MyRowProcessor<T> myRowProcessor) {
        this.type = type;
        this.convert = convert;
        this.myRowProcessor = myRowProcessor;
    }
    @Override
    public Integer handle(ResultSet rs) throws SQLException {
        int ret=0;
        while(rs.next()){
            T t= this.convert.toBean(rs, this.type);
            ret++;
            myRowProcessor.rowProcessor(t);
        }
        return ret;
    }
}

druid线程池

import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;

public class JDBCUtils {
    public static DruidDataSource dataSource_mysql;
    public static DruidDataSource dataSource_oracle;

    static {
        try {
            dataSource_mysql= new DruidDataSource();
            dataSource_mysql.setUrl(MyConfig.mysql_url);
            dataSource_mysql.setUsername(MyConfig.mysql_user);
            dataSource_mysql.setPassword(MyConfig.mysql_password);
            dataSource_mysql.setDriverClassName(MyConfig.mysql_dirver);
            dataSource_mysql.setInitialSize(5);
            dataSource_mysql.setMaxActive(10);

        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    /**
     * 获取数据库连接池中的连接
     * @return 如果返回null,说明连接失败,有值就是获取连接成功
     */
    public static Connection getConnection_mysql() {
        Connection conn=null;

        try {
            conn=(Connection)dataSource_mysql.getConnection();
        }catch(Exception e) {
            e.printStackTrace();
        }

        return conn;
    }

}

测试类

import com.cetc.util.*;
import org.apache.commons.dbutils.QueryRunner;
import java.sql.*;



public class Run implements MyRowProcessor<TestBean>{

    public static void main(String[] args) throws SQLException {
        QueryRunner queryRunner_mysql = new QueryRunner(JDBCUtils.dataSource_mysql);
        String sql = "SELECT * FROM test ";

        Integer ret= queryRunner_mysql.query( sql, new MyResultSetHandler<>(TestBean.class,new Run()));
        System.out.println("处理数据量："+ret);

    }

    @Override
    public void rowProcessor(TestBean testBean) {
        System.out.println(testBean.toString());
    }
}