customershoppeddao.java

基于struts的网上商店源码

package com.mole.struts.dao;

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

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

import com.mole.struts.bean.MerchantStoreInfoBean;

public class CustomerShoppedDAO {
	private Connection conn;

	public CustomerShoppedDAO() {
		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 getCount(String LoginID) {
		int count = 0;
		ResultSet rs = null;
		String sql = "SELECT COUNT(DISTINCT [StoreID]) FROM [SwiftNumber] WHERE [CustomerID]="
				+ LoginID;
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 获取是否公开消费过的店铺
	public String getPublic(String cid) {
		String result = "";
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT [ShowShopped] FROM [Customer] WHERE [ID]=" + cid;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next())
				result = rs.getString(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	// 获取消费过的店铺
	public ArrayList<MerchantStoreInfoBean> getShopStore(String LoginID,
			int currentPage, int pageSize) throws Exception {
		ArrayList<MerchantStoreInfoBean> al = new ArrayList<MerchantStoreInfoBean>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT DISTINCT TOP "
				+ pageSize
				+ " a.[ID],a.[Name],a.[Image] FROM [Store] a,[SwiftNumber] b WHERE a.[ID]=b.[StoreID] AND b.[CustomerID]="
				+ LoginID
				+ " AND a.[ID] NOT IN (SELECT DISTINCT TOP "
				+ (currentPage - 1)
				* pageSize
				+ " a.[ID] FROM [Store] a,[SwiftNumber] b WHERE a.[ID]=b.[StoreID] AND b.[CustomerID]="
				+ LoginID + " ORDER BY a.[ID] DESC) ORDER BY a.[ID] DESC";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				MerchantStoreInfoBean bean = new MerchantStoreInfoBean();
				bean.setStoreID(rs.getString(1));
				bean.setStoreName(rs.getString(2));
				bean.setStoreImage(rs.getString(3));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

}