データベースとの連携 (Java アプリケーション)

1. 検索条件の指定　(1)

SQLの選択文を利用してデータベースの検索をおこなうには、以下の手続きをとる必要がある。

createStatement()メソッドを使って、ConnectionオブジェクトからStatementオブジェクトを生成する
StatementオブジェクトのexecuteQuery()メソッドにより select文を実行し、結果をResultSetオブジェクトとして取得する。
ResultSetオブジェクトのgetXXX()メソッドを利用して結果の値を取得する。

次のプログラム JdbcTest7.java は、

java JdbcTest7  ↓

により、検索条件が与えられ、データの検索をおこなう。このプログラムでは、fruitテーブルのpriceの値が100より小さいレコードnameフィールドとpriceフィールドの値の一覧を表示する。

import java.sql.*;

public class JdbcTest7 {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost/test";	
			String user = "root";
			String password = "tuis2019system";
			Connection conn =　DriverManager.getConnection(url, user, password);
			
			Statement stmt = conn.createStatement();
			ResultSet rs = 　stmt.executeQuery("SELECT name, price FROM fruit WHERE price < 100");
			
			while (rs.next()) {
				String name = rs.getString("name");
				int price = rs.getInt("price");
				System.out.println(name + " : " + price);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch ( SQLException e ) {
			e.printStackTrace();
		} catch ( Exception e ) {
			e.printStackTrace();
		}
	}
}

プログラム JdbcTest7.java の説明：

JdbcTest7.javaは、 localhostにあるデータベースサーバと接続し、プログラムを実行する。ユーザ名は"root"、パスワードは"tuis2008system" である。

まず、

import java.sql.*;

では、DBC2.0 Core APIであるjava.sqlパッケージがインポートされる。

 Class.forName( "com.mysql.jdbc.Driver" );

では、クラスローダによりJDBCドライバを読み込んでいることを示している。引数は、データベースにアクセスするためのJDBCドライバのクラス名である。

    String url = "jdbc:mysql://localhost/test";

では、データベースをあらわすURLを設定している。データベースをあらわすURL（データベースURL)は、データベースに接続する場合に必要となる情報をセットした文字列である。この文字列の構造は、 "jdbc"、サブプロトコル、サブネームの３つの部分から構成される。ここでは、localhost上のtestデータベースと接続することをあらわしている。

    String user           = "root";
    String password       = "tuis2019system";

では、データベースの接続に用いるユーザ名"root"を指定しており、その際のパスワードは"tuis2008system"をあらわす。

conn = DriverManager.getConnection( url, user, password );

では、DriverManagerクラスのgetConnectionメソッドを使ってデータベースに接続する。ここで、第一引数に与えているものが、上記のデータベースURL である。実行されたメソッドの戻り値は、接続を表すConnectionオブジェクトである。

      Statement stmt = conn.createStatement();

では、データベースの接続後に、sql文をデータベースに直接渡すのではなく、sqlコンテナの役割を果たすオブジェクトに渡すためのStatement オブジェクトmtを作成する。

      ResultSet rs = stmt.executeQuery("SELECT name, price FROM fruit WHERE price < 100");

では、Statementオブジェクトstmtに対してSQL文("SELECT name, price FROM fruit WHERE price < 100")を渡し、SQL文を実行する。 SQL文を渡し実行するためのメソッドは、executeQuery()を利用する。得られるResultSetオブジェクトrsには、executeQuery()メソッドにより問い合わせをおこなった結果が格納される。

      while( rs.next() ) {
	String name = rs.getString("name");
　　　　int price = rs.getInt("price");
	System.out.println(name + " : " + price);
      }

では、ResultSetオブジェクトrsから結果を取得して表示している。 getString()メソッドは、RuseltSetから指定されたフィールド名（列）の値をStringとして取得する。getInt()メソッドは、RuseltSetから指定されたフィールド名（列）の値をIntとして取得する。

      rs.close();
      stmt.close();
      conn.close();

では、ResultSetオブジェクト、Statementオブジェクト、Connection オブジェクトの切断をおこなう。

2. 検索条件の指定　(2)

次のプログラム JdbcTest8.java は、

java JdbcTest8  ↓

により、複数の検索条件が与えられ、データの検索をおこなう。

import java.sql.*;

public class JdbcTest8 {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost/test";	
			String user = "root";
			String password = "tuis2019system";

			Connection conn = DriverManager.getConnection(url, user, password);
			
			Statement stmt = conn.createStatement();
			ResultSet rs = 　stmt.executeQuery("SELECT name, price FROM fruit WHERE price > 100 AND price < 200");
			
