package com.departmodule.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import util.ConnectDB;
import util.UtilDAO;
import util.VskPage;
import com.departmodule.bean.TbDepart;
public class TbDepartDAO {
private Connection conn = null;
private PreparedStatement pt = null;
private ResultSet rs = null;
private boolean flag = false;
private String sql = "";
private TbDepart tbDepart=null;
private List<TbDepart> tbDepartList=null;
private Map<Integer,TbDepart> mapDepart=null;
public boolean add(TbDepart inTbDepart){
try{
conn=ConnectDB.getSqlServerConnection();
sql="insert into Tb_Depart(DepartName,ParentDepartId,DepartMemo) values(?,?,?)";
pt = conn.prepareStatement(sql);
pt.setString(1,inTbDepart.getDepartName());
if(inTbDepart.getParentDepartId()==0)pt.setNull(2, Types.INTEGER);
else pt.setInt(2, inTbDepart.getParentDepartId());
pt.setString(3,inTbDepart.getDepartMemo());
if (pt.executeUpdate() > 0) flag=true;
}catch(Exception e){
e.printStackTrace();
}finally {
ConnectDB.closeSqlConnection();
}
return flag;
}
public boolean del(int inDepartId){
return UtilDAO.del("Tb_Depart", "DepartId", inDepartId);
}
public boolean edit(TbDepart inTbDepart){
try{
conn=ConnectDB.getSqlServerConnection();
sql="update Tb_Depart set DepartName=?,ParentDepartId=?,DepartMemo=?,CreateTime=getdate() where DepartId=?";
pt = conn.prepareStatement(sql);
pt.setString(1,inTbDepart.getDepartName());
pt.setInt(2,inTbDepart.getParentDepartId());
pt.setString(3,inTbDepart.getDepartMemo());
pt.setInt(4,inTbDepart.getDepartId());
if (pt.executeUpdate() > 0) flag=true;
}catch(Exception e){
e.printStackTrace();
}finally {
ConnectDB.closeSqlConnection();
}
return flag;
}
public TbDepart getTbDepartByDepartId(int inDepartId) {
tbDepart=null;
try{
conn = ConnectDB.getSqlServerConnection();
sql = "select * from Tb_Depart where DepartId=? ";
pt = conn.prepareStatement(sql);
pt.setInt(1,inDepartId);
rs = pt.executeQuery();
while (rs.next()) {
if (tbDepart == null)tbDepart = getTbDepart(rs);
}
}catch(Exception e){
e.printStackTrace();
}finally {
ConnectDB.closeSqlConnection();
}
return tbDepart;
}
public List<TbDepart> getTbDepartAll() {
tbDepartList=new ArrayList<TbDepart>();
try{
conn = ConnectDB.getSqlServerConnection();
sql = "select * from Tb_Depart";
pt = conn.prepareStatement(sql);
rs = pt.executeQuery();
while (rs.next()) {
tbDepartList.add(getTbDepart(rs));
}
}catch(Exception e){
e.printStackTrace();
}finally {
ConnectDB.closeSqlConnection();
}
return tbDepartList;
}
public List<TbDepart> getTbDepartAll(VskPage vp) {
tbDepartList=new ArrayList<TbDepart>();
try{
conn = ConnectDB.getSqlServerConnection();
sql = "select * from Tb_Depart";
vp.setAllCountSql(sql);
vp.getAllRecordCount(conn, vp.getAllCountSql());
pt = conn.prepareStatement(vp.processPageSql( "Tb_Depart", "DepartId"));
rs = pt.executeQuery();
while (rs.next()) {
tbDepartList.add(getTbDepart(rs));
}
}catch(Exception e){
e.printStackTrace();
}finally {
ConnectDB.closeSqlConnection();
}
return tbDepartList;
}
public TbDepart getTbDepart(ResultSet rs) throws Exception{
TbDepart tbDepart = new TbDepart();
tbDepart.setDepartId(rs.getInt("DepartId"));
tbDepart.setDepartName(rs.getString("DepartName"));
tbDepart.setParentDepartId(rs.getInt("ParentDepartId"));
tbDepart.setDepartMemo(rs.getString("DepartMemo"));
tbDepart.setCreateTime(rs.getString("CreateTime"));
return tbDepart;
}
public Map<Integer, TbDepart> getDepart(){
mapDepart = new HashMap<Integer,TbDepart>();
try{
conn = ConnectDB.getSqlServerConnection();
sql = "select * from Tb_Depart";
pt = conn.prepareStatement(sql);
rs = pt.executeQuery();
while (rs.next()) {
mapDepart.put(getTbDepart(rs).getDepartId(), getTbDepart(rs));
}
}catch(Exception e){
e.printStackTrace();
}finally {
ConnectDB.closeSqlConnection();
}
return mapDepart;
}
}