bookdao.java

servlet实现网上书店查询

package com.ghy.bookstore.book.dao;

import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


import com.ghy.base.dao.BaseDao;
import com.ghy.bookstore.book.model.BookCat;
import com.ghy.bookstore.book.model.BookVO;

public class BookDao extends BaseDao implements Serializable {
	// BOOKCAT 表
	public String findFirstCatId() throws IOException, InstantiationException,
			IllegalAccessException, ClassNotFoundException, SQLException {
		Connection conn = this.getDatamanager().getConnection();
		String sql = "select * from BOOKCAT";
		PreparedStatement statement = conn.prepareStatement(sql);
		ResultSet rt = statement.executeQuery();
		BookCat bookcat = new BookCat();
		if (rt.next()) {
			bookcat.setId(rt.getString("ID"));
			bookcat.setName(rt.getString("NAME"));
		}
		getDatamanager().closeDBConnection();
		return bookcat.getId();
	}

	// book表
	public int getBookCount(int mode, String catId, String field, String key)
			throws IOException, InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Connection conn = this.getDatamanager().getConnection();
		String sql = null;
		String listValues = null;
		int k = 0;
		if (mode == 1) {
			sql = "select count(*) from book where CATEGORY='" + catId + "'";
		} else {
			if (field.equals("1"))
				listValues = "name";
			else if (field.equals("2"))
				listValues = "author";
			sql = "select count(*) from book where " + listValues + " like '%"
					+ key + "%' order by id";
		}
		PreparedStatement statement = conn.prepareStatement(sql);
		ResultSet rt = statement.executeQuery();
		if (rt.next()) {
			k = rt.getInt(1);
		}
		getDatamanager().closeDBConnection();
		return k;

	}

	public ArrayList<BookCat> findAllBookCat() throws IOException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Connection conn = this.getDatamanager().getConnection();
		String sql = "select * from BOOKCAT";
		PreparedStatement statement = conn.prepareStatement(sql);
		ResultSet rt = statement.executeQuery();
		ArrayList<BookCat> list = new ArrayList<BookCat>();
		while (rt.next()) {
			BookCat bookcat = new BookCat();
			bookcat.setId(rt.getString("ID"));
			bookcat.setName(rt.getString("NAME"));
			list.add(bookcat);
		}
		getDatamanager().closeDBConnection();
		return list;
	}

	// book表
	public ArrayList<BookVO> findBookWithCat(int i, String catId, String key,
			String field) throws SQLException, IOException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		Connection conn = this.getDatamanager().getConnection();
		String sql = null;
		String listValues = null;
		ArrayList<BookVO> list = new ArrayList<BookVO>();
		BookVO book;
		if (i == 1) {
			sql = "select * from BOOK where CATEGORY='" + catId + "'";
		} else {
			if (field.equals("1"))
				listValues = "name";
			else if (field.equals("2"))
				listValues = "author";
			sql = "select * from BOOK where " + listValues + " like '%" + key
					+ "%' order by ID";
		}
		PreparedStatement statement = conn.prepareStatement(sql);
		ResultSet rt = statement.executeQuery();
		while (rt.next()) {
			book = new BookVO();
			book.setId(rt.getString("id"));
			book.setName(rt.getString("name"));
			book.setNumber(rt.getString("num"));
			book.setAuthor(rt.getString("author"));
			book.setPublisher(rt.getString("publisher"));
			book.setPrice(rt.getDouble("price"));
			book.setCategrory(rt.getString("category"));
			book.setDescription(rt.getString("description"));
			list.add(book);

		}
		return list;
	}
	public BookVO findBookByID(String id) throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
	{
		Connection conn=this.getDatamanager().getConnection();
		String sql="select * from book where id='"+id+"'";
		BookVO book=null;
		PreparedStatement statement=conn.prepareStatement(sql);
		ResultSet rt=statement.executeQuery();
		if(rt.next())
		{
			book=new BookVO();
			book.setId(rt.getString("id"));
			book.setName(rt.getString("name"));
			book.setNumber(rt.getString("num"));
			book.setAuthor(rt.getString("author"));
			book.setPublisher(rt.getString("publisher"));
			book.setPrice(rt.getDouble("price"));
			book.setCategrory(rt.getString("category"));
			book.setDescription(rt.getString("description"));
			}
		return book;
	}
	public ArrayList findAllBooks() throws IOException, InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException
	{
		Connection conn=this.getDatamanager().getConnection();
		String sql="select * from book ";
		Statement statement = conn.createStatement();
		ResultSet rt = statement.executeQuery(sql);
		ArrayList<BookVO> list = new ArrayList<BookVO>();
		BookVO book =null;
		while (rt.next()) {
			book = new BookVO();
			book.setId(rt.getString("id"));
			book.setName(rt.getString("name"));
			book.setNumber(rt.getString("num"));
			book.setAuthor(rt.getString("author"));
			book.setPublisher(rt.getString("publisher"));
			book.setPrice(rt.getDouble("price"));
			book.setCategrory(rt.getString("category"));
			book.setDescription(rt.getString("description"));
			list.add(book);
		}
		return list;
	}
}