华迪实训javaee--劳模信息管理

package com.daoimpl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import com.dao.PerApplyInfoDao; import com.entity.CheckPerApply; import com.entity.PerApplyInfo; import com.utils.DBUtil; public class PerApplyInfoDaoImpl implements PerApplyInfoDao { @Override public boolean find(String perlogname) throws Exception { // TODO Auto-generated method stub Connection con= DBUtil.getConnection(); ResultSet rs=DBUtil.query(con,"select * from perapplyinfo where applyname='"+perlogname+"'");//个人先进信息表：perapplyinfo boolean b=false; try { if(rs.next())b=true; } catch (Exception e) { // TODO Auto-generated catch block throw new Exception(e); } return b; } @Override public void add(PerApplyInfo p) throws Exception { // TODO Auto-generated method stub Connection con=DBUtil.getConnection(); String sql="insert into perapplyinfo(gloryname,year,applyname,applyreason,applyresult,daiyu)values(?,?,?,?,?,?)"; boolean b=find(p.getApplyname()); System.out.println(p.getApplyname()); System.out.println(b); if(b) { throw new Exception(); } else { //DBUtil.excuteUpdate(con, sql); PreparedStatement ps=con.prepareStatement(sql); ps.setString(1, p.getGloryname()); ps.setString(2, p.getYear()); ps.setString(3, p.getApplyname()); ps.setString(4, p.getApplyreason()); ps.setString(5, p.getApplyresult()); ps.setString(6, p.getDaiyu()); ps.execute(); DBUtil.closeCon(con); } } @Override public PerApplyInfo checkInfo(String applyname) throws SQLException { // TODO Auto-generated method stub Connection con=DBUtil.getConnection(); String sql="select * from perapplyinfo where applyname='"+applyname+"'"; PerApplyInfo p=new PerApplyInfo(); ResultSet rs=DBUtil.query(con, sql); try { while(rs.next()&&rs!=null) { p.setApplyname(rs.getString("applyname"));//perapplyinfo数据表中的字段 p.setApplyreason(rs.getString("applyreason")); p.setApplyresult(rs.getString("applyresult")); p.setDaiyu(rs.getString("daiyu")); p.setGloryname(rs.getString("gloryname")); p.setYear(rs.getString("year")); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } DBUtil.closeCon(con); return p; } @Override public ArrayList<CheckPerApply> checkInfo(int rownum) { // TODO Auto-generated method stub Connection con=DBUtil.getConnection(); String num=rownum+""; String sql1="select * from personal join ("; String sql="select top 5 * from perapplyinfo where zhu not in (select top "+num+" zhu from perapplyinfo))"; String sql2="as A on personal.perlogname=A.applyname"; System.out.println(sql1+sql+sql2); ResultSet rs=DBUtil.query(con, sql1+sql+sql2); ArrayList<CheckPerApply> l=new ArrayList<CheckPerApply>(); try { //rs.next(); while(rs.next()) { CheckPerApply p=new CheckPerApply(); p.setName(rs.getString("realname")); p.setApplyname(rs.getString("applyname")); p.setApplyresult(rs.getString("applyresult")); p.setGloryname(rs.getString("gloryname")); p.setSex(rs.getString("sex")); p.setYear(rs.getString("year")); p.setApplyreason(rs.getString("applyreason")); p.setWorkplace(rs.getString("workplace")); l.add(p); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return l; } @Override public void update(PerApplyInfo newinfo) throws Exception//修改个人申报信息 { // TODO Auto-generated method stub Connection con=DBUtil.getConnection(); String sql="delete from perapplyinfo where applyname='"+newinfo.getApplyname()+"'";//先把原来的记录删掉，再添加上 DBUtil.excuteUpdate(con, sql); add(newinfo); DBUtil.closeCon(con); } @Override public void delete(String applyname) throws Exception { // TODO Auto-generated method stub Connection con=DBUtil.getConnection(); String sql="delete from perapplyinfo where applyname='"+applyname+"'"; String sql1="select * from perapplyinfo where applyname='"+applyname+"' and applyresult='已申报未审核'"; ResultSet rs=DBUtil.query(con, sql1); System.out.println("aaaaabbbbaaaa"); if(rs.next()) { System.out.println("aaaaabbbbaaaa"); DBUtil.excuteUpdate(con, sql); DBUtil.closeCon(con); } else{ throw new Exception(); } } public void shenhe(String name,String applyresult) throws SQLException { Connection con=DBUtil.getConnection(); String sql="update perapplyinfo set applyresult='"+applyresult+"'where applyname='"+name+"'"; DBUtil.excuteUpdate(con, sql); DBUtil.closeCon(con); } @Override public ArrayList<CheckPerApply> checkYearPer(String year, int rownum) { Connection con=DBUtil.getConnection(); String num=rownum+""; String sql3="select * from "; String sql1="(select * from personal join ("; String sql="select top 5 * from perapplyinfo where zhu not in (select top "+num+" zhu from perapplyinfo))"; String sql2="as A on personal.perlogname=A.applyname) as B "; String sql4="where B.year='"+year+"' and B.applyresult='已审核批准'"; System.out.println(sql3+sql1+sql+sql2+sql4); ResultSet rs=DBUtil.query(con, sql3+sql1+sql+sql2+sql4); ArrayList<CheckPerApply> l=new ArrayList<CheckPerApply>(); try { //rs.next(); while(rs.next()) { CheckPerApply p=new CheckPerApply(); p.setName(rs.getString("realname")); p.setApplyname(rs.getString("applyname")); p.setApplyresult(rs.getString("applyresult")); p.setGloryname(rs.getString("gloryname")); p.setSex(rs.getString("sex")); p.setYear(rs.getString("year")); p.setApplyreason(rs.getString("applyreason")); p.setWorkplace(rs.getString("workplace")); l.add(p); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return l; } }