Java データベース

サンプルDB:hanbai.sql

SQL基本
JDBC
更新系SQLの実行
参照系SQL（SELECT）の実行
JDBCで使うデータ型
プレースホルダの利用
DAO、DTOパターン
挿入直後のID取得
結合

SQL基本

参照

全件参照：SELECT 列名 FROM テーブル名

例：SELECT * FROM shouhin

抽出：SELECT 列名 FROM テーブル名 WHERE 抽出条件

※抽出条件：sid=1 など。LIKE で文字列の部分検索可能（例：snameが「り」で始まる rsname LIKE "り%"）

例：SELECT * FROM shouhin WHERE sid=1

整列：SELECT 列名 FROM テーブル名 ORDER BY 整列順

※整列順：列名昇順(ASC) or 降順(DESC)

例：SELECT * FROM shouhin ORDER BY sid DESC

更新系

追加：INSERT INTO テーブル名(列名1,列名2,列名3,,) VALUES(値1,値2,値3・・)

例：INSERT INTO shouhin (sname,tanka) VALUES('もも',100)

変更：UPDATE テーブル名 SET 列名=値,,, WHERE 抽出条件

例：UPDATE shouhin SET sname='かき',tanka=200 WHERE sid=4

削除：DELETE FROM テーブル名 WHERE 抽出条件

例：DELETE FROM shouhin WHERE sid=5

JDBC

JDBC(Java Database Connectivity)はJavaからデータベースを使うための仕組み。異なるDBMSも同じソースコードで扱うことが出来る。ただし、各DBMS用のJDBCドライバを用意する必要がある。

JDBCドライバの準備

ドライバファイルをダウンロードし、展開しておく
プロジェクトを右クリックし、[ビルドパス]-[外部アーカイブの追加]からドライバファイル（MySQLの場合、mysql-connector-j-X.X.XX.jar）を指定する。
動的Webプロジェクトの場合、Eclipseインストールフォルダ内の tomcat/9/lib に入れる。

主なクラス

クラス名	役割
DriverManager	Connectionの取得
Connection	データベースとの接続
PreparedStatement	SQLの実行
ResultSet	SELECTでの結果を格納
SqlException	データベース処理の例外クラス

データベースの接続

データベースに接続し、Connectionクラスのオブジェクトを取得する。

Connection con = DriverManager.getConnection (データソース, ユーザ名, パスワード);

データソースの書き方はデータベースによって異なる。

mysqlのデータソース書式

//同じPC（localhost）のhanbaiデータベースに接続する場合   

"jdbc:mysql://localhost/hanbai"

オブジェクトの生成例

あらかじめ、クラスのフィールド（定数）としてデータソース、ユーザ名、パスワードを指定しておく。

static final String SRC =  "jdbc:mysql://localhost/hanbai";
static final String USER = "java";
static final String PASS = "pass";

メソッド内に以下の接続のコードを書く。

Connection con = DriverManager.getConnection(SRC,USER,PASS);

※java.sql.*をインポートする。
※SQLExceptionの例外処理が必要。以降のDB処理も同様。

closeと例外

接続完了後、SQLを実行するなどの処理を行う。
最後に、con.close()のようにして接続を切断し、処理を終了する。
try with resource文を使うと自動的にcloseされる。

接続の例

public class DbSample {
	static final String SRC =  "jdbc:mysql://localhost/hanbai";
	static final String USER = "java";
	static final String PASS = "pass";

	public static void main(String[] args) {
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){
			// テスト用に現在のデータベースを表示
			System.out.println(con.getCatalog());
			
		} catch (SQLException e) {
			System.out.println("DB接続エラー：" + e.getMessage());
		}
	}
}

更新系SQLの実行

SQL実行の準備

SQLを実行するためには、まずその準備を行うためのPreparedStatementクラスのオブジェクトを取得する。
ConnectionオブジェクトのprepareStatementメソッドで、引数にSQL文字列を指定することで、PreparedStatementクラスのオブジェクトを取得する。

PreparedStatement stmt = con.prepareStatement(SQL文字列);

//例：
String sql = "INSERT INTO shouhin (sname,tanka) VALUES('もも',100)";
PreparedStatement stmt = con.prepareStatement(sql);

SQLの実行

PrepareStatementオブジェクトを得ても、SQL実行の準備をしただけで、SQL自体はまだ実行されていない。
INSERT、DELETE、UPDATEなど更新系のSQLの実行は、PrepareStatementクラスの executeUpdateメソッドを使用する。

//例
stmt.executeUpdate();

※executeUpdateは戻り値として更新があった行数をint型で返す。INSERTが正常に終了した場合、戻り値は１になる。追加できなかった場合、０になる。DELETEは削除した件数、UPDATEは変更した件数になる。

INSERTの全コード

