dao层持久层运用QueryRunner+C3P0实现对数据库的所有操作 增删改查 特殊条件查询

QueryRunner*

获取连接

C3P0Utils.getDataSource()

封装到QueryRunner中去，数据库对象

	QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());

查询所有

public List<Book> findAllBook() throws SQLException{
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		return qr.query("select * from j2ee_books", new BeanListHandler<Book>(Book.class));
	}

添加对象数据

public void addBook(Book book) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		qr.update("INSERT INTO j2ee_books VALUES(?,?,?,?,?,?,?,?,?)",
				book.getBook_id(),book.getBook_name(),book.getBook_type(),
				book.getAuthor() ,book.getPress(),book.getPublish_date(),
				book.getPrice(),book.getRegister_time(),book.getIs_borrow());
	}

通过ID查询数据 关键值

public Book findBookByid(String id) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		return qr.query("select * from j2ee_books where book_id=?", 
				new BeanHandler<Book>(Book.class),id);
	}

更新数据

public void updateBook(Book book) throws SQLException {
			QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
			qr.update("update j2ee_books set book_name=?,book_type=?, "
					+ "author=?,press=?,publish_date=?,price =?,"
					+ "register_time=?, is_borrow=?  where book_id=?", 
					book.getBook_name(),book.getBook_type(),
					book.getAuthor() ,book.getPress(),book.getPublish_date(),
					book.getPrice(),book.getRegister_time(),book.getIs_borrow(),book.getBook_id());
		
		
	}

删除数据对象 通过id

public void delBook(String id) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		qr.update("delete from j2ee_books where book_id =?", id);
	}

通过输入 来搜索数据对象 展示

	public List<Book> searchBook(String name) throws SQLException {
		// TODO Auto-generated method stub
		
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		return qr.query("select * from j2ee_books where book_name like concat('%',?,'%') or author like concat('%',?,'%') or press like concat('%',?,'%') or book_id like concat('%',?,'%') or price like concat('%',?,'%')", 
				new BeanListHandler<Book>(Book.class),name,name,name,name,name);
	}

返回某列数据 条件查询

ColumnListHandler

public List<String> findBorrowBookidList(int id) throws SQLException {
		// TODO Auto-generated method stub
		 QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		 List<String> list = qr.query("select book_id from j2ee_borrow where reader_id=? and is_return=0", 
				 new ColumnListHandler<String>(),id);
		 return list;
	}

删除第一行数据 条件
限制在第一行

limit 1

public void delBorrowbook(String huanBookid, int readerId) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		qr.update("update j2ee_borrow set is_return=1  where book_id =? and reader_id=? limit 1", huanBookid,readerId);
	}