merchantcardviewdao.java

基于struts的网上商店源码

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.MerchantCardViewBean;

public class MerchantCardViewDAO extends AbstractDAO {
	private int pageSize;

	public int getPageInfo(String storeID, int pageSize) {
		Connection conn = getConn();
		int count = 0;
		this.pageSize = pageSize;
		String sql = "SELECT COUNT(*) FROM v_merchantCustomerCardInfo WHERE [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<MerchantCardViewBean> queryCardView(String storeID,
			int currentPage) throws Exception {
		Connection conn = getConn();
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT TOP "
				+ pageSize
				+ " [ID],[CustomerID],[OriginID],[State],[GrantDate],[loginname] "
				+ "FROM v_merchantCustomerCardInfo "
				+ " WHERE [StoreID]=? and [ID] NOT IN (SELECT TOP "
				+ (currentPage - 1) * pageSize + " [ID] FROM "
				+ "v_merchantCustomerCardInfo WHERE [StoreID]=?)";

		ArrayList<MerchantCardViewBean> arrayList = new ArrayList<MerchantCardViewBean>();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, storeID);
			ps.setObject(2, storeID);
			rs = ps.executeQuery();

			int i = 0;
			while (rs.next()) {
				MerchantCardViewBean MerchantCardView = new MerchantCardViewBean();
				MerchantCardView.setStoreId(storeID);
				MerchantCardView.setId(rs.getString(1));
				MerchantCardView.setCustomerId(rs.getString(2));
				MerchantCardView.setOriginId(rs.getString(3));
				MerchantCardView.setState(rs.getString(4));
				String str = rs.getString(5);
				if (str != null && str.length() >= 10)
					MerchantCardView.setGrantData(str.substring(0, 10));
				else
					MerchantCardView.setGrantData(str);
				MerchantCardView.setCustomerName(rs.getString(6));

				arrayList.add(MerchantCardView);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return arrayList;
	}
}