dao层sql

一。以下为增删改查，分页，模糊查询的dao层实现

package servlet02.dao.impl;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import jdbc.util.DBConnection;import servlet02.Model.Worker;import servlet02.dao.intf.WorkerDao;public class WorkerDaoImpl implements WorkerDao{//数据添加public void add(Worker worker) {Connection connection = DBConnection.getConnection();String sql = "INSERT INTO office(username,age,sex,department,headerUrl,password) VALUES(?,?,?,?,?,?)";PreparedStatement ps = null;try {ps = connection.prepareStatement(sql);ps.setString(1, worker.getUsername());ps.setInt(2, worker.getAge());ps.setInt(3, worker.getSex());ps.setString(4, worker.getDepartment());ps.setString(5, worker.getHeaderUrl());ps.setString(6, worker.getPassword());ps.execute();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {DBConnection.close(ps, null, connection);}}@Override//数据查询public List<Worker> query() {Connection connection = DBConnection.getConnection();List<Worker> workers = new ArrayList<Worker>();String sql = "SELECT  id,username,age,sex,department,headerUrl,password FROM office";PreparedStatement ps = null;ResultSet rs = null;try {ps = connection.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()) {String id=rs.getString("id");String username = rs.getString("username");String age = rs.getString("age");String sex = rs.getString("sex");String department =rs.getString("department");String headerUrl = rs.getString("headerUrl");String password = rs.getString("password");Worker worker = new Worker(Integer.parseInt(id),username,Integer.parseInt(age),Integer.parseInt(sex),  department, headerUrl,password );workers.add(worker);}ps.execute();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {DBConnection.close(ps, rs, connection);}return workers;}//数据修改 username,age,sex,department,headerUrl,passwordpublic void update(Worker worker) {Connection connection=DBConnection.getConnection();String sql="UPDATE office SET username=?,age=?,sex=?,department=?,headerUrl=?,password=? WHERE id=?";PreparedStatement ps=null;try {ps=connection.prepareStatement(sql);ps.setString(1, worker.getUsername());ps.setInt(2, worker.getAge());ps.setInt(3, worker.getSex());ps.setString(4, worker.getDepartment());ps.setString(5, worker.getHeaderUrl());ps.setString(6, worker.getPassword());ps.setInt(7, worker.getId());ps.executeUpdate();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{DBConnection.close(ps, null, connection);}}//查询账号密码语句/*//删除单个public void delete(String id) {Connection connection=DBConnection.getConnection();String sql="DELETE FROM office WHERE id=?";PreparedStatement ps=null;try {ps=connection.prepareStatement(sql);ps.setString(1, id);System.out.println(ps.toString());ps.execute();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{DBConnection.close(ps, null, connection);}} *///根据id查询  username,age,sex,department,headerUrl,password@Overridepublic Worker queryById(String id) {Connection connection= DBConnection.getConnection();String sql="SELECT * FROM office WHERE id=?";PreparedStatement ps=null;ResultSet rs=null;Worker worker=null;try {ps=connection.prepareStatement(sql);ps.setString(1, id);rs=ps.executeQuery();while(rs.next()){int id1=rs.getInt("id");String username=rs.getString("username");int age=rs.getInt("age");int sex=rs.getInt("sex");String department=rs.getString("department");String headerUrl=rs.getString("headerUrl");String password=rs.getString("password");worker=new Worker(id1, username, age, sex, department, headerUrl, password);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return worker;}//批量删除，删除选中@Overridepublic void deleteselect(String[] idList) {StringBuilder idListDemo = new StringBuilder();  // 最终的效果 ?,?,?  for (int i = 0; i < idList.length; i++) {  idListDemo.append("?");  if (i < idList.length - 1) {  idListDemo.append(",");  }  }  System.out.println("idList.length: "+idList.length);System.out.println("sql语句中idListDemo："+idListDemo);Connection connection= DBConnection.getConnection();StringBuffer sql = new StringBuffer();  sql.append("delete from office ")  .append("where id in (")  .append(idListDemo)  .append(")");System.out.println("sql语句:"+sql);PreparedStatement ps=null;try {ps=connection.prepareStatement(sql.toString());for (int i = 0; i < idList.length; i++) {  ps.setString(i + 1, idList[i]);  }  ps.executeUpdate();  } catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{DBConnection.close(ps, null, connection);}}@Overridepublic List<Worker> queryusername() {Connection connection=DBConnection.getConnection();List<Worker> workers=new ArrayList<Worker>();String sql="SELECT username,password FROM office";PreparedStatement ps=null;ResultSet rs=null;Worker worker=null;try {ps=connection.prepareStatement(sql);rs=ps.executeQuery();while(rs.next()){String userName=rs.getString("username");String passWord=rs.getString("password");worker=new Worker(userName, passWord);workers.add(worker);}ps.execute();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{DBConnection.close(ps, rs, connection);}return workers;}//查询数据库worker总记录数@Overridepublic int queryAllcounts() {Connection  connection=DBConnection.getConnection();String sql="SELECT count(*) FROM office";PreparedStatement ps=null;ResultSet rs=null;int totalCount=0;//总记录数try {ps=connection.prepareStatement(sql);rs=ps.executeQuery();if(rs.next()){totalCount=rs.getInt("count(*)");}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}finally{DBConnection.close(ps, rs, connection);}return totalCount;}//分页查询@Overridepublic List<Worker> queryDividePages(int currentpage, int pagecount) {Connection  connection=DBConnection.getConnection();String sql="SELECT * FROM office limit ?,?";PreparedStatement ps=null;List<Worker> workers=new ArrayList<Worker>();try {ps=connection.prepareStatement(sql);ps.setInt(1, (currentpage-1)*pagecount);ps.setInt(2, pagecount);} catch (SQLException e1) {// TODO Auto-generated catch blocke1.printStackTrace();}ResultSet rs=null;try {rs=ps.executeQuery();while(rs.next()){String id=rs.getString("id");String username = rs.getString("username");String age = rs.getString("age");String sex = rs.getString("sex");String department =rs.getString("department");String headerUrl = rs.getString("headerUrl");String password = rs.getString("password");Worker worker = new Worker(Integer.parseInt(id),username,Integer.parseInt(age),Integer.parseInt(sex),  department, headerUrl,password );workers.add(worker);}ps.execute();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return workers;}//多条件查询@Overridepublic List<Worker> ManyConditionsQuery(String username1, String sex1, String department1,int currentpage, int pagecount) {/*System.out.println("sex的值为"+sex1);System.out.println("2".equals(sex1));*/System.out.println("dao层当前页"+currentpage);System.out.println("dao层当前页显示条数"+pagecount);List<Worker> workers=new ArrayList<Worker>();Connection  connection=DBConnection.getConnection();String sql="SELECT * FROM office WHERE 1=1";StringBuffer sb=new StringBuffer(sql);PreparedStatement ps=null;ResultSet rs=null;if(null!=username1&&""!=username1){sb.append(" and username LIKE "+"'%"+username1+"%'");}if(null!=sex1&&""!=sex1&&!"2".equals(sex1)){sb.append(" and sex LIKE "+"'%"+sex1+"%'");}if(null!=department1&&""!=department1){sb.append(" and department LIKE "+"'%"+department1+"%'");}sb.append(" limit ?,?");System.out.println("sql语句："+sb.toString());try {ps=connection.prepareStatement(sb.toString());ps.setInt(1, (currentpage-1)*pagecount);ps.setInt(2, pagecount);System.out.println("sql语句："+sb.toString());rs=ps.executeQuery();while(rs.next()){String id=rs.getString("id");String username = rs.getString("username");String age = rs.getString("age");String sex = rs.getString("sex");String department =rs.getString("department");String headerUrl = rs.getString("headerUrl");String password = rs.getString("password");Worker worker = new Worker(Integer.parseInt(id),username,Integer.parseInt(age),Integer.parseInt(sex),  department, headerUrl,password );workers.add(worker);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return workers;}   //查询符合多条件查询的总记录数@Overridepublic int getqueryTotal(String username1, String sex1, String department1) {List<Worker> workers=new ArrayList<Worker>();Connection  connection=DBConnection.getConnection();String sql="SELECT * FROM office WHERE 1=1";StringBuffer sb=new StringBuffer(sql);PreparedStatement ps=null;ResultSet rs=null;if(null!=username1&&""!=username1){sb.append(" and username LIKE "+"'%"+username1+"%'");}if(null!=sex1&&""!=sex1&&!"2".equals(sex1)){System.out.println("sex1进入if");sb.append(" and sex LIKE "+"'%"+sex1+"%'");}else{System.out.println("sex1进入else");}if(null!=department1&&""!=department1){sb.append(" and department LIKE "+"'%"+department1+"%'");}try {ps=connection.prepareStatement(sb.toString());rs=ps.executeQuery();while(rs.next()){String id=rs.getString("id");String username = rs.getString("username");String age = rs.getString("age");String sex = rs.getString("sex");String department =rs.getString("department");String headerUrl = rs.getString("headerUrl");String password = rs.getString("password");Worker worker = new Worker(Integer.parseInt(id),username,Integer.parseInt(age),Integer.parseInt(sex),  department, headerUrl,password );workers.add(worker);}} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return workers.size();}}