			while ( rs.next() ) {
				String name = rs.getString("name");
				int price = rs.getInt("price");
				System.out.println(name + " : " + price);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch ( SQLException e ) {
			e.printStackTrace();
		} catch ( Exception e ) {
			e.printStackTrace();
		}
	}
}

プログラム JdbcTest8.java の説明：

JdbcTest8.javaは、 localhostにあるデータベースサーバと接続し、プログラムを実行する。ユーザ名は"root"、パスワードは"tuis2019system" である。

まず、

import java.sql.*;

では、DBC2.0 Core APIであるjava.sqlパッケージがインポートされる。

 Class.forName( "com.mysql.jdbc.Driver" );

    String url = "jdbc:mysql://localhost/test";

    String user           = "root";
    String password       = "tuis2019system";

では、データベースの接続に用いるユーザ名を指定しており、ここでは匿名ユーザをあらわしている。その際のパスワードは設定しないことをあらわす。

conn = DriverManager.getConnection( url, user, password );

      Statement stmt = conn.createStatement();

では、データベースの接続後に、sql文をデータベースに直接渡すのではなく、sqlコンテナの役割を果たすオブジェクトに渡すためのStatement オブジェクトstmtを作成する。

      ResultSet rs = stmt.executeQuery("SELECT name, price FROM fruit WHERE price > 100 AND price < 200");

では、Statementオブジェクトstmtに対してSQL文("SELECT name, price FROM fruit WHERE price > 100 AND price <200")を渡し、SQL文を実行する。 SQL文を渡し実行するためのメソッドは、executeQuery()を利用する。

ResultSetオブジェクトrsには、executeQuery()メソッドにより問い合わせをおこなった結果が格納される。

      while( rs.next() ) {
	String name = rs.getString("name");
　　　　int price = rs.getInt("price");
	System.out.println(name + " : " + price);
      }

では、ResultSetオブジェクトから結果を取得して表示している。 getString()メソッドは、RuseltSetから指定されたフィールド名（列）の値をStringとして取得する。getInt()メソッドは、RuseltSetから指定されたフィールド名（列）の値をIntとして取得する。

      rs.close();
      stmt.close();
      conn.close();

では、ResultSetオブジェクト、Statementオブジェクト、Connection オブジェクトの切断をおこなう。

3. 検索結果のソート

次のプログラム JdbcTest9.java は、

java JdbcTest9  ↓

