Java データベース

サンプルDB:hanbai.zip

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

SQL基本

参照

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

例：SELECT * FROM shouhin

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

※抽出条件：sid=1 など。LIKE で文字列の部分検索可能（例：snameが「り」で始まる sname 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プログラムからデータベースにアクセスするためのAPI（インターフェース）。
JDBCを使うと、MySQL・Oracle・PostgreSQLなど異なるDBMSでも同じソースコードで操作できる。
ただし、各DBMS専用のJDBCドライバ（接続用のライブラリ）を別途用意する必要がある。

JDBCドライバの準備

Javaプロジェクトの場合

プロジェクトを右クリックし、[ビルドパス]-[外部アーカイブの追加]からドライバファイル（MySQLの場合、mysql-connector-j-X.X.XX.jar）を指定する。

動的Webプロジェクトの場合

Eclipseインストールフォルダ（c:/pleiades/2023-12）内の tomcat/9/lib に入れる。

主なクラス

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

JDBCを使ったDB操作の基本的な流れは以下のとおり。

接続：DriverManager で Connection を取得する
SQL準備：Connection から PreparedStatement を生成し、SQL文を渡す
実行：更新系は executeUpdate()、参照系は executeQuery() を呼ぶ
結果取得（SELECT時）：ResultSet から行・列を取り出す
切断：Connection を close する（try-with-resources で自動化可）

データベースの接続

データベースに接続し、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 stmt = con.prepareStatement(SQL文字列);

なお、PreparedStatementクラスもclose()する必要があるため、tryのあとの()内に記述する。

String sql = "INSERT INTO shouhin (sname,tanka) VALUES('もも',100)";

try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
	PreparedStatement stmt = con.prepareStatement(sql);){

}

SQLの実行

prepareStatement()でSQLを渡しただけでは、まだSQLは実行されていない（あくまで「実行の準備」）。
実際にSQLを実行するには、stmt.executeUpdate()を呼ぶ。このメソッドはINSERT・UPDATE・DELETEなど更新系のSQLに使用する。

// SQLの実行
stmt.executeUpdate();

※executeUpdate()の戻り値は、更新された行数（int型）。

INSERT：追加した件数（1件なら 1）
UPDATE：変更した件数
DELETE：削除した件数

INSERTの全コード

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) {
		String sql = "INSERT INTO shouhin (sname,tanka) VALUES('もも',100)";
		
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
			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() {
		String sql = "INSERT INTO shouhin (sname,tanka) VALUES('もも',100)";

		try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
			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は「行を指すカーソル」を持っており、最初はどの行にも指していない状態になっている。
rs.next()を呼ぶと次の行に進み、行が存在すれば true、存在しなければ false を返す。
そのため、if(rs.next()) で行の存在確認をしてからデータを取り出す。

if(rs.next()){
	// ここでデータを取り出す
}

現在の行の各列を取り出すには、Javaの型に合わせたメソッドを使い、列名を引数に指定する。
主なメソッド：getString()（文字列）、getInt()（整数）など。
→ 型とメソッドの対応は JDBCで使うデータ型を参照。

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() {
	String sql = "SELECT * FROM shouhin";

	try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
		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	getString()
INT/INTEGER	int	getInt()
BIGINT	long	getLong()
TINYINT(0=false,1=true)	boolean	getBoolean()
DOUBLE	double	getDouble()
DATE	Date（java.sql）	getDate()
TIME	Time	getTime()
DATETIME/TIMESTAMP	Timestamp	getTimestamp()

java.sql.Dateクラス

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

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

// 特定の日付
LocalDate ld = LocalDate.of(2026, 5, 1); // 2026年5月1日
Date d = Date.valueOf(ld);

// 今日の日付
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 09: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");

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

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

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

プレースホルダの利用

ユーザー入力などを文字列連結でSQLに組み込むと、SQLインジェクションの危険がある。
SQLインジェクションとは、悪意ある入力によって意図しないSQLが実行され、データの不正取得・改ざん・削除が起こりうる攻撃のこと。
これを防ぐために、値の部分を ?（プレースホルダ）に置き換え、後から安全に値をセットする方法を使う。

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

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

String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?, ?)";

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

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) {
	String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?, ?)";

	try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
		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 static final String SRC = "jdbc:mysql://localhost/hanbai";
	private static final String USER = "java";
	private static final String PASS = "pass";

	public void insert(Shouhin dto) {
		String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?,?)";

		try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
			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) {
		String sql = "UPDATE shouhin SET sname=?,tanka=? WHERE sid=?";
		
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
			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) {
		String sql = "DELETE FROM shouhin WHERE sid=?";

		try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
			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<>();
		String sql = "SELECT * FROM shouhin";

		try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
			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){
		String sql = "SELECT * FROM shouhin WHERE sid = ?";
		
		try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
			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,mid,kosu,hi）にShouhinクラス（sid,sname,tanka）を結合する場合、まず、UriageクラスにShouhinのフィールドとgetter/setterを追加する。

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 mid, int kosu, Date hi) {
		this.uid = uid;
		this.sid = sid;
		this.mid = mid;
		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";

try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
	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) {
	String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?,?)";

	try(Connection con = DriverManager.getConnection(SRC,USER,PASS);
		PreparedStatement stmt = con.prepareStatement(sql);){
		// 通常の追加処理
		
		stmt.setString(1, dto.getSname());
		stmt.setInt(2, dto.getTanka());

		stmt.executeUpdate();

		// ID取得
		sql = "SELECT last_insert_id()";
		try(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;
}