20170627水野

[ prev | next ]

JavaからのDB利用

演習課題

大学側ではなく、学生側が自分の履修管理のために使うデータベースを作ろうとゆう課題です。　従って自分以外の学生の履修状況などは含みません。

１）サンプルプログラム（DerbyTest.java）の動作確認

２）データ定義プログラム
科目（科目ID、科目名、実施期、単位数、科目区分）
履修（科目ID、履修期、成績）
※ドメインは下記のテキストファイルを見て適切に決めてください。

３）タブ区切テキストからのデータ入力プログラム
科目　専門の科目のリスト。簡単にするために通年科目は後期開講に変更してあります。
履修　成績の空欄は履修中で成績未定の意味です。
※読み取りを容易にするためデータが空の場合でも区切のタブは必ず在ります。
※通年科目も扱うにはどのようにすればいいか？考えてみよう。

４）データの出力プログラム

4-1 科目区分ごとの既取得単位合計を出力する

科目区分 総単位数
必修科目  23
選択科目A 12
選択科目B  8
選択科目C  6
選択科目D  4
選択科目E  3

4-2 履修中の科目が全て合格の場合の科目区分ごとの取得単位合計を出力する

科目区分 総単位数
必修科目  27
選択科目A 12
選択科目B  8
選択科目C 10
選択科目D  6
選択科目E  5
随意科目   2

ここまでの回答例？

---------次回の課題-----------

５）進級や卒業の要件をデータベースに取り込んでチェックできるようにする。

ここは完成しなくてもいいので、できたところまでを示してください。例えば要件をどんな形のテーブルに格納したかなど。答えは無いので色々工夫してみよう。工夫の跡が分かるようなレポートを作ってください。

３年への進級では１年までの必修科目が全て必要です。３年進級では２年次の実験科目が必要ですす。このような条件はどのようにしてチェックできるでしょう。全てをプログラムコードとして書いてしまう事もできますが、それでは条件が変わった時の更新が大変です。

0.　Apach Derby

　Derbyと呼ばれるjavaで書かれたRDBMSがあります。javaで書かれていてSQLもサポートするデータベースソフトです。javaの開発キットjdkにも付いてきます。ただし、一体化はしておらず、下記のようにjdkがインストールされたフォルダのdbフォルダに格納されています。

jdk1.8.0_121を標準でインストールすると

C:\Program Files\Java\jdk1.8.0_121\db

に格納されています。（ｊdkのバージョンによって「jdk1.8.0_121」の部分は変わります）

Derbyはjavaソフトの中に組み込まれたRDBMSとして利用することも、ネットワークから利用するRDBMSサーバーとして使うこともできます。

javaプログラムからRDBMSを利用したい場合、Derbyは有力な候補です。

※Derbyを元からダウンロードしたければアパッチの下記サイトから

Apache Derby

リファレンス

※フリーのデータベースとしてはMySQLなどが良く使われています。スピードなど効率性を必要とする場合はこちらの方が良いでしょう。　しかし、テーブル名などに日本語文字を使うと、その部分がOS依存だったりして注意が必要です。

組み込み型のデータベースとして使う

組み込み型のデータベースで実行するサンプル　DerbyTest.java

コンパイルは通常通りです。

javac DerbyTest.java

しかし、実行ではlibフォルダにあるDerbyのライブラリderby.jarをパスに加えておくことが必要です。実行のコマンドラインは

java -classpath "./;C:\Program Files\Java\jdk1.8.0_121\db\lib\derby.jar" DerbyTest

※ｊdkのバージョンによって「jdk1.8.0_121」の部分は変わります

上記のようなコマンドラインで実行した場合。カレントディレクトリーにtestフォルダが作られデータベースのファイルはここに格納されます。

独立したデータベースとして動かす

起動するためのバッチファイルや操作用のプログラムファイルがbinフォルダに格納されています。この他darby関連のライブラリーはlibフォルダにある。javaプログラムの実行ではクラスパスにderby.jarとderbyclient.jarを設定する必要があります。

