package com.wy.dao;

import com.wy.tool.JDBConnection;
import java.sql.*;
import java.util.*;
import com.wy.domain.MemberForm;

//對會員表的操作
public class MemberDao {
  private Connection connection = null; //定義連接的對象
  private PreparedStatement ps = null; //定義預準備的對象
  private JDBConnection jdbc = null; //定義數據庫連接對象
  public MemberDao() {
    jdbc = new JDBConnection();
    connection = jdbc.connection; //利用構造方法取得數據庫連接
  }


//以數據庫流水號為條件修改會員的密碼
  public boolean updatePassword(String password, Integer id) {
    try {
      ps = connection.prepareStatement("update tb_member set password=? where id=?");
      ps.setString(1, password);
      ps.setInt(2, id.intValue());
      ps.executeUpdate();
      ps.close();
      return true;
    }
    catch (SQLException ex) {
      return false;
    }
  }

//找回密碼之用
  public MemberForm selectFind(String name, String result) {
    MemberForm member = null;
    try {
      ps = connection.prepareStatement("select * from tb_member where name=? and result=?");
      ps.setString(1, name);
      ps.setString(2, result);
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        member = new MemberForm();
        member.setId(Integer.valueOf(rs.getString(1)));
        member.setName(rs.getString(2));
        member.setPassword(rs.getString(3));
        member.setReallyName(rs.getString(4));
        member.setAge(Integer.valueOf(rs.getString(5)));
        member.setProfession(rs.getString(6));
        member.setEmail(rs.getString(7));
        member.setQuestion(rs.getString(8));
        member.setResult(rs.getString(9));
      }
    }
    catch (SQLException ex) {
    }
    return member;
  }

  //全部查詢的操作或以會員名稱為條件查詢信息
  public MemberForm selectMemberForm(String name) {
    MemberForm member = null;
    try {
      ps = connection.prepareStatement("select * from tb_member where name=?");
      ps.setString(1, name);
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        member = new MemberForm();
        member.setId(Integer.valueOf(rs.getString(1)));
        member.setName(rs.getString(2));
        member.setPassword(rs.getString(3));
        member.setReallyName(rs.getString(4));
        member.setAge(Integer.valueOf(rs.getString(5)));
        member.setProfession(rs.getString(6));
        member.setEmail(rs.getString(7));
        member.setQuestion(rs.getString(8));
        member.setResult(rs.getString(9));
      }
    }
    catch (SQLException ex) {
    }
    return member;
  }

//刪除操作
  public boolean deleteMember(Integer id) {
    try {
      ps = connection.prepareStatement("delete from tb_member where id=?");
      ps.setString(1, id.toString());
      ps.executeUpdate();
      ps.close();
      return true;
    }
    catch (SQLException ex) {
      return false;
    }
  }

//添加信息
  public void insertMember(MemberForm form) {
    try {
      ps = connection.prepareStatement("insert into tb_member values (?,?,?,?,?,?,?,?)");
      ps.setString(1, form.getName());
      ps.setString(2, form.getPassword());
      ps.setString(3, form.getReallyName());
      ps.setString(4, form.getAge().toString());
      ps.setString(5, form.getProfession());
      ps.setString(6, form.getEmail());
      ps.setString(7, form.getQuestion());
      ps.setString(8, form.getResult());
      ps.executeUpdate();
      ps.close();
    }
    catch (SQLException ex) {
    }
  }

  //以數據庫流水號為條件修改信息
  public void updateMember(MemberForm form) {
    try {
      ps = connection.prepareStatement("update tb_member set name=?,password=?,reallyName=?,age=?,profession=?,email=?,question=?,result=? where id=?");
      ps.setString(1, form.getName());
      ps.setString(2, form.getPassword());
      ps.setString(3, form.getReallyName());
      ps.setString(4, form.getAge().toString());
      ps.setString(5, form.getProfession());
      ps.setString(6, form.getEmail());
      ps.setString(7, form.getQuestion());
      ps.setString(8, form.getResult());
      ps.setString(9, form.getId().toString());
      ps.executeUpdate();
      ps.close();
    }
    catch (SQLException ex) {
    }
  }



//全部查詢信息操作
  public List selectMember() {
    List list = new ArrayList();
    MemberForm member = null;
    try {
      ps = connection.prepareStatement("select * from tb_member order by id DESC");
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        member = new MemberForm();
        member.setId(Integer.valueOf(rs.getString(1)));
        member.setName(rs.getString(2));
        member.setPassword(rs.getString(3));
        member.setReallyName(rs.getString(4));
        member.setAge(Integer.valueOf(rs.getString(5)));
        member.setProfession(rs.getString(6));
        member.setEmail(rs.getString(7));
        member.setQuestion(rs.getString(8));
        member.setResult(rs.getString(9));
        list.add(member);
      }
    }
    catch (SQLException ex) {
    }
    return list;
  }

//以數據庫流水號為條件查詢信息
  public MemberForm selectOneMember(Integer id) {
    MemberForm member = null;
    try {
      ps = connection.prepareStatement("select * from tb_member where id=?");
      ps.setInt(1, id.intValue());
      ResultSet rs = ps.executeQuery();
      while (rs.next()) {
        member = new MemberForm();
        member.setId(Integer.valueOf(rs.getString(1)));
        member.setName(rs.getString(2));
        member.setPassword(rs.getString(3));
        member.setReallyName(rs.getString(4));
        member.setAge(Integer.valueOf(rs.getString(5)));
        member.setProfession(rs.getString(6));
        member.setEmail(rs.getString(7));
        member.setQuestion(rs.getString(8));
        member.setResult(rs.getString(9));
      }
    }
    catch (SQLException ex) {
    }
    return member;
  }




}