merchantgoodsdao.java

基于struts的网上商店源码

/*
 * 作者:
 * 时间:2007年11月28日
 * 功能:库存销售管理-＞现有商品管理 
 * 查看管理现在商品的各种信息
 */
package com.mole.struts.dao;

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

import com.mole.struts.bean.GoodsTypeBean;
import com.mole.struts.bean.MerchantGoodsBean;

public class MerchantGoodsDAO extends AbstractDAO {

	public int getPageInfo(String storeID, int pageSize) {
		Connection conn = getConn();
		int count = 0;
		String sql = "SELECT COUNT(*) FROM [Goods] a,[GoodsType] b WHERE a.[GoodsType]=b.[ID] AND a.[StoreID]=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, storeID);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	public ArrayList<MerchantGoodsBean> queryGoods(String storeID,
			int currentPage, int pageSize) throws Exception {
		Connection conn = getConn();
		ResultSet rs = null;
		PreparedStatement ps = null;
		String desp = null;
		ArrayList<MerchantGoodsBean> al = new ArrayList<MerchantGoodsBean>();
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.[ID],a.[GoodsNumber],a.[Name],b.[Name],a.[Price],a.[Description],CONVERT(nchar(19),a.[LastUpdate],120),a.[Image],a.commend "
				+ "FROM [Goods] a,[GoodsType] b WHERE a.[GoodsType]=b.[ID] AND a.[StoreID]=? AND a.[ID] NOT IN ("
				+ "SELECT TOP "
				+ (currentPage - 1)
				* pageSize
				+ " a.[ID] FROM [Goods] a,[GoodsType] b WHERE a.[GoodsType]=b.[ID] AND a.[StoreID]=?)";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, storeID);
			ps.setObject(2, storeID);
			rs = ps.executeQuery();
			while (rs.next()) {
				desp = rs.getString(6) == null ? "无" : rs.getString(6);
				if (desp.length() > 15)
					desp = desp.substring(0, 12) + "...";
				MerchantGoodsBean merchantGoods = new MerchantGoodsBean();
				merchantGoods.setId(rs.getString(1));
				merchantGoods.setStoreId(storeID);
				merchantGoods.setGoodsNumber(rs.getString(2));
				merchantGoods.setName(rs.getString(3));
				merchantGoods.setGoodsType(rs.getString(4));
				merchantGoods.setPrice(rs.getString(5));
				merchantGoods.setDescription(desp);
				merchantGoods.setLastUpdate(rs.getString(7).substring(0, 10));
				merchantGoods.setImage(rs.getString(8));
				merchantGoods.setCommend(rs.getString(9));
				al.add(merchantGoods);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public MerchantGoodsBean queryGoods(String storeID) throws Exception {
		Connection conn = getConn();
		String desp = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		MerchantGoodsBean merchantGoods = new MerchantGoodsBean();
		String sql = "SELECT a.[ID],a.[Name],b.[Name],a.[Price],a.[Description],CONVERT(nchar(19),a.[LastUpdate],120),a.[Image],a.[GoodsNumber] ,a.commend "
				+ "FROM [Goods] a,[GoodsType] b WHERE a.[GoodsType]=b.[ID] AND a.[ID]=?";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, storeID);
			rs = ps.executeQuery();
			if (rs.next()) {
				desp = rs.getString(5) == null ? "无" : rs.getString(5);
				if (desp.length() > 15)
					desp = desp.substring(0, 12) + "...";
				merchantGoods.setId(rs.getString(1));
				merchantGoods.setStoreId(storeID);
				merchantGoods.setName(rs.getString(2));
				merchantGoods.setGoodsType(rs.getString(3));
				merchantGoods.setPrice(rs.getString(4));
				merchantGoods.setDescription(desp);
				merchantGoods.setLastUpdate(rs.getString(6));
				merchantGoods.setImage(rs.getString(7));
				merchantGoods.setGoodsNumber(rs.getString(8));
				merchantGoods.setCommend(rs.getString(9));
			}
			return merchantGoods;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public ArrayList<GoodsTypeBean> queryGoodsType(String storeID)
			throws Exception {
		Connection conn = getConn();
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<GoodsTypeBean> al = new ArrayList<GoodsTypeBean>();
		String sql = "SELECT [ID],[Name] "
				+ "FROM [GoodsType] WHERE [StoreID]=?";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, storeID);
			rs = ps.executeQuery();
			while (rs.next()) {
				GoodsTypeBean type = new GoodsTypeBean();
				type.setId(rs.getString(1));
				type.setName(rs.getString(2));
				al.add(type);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public void updateGoods(MerchantGoodsBean bean) throws Exception {
		Connection conn = getConn();
		PreparedStatement ps = null;
		String sql = "UPDATE [Goods] SET [GoodsNumber]=?,[Name]=?"
				+ ("".equals(bean.getGoodsType()) ? "" : ",[GoodsType]=?")
				+ ",[Price]=?,[Description]=?,[Image]=? ,commend=? WHERE [ID]=?";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setString(1, bean.getGoodsNumber());
			ps.setString(2, bean.getName());
			if (!"".equals(bean.getGoodsType())) {
				ps.setInt(3, Integer.parseInt(bean.getGoodsType()));
				ps.setFloat(4, Float.parseFloat(bean.getPrice()));
				ps.setString(5, bean.getDescription());
				ps.setString(6, bean.getImage());
				ps.setInt(7, Integer.parseInt(bean.getCommend()));
				ps.setInt(8, Integer.parseInt(bean.getId()));
			} else {
				ps.setFloat(3, Float.parseFloat(bean.getPrice()));
				ps.setString(4, bean.getDescription());
				ps.setString(5, bean.getImage());
				ps.setInt(6, Integer.parseInt(bean.getCommend()));
				ps.setInt(7, Integer.parseInt(bean.getId()));
			}
			ps.executeUpdate();
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public void insertGoods(MerchantGoodsBean bean) throws Exception {
		Connection conn = getConn();
		PreparedStatement ps = null;
		String sql = "INSERT INTO [Goods]([StoreID],[GoodsNumber],[Name],[GoodsType],[Price],[Description],commend) VALUES(?,?,?,?,?,?,?)";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setString(1, bean.getStoreId());
			ps.setString(2, bean.getGoodsNumber());
			ps.setString(3, bean.getName());
			ps.setInt(4, Integer.parseInt(bean.getGoodsType()));
			ps.setFloat(5, Float.parseFloat(bean.getPrice()));
			ps.setString(6, bean.getDescription());
			ps.setInt(7, Integer.parseInt(bean.getCommend()));
			ps.executeUpdate();
		} finally {
			if (ps != null)
				ps.close();
		}
	}
}