(1) Statement
stmt = conn.createStatement();
result = stmt.executeQuery|update(완성된 sql문)
(2) PreparedStatement
pstmt = conn.preparedStatement([미]완성된 sql문 )
pstmt.setXXXX(1, 대체할값)
view, controller 테이블은 똑같이 작성함 !
public Member selectByUserId(String userId) {
// select문(단일행) -> ResultSet -> 자바객체(Member)
Member m= null;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
String sql = "SELECT * FROM MEMBER WUERE USER_ID = ?"
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","JDBC","JDBC");
pstmt = conn.prepareStatement(sql); // 미완성된 sql문
pstmt.setString(1, userId); // 완성형태로 만들어줌
rset = pstmt.executeQuery();
if(rset.next()) {
m = new Member(rset.getInt("user_no"),
rset.getString("user_id"),
rset.getString("user_pwd"),
rset.getString("user_name"),
rset.getString("gender"),
rset.getInt("age"),
rset.getString("email"),
rset.getString("phone"),
rset.getString("address"),
rset.getString("hobby"),
rset.getDate("enroll_date")
);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rset.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return m;
}
public ArrayList<Member> selectByUserName(String keyword){
// select문(여러행) => ResultSet 객체 => ArrayList객체에 담기
ArrayList <Member> list = new ArrayList<>();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rset = null;
String sql = "SELECT * FROM MEMBER WHERE USER_NAME LIKE '%'||?||'%'" ;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","JDBC","JDBC");
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, keyword);
rset = pstmt.executeQuery();
while(rset.next()) {
list.add(new Member(rset.getInt("user_no"),
rset.getString("user_id"),
rset.getString("user_pwd"),
rset.getString("user_name"),
rset.getString("gender"),
rset.getInt("age"),
rset.getString("email"),
rset.getString("phone"),
rset.getString("address"),
rset.getString("hobby"),
rset.getDate("enroll_date")));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rset.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
String sql = "SELECT * FROM 테이블명 WHERE 조건식 LIKE '%'||?||'%'");
1) 패턴 자체를 ?로 공간 확보하기
SELECT * FROM 테이블명 WHERE 조건식 LIKE ?
pstmt.setString(1, "%" + keyword + "%"
);
2) 연결연산자로 sql문에 바로 작성하기
SELECT * FROM 테이블명 WHERE 조건식 LIKE '%'||?||'%';