bookdao.java

系统分为用户模块和管理员模块两大部分。 普通用户只能查看、搜索和购买图书。搜索有按书籍的类别、书籍名称、出版社名称、上架时间等条件的查询。购买方式有选择邮递和特快等方式。购物车的设计有记录某用户购买

/**
 * 
 */
package com.jstu.dao;

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 bookshop.base.BaseDTO;
import bookshop.tools.exception.MyException;

import com.jstu.dto.BookDTO;
import com.jstu.util.DBUtil;

/**
 * @author Administrator
 * 
 */
public class BookDAO {

	Connection conn = DBUtil.getConnection();

	PreparedStatement ps = null;

	ResultSet rs = null;

	List al=new ArrayList();

	public List show() throws MyException {

		String sql = "select * from book";

		try {

			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {

				BookDTO book=new BookDTO();
				
				book.setId(rs.getInt(1));
				book.setBookId(rs.getInt(2));
				book.setBookISBN(rs.getString(3));
				book.setBookName(rs.getString(4));
				
				book.setAuthor(rs.getString(5));
				book.setPublication(rs.getString(6));
				book.setPublicTime(rs.getDate(7));
				
				book.setCategory(rs.getString(8));
				book.setPrice(rs.getDouble(9));
				book.setVipPrice(rs.getDouble(10));
				book.setSave(rs.getDouble(11));
				book.setAmount(rs.getInt(12));
				book.setImage(rs.getString(13));
				book.setShelfTime(rs.getDate(14));

				al.add(book);
			}
		} catch (SQLException e) {
			throw new MyException("查询图书数据库时失败");
		}
		DBUtil.close();
		return al;
	}

	public void add(BaseDTO baseDTO) throws MyException {

		BookDTO book = (BookDTO) baseDTO;

		String sql = "insert into book"
				+ "(bookId,bookISBN,bookName,author,publication,publicTime,category,price,vipPrice,save,amount,image)"
				+ " values(?,?,?,?,?,?,?,?,?,?,?,?)";

		System.out.println(sql);
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,book.getBookId());
			ps.setString(2, book.getBookISBN());
			ps.setString(3,book.getBookName());
			
			ps.setString(4, book.getAuthor());
			ps.setString(5, book.getPublication());
			ps.setDate(6, book.getPublicTime());
			
			ps.setString(7, book.getCategory());
			ps.setDouble(8, book.getPrice());
			ps.setDouble(9, book.getVipPrice());
			ps.setDouble(10, book.getSave());
			ps.setInt(11, book.getAmount());
			ps.setString(12, book.getImage());

			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new MyException("数据库添加图书信息时失败！");
		}
		DBUtil.close();
	}

	public void delete(BaseDTO baseDTO) throws MyException {

		BookDTO book = (BookDTO) baseDTO;
		String sql = "delete from book where id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, book.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new MyException("数据库删除图书信息时失败！");
		}
		DBUtil.close();
	}

	public void update(BaseDTO baseDTO) throws MyException {

		BookDTO book = (BookDTO) baseDTO;
		String sql = "update book set bookId=?,bookISBN=?,bookName=?,author=?,publication=?,publicTime=?,category=?,price=?,vipPrice=?,save=?,amount=?,image=? where id=?";

		try {
			ps = conn.prepareStatement(sql);

			ps.setInt(1, book.getBookId());
			ps.setString(2, book.getBookISBN());
			ps.setString(3, book.getBookName());
			ps.setString(4, book.getAuthor());
			ps.setString(5, book.getPublication());
			ps.setDate(6, book.getPublicTime());
			ps.setString(7, book.getCategory());
			ps.setDouble(8, book.getPrice());
			
			ps.setDouble(9,book.getVipPrice());
			ps.setDouble(10, book.getSave());
			ps.setInt(11, book.getAmount());
			ps.setString(12, book.getImage());
			
			ps.setInt(13, book.getId());
			
		} catch (SQLException e) {

			throw new MyException("数据库修改图书信息时失败!");
		}
		DBUtil.close();
	}

	public List search(String search, String category) throws MyException {
		
		String sql = "select * from book where "+category+" like %"+search+"% order by id desc";
		
		try {

			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {	
		
				BookDTO book =new BookDTO();
				book.setId(rs.getInt(1));
				book.setBookId(2);
				book.setBookISBN(rs.getString(3));
				book.setBookName(rs.getString(4));
				book.setAuthor(rs.getString(5));
				book.setPublication(rs.getString(6));
				book.setPublicTime(rs.getDate(7));
				
				book.setCategory(rs.getString(8));
				book.setPrice(rs.getDouble(9));
				book.setVipPrice(rs.getDouble(10));
				book.setSave(rs.getDouble(11));
				book.setAmount(rs.getInt(12));
				book.setImage(rs.getString(13));
				book.setShelfTime(rs.getDate(14));
				
				al.add(book);
			}
		} catch (SQLException e) {
			throw new MyException("按条件搜索图书时出错！");
		}
		DBUtil.close();
		return al;
	}
}