dao/AdminMemberDAO.java
페이지 정보
작성자 관리자 작성일 23-03-18 22:17 조회 453 댓글 0본문
package dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import model.Member;
public class AdminMemberDAO {
//사용할 객체를 미리 선언
Statement stmt;
PreparedStatement pstmt;
ResultSet rs;
DAO d = new DAO(); //d.getCon() , d.conn 사용 목적
// 회원 총 수
public int getAllcount(String field, String search) {
d.getCon();
int count = 0;
try {
String sql = "select count(*) from member";
if(search != null && !search.equals("")) {
sql = "select count(*) from member where "+field+" like '%"+search+"%'";
}
pstmt = d.conn.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()) {
count = rs.getInt(1);
}
pstmt.close();
d.conn.close();
}catch(Exception e) {
e.printStackTrace();
}
return count;
}
//회원목록
public ArrayList<Member> getAllMember(int startRow, int endRow, String field, String search) {
d.getCon();
ArrayList<Member> v = new ArrayList<Member>();
try {
String sql = "select * from member limit ?,?";
if(search != null && !search.equals("")) {
sql = "select * from member where "+field+" like '%"+search+"%' limit ?,?";
}
pstmt = d.conn.prepareStatement(sql);
pstmt.setInt(1, startRow);
pstmt.setInt(2, endRow);
rs = pstmt.executeQuery();
while(rs.next()) {
Member m = new Member();
m.setId(rs.getString("id"));
m.setName(rs.getString("name"));
m.setAge(rs.getInt("age"));
m.setGender(rs.getString("gender"));
m.setEmail(rs.getString("email"));
m.setLevel(rs.getString("level"));
v.add(m);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
pstmt.close();
d.conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
return v;
}
//회원 한명의 정보
public Member oneMember(String id) {
d.getCon();
Member m = new Member();
try {
String sql = "select * from member where id=?";
pstmt = d.conn.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next()) {
m.setId(rs.getString("id")); //id : 데이터베이스 테이블 칼럼명
m.setPass(rs.getString("pass"));
m.setName(rs.getString("name"));
m.setAge(rs.getInt("age"));
m.setGender(rs.getString("gender"));
m.setEmail(rs.getString("email"));
m.setLevel(rs.getString("level"));
m.setMemo(rs.getString("memo"));
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
pstmt.close();
d.conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
return m;
}
//회원등록
public void insertMember(Member member) {
d.getCon();
try {
String sql = "insert into member values (?,?,?,?,?,?,?,?)";
pstmt = d.conn.prepareStatement(sql);
pstmt.setString(1, member.getId());
pstmt.setString(2, member.getPass());
pstmt.setString(3, member.getName());
pstmt.setInt(4, member.getAge());
pstmt.setString(5, member.getGender());
pstmt.setString(6, member.getEmail());
pstmt.setString(7, "1");
pstmt.setString(8, member.getMemo());
pstmt.executeUpdate();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close(); //사용 객체 닫음
d.conn.close(); //사용 객체 닫음
}catch(Exception e) {
e.printStackTrace();
}
}
}
//회원 수정
public void updateMember(Member m) {
d.getCon();
try {
String sql = "update member set pass=?,name=?,age=?,gender=?,email=?,memo=? where id=?";
pstmt = d.conn.prepareStatement(sql);
pstmt.setString(1, m.getPass());
pstmt.setString(2, m.getName());
pstmt.setInt(3, m.getAge());
pstmt.setString(4, m.getGender());
pstmt.setString(5, m.getEmail());
pstmt.setString(6, m.getMemo());
pstmt.setString(7, m.getId());
pstmt.executeUpdate();
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close();
d.conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
//회원삭제
public void deleteMember(String id) {
d.getCon();
try {
String sql = "delete from member where id=?";
pstmt = d.conn.prepareStatement(sql);
pstmt.setString(1, id);
pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
pstmt.close();
d.conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
//아이디 존재여부
public int loginSelect(String id) {
d.getCon();
int num = 0;
try {
String sql = "select count(*) from member id=?";
pstmt = d.con.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next()) {
num = rs.getInt(1);
}
rs.close();
pstmt.close();
d.conn.close();
} catch (Exception e) {
e.printStackTrace();
}
return num;
}
}
댓글목록 0
등록된 댓글이 없습니다.