CentOS 7.4系にインストールしたOracle Database 12c Release 2 (12.2.0.1.0) へ、EclipseでJavaプログラムを作って接続

前回までで、テーブルとかも作成できました。 

ts0818.hatenablog.com

 

となったら、いよいよ、JavaOracle Databaseにアクセスしていこうかと思います。 

JDBCドライバをホストOS側(自分の場合ですと、Windows 10 Home)にインストールしていない場合は、データベースに対応したバージョンのJDBCドライバをインストールしときます。

JDBC, SQLJ, Oracle JPublisher and Universal Connection Pool (UCP)

⇧  Oracleの公式サイトで、JDBCドライバのインストールができます。

 

それでは、レッツトライ。

 

Javaプロジェクトを作成

Eclipseで、 「ファイル(F)」>「新規(N)」>「Java プロジェクト」を選択。

f:id:ts0818:20180422172259p:plain

「プロジェクト名(P):」を入力し、今回は「ワーキング・セット」の「新規(W)...」も選択します。

f:id:ts0818:20180422172650p:plain

Java」を選択し、「次へ(N)>」をクリック。

f:id:ts0818:20180422180137p:plain

「ワーキング・セット名(W):」を入力し、「完了(F)」をクリック。

f:id:ts0818:20180422180358p:plain

そしたらば、「次へ(N)>」をクリック。

f:id:ts0818:20180422180831p:plain

今回は、JDBCドライバをあらかじめ追加してしまおうと思うので、ここで、ライブラリー(L)タブをクリック。

f:id:ts0818:20180422181110p:plain

JDBCドライバーがいないので、追加します。「外部 JAR の追加(X)...」を選択。

f:id:ts0818:20180422181537p:plain

JDBCドライバを選択します。

f:id:ts0818:20180422181811p:plain

ライブラリーに追加されていればOK。「完了(F)」をクリック。

f:id:ts0818:20180422182132p:plain

「パッケージ・エクスプローラー」内に、プロジェクトが作成されました。

f:id:ts0818:20180422182317p:plain

 

データベース接続用のクラスを作成

それでは、実際にデータベースに接続などを行うクラスを作成します。

「パッケージ・エクスプローラー」内のプロジェクトの中の「src」を選択した状態で右クリックし、「新規(W)」>「クラス」を選択。

f:id:ts0818:20180422183240p:plain

「パッケージ(K):」「名前(M)」を適当に付け、「完了(F)」をクリック。

f:id:ts0818:20180422183739p:plain

クラスが作成されました。

f:id:ts0818:20180422183932p:plain

 

データベース接続用のクラスを編集

DB接続用のクラスを編集していきます。 

package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbManage {

	// DB接続情報
	private final String host = "jdbc:oracle:thin:@//127.0.0.1:3333/pdb_diary";
	private final String user = "ts0818";
	private final String pass = "ppp_pwd";

	/**
	 * DBと接続
	 * @return DBコネクション
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getConn() throws ClassNotFoundException, SQLException {

		Class.forName("oracle.jdbc.driver.OracleDriver");

		Connection conn = DriverManager.getConnection(host, user, pass);
		System.out.println("DBに接続しました");
		return conn;
	}

	/**
	 * DB接続を切断
	 * @param conn DBコネクション
	 */
	public void close(Connection conn) {
		try {
			if(conn != null) {
				conn.close();
				System.out.println("切断しました。");
			}

		} catch (SQLException e) {
			// TODO 自動生成された catch ブロック
			e.printStackTrace();
		}
	}


}

とりあえず、シンプルに接続と切断のメソッドだけ用意。

8 データソースおよびURL

docs.oracle.com

によると、hostの部分の記述が、SIDとサービス名のどちらかを使うかで微妙に変わってくるみたいです...安定の不親切さ。

今回は、サービス名(自分の場合ですと、pdb_diary) を使ってます。

 

csvファイルを用意

テーブルに入れるデータをcsvファイルとして用意します。

プロジェクトを選択した状態で右クリックし、「新規(W)」>「フォルダー」を選択。

f:id:ts0818:20180422205521p:plain

「フォルダー名(N):」を適当に入力し、「完了(F)」をクリック。

f:id:ts0818:20180422210213p:plain

フォルダーが作成されました。

f:id:ts0818:20180422210414p:plain

csvファイルを作成します。

作成されたフォルダーを選択した状態で右クリックし、「新規(W)」>「ファイル」を選択。

f:id:ts0818:20180422210734p:plain

「ファイル名(M):」を適当に入力し、「完了(F)」をクリック。

f:id:ts0818:20180422210836p:plain

テーブルのカラム型に合ったデータを入力し、保存。

今回は、book_authorsテーブル用のデータを用意しました。Date型のデータは、yyyy-mm-dd みたいな形にしてます。

f:id:ts0818:20180422213507p:plain

 

 

Javaでテーブルの操作を実装するクラス、つまり、main()メソッドを持つクラスを作成

では、main()メソッドを持つクラスを作成して、実際にDBに接続してテーブルを操作していきたいと思います。

本当は、テーブルに紐づくEntityクラスを作ったり、そのEntityクラスを操作するDAOクラスのようなものを作るのが良いのでしょうが、今回は割愛。

「パッケージ・エクスプローラー」内のプロジェクトの中の「src」を選択した状態で右クリックし、「新規(W)」>「クラス」を選択。

f:id:ts0818:20180422195835p:plain

「パッケージ(K):」「名前(M)」を適当に付け、「public static void main(String[] args)(V)」にチェックを付け、「完了(F)」をクリック。 

f:id:ts0818:20180422200502p:plain

クラスが作成できました。

f:id:ts0818:20180422200650p:plain

実際にINSERT文でOracle Databaseにデータを追加していくことをJavaで実装していきたいのですが、どんな方法でいくのが良いのか? 

 

