JavaDB

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();
    }
}