java.sql.DatabaseMetaDataでDBメタ情報からSQLを自動生成する

2006/01

java.sql.DatabaseMetaDataを使うとDBのメタ情報(テーブル定義情報等)を
取得することが出来ます。テーブル名や列名、属性、長さ等です。
これらを元にSQL文を生成したいと思います。

package tool.db;

import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;

/**
 * JDBCでDBに接続してメタ情報を取得し、SQL文を生成してファイルに出力する。
 * SQL文はSELECT(無条件 ＆ 主キーを条件)、INSERT、UPDATE(主キーを条件)、
 * DELETE(主キーを条件)
 * @author yamarou
 */
public class SQLGeneretor {
  public static void main(String[] args) throws Exception {
    System.out.println("処理開始");
    PrintWriter out = new PrintWriter(new FileWriter("c:/TEMP/sql.txt"));
    String driverName = "COM.ibm.db2.jdbc.app.DB2Driver";
    String url = "jdbc:db2:YAMAROUDB";
    String user = "db2admin";
    String password = "db2admin";
    String schem = "SCHEM01";
    ArrayList list = loadDBInfo(driverName, url, user, password, schem);
    for (int i = 0; i < list.size(); i++) {
      TableVO table = (TableVO) list.get(i);
      String tableID4Java = table.getName();
      tableID4Java =
        tableID4Java.substring(0, 1)
          + tableID4Java.substring(1).toLowerCase();
      String sql = "SELECT ";
      String columnNames = "";
      for (Iterator iter = table.getColumns().iterator();
        iter.hasNext();
        ) {
        String column = (String) iter.next();
        sql += column + ",";
        columnNames += column + ",";
      }
      sql = sql.substring(0, sql.length() - 1);
      sql += " FROM " + schem + "." + table.getName();

      columnNames = columnNames.substring(0, columnNames.length() - 1);

      sql += " WHERE ";
      String where = "";
      for (Iterator iter = table.getPrimaryKeys().iterator();
        iter.hasNext();
        ) {
        String column = (String) iter.next();
        sql += column + "=? AND ";
        where += column + "=? AND ";
      }
      sql = sql.substring(0, sql.length() - 5);
      out.println(sql);
      try {
        where = where.substring(0, where.length() - 5);
      } catch (StringIndexOutOfBoundsException e) {
      }

      sql = "INSERT INTO " + schem + "." + table.getName() + "(";
      for (Iterator iter = table.getColumns().iterator();
        iter.hasNext();
        ) {
        String column = (String) iter.next();
        sql += column + ",";
      }
      sql = sql.substring(0, sql.length() - 1);
      sql += ") VALUES(";
      String values = "";
      for (Iterator iter = table.getColumns().iterator();
        iter.hasNext();
        ) {
        String column = (String) iter.next();
        sql += "?,";
        if (column.equals("RCRD_CRTS")) {
          values += "CURRENT TIMESTAMP,";
        } else if (column.equals("ROW_UPDTS_DBS")) {
          values += "CURRENT TIMESTAMP,";
        } else {
          values += "?,";
        }
      }
      sql = sql.substring(0, sql.length() - 1) + ")";
      out.println(sql);
      values = values.substring(0, values.length() - 1);

      sql = "DELETE FROM " + schem + "." + table.getName() + " WHERE ";
      for (Iterator iter = table.getPrimaryKeys().iterator();
        iter.hasNext();
        ) {
        String column = (String) iter.next();
        sql += column + "=? AND ";
      }
      sql = sql.substring(0, sql.length() - 5);
      out.println(sql);

      sql = "UPDATE " + schem + "." + table.getName() + " SET ";
      String columnNamesAndParameters = "";
      for (Iterator iter = table.getColumns().iterator();
        iter.hasNext();
        ) {
        String column = (String) iter.next();
        sql += column + "=?,";
        if (column.equals("ROW_UPDTS_DBS")) {
          columnNamesAndParameters += column + "=CURRENT TIMESTAMP,";
        } else if (
          column.equals("RCRD_CRTS")
            || column.equals("CREAT_USRID_RCRD")
            || column.equals("DEL_FLAG")) {

        } else {
          columnNamesAndParameters += column + "=?,";
        }
      }
      columnNamesAndParameters =
        columnNamesAndParameters.substring(
          0,
          columnNamesAndParameters.length() - 1);
      sql = sql.substring(0, sql.length() - 1) + " WHERE ";
      for (Iterator iter = table.getPrimaryKeys().iterator();
        iter.hasNext();
        ) {
        String column = (String) iter.next();
        sql += column + "=? AND ";
      }
      sql = sql.substring(0, sql.length() - 5);
      out.println(sql);
    }
    out.close();
    System.out.println("処理終了");
  }