SQLJavaで実行するためのAPIとしては、 

http://www.knowledge-ex.jp/opendoc/JDBCProgramming.pdf

⇧  上記サイト様によりますと、

  • Statementインタフェース
  • PreparedStatementインタフェース
  • CallableStatementインタフェース
    • – ストアドプロシージャを実行する場合に使用

 の3つがあるようです。

 

 

INSERTのあれこれ

そもそも、Oracle DatabaseでINSERTを効率よくするにはどうすれば?

www.drk7.jp

⇧  上記サイト様によりますと(Perlで実装してるようです。)、

  1. SQL *Loader をバックエンドで実行させて一括登録する
  2. バルクインサート処理を行う PL/SQL を定義して一括登録する
  3. マルチテーブルインサート機能を用いて、1つの insert で複数データを一括登録する
  4. アプリ側から csv のデータを読み込んで for 文で件数分ループして insert する

の順で、処理速度が落ちていくらしいです...csvからデータを取り込んでって方法を考えていたんですが、処理速度的には一番遅いらしいです(涙)。 

でも、例えば、外部のAPIから、大量の売上の情報(大手のコンビニなど?)があって、jsoncsvなどの形式で取得してINSERT処理したい場合なんかどうすれば良いんですかね?

そもそも、そんな設計はありえない?

う~ん、このへんはシステム構築の経験が豊富な先輩とかに聞ける環境があれば、解決できるとは思われるんですが。

 

kagamihoge.hatenablog.com

⇧   上記サイト様によりますと、ある程度、まとめてINSERT文を実行することで処理速度を上げることができるようです。

 

ちなみに、

d.hatena.ne.jp

⇧  上記サイト様によりますと、複数レコードのINSERTには制限があるようで、

monokurotamago.hatenablog.com

⇧  上記サイト様によりますと、Oracle Databaseの仕様上、一度に利用できるバインド変数に制限があるようです。

まとめてのINSERTは、100レコードずつぐらいにしていったほうが良いようです。

 

MySQLの場合については、

d.hatena.ne.jp

⇧  上記サイト様が詳しいです。

上記サイト様も、100レコードずつINSERTしていってるみたいですね。

 

 

重複レコードの問題を回避するために、テーブルにデータが存在しない場合のみINSERTもしくは、UNIONやUNION ALLなどで

テーブルにいざ、INSERTする場合に、気を付けることがあります。 

それは、テーブルの何かしらのカラムにprimary keyを設定した場合(プライマリーキー以外でも、一意制約が設定されてる場合も)、重複したデータを登録できないということです。 

例えば、idというカラムにprimary key を指定してた場合で、id=1のレコードが登録されていたら、id=1のデータはINSERTできないということです。

つまり、INSERTする前に、テーブルにどんなデータが登録されているか確認する必要があるってことですね。

 

scrap.php.xdomain.jp

⇧  上記サイト様で、説明してくれてます。

 

blog.mudatobunka.org

⇧  UPDATEバージョンもあるようです。(MySQLの場合の例のようです。)

UPSERT(INSERTできない場合はUPDATEすること)に関しては、

ON DUPLICATE KEY UPDATE を使うと、テーブルの PRIMARY KEY 、もしくは UNIQUE インデックスの値と、INSERT文で挿入しようとしているデータの値が異なれば INSERTを行うが、同じであれば、ON DUPLICATE KEY UPDATE句で指定した値でUPDATE を実行します。

MySQLのINSERT | dbSheetClient IT技術ブログ

⇧  上記サイト様にもやり方が載ってます。(MySQLの場合の例のようです。) 

 

重複チェックに関しては、UNIONを利用できるときは、利用したほうが良いらしい?(複数テーブルの場合ですかね?)

d.hatena.ne.jp

⇧  上記サイト様で、UNIONとUNION ALLの違いを説明してくれています。

 

ちなみに、プライマリーキー(主キー)制約とユニークキー(一意キー)制約の違いについてよく分かってなかったんだけど、

d.hatena.ne.jp

⇧  上記サイト様によりますと、値としてNULLを許容するかどうかの違いらしいです。primary keyは、値としてNULLはNGということのようです。

 

ameblo.jp

⇧  上記サイト様によりますと、Primary Key(主キー)、UNIQUE(一意キー制約)とユニークインデックスの関係について説明してくれています。

 

d.hatena.ne.jp

⇧  上記サイト様によりますと、

一意制約(一意キー制約とは別物、ややこしいけど)を指定するには、

  • Primary Key(主キー制約)
  • UNIQUE(一意キー制約)
  • UNIQUE INDEX(ユニーク・インデックス)

の3つのいずれか、または組み合わせで実現できるようです、たぶん。 というか、UNIQUEなのに一意キー制約って...キーがどこにも出てこないけどって疑問が湧き出てきますけど...モヤモヤ感が半端ない。

 

一意制約の話は、結構込み入っていて、

teratail.com

⇧   上記サイト様によりますと、何かしらの一意制約を付与しておくほうが無難のようです。

 

だいぶ話が脱線してしまいましたが、とりあえず、重複チェックをどうするかってことですかね。 

 方法としては、

  • UNION、またはUNION ALLを利用
  • 一意制約のキーを対象に重複チェック
    • ストアドプロシージャ使う
    • ストアドプロシージャ使わない
  • わざと例外を起こす(一意制約のエラーの場合と、それ以外のエラーで処理を分ける)

 の3パターンぐらいがネットで言及されてましたかね、というか他の方法が見つからない、または検索の仕方が分からない。

UNION、またはUNION ALLは、おそらく複数テーブルの場合の重複除外っぽいので、今回は使えない感じですかね、と思ったらダミーテーブル(dual)からの取得結果をUNION ALLするという発想でいけるようです。

