customervoucherdao.java

来自「基于struts的网上商店源码」· Java 代码 · 共 104 行

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

public class CustomerVoucherDAO {
	private Connection conn;

	public CustomerVoucherDAO() {
		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, String end) {
		int count = 0;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM v_CustomerVoucher a WHERE a.CustomerID="
				+ LoginID + end;
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 获取店铺信息
	public ArrayList<Object[]> getStore(String LoginID) throws Exception {
		String sql = "select distinct a.StoreID,a.StoreName from v_CustomerVoucher a where a.CustomerID='"
				+ LoginID + "'";
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] o = new Object[2];
				o[0] = rs.getString(1);
				o[1] = rs.getString(2);
				al.add(o);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取优惠券信息
	public ArrayList<CustomerVoucherBean> getVoucher(String LoginID,
			String end, int currentPage, int pageSize) throws Exception {
		ArrayList<CustomerVoucherBean> al = new ArrayList<CustomerVoucherBean>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.VoucherName,a.StoreName,a.Description,a.Discount,a.Amount,a.UsedAmount FROM v_CustomerVoucher a WHERE a.CustomerID="
				+ LoginID + end + " AND a.[ID] NOT IN (" + "SELECT TOP "
				+ (currentPage - 1) * pageSize
				+ " [ID] FROM [v_CustomerVoucher] WHERE a.CustomerID="
				+ LoginID + end + ")";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerVoucherBean voucher = new CustomerVoucherBean();
				voucher.setVoucherName(rs.getString(1));
				voucher.setStoreName(rs.getString(2));
				voucher.setDescription(rs.getString(3));
				voucher.setScale(rs.getString(4));
				voucher.setAmount(rs.getString(5));
				voucher.setUsedAmount(rs.getString(6));
				al.add(voucher);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

}