※Connectionは自動close使用

public class DbSample {
	static final String SRC =  "jdbc:mysql://localhost/hanbai";
	static final String USER = "java";
	static final String PASS = "pass";

	public static void main(String[] args) {
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

			String sql = "INSERT INTO shouhin (sname,tanka) VALUES('もも',100)";
			PreparedStatement stmt = con.prepareStatement(sql);

			stmt.executeUpdate();

		}catch (SQLException e) {
			System.out.println("INSERTエラー：" + e.getMessage());
		}
	}
}

INSERTをメソッドに

insert() メソッドを作り、mainから呼び出すようにした例。

public class DbSample {
	static final String SRC =  "jdbc:mysql://localhost/hanbai";
	static final String USER = "java";
	static final String PASS = "pass";

	public static void main(String[] args) {
		insert();
	}

	public static void insert() {
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

			String sql = "INSERT INTO shouhin (sname,tanka) VALUES('もも',100)";
			PreparedStatement stmt = con.prepareStatement(sql);

			stmt.executeUpdate();

		}catch (SQLException e) {
			System.out.println("INSERTエラー：" + e.getMessage());
		}
	}
}

参照系SQL（SELECT）の実行

SELECT文の実行も基本は同じだが、executeUpdate ではなくexecuteQueryを行う。
戻り値はResultSetクラスのオブジェクトであり、これに検索結果が入っている。

ResultSet rs = stmt.executeQuery();

SELECT結果の取り出し

ResultSetクラスのオブジェクトから結果を取り出すことができる。
結果を一件(一行)取り出すにはまずnextメソッドを使用する。これで最初の行を扱うことが出来る。

rs.next();

現在の行の各列を取り出すには、文字列の場合 getStringメソッド、整数の場合、 getIntメソッドを使用し、列名を指定する（他に、getLong、getDouble、getDate、getTimestamp、getBoolean など、取得する型に応じたメソッドがある。LocalDateはgetDate("列名").toLocalDate()）。

String sname = rs.getString("sname");
int tanka = rs.getInt("tanka");

System.out.println(sname + " " + tanka);

さらにnextメソッドを使うと、次の行の結果を取得できる。
次の行が無いときには、nextメソッドの戻り値がfalseになる。

全データの表示

全データを取得する場合 while文でnext メソッドを使い、false を得たところで終了する。

while( rs.next() ) {
	String sname = rs.getString("sname");
	int tanka = rs.getInt("tanka");

	System.out.println(sname + " " + tanka);
}

SELECTの全コード

public static void select() {
	try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

		String sql = "SELECT * FROM shouhin";
		PreparedStatement stmt = con.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			int sid = rs.getInt("sid");
			String sname = rs.getString("sname");
			int tanka = rs.getInt("tanka");

			System.out.println(sid + " " + sname + " " + tanka);
		}

	}catch (SQLException e) {
		System.out.println("SELECTエラー：" + e.getMessage());
	}
}

JDBCで使うデータ型

MySQLのデータ型との対応

MySQL	Java
VARCHAR/CHAR/TEXT	String
INT/INTEGER	int
BIGINT	long
TINYINT(0=false,1=true)	boolean
DOUBLE	double
DATE	Date（java.sql）
TIME	Time
DATETIME/TIMESTAMP	Timestamp

java.sql.Dateクラス

MySQLのDATE型に対応。java.util.Dateとは違い、日付のみを扱う（時間は扱わない）。コンストラクタの引数はエポックタイム（new Date()は出来ない）。

// 文字列から
Date d = Date.valueOf("2020-03-05");

// 特定の日付
Calendar c = Calendar.getInstance();
c.set(2020, 9,1);	// 2020年10月1日
Date d = new Date(c.getTimeInMillis());

// 今日の日付
Date d = new Date(System.currentTimeMillis());

// LocalDateへの変換
LocalDate localDate = d.toLocalDate();

// LocalDateからの変換
Date d = Date.valueOf(localDate);

Timestampクラス

MySQLのDATETIME型に対応。日時（日付と時間）を扱う。コンストラクタの引数はエポックタイム。

// 文字列から
Timestamp ts = Timestamp.valueOf("2022-04-24 9:59:10");

// 現在日時
Timestamp ts = new Timestamp(System.currentTimeMillis());

// LocalDateTimeへの変換
LocalDateTime localDateTime = ts.toLocalDateTime();

// LocalDateTimeからの変換
Timestamp ts = Timestamp.valueOf(localDateTime);

Timeクラス

MySQLのTIME型に対応。時間のみを扱う。コンストラクタの引数はエポックタイム。

// 文字列から
Time t = Time.valueOf("13:59:10");

// 現在日時
Times t = new Time(System.currentTimeMillis());

// LocalTimeへの変換
LocalTime localTime = t.toLocalTime();