インラインビューで作成した集合の中で、
一意制約違反が発生しないレコードを、まとめてinsertしてます。

ただし、ファントムリードやアンリピータブルリードによる一意制約違反を完全に防ぐには、
for updateを使って、ロックをかける必要があります。

2-2-6 条件付きinsert(複数レコード)

⇧  ファントムリード、アンリピータブルリードって何?と思ったんですが、

⇩  DBによって結構変わってくる感じですかね?

qiita.com

⇧  上記サイト様によりますと、PostgreSQLの場合について説明してくれています。

その前に、ファントムリード、アンリピータブルリード、リピータブルリード、シリアライザブル、とかって何ぞや?な自分ですので、調べてみました。

また、脱線します。

 

 

トランザクション分離レベルって?

トランザクション分離レベルってのが関わってくるようです。 

トランザクション分離レベル (-ぶんり-)または 分離レベル (英: Isolation) とは、データベース管理システム上での一括処理(トランザクション)が複数同時に行われた場合に、どれほどの一貫性、正確性で実行するかを4段階で定義したものである。隔離レベル 、 独立性レベルとも呼ばれる。トランザクションを定義づけるACID特性のうち,I(Isolation; 分離性, 独立性)に関する概念である。

トランザクション分離レベル - Wikipedia

ACIDのうちの、I(Isolation: 分離性、独立性)に関する部分の概念のようです。

ACIDは、

ACIDとは、信頼性のあるトランザクションシステムの持つべき性質として1970年代後半にジム・グレイが定義した概念で、これ以上分解してはならないという意味の原子性(atomicity不可分性)、一貫性(consistency)、独立性(isolation)、および永続性(durability)は、トランザクション処理の信頼性を保証するために求められる性質であるとする考え方である

ACID (コンピュータ科学) - Wikipedia

ってなってますね。 

で、トランザクション分離レベルに戻ると、

  • SERIALIZABLE ( 直列化可能 )
    • 複数の並行に動作するトランザクションそれぞれの結果が、いかなる場合でも、それらのトランザクションを時間的重なりなく逐次実行した場合と同じ結果となる。このような性質を直列化可能性(Serializability)と呼ぶ。SERIALIZABLEは最も強い分離レベルであり、最も安全にデータを操作できるが、相対的に性能は低い。ただし同じ結果とされる逐次実行の順はトランザクション処理のレベルでは保証されない。
  • REPEATABLE READ ( 読み取り対象のデータを常に読み取る )
  • READ COMMITTED ( 確定した最新データを常に読み取る )
    • 他のトランザクションによる更新については、常にコミット済みのデータのみを読み取る。 MVCC はREAD COMMITTEDを実現する実装の一つである。ファントム・リー に加え、非再現リード(Non-Repeatable Read)と呼ばれる、同じトランザクション中でも同じデータを読み込むたびに値が変わってしまう現象が発生する可能性がある。
  • READ UNCOMMITTED ( 確定していないデータまで読み取る )
    • 他の処理によって行われている、書きかけのデータまで読み取る。PHANTOM 、 NON-REPEATABLE READ 、さらに ダーティ・リード(Dirty Read) と呼ばれる現象(不完全なデータや、計算途中のデータを読み取ってしまう動作)が発生する。トランザクションの並行動作によってデータを破壊する可能性は高いが、その分性能は高い。

の4つのパターンがあるそうです。

ファントムリード、アンリピータブルリード(ファジーリードとも言うらしいです)、ダーティリードとかは、各トランザクション分離レベルで発生しうる問題(読み込み不都合)のことのようです。 

 

qiita.com

⇧  上記サイト様が、説明してくれています。

デフォルトのトランザクション分離レベルも各RDBSによって異なるようで、Oracle Databaseの場合は、READ COMMITTEDが設定されてるようです。

まぁ、どの場合もなにがしかの問題が起こっているわけで...。

 

ロックが解決  - We will rock youとは関係ない

そんでは、問題をどうするか?

gihyo.jp

⇧  上記サイト様によりますと、ロックで解決できるとのこと。

ロック様~(ドウェイン・ジョンソン)! <= まったく関係ないです。

ロックとは,⁠鍵」という名前のとおり,ある資源に対してほかのユーザが使用できないよう鍵をかけることです。データベースにおける資源とは,テーブル,インデックス,シーケンス,ビューなどオブジェクト全般が該当します。

第2回 トランザクションを知ればデータベースがわかる―「データ復旧」「同時実行制御」を行う“不完全な”しくみ(3):DBアタマアカデミー|gihyo.jp … 技術評論社

で、ロックには2種類あるとのこと。

ロックの種類には一般に共有ロック(Sロック)と排他ロック(Xロック)の2つがあり,SはShared,XはeXcludedの略です。それぞれ読み込みロック/書き込みロックとも呼ばれます。

第2回 トランザクションを知ればデータベースがわかる―「データ復旧」「同時実行制御」を行う“不完全な”しくみ(3):DBアタマアカデミー|gihyo.jp … 技術評論社

