customerrecorddao.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.CustomerTradeRecordBean;

public class CustomerRecordDAO {
	private Connection conn;
	private int pageSize;

	public CustomerRecordDAO() {
		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 cid, String end) {
		int count = 0;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM [v_CustomerRecord] a WHERE a.[customerId]="
				+ cid + end;
		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 [ShowRecord] 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<Object[]> getStoreInfo(String ID) throws Exception {
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select distinct a.[storeId],a.[storename] from [v_CustomerRecord] a where a.[customerId]="
				+ ID;
		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<Object[]> getRecord(String cid, String end,
			int currentPage, int pageSize) throws Exception {
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.[Id],a.[StoreName],a.[NominalPrice],a.[totalPrice],convert(char(20),a.[DealTime],120) FROM [v_CustomerRecord] a WHERE a.[CustomerID]="
				+ cid + end + " AND a.[ID] NOT IN (" + "SELECT TOP "
				+ (currentPage - 1) * pageSize
				+ " [ID] FROM [v_CustomerRecord] WHERE a.[CustomerID]=" + cid
				+ end + ")";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] o = new Object[5];
				o[0] = rs.getString(1);
				o[1] = rs.getString(2);
				o[2] = rs.getString(3);
				o[3] = rs.getString(4);
				o[4] = rs.getString(5);
				al.add(o);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 添加消费评论
	public void addComment(String customerId, String swiftId, String comment,
			String point) {
		PreparedStatement ps = null;
		try {
			conn.setAutoCommit(true);
			String sql = "insert into RecordComment(CustomerID,swiftID,Grade,CommentContext)values ('"
					+ customerId
					+ "','"
					+ swiftId
					+ "','"
					+ point
					+ "','"
					+ comment + "')";
			ps = conn.prepareStatement(sql);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				ps.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 获取分页信息
	public int adminGetPageInfo(String mer, String area, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "";
		if (mer == null && area == null) {
			sql = "select count(*) from [v_CustomerRecord]";
		} else if (mer != null && area == null) {
			sql = "select count(*) from [v_CustomerRecord] where MerchantName like '%"
					+ mer + "%'";
		} else if (mer == null && area != null) {
			sql = "select count(*) from [v_CustomerRecord] where AreaName like '%"
					+ area + "%'";
		} else if (mer != null && area != null) {
			sql = "select count(*) from [v_CustomerRecord] where MerchantName like '%"
					+ mer + "%' and AreaName '%" + area + "%'";
		}
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 获取用户记录
	public ArrayList adminGetCustomerRecord(String mer, String area,
			int currentPage, int pageSize) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList al = new ArrayList();
		String sql = "";
		if (mer == null && area == null) {
			sql = "select top  "
					+ pageSize
					+ " [customerName],[CardID],[MerchantName],[GoodPrice],[DealTime],[GoodName] from [v_CustomerRecord] WHERE ID NOT IN (select TOP "
					+ (currentPage - 1) * pageSize
					+ " [ID] FROM [v_CustomerRecord])";
		} else if (mer != null && area == null) {
			sql = "select top  "
					+ pageSize
					+ " [customerName],[CardID],[MerchantName],[GoodPrice],[DealTime],[GoodName] from [v_CustomerRecord] where MerchantName like '%"
					+ mer
					+ "%' and ID not in(select TOP "
					+ (currentPage - 1)
					* pageSize
					+ " [ID] FROM [v_CustomerRecord] where MerchantName like '%"
					+ mer + "%')";
		} else if (mer == null && area != null) {
			sql = "select top  "
					+ pageSize
					+ " [customerName],[CardID],[MerchantName],[GoodPrice],[DealTime],[GoodName] from [v_CustomerRecord] where AreaName like '%"
					+ area + "%' and ID not in(select TOP " + (currentPage - 1)
					* pageSize
					+ " [ID] FROM [v_CustomerRecord] where AreaName like '%"
					+ area + "%')";
		} else if (mer != null && area != null) {
			sql = "select top  "
					+ pageSize
					+ " [customerName],[CardID],[MerchantName],[GoodPrice],[DealTime],[GoodName] from [v_CustomerRecord] where MerchantName like '%"
					+ mer
					+ "%' and AreaName '%"
					+ area
					+ "%' and ID not in(select TOP "
					+ (currentPage - 1)
					* pageSize
					+ " [ID] FROM [v_CustomerRecord] where MerchantName like '%"
					+ mer + "%' and AreaName '%" + area + "%')";
		}
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			System.out.println(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerTradeRecordBean ctr = new CustomerTradeRecordBean();

				ctr.setCustomerName(rs.getString(1));
				ctr.setCardID(rs.getString(2));
				ctr.setMerchantName(rs.getString(3));
				ctr.setPurchasePrice(rs.getString(4));
				ctr.setPurchaseDate(rs.getString(5));
				ctr.setGoodName(rs.getString(6));

				al.add(ctr);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

}