JDBCを使ってデータベースのデータをコピーする

はじめに

　データベースを操作していると、データベース間でデータをコピーする作業が必要になることがよくあります。これは単純な作業のように思えますが、単なるコピーだけでは済まないことも少なくありません。たとえば、データの一部だけを移動したい場合や、コピーの際にデータを変更したい場合があります。本稿では、私が「データムーバー」（DataMover）と呼んでいる簡単なアプリケーションを使って、基本的なデータベースのコピー（データベース間のテーブルの移動）を実行する一連のクラスを紹介します。このサンプルは、データベース全体をコピーする方法を示す叩き台にすぎませんが、コードに手を加えることにより、必要に応じて個別のテーブルまたは特定のテーブルの行や列をコピーするなど、望みどおりのデータコピー／変換操作を実行できることが分かるでしょう。

　本稿では、JDBC（Java Database Connectivity）の扱いに不慣れな方のために、入門編として、次のようなJDBCの基本的な使い方を説明します。

• データベースへの接続を開く
• クエリを作成して発行する
• クエリによって返されるResultSetを処理する

　さらに、データベースにクエリを発行し、データベースの構造に関する情報を収集する方法についても説明します。これにより、DataMoverユーティリティを使ってデータベースに関する情報を収集し、データベースをコピーできるようになります。DataMoverは、次の4段階の手順に沿って処理を実行します。

• コピー先のテーブルを作成するSQL（CREATE TABLE文）を生成する
• 既存のテーブルを削除するSQLを生成する
• コピー元のテーブルからデータを選択（SELECT）するSQLを生成する
• コピー先のテーブルにデータを挿入（INSERT）するSQLを生成する

　DataMoverアプリケーションをプログラムする詳しい方法を説明する前に、このアプリケーションを使ってデータベースをコピーする方法を説明します。

DataMoverを使ってデータベースをコピーする

　本稿のダウンロードサンプルには、DataMoverUtilityという簡単なユーティリティが含まれています。このユーティリティは設定ファイルを読み取り、コピー元データベースのデータをコピー先データベースにコピーします。

　ユーティリティを実行するには、まず設定ファイルを作成する必要があります。このファイルには、4行のデータを記述します。たとえば、DataMoverをテストするために、私は次のような設定ファイルを使いました。

sourceDriver=com.mysql.jdbc.Driver
sourceURL=jdbc:mysql://127.0.0.1/test?user=root
targetDriver=com.mysql.jdbc.Driver
targetURL=jdbc:mysql://127.0.0.1/test2?user=root

　この設定ファイルでは、MySQLデータベース間でデータをコピーすることを指定しています。データベースは両方ともローカルコンピュータ（127.0.0.1）上にあるという前提です。JDBCのデータベースごとに、ドライバと接続URLを指定する必要があります。sourceDriverとsourceURLには、コピー元データベースのドライバとURLを指定します。同様に、targetDriverとtargetURLには、コピー先データベースの情報を指定します。

　DataMoverユーティリティを実行するときに、この設定ファイルを最初のパラメータとして指定します。たとえば次のように指定します。

java DataMoverUtility c:export.txt 

MySQLをJavaで使用する

　JDBCアプリケーションをコンパイルして使用する前に、使用するデータベースのドライバをインストールする必要があります。通常は、ドライバのJARファイルをプロジェクトのクラスパスに追加するだけです。

　本稿では、MySQLを使用するという前提で話を進めます。ただし、ここで示すコードはほとんどのデータベースと非常に高い互換性があります。MySQLに固有と考えられる部分は、すべて「MySQL.java」クラスファイルにまとめられています。他のデータベースを使う場合は、単純にDatabaseクラスを拡張し、そのデータベース特有のコードを追加します。

　MySQLでは、Connector/Jドライバを使います。これはwww.mysql.orgからダウンロードできます。パッケージにJARファイルが含まれているので、それをクラスパスに追加します。これでMySQLデータベースに接続する準備は完了です。