おそらく、

  • 排他ロック(悲観ロック:悲観的排他制御
  • 共有ロック(楽観ロック:楽観的排他制御

ということではなかろうかということで、

qiita.com

⇧  上記サイト様が詳しいです。

 

で、ロックにも問題が起こることがあって、 

などが挙げられています。 

 

ロックは厳密さを保証する方法だが,常にパフォーマンスとのトレードオフとなるため,⁠I 分離性)のレベルについて選択が必要

第2回 トランザクションを知ればデータベースがわかる―「データ復旧」「同時実行制御」を行う“不完全な”しくみ(3):DBアタマアカデミー|gihyo.jp … 技術評論社

トランザクション分離レベルとロックのバランスが大事ってことですかね。 

 

Oracleはfor updateでロックをかけれるけど...問題が起こる?

で、for updateでロックできちゃうらしいんですね、Oracleは。

だが、しかし!for updateは、排他ロックらしい。

www.projectgroup.info

⇧  上記サイト様によりますと、解決方法はあるようですが、

www.shift-the-oracle.com

⇧  上記サイト様によりますと、COMMITまたはROLLBACKによってロックは解除されるそうです。

 

テーブル単体のときのロックは意味ないってなってますね。 

FOR UPDATE OF で指定するカラム名は、どのテーブルをロックするかを決定するためのものである。 指定フィールドだけが更新できるという制限ではない。対象テーブルが1つの場合にはあまり意味が無い。

SELECT FOR UPDATE - オラクル・Oracle SQL 入門

INSERTの場合は、ロックは厳しそうってなってますね... 

UPDATE や DELETE は SELECT 〜 FOR UPDATE を使用して該当する行をロック可能であるが、 INSERT の場合には直接的な回避方法がない。 間接的な回避策としては 主キー としてシーケンスによる代替キーを定義するか、ロック順序設計自体を見直す。

SELECT FOR UPDATE - オラクル・Oracle SQL 入門

あれ?UNIONだと駄目...UNION ALLはいけるってことですかね? 

 行が一意に特定できなくなる操作などには FOR UPDATE 句は使用できない。

SELECT FOR UPDATE - オラクル・Oracle SQL 入門

ビュー およびインラインビューにも FOR UPDATE を使用することはできるが、以下の内容を含むビューには使用できない。(=更新できないビューに該当するもの)

SELECT FOR UPDATE - オラクル・Oracle SQL 入門

 

rmrmrmarmrmrm.seesaa.net

⇧  上記サイト様でも仰っていますが、for updateは謎が多い?仕様になってるようですね。

 

とりあえず、重複チェック

あれこれ脱線してしまいましたが、今回は、

rmrmrmarmrmrm.seesaa.net

⇧  上記サイト様を参考に、わざとエラー(一意制約違反)を起こすやり方でトライ。

で完成したコードが下記。

package app;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import db.DbManage;

public class OracleDBTest {

  public static void main(String[] args) {

    // プロジェクトのルートディレクトリまでのパス
    String projectRootPath = System.getProperty("user.dir");
    // csvファイルまでのパス
    String resourceDataPath = projectRootPath + "/data/insert.csv";
    // csvファイルのオブジェクト生成
    File data1 = new File(resourceDataPath);
    // パスの区切り文字を使用してる環境に合わせる
    File data2 = new File(data1.getParentFile(), "insert.csv");

    // SQL文の用意
    String insert = "INSERT INTO book_authors VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

    String select = "SELECT * FROM book_authors ";
    // SELECT文の結果を格納する用のオブジェクト
    ResultSet rs = null;

    // データベース接続用のクラスのオブジェクト
    DbManage dbManage = new DbManage();

    int tryInsert = 0;                              // 何行目にINSERTしようとしてるか
    int insertCount = 0;                            // 実際にINSERTできたレコード数
    // 全データの処理状態(INSERTできたかできてないか)
    List<Map<Integer,Integer>> insertCheck = new ArrayList<>();
    Map<Integer,Integer> isErrorCodeAndRow = new HashMap<>();
    // 重複してる行を確認する用


    boolean exsitsInsertData = true;               // INSERTできるデータが存在するか
    int errorCode = 0;
    boolean isExecuteInsert = false;
    StringBuilder sb = new StringBuilder();

    // DB接続、csvファイル読み込みオブジェクト用意
    try(Connection conn = dbManage.getConn();
      BufferedReader br = new BufferedReader(new FileReader(data2));){

      // INSERTできるデータがある間は、ループ
      while(exsitsInsertData) {
        // オートコミットをオフ
        conn.setAutoCommit(false);

        try(Statement stmt = conn.createStatement();
          PreparedStatement ps = conn.prepareStatement(insert);) {

          String line;

          // csvファイルに行がある間はループ
          while((line = br.readLine()) != null) {
            isExecuteInsert = false;
            String[] data = line.split(",", 0); // 行をカンマで区切り配列へ

            // INSERTするデータをセット
            ps.setInt(1, Integer.parseInt(data[0])); // id
            ps.setString(2, data[1]);                // last_name
            ps.setString(3, data[2]);                // first_name
            ps.setString(4, data[3]);                // last_name_kana
            ps.setString(5, data[4]);                // first_name_kana
            ps.setString(6, data[5]);                // birthplace
            ps.setDate(7, Date.valueOf(data[6]));    // birthday
            ps.setString(8, data[7]);                // history

            tryInsert = Integer.parseInt(data[0]);   // 何行目にINSERTしようとしてるか
            isErrorCodeAndRow.put(tryInsert, errorCode);
            insertCheck.add(isErrorCodeAndRow);
            // INSERT文の実行
            ps.executeUpdate();
            isExecuteInsert = true;
            insertCount++;

          }

          // INSERTが実行された場合
          if(insertCount != 0) {

            System.out.println(insertCount + "件のデータが登録されました。");

            // SELECT文のWHERE条件を追記
            sb.append(select);
            sb.append("WHERE ");

            for(Map<Integer, Integer> m: insertCheck) {
              int dataIndex = m.entrySet().size() -1;
              for(Entry<Integer, Integer> entry : m.entrySet()) {
                // INSERTできた行
                if(entry.getValue() == 0) {
                  sb.append("id = ");
                  sb.append(entry.getKey());
                  if(dataIndex != 0) {
                      sb.append(" OR ");
                  }
                  dataIndex--;
                }
              }
            }


            // INSERTされたデータを確認
            rs = stmt.executeQuery(select);
            System.out.println( insertCheck.size() + "件のデータ中" + insertCount + "件が登録されています。");

            while(rs.next()) {
              System.out.println(rs.getInt("id") + "行目");

              System.out.println("ID: "     + rs.getInt("id"));
              System.out.println("名前: "   + rs.getString("author_last_name") + " " + rs.getString("author_first_name") + "(" + rs.getString("author_last_name_kana") + " " + rs.getString("author_first_name_kana") + ")");
              System.out.println("出身: "   + rs.getString("author_birthplace"));
              System.out.println("誕生日: " + rs.getDate("author_birthday"));
              System.out.println("経歴: "   + rs.getString("author_history"));

            }

          }

          // INSERTできるデータが無くなったら、
          conn.commit();
          conn.setAutoCommit(true);
          exsitsInsertData = false;  // ループを抜けるためにフラグをfalse

        } catch (SQLException e) {
          // エラーコード取得
          errorCode = e.getErrorCode();
          // エラーコードが、一意制約違反の場合
          if(errorCode == 1) {

            // 重複データで上書き
            isErrorCodeAndRow.put(tryInsert, errorCode);
            continue;  // スキップ処理

            // 一意制約違反以外のSQLExceptionの場合
          } else {
            //
            e.printStackTrace();
            conn.rollback();
            conn.setAutoCommit(true);
            break;
          }

        } catch (FileNotFoundException e1) {
          //
          e1.printStackTrace();

        } catch (IOException e1) {
          //
          e1.printStackTrace();

        }
      }
    } catch (ClassNotFoundException e2) {
      //
      e2.printStackTrace();
    } catch (SQLException e3) {
      //
      e3.printStackTrace();
    } catch (FileNotFoundException e4) {
      // TODO 自動生成された catch ブロック
      e4.printStackTrace();
    } catch (IOException e4) {
      // TODO 自動生成された catch ブロック
      e4.printStackTrace();
    }

    // 一意制約違反でないSQLExceptionが発生した場合、
    if(exsitsInsertData) {


    } else {

      if(insertCount == 0) {
        System.out.println("すべてのデータは、既に登録されています。");

      // INSERT処理が行われた場合、
      } else {
        // 重複した行数を確認
        for(Map<Integer, Integer> m: insertCheck) {
          for(Entry<Integer, Integer> entry : m.entrySet()) {
            // INSERTできた行
            if(entry.getValue() == 1) {
              System.out.println(entry.getKey() + "行目のデータは重複のためINSERTはされませんでした。");

            }
          }
        }
      }
    }
  }
}

う~ん、これで良いのかの判断がつかないっす。

サイト先の先輩を信じて~。

try-with-resourcesでのrollback問題は、上手い解決策が無さそうです...

stackoverflow.com

どうしても、二重の入れ子になっちゃうのは避けられなさそうですかね。

 

とりあえず、実行してみますか。その前に、仮想マシンを起動して(久々にVagrantで起動)、

vagrant up

f:id:ts0818:20180503133419p:plain

oracleユーザーでsshログイン。

ssh [仮想マシンに存在するユーザー]@[仮想マシンのIPアドレスまたは、ホスト名]

f:id:ts0818:20180503133645p:plain

で、Oracle Databaseにログイン。

sqlplus / as sysdba

f:id:ts0818:20180503134409p:plain

 とりえあず、ORACLEインスタンスの起動とDBのマウント。

startup

f:id:ts0818:20180503134704p:plain

 PDB(プラカブルデータベース)が起動されていないので、

f:id:ts0818:20180503135159p:plain

起動します。

alter pluggable database all open;

f:id:ts0818:20180503135418p:plain

外部から接続するために、リスナーを起動します。

sqlplusでログインしてる場合は、

!lsnrctl start

f:id:ts0818:20180503140223p:plain

でリスナーを起動できるようです。先頭に 「!」マークが欲しいみたいですね。

では、Oracle Databaseの準備も整ったことですし、Eclipse側で、プロジェクトを選択した状態で右クリックし、「実行(R)」>「Java アプリケーション」を選択。

f:id:ts0818:20180503141459p:plain

なんか謎の選択肢が...

f:id:ts0818:20180503141635p:plain

で、「OK」って選択すると、エラー。

f:id:ts0818:20180503142333p:plain

    Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 0
	at oracle.jdbc.driver.JavaToJavaConverter.main(JavaToJavaConverter.java:3562)

で、Google先生に聞いたところ、同じような問題を抱えてる人が。

stackoverflow.com

⇧  答えが出てないみたい(涙)。 

で、mainメソッドのあるクラスを選択した状態で右クリックし、「実行(R)」>「Java アプリケーション」を選択でいけたっぽい。のですが、

f:id:ts0818:20180503143955p:plain

別のエラーが...。

f:id:ts0818:20180503144144p:plain

java.sql.SQLRecoverableException: IOエラー: The Network Adapter could not establish the connection
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:774)
	at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:688)
	at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:39)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:691)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:247)
	at db.DbManage.getConn(DbManage.java:24)
	at app.OracleDBTest.main(OracleDBTest.java:49)