により、データベースから受け取る検索結果を、特定の基準(昇順）に基づいて並べ替えて取得する。

import java.sql.*;

public class JdbcTest9 {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost/test";	
			String user = "root";
			String password = "tuis2019system";

			Connection conn = DriverManager.getConnection(url, user, password);
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT name, price FROM fruit ORDER BY price");
			
			while ( rs.next() ) {
				String name = rs.getString("name");
				int price = rs.getInt("price");
				System.out.println(name + " : " + price);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch ( SQLException e ) {
			e.printStackTrace();
		} catch ( Exception e ) {
			e.printStackTrace();
		}
	}
}

プログラム JdbcTest9.java の説明：

JdbcTest9.javaは、 localhostにあるデータベースサーバと接続し、プログラムを実行する。ユーザ名は"root"、パスワードは "tuis2019system"をあらわす。

まず、

import java.sql.*;

では、DBC2.0 Core APIであるjava.sqlパッケージがインポートされる。

 Class.forName( "com.mysql.jdbc.Driver" );

    String url = "jdbc:mysql://localhost/test";

    String user           = "root";
    String password       = "tuis2019system";

では、データベースの接続に用いるユーザ名"root"を指定しており、その際のパスワードは"tuis2019"を指定する。

conn = DriverManager.getConnection( url, user, password );

      Statement stmt = conn.createStatement();

      ResultSet rs = stmt.executeQuery("SELECT name, price FROM fruit ORDER BY price");

では、Statementオブジェクトstmtに対してSQL文("SELECT name, price FROM fruit ORDER BY price")を渡し、SQL文を実行する。 SQL文を渡し実行するためのメソッドは、executeQuery()を利用する。

ResultSetオブジェクトrsには、executeQuery()メソッドにより問い合わせをおこなった結果が格納される。

      while( rs.next() ) {
	String name = rs.getString("name");
　　　　int price = rs.getInt("price");
	System.out.println(name + " : " + price);
      }

      rs.close();
      stmt.close();
      conn.close();

では、ResultSetオブジェクト、Statementオブジェクト、Connection オブジェクトの切断をおこなう。

4. 行のグループ化

次のプログラム JdbcTest10.java は、

java JdbcTest10  ↓

により、複数の行を列の値を基準にしてグループ化して取得する。

import java.sql.*;

public class JdbcTest10 {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost/test";	
			String user = "root";
			String password = "tuis2019system";

			Connection conn = DriverManager.getConnection(url, user, password);
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT color, COUNT(*) AS count FROM color GROUP BY color");
			
			while ( rs.next() ) {
				String color = rs.getString("color");
				int count = rs.getInt("count");
				System.out.println(color + " : " + count);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch ( SQLException e ) {
			e.printStackTrace();
		} catch ( Exception e ) {
			e.printStackTrace();
		}
	}
}

ここで対象とするcolorテーブルの構造と値は以下に示される。

colorテーブルの構造

列名データ型 NOT NULL or not

name VARCHAR(10) NOT NULL

color VARCHAR(10)

列名	データ型	NOT NULL or not
name	VARCHAR(10)	NOT NULL
color	VARCHAR(10)

colorテーブル

name color

apple red

orange orange

grape purple

banana yellow

meron green

tomato red

lemon yellow

carrot orange

name	color
apple	red
orange	orange
grape	purple
banana	yellow
meron	green
tomato	red
lemon	yellow
carrot	orange

プログラム JdbcTest10.java の説明：

JdbcTest10.javaは、 localhostにあるデータベースサーバと接続し、プログラムを実行する。ユーザ名は"root"、パスワードは"tuis2019system"を指定する。

まず、

import java.sql.*;

では、DBC2.0 Core APIであるjava.sqlパッケージがインポートされる。

 Class.forName( "com.mysql.jdbc.Driver" );

    String url = "jdbc:mysql://localhost/test";

    String user           = "root";
    String password       = "tuis2019system";

では、データベースの接続に用いるユーザ名とパスワードを指定している。

conn = DriverManager.getConnection( url, user, password );

      Statement stmt = conn.createStatement();

      ResultSet rs = stmt.executeQuery("SELECT color, COUNT(*) AS count GROUP BY color");

では、Statementオブジェクトstmtに対してSQL文("SELECT color, COUNT(*) AS count GROUP BY color")を渡し、SQL文を実行する。 SQL文を渡し実行するためのメソッドは、executeQuery()を利用する。取得したResultSetオブジェクトrsには、executeQuery()メソッドにより問い合わせをおこなった結果が格納される。

      while( rs.next() ) {
	String name = rs.getString("color");
　　　　int count = rs.getInt("count");
	System.out.println(color + " : " + count);
      }

      rs.close();
      stmt.close();
      conn.close();

では、ResultSetオブジェクト、Statementオブジェクト、Connection オブジェクトの切断をおこなう。

5. テーブルの結合（内部結合)

次のプログラム JdbcTest11.java は、

java JdbcTest11  ↓

により、SELECT文のWhere句に結合条件を与えることで、複数のテーブルから列の値を取得する。

import java.sql.*;

public class JdbcTest11 {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost/test";	

			String user = "root";
			String password = "tuis2019system";

			Connection conn = DriverManager.getConnection(url, user, password);
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT fruit.name, fruit.price, color.color FROM fruit, color WHERE fruit.name = color.name");
			
			while ( rs.next() ) {
　　			　　　　String name = rs.getString("name");
				int price = rs.getInt("price");
				String color = rs.getString("color");
				System.out.println(name + " : " + price  + " : " + color);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch ( SQLException e ) {
			e.printStackTrace();
		} catch ( Exception e ) {
			e.printStackTrace();
		}
	}
}

プログラム JdbcTest11.java の説明：

JdbcTest11.javaは、 localhostにあるデータベースサーバと接続し、プログラムを実行する。

まず、

import java.sql.*;

では、DBC2.0 Core APIであるjava.sqlパッケージがインポートされる。

 Class.forName( "com.mysql.jdbc.Driver" );

    String url = "jdbc:mysql://localhost/test";

    String user           = "root";
    String password       = "tuis2019system";

では、データベースの接続に用いるユーザ名とパスワードを指定している。

conn = DriverManager.getConnection( url, user, password );

      Statement stmt = conn.createStatement();

      ResultSet rs = stmt.executeQuery("SELECT fruit.name, fruit.price, color.color FROM fruit, color WHERE fruit.name = color.name");

では、Statementオブジェクトstmtに対してSQL文("SELECT fruit.name, fruit.price, color.color FROM fruit.name = color.name")を渡し、SQL文を実行する。SQL文を渡し実行するためのメソッドは、 executeQuery()を利用する。取得したResultSetオブジェクトrsには、 executeQuery()メソッドにより問い合わせをおこなった結果が格納される。

      while( rs.next() ) {
　　　　String name = rs.getString("name");
　　　　int price = rs.getInt("price");
	String color = rs.getString("color");
	System.out.println(name + " : " + price + " : " + color);
      }

      rs.close();
      stmt.close();
      conn.close();