DataMoverの構造

　DataMoverは、主要な2つのパッケージから成ります。com.heatonresearch.datamoverという名前のパッケージには、DataMoverクラスとDataMoverUtilityクラスが含まれています。DataMoverクラスには、データベース間のテーブルコピーを実行したり、より高度な操作を実装するときの叩き台になったりする再利用可能な関数がいくつか含まれます。DataMoverUtilityクラスは、本稿で紹介するクラスの使い方の一例です。

　com.heatonresearch.datamover.dbという名前のパッケージには、下位レベルのすべてのデータベースクラスが含まれています。本稿では、DatabaseException、Database、およびMySQLの3つのクラスを使用します。DatabaseExceptionクラスは、データベースの問題が発生したときにDataMoverがスローする例外を定義します。Databaseクラスは、データベースアクセスのためのすべての基本関数を提供します。MySQLクラスはDatabaseクラスを継承し、MySQL固有のコードを実装します。このMySQLクラスと同様に、Databaseクラスから新しいデータベースクラスを派生させることにより、このクラスライブラリを拡張して別のデータベースを扱うことができます。

MySQLに接続する

　どのような操作を行うにも、まずデータベースに接続する必要があります。JDBC経由でデータベースに接続するには、2つの情報を知っておく必要があります。JDBCドライバ名と、データベースURLです。

　JDBCドライバ名は、単なるクラス名です。MySQLでは、クラスは以前にインストールしたMySQL JARファイルによって提供されます。データベースURLでは、データベースの名前、場所、および使用するユーザーを指定します。データベースへの接続を開くには、Databaseクラスのconnectメソッドを使います。次のコードは、connectメソッドの実装コードのうち、データベースに接続する部分を示しています。

try
{
  Class.forName(driver).newInstance();
  connection = DriverManager.getConnection(url);
} catch (InstantiationException e)
{
  throw new DatabaseException(e);
} catch (IllegalAccessException e)
{
  throw new DatabaseException(e);
} catch (ClassNotFoundException e)
{
  throw new DatabaseException(e);
} catch (SQLException e)
{
  throw new DatabaseException(e);
}

　コードの最初の2行で、ドライバとURLを処理しています。残りの行では、データベースを開くときに起こりうるいろいろな例外を処理しています。いずれかの例外が発生した場合は、その例外をDatabaseExceptionとしてパッケージ化してスローします。

テーブルおよびカタログ情報にアクセスする

　データベースをコピーするには、データベースに関するカタログ情報を取得する必要があります。具体的には、データベースのテーブルのリストと、各テーブル内のすべての列のリストが必要になります。この情報に基づいて、DataMoverはデータのコピーに必要なSQL文を作成します。

　Databaseクラスには、データベースのすべてのテーブルのリストを返すlistTablesというメソッドがあります。JDBCでテーブルのリストを取得する方法はいくつかあります。多くのデータベースには、テーブルのリストを取得するためのストアドプロシージャやシステムテーブルが用意されています。しかし、ストアドプロシージャやシステムテーブルの名前はデータベースごとに異なるため、テーブルのリストを取得する場合はJavaクラスのDatabaseMetaDataを使うのが最も互換性の高い方法になります。このクラスは、テーブルの名前など、データベースに関する情報を提供します。

　listTablesメソッドは、まずテーブルリストを格納するコレクションを作成します。

Collection<String> result = new ArrayList<String>();

　次に、テーブルのリストを保持するResultSetを作成し、DatabaseMetaDataオブジェクトを取得します。

