GIG

赴くままに技術を。

DBからモデルにデータを読み込ませる(前編)

以前最適化計算をGAMSで行う - GIGの続き。 モデルと使うデータを外から読み込ませて、計算させたいということで、まずはDBに計算するデータを入れてみる。

今回は使うものはこちら。

ということで、MySQLをインストール(インストール方法は割愛)したら早速Workbenchでデータベースを作っていく。

Connectionの作成

接続できるかまずはConnectionを作成する。

f:id:hermesian:20140621133502p:plain

データベースを作成

  • “Local"コネクション画面で、上メニューのf:id:hermesian:20140621133503p:plainリポジトリに”+”(プラス)マークがついたアイコンをクリックする。

  • 以下のプロパティを設定する

    • Schema Name: transport
    • Default Collation; utf8 - default collation

データベースの利用ユーザを作成

f:id:hermesian:20140621133504p:plain

再び”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図を用いて、作成していく。

f:id:hermesian:20140621133505p:plain

ホーム画面の“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をリセットさせる必要がある)

f:id:hermesian:20140621133508p:plain

f:id:hermesian:20140621133506p:plain

f:id:hermesian:20140621133507p:plain

ER図からテーブルを作成

それでは、作ったER図からテーブルを作成する。 [Database] > [Forward Engineer]を選択する。

f:id:hermesian:20140621133509p:plain

するとデータベースへの接続画面が表示されるので、必要に応じて設定し[Continue]ボタンをクリックする。

f:id:hermesian:20140621133510p:plain

データベースの作成時のオプションとしては、既に同じテーブルをDropするように”DROP Objects Before Each CREATE Object”にチェックし、[Continue]ボタンをクリックする。

f:id:hermesian:20140621133511p:plain

次の”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シート

f:id:hermesian:20140621140638p:plain

--2014.06.29 修正 -- "sandiego" -> "san-diego"

@Plantシート

f:id:hermesian:20140629202942p:plain

@Distanceシート

f:id:hermesian:20140621140640p:plain

注意したいのは、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>


<!-- 省略 -->

DBUnitApache 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=(適当なぱすわぁど)

最後に実行クラスは以下。

gist610709ea8da4ec1b91e1