merchantstoregetgoodsinfodao.java

基于struts的网上商店源码

//张建
package com.mole.struts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.mole.struts.bean.MerchantStoreGoodsInfoBean;

public class MerchantStoreGetGoodsInfoDAO {
	private Connection conn;

	// 商家店铺查询商品信息
	public MerchantStoreGetGoodsInfoDAO() {
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Failed to initial context!");
			DataSource ds = (DataSource) ctx
					.lookup("java:comp/env/jdbc/crmdata");
			conn = ds.getConnection();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 查询商品数量
	public int getGoodsCount(String storeID, String name, String goodsType,
			String priceMin, String priceMax) {
		int count = 0;
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT COUNT(*) FROM [v_StoreGoods] WHERE StoreID=?";
		if (name != null) {
			sql += " and Name like '%" + name + "%'";
		}
		if (null != goodsType) {
			sql += " and GoodsTypeID='" + goodsType + "'";
		}
		if (null != priceMin && null == priceMax) {
			sql += " and Price >= " + Integer.parseInt(priceMin) + "";
		} else if (null == priceMin && null != priceMax) {
			sql += " and Price <= " + Integer.parseInt(priceMax) + "";
		} else if (null != priceMin && null != priceMax) {
			sql += " and Price between " + Integer.parseInt(priceMin) + "and "
					+ Integer.parseInt(priceMax) + "";
		}
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, storeID);
			rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 获取货物
	public ArrayList<MerchantStoreGoodsInfoBean> getGoods(String storeID,
			String name, String goodsType, String priceMin, String priceMax,
			int currentPage, int pageSize) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String temp = "";
		if (null != name) {
			temp += " and Name like '%" + name + "%'";
		}
		if (null != goodsType) {
			temp += " and GoodsTypeID='" + goodsType + "'";
		}
		if (null != priceMin && null == priceMax) {
			temp += " and Price >= " + Integer.parseInt(priceMin) + "";
		} else if (null == priceMin && null != priceMax) {
			temp += " and Price <= " + Integer.parseInt(priceMax) + "";
		} else if (null != priceMin && null != priceMax) {
			temp += " and Price between " + Integer.parseInt(priceMin) + "and "
					+ Integer.parseInt(priceMax) + "";
		}
		String sql = "select top "
				+ pageSize
				+ " ID,StoreID,GoodsTypeID,Name,StoreName,GoodsTypeName,Price,Description,LastUpdate,Amount,LastStockAmount,TotalAmount,GoodsNumber,Image from v_StoreGoods "
				+ "where StoreID=?"
				+ temp
				+ " and ID not in (select top "
				+ (currentPage - 1)
				* pageSize
				+ " ID from v_StoreGoods where StoreID=? order by LastUpdate desc) order by LastUpdate desc";
		ArrayList<MerchantStoreGoodsInfoBean> al = new ArrayList<MerchantStoreGoodsInfoBean>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, storeID);
			ps.setObject(2, storeID);

			rs = ps.executeQuery();
			while (rs.next()) {
				MerchantStoreGoodsInfoBean bean = new MerchantStoreGoodsInfoBean();
				bean.setID(rs.getString(1));
				bean.setStoreID(rs.getString(2));
				bean.setGoodsTypeID(rs.getString(3));
				bean.setGoodsname(rs.getString(4));
				bean.setStoreName(rs.getString(5));
				bean.setGoodsTypeName(rs.getString(6));
				bean.setGoodsPrice(rs.getString(7));
				bean.setGoodsDesciption(rs.getString(8));
				bean.setGoodsLastUpdate(rs.getString(9));
				bean.setGoodsAmount(rs.getString(10));
				bean.setGoodsLastStockAmount(rs.getString(11));
				bean.setGoodsTotalAmount(rs.getString(12));
				bean.setGoodsNumber(rs.getString(13));
				bean.setGoodsImage(rs.getString(14));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}
}