※Windowsのサービスとして動かすことも可能ですが、サービス化するためにはサービス化ラッパーを必要とします。Tomcatのサービス化で使われているprocrunなどがあります。

1.JDBC

jdkに標準で用意されているRDB(リレーショナル・データベース)にアクセスするために使うAPI（アプリケーション・プログラム・インターフェース）。javaプログラマはJDBCを実装した標準のクラスライブラリを利用してデータベース管理システム(DBMS)を操作できます。

javaプログラムはJDBC APIでJDBCドライバマネージャと会話し、このマネージャはJDBCDriverAPIでJDBCドライバと会話する。そさらに、JDBCドライバがネイティブプロトコルでデータベースと会話します。ここでマネージャが有るのは沢山の種類があるJDBCドライバを使い分けることができるようにするためです。ドライバはネイティブプロトコルを使うのでデータベースの種類ごとに用意する必要があり、普通はデータベースソフトの提供者が開発した物を使うことになるでしょう。しかし、ドライバとデータベースの間にプロトコルの違いを吸収する仕組みを挟めばドライバ部分のデータベース依存を解消できます。この場合、違いを吸収する部分はデータベース依存ですが、javaプログラムの部分はドライバまで含めてデータベース依存から開放されることになります。

javaプログラム
<=JDBC API=>
JDBCドライバマネージャ　
<=JDBCDriverAPI=>
JDBCドライバ　　＜＜接続するデータベースに応じて用意する必要あり
<=ネイティブプロトコル=>
データベース管理システム

1-1.JDBCドライバマネージャ：`java.sql.DriverManager`クラス

JDBCドライバの管理機能を提供するクラス。管理対象のJDBCドライバのロード方法は2種類ある。

１）システムプロパティのjdbc.driversでドライバを指定。（DriverManager の初期化時にロード）

２）プログラム中でクラスローダを用いてドライバクラスをロードする
例）　Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

スタティク・メソッドDriverManager.getConnection() を呼び出すと、ロードされたドライバの中から適切なドライバを探しデータベースと接続したConnectionオブジェクトを戻す。

1-2.JDBCドライバ

ドライバには下記の様なタイプがある。以下は　http://www.atmarkit.co.jp/fjava/rensai/jdbc01/jdbc01.htmlを参考にした。Appletのようにdownloadして実行されるプログラムではタイプ３，４を使う必要がある。

タイプ1 ：JDBC-ODBCブリッジ・ドライバ
データベースの違いを吸収するためにODBCドライバと接続するドライバ。
実行環境内に目的のデータベースと接続済みのODBCドライバが必要。
ODBCドライバの設定が必要だが、JDBC-ODBCブリッジ・ドライバはjdkに付いてくる
（この方法を紹介したかったがWindows7用のAccess用ODBCドライバがインストールされていないので断念。2012年度まで使っていたJDBCのサンプルプログラムはJDBCTest.java。）
タイプ2 ：ネイティブ・ブリッジ・ドライバ
ネイティブライブラリなどを使って目的のデータベースと接続する。
ドライバがデータベース依存になるだけでなく、実行環境に対応したネイティブライブラリのインストールも必要。
タイプ3 ：ネット・プロトコル・ドライバ
中間サーバを介してデータベースと接続するドライバ。中間サーバとはネット・プロトコルで接続する。
中間サーバが必要で仕組みが複雑になるが、ドライバはデータベースに依存しない形にできる。
タイプ4 ：ネイティブ・プロトコル・ドライバ
じかにデータベースとネイティブ・プロトコルで接続するドライバ。接続は単純だが目的のデータベースに対応したドライバが必要。ドライバがデータベース依存になる。

DerbyのJDBCドライバー

上記の分類でどのタイプに属するかは明記されていませんが、JDBCとDerbyを繋ぐドライバーは2種類あります。

組み込み用のドライバー（タイプ2？）
derby.jar
に含まれます。以下の演習はこちらで行います
Derbyのサーバと繋ぐクライアント用のドライバー（タイプ4？）
derbyclient.jar　
ネットワーク経由でDerbyのデータベースサーバーに繋ぐような場合に使います