// LocalTimeからの変換
Time t = Time.valueOf(localTime);

プレースホルダの利用

単純に文字列を連結し、SQLを発行する形のプログラムはセキュリティ上「SQLインジェクション」の危険がある。
これを避けるにはプレースホルダ（SQL内の場所を確保する記号）の利用を行う。

１．SQL中の埋め込みたい箇所に ? と書く

値の部分をプレースホルダである「 ? 」に書き換える。なお、値が文字列でも ? を ''で囲む必要は無い。

String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?, ?)";
PreparedStatement stmt = con.prepareStatement(sql);

２．埋め込み場所に入れる変数を指定する。

PreparedStatementオブジェクトを作成後、そのメソッドを使い、？に入れるデータを指定する。
String型はsetStringメソッド、int型はsetIntで指定する（他に、setLong、setDouble、setDate、setTimestamp などデータ型に応じたメソッドがある。LocalDateの場合、setDate(Date.valueOf(値) ）。

引数は？の位置と値を指定する。

stmt.setString(1, sname);
stmt.setInt(2, tanka);

オブジェクト.setString(番号, 値);

番号：？の出現位置（左から１、２、３・・）
　例：INSERT INTO shouhin (sname,tanka) VALUES(?, ?)の場合
　　　INSERT INTO shouhin (sname,tanka) VALUES(１番, ２番)　となる

値：埋め込みたいデータ

プレースホルダ INSERTの例

public static void insert(String sname, int tanka) {
	try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

		String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?, ?)";
		PreparedStatement stmt = con.prepareStatement(sql);
		stmt.setString(1, sname);
		stmt.setInt(2, tanka);

		stmt.executeUpdate();

	}catch (SQLException e) {
		System.out.println("INSERTエラー：" + e.getMessage());
	}
}

DAO、DTOパターン

データベースを簡単に使用できるようにクラスにまとめる典型的パターン（デザインパターンの１つ）。
DTO は Data Transfer Object（データ転送用オブジェクト）で１行分のデータを入れる入れ物のクラス。
DAO は Data Access Object でDTOを使ってデータを操作するメソッドを集めたクラス。

プログラムは必ずDAOを介してデータベースを使うようにする。そうすることで、データベースを使うコードをプログラム本体から分離できる。

DTOクラス

データ１件（１行）を表すクラスをDTOクラスという（エンティティとも呼ぶ）。テーブルの各列をフィールドにしてクラス化する。

public class Shouhin {
	// フィールド テーブルの各列
	private int sid;
	private String sname;
	private int tanka;
	
	// コンストラクタ（eclipseで生成： [ソース]-[フィールドを使用してコンストラクタ生成]）
	// getter/setter（eclipseで生成： [ソース]-[getterおよびsetterの生成]）

	// デバッグ用にtoStringもeclipse生成すると便利（[ソース]-[toString生成]）
}

DAOクラス

データベースの操作を実現するクラス。外部とのやりとり（引数や戻り値）に原則としてDTOクラスを使う。
一般に次のようなメソッドを備える。

insert(DTO) ・・・DTOを追加
update(DTO) ・・・DTOで更新
delete(主キー) ・・・主キーの行を削除
find(主キー)・・・主キーを値で検索し、そのDTOを返す（存在しない場合、NULLを返す）
findAll() ・・・全件取得。DTOのListを返す。

public class ShouhinDAO {
	private final String SRC =  "jdbc:mysql://localhost/hanbai";
	private final String USER = "java";
	private final String PASS = "pass";

	public void insert(Shouhin dto) {
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

			String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?,?)";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1, dto.getSname());
			stmt.setInt(2, dto.getTanka());

			stmt.executeUpdate();
		}
		catch (SQLException e) {
			System.out.println("INSERTエラー：" + e.getMessage());
		}
	}

	public void update(Shouhin dto) {
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

			String sql = "UPDATE shouhin SET sname=?,tanka=? WHERE sid=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1,dto.getSname());
			stmt.setInt(2, dto.getTanka());
			stmt.setInt(3, dto.getSid());

			stmt.executeUpdate();
		}
		catch (SQLException e) {
			System.out.println("UPDATE エラー：" + e.getMessage());
		}
	}

	public void delete(int sid) {
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

			String sql = "DELETE FROM shouhin WHERE sid=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, sid);

			stmt.executeUpdate();
		}
		catch (SQLException e) {
			System.out.println("DELETE エラー：" + e.getMessage());
		}
	}

	public List<Shouhin> findAll(){
		List<Shouhin> list = new ArrayList<>();
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

			String sql = "SELECT * FROM shouhin";
			PreparedStatement stmt = con.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				Shouhin dto = new Shouhin(
					rs.getInt("sid"),
					rs.getString("sname"),
					rs.getInt("tanka")
				);

				list.add(dto);
			}
		}
		catch (SQLException e) {
			System.out.println("findAllエラー：" + e.getMessage());
		}
		return list;
	}

	public Shouhin find(int sid){
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){

			String sql = "SELECT * FROM shouhin WHERE sid = ?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setInt(1, sid);
			ResultSet rs = stmt.executeQuery();

			if( rs.next() ){
				Shouhin dto = new Shouhin(
					rs.getInt("sid"),
					rs.getString("sname"),
					rs.getInt("tanka")
				);
				
				return dto;
			}
		}
		catch (SQLException e) {
			System.out.println("findエラー：" + e.getMessage());
		}
		return null;
	}


}

