PreparedStatement에서 Statement로 변환
개발환경 : Oracle 11g, CentOS7, Apache-Tomcat 8.5
기존의 Oracle과 Eclipse로 연동하여 만든 게시판을 수정하여 다시 연동해 보겠다.
기존에는 PreparedStatement를 사용했다면, 이번엔 Statement를 이용해 Oracle에 로그인, 회원가입, 게시판 글쓰기, 수정, 삭제를 구현해보도록 하겠다.
먼저 PreparedStatement와 Statement의 차이를 간단히 알아보도록 하자.
백문이 불여일견, 코드 먼저 보고 가자
Statement
String sqlstr = "SELECT name, memo FROM TABLE WHERE num = " + num
Statement stmt = conn.createStatement();
ResultSet rst = stmt.executeQuerey(sqlstr); PreparedStatement
String sqlstr = "SELECT name, memo FROM TABLE WHERE num = ? "
PreparedStatement stmt = conn.prepareStatement(sqlstr);
pstmt.setInt(1, num);
ResultSet rst = pstmt.executeQuerey(); 코딩에 있어 차이점은, 해당 변수를 그대로 String으로 받아와 넣느냐 PreparedStatement를 이용해 ‘?’로 쿼리를 생성한 뒤, pstmt.setInt()와 같은 함수를 이용하여 넣느냐 차이이다.
기능에 있어서 차이는 캐시이다.
Statement를 사용하면 매번 쿼리를 수행할 때마다 쿼리문장분석 -> 컴파일 -> 실행 단계를 거치게 되고, PreparedStatement는 처음 한 번만 세 단계를 거친 후 캐시에 담아 재사용을 한다는 것이다. 만약 동일한 쿼리를 반복적으로 수행한다면 PreparedStatment가 DB에 훨씬 적은 부하를 주며, 성능도 좋다.
그럼 UerDAO 파일부터 수정해보자
주석처리해 둔 부분은 기존의 Prepared Statement를 이용한 부분이다. 구분하여 보도록 하자.
주석으로 처리되어 있는 기존의 로그인 함수를 다음과 같이 변경한다.
public int login(String userID, String userPassword) {
String SQL = "SELECT USERPASSWORD FROM USER_BBS WHERE USERID = '" + userID+"'";
//String SQL = "SELECT USERPASSWORD FROM USER_BBS WHERE USERID = ? ";
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(SQL);
//pstmt = conn.prepareStatement(SQL);
//pstmt.setString(1, userID);
//rs = pstmt.executeQuery();
if(rs.next()) {
if(rs.getString(1).contentEquals(userPassword)) {
return 1;
}
else
return 0;
}
return -1;
} catch (Exception e) {
e.printStackTrace();
}
return -2;
}주의해야 할 점은 string을 그대로 쿼리로 넣기 때문에 해당 변수가 문자열일 경우 앞뒤에 ‘ID’ 이런식으로 작은 따옴표를 넣어준다.
Join 회원가입 함수도 마찬가지로 변경해준다.
public int join(User user) {
String SQL = "INSERT INTO user_bbs VALUES("+ "'"+ user.getUserID() + "'"+","+ "'" + user.getUserPassword() + "'"+","+ "'" + user.getUserName() + "'"
+","+ "'" + user.getUserGender() + "'"+","+ "'" + user.getUserEmail() + "'"+")";
//String SQL = "INSERT INTO user_bbs VALUES( ?, ?, ?, ?, ?)";
try {
stmt = conn.createStatement();
//rs = stmt.executeQuery(SQL);
//pstmt = conn.prepareStatement(SQL);
//pstmt.setString(1, user.getUserID());
//pstmt.setString(2, user.getUserPassword());
//pstmt.setString(3, user.getUserName());
//pstmt.setString(4, user.getUserGender());
//pstmt.setString(5, user.getUserEmail());
System.out.println("executeupdate_before");
//return pstmt.executeUpdate();
return stmt.executeUpdate(SQL);
}catch(Exception e){
System.out.println("exeption");
e.printStackTrace();
}
System.out.println("return: -1");
return -1;
}다음은 게시판 글쓰기, 글 불러오기, 추가, 수정, 삭제 부분을 변경해 보자.
BbsDAO.jsp
package bbs;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
public class BbsDAO {
private Connection conn;
private ResultSet rs;
public BbsDAO() {
try {
String dbURL = "jdbc:oracle:thin:@localhost:1521:xe";
String dbID = "jsw";
String dbPassword = "jsw";
Class.forName("oracle.jdbc.OracleDriver");
conn = DriverManager.getConnection(dbURL,dbID,dbPassword);
}catch (Exception e) {
e.printStackTrace();
}
}
public String getDate() {
String SQL = "SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD-hh24:mi:ss') FROM DUAL";
try {
//PreparedStatement pstmt = conn.prepareStatement(SQL);
Statement stmt = conn.createStatement();
rs = stmt.executeQuery(SQL);
//rs = pstmt.executeQuery();
if(rs.next()) {
return rs.getString(1);
}
} catch (Exception e) {
e.printStackTrace();
}
return "";
}
public int getNext() {
String SQL = "SELECT bbsID FROM BBS ORDER BY bbsID DESC";
try {
//PreparedStatement pstmt = conn.prepareStatement(SQL);
//rs = pstmt.executeQuery();
Statement stmt = conn.createStatement();
rs = stmt.executeQuery(SQL);
if(rs.next()) {
return rs.getInt(1)+1;
}
return 1;
} catch (Exception e) {
e.printStackTrace();
}
return -1;
}
public int write(String bbsTitle, String userID, String bbsContent) {
//String SQL = "INSERT INTO BBS VALUES (?, ?, ?, ?, ?, ?)";
String SQL = "INSERT INTO BBS VALUES("+ getNext() + ","+ "'" + bbsTitle + "'"+","+ "'" + userID + "'"
+","+ "'" + getDate() + "'"+","+ "'" + bbsContent + "'"+"," + 1 +")";;
try {
//PreparedStatement pstmt = conn.prepareStatement(SQL);
//pstmt.setInt(1, getNext());
//pstmt.setString(2, bbsTitle);
//pstmt.setString(3, userID);
//pstmt.setString(4, getDate());
//pstmt.setString(5, bbsContent);
//pstmt.setInt(6, 1);
//return pstmt.executeUpdate();
Statement stmt = conn.createStatement();
return stmt.executeUpdate(SQL);
} catch (Exception e) {
e.printStackTrace();
}
return -1;
}
public ArrayList<Bbs> getList(int pageNumber){
//String SQL = "select * from ( select row_number() over (order by bbsDate desc) NUM, A.* from bbs A where bbsavailable=1 order by bbsDate desc) where NUM between ? and ?";
int no2=0;
if(getNext()>pageNumber*10) {
no2 = pageNumber*10;
} else {
no2 = getNext();
}
String SQL = "select * from ( select row_number() over (order by bbsDate desc) NUM, A.* from bbs A where bbsavailable=1 order by bbsDate desc) "
+ "where NUM between "
+ (pageNumber -1)*10+1
+ " and "
+ no2;
ArrayList<Bbs> list = new ArrayList<Bbs>();
try {
//PreparedStatement pstmt = conn.prepareStatement(SQL);
//pstmt.setInt(1, (pageNumber -1)*10+1);
// if(getNext()>pageNumber*10) {
// pstmt.setInt(2, pageNumber*10);
// } else {
// pstmt.setInt(2, getNext());
// }
// rs = pstmt.executeQuery();
System.out.println("sql statement : "+SQL);
Statement stmt = conn.createStatement();
rs = stmt.executeQuery(SQL);
while(rs.next()) {
Bbs bbs = new Bbs();
bbs.setBbsID(rs.getInt(2));
bbs.setBbsTitle(rs.getString(3));
bbs.setUserID(rs.getString(4));
bbs.setBbsDate(rs.getString(5));
bbs.setBbsContent(rs.getString(6));
bbs.setBbsAvailable(rs.getInt(7));
list.add(bbs);
}
}catch(Exception e) {
System.out.println("Exception");
e.printStackTrace();
}
System.out.println(" resultset_return list");
return list;
}
public boolean nextPage(int pageNumber) {
try {
if(getNext()>(pageNumber-1)*10) {
System.out.println("nextpage");
return true;
} else {
return false;
}
}catch(Exception e) {
e.printStackTrace();
}
return false;
}
public Bbs getBbs(int bbsID) {
//String SQL = "SELECT * FROM bbs WHERE bbsID = ?";
String SQL = "SELECT * FROM bbs WHERE bbsID = " + bbsID;
try {
//PreparedStatement pstmt = conn.prepareStatement(SQL);
//pstmt.setInt(1, bbsID);
///rs = pstmt.executeQuery();
Statement stmt = conn.createStatement();
rs = stmt.executeQuery(SQL);
if(rs.next()) {
Bbs bbs = new Bbs();
bbs.setBbsID(rs.getInt(1));
bbs.setBbsTitle(rs.getString(2));
bbs.setUserID(rs.getString(3));
bbs.setBbsDate(rs.getString(4));
bbs.setBbsContent(rs.getString(5));
bbs.setBbsAvailable(rs.getInt(6));
return bbs;
}
}catch(Exception e) {
e.printStackTrace();
}
return null;
}
public int update(int bbsID, String bbsTitle, String bbsContent) {
Bbs bbs = getBbs(bbsID);
//String SQL = "UPDATE BBS SET bbsTitle = ?, bbsContent = ? WHERE bbsID = ?";
String SQL = "UPDATE BBS SET bbsTitle = "
+"'"+ bbsTitle + "'" + ", "+ "bbsContent = "+"'" + bbsContent + "'" + " WHERE bbsID = "+ bbsID;
try {
//PreparedStatement pstmt = conn.prepareStatement(SQL);
//pstmt.setString(1, bbsTitle);
//pstmt.setString(2, bbsContent);
//pstmt.setInt(3, bbsID);
//return pstmt.executeUpdate();
System.out.println("this is query for update : "+SQL);
Statement stmt = conn.createStatement();
return stmt.executeUpdate(SQL);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("this is return-1 for update : "+SQL);
return -1;
}
public int delete(int bbsID) {
//String SQL = "UPDATE BBS SET bbsAvailable = 0 WHERE bbsID = ?";
String SQL = "UPDATE BBS SET bbsAvailable = 0 WHERE bbsID = "+ bbsID;
System.out.println("여기 동작하나"+bbsID);
try {
//PreparedStatement pstmt = conn.prepareStatement(SQL);
//pstmt.setInt(1, bbsID);
//return pstmt.executeUpdate();
Statement stmt = conn.createStatement();
return stmt.executeUpdate(SQL);
} catch (Exception e) {
e.printStackTrace();
}
return -1;
}
}
잘 작동한다. 끝
https://github.com/jsw4215