2.データベースとの接続

2-1.　ドライバのロード

ここではクラスローダを用いる例を示す。

ドライバクラスのロードはアプリケーションの中でデータベースとの接続の前に行えばいい。ここではクラスの初期化時に行う例を示す。この場合、クラスローダに対して直接ロードを指示することになる。

static private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
//JDBC-Derbyの組み込み用ドライバー
//JDBC-ODBCのドライバーなら次のようになる
//static private String driver ="sun.jdbc.odbc.JdbcOdbcDriver";

//クラスの初期化時にクラスローダを使ってドライバーを読み込む
static{
    try{
        Class.forName(driver).newInstance();
        System.out.println("Loaded the driver:"+driver);
    } catch (Exception e) {
        e.printStackTrace(System.err);
    }
}

2-2.　データベースとの接続開始(open)

データベース接続の窓口となるのはjava.sql.Connectioインターフェイスを実装したオブジェクトで、DriverManagerのstaticメソッドgetConnection()で取得する。このときに引数で、データソースのurlと接続時に必要なユーザＩＤ:idとパスワードpwを渡す。

たとえばDarbyの名前が「test」の組み込みデータベースを開いて接続する場合は次のプログラム例のようになる。

※データベースはコメントにあるようにカレントディレクトリにtestディレクトリを作成しデータを格納する。

下記では接続の窓口のconnectionを生成し続いてSQL文を実行するためのオブジェクトstatementを作成した。


static private String protocol = "jdbc:derby:";//JDBC-Darbyのドライバーで接続
//static private String protocol = "jdbc:odbc:";//JDBC-ODBCのドライバーで接続
static private String dbName = "test"; 
//Darbyはカレントディレクトリにtestディレクトリを作成しここにデータベースを格納する
static private String opption = "create=true";//データベースが無い場合は新規作成。
static private String url = protocol + dbName + ";" + opption + ";" ;

static private String id = "SYSBDA";
static private String pw = "masterkey;";

public void open()
{
    try{

        connection=DriverManager.getConnection(url,id,pw);

        statement=connection.createStatement();

    }catch(SQLException ex){
        ex.printStackTrace();
    }
}

2-3.　データベースの情報を得る

データソースの名前やバージョン、中身のテーブルのリストなどを得る例。結果がテーブルの形のデータの場合は戻り値はResultSetのオブジェクトとなる。

書式などのデータを、データのデータの意味でメタデータと言う。まずは作ったばかりのデータベースにどの様なメタデータが在るのかを見てください。RDBMSはテーブルのスキーマなどのメタデータをシステム側のテーブルで管理しています。

public void printMataData()
{
	ResultSet rs=null;
	try{
		DatabaseMetaData dmd=connection.getMetaData();
		////////////////////////////
		System.out.println("\nデータベースMetaData");
		System.out.println("DBMS製品名："+dmd.getDatabaseProductName());
		System.out.println("バージョン："+dmd.getDatabaseProductVersion());
		System.out.println("外部結合の可否："+dmd.supportsOuterJoins());
		System.out.println("最大同時接続数："+dmd.getMaxConnections());

		///////////////////////////
		//データベースカタログの取得
		rs= dmd.getCatalogs();
		System.out.println("\nデータベースCatalog");
		while(rs.next()){
			System.out.println(rs.getString(1));
		}	
		rs.close();
		rs=null;
		//データベース内のテーブルのリストを得る（システム表を含む）
		//getTables(
                //  String catalog, String schemaPattern,
                //  String tableNamePattern, String[] types
                //  ) 
		//ここでは条件を付けずに検索

		rs= dmd.getTables(null,null,null,null);
		System.out.println("\nデータベース内のTable");
		while(rs.next()){
			System.out.println(" カタログ:"+rs.getString(1));
			System.out.println(" スキーマ:"+rs.getString(2));
			System.out.println(" 表名:"+rs.getString(3));
			System.out.println(" タイプ:"+rs.getString(4));
			System.out.println(" 説明:"+rs.getString(5));
			System.out.println();
		}
		rs.close();
		rs=null;
	}catch(SQLException ex){
		if(rs!=null)
			try{rs.close();}catch(Exception e){ex.printStackTrace();}
		ex.printStackTrace();
	}
}

