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;
}
}