ResultSet rs = null;
try
{
DatabaseMetaData dbm = 
   connection.getMetaData();

　DatabaseMetaDataクラスにはたくさんのプロパティとメソッドがありますが、この例ではテーブルを取得するだけなので、getTablesメソッドを使います。このメソッドは3つの引数をとります。catalog文字列とschemaPattern文字列（この例ではどちらもnull）、および取得するテーブルの種類を示す文字列配列です。次の例では、"TABLE"という1つの要素を含む文字列配列を作成し、この配列を3つ目の引数としてgetTablesメソッドを呼び出しています。

String types[] = { "TABLE" };
rs = dbm.getTables(null, null, "", types);

　この場合のgetTablesメソッドは、すべてのテーブルのリストが含まれるResultSetを返します。後は、結果に対して繰り返し処理を行い、各テーブル名をresultコレクションに追加するだけです。

  while (rs.next())
  {
    String str = rs.getString("TABLE_NAME");
    result.add(str);
  }
} catch (SQLException e)
{
  throw (new DatabaseException(e));
}

　finallyブロックを用意して、ResultSetが正しく閉じられるようにします。

finally
{
  if( rs!=null )
  {
    try
    {
      rs.close();
    } catch (SQLException e)
    {
    }
  }
}

　最後に、getTablesメソッドはテーブルのリストを返します。

return result;

　テーブルのリストを取得した後は、それらを処理してテーブル内の列のリストを取得します。それにより、適切なCREATE TABLE文に加えて、現在のテーブルに適合するINSERT文とSELECT文も作成できるようになります。テーブルの列のリストを取得するために、DatabaseクラスにはlistColumnsメソッドが含まれています。このメソッドの処理はテーブルリストの取得と似ているので、詳しくは説明しません。

　データベースカタログ情報（テーブルと列）を取得したら、必要なSQL文を生成することができます。

CREATE TABLE文を生成する

　データベースカタログ情報からCREATE TABLE文を生成するルーチンは、さまざまな目的に利用できます。DatabaseクラスのgenerateCreateメソッドは、まずCREATE TABLE文を格納するためのStringBufferを作成します。

StringBuffer result = new StringBuffer();

　次に、SELECT * FROM [table]という文を作成して実行し、得られた結果からテーブル構造のメタデータを取得します。

try
{
StringBuffer sql = new StringBuffer();
sql.append("SELECT * FROM ");
sql.append(table);
ResultSet rs = executeQuery(sql.toString());
ResultSetMetaData md = rs.getMetaData();

　先にCREATE TABLEの部分を作成します。列は後から埋めることができます。

result.append("CREATE TABLE ");
result.append(table);
result.append(" ( ");

　すべての列に対してループ処理を行い、列の名前をCREATE TABLE文に追加します。

for (int i = 1; i <= md.getColumnCount(); i++)
{

　CREATE TABLE文の列はコンマ区切りなので、最初の列を除き、各列名の後ろにコンマを追加する必要があります。

if (i != 1)
  result.append(’,’);
result.append(md.getColumnName(i));
result.append(’ ’);

　列の型を取得し、それを文の後ろに追加します。

String type = processType(md.getColumnTypeName(i), 
  md.getPrecision(i));
result.append(type);

　型の後に精度を指定する必要があります。精度が65535を超える場合は、BLOB（Binary Large Object）またはText型なので精度は不要です。そうでない場合は、精度と桁数を指定します。

if (md.getPrecision(i) < 65535)
{
  result.append(’(’);
  result.append(md.getPrecision(i));
  if (md.getScale(i) > 0)
  {
    result.append(’,’);
    result.append(md.getScale(i));
  }
  result.append(") ");
} else
    result.append(’ ’);

　型が数値で、種類が符号なしの場合は、UNSIGNED句を指定します。

if (this.isNumeric(md.getColumnType(i)))
{
  if (!md.isSigned(i))
    result.append("UNSIGNED ");
}

　また、データ型がNULL値を受け付けるかどうかも指定します。

if (md.isNullable(i) == 
  ResultSetMetaData.columnNoNulls)
  result.append("NOT NULL ");
else
  result.append("NULL ");
if (md.isAutoIncrement(i))
  result.append(" auto_increment");
}

　さらに、主キーを指定する必要があります。これはDatabaseMetaDataクラスを使って取得できます。主キーの列には、それを示す文字列を指定します。

DatabaseMetaData dbm = connection.getMetaData();
ResultSet primary = dbm.getPrimaryKeys(
  null, null, table);
boolean first = true;
while (primary.next())
{
  if (first)
  {
    first = false;
    result.append(’,’);
    result.append("PRIMARY KEY(");
  } else
      result.append(",");
      result.append(primary.getString
       ("COLUMN_NAME"));
} 

if (!first)
  result.append(’)’);

　終わりのかっこを付けてCREATE TABLE文は完成です。

  result.append(" ); ");
} 

　エラーが発生した場合は、DataMoverがDatabaseExceptionをスローします。

   catch (SQLException e)
   {
     throw (new DatabaseException(e));
   }

　最後に、generateCreateメソッドは完成したCREATE TABLE文を文字列として返します。

return result.toString();

データをコピーする

　コピー先データベースにテーブルを作成したら、そこにデータをコピーします。それには、SELECT文とINSERT文を作成する必要があります。SELECT文はコピー元データベースからテーブルのデータを読み取ります。INSERT文はコピー先データベースにデータを書き込みます。

　まず、SELECT文、INSERT文、およびINSERT文のVALUES句を格納するための3つのStringBufferのインスタンスを作成します。

StringBuffer selectSQL = new StringBuffer();
StringBuffer insertSQL = new StringBuffer();
StringBuffer values = new StringBuffer();

　DataMoverは列を取得し、それぞれの状態を表示します。

Collection<String> columns = 
  source.getColumns(table);

System.out.println("Begin copy: " + table);

　次に、SELECT文とINSERT文の先頭部分を作成します。

selectSQL.append("SELECT ");
insertSQL.append("INSERT INTO ");
insertSQL.append(table);
insertSQL.append("(");

　さらに、すべての列に対してループ処理を行い、SELECT文とINSERT文を作成します。最初の列名を除くすべての列名の後ろにコンマを挿入します。

boolean first = true;
for (String column : columns)
{

  if (!first)
  {
    selectSQL.append(",");
    insertSQL.append(",");
    values.append(",");
  } else
    first = false;

　各文に列名を追加し、VALUES句に疑問符（?）を追加します。疑問符が必要なのは、パラメータ化されたSQLを使用しているからです。この部分を後から実際の値に置き換えます。文をこのように作成すると、データベースでINSERT文をプリコンパイルして時間を節約することができます。

  selectSQL.append(column);
  insertSQL.append(column);
  values.append("?");
}

　次に、FROM句とVALUES句を追加します。

selectSQL.append(" FROM ");
selectSQL.append(table);

insertSQL.append(") VALUES (");
insertSQL.append(values);
insertSQL.append(")");

　ここまででSELECT文とINSERT文が完成し、SELECT文を実行してレコードのリストを取得できます。

// now copy
PreparedStatement statement = null;
ResultSet rs = null;

try
{
  statement = target.prepareStatement(
    insertSQL.toString());
  rs = source.executeQuery(selectSQL.toString());

　サンプルでは、状態レポート用に行数をカウントしています。

int rows = 0;

　すべてのレコードに対してループ処理を行い、INSERT文を実行します。次に進むごとに値を増分します。

while (rs.next())
{
  rows++;

　それぞれのINSERT文に、個々の列データをコピーします。

for (int i = 1; i <= columns.size(); i++)
{
  statement.setString(i, rs.getString(i));
}

　その後、INSERT文を実行します。

  statement.execute();
}

　最後に、状態情報を表示して終了します。

  System.out.println("Copied " + rows + " rows.");
  System.out.println("");
} 

　最初に述べたように、このサンプルはデータベース間でデータをコピーする簡単なユーティリティですが、私はこれを、より大きなデータベースユーティリティを作成するための土台としてよく利用しています。このユーティリティには、もっと複雑なデータベースコピーアプリケーションを作成するために必要な多くの関数が含まれていることがわかるでしょう。