Step18. JDBC 기본 문법

1. select문의 경우

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class TestJDBC {

public static void main(String[] args) {

// TODO Auto-generated method stub

try {

//드라이버 로딩

Class.forName("oracle.jdbc.OracleDriver");

//DB접속

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "scott", "tiger");

//SQL문을 실행하기 위해 Statement와 SQL 작성 및 셋팅

Statement stmt = conn.createStatement();

String str = "select * from person";

//실행

ResultSet rs = stmt.executeQuery(str);

while(rs.next()){

System.out.printf("이름 : %s\n나이 : %d\n사는곳 : %s\n",rs.getString(1),rs.getInt(2),rs.getString(3));

}

//닫기

rs.close();

stmt.close();

conn.close();

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

2. insert의 경우

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class TestJDBC1 {

public static void main(String[] args) {

// TODO Auto-generated method stub

try {

//드라이버 로딩

Class.forName("oracle.jdbc.OracleDriver");

//DB접속

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "scott", "tiger");

//SQL문을 실행하기 위해 Statement와 SQL 작성 및 셋팅

Statement stmt = conn.createStatement();

String str = "insert into PERSON values('김오팔',35,'경기')";

//실행

int affect = stmt.executeUpdate(str);

System.out.println(affect + "건 적용 됨");

//닫기

stmt.close();

conn.close();

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

3. update의 경우

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class TestJDBC1 {

public static void main(String[] args) {

// TODO Auto-generated method stub

try {

//드라이버 로딩

Class.forName("oracle.jdbc.OracleDriver");

//DB접속

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "scott", "tiger");

//SQL문을 실행하기 위해 Statement와 SQL 작성 및 셋팅

Statement stmt = conn.createStatement();

String str = "update person set loc='제주' where age >= 30";

//실행

int affect = stmt.executeUpdate(str);

System.out.println(affect + "건 적용 됨");

//닫기

stmt.close();

conn.close();

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

4. delete의 경우

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class TestJDBC1 {

public static void main(String[] args) {

// TODO Auto-generated method stub

try {

//드라이버 로딩

Class.forName("oracle.jdbc.OracleDriver");

//DB접속

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe", "scott", "tiger");

//SQL문을 실행하기 위해 Statement와 SQL 작성 및 셋팅

Statement stmt = conn.createStatement();

String str = "delete from person";

//실행

int affect = stmt.executeUpdate(str);

System.out.println(affect + "건 적용 됨");

//닫기

stmt.close();

conn.close();

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

*executeQuery하고 executeUpdate는 DB에 있는 데이터를 조회하느냐 아니면 데이터를 변경하느냐에 따라서 쓰이는 것이 다르다.

* 데이터 조회 : executeQuery -> 조회한 결과를 가리키는 ResultSet 리턴

* 데이터 변경 : executeUpdate -> 결과로 적용된 레코드 개수 리턴