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 block
			e.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 block
			e.printStackTrace();
		} finally {
			DBConnection.close(ps, rs, connection);
		}
		return workers;
	}
	//数据修改 username,age,sex,department,headerUrl,password
	public 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 block
			e.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 block
			e.printStackTrace();
		}
		finally{
			DBConnection.close(ps, null, connection);
		}
	}
	 */

	//根据id查询  username,age,sex,department,headerUrl,password
	@Override
	public 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 block
			e.printStackTrace();
		}
		return worker;

	}
	//批量删除，删除选中
	@Override
	public 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 block
			e.printStackTrace();
		}finally{
			DBConnection.close(ps, null, connection);
		}

	}

	@Override
	public 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 block
			e.printStackTrace();
		}finally{
			DBConnection.close(ps, rs, connection);
		}
		return workers;
	}
	//查询数据库worker总记录数
	@Override
	public 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 block
			e.printStackTrace();
		}finally{
			DBConnection.close(ps, rs, connection);
		}
		return totalCount;

	}
	//分页查询
	@Override
	public 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 block
			e1.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 block
			e.printStackTrace();
		}
		return workers;

	}
	//多条件查询
	@Override
	public 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 block
			e.printStackTrace();
		}
		return workers;
	}
   //查询符合多条件查询的总记录数
	@Override
	public 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 block
			e.printStackTrace();
		}
		return workers.size();
	}

}