多条件分页，增加，修改，删除（包含文件上传，下载）（图书管理系统）

首先，我们看一下实现类中的 sql代码

package cn.happy.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.junit.Test;

import cn.happy.dao.BaseDao;
import cn.happy.dao.bookdao;
import cn.happy.entity.book;
import cn.happy.entity.bookcategory;

public class bookdaoimpl extends BaseDao implements bookdao{
	
	public List<bookcategory> selectdept() throws Exception {
        List<bookcategory> deptlist = new ArrayList<bookcategory>();
        String sql = "select * from bookcategory";
        ResultSet rs = executeQuery(sql);
        if (rs != null) {
            while (rs.next()) {
            	bookcategory b = new bookcategory();
            b.setCateid(rs.getInt("cateid"));
            b.setCatename(rs.getString("catename"));
                deptlist.add(b);
            }
        }
        return deptlist;
    }
	
/*	@Test
	public void gs() throws Exception{
		 List<book> count = selectpage(4,4);
		    for (book item:count) {
		        System.out.println(item.getBookname());
		    }
	}
*/
	@Override
	public List<book> selectpage(int pageindex, int pagesize) throws Exception {
		List<book> list=new ArrayList<book>();
		String sql="select * from book limit ?,?";
		ResultSet rs=executeQuery(sql, pageindex,pagesize);
		if(rs!=null){
			while(rs.next()){
				book b=new book();
				b.setBookid(rs.getInt("bookid"));
				b.setBookname(rs.getString("bookname"));
				b.setBookcategory(rs.getInt("bookcategory"));
				b.setBoolprice(rs.getDouble("bookprice"));
				b.setBookauthor(rs.getString("bookauthor"));
				b.setBookdatetime(rs.getDate("bookdatetime"));
				b.setBookpicture(rs.getString("bookpicture"));
		         list.add(b);
			}
		}
		
		return list;
		
	}

/*	@Test
	public void efg() throws Exception{
		int in=getCount();
		System.out.println(in);
	}*/
	@Override
	public int getCount() throws Exception {
		int result=0;
				String sql="select count(*) as num from book";
				ResultSet rs=executeQuery(sql);
				if(rs!=null){
					if(rs.next()){
						result=rs.getInt("num");
					}
				}
		return result;
	}

	
	
	
	
	
/*@Test
	public void gs() throws Exception{
		List<book> list=selectlikepage(null,1,0,3);
		System.out.println(list);
		for(book item:list){
			System.out.println(item.getBookname());
		}
	}*/
	@Override
	public List<book> selectlikepage(String bookname,int bookcategory,int pageindex,int pagesize) throws Exception {
		List<book> list=new ArrayList<book>();
		StringBuffer buffer=new StringBuffer(" select * from book where 1=1 ");
		if(bookname!=null){
			buffer.append(" and bookname like '%"+bookname+"%' ");
		}if(bookcategory!=0){
			buffer.append(" and bookcategory="+bookcategory+" ");
		}
		buffer.append(" limit ?,?");
		ResultSet rs=executeQuery(buffer.toString(),pageindex,pagesize);
		if(rs!=null){
			while(rs.next()){
				book bs=new book();
				bs.setBookid(rs.getInt("bookid"));
				bs.setBookname(rs.getString("bookname"));
				bs.setBookcategory(rs.getInt("bookcategory"));
				bs.setBoolprice(rs.getDouble("bookprice"));
				bs.setBookauthor(rs.getString("bookauthor"));
				bs.setBookdatetime(rs.getDate("bookdatetime"));
				bs.setBookpicture(rs.getString("bookpicture"));
				list.add(bs);
			}
		}
		
		return list;
	}
/*@Test
public void efg() throws Exception{
	int in=getCountlike(null,1);
	System.out.println(in);
}*/

@Override
public int  getCountlike(String bookname,int bookcategory)throws Exception {
    int result=0;
    StringBuffer buffer=new StringBuffer("select count(*) as num from book where 1=1 ");
    if(bookname!=null){
        buffer.append(" and bookname like '%"+bookname+"%' ");
    }
    if (bookcategory != 0) {
        buffer.append(" and bookcategory="+bookcategory+" ");
    }
    ResultSet rs=executeQuery(buffer.toString());
    if(rs!=null){
        if(rs.next()){
            result=rs.getInt("num");
        }
    }
    return  result;
}

@Test
public void gse() throws Exception{
	book boo=new book();
	boo.setBookname("哈哈111哈");

	boo.setBookcategory(1);
	boo.setBoolprice(123.2);
	boo.setBookauthor("2订单");
	boo.setBoomkabout("111111");
	boo.setBookdatetime(new Date());
	boo.setBookpicture("S:ede");
	int i=addbook(boo);
	if(i>0){
		System.out.println("ok");
	}else{
		System.out.println("out");
	}

}


	@Override
	//添加图书
	public int addbook(book b) throws Exception {
		String sql="insert into book(bookname,bookcategory,bookprice,bookauthor,bookabout,bookdatetime,bookpicture) value(?,?,?,?,?,?,?)";
		int count=executeUpdate(sql, b.getBookname(),b.getBookcategory(),b.getBoolprice(),b.getBookauthor(),b.getBoomkabout(),b.getBookdatetime(),b.getBookpicture());
		return count;
	}

	@Override
	public int delbook(int id) throws Exception {
		String sql="delete from book where bookid=?";
		int count=executeUpdate(sql, id);
		return count;
	}
/*@Test
public void de() throws Exception{
	int d=delbook(8);
	if(d>0){
		System.out.println("删除成功");
	}else{
		System.out.println("删除失败");
	}
}*/
	

@Test
	public void de() throws Exception{
		book d=new book();
		d.setBookauthor("eee");
		d.setBookcategory(1);
		d.setBookname("deg");
		d.setBoolprice(12.3);
		d.setBoomkabout("de");
		d.setBookdatetime(new Date());
		d.setBookpicture("Siedmf");
		d.setBookid(2);
		int ids=updatebook(d);
		if(ids>0){
			System.out.println("修改成功");
		}
	}
	
	/*@Test
	public void fe() throws Exception{
		List<book> list=selectbook(2);
		for(book item:list){
			System.out.println(item.getBookname());
		}
	}*/
	@Override
	public List<book> selectbook(int bookid) throws Exception {
		List<book> list=new ArrayList<book>();
		Stri