【Java】 Apache Commons DbUtils でJoinしたデータを扱うには...

Java / DB関連

サンプル

DBデータ(MySQL)

CREATE TABLE `section` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

CREATE TABLE `employee` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NOT NULL,
	`birth_date` DATE NOT NULL,
	`section_id` BIGINT(20) NOT NULL,
	PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

INSERT INTO section(name)
    VALUES ('General Affairs Department'),
           ('Human Resources Division'),
           ('Accounting Department'),
           ('Development Department');
           
INSERT INTO employee(name, birth_date, section_id)
    VALUES ('Mike', '1982-11-22', 1),
           ('Tom', '1973-1-2', 2),
           ('Smith', '1939-4-15', 3),
           ('Ken', '1949-12-31', 3),
           ('John', '1976-9-19', 4);

EmployeeExDao.java

import java.util.Date;

public class EmployeeExDao {
  private Long id;
  private String name;
  private Date birthDate;
  private String sectionName;
  
  public Long getId() {
    return this.id;
  }
  public void setId(Long id) {
    this.id = id;
  }
  public String getName() {
    return this.name;
  }
  public void setName(String name) {
    this.name = name;
  }
  public Date getBirthDate() {
    return this.birthDate;
  }
  public void setBirthDate(Date birthDate) {
    this.birthDate = birthDate;
  }
  public String getSectionName() {
    return this.sectionName;
  }
  public void setSectionName(String sectionName) {
    this.sectionName = sectionName;
  }
}

MainEx.java

実際の使用例 
import java.util.List;

public class MainEx {

  public static void main(String[] args) {
    try (DbConnector dbConnector = new DbConnector()) {
      dbConnector.initialize("jdbc:mysql://localhost:3306/sampledb", "root", "password");

      // SELECTサンプル
      List<EmployeeExDao> employees = dbConnector.selectList(EmployeeExDao.class,
          "SELECT e.id AS id, e.name AS name, e.birth_date AS birth_date, s.name AS section_name FROM employee AS e "
          + "INNER JOIN section AS s ON e.section_id = s.id");
      print(employees);

    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  private static void print(List<EmployeeExDao> employees) {
    for (EmployeeExDao employee : employees) {
      System.out.println("************");
      System.out.println(employee.getId());
      System.out.println(employee.getName());
      System.out.println(employee.getBirthDate());
      System.out.println(employee.getSectionName());
      System.out.println("************");
    }
  }
}

DbConnector.java

http://blogs.yahoo.co.jp/dk521123/36096631.html
と同じ 
import java.io.Closeable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

public class DbConnector implements Closeable {
  private Connection connection = null;
  private QueryRunner queryRunner = null;

  @Override
  public void close() {
    try {
      if (this.connection != null && !this.connection.isClosed()) {
        DbUtils.closeQuietly(this.connection);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void initialize(String dbUrl, String dbUserId, String dbPassword) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    this.queryRunner = new QueryRunner();
    // DB接続
    this.connection = DriverManager.getConnection(dbUrl, dbUserId, dbPassword);
  }

  public void beginTransaction() throws SQLException {
    this.connection.setAutoCommit(false);
  }

  public void commit() {
    DbUtils.commitAndCloseQuietly(this.connection);
  }

  public void rollback() {
    DbUtils.rollbackAndCloseQuietly(this.connection);
  }

  /**
   * ScalarHandlerのサンプル
   */
  public Long selectLongValue(String query, Object... parameters) throws SQLException {
    ScalarHandler<Long> resultSetHandler = new ScalarHandler<Long>(1);
    return  this.queryRunner.query(this.connection, query, resultSetHandler, parameters);
  }
  
  /**
   * BeanListHandlerのサンプル
   */
  public <T> T selectUnique(Class<T> targetClass, String query, Object... parameters) throws SQLException {
    RowProcessor rp = new BasicRowProcessor(new EmployeeBeanProcessor());
    BeanHandler<T> beanHandler = new BeanHandler<T>(targetClass, rp);
    return (T) this.queryRunner.query(this.connection, query, beanHandler, parameters);
  }

  /**
   * BeanListHandlerのサンプル
   */
  public <T> List<T> selectList(Class<T> targetClass, String query, Object... parameters) throws SQLException {
    RowProcessor rp = new BasicRowProcessor(new EmployeeBeanProcessor());
    ResultSetHandler<List<T>> resultSetHandlers = new BeanListHandler<T>(targetClass, rp);
    return (List<T>) this.queryRunner.query(this.connection, query, resultSetHandlers, parameters);
  }

  /**
   * QueryRunner.updateのサンプル
   */
  public Long insert(String query, String... parameters) throws SQLException {
    PreparedStatement statement = this.connection.prepareStatement(query, java.sql.Statement.RETURN_GENERATED_KEYS);

    int index = 1;
    for (String parameter : parameters) {

      // FIXME
      if (parameter instanceof String) {
        statement.setString(index, parameter);
      } else {
        statement.setObject(index, parameter);
      }

      index++;
    }
    // 追加
    statement.executeUpdate();

    // auto-incrementの値取得
    Long returnValue = Long.MIN_VALUE;
    ResultSet rs = statement.getGeneratedKeys();
    if (rs.next()) {
      returnValue = rs.getLong(1);
    }
    return returnValue;
  }

  /**
   * QueryRunner.updateのサンプル
   */
  public void update(String query, Object... parameters) throws SQLException {
    this.queryRunner.update(connection, query, parameters);
  }
}

EmployeeBeanProcessor.java

http://blogs.yahoo.co.jp/dk521123/36096631.html
と同じ 
import java.beans.PropertyDescriptor;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Arrays;

import org.apache.commons.dbutils.BeanProcessor;

public class EmployeeBeanProcessor extends BeanProcessor {
  
  @Override
  protected int[] mapColumnsToProperties(ResultSetMetaData rsmd,
          PropertyDescriptor[] props) throws SQLException {
      int columns = rsmd.getColumnCount();
      int columnToProperty[] = new int[columns + 1];
      Arrays.fill(columnToProperty, PROPERTY_NOT_FOUND);
      for (int col = 1; col <= columns; col++) {
          // ★getColumnLabel()を使用することにより、別名(AS)でマッピングできる★
          String columnName = rsmd.getColumnLabel(col);
          for (int i = 0; i < props.length; i++) {
              if (equalsColumnProperty(columnName, props[i].getName())) {
                  columnToProperty[col] = i;
                  break;
              }
          }
      }
      return columnToProperty;
  }
  private boolean equalsColumnProperty(String colName, String propName) {
    System.out.println(" colName " + colName + " propName " + propName);
      return colName.replaceAll("_", "").equalsIgnoreCase(propName);
  }
}

出力結果

************
1
Mike
1982-11-22
General Affairs Department
************
************
2
Tom
1973-01-02
Human Resources Division
************
************
3
Smith
1939-04-15
Accounting Department
************
************
4
Ken
1949-12-31
Accounting Department
************
************
5
John
1976-09-19
Development Department
************

関連記事

O/Rマッパー [2] ～Apache Commons DbUtils～

http://blogs.yahoo.co.jp/dk521123/34079202.html

Apache Commons DbUtils を使いやすくすることを考える

http://blogs.yahoo.co.jp/dk521123/36096631.html

Apache Commons DbUtils で、List<String> を返すようにするには...

http://blogs.yahoo.co.jp/dk521123/36274590.html