순서
memberdto.java
package data.dto;
import java.sql.Timestamp;
public class MemberDto {
private String num;
private String name;
private String id;
private String pass;
private String hp;
private String addr;
private String email;
private Timestamp gaipday;
public String getNum() {
return num;
}
public void setNum(String num) {
this.num = num;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPass() {
return pass;
}
public void setPass(String pass) {
this.pass = pass;
}
public String getHp() {
return hp;
}
public void setHp(String hp) {
this.hp = hp;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Timestamp getGaipday() {
return gaipday;
}
public void setGaipday(Timestamp gaipday) {
this.gaipday = gaipday;
}
}
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
memberdao.java
package data.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Vector;
import data.dto.MemberDto;
import mysql.db.DbConnect;
public class MemberDao {
DbConnect db=new DbConnect();
//아이디 체크..boolean..파라메타로 string id
public boolean isIdCheck(String id) {
boolean isid=false;
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null; //있는지 없는지 확인하는 거니까 필요
String sql="select * from member where id=?";
try {
pstmt=conn.prepareStatement(sql);
//바인딩
pstmt.setString(1, id);
rs=pstmt.executeQuery();
//해당 아이디가 존재할 경우 true
if(rs.next())
isid=true;
} catch (SQLException e) {
} finally {
db.dbClose(rs, pstmt, conn);
}
return isid;
}
//아이디에 따른 name..name이니까 파라메타로 string id
public String getName(String id) {
String name="";
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
String sql="select * from member where id=?";
try {
pstmt=conn.prepareStatement(sql);
//바인딩
pstmt.setString(1, id);
//실행
rs=pstmt.executeQuery();
if(rs.next())
name=rs.getString("name");
} catch (SQLException e) {
} finally {
db.dbClose(rs, pstmt, conn);
}
return name;
}
//insert
public void insertMember(MemberDto dto) {
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
String sql="insert into member (name,id,pass,hp,addr,email,gaipday) values(?,?,?,?,?,?,now())";
try {
pstmt=conn.prepareStatement(sql);
//?바인딩
pstmt.setString(1, dto.getName());
pstmt.setString(2, dto.getId());
pstmt.setString(3, dto.getPass());
pstmt.setString(4, dto.getHp());
pstmt.setString(5, dto.getAddr());
pstmt.setString(6, dto.getEmail());
//실행
pstmt.execute();
} catch (SQLException e) {
} finally {
db.dbClose(pstmt, conn);
}
}
//전체리스트
public List<MemberDto> getAllMember(){
List<MemberDto>list=new Vector<MemberDto>();
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
String sql="select * from member order by num asc";
try {
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()) {
MemberDto dto=new MemberDto();
dto.setNum(rs.getString("num"));
dto.setName(rs.getString("name"));
dto.setId(rs.getString("id"));
dto.setPass(rs.getString("pass"));
dto.setHp(rs.getString("hp"));
dto.setAddr(rs.getString("addr"));
dto.setEmail(rs.getString("email"));
dto.setGaipday(rs.getTimestamp("gaipday"));
//리스트에 추가
list.add(dto);
}
} catch (SQLException e) {
} finally {
db.dbClose(rs, pstmt, conn);
}
return list;
}
//비밀번호 체크
public boolean isPassEqual(String num,String pass) {
boolean b=false;
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
//조건 2개일 때는 and
String sql="select * from member where num=? and pass=?";
try {
pstmt=conn.prepareStatement(sql);
//?바인딩
pstmt.setString(1, num);
pstmt.setString(2, pass);
//실행
rs=pstmt.executeQuery();
//하나 찾는 거니까 if
if(rs.next()) {
b=true;
}
} catch (SQLException e) {
} finally {
db.dbClose(rs, pstmt, conn);
}
return b;
}
//삭제
public void deleteMember(String num) {
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
String sql="delete from member where num=?";
try {
pstmt=conn.prepareStatement(sql);
//?바인딩
pstmt.setString(1, num);
//실행
pstmt.execute();
} catch (SQLException e) {
} finally {
db.dbClose(pstmt, conn);
}
}
//num에 해당하는 회원 dto 반환
public MemberDto getMember(String num) {
MemberDto dto=new MemberDto();
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
ResultSet rs=null;
String sql="select * from member where num=?";
try {
pstmt=conn.prepareStatement(sql);
//?바인딩
pstmt.setString(1, num);
rs=pstmt.executeQuery();
if(rs.next()) {
dto.setNum(rs.getString("num"));
dto.setName(rs.getString("name"));
dto.setId(rs.getString("id"));
dto.setHp(rs.getString("hp"));
dto.setAddr(rs.getString("addr"));
dto.setEmail(rs.getString("email"));
dto.setGaipday(rs.getTimestamp("gaipday"));
}
} catch (SQLException e) {
} finally {
db.dbClose(rs, pstmt, conn);
}
return dto;
}
//수정
public void updateMember(MemberDto dto) {
Connection conn=db.getConnection();
PreparedStatement pstmt=null;
//이름,핸드폰,주소,이메일만 수정 가능
String sql="update member set name=?,hp=?,addr=?,email=? where num=?";
try {
pstmt=conn.prepareStatement(sql);
//?바인딩
pstmt.setString(1, dto.getName());
pstmt.setString(2, dto.getHp());
pstmt.setString(3, dto.getAddr());
pstmt.setString(4, dto.getEmail());
pstmt.setString(5, dto.getNum());
pstmt.execute();
} catch (SQLException e) {
} finally {
db.dbClose(pstmt, conn);
}
}
}