2-4.　データベースを閉じる(close)

接続を閉じる場合は次のようになる。いったん開いた接続は必ず閉じること。

public void close()
{
	try{
		statement.close();
		connection.close();
	}catch(SQLException ex){
		ex.printStackTrace();//失敗したら再度closeを実行するわけにも行かない
	}
}

3.　SQL文の実行

SQL文を実行するのがStatementオブジェクト。既定値ではこれがトランザクションの単位にもなる。実行命令は戻り値の有無で2種類有る。

結果表を戻さない無い場合　executeUpdate（SQL文）

結果表を戻す場合：ResultSetオブジェクトの形で戻す　executeQuery（SQL文）

3-1.　データ定義

実行結果は実行できたか否かなのでexecuteUpdate（SQL文）でSQL文を実行します。戻り値は有りませんがエラーが在れば例外を投げます。

表の生成（CREATE TABLE）

public void testCreateTable()
{
    //テーブルを作るSQL文
    String sqlString=
        "CREATE TABLE WebUser ("
        +" userID INTEGER NOT NULL,"
        +" name VARCHAR(50),"
        +" access CHAR(2),"
        +" PRIMARY KEY(userID)"
        +")"
    ;
    try{
        statement.executeUpdate(sqlString);//SQL文の実行
    }catch(SQLException ex){
        ex.printStackTrace();
    }
}

表のメタデータの印刷

作られた表の確認ができます。

public void printTabelMetaData(String tableName)
{
    try{
        DatabaseMetaData dmd=connection.getMetaData();

        //全属性のリスト
        ResultSet rs= dmd.getColumns(null,null,tableName,null);
        System.out.println("--「"+tableName+"」の属性　SQL型(サイズ)--");
        while(rs.next()){
            /*メタデータの順番ではなくプロパティー名でgetした*/
            System.out.print(rs.getString("COLUMN_NAME"));
            System.out.print("\t"+rs.getString("TYPE_NAME"));
            System.out.print("\t("+rs.getInt("COLUMN_SIZE"));
            System.out.print(")");
            if( rs.getString("IS_NULLABLE").equals("NO"))System.out.print("\tNOT NULL");
            System.out.println();
        }
        rs.close();

        //主キーのリスト
        rs= dmd.getPrimaryKeys(null,null,tableName);
        System.out.print("PRIMARY KEY(");
        boolean first=true;
        while(rs.next()){
            if(first) first=false; 
            else System.out.print(", ");
            System.out.print(rs.getString("COLUMN_NAME"));
        }
        System.out.println(")");
        rs.close();

        //外部キーのリスト
        rs= dmd.getImportedKeys(null,null,tableName);
        while(rs.next()){
            System.out.print("FOREIGN KEY("+rs.getString("FKCOLUMN_NAME")+")　");
            System.out.print(" REFERENCES "+rs.getString("PKTABLE_NAME"));
            System.out.print(" ("+rs.getString("PKCOLUMN_NAME")+")");
            System.out.println();
        }
        rs.close();
    }catch(Exception ex){ex.printStackTrace();}
}

表の削除（DROP TABLE）

public void testDropTable()
{
	//テーブルを削除するSQL文
	String sqlString="DROP TABLE WebUser";

	try{
		statement.executeUpdate(sqlString);//SQL文の実行
	}catch(SQLException ex){
		ex.printStackTrace();
	}
}

課題のヒント

課題の科目表の場合、例えば以下のようにもできる。ドメインと主キーや外部キーには注意してください。

public void createTable()
{
    try{
        statement.executeUpdate(
            "CREATE TABLE 科目("
            +" 科目ID INTEGER NOT NULL,"
            +" 科目名 VARCHAR(50) NOT NULL,"
            +" 実施期 INTEGER,"
            +" 単位数 INTEGER,"
            +" 科目区分 VARCHAR(10),"
            +" PRIMARY KEY(科目ID)"
            +")"
        );
    }catch(SQLException ex){
        ex.printStackTrace();
    }
}

