customergrouplistdao.java

基于struts的网上商店源码

/*
 * 作者:李斌
 * 时间:2007年11月24日
 * 功能:创建用户群
 */
package com.mole.struts.dao;

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

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

import com.mole.struts.bean.CustomerGroupListBean;

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

	public CustomerGroupListDAO() {
		System.out.println("Data source init...");
		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();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 获取用户风格
	public String getStyle(String cid) {
		String style = "";
		String sql = "SELECT [BlogStyle] FROM [Customer] WHERE [ID]=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, cid);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				style = rs.getString(1).trim();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return style;
	}

	// 所有群数量(平台管理员)
	public int getAllPageInfo(int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "select count(*) from [CustomerGroup]";
		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 int getPageInfo(int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "select count(*) from [CustomerGroup] where [State]=0";
		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 int getPageInfo(String user, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "select count(*) from [CustomerGroup] "
				+ "where [State]=0 and [ID] in(select [GroupID] from [CustomerGroupMember] where [ID]="
				+ user + ")";
		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 int getPageInfo(String key, int type, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql;
		if (type == 0)
			sql = "select count(*) from [v_CustomerGroupDetail] a where a.[State]=0 and a.[Name] like '%"
					+ key + "%'";
		else
			sql = "select count(*) from [v_CustomerGroupDetail] a where a.[State]=0 and a.[KeyWord] like '%"
					+ key + "%'";
		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 Hashtable<String, CustomerGroupListBean> queryGroups(int currentPage)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String desp = null;
		Hashtable<String, CustomerGroupListBean> hash = new Hashtable<String, CustomerGroupListBean>();
		String sql = "select top "
				+ pageSize
				+ " a.[ID],a.[Name],a.[KeyWord],a.[Description],a.[GroupImage],a.[GroupMemberNum],a.[HostID],a.[HostName]"
				+ " from [v_CustomerGroupDetail] a where a.[State]=0 and a.[ID] not in(select top "
				+ (currentPage - 1) * pageSize
				+ " a.[ID] from [v_CustomerGroupDetail] a where a.[State]=0)";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				desp = rs.getString(4) == null ? "无" : rs.getString(4);
				if (desp.length() > 15)
					desp = desp.substring(0, 12) + "...";
				CustomerGroupListBean customerGroups = new CustomerGroupListBean();
				customerGroups.setGroupID(rs.getString(1));
				customerGroups.setGroupName(rs.getString(2));
				customerGroups.setGroupKeyWord(rs.getString(3));
				customerGroups.setGroupDesc(desp);
				customerGroups.setGroupImage(rs.getString(5));
				customerGroups.setGroupMemberNum(rs.getString(6));
				customerGroups.setGroupCreatorID(rs.getString(7));
				customerGroups.setGroupCreator(rs.getString(8));
				hash.put(rs.getString(1), customerGroups);
			}
			return hash;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 查找所有群列表的一页(平台管理员)
	public ArrayList<CustomerGroupListBean> queryAllGroups(int currentPage)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<CustomerGroupListBean> list = new ArrayList<CustomerGroupListBean>();
		String sql = "select top "
				+ pageSize
				+ " a.[ID],a.[Name],a.[HostName],convert(char(20),a.[CreateDate],120),a.[GroupMemberNum],a.[KeyWord],a.[HostID],a.[State]"
				+ " from [v_CustomerGroupDetail] a where a.[ID] not in(select top "
				+ (currentPage - 1) * pageSize
				+ " a.[ID] from [v_CustomerGroupDetail] a)";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerGroupListBean groups = new CustomerGroupListBean();
				groups.setGroupID(rs.getString(1));
				groups.setGroupName(rs.getString(2));
				groups.setGroupCreator(rs.getString(3));
				groups.setGroupCreateDate(rs.getString(4));
				groups.setGroupMemberNum(rs.getString(5));
				groups.setGroupKeyWord(rs.getString(6));
				groups.setGroupCreatorID(rs.getString(7));
				groups.setGroupState(rs.getString(8));
				list.add(groups);
			}
			return list;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 查找我加入的群列表的一页
	public Hashtable<String, CustomerGroupListBean> queryGroups(String user,
			int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String desp = null;
		Hashtable<String, CustomerGroupListBean> hash = new Hashtable<String, CustomerGroupListBean>();
		String sql = "select top "
				+ pageSize
				+ " a.[ID],a.[Name],a.[KeyWord],a.[Description],a.[GroupImage],a.[GroupMemberNum],a.[HostID],a.[HostName] from [v_CustomerGroupDetail] a"
				+ " where a.[State]=0 and a.[ID] in(select c.[GroupID] from [CustomerGroupMember] c where c.[ID]="
				+ user
				+ ") and a.[ID] not in(select top "
				+ (currentPage - 1)
				* pageSize
				+ " a.[ID] from "
				+ "[v_CustomerGroupDetail] a where a.[State]=0 and a.[ID] in(select c.[GroupID] from [CustomerGroupMember] c where c.[ID]="
				+ user + "))";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				desp = rs.getString(4) == null ? "无" : rs.getString(4);
				if (desp.length() > 15)
					desp = desp.substring(0, 12) + "...";
				CustomerGroupListBean customerGroups = new CustomerGroupListBean();
				customerGroups.setGroupID(rs.getString(1));
				customerGroups.setGroupName(rs.getString(2));
				customerGroups.setGroupKeyWord(rs.getString(3));
				customerGroups.setGroupDesc(desp);
				customerGroups.setGroupImage(rs.getString(5));
				customerGroups.setGroupMemberNum(rs.getString(6));
				customerGroups.setGroupCreatorID(rs.getString(7));
				customerGroups.setGroupCreator(rs.getString(8));
				hash.put(rs.getString(1), customerGroups);
			}
			return hash;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 查找按群名称或主题查找的群列表的一页
	public Hashtable<String, CustomerGroupListBean> queryGroups(String key,
			int type, int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String desp = null;
		String sql;
		Hashtable<String, CustomerGroupListBean> hash = new Hashtable<String, CustomerGroupListBean>();
		if (type == 0) {
			sql = "select top "
					+ pageSize
					+ " a.[ID],a.[Name],a.[KeyWord],a.[Description],a.[GroupImage],a.[GroupMemberNum],a.[HostID],a.[HostName]"
					+ " from [v_CustomerGroupDetail] a where a.[State]=0 and a.[Name] like '%"
					+ key
					+ "%' and a.[ID] not in(select top "
					+ (currentPage - 1)
					* pageSize
					+ " a.[ID]"
					+ " from [v_CustomerGroupDetail] a where a.[State]=0 and a.[Name] like '%"
					+ key + "%')";
		} else {
			sql = "select top "
					+ pageSize
					+ " a.[ID],a.[Name],a.[KeyWord],a.[Description],a.[GroupImage],a.[GroupMemberNum],a.[HostName]"
					+ " from [v_CustomerGroupDetail] a where a.[State]=0 and a.[KeyWord] like '%"
					+ key
					+ "%' and a.[ID] not in(select top "
					+ (currentPage - 1)
					* pageSize
					+ " a.[ID]"
					+ " from [v_CustomerGroupDetail] a where a.[State]=0 and a.[KeyWord] like '%"
					+ key + "%')";
		}
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				desp = rs.getString(4) == null ? "无" : rs.getString(4);
				if (desp.length() > 15)
					desp = desp.substring(0, 12) + "...";
				CustomerGroupListBean customerGroups = new CustomerGroupListBean();
				customerGroups.setGroupID(rs.getString(1));
				customerGroups.setGroupName(rs.getString(2));
				customerGroups.setGroupKeyWord(rs.getString(3));
				customerGroups.setGroupDesc(desp);
				customerGroups.setGroupImage(rs.getString(5));
				customerGroups.setGroupMemberNum(rs.getString(6));
				customerGroups.setGroupCreatorID(rs.getString(7));
				customerGroups.setGroupCreator(rs.getString(8));
				hash.put(rs.getString(1), customerGroups);
			}
			return hash;
		} finally {
			if (ps != null)
				ps.close();
		}
	}
}