Java データベース

SQL基本

参照：SELECT 列名1,列名2,列名3・・ FROM テーブル名 WHERE 抽出条件　ORDER BY 整列順

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

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

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

更新：UPDATE テーブル名 SET 列名1=値 , 列名2=値 , 列名3=値・・ WHERE 抽出条件

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

JDBC

Java Database Connectivity(JDBC)はJavaからデータベースを使うための仕組み。MySQL、Oracleなど各DBMS用のJDBCドライバを用意する必要がある。

JDBCドライバの準備

ドライバファイルをダウンロードする（MySQL）
プロジェクトの「JREシステムライブラリ」を右クリックし、[ビルドパス]-[ビルドパスの構成]から「クラスパス」を選択し、「外部JRの追加でドライバファイル（MySQLの場合、mysql-connector-java-X.X.XX.jar）を指定する。
Tomcatの場合、Eclipseインストールフォルダ内の tomcat/9/lib に入れる。

データベースの接続

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

○書式
Connectionオブジェクト = DriverManager.getConnection (データソース,ユーザ名,パスワード);

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

mysqlの場合のデータソース書式

同じPC（localhost）のhanbaiデータベースに接続する場合   
jdbc:mysql://localhost/hanbai?useSSL=false

オブジェクトの生成例

Connection con = DriverManager.getConnection("jdbc:mysql://localhost/hanbai?useSSL=false", "java", "pass");

※SqlExceptionの例外処理が必要。以降のDB処理も同様。

SQL発行の準備

PreparedStatementクラス

SQLを発行するにはまずコネクションのprepareStatementメソッドにSQL文字列を引数で渡し、PreparedStatementクラスのオブジェクトを取得する。これによりSQL発行の準備を行う。

○書式
PreparedStatementオブジェクト = コネクション. prepareStatement(SQL文字列);

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

更新系SQLの実行

SQLの実行

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

○書式
戻り値 = オブジェクト.executeUpdate();

戻り値はint型で更新行数が返される。

例：int num = stmt.executeUpdate ();

※INSERTが正常に終了した場合、戻り値は１になる。追加できなかった場合、０になる。DELETEは削除した件数、UPDATEは変更した件数になる。

データベースの切断

処理が終わったら各オブジェクトのclose処理を行い、データベースへの接続を切断する。

例：
stmt.close();
con.close();

サンプル

