📄 staffdao.java
字号:
conn.close();
}
if(ps!=null)
{
ps.close();
}
if(rs!=null)
{
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return i;
}
public int updateStaff(int employeeid,String name,int sex,String birthday,String nation,String major,String liveaddress,String phone,String mail,String QQ,String MSN,int departmentid,String position,int roleid)
{
int i = 0;
try {
conn = tj.getConnection();
conn.setAutoCommit(false);
st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
//String departmentid=departmentid;
String professiontitle="";
//String position=position;
int jobtype=0;
int isinservice=0;
String oldname="";
//String sex=sex;
//String birthday=birthday;
String hometown="";
//String nation=nation;
String politicalappear="";
int ismarried=0;
String health="";
int weight=0;
int height=0;
String ingredient="";
//String major=major;
String workyears="";
String educationdegree="";
String foreignlanguage="";
String foreignlanLevel="";
String chineselevel="";
String computerlevel="";
String residenceplace="";
//String liveaddress="";
String archivesplace="";
String specialskills="";
String rewardandpunish="";
String workexperience="";
String familysituation="";
String emergencontact="";
String remark="";
String yueyulevel="";
String sql="update employee set departmentid="+departmentid+",position='"+position+"',name='"+name+"',sex="+sex+",birthday=to_date('"+birthday+"','yyyy-mm-dd'),nation='"+nation+"',major='"+major+"',liveaddress='"+liveaddress+"'where employeeid="+employeeid;
System.out.println(sql);
ps = conn.prepareStatement(sql);
i = ps.executeUpdate();
if(i>0)
{
String sqllogin="update login set roleid="+roleid+"where employeeid="+employeeid;
String sqlcon="update contactlist set mobilephone='"+phone+"',qq='"+QQ+"',msn='"+MSN+"',email='"+mail+"' where employeeid="+employeeid;
st.executeUpdate(sqllogin);
System.out.println(sqllogin);
st.executeUpdate(sqlcon);
System.out.println(sqlcon);
}
conn.commit();
} catch (SQLException e) {
try {
if(conn!=null)
{
conn.rollback();//事务回滚
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
finally
{
try {
if(conn!=null)
{
conn.close();
}
if(ps!=null)
{
ps.close();
}
if(rs!=null)
{
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return i;
}
public int delStaff(int employeeid)
{
int i = 0;
try {
conn = tj.getConnection();
conn.setAutoCommit(false);
st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ps = conn.prepareStatement("delete from contactlist where employeeid=?");
ps.setInt(1,employeeid);
i = ps.executeUpdate();
if(i>0)
{
st.executeUpdate("delete from login where employeeid="+employeeid);
st.executeUpdate("delete from employee where employeeid="+employeeid);
}
conn.commit();
} catch (SQLException e) {
try {
if(conn!=null)
{
conn.rollback();//事务回滚
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
finally
{
try {
if(conn!=null)
{
conn.close();
}
if(ps!=null)
{
ps.close();
}
if(rs!=null)
{
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return i;
}
public List searchStaff(int employeeid)
{
List<UserInfo> lt = new ArrayList<UserInfo>();
try {
conn=tj.getConnection();
conn.setAutoCommit(false);
String sql = "select * from (select e.employeeid,e.name,e.sex,e.nation,e.position,e.liveaddress,e.major,e.birthday,d.departmentid,c.mobilephone,c.qq,c.msn,c.email,l.roleid from employee e,department d ,contactlist c,login l where e.departmentid=d.departmentid and e.employeeid=c.employeeid and e.employeeid=l.employeeid ) where employeeid="+employeeid;
st=conn.createStatement();
rs=st.executeQuery(sql);
while(rs.next())
{
UserInfo uif = new UserInfo();
String name=rs.getString("name");
int sex=rs.getInt("sex");
String nation=rs.getString("nation");
String position=rs.getString("position");
String liveaddress=rs.getString("liveaddress");
String major=rs.getString("major");
Date birthday=rs.getDate("birthday");
int departmentid=rs.getInt("departmentid");
String phone=rs.getString("mobilephone");
String qq=rs.getString("qq");
String msn=rs.getString("msn");
String mail=rs.getString("email");
int roleid=rs.getInt("roleid");
uif.setEmployeeId(employeeid);
uif.setName(name);
uif.setSex(sex);
uif.setNation(nation);
uif.setPosition(position);
uif.setLiveAddress(liveaddress);
uif.setMajor(major);
uif.setBirthday(birthday);
uif.setDepartmentId(departmentid);
uif.setPhone(phone);
uif.setQq(qq);
uif.setMsn(msn);
uif.setEmail(mail);
uif.setRoleid(roleid);
lt.add(uif);
rs.next();
}
conn.commit();
} catch (SQLException e) {
try {
if(conn!=null)
{
conn.rollback();//事务回滚
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
finally
{
try {
if(conn!=null)
{
conn.close();
}
if(ps!=null)
{
ps.close();
}
if(rs!=null)
{
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return lt;
}
public List getStaff(int employeeid)
{
System.out.println("编号为：。。。。。。。。。。。。。。。。。。。。"+employeeid);
List<Deptment> list=new ArrayList<Deptment>();
conn=tj.getConnection();
try
{
conn.setAutoCommit(false);
String sql="select e.name,d.departmentname from employee e, department d where e.employeeid=d.leader and e.employeeid in (select d.leader from department d where d.departmentid in ( select e.departmentid from employee e where e.employeeid="+employeeid+")) ";
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next())
{
Deptment dp=new Deptment();
dp.setDepartmentName(rs.getString("departmentname"));
dp.setName(rs.getString("name"));
list.add(dp);
}
System.out.println("List大小为://///////////////"+list.size());
conn.commit();
}
catch(SQLException e)
{
try{
if(conn!=null)
conn.rollback();
}
catch(SQLException ex)
{
ex.printStackTrace();
}
e.printStackTrace();
}
finally
{
try
{
if(conn!=null)
conn.close();
if(ps !=null)
ps.close();
if(rs!=null)
rs.close();
}
catch(SQLException e)
{
e.printStackTrace();
}
}
return list;
}
public List getStaff()
{
List<UserInfo> lt = new ArrayList<UserInfo>();
try {
conn=tj.getConnection();
conn.setAutoCommit(false);
String sql="select e.employeeid,e.name,e.sex,e.nation,d.departmentid,d.departmentname,c.mobilephone from employee e,department d ,contactlist c where e.departmentid=d.departmentid and e.employeeid=c.employeeid and 1=1";
ps=conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
res=new PageableResultSet(ps.executeQuery());
while(res.next())
{
String sexstr=null;
UserInfo uif = new UserInfo();
int employeeid=res.getInt("employeeid");
String name=res.getString("name");
int sex=res.getInt("sex");
String nation=res.getString("nation");
int departmentid=res.getInt("departmentid");
String departmentname=res.getString("departmentname");
String phone=res.getString("mobilephone");
if(sex==1)
sexstr="女";
if(sex==0)
sexstr="男";
uif.setEmployeeId(employeeid);
uif.setName(name);
uif.setSex(sex);
uif.setSexstr(sexstr);
uif.setNation(nation);
uif.setDepartmentId(departmentid);
uif.setDepartmentName(departmentname);
uif.setPhone(phone);
lt.add(uif);
}
System.out.println(lt.size());
conn.commit();
} catch (SQLException e) {
try {
if(conn!=null)
{
conn.rollback();//事务回滚
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
finally
{
try {
if(conn!=null)
{
conn.close();
}
if(ps!=null)
{
ps.close();
}
if(rs!=null)
{
rs.close();
}
if(res!=null)
{
res.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return lt;
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -