DBからモデルにデータを読み込ませる(前編)
以前最適化計算をGAMSで行う - GIGの続き。 モデルと使うデータを外から読み込ませて、計算させたいということで、まずはDBに計算するデータを入れてみる。
今回は使うものはこちら。
ということで、MySQLをインストール(インストール方法は割愛)したら早速Workbenchでデータベースを作っていく。
Connectionの作成
接続できるかまずはConnectionを作成する。
- ”MySQL Connections”の横の+マークを選択
- 必要な項目を入力する
- Connection Name : Local
- Hostname: 127.0.0.1(デフォルト)
- Port: 3306(デフォルト)
- Username: root(デフォルト)
- パスワードを聞かれるけど、MySQLはインストール時のrootパスワードは空であることに注意(参照: MySQL :: MySQL 4.1 リファレンスマニュアル :: 4.4.4 MySQL 権限の初期設定)
データベースを作成
“Local"コネクション画面で、上メニューのリポジトリに”+”(プラス)マークがついたアイコンをクリックする。
以下のプロパティを設定する
- Schema Name: transport
- Default Collation; utf8 - default collation
データベースの利用ユーザを作成
再び”Local"コネクション画面で、左ペインの[Users and Privileges]を選択して、作業ユーザを作成する
- [Login]タブで
- “Login Name” ; work
- “Limit Connectivity to Hosts Matching” ; localhost
- “Password / Confirm Password” ; (適当なパスワード)
- [Schema Privileges]タブで
- [Add Entry]をクリックして、[Selected schema]から対象のデータベースを選択する(今回なら"transport")
- その後[Select “ALL”]でtransportデータベースに対して、全操作を許可
- 最後に[OK] > [Apply]ボタンをクリック
ER図を作成
先ほど作成した”tranport”データベース上にテーブルをER図を用いて、作成していく。
ホーム画面の“Models”の横3つ目のマークを選択し、[Create EER Model from Database]を選択し、画面に従ってER図作成画面に進む。
参考にしたのは、$GAMS_HOME/gams24.2_osx_x64_64_sfx/apifiles/Data/transport.xls。
今回は、CapacityとDemandの両地域の総当たり表なので、中間テーブルとして表現しようとしたが、モデルを登録する人からすると、外部キーの組み合わせを考えながら、登録するのはやってられないので、3つ別々のテーブルとした。
その後、[EER Diagrams] > [Add Diagram]で3つ下のようなテーブルを作ってみる。 オートインクリメントさせたい場合は、[AI]にチェックする(こうすると、後述のExcelシートではIDが必要ないが、IDをリセットさせる必要がある)
ER図からテーブルを作成
それでは、作ったER図からテーブルを作成する。 [Database] > [Forward Engineer]を選択する。
するとデータベースへの接続画面が表示されるので、必要に応じて設定し[Continue]ボタンをクリックする。
データベースの作成時のオプションとしては、既に同じテーブルをDropするように”DROP Objects Before Each CREATE Object”にチェックし、[Continue]ボタンをクリックする。
次の”Select Objects”は[Show Filter]で作りたいテーブルを絞ったりすることができるみたい。今回はそのまま作るので、[Continue]ボタンをクリックする。
それで出来たDDLは以下。
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; DROP SCHEMA IF EXISTS `transport` ; CREATE SCHEMA IF NOT EXISTS `transport` DEFAULT CHARACTER SET utf8 ; USE `transport` ; -- ----------------------------------------------------- -- Table `transport`.`MARKET` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `transport`.`MARKET` ( `id` INT NOT NULL, `MARKET` VARCHAR(45) NOT NULL, `DEMAND` VARCHAR(45) NOT NULL, `DESCRIPTION` VARCHAR(45) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `transport`.`PLANT` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `transport`.`PLANT` ( `id` INT NOT NULL, `PLANT` VARCHAR(45) NOT NULL, `CAPACITY` VARCHAR(45) NOT NULL, `DESCRIPTION` VARCHAR(45) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `transport`.`DISTANCE` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `transport`.`DISTANCE` ( `PLANT` VARCHAR(45) NOT NULL, `MARKET` VARCHAR(45) NOT NULL, `DISTANCE` VARCHAR(45) NOT NULL) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
最後にこのDDLが発行される。これでForward Engineer終了。 テーブルが作成されていることが確認できる。
ExcelデータをDBUnitを使って読み込み
DBUnitを使って、Excelからデータを読み込むようにしてみた。 読ませるデータは、Excel(xls形式)で各シートが一つのテーブルとして読み込まれる。
@Marketシート
--2014.06.29 修正 -- "sandiego" -> "san-diego"
@Plantシート
@Distanceシート
注意したいのは、xlsx形式のファイルには対応しておらず、実行すると下記のようなエラーが発生する(内部で利用しているApache POIがxlsのAPIを利用しているため)。
Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML. You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
まずはpom.xmlについて。
<!-- 省略 --> <!-- DBUnit --> <dependency> <groupId>org.dbunit</groupId> <artifactId>dbunit</artifactId> <version>2.5.0</version> </dependency> <!-- MySQL Connector --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.30</version> </dependency> <!-- Apache POI --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.2-FINAL</version> </dependency> <!-- 省略 -->
DBUnitとApache POIの互換性を考えないと行けなくて、安易にApache POIの最新版を選択すると、下記のような例外が実行時に発生する。
Exception in thread "main" java.lang.NoSuchMethodError: org.apache.poi.hssf.usermodel.HSSFDateUtil.isCellDateFormatted(Lorg/apache/poi/hssf/usermodel/HSSFCell;)Z
現行の3.10-FINALは無理だったので、3.2-FINALを使ってみたら大丈夫でした。(参照: 有効なWikiNameではありません - @//メモ)。
またDBのプロパティはpropertyファイルとして用意する。
@ mysql.properties
# DBのプロパティ DB_URL=jdbc:mysql://localhost:3306/transport DB_USER=work DB_PASSWORD=(適当なぱすわぁど)
最後に実行クラスは以下。