Caused by: oracle.net.ns.NetException: The Network Adapter could not establish the connection
	at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:523)
	at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:521)
	at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:660)
	at oracle.net.ns.NSProtocol.connect(NSProtocol.java:286)
	at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1438)
	at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:518)
	... 7 more
Caused by: java.io.IOException: Connection timed out: connect, socket connect lapse 21002 ms. /172.0.0.1 3333 0 1 true
	at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:209)
	at oracle.net.nt.ConnOption.connect(ConnOption.java:161)
	at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:470)
	... 12 more
Caused by: java.net.ConnectException: Connection timed out: connect
	at sun.nio.ch.Net.connect0(Native Method)
	at sun.nio.ch.Net.connect(Net.java:454)
	at sun.nio.ch.Net.connect(Net.java:446)
	at sun.nio.ch.SocketChannelImpl.connect(SocketChannelImpl.java:648)
	at java.nio.channels.SocketChannel.open(SocketChannel.java:189)
	at oracle.net.nt.TimeoutSocketChannel.>init<(TimeoutSocketChannel.java:81)
	at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:169)
	... 14 more
	.....以下省略

接続に失敗しとる....で、仮想マシンのネットワーク設定を見たところ、

f:id:ts0818:20180503145353p:plain

f:id:ts0818:20180503145241p:plain

