dbbook.java

本系统实现了网上书店功能

package com.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Vector;

import javax.sql.DataSource;

import com.bean.Book;



public class DBBook {
	private DataSource ds;
	public DBBook(DataSource dataSource){
		this.ds= dataSource;
	}
	
	/***************
	 * 
	 * @return
	 * @throws SQLException
	 * @throws ParseException
	 * @author Administrator
	 * @param
	 * @version 1.1
	 * @time 2006-12-08 
	 ***************/
	public Vector getAllBook() throws SQLException, ParseException{
		Connection con = ds.getConnection();
		String presql="select * from book";
		PreparedStatement pstmt = con.prepareStatement(presql);
		ResultSet rs = pstmt.executeQuery();
		Vector bookVector = new Vector();
		while(rs.next()){
			Book book = new Book();
			int id_sql = rs.getInt("id");
			String bookname_sql=rs.getString("bookname");
			String author_sql = rs.getString("author");
			String isbn_sql=rs.getString("isbn");
			String content_sql=rs.getString("content");
			String publishtime_sql=rs.getString("publishtime");
			float price_sql=rs.getFloat("price");
			int bookClassID_sql = rs.getInt("bookcatalog");
			book.setBookID(id_sql);
			book.setBookName(bookname_sql);
			book.setAuthor(author_sql);
			book.setIsbn(isbn_sql);
			book.setContent(content_sql);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
			book.setPublishtime(sdf.parse(publishtime_sql));
			book.setPrice(price_sql);
			book.setBookClassID(bookClassID_sql);
			bookVector.add(book);			
		}
		con.close();
		return bookVector;
	}
	public Vector searchBook(String key,String field) throws Exception{
		Connection con = ds.getConnection();		
		String presql="select * from book where bookname like '%"+key+"%'";
		if(field.equals("2")){
			presql="select * from book where author like '%"+key+"%'";
		}
		
		PreparedStatement pstmt = con.prepareStatement(presql);
		//pstmt.setString(1,key);
		ResultSet rs = pstmt.executeQuery();
		Vector bookVector = new Vector();
		while(rs.next()){
			Book book = new Book();
			int id_sql = rs.getInt("id");
			String bookname_sql=rs.getString("bookname");
			String author_sql = rs.getString("author");
			String isbn_sql=rs.getString("isbn");
			String content_sql=rs.getString("content");
			String publishtime_sql=rs.getString("publishtime");
			float price_sql=rs.getFloat("price");
			int bookClassID_sql = rs.getInt("bookcatalog");
			book.setBookID(id_sql);
			book.setBookName(bookname_sql);
			book.setAuthor(author_sql);
			book.setIsbn(isbn_sql);
			book.setContent(content_sql);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
			book.setPublishtime(sdf.parse(publishtime_sql));
			book.setPrice(price_sql);
			book.setBookClassID(bookClassID_sql);
			bookVector.add(book);			
		}
		con.close();
		return bookVector;
	}
	public Vector searchBookClass(String bookCatId) throws Exception{
		Connection con = ds.getConnection();		
		String presql="select * from book where bookcatalog=?";
		
		PreparedStatement pstmt = con.prepareStatement(presql);
		pstmt.setString(1,bookCatId);
		ResultSet rs = pstmt.executeQuery();
		Vector bookVector = new Vector();
		while(rs.next()){
			Book book = new Book();
			int id_sql = rs.getInt("id");
			String bookname_sql=rs.getString("bookname");
			String author_sql = rs.getString("author");
			String isbn_sql=rs.getString("isbn");
			String content_sql=rs.getString("content");
			String publishtime_sql=rs.getString("publishtime");
			float price_sql=rs.getFloat("price");
			int bookClassID_sql = rs.getInt("bookcatalog");
			book.setBookID(id_sql);
			book.setBookName(bookname_sql);
			book.setAuthor(author_sql);
			book.setIsbn(isbn_sql);
			book.setContent(content_sql);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
			book.setPublishtime(sdf.parse(publishtime_sql));
			book.setPrice(price_sql);
			book.setBookClassID(bookClassID_sql);
			bookVector.add(book);			
		}
		con.close();
		return bookVector;
	}
	
	public Book getBookDetail(String bookId) throws Exception{
		Connection con = ds.getConnection();		
		String presql="select * from book where id=?";
		
		PreparedStatement pstmt = con.prepareStatement(presql);
		pstmt.setString(1,bookId);
		ResultSet rs = pstmt.executeQuery();
		Book book = new Book();
		if(rs.next()){			
			int id_sql = rs.getInt("id");
			String bookname_sql=rs.getString("bookname");
			String author_sql = rs.getString("author");
			String isbn_sql=rs.getString("isbn");
			String content_sql=rs.getString("content");
			String publishtime_sql=rs.getString("publishtime");
			float price_sql=rs.getFloat("price");
			int bookClassID_sql = rs.getInt("bookcatalog");
			book.setBookID(id_sql);
			book.setBookName(bookname_sql);
			book.setAuthor(author_sql);
			book.setIsbn(isbn_sql);
			book.setContent(content_sql);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
			book.setPublishtime(sdf.parse(publishtime_sql));
			book.setPrice(price_sql);
			book.setBookClassID(bookClassID_sql);					
		}
		con.close();
		return book;
	}

}