DAOクラスの利用

コントローラなどで利用する

追加

ShouhinDAO dao = new ShouhinDAO();

// 追加時にsidは必要ないので0
Shouhin dto = new Shouhin(0,"ドリアン",800);
dao.insert(dto);

変更

ShouhinDAO dao = new ShouhinDAO();

Shouhin dto = new Shouhin(4,"かき",200);
dao.update(dto);

削除

ShouhinDAO dao = new ShouhinDAO();

// sid が 5 のものを削除
dao.delete(5);

全件検索

ShouhinDAO dao = new ShouhinDAO();
		
List<Shouhin> list = dao.findAll();

１件検索

ShouhinDAO dao = new ShouhinDAO();
		
Shouhin dto = dao.find(1);

結合

結合してデータを取得したい場合、次の方法がある。

結合した全データを格納するDTOを用意する
テーブル別のDTOのままで、別のDTOをフィールドに持つ

別のDTOをフィールドに持つ

Uriageクラス（uid,sid,kosu,hi）にShouhinクラス（sid,sname,tanka）を結合する場合、以下のようなクラスを用意し、DAOでは結合したSELECT文でデータを格納する。。

public class Uriage {
	private int uid;
	private int sid;
	private int mid;
	private int kosu;
	private Date hi;

	private Shouhin shouhin;

	// コンストラクタはそのまま
	public Uriage(int uid, int sid, int kosu, Date hi) {
		this.uid = uid;
		this.sid = sid;
		this.kosu = kosu;
		this.hi = hi;
	}
	
	// 以下getter/setter
	
	// shouhinのgetter/setterも作成
	public Shouhin getShouhin() {
		return shouhin;
	}

	public void setShouhin(Shouhin shouhin) {
		this.shouhin = shouhin;
	}
}

DAOでは、findAllなどUriageを検索するところで結合のSQLを実行し、Uriageインスタンス生成時にShouhinインスタンスも生成し、Uriageにセットする。

	String sql = "SELECT * FROM uriage JOIN shouhin ON uriage.sid = shouhin.sid";
	PreparedStatement stmt = con.prepareStatement(sql);

	ResultSet rs = stmt.executeQuery();

	while(rs.next()) {
		Uriage dto = new Uriage(
			rs.getInt("uid"),
			rs.getInt("sid"),
			rs.getInt("mid"),
			rs.getInt("kosu"),
			rs.getDate("hi")
		);
		
		// Shouhinインスタンス生成
		Shouhin s = new Shouhin(
				rs.getInt("sid"), 
				rs.getString("sname"), 
				rs.getInt("tanka")
		);

		// ShouhinをUriageにセット
		dto.setShouhin(s);

		list.add(dto);
	}

JSPでは、Uriageインスタンスの中のshouhinインスタンスのsnameやtankaを表示する。

		<td>${u.uid}</td>
		<td>${u.sid}</td>
		
		<td>${u.shouhin.sname}</td>
		<td>${u.shouhin.tanka}円</td>

挿入直後のID取得

INSERT直後に auto_increment で自動挿入された主キーは、MySQLの場合、以下のSQLで取得できる。

SELECT last_insert_id()

ただし、コネクションを閉じてしまうと取得できない。よってinsertと連続して取得する。

例：DAOで、insertで挿入したキーを戻り値として返す。

public int insert(Shouhin dto) {
	try(Connection con = DriverManager.getConnection(SRC,USER,PASS)){
		// 通常の追加処理
		String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?,?)";
		PreparedStatement stmt = con.prepareStatement(sql);
		stmt.setString(1, dto.getSname());
		stmt.setInt(2, dto.getTanka());

		stmt.executeUpdate();

		// ID取得
		sql = "SELECT last_insert_id()";
		PreparedStatement stmt2 = con.prepareStatement(sql);
		ResultSet rs = stmt2.executeQuery();

		if( rs.next() ){
			int index = rs.getInt(1);
			return index;
		}

	}
	catch (SQLException e) {
		System.out.println("INSERTエラー：" + e.getMessage());
	}
	return 0;
}