Class.forName("com.mysql.jdbc.Driver");
Connection conn =null; //연결을 맺어낼 객체
PreparedStatement ps = null; //명령을 선언할 객체
ResultSet rs = null; //결과값을 담아낼 객체
executeQuery
select 명령문에서 사용
- 반환값 : ResultSet 클래스의 인스턴스로 반환
executeUpdate
insert, update, delete 명령문에서 사용
- 반환값 : int
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo1 {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/javadb";
// mysql url로 검색하기
// 주의! sql 클래스인 애로 가져와야함
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1. jdbc 드라이버 로드
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("데이터베이스 연결중...");
// 2. 데이터베이스 연결
conn = DriverManager.getConnection(url, "root", "admin1234");
System.out.println("데이터베이스 연결성공...");
String str = "insert into student(name,age,gender,grade,type) values('홍길동',12,0,1,1 )";
// 3. statement 객체 만들기
stmt = conn.createStatement();
// 4. SQL문 처리명령
int result = stmt.executeUpdate(str);
if (result > 0)
System.out.println("데이터 삽입 성공");
else
System.out.println("데이터 삽입 실패");
} catch (Exception e) {
e.printStackTrace();
} finally {
// 0. close문
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
public class DbConnect implements AutoCloseable {
// 드라이버 로드
private final String URL = "jdbc:mysql://localhost:3306/javadb";
private final String ID = "javadb";
private final String PASSWORD = "admin1234";
private Connection conn ;
public Connection getConn() {
return conn ;
}
public DbConnect() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
this.conn = DriverManager.getConnection(URL, ID, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
}
public Statement getStatement() throws Exception {
return conn.createStatement();
}
public PreparedStatement getPrepare(String sql) throws Exception {
return conn.prepareStatement(sql);
}
@Override
public void close() throws Exception {
System.out.println("자동 close 호출");
conn.close();
}
}
@Override: AutoCloseable 구현하고
오버라이드 하면 수거도 깔끔!
예외처리
여기서? vs 아님 데모에서?
1) 1. 드라이버 로드 + 2. 데이터베이스 연결 메소드
1-1) dbco 메소드로 만들기
public Connection getConnection() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(URL, ID, PASSWORD);
return conn;
}
1-2) db 클래스의 생성자에 넣어서 객체로 가져오기
public DbConnect() throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
this.conn = DriverManager.getConnection(URL, ID, PASSWORD);
}
2) 3. statement 객체
2-1) getStatement() 메소드로 만들어버리기
conn = DriverManager.getConnection(url, "root", "admin1234");
stmt = conn.createStatement();
⇩
public Statement getStatement() throws Exception {
return conn.createStatement();
}
2-2) 3. statement 객체 만들기
public PreparedStatement getPrepare(String sql) throws Exception {
return conn.prepareStatement(sql);
}
3) 4. SQL문 처리명령
int result = stmt.executeUpdate(str);
⇩
ResultSet rs = stmt.executeQuery(sql);
4) ? 로 sql 입력 문장 받기
String sql = "update student set name = '동해산', age = 100 where id = 2";
⇩
String sql = " update student set name = ? , age = ? where id = 2";
pstmt.setString(1, "동해산");
pstmt.setInt(2, 50);
pstmt.executeUpdate();
Statement stmt = dbco.getStatement();
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Select_1 {
// 조회 기능
public static void main(String[] args) {
String sql = "select * from student";
try(
// 1. 드라이버 로드 + 2. 데이터베이스 연결 메소드 -> dbco
DbConnect dbco = new DbConnect();
// 3. statement 객체 만들기
Statement stmt = dbco.getStatement();
// 4. SQL문 처리명령
ResultSet rs = stmt.executeQuery(sql);
)
{
// 데이터 읽어오는 부분 ( 다음줄이 있을 때까지)
while (rs.next()) {
System.out.println(
rs.getInt("id") + " " + rs.getString("name")
+ " " + rs.getInt("age") + " " + rs.getInt("gender")
+ " " + rs.getInt("grade") + " " + rs.getInt("type")
);
}
// 0. catch문
} catch (Exception e) {
e.printStackTrace();
}
}
}
1) ResultSet.rs
rs.next();
2) try with resources
3) rs.getint
package day9_jdbc2;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class Insert_2 {
public static void main(String[] args) {
String str = " insert into student(name,age,gender,grade,type)";
str += " values(?,?,?,?,1 ) ";
try (
DbConnect dbc = new DbConnect();
PreparedStatement pstmt = dbc.getPrepare(str);
)
{
pstmt.setString(1, "김밥");
pstmt.setInt(2, 20);
pstmt.setInt(3, 0);
pstmt.setInt(4, 2);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
}
// insert into student(name,age,gender,grade,type) values('홍길동',12,0,1,1 )
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Update_3 {
public static void main(String[] args) {
// 데이터베이스 접속
// 업데이트 구문
// 실행
String sql = " update student";
sql += " set name = ? , age = ? ";
sql += " where id = 2";
try (
DbConnect dbc = new DbConnect();
PreparedStatement pstmt = dbc.getPrepare(sql);
)
{
// 4. SQL문 처리명령
// String sql = "update student set name = '동해산', age = 100 where id = 2";
// sql += " set name = " + "'동해'";
pstmt.setString(1, "백s산"); // @@ 여기 이해안감!!
pstmt.setInt(1, 50);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
}
package day9_jdbc2;
import java.sql.PreparedStatement;
public class Delete_4 {
public static void main(String[] args) {
String sql = " DELETE FROM student WHERE id = ?";
try(
DbConnect dbcon = new DbConnect();
PreparedStatement pstmt = dbcon.getPrepare(sql);
)
{
pstmt.setInt(1, 2);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//DELETE FROM student WHERE id = 2