goodsdao.java

Java购物车及JSTL的应用

package org.qhit.li.store.dao;

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 org.qhit.li.store.dbmade.DBMade;
import org.qhit.li.store.dto.GoodsDTO;

public class GoodsDAO {
	private int pageSize = 6;

	private int pageNum = 0;

	private int size = 0;

	private int pageId = 0;

	/**
	 * 查询所有产品
	 * 
	 * @param pageid
	 * @return
	 */
	public ArrayList<GoodsDTO> selectBook(int pageid) {

		this.pageId = pageid;

		String sql = "select * from Goods";
		Connection con = null;
		Statement stm = null;
		ResultSet rs = null;
		ArrayList<GoodsDTO> goods = null;

		try {
			con = DBMade.getCon();
			stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			rs = DBMade.getRs(stm, sql);
			goods = paginationgoods(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, stm, con);
		}

		return goods;
	}

	/**
	 * 得到所有产品的规格
	 * 
	 * @return
	 */
	public ArrayList<String> getType() {
		ArrayList<String> type = new ArrayList<String>();
		String sql = "select distinct bSpec from Goods";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = DBMade.getCon();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String temp = rs.getString(1);
				type.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, ps, con);
		}
		return type;
	}
	
	
	/**
	 * 得到产品的编号
	 * @return
	 */
	public ArrayList<String> getpid(){
		ArrayList<String> btid = new ArrayList<String>();
		String sql = "select btid from Goods";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = DBMade.getCon();
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String temp = rs.getString(1);
				btid.add(temp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, ps, con);
		}
		return btid;
	}

	/**
	 * 产品规格
	 * 
	 * @param type
	 * @param pageid
	 * @return
	 */
	public ArrayList<GoodsDTO> selectByBtype(String type, int pageid) {
		this.pageId = pageid;
		String sql = "select * from Goods where bSpec=?";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<GoodsDTO> goods = null;

		try {
			con = DBMade.getCon();
			ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			ps.setString(1, type);
			rs = ps.executeQuery();
			goods = paginationgoods(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, ps, con);
		}

		return goods;
	}

	/**
	 * 显示单个产品
	 * 
	 * @param isbn
	 * @return
	 */
	public GoodsDTO selectOneBook(String tid) {
		String sql = "select * from Goods where bTid=?";
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		GoodsDTO good = new GoodsDTO();

		try {
			con = DBMade.getCon();
			ps = DBMade.getPds(con, sql);
			ps.setString(1, tid);
			rs = ps.executeQuery();
			if (rs.next()) {
				good.setBid(rs.getInt(1));
				good.setBtid(rs.getString(2));
				good.setBname(rs.getString(3));
				good.setBprice(rs.getDouble(4));
				good.setBfreight(rs.getDouble(5));
				good.setBpic(rs.getString(6));
				good.setBremark(rs.getString(7).trim());
				good.setBspec(rs.getString(8).trim());
				good.setBxremark(rs.getString(9).trim());
				good.setBnum(rs.getInt(10));
				good.setBdate(rs.getDate(11));
				good.setBabate(rs.getDouble(12));
				good.setBcommend(rs.getInt(13));
				good.setBopen(rs.getInt(14));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, ps, con);
		}

		return good;
	}

	/**
	 * 添加产品
	 * 
	 * @param book
	 * @return
	 */
	public int insertBook(GoodsDTO info) {
		int result = 0;

		String sql = "insert into Goods values(?,?,?,?,?,?,?,?,?,getdate(),?,?,?)";
		Connection con = null;
		PreparedStatement ps = null;

		try {
			con = DBMade.getCon();
			ps = DBMade.getPds(con, sql);
			ps.setString(1, info.getBtid());
			ps.setString(2, info.getBname());
			ps.setDouble(3, info.getBprice());
			ps.setDouble(4, info.getBfreight());
			ps.setString(5, info.getBpic());
			ps.setString(6, info.getBremark());
			ps.setString(7, info.getBspec());
			ps.setString(8, info.getBxremark());
			ps.setInt(9, info.getBnum());
			ps.setDouble(10, info.getBabate());
			ps.setInt(11, info.getBcommend());
			ps.setInt(12, info.getBopen());
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(null, ps, con);
		}
		return result;
	}

	/**
	 * 热门书籍
	 * 
	 * @return
	 */
	public ArrayList<GoodsDTO> selectPopBooks(int pageid) {
		this.pageId = pageid;
		String sql = "select * from V_popbooks";
		Connection con = null;
		Statement sta = null;
		ResultSet rs = null;
		ArrayList<GoodsDTO> goods = null;
		try {
			con = DBMade.getCon();
			sta = con.createStatement(1004, 1008);
			rs = DBMade.getRs(sta, sql);
			goods = paginationgoods(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, sta, con);
		}
		return goods;
	}

	/**
	 * 推荐产品
	 * 
	 * @return
	 */
	public ArrayList<GoodsDTO> selectCommend(int pageid) {
		this.pageId = pageid;
		String sql = "select * from Goods where bCommend=1 order by bDate desc";
		Connection con = null;
		Statement sta = null;
		ResultSet rs = null;
		ArrayList<GoodsDTO> goods = null;
		try {
			con = DBMade.getCon();
			sta = con.createStatement(1004, 1008);
			rs = DBMade.getRs(sta, sql);

			goods = paginationgoods(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, sta, con);
		}
		return goods;
	}

	/**
	 * 特价产品
	 * 
	 * @return
	 */
	public ArrayList<GoodsDTO> selectAbate(int pageid) {
		this.pageId = pageid;
		String sql = "select * from Goods where bAbate<1.0  order by bAbate asc";
		Connection con = null;
		Statement sta = null;
		ResultSet rs = null;
		ArrayList<GoodsDTO> goods = null;
		try {
			con = DBMade.getCon();
			sta = con.createStatement(1004, 1008);
			rs = DBMade.getRs(sta, sql);
			goods = paginationgoods(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(rs, sta, con);
		}
		return goods;
	}

	/**
	 * 修改产品信息
	 * 
	 * @param info
	 * @return
	 */
	public int updateGood(GoodsDTO info) {
		int result = 0;
		String sql = "update Goods set  bName=?,bPrice=?,bFreight=?,bPic=?,bRemark=?,bSpec=?,bXremark=?,bNum=?,bDate=getdate(),bAbate=?,bCommend=?,bOpen=? where bTid=?";
		String sql1 = "update Goods set bName=?,bPrice=?,bFreight=?,bRemark=?,bSpec=?,bXremark=?,bNum=?,bDate=getdate(),bAbate=?,bCommend=?,bOpen=? where bTid=?";
		Connection con = null;
		PreparedStatement ps = null;

		try {
			con = DBMade.getCon();
			if ("".equals(info.getBpic())) {
				ps = DBMade.getPds(con, sql1);
				ps.setString(1, info.getBname());
				ps.setDouble(2, info.getBprice());
				ps.setDouble(3, info.getBfreight());
				ps.setString(4, info.getBremark());
				ps.setString(5, info.getBspec());
				ps.setString(6, info.getBxremark());
				ps.setInt(7, info.getBnum());
				ps.setDouble(8, info.getBabate());
				ps.setDouble(9, info.getBcommend());
				ps.setInt(10, info.getBopen());
				ps.setString(11, info.getBtid());
			} else {
				ps = DBMade.getPds(con, sql);
				ps.setString(1, info.getBname());
				ps.setDouble(2, info.getBprice());
				ps.setDouble(3, info.getBfreight());
				ps.setString(4, info.getBpic());
				ps.setString(5, info.getBremark());
				ps.setString(6, info.getBspec());
				ps.setString(7, info.getBxremark());
				ps.setInt(8, info.getBnum());
				ps.setDouble(9, info.getBabate());
				ps.setDouble(10, info.getBcommend());
				ps.setInt(11, info.getBopen());
				ps.setString(12, info.getBtid());
			}
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(null, ps, con);
		}

		return result;
	}

	/**
	 * 删除产品
	 * 
	 * @param bookid
	 * @return
	 */
	public int deleteBook(String tid) {
		String sql = "delete Goods where bTid=?";
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		try {
			con = DBMade.getCon();
			ps = DBMade.getPds(con, sql);
			ps.setString(1, tid);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBMade.close(null, ps, con);
		}

		return result;
	}

	/**
	 * 分页
	 * 
	 * @param rs
	 * @return
	 * @throws Exception
	 */
	public ArrayList<GoodsDTO> paginationgoods(ResultSet rs) throws Exception {
		ArrayList<GoodsDTO> good = null;
		try {
			if (rs.next()) {
				good = new ArrayList<GoodsDTO>();

				// 得到所用行数
				rs.last();
				size = rs.getRow();

				pageNum = (size % pageSize == 0) ? (size / pageSize) : (size
						/ pageSize + 1);

				rs.beforeFirst();

				// 判断页数是否存在
				if (this.pageId == pageNum + 1) {
					this.pageId = 1;
				}

				if (this.pageId == 0) {
					this.pageId = pageNum;
				}

				// 下移游标
				for (int i = 0; i < (pageId - 1) * pageSize; i++) {
					rs.next();
				}

				// 循环取值
				for (int i = 0; i < pageSize; i++) {
					GoodsDTO tempgood = new GoodsDTO();
					if (!rs.next()) {
						break;
					}
					tempgood.setBid(rs.getInt(1));
					tempgood.setBtid(rs.getString(2));
					tempgood.setBname(rs.getString(3));
					tempgood.setBprice(rs.getDouble(4));
					tempgood.setBfreight(rs.getDouble(5));
					tempgood.setBpic(rs.getString(6));
					tempgood.setBremark(rs.getString(7));
					tempgood.setBspec(rs.getString(8));
					tempgood.setBxremark(rs.getString(9));
					tempgood.setBnum(rs.getInt(10));
					tempgood.setBdate(rs.getDate(11));
					tempgood.setBabate(rs.getDouble(12));
					tempgood.setBcommend(rs.getInt(13));
					tempgood.setBopen(rs.getInt(14));
					tempgood.setBprice(tempgood.getBprice()
							* tempgood.getBabate());
					good.add(tempgood);
				}
			}
		} catch (Exception e) {
			throw new Exception("操作出现异常");
		}
		return good;
	}

	public int getPageId() {
		return pageId;
	}

	public int getPageNum() {
		return pageNum;
	}

	public int getSize() {
		return size;
	}

}