⇧  ポートフォワーディングの設定が亡くなっとる!どうやら、vagrant upで起動した影響かしら?

Vagrantを利用する場合、Vagrantfileにポートフォワーディングの設定をしとかないと、ポートフォワーディングのネットワークを構成してくれないようですかね...酷っ。(「クセが強い!」)。

Vagrant forwarded ports allow you to access a port on your host machine and have all data forwarded to a port on the guest machine, over either TCP or UDP.

Forwarded Ports - Networking - Vagrant by HashiCorp 

www.vagrantup.com

VBoxManageで起動し直して、ネットワーク設定を確認したところ、

f:id:ts0818:20180503151633p:plain

f:id:ts0818:20180503151740p:plain

⇧  悲しいとき~、設定してたポートフォワーディングルールが消えてたとき。

ちなみに、ポートフォワーディングルールを設定して、

f:id:ts0818:20180503152305p:plain

VBoxManageで仮想マシンを停止、

f:id:ts0818:20180503152610p:plain

f:id:ts0818:20180503152659p:plain

f:id:ts0818:20180503152740p:plain

⇧  設定は維持されてますね。

では、VBoxManageで仮想マシンを起動。

f:id:ts0818:20180503152902p:plain

f:id:ts0818:20180503152935p:plain

f:id:ts0818:20180503153013p:plain

⇧  設定は維持されてますね。 

では、本題。

VBoxManageで仮想マシンを停止し、Vagrant仮想マシンを起動してみます。

f:id:ts0818:20180503153206p:plain

f:id:ts0818:20180503153306p:plain

f:id:ts0818:20180503153341p:plain

⇧  設定が...消し飛んでるわ~!!!誠に遺憾であります!これで良いのか?Vagrant!え~、ちょっと信じたくないけど...これはアウトな気がするんですが....。

こういうの見ちゃうと、Vagrantイケてないって思ってしまうんですが...。

まぁ、せっかくVagrantを使っているので、

qiita.com

maku77.github.io

kawairi.jp

⇧  上記サイト様を参考に、Vagrantfileに設定していきたいと思います。

f:id:ts0818:20180503155610p:plain

Vagrantfileを開きます。(ファイルの場所はご自分の環境に置き換えてください。)

f:id:ts0818:20180503155852p:plain

Vagrant.configure("2") do |config|
  # The most common configuration options are documented and commented below.
  ... 途中省略
  config.vm.box = "bento/centos-7.3"
  ... 途中省略
  # config.vm.network "forwarded_port", guest: 80, host: 8080, host_ip: "127.0.0.1"
  ... 途中省略  
end

を、「# config.vm.network "forwarded_port", guest: 80, host: 8080, host_ip: "127.0.0.1"」ってなってる部分を、

Vagrant.configure("2") do |config|
  # The most common configuration options are documented and commented below.
  ... 途中省略
  config.vm.box = "bento/centos-7.3"
  ... 途中省略
  config.vm.network "forwarded_port", guest: 1521, host: 3333, id:"oracle12cTest", auto_correct: true
  ... 途中省略  
end

みたいな感じで。guest: 1521の部分は、Oracle Databaseのリスナーのポート番号に合わせてください。host_ipを指定しない場合デフォルトの設定が適応されるらしく、すべてのIPアドレスをバインドしてくれるようです。

f:id:ts0818:20180503161115p:plain

f:id:ts0818:20180503163335p:plain

ポートフォワーディングルールの設定がされました。 

では、oracleユーザーでsshログインし、

f:id:ts0818:20180503163742p:plain

Oracle Databaseにログインし、

f:id:ts0818:20180503163851p:plain

Oracle Databaseの起動、PDBの起動、

f:id:ts0818:20180503164114p:plain

f:id:ts0818:20180503164248p:plain

リスナーの起動前に、PDBに接続して、

f:id:ts0818:20180503170546p:plain

PDBのサービスを起動しておきます。 

f:id:ts0818:20180503170701p:plain

PDBのサービスの登録までは、

ts0818.hatenablog.com

⇧  こちらを見ていただければと思います。 

 

リスナーの起動をして、

f:id:ts0818:20180503164409p:plain

一応、サービスの状態も確認。

f:id:ts0818:20180503171251p:plain

 

EclipseのDBViewerでの接続はできるように。

f:id:ts0818:20180503172636p:plain

で準備が整ったところで、Eclipseより、mainメソッドのあるクラスを選択した状態で右クリックし、「実行(R)」>「Java アプリケーション」を選択。

f:id:ts0818:20180503164611p:plain

で、なんとか実行されたのですが....

文字化けの雨あられ.....ここまで酷いとあっぱれと言ってもいいのでは。

f:id:ts0818:20180504141028p:plain

で、もう一回実行すると、

f:id:ts0818:20180504144202p:plain

⇧  登録されないことが確認できました。

DBViewerで確認すると、SYSユーザーのTABLEに追加していた、book_authorsテーブルにデータが追加されてます、文字化けしてますが(涙)。

f:id:ts0818:20180504144339p:plain

文字化けは、 

www.sejuku.net

⇧  上記サイト様で仰っているように、 

BufferedReader br = new BufferedReader(new FileReader(data2));

ってなってた部分を、

BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(data2), StandardCharsets.UTF_8)); 

みたいにすれば大丈夫かと。(⇐ 駄目でした...涙。)

qiita.com

