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());
  }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

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(

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]

SELECT 〜
INSERT 〜
DELETE 〜
UPDATE 〜
　　・
　　・
　　・
続く

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

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