3-2.　データ操作

戻り値の有無で、executeUpdate（SQL文）とexecuteQuery（SQL文）を使い分けることが必要です。何れも例外を投げることが在るので、例外処理を忘れずに行ってください。

挿入(INSERT)

Statementの一種として繰り返し似たようなSQL文を効率よく実行するためのPreparedStatemantがある。これは後で置き換える部分を「？」で示したSQL分を渡してconnectionから生成される。下記の例のように各「？」部分を置き換えてからSQL文を実行する。

public void testInsert()
{
	String strInser="INSERT INTO WebUser VALUES (?,?,?)";//?部分は使う前に置き換える
	PreparedStatement insertStmt=null;	
	try{
		//繰り返し使うので予めPreparedStatementを準備
		//?部分は使う前に置き換えるが
		//データベースによつては文字列の長さが変化するとエラーになる場合が有る
		insertStmt=connection.prepareStatement(strInser);
		//
		//適当に10個のデータを追加
		for(int i=0;i<10;i++){
			insertStmt.clearParameters();//初期化
			insertStmt.setInt(1,20010000+i);//1番目の?をint型の整数で置き換える
			insertStmt.setObject(2,"学生" +i,Types.VARCHAR);//2番目の?を文字列で置き換える
			insertStmt.setString(3,"OK");//3番目の?を2文字で置き換える
			//
			insertStmt.executeUpdate();//テーブル学生にデータを追加（SQLの実行）
		}
		//
		insertStmt.close();
	}catch(SQLException ex){
		if(insertStmt!=null)
			try{insertStmt.close();}catch(Exception e){e.printStackTrace();}
		ex.printStackTrace();
	}
}

検索　（SELECT）

実行結果として表（結果表）が戻るSQL文の実行はexecuteQuery（SQL文）で行う。結果はResultSetのオブジェクトとして戻される。　


public void testSelect()
{
	String strSelect="SELECT * FROM WebUser ORDER BY userID";//学籍番号順で結果テーブルを作る
	ResultSet rs=null;	
	try{
		rs=statement.executeQuery(strSelect);//一覧をrsに取り出す
		while(rs.next()){
			int id=rs.getInt(1);
			String name=rs.getString(2);
			String access=rs.getString(3);
			System.out.println(id+"\t"+name+"\t"+access);
		}
		rs.close();
	}catch(SQLException ex){
		if(rs!=null)
			try{rs.close();}catch(Exception e){e.printStackTrace();}
		ex.printStackTrace();
	}
}

課題のヒント

テキストからデータを読む部分と、逆にデータベースから読んだデータを書き出す場合の例を示します。

タブ区切りのテキストから読む場合、１行文字列からのデータの切り出しが結構面倒です。またデータを挿入する場合もデータが空の時にはデータベースに空値Nullを設定する必要があります。

※テキストデータの区切りになるタブが必ず在ると仮定しています。この仮定が無いともっと煩雑になる。

※タブ区切りの文字列を読み取る関数を用意すると、もう少し簡潔に書けるかも？

※テーブルのメタデータを読み出して読み取るデータとの対応をとるようにすると、テーブル名とファイル名を渡すと読み込んでくれるような汎用性のある関数がけけるはずです。