では、ResultSetオブジェクト、Statementオブジェクト、Connection オブジェクトの切断をおこなう。

6. テーブルの内部（テーブルの別名の使用）

次のプログラム JdbcTest12.java は、

java JdbcTest12  ↓

により、テーブル名に別名を与え、それを使ってデータを検索する(内部結合する）。

import java.sql.*;

public class JdbcTest12 {
	public static void main(String[] args) {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			String url = "jdbc:mysql://localhost/test";	

			String user = "root";
			String password = "tuis2019system";

			Connection conn = DriverManager.getConnection(url, user, password);
			
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT f.name, f.price, c.color FROM fruit f, color c WHERE f.name = c.name");
			
			while ( rs.next() ) {
				String name = rs.getString("name");
				int price = rs.getInt("price");
				String color = rs.getString("color");
				System.out.println(name + " : " 
　　　　　　　　　　　　　　　　　　　　　　　　　+ price + " : " + color);
			}
			
			rs.close();
			stmt.close();
			conn.close();
		} catch ( SQLException e ) {
			e.printStackTrace();
		} catch ( Exception e ) {
			e.printStackTrace();
		}
	}
}

プログラム JdbcTest12.java の説明：

JdbcTest12.javaは、 localhostにあるデータベースサーバと接続し、プログラムを実行する。ユーザ名は"root"、パスワードは"tuis2019system" を指定する。

まず、

import java.sql.*;

では、DBC2.0 Core APIであるjava.sqlパッケージがインポートされる。

 Class.forName( "com.mysql.jdbc.Driver" );

    String url = "jdbc:mysql://localhost/test";

    String user           = "root";
    String password       = "tuis2019system";

では、データベースの接続に用いるユーザ名とパスワードを指定している。

conn = DriverManager.getConnection( url, user, password );

      Statement stmt = conn.createStatement();

では、データベースの接続後に、sql文をデータベースに直接渡すのではなく、sqlコンテナの役割を果たすオブジェクトに渡すためのStatement オブジェクトを作成する。

      ResultSet rs = stmt.executeQuery("SELECT f.name, f.price, c.color FROM fruit f, color c WHERE f.name = c.name");

では、Statementオブジェクトstmtに対してSQL文("SELECT f.name, f.price, c.color FROM fruit f, color c WHERE f.name = c.name")を渡し、SQL文を実行する。SQL文を渡し実行するためのメソッドは、executeQuery()を利用する。取得するResultSetオブジェクトrsには、executeQuery()メソッドにより問い合わせをおこなった結果が格納される。

      while( rs.next() ) {
	String name = rs.getString("name");
	int price = rs.getInt("price");
	String color = rs.getString("color");
	System.out.println(name + " : " + price + " : " + color);
      }

      rs.close();
      stmt.close();
      conn.close();

では、ResultSetオブジェクト、Statementオブジェクト、Connection オブジェクトの切断をおこなう。

7. 演習問題

問題-1

JdbcTest7.javaを実行して、その結果を表示せよ。

問題-2

JdbcTest8.javaを実行して、その結果を表示せよ。

問題-3

JdbcTest9.javaを実行して、その結果を表示せよ。

問題-4

MySQLコマンドを起動し、SQL文を与えることでテーブル colorとfruitを作成せよ。使用したSQLコマンドも示せ。

問題-5

接続するテーブルをcolorとして、プログラムJdbcTest10.javaを実行し、その結果を示せ。

問題-6

プログラムJdbcTest11.javaを作成・実行し、その結果を示せ。

問題-7

プログラムJdbcTest12.javaを作成・実行し、その結果を示せ。

問題-8

接続するテーブルをfruit1として、問題-1と同様な動作をするプログラムJdbcTest71.java を作成・実行し、結果を示せ。

問題-9

接続するテーブルをfruit1として、問題-2と同様な動作をするプログラムJdbcTest81.java を作成・実行し、結果を示せ。

問題-10

接続するテーブルをfruit1として、問題-3と同様な動作をするプログラムJdbcTest91.java を作成・実行し、結果を示せ。

システムプログラミング演習

Yasuo NAGAI

データベースとの連携 (Java アプリケーション)

1. 検索条件の指定 (1)

2. 検索条件の指定 (2)

3. 検索結果のソート

4. 行のグループ化

colorテーブルの構造

colorテーブル

5. テーブルの結合 （内部結合)

6. テーブルの内部（テーブルの別名の使用）

7. 演習問題

1. 検索条件の指定　(1)

2. 検索条件の指定　(2)

5. テーブルの結合（内部結合)