try{
	Connection con = DriverManager.getConnection("jdbc:mysql://localhost/hanbai?useSSL=false", "java", "pass");

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

	int num = stmt.executeUpdate();

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

SELECTの実行

SELECT時にも基本は同じだが、executeUpdate ではなくexecuteQueryを行う。戻り値はResultSetクラスのオブジェクトとなる。

○書式
戻り値 = オブジェクト. executeQuery ();

例：ResultSet rs = stmt.executeQuery();

SELECT結果の取り出し

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

○書式
オブジェクト.next();

結果の各列を取り出すには、文字列の場合 getStringメソッド、整数の場合、 getIntメソッドを使用し、列名を指定する（他に、getLong、getDouble、getDate、getTimestamp などがある）。例：

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

nextメソッドは繰り返して使うことで、次のデータを取得できる。

全データの表示

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

while (rs.next()) {
	System.out.println( rs.getString("sname") );
	System.out.println( rs.getInt("tanka") );
}

SELECTサンプル

try{
	Connection con = DriverManager.getConnection("jdbc:mysql://localhost/hanbai?useSSL=false", "java", "pass");

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

	while (rs.next()) {
		System.out.println(rs.getString("sname"));
		System.out.println(rs.getInt("tanka"));
	}

	rs.close();
	stmt.close();
	con.close();

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

パラメータの使用

単純に文字列を連結し、SQLを発行する形のプログラムはセキュリティ上「SQLインジェクション」の危険がある。これを避けるには prepareメソッドを使用する際にパラメータ埋め込みを使用する。

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

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

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

String型はsetStringメソッド、int型はsetIntで指定する（他に、setLong、setDouble、setDate、setTimestamp などがある）。

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

○setStringの書式
　オブジェクト.setString(番号,値);
　
　番号：？が出てくる順番（１から）
　値：埋め込みたいデータ

パラメータ INSERTの例

try{
	Connection con = DriverManager.getConnection("jdbc:mysql://localhost/hanbai?useSSL=false", "java", "pass");

	String sname = "にんじん";
	int tanka = 300;

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

	int num = stmt.executeUpdate();

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

パラメータ UPDATEの例

try{
	Connection con = DriverManager.getConnection("jdbc:mysql://localhost/hanbai?useSSL=false", "java", "pass");

	String sname = "にんじん";
	int tanka = 300;
	int sid = 4;

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

	int num = stmt.executeUpdate();

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

パラメータ DELETEの例

try{
	Connection con = DriverManager.getConnection("jdbc:mysql://localhost/hanbai?useSSL=false", "java", "pass");

	int sid = 4;

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

	int num = stmt.executeUpdate();

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

パラメータ SELECTの例

try{
	Connection con = DriverManager.getConnection("jdbc:mysql://localhost/hanbai?useSSL=false", "java", "pass");

	int sid = 4;

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

	rs.next();
	System.out.println( rs.getString("sname") );
	System.out.println( rs.getInt("tanka") );

	rs.close();
	stmt.close();
	con.close();		
}catch (SQLException e) {
	System.out.println("DBエラー：" + e.getMessage());
}

DAO、DTOパターン

データベースを簡単に使用できるようにクラスにまとめる典型的パターン。DTOは１レコード分のデータを入れる入れ物のクラス。DAOはデータベースアクセス用のクラス。

ダウンロード

コネクション用クラス

コネクションを取得するためのクラスを作っておく。このクラスからコネクションを取得し（getConnection）、DAOのコンストラクタに渡すようにする。DAO使用後はcloseする。

public class HanbaiConnection {
	private Connection con=null ;

	public Connection getConnection(){
		try {
			con = DriverManager.getConnection("jdbc:mysql://localhost/hanbai?useSSL=false", "java", "pass");
		} catch (SQLException e) {
			System.out.println("DBコネクションエラー:" + e.getMessage());
		}
		return con;
	}

	public void close() {
		try {
			if( con != null ) {
				con.close();
			}
		}catch (SQLException e) {
			System.out.println("DBクローズエラー");
		}
	}
}

DTOクラス

テーブルの各列を属性にクラス化する。JavaBeansと同じ要領で作れば良い。

public class Shouhin {
	private int sid;
	private String sname;
	private int tanka;
	
	// 後はコンストラクタとgetter/setter
}

DAOクラス

findAll 全件をArrayListで取得
findById IDを渡し、一件検索
insert 追加
update 更新
delete 削除

public class ShouhinDAO {
	private Connection con;

	public ShouhinDAO(Connection con) {
		this.con = con;
	}

	public ArrayList<Shouhin> findAll(){
		ArrayList<Shouhin> list = new ArrayList<Shouhin>();
		try{
			PreparedStatement stmt = con.prepareStatement("SELECT * from shouhin");
			ResultSet rs = stmt.executeQuery();

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

				Shouhin s = new Shouhin(sid,sname,tanka);
				list.add(s);
			}

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

	public Shouhin findById(int sid){
		Shouhin shouhin=null;
		try{
			PreparedStatement stmt = con.prepareStatement("SELECT * from shouhin WHERE sid = ?");
			stmt.setInt(1,sid);
			ResultSet rs = stmt.executeQuery();

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

			shouhin = new Shouhin(sid,sname,tanka);

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

	public void insert(Shouhin shouhin) {
		try{
			String sql = "INSERT INTO SHOUHIN (sname,tanka) VALUES(?,?)";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1,shouhin.getSname());
			stmt.setInt(2,shouhin.getTanka());

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

	public void update(Shouhin shouhin) {
		try{
			String sql = "UPDATE SHOUHIN SET sname=?,tanka=? WHERE sid=?";
			PreparedStatement stmt = con.prepareStatement(sql);
			stmt.setString(1,shouhin.getSname());
			stmt.setInt(2,shouhin.getTanka());
			stmt.setInt(3,shouhin.getSid());

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

	public void delete(int sid) {
		try{
			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());
		}
	}

}

DAOクラスの利用

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

HanbaiConnection con = new HanbaiConnection();
ShouhinDAO dao = new ShouhinDAO(con.getConnection());
		
Shouhin s = dao.findById(1);

System.out.println(s.getSname());

con.close();