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

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

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

	Connection conn = DBUtil.getConnection();

	PreparedStatement ps = null;

	ResultSet rs = null;

	ArrayList al = new ArrayList();

	public ArrayList show() throws MyException {

		String sql = "select * from bookcategory";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

			while (rs.next()) {
				
				BookCategoryDTO bookCategory = new BookCategoryDTO();
				
				bookCategory.setId(rs.getInt(1));
				bookCategory.setCategory(rs.getString(2));

				al.add(bookCategory);
			}
		} catch (SQLException e) {
			throw new MyException("查询书籍类别数据库时失败！");
		}
		DBUtil.close();
		return al;

	}

	public void add(BaseDTO baseDTO) throws MyException {

		BookCategoryDTO bookcategory = (BookCategoryDTO) baseDTO;

		String sql = "insert into bookcategory(categoryName) values(?)";

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, bookcategory.getCategory());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new MyException("数据库添加图书类别失败！");
		}
		DBUtil.close();
	}

	public void delete(BaseDTO baseDTO) {

		BookCategoryDTO bookcategoryDTO = (BookCategoryDTO) baseDTO;

		conn = DBUtil.getConnection();
		String sql = "delete from bookcategory where id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, bookcategoryDTO.getId());
			ps.executeUpdate();
		} catch (SQLException e) {

		}
		DBUtil.close();
	}

	public int update(String category, BaseDTO baseDTO) throws MyException {
		BookCategoryDTO bookcategory = (BookCategoryDTO) baseDTO;

		String sql = "update book,bookcategory set book.category=?,bookcategory.categoryName=?"
				+ "where book.category=? and bookcategory.categoryName=?";
		int rs = 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, bookcategory.getCategory());
			ps.setString(2, bookcategory.getCategory());
			ps.setString(3, category);
			ps.setString(4, category);

			rs = ps.executeUpdate();
		} catch (SQLException e) {
			throw new MyException("数据库修改书籍类别时失败，修改书籍类别时相应的书籍表中类别也要修改！");
		}
		DBUtil.close();
		return rs;
	}
}