  /**
   * DB情報を取得してリストに格納して返す。
   * @param driverName ドライバー名
   * @param url URL
   * @param user DBユーザ名
   * @param password DBパスワード
   * @param schem DBスキーマ名
   * @return DB情報
   * @throws ClassNotFoundException ドライバークラスが見つからない
   * @throws SQLException DBエラー
   */
  public static ArrayList loadDBInfo(
    String driverName,
    String url,
    String user,
    String password,
    String schem)
    throws ClassNotFoundException, SQLException {

    Class.forName(driverName);
    Connection con = DriverManager.getConnection(url, user, password);
    DatabaseMetaData dbmd = con.getMetaData();

    ResultSet rs = dbmd.getColumns(null, schem, "%", "%");
    HashMap map = new HashMap();
    while (rs.next()) {
      //printResultSet(rs);
      String tableName = rs.getString("TABLE_NAME");
      String columnName = rs.getString("COLUMN_NAME");
      String typeName = rs.getString("TYPE_NAME");
      TableVO table = (TableVO) map.get(tableName);
      if (table == null) {
        table = new TableVO();
        table.setName(tableName);
        loadPrimaryKeyList(
          dbmd,
          table.getName(),
          table.getPrimaryKeys(),
          schem);
        map.put(tableName, table);
      }
      table.getColumns().add(columnName);
    }
    rs.close();
    con.close();
    ArrayList list = new ArrayList();
    Collection c = map.values();
    for (Iterator iter = c.iterator(); iter.hasNext();) {
      TableVO e = (TableVO) iter.next();
      list.add(e);
    }
    Collections.sort(list);
    return list;
  }

  /**
   * dbmdからschemスキーマのtableNameテーブルの主キー情報を取得して
   * listに格納して返す。
   * @param dbmd DB定義情報
   * @param tableName テーブル名
   * @param list 主キー情報を格納するリスト
   * @param schem スキーマ名
   * @throws SQLException DB例外
   */
  private static void loadPrimaryKeyList(
    DatabaseMetaData dbmd,
    String tableName,
    ArrayList list,
    String schem)
    throws SQLException {
    //ユニークインデックスで主キーを定義している場合は↓のコードを実行
    ResultSet rs = dbmd.getIndexInfo(null, schem, tableName, true, true);
    //プライマリーキーで主キーを定義している場合は↓のコードのコメントを外す
    //ResultSet rs = dbmd.getPrimaryKeys(null, schem, tableName);
    while (rs.next()) {
      String s = rs.getString("COLUMN_NAME");
      if (s != null) {
        list.add(s);
      }
    }
    rs.close();
  }

  /**
   * ResultSetを標準出力する
   * @param rs 標準出力するResultSet
   * @throws SQLException DBエラー
   */
  private static void printResultSet(ResultSet rs) throws SQLException {
    while (rs.next()) {
      ResultSetMetaData rm = rs.getMetaData();
      for (int i = 1; i <= rm.getColumnCount(); i++) {
        System.out.println(
          i + ":" + rm.getColumnName(i) + ":" + rs.getString(i));
      }
    }
  }
}

package tool.db;

import java.util.ArrayList;

import org.apache.commons.lang.builder.ToStringBuilder;

/**
 * Table情報を格納する
 * @author yamarou
 */
public class TableVO implements Comparable {
  /** テーブル名 */
  private String name;

  /** 列名リスト */
  private ArrayList columns = new ArrayList();

  /** 主キー名リスト */
  private ArrayList primaryKeys = new ArrayList();

  /**
   * オブジェクトの文字列表現を返します。
   */
  public String toString() {
    return ToStringBuilder.reflectionToString(this);
  }

  /**
   * 列名リストを返す
   * @return 列名リスト
   */
  public ArrayList getColumns() {
    return columns;
  }

  /**
   * テーブル名を返す
   * @return テーブル名
   */
  public String getName() {
    return name;
  }

  /**
   * 主キー名リストを返す
   * @return 主キー列名リスト
   */
  public ArrayList getPrimaryKeys() {
    return primaryKeys;
  }

  /**
   * テーブル名を設定する
   * @param string テーブル名
   */
  public void setName(String string) {
    name = string;
  }

  /**
   * オブジェクトを比較する。
   */
  public int compareTo(Object o) {
    TableVO table = (TableVO) o;
    return this.name.compareTo(table.getName());
  }

}

■実行
java tool.db.SQLGeneretor

■実行結果
c:/TEMP/sql.txtにテキストファイルが出来上がります。

[sql.txt]

この仕組みを使えばORマッピングクラスや、
JUnitのテストコード等、DB関連のコードを
自動生成したりといったことが出来ると思います。

なかなか使えそうですね♪