PreparedStatement를 이용한 CRUD Test

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class PreparedStmt {

/**

* PreparedStatement 테스트

*/

Connection con;

static {

try {

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

} catch (ClassNotFoundException cne) {

cne.printStackTrace();

}

}

public void connect() {

try {

con = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

System.out.println("Connection Success!");

} catch (SQLException se) {

se.printStackTrace();

}

}

public void createTable() {

PreparedStatement pstmt = null;

try {

String sql = "create table member (id varchar(10), name varchar(10))";

pstmt = con.prepareStatement(sql);

pstmt.executeUpdate();

System.out.println("create success!");

} catch (SQLException e) {

System.out.println("create fail or aleady defined");

e.printStackTrace();

} finally {

try {

pstmt.close();

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

public void insert() {

PreparedStatement pstmt = null;

try {

String sql = "INSERT INTO member VALUES(?,?)";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, "java");

pstmt.setString(2, "kim");

int count = pstmt.executeUpdate();

if (count > 0) {

System.out.println("insert success!");

} else {

System.out.println("insert fail");

}

} catch (SQLException se) {

System.out.println("insert fail");

se.printStackTrace();

} finally {

try {

pstmt.close();

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

public void select() {

PreparedStatement pstmt = null;

ResultSet rs = null;

try {

String sql = "SELECT * FROM member";

pstmt = con.prepareStatement(sql);

rs = pstmt.executeQuery();

while (rs.next()) {

System.out.println("아이디 : " + rs.getString(1) + ",이름 : "

+ rs.getString("name"));

}

} catch (SQLException se) {

se.printStackTrace();

} finally {

try {

pstmt.close();

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

public void update() {

PreparedStatement pstmt = null;

try {

String sql = "UPDATE member SET id = ? WHERE name = ?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, "trans");

pstmt.setString(2, "kim");

int count = pstmt.executeUpdate();

if (count > 0) {

System.out.println("update success!");

} else {

System.out.println("update fail");

}

} catch (SQLException se) {

System.out.println("update fail");

se.printStackTrace();

} finally {

try {

pstmt.close();

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

public void delete() {

PreparedStatement pstmt = null;

try {

String sql = "DELETE member WHERE id = ?";

pstmt = con.prepareStatement(sql);

pstmt.setString(1, "trans");

int count = pstmt.executeUpdate();

if (count > 0) {

System.out.println("delete success!");

} else {

System.out.println("delete fail");

}

} catch (SQLException se) {

System.out.println("delete fail");

se.printStackTrace();

} finally {

try {

pstmt.close();

con.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}

public static void main(String[] args) {

// TODO Auto-generated method stub

PreparedStmt pt = new PreparedStmt();

pt.connect();

pt.createTable(); // insert

System.out.println("create 수행 후");

pt.connect();

pt.select();

pt.connect();

pt.insert(); // insert

System.out.println("insert 수행 후");

pt.connect();

pt.select();

pt.connect();

pt.update(); // update

System.out.println("update 수행 후");

pt.connect();

pt.select();

pt.connect();

pt.delete(); // delete

System.out.println("delete 수행 후");

pt.connect();

pt.select();

}

}

결과 : 

Connection Success!

create success!

create 수행 후

Connection Success!

Connection Success!

insert success!

insert 수행 후

Connection Success!

아이디 : java,이름 : kim

Connection Success!

update success!

update 수행 후

Connection Success!

아이디 : trans,이름 : kim