listdao.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.ListDTO;
import com.jstu.util.DBUtil;

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

	Connection conn = DBUtil.getConnection();

	PreparedStatement ps = null;

	ResultSet rs = null;

	ArrayList al = new ArrayList();

	public ArrayList show() throws MyException {

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

			while (rs.next()) {
				ListDTO list = new ListDTO();

				list.setAmount(rs.getInt(7));
				list.setBookId(rs.getInt(3));
				list.setBookName(rs.getString(4));
				list.setId(rs.getInt(1));
				list.setPrice(rs.getInt(5));
				list.setUserId(rs.getInt(2));
				list.setVipPrice(rs.getDouble(6));

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

	public void add(BaseDTO baseDTO) throws MyException {

		ListDTO list = (ListDTO) baseDTO;

		String sql = "insert into list(userId,bookId,bookName,price,vipPrice,amount) values(?,?,?,?,?,?)";

		try {
			ps = conn.prepareStatement(sql);

			ps.setInt(1, list.getUserId());
			ps.setInt(2, list.getBookId());
			ps.setString(3, list.getBookName());
			ps.setDouble(4, list.getPrice());
			ps.setDouble(5, list.getVipPrice());
			ps.setInt(6, list.getAmount());

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

	public void delete(BaseDTO baseDTO) {

		ListDTO list = (ListDTO) baseDTO;

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

		}
		DBUtil.close();
	}

}