package com.hongbo5.top.dao; import com.hongbo5.top.model.AdminDatagrid; import com.hongbo5.top.model.AdminDatagrid; import com.hongbo5.top.model.MarathonerDatagrid; import com.hongbo5.top.model.PageBean; import com.hongbo5.top.util.DateUtil; import com.hongbo5.top.util.StringUtil; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; /** * 管理员信息Dao */ public class AdminDatagridDao { public ResultSet adminList(Connection con, PageBean pageBean,AdminDatagrid adminDatagrid,String bBirthday,String eBirthday)throws Exception{ //关联查询 //select * :"*"不明确,数据库混乱,导致获得id不理想 //数据库取名字独特 加前缀 StringBuffer sb=new StringBuffer("select * from t_adminInfo a,t_user u1 where a.userId=u1.id"); //like 模糊查询 if (StringUtil.isNotEmpty(adminDatagrid.getJobNo())) { sb.append(" and a.jobNo like '%" + adminDatagrid.getJobNo() + "%'"); } if (StringUtil.isNotEmpty(adminDatagrid.getName())) { sb.append(" and a.name like '%" + adminDatagrid.getName() + "%'"); } //精确查询 if (StringUtil.isNotEmpty(adminDatagrid.getSex())) { sb.append(" and a.sex ='"+adminDatagrid.getSex()+"'"); } //userId数据操作 在model中定义userId值为-1 if (adminDatagrid.getUserId()!=-1) { sb.append(" and a.userId ='"+adminDatagrid.getUserId()+"'"); } //bBirthday--eBirthday范围 数据库操作 //mysql中 TO_DAYS() if (StringUtil.isNotEmpty(bBirthday)) { sb.append(" and TO_DAYS(a.birthday)>=TO_DAYS('"+bBirthday+"')"); } if (StringUtil.isNotEmpty(eBirthday)) { sb.append(" and TO_DAYS(a.birthday)<=TO_DAYS('"+eBirthday+"')"); } if(pageBean!=null){ sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());//分页功能 } PreparedStatement pstmt=con.prepareStatement(sb.toString()); return pstmt.executeQuery(); } public int adminCount(Connection con,AdminDatagrid adminDatagrid,String bBirthday,String eBirthday)throws Exception{ //关联查询 StringBuffer sb=new StringBuffer("select count(*) as total from t_adminInfo a,t_user u1 where a.userId=u1.id"); if (StringUtil.isNotEmpty(adminDatagrid.getJobNo())) { sb.append(" and a.jobNo like '%" + adminDatagrid.getJobNo() + "%'"); } if (StringUtil.isNotEmpty(adminDatagrid.getName())) { sb.append(" and a.name like '%" + adminDatagrid.getName() + "%'"); } //精确查询 if (StringUtil.isNotEmpty(adminDatagrid.getSex())) { sb.append(" and a.sex ='"+adminDatagrid.getSex()+"'"); } //userId数据操作 在model中定义userId值为-1 if (adminDatagrid.getUserId()!=-1) { sb.append(" and a.userId ='"+adminDatagrid.getUserId()+"'"); } //bBirthday--eBirthday范围 数据库操作 //mysql中 TO_DAYS() if (StringUtil.isNotEmpty(bBirthday)) { sb.append(" and TO_DAYS(a.birthday)>=TO_DAYS('"+bBirthday+"')"); } if (StringUtil.isNotEmpty(eBirthday)) { sb.append(" and TO_DAYS(a.birthday)<=TO_DAYS('"+eBirthday+"')"); } PreparedStatement pstmt=con.prepareStatement(sb.toString()); ResultSet rs=pstmt.executeQuery(); if(rs.next()){ return rs.getInt("total"); }else{ return 0; } } public int adminDelete(Connection con, String delIds)throws Exception { String sql = "delete from t_adminInfo where adminId in(" + delIds + ")"; PreparedStatement psmt = con.prepareStatement(sql); return psmt.executeUpdate(); } public int adminAdd(Connection con, AdminDatagrid adminDatagrid)throws Exception { String sql = "insert into t_adminInfo values(null,?,?,?,?,?,?,?)"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1,adminDatagrid.getJobNo()); pstmt.setString(2,adminDatagrid.getName()); pstmt.setString(3,adminDatagrid.getSex()); pstmt.setString(4, DateUtil.formatDate(adminDatagrid.getBirthday(),"yyyy-MM-dd")); pstmt.setInt(5,adminDatagrid.getUserId()); pstmt.setString(6,adminDatagrid.getEmail()); pstmt.setString(7,adminDatagrid.getSynopses()); return pstmt.executeUpdate(); } public int adminModify(Connection con, AdminDatagrid adminDatagrid)throws Exception { String sql = "update t_adminInfo set jobNo=?,name=?,sex=?,birthday=?,userId=?,email=?,synopses=? where adminId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1,adminDatagrid.getJobNo()); pstmt.setString(2,adminDatagrid.getName()); pstmt.setString(3,adminDatagrid.getSex()); pstmt.setString(4, DateUtil.formatDate(adminDatagrid.getBirthday(),"yyyy-MM-dd")); pstmt.setInt(5,adminDatagrid.getUserId()); pstmt.setString(6,adminDatagrid.getEmail()); pstmt.setString(7,adminDatagrid.getSynopses()); pstmt.setInt(8,adminDatagrid.getAdminId()); return pstmt.executeUpdate(); } //判断是否有数据 public boolean getAdminByUserId(Connection con, String userId) throws Exception { String sql = "select * from t_adminInfo where userId=?"; PreparedStatement pstmt = con.prepareStatement(sql); pstmt.setString(1,userId); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return true; }else{ return false; } } }
最近下载更多
poipoiiop LV8
2023年1月3日
多吃多占 LV12
2022年11月30日
wkw1376744752 LV1
2022年10月26日
zhouzhouzhouyy LV2
2022年10月25日
uni-code_0123 LV1
2022年10月24日