Article updated on

How to Put all the values of a Resultset to a bean with BeanProcesor

BeanProcesor is an Apache API that helps us copy all the values from a Resultset to a bean. It uses reflection and unless you need an extremely optimal code it highly recommended to uses because it reduces development time.

This example reads a table and put its values in an ArrayList, It parses only two fields but you can use as many as you need.

  • BeanProcesor Ignore Cases

Table Example contains

img/0/49/table.JPG

Java Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import org.apache.commons.dbutils.BeanProcessor;
public class TestBeanProcessor {
    public static void main(String[] args) {
        Connection conn = null;
        try {
            //connection
            String userName = "root";
            String password = "mypassword";
            String url = "jdbc:mysql://localhost:3306";
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            conn = DriverManager.getConnection(url, userName, password);
            PreparedStatement ps1;
            conn.setAutoCommit(true);
            //query
            ps1 = conn.prepareStatement("SELECT * FROM TEST");
            ResultSet rs = ps1.executeQuery();
            //bean procesor
            BeanProcessor bp = new BeanProcessor();
            ArrayList<MyBean> list = new ArrayList<MyBean>();
            while (rs.next()) {
                list.add((MyBean) bp.toBean(rs, MyBean.class));
            }
            rs.close();
            ps1.close();
            //result
            for(MyBean mb : list){
                System.out.println(mb.getId()+ " "+mb.getTest());
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Bean

public class MyBean {
    private String id;
    private String test;
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getTest() {
        return test;
    }
    public void setTest(String test) {
        this.test = test;
    }
}

Maven

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

Notes

  • Bean processor doesn't accept aliases, if two columns have the same name you may have to override those conflicting fields one by one.
  • For a much more advance mapping consider other libraries such us resultsetmapper or other frameworks such us hibernate.