DB接続手順
準備
String url = "jdbc:mysql://localhost:3306/example?useSSL=false&serverTimezone=JST";
String user = "root";
String password = "パスワード";
Connection
Connection con = DriverManager.getConnection(url, user, password);
Statement
Statement st = con.createStatement();
PreparedStatement
PreparedStatement ps = con.prepareStatement(sql)
ps.setString(1, parameterあり);
ResultSet（parameterなし）
ResultSet rs = rs.executeQuery(sql);
ResultSet（parameterあり）
ResultSet rs = ps.executeQuery();
出力
while (rs.next()) {
int id = rs.getInt("key1);
String title = rs.getString("key2");
int price = rs.getInt("key3");
System.out.println(key1 + "," + key2 + "," + key3);
}
DAOサンプル
フィールド設定
protected Connection con;
コンストラクタ設定
public SampleDAO(Connection con) {
this.con = con;
}
メソッド設定
全件取得
public List<Sample> findAll() throws SQLException {
String sql = "select key1, key2, key3 from sample order by id";
try (PreparedStatement ps = con.prepareStatement(sql)) {
ResultSet rs = ps.executeQuery();
List<Sample> sampleList = new ArrayList<>();
while (rs.next()) {
Sample sample = new Sample(rs.getInt("key1"), rs.getString("key2"),rs.getInt("key3"));
sampleList.add(sample);
}
return sampleList;
}
}
条件取得
public Sample findByPrimaryKey(int id) throws SQLException {
String sql = "select id, key2, key3 from sample where id = ?";
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, 条件);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
Sample sample = new Sample(rs.getInt("key1"), rs.getString("key2"), rs.getInt("key3"));
return sample;
}
throw new DataNotFoundException();
}
}
追加
public int create(Book book) throws SQLException {
String sql = "insert into book (key1, key2, key3) values (?, ?, ?)";
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, sample.getKey1());
ps.setString(2, sample.getKey2());
ps.setInt(3, sample.getKey3());
return ps.executeUpdate();
}
}
更新
public int update(Sample sample, int id) throws SQLException {
String sql = "update sample set key1=?, key2=?, key3=? where id =?;
try (PreparedStatement ps = con.prepareStatement(sql) {
ps.setInt(1, sample.getKey1());
ps.setString(2, sample.getKey2());
ps.setInt(3, sample.getKey3());
ps.setInt(4, id);
return ps.executeUpdate();
}
}
消去
public int delete(int id) throws SQLException {
String sql = "delete from sample where id = ?";
try (PreparedStatement ps = con.prepareStatement(sql) {
ps.setInt(1, id);
return ps.executeUpdate();
}
}
Local Disk上（pcのディスク）で開発ファイルを保存しデータベースは
開発環境はサーバーで管理。
以上で