public void read科目File()
{
    PreparedStatement insertStmt=null;
    try{
        FileInputStream stream=new FileInputStream(new File("科目.txt")); 
        BufferedReader in=new BufferedReader(new InputStreamReader(stream,"JISAutoDetect"));
        String str;
        String strInser="INSERT INTO 科目 VALUES (?,?,?,?,?)";//?部分は使う前に置き換える
        insertStmt=connection.prepareStatement(strInser);
        //
        str=in.readLine();//先頭を1行読み飛ばす
        while(in.ready()){

            str=in.readLine();//1行読んで
            int pos0=0;
            int pos;//最初のタブの位置
            int lastIndex=str.length();

            //初期化
            insertStmt.clearParameters();

            //1番目の?をintで置き換える
            pos=str.indexOf("\t",pos0);
            insertStmt.setInt(1,Integer.parseInt(str.substring(pos0,pos)));
            pos0=pos+1;
            //2番目の?をStringで置き換える
            pos=str.indexOf("\t",pos0);
            insertStmt.setObject(2,str.substring(pos0,pos),Types.VARCHAR);
            pos0=pos+1;
            //3番目の?をintで置き換える
            pos=str.indexOf("\t",pos0);
            if(pos == pos0){
                //空の場合
                insertStmt.setNull(3,Types.INTEGER);
            }else{
                insertStmt.setInt(3,Integer.parseInt(str.substring(pos0,pos)));
            }
            pos0=pos+1;
            //4番目の?をintで置き換える
            pos=str.indexOf("\t",pos0);
            if(pos == pos0){
                //空の場合
                insertStmt.setNull(4,Types.INTEGER);
            }else{
                insertStmt.setInt(4,Integer.parseInt(str.substring(pos0,pos)));
            }
            pos0=pos+1;
            //5番目の?をStringで置き換える
            if(lastIndex==pos0){
                //空の場合
                insertStmt.setNull(5,Types.VARCHAR);
            }else{
                insertStmt.setObject(5,str.substring(pos0));
            }
            //テーブルにデータ挿入（SQLの実行）
            insertStmt.executeUpdate();
        }
        insertStmt.close();
    }catch(Exception ex){
        if(insertStmt!=null)
        try{insertStmt.close();}catch(Exception e){e.printStackTrace();}
        ex.printStackTrace();
    }
}

こちらはテーブルを読んでタブ区切で書き出す関数

public void print科目Table()
{
    String strSelect="SELECT * FROM 科目 ";
    ResultSet rs=null;
    try{
        rs=statement.executeQuery(strSelect);//一覧をrsに取り出す
        while(rs.next()){
            System.out.print(""+rs.getInt(1)+"\t"+rs.getString(2)+"\t");

            //以下は値が無い場合があるのでnullかどうかを調べながらになる。
            //メソッドwasNull()は直前でgetした値がNullなら真
            int n=rs.getInt(3);
            if( ! rs.wasNull()) System.out.print(""+n);
            System.out.print("\t");
            n=rs.getInt(4);
            if( ! rs.wasNull()) System.out.print(""+n);
            System.out.print("\t");
            String kclass=rs.getString(5);
            if( ! rs.wasNull()) System.out.print(kclass);
            System.out.println("");
        }
        rs.close();
    }catch(SQLException ex){
        if(rs!=null)
        try{rs.close();}catch(Exception e){e.printStackTrace();}
        ex.printStackTrace();
    }
}

※SQL文を実行するだけならSQLの知識だけで足りますが、データの渡し方や受け取り方はデータベースを利用するソフトウエア側に用意されたライブラリで様々です。ここではjavaの標準ライブラリの使い方を紹介しました

JavaからのDB利用

演習課題

0. Apach Derby

組み込み型のデータベースとして使う

独立したデータベースとして動かす

1.JDBC

1-1.JDBCドライバマネージャ：java.sql.DriverManagerクラス

1-2.JDBCドライバ

DerbyのJDBCドライバー

2.データベースとの接続

2-1. ドライバのロード

2-2. データベースとの接続開始(open)

2-3. データベースの情報を得る

2-4. データベースを閉じる(close)

3. SQL文の実行

3-1. データ定義

表の生成（CREATE TABLE）

表のメタデータの印刷

表の削除（DROP TABLE）

課題のヒント

3-2. データ操作

挿入(INSERT)

検索 （SELECT）

課題のヒント

0.　Apach Derby

1-1.JDBCドライバマネージャ：`java.sql.DriverManager`クラス

2-1.　ドライバのロード

2-2.　データベースとの接続開始(open)

2-3.　データベースの情報を得る

2-4.　データベースを閉じる(close)

3.　SQL文の実行

3-1.　データ定義

3-2.　データ操作

検索　（SELECT）