⇧  どうやら、JDBCドライバのインストールで同封されてる?らしいorai18n.jarってのをEclipseの「ビルド・パスの構成」とかで、ライブラリとして追加しないとマズいようです。

自分の場合は、WindowsOracle Databaseをインストールしたときに一緒にインストールしてたので、Windows側の$ORACLE_HOME¥dmu¥jlib¥orai18n.jarにいました。

f:id:ts0818:20180504190101p:plain

 Eclipseで、プロジェクトを選択した状態で右クリックし、「ビルド・パス(B)」>「ビルド・パスの構成(C)」を選択。

f:id:ts0818:20180504190527p:plain

ほんとは、プロジェクト内にlibフォルダとか作って、そこにライブラリとかは配置しておいて、「JARの追加(J)...」とかにしといたほうが良いとは思うんですが(本番環境にデプロイとかする場合を考えると。)

今回も、「外部JARの追加(X)...」で。

f:id:ts0818:20180504190808p:plain

orai18n.jarを追加しちゃいましょう。

f:id:ts0818:20180504191217p:plain

う~ん、なんかorai18n.jar以外にも、orai18n-○○みたいな感じでいっぱい参照されてるけどOKですかね。

f:id:ts0818:20180504191346p:plain

 

で、一旦、テーブルの中身を全削除して、

f:id:ts0818:20180504150036p:plain

コミットまでしときます。(コミット忘れてて、EclipseJava側のpreparestatementのexecuteUpdateがいつまでも実行されなくてハマりました。涙)

f:id:ts0818:20180504175443p:plain

Eclipseで、Javaプログラムを起動!

駄目でしたけど(涙)。

f:id:ts0818:20180504191920p:plain

⇧  ちょっと文字化けは調査したいと思います。何か分かりましたら、追記します。

 

というわけで、最終的なコードはこんな感じ。もうちょっと何とかしたかったけど...。

package app;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import db.DbManage;

public class OracleDBTest {

  public static void main(String[] args) {

    // プロジェクトのルートディレクトリまでのパス
    String projectRootPath = System.getProperty("user.dir");
    // csvファイルまでのパス
    String resourceDataPath = projectRootPath + "/data/insert.csv";
    // csvファイルのオブジェクト生成
    File data1 = new File(resourceDataPath);
    // パスの区切り文字を使用してる環境に合わせる
    File data2 = new File(data1.getParentFile(), "insert.csv");

    // SQL文の用意
    String insert = "INSERT INTO book_authors VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

    String select = "SELECT * FROM book_authors ";
    // SELECT文の結果を格納する用のオブジェクト
    ResultSet rs = null;

    // データベース接続用のクラスのオブジェクト
    DbManage dbManage = new DbManage();

    int tryInsert = 0;                              // 何行目にINSERTしようとしてるか
    int insertCount = 0;                            // 実際にINSERTできたレコード数
    // 全データの処理状態(INSERTできたかできてないか)
    List<Map<Integer,Integer>> insertCheck = new ArrayList<>();
    Map<Integer,Integer> isErrorCodeAndRow = new HashMap<>();
    // 重複してる行を確認する用


    boolean exsitsInsertData = true;               // INSERTできるデータが存在するか
    int errorCode = 0;
    boolean isExecuteInsert = false;
    StringBuilder sb = new StringBuilder();

    // DB接続、csvファイル読み込みオブジェクト用意
    try(Connection conn = dbManage.getConn();
      BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(data2), StandardCharsets.UTF_8));){

      // INSERTできるデータがある間は、ループ
      while(exsitsInsertData) {
        // オートコミットをオフ
        conn.setAutoCommit(false);

        try(Statement stmt = conn.createStatement();
          PreparedStatement ps = conn.prepareStatement(insert);) {

          String line;

          // csvファイルに行がある間はループ
          while((line = br.readLine()) != null) {
            isExecuteInsert = false;
            String[] data = line.split(",", 0); // 行をカンマで区切り配列へ

            // INSERTするデータをセット
            ps.setInt(1, Integer.parseInt(data[0])); // id
            ps.setString(2, data[1]);                // last_name
            ps.setString(3, data[2]);                // first_name
            ps.setString(4, data[3]);                // last_name_kana
            ps.setString(5, data[4]);                // first_name_kana
            ps.setString(6, data[5]);                // birthplace
            ps.setDate(7, Date.valueOf(data[6]));    // birthday
            ps.setString(8, data[7]);                // history

            tryInsert = Integer.parseInt(data[0]);   // 何行目にINSERTしようとしてるか
            isErrorCodeAndRow.put(tryInsert, errorCode);
            insertCheck.add(isErrorCodeAndRow);
            // INSERT文の実行
            ps.executeUpdate();
            isExecuteInsert = true;
            insertCount++;

          }

          // INSERTが実行された場合
          if(insertCount != 0) {

            System.out.println(insertCount + "件のデータが登録されました。");

            // SELECT文のWHERE条件を追記
            sb.append(select);
            sb.append("WHERE ");

            for(Map<Integer, Integer> m: insertCheck) {
              int dataIndex = m.entrySet().size() -1;
              for(Entry<Integer, Integer> entry : m.entrySet()) {
                // INSERTできた行
                if(entry.getValue() == 0) {
                  sb.append("id = ");
                  sb.append(entry.getKey());
                  if(dataIndex != 0) {
                      sb.append(" OR ");
                  }
                  dataIndex--;
                }
              }
            }


            // INSERTされたデータを確認
            rs = stmt.executeQuery(select);
            System.out.println( insertCheck.size() + "件のデータ中" + insertCount + "件が登録されています。");

            while(rs.next()) {
              System.out.println(rs.getInt("id") + "行目");

              System.out.println("ID: "     + rs.getInt("id"));
              System.out.println("名前: "   + rs.getString("author_last_name") + " " + rs.getString("author_first_name") + "(" + rs.getString("author_last_name_kana") + " " + rs.getString("author_first_name_kana") + ")");
              System.out.println("出身: "   + rs.getString("author_birthplace"));
              System.out.println("誕生日: " + rs.getDate("author_birthday"));
              System.out.println("経歴: "   + rs.getString("author_history"));

            }

          }

          // INSERTできるデータが無くなったら、
          conn.commit();
          conn.setAutoCommit(true);
          exsitsInsertData = false;  // ループを抜けるためにフラグをfalse

        } catch (SQLException e) {
          // エラーコード取得
          errorCode = e.getErrorCode();
          // エラーコードが、一意制約違反の場合
          if(errorCode == 1) {

            // 重複データで上書き
            isErrorCodeAndRow.put(tryInsert, errorCode);
            continue;  // スキップ処理

            // 一意制約違反以外のSQLExceptionの場合
          } else {
            //
            e.printStackTrace();
            conn.rollback();
            conn.setAutoCommit(true);
            break;
          }

        } catch (FileNotFoundException e1) {
          //
          e1.printStackTrace();

        } catch (IOException e1) {
          //
          e1.printStackTrace();

        }
      }
    } catch (ClassNotFoundException e2) {
      //
      e2.printStackTrace();
    } catch (SQLException e3) {
      //
      e3.printStackTrace();
    } catch (FileNotFoundException e4) {
      // TODO 自動生成された catch ブロック
      e4.printStackTrace();
    } catch (IOException e4) {
      // TODO 自動生成された catch ブロック
      e4.printStackTrace();
    }

    // 一意制約違反でないSQLExceptionが発生した場合、
    if(exsitsInsertData) {


    } else {

      if(insertCount == 0) {
        System.out.println("すべてのデータは、既に登録されています。");

      // INSERT処理が行われた場合、
      } else {
        // 重複した行数を確認
        for(Map<Integer, Integer> m: insertCheck) {
          for(Entry<Integer, Integer> entry : m.entrySet()) {
            // INSERTできた行
            if(entry.getValue() == 1) {
              System.out.println(entry.getKey() + "行目のデータは重複のためINSERTはされませんでした。");

            }
          }
        }
      }
    }
  }
}

