UserDAO.java

 package model;

import database.ConnectionDB;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Vector;

public class UserDAO
{
 private UserDTO userDTO;
 private Connection conn;              //连接对象
 private CallableStatement cstmt;     //本接口实现存储过程访问
 private Statement stmt;     //通过简单SQL语句访问数据库
 private PreparedStatement pstmt;   //预编译的SQL语句访问数据库
 private ResultSet rs;             //结果集
 private SimpleDateFormat dateTime = new SimpleDateFormat("yyyy-MM-dd");

 public UserDAO()
 {
  conn = ConnectionDB.getConnection();
 }

 public boolean validate()
 {
   boolean flag = false;
   try
   {
    conn = ConnectionDB.getConnection();
    cstmt = conn.prepareCall("{?=call user_validate(?,?)}");
   cstmt.registerOutParameter(1, Types.INTEGER);
   cstmt.setString(2, userDTO.getUserName());
   cstmt.setString(3, userDTO.getPassword());
   cstmt.execute();
   flag = (cstmt.getInt(1)==1);
   cstmt.close();
   conn.close();
   }
   catch (SQLException e)
   {
    flag = false;
    System.out.print(e.getMessage());
   }
   return flag;
    }

 public Vector<UserDTO> fillWorker()
 {
  Vector<UserDTO> users = new Vector<UserDTO>();
  try
  {
   conn = ConnectionDB.getConnection();
   stmt=conn.createStatement();
   rs=stmt.executeQuery("select * from Worker");
   while(rs.next())
   {
    userDTO = new UserDTO();
    userDTO.setWorkerID(rs.getString(rs.findColumn("WorkerID")));
    userDTO.setUserName(rs.getString(rs.findColumn("PurchaserName")));
    userDTO.setDepartmentName(rs.getString(rs.findColumn("DepartmentName")));
    userDTO.setWorkKind(rs.getString(rs.findColumn("WorkKind")));
    userDTO.setSex(rs.getString(rs.findColumn("Sex")));
    userDTO.setBirthday(dateTime.format(rs.getDate(rs.findColumn("Birthday"))));
    userDTO.setHomePhone(rs.getString(rs.findColumn("HomePhone")));
    userDTO.setTEL(rs.getString(rs.findColumn("TEL")));
    userDTO.setGrantDeny(rs.getString(rs.findColumn("GrantDeny")));
    users.add(userDTO);
   }
   rs.close();
   stmt.close();
   conn.close();
  }
  catch (SQLException e)
  {
   System.out.print(e.getMessage());
  }
  return users;
 }

 public Vector<UserDTO> searchWorker()
 {
  Vector<UserDTO> users = new Vector<UserDTO>();
  try
  {
   conn = ConnectionDB.getConnection();
   pstmt=conn.prepareStatement("select * from Worker where WorkerID like ? and PurchaserName like ? and DepartmentName like ?");
   pstmt.setString(1, userDTO.getWorkerID() + "%");
   pstmt.setString(2, "%" + userDTO.getUserName() + "%");
   pstmt.setString(3, userDTO.getDepartmentName() + "%");
   rs=pstmt.executeQuery();
   while(rs.next())
   {
    userDTO = new UserDTO();
    userDTO.setWorkerID(rs.getString(rs.findColumn("WorkerID")));
    userDTO.setUserName(rs.getString(rs.findColumn("PurchaserName")));
    userDTO.setDepartmentName(rs.getString(rs.findColumn("DepartmentName")));
    userDTO.setWorkKind(rs.getString(rs.findColumn("WorkKind")));
    userDTO.setSex(rs.getString(rs.findColumn("Sex")));
    userDTO.setBirthday(dateTime.format(rs.getDate(rs.findColumn("Birthday"))));
    userDTO.setHomePhone(rs.getString(rs.findColumn("HomePhone")));
    userDTO.setTEL(rs.getString(rs.findColumn("TEL")));
    userDTO.setGrantDeny(rs.getString(rs.findColumn("GrantDeny")));
    users.add(userDTO);
   }
   rs.close();
   pstmt.close();
   conn.close();
  }
  catch (SQLException e)
  {
   System.out.print(e.getMessage());
  }
  return users;
 }

 public boolean addWorker()
 {
  boolean flag = false;
   try
   {
    conn = ConnectionDB.getConnection();
    cstmt = conn.prepareCall("{call Insert_Worker(?,?,?,?,?,?,?,?,?,?)}");
   cstmt.setString(1, userDTO.getWorkerID());
   cstmt.setString(2, userDTO.getUserName());
   cstmt.setString(3, userDTO.getDepartmentName());
   cstmt.setString(4, userDTO.getWorkKind());
   cstmt.setString(5, userDTO.getSex());
   cstmt.setString(6, userDTO.getBirthday());
   cstmt.setString(7, userDTO.getHomePhone());
   cstmt.setString(8, userDTO.getTEL());
   cstmt.setString(9, userDTO.getPassword());
   cstmt.setString(10, userDTO.getGrantDeny());
   flag = (cstmt.executeUpdate() > 0);
   cstmt.close();
   conn.close();
   }
   catch (SQLException e)
   {
    flag = false;
    System.out.print(e.getMessage());
   }
   return flag;
 }

 public boolean updateWorker()
 {
  boolean flag = false;
   try
   {
    conn = ConnectionDB.getConnection();
    cstmt = conn.prepareCall("{call Update_Worker(?,?,?,?,?,?,?,?,?,?)}");
    cstmt.setString(1, userDTO.getWorkerID());
   cstmt.setString(2, userDTO.getUserName());
   cstmt.setString(3, userDTO.getDepartmentName());
   cstmt.setString(4, userDTO.getWorkKind());
   cstmt.setString(5, userDTO.getSex());
   cstmt.setString(6, userDTO.getBirthday());
   cstmt.setString(7, userDTO.getHomePhone());
   cstmt.setString(8, userDTO.getTEL());
   cstmt.setString(9, userDTO.getPassword());
   cstmt.setString(10, userDTO.getGrantDeny());
   flag = (cstmt.executeUpdate() > 0);
   cstmt.close();
   conn.close();
   }
   catch (SQLException e)
   {
    flag = false;
    System.out.print(e.getMessage());
   }
   return flag;
 }

 public boolean deleteWorker()
 {
  boolean flag = false;
   try
   {
    conn = ConnectionDB.getConnection();
    cstmt = conn.prepareCall("{call Delete_Worker(?)}");
   cstmt.setString(1, userDTO.getWorkerID());
   flag = (cstmt.executeUpdate() > 0);
   cstmt.close();
   conn.close();
   }
   catch (SQLException e)
   {
    flag = false;
    System.out.print(e.getMessage());
   }
   return flag;
 }

 public void setUserDTO(UserDTO userDTO) {
  this.userDTO = userDTO;
 }
}