dao/AdminMemberDAO.java > MODEL2

• 목록

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.con.close();

} catch (Exception e) {

e.printStackTrace();

}

return num;

}

}