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が正常に終了した場合、戻り値は1になる。追加できなかった場合、0になる。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メソッドを使用する際にパラメータ埋め込みを使用する。
1.SQL中の埋め込みたい箇所に ? と書く
String sql = "INSERT INTO shouhin (sname,tanka) VALUES(?,?)"; PreparedStatement stmt = con.prepareStatement(sql);
2.埋め込み場所に入れる変数を指定する。
String型はsetStringメソッド、int型はsetIntで指定する(他に、setLong、setDouble、setDate、setTimestamp などがある)。
stmt.setString(1,sname); stmt.setString(2,tanka);
○setStringの書式 オブジェクト.setString(番号,値); 番号:?が出てくる順番(1から) 値:埋め込みたいデータ
パラメータ 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は1レコード分のデータを入れる入れ物のクラス。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();