context.xml 추가
<Resource
name="jdbc/orcl"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
loginTimeout="10"
maxWait="5000"
maxActive="50"
username="데이터베이스아이디"
password="데이터에비스비밀번호"
testOnBrrow="true"
url="jdbc:oracle:thin:@데이터베이스주소:포트번호:서비스이름"
/>
MemberDTO.java
package bean;import java.sql.Timestamp;
public class MemberDTO {
private String id;
private String pw;
private String name;
private String month;
private String day;
private String sex;
private String tel;
private String email;
private Timestamp reg_date;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPw() {
return pw;
}
public void setPw(String pw) {
this.pw = pw;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getMonth() {
return month;
}
public void setMonth(String month) {
this.month = month;
}
public String getDay() {
return day;
}
public void setDay(String day) {
this.day = day;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Timestamp getReg_date() {
return reg_date;
}
public void setReg_date(Timestamp reg_date) {
this.reg_date = reg_date;
}
}
MemberDAO.java
package bean;import java.sql.*;
import java.util.ArrayList;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class MemberDAO {
private Connection conn = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;
private static MemberDAO dao = new MemberDAO();
public static MemberDAO getInstance(){
return dao;
}
private MemberDAO(){
}
private Connection getOracle() throws Exception{
Context ctx = new InitialContext();
Context env = (Context)ctx.lookup("java:comp/env");
DataSource ds = (DataSource)env.lookup("jdbc/orcl");
return ds.getConnection();
}
//등록되어 있는 아이디인지 확인
public boolean confirmId(String id) {
boolean result = false;
try {
conn = getOracle();
pstmt = conn.prepareStatement("select id from members where id = ?");
pstmt.setString(1, id);
rs= pstmt.executeQuery();
if(rs.next())
result = true;
} catch(Exception ex) {
ex.printStackTrace();
} finally {
if (rs != null) try { rs.close(); } catch(SQLException ex) {}
if (pstmt != null) try { pstmt.close(); } catch(SQLException ex) {}
if (conn != null) try { conn.close(); } catch(SQLException ex) {}
}
return result;
}
//로그인 인증시 입력한 ID와 PW가 올바른지 검사한다.
public boolean userCheck(String id, String pw) {
String dbpw="";
boolean result = false;
try{
conn = getOracle();
pstmt = conn.prepareStatement("select pw from members where id=?");
pstmt.setString(1, id);
rs= pstmt.executeQuery();
if(rs.next()){
dbpw = rs.getString("pw");
if(dbpw.equals(pw))
result = true;
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{rs.close();}catch(SQLException e){}
try{pstmt.close();}catch(SQLException e){}
try{conn.close();}catch(SQLException e){}
}
return result;
}
//member정보를 수정하기 위해 1명의 회원을 불러온다.
public MemberDTO getMember(String id){
MemberDTO member=null;
try{
conn = getOracle();
pstmt = conn.prepareStatement("select * from members where id = ?");
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if (rs.next()) {
member = new MemberDTO();
member.setId(rs.getString("id"));
member.setPw(rs.getString("pw"));
member.setName(rs.getString("name"));
member.setMonth(rs.getString("month"));
member.setDay(rs.getString("day"));
member.setSex(rs.getString("sex"));
member.setTel(rs.getString("tel"));
member.setEmail(rs.getString("email"));
member.setReg_date(rs.getTimestamp("reg_date"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{rs.close();}catch(SQLException e){}
try{pstmt.close();}catch(SQLException e){}
try{conn.close();}catch(SQLException e){}
}
return member;
}
public void updateMember(MemberDTO dto) {
try{
conn = getOracle();
pstmt = conn.prepareStatement("update members set pw=?,tel=?,email=? where id=?");
pstmt.setString(1, dto.getPw());
pstmt.setString(2, dto.getTel());
pstmt.setString(3, dto.getEmail());
pstmt.setString(4, dto.getId());
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
try{rs.close();}catch(SQLException e){}
try{pstmt.close();}catch(SQLException e){}
try{conn.close();}catch(SQLException e){}
}
}
//비번맞으면 true, 틀리면 false
public boolean deleteMember(String id, String pw) {
boolean result = false;
try{
String dbpw="";
conn = getOracle();
pstmt = conn.prepareStatement("select pw from members where id=?");
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next()){
dbpw= rs.getString("pw");
if(dbpw.equals(pw)){
pstmt = conn.prepareStatement("delete from members where id=?");
pstmt.setString(1, id);
pstmt.executeUpdate();
result = true; //회원탈퇴 성공
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{rs.close();}catch(SQLException e){}
try{pstmt.close();}catch(SQLException e){}
try{conn.close();}catch(SQLException e){}
}
return result;
}
//select
public ArrayList<MemberDTO> select(){
ArrayList<MemberDTO> list = new ArrayList<MemberDTO>();
try{
conn = getOracle();
pstmt = conn.prepareStatement("select * from members where name=? and email=?");
pstmt.setString(1, "name");
pstmt.setString(2, "email");
rs = pstmt.executeQuery();
while(rs.next()){
MemberDTO dto = new MemberDTO();
dto.setId(rs.getString("id"));
dto.setPw(rs.getString("pw"));
dto.setName(rs.getString("name"));
dto.setMonth(rs.getString("month"));
dto.setDay(rs.getString("day"));
dto.setSex(rs.getString("sex"));
dto.setTel(rs.getString("tel"));
dto.setEmail(rs.getString("email"));
dto.setReg_date(rs.getTimestamp("reg_date"));
list.add(dto);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{rs.close();}catch(SQLException e){}
try{pstmt.close();}catch(SQLException e){}
try{conn.close();}catch(SQLException e){}
}
return list;
}
//insert
public void insert(MemberDTO dto){
try{
conn = getOracle();
String sql = "insert into members values(?,?,?,?,?,?,?,?,sysdate)";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getId());
pstmt.setString(2, dto.getPw());
pstmt.setString(3, dto.getName());
pstmt.setString(4, dto.getMonth());
pstmt.setString(5, dto.getDay());
pstmt.setString(6, dto.getSex());
pstmt.setString(7, dto.getTel());
pstmt.setString(8, dto.getEmail());
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
try{pstmt.close();}catch(SQLException e){}
try{conn.close();}catch(SQLException e){}
}
}
//update
public void update(MemberDTO dto){
try{
conn = getOracle();
String sql = "select * from members where id=? and pw=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, dto.getId());
pstmt.setString(2, dto.getPw());
rs = pstmt.executeQuery();
if(rs.next()){
//sql = "update test2 set age=? where id=?";
//pstmt = conn.prepareStatement(sql);
//int age = Integer.parseInt(dto.getAge());
//pstmt.setInt(1, age);
//pstmt.setString(2, dto.getId());
//pstmt.executeUpdate();
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{rs.close();}catch(SQLException e){}
try{pstmt.close();}catch(SQLException e){}
try{conn.close();}catch(SQLException e){}
}
}
}