今回もハマりにハマってしまいました。

Oracle Databaseは、よく分からんですが勉強せねばならんですかね...。

今回は、このへんで。

 

 

2018年5月4日(金)20:55  追記

結論から申し上げますと、自分の場合は、データベース側の文字コードの設定の問題かと思われます。(まだ、文字化けが解決できてないですが)

www.atmarkit.co.jp

teratail.com

⇧  上記サイト様での情報から推察するに、データベースの文字コードは、

「JA16SJISTILDE」が望ましい?ということみたいです。

SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';

f:id:ts0818:20180504194901p:plain

⇧  「US7ASCII」って...こいつかー!

 

たとえば、Oracle InstallerでNLS_LANGが移入されない場合、その値はデフォルトでAMERICAN_AMERICA.US7ASCIIとなります。

NLS_LANG のデフォルト値は AMERICAN_AMERICA.US7ASCII - ablog

d.hatena.ne.jp

⇧  上記サイト様によりますと、Oracle Databaseのインストール時に設定していなかったのがマズかったようです。

 

Oracle Databaseのキャラクタ・セット「UTF8」mutatsu.wordpress.com

⇧  上記サイト様によりますと、「AL32UTF8」の文字コードが良いようです。

 

データベースキャラクタセット(NLS_CHARACTER_SET)とNLS_LANGの間で 文字コードが違う場合、Oracleクライアントにより変換処理が行われる。 その際に問題が発生する。

データベースキャラクタセット(NLS_CHARACTER_SET)とNLS_LANG : eraxメモ

⇧  上記サイト様によりますと、 

  • NLS_CHARACTER_SET
  • NLS_LANG

の2つの組み合わせが大事みたいですね、その組み合わせが分からんのだけども...。 

 

そして、文字コードの変更がとてつもなく面倒くさそう...。

キャラクタ・セットの変更方法の詳細は、Oracle Database Migration Assistant for Unicodeガイド』を参照してください。

キャラクタ・セットの選択

⇧  っていうか、Unicode以外まったく考慮する気が無さそうですね....。

というか、文字コードを変えたいだけなのに、まさかのデータベース作り直しが必要っぽいんですが....ありえないんですけど...。 

 

ということで、文字コード問題の解決方法(Oracle Database Migration Assistant for Unicodeの使い方など)はまた時間のある時にやっていこうと思います、疲れるな~。 

 

2018年5月6日(日)16:15  追記

データベース・キャラクタ・セットはDBインストール時の設定で決めることが重要みたいですね...。

データベースを作成した後でキャラクタ・セットを変更すると、一般的に、時間およびリソースの面で大きなコストがかかります。このような処理を行うには、データベース全体をエクスポートした後で再びインポートすることにより、すべての文字データの変換が必要な場合もあります。そのため、データベース・キャラクタ・セットは、インストールの時点で慎重に選択することが重要です。

インストール中のキャラクタ・セット選択について - Oracle® Databaseグローバリゼーション・サポート・ガイド リリース2 (12.2) for Linux

というか、変更が難しいんだったら、データベース・キャラクタ・セットについて読み飛ばしそうなところに記載するんじゃなくて...まぁ、もう後の祭りですが。

そもそも、レスポンスファイルでのDBインストールに関しては、ほとんど説明らしい説明ないですしね...安定の不親切さ。 

ファイルに記載された説明に従って編集します。

レスポンス・ファイル・テンプレートの編集

docs.oracle.com

 

 

2018年5月13日(日) 追記

下記の記事に、文字データに関して追記してます。 

ts0818.hatenablog.com

 「2018年5月13日(日) 追記(超超超重要):」 を参照ください。