customergroupforumlistdao.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 javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.mole.struts.bean.CustomerGroupForumListBean;

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

	public CustomerGroupForumListDAO() {
		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 int getPageInfo(String groupID, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "select count(*) from [v_CustomerGroupBlogList] where [GroupID]="
				+ groupID;
		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 blogID, String groupID, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		String sql = "select count(*) from [CustomerGroupList] where [GroupID]="
				+ groupID + " and [TopicID]=" + blogID;
		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<CustomerGroupForumListBean> queryGroups(String groupID,
			int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String content = null;
		ArrayList<CustomerGroupForumListBean> list = new ArrayList<CustomerGroupForumListBean>();
		String sql = "select top "
				+ pageSize
				+ " a.[ID],a.[Title],a.[Content],a.[Nickname],convert(nvarchar(20),a.[IssueTime],120),a.[Revert],a.[AuthorID] from [v_CustomerGroupBlogList] a where a.[GroupID]="
				+ groupID + " and a.[ID] not in(select top "
				+ (currentPage - 1) * pageSize
				+ " a.[ID] from [v_CustomerGroupBlogList] a where a.[GroupID]="
				+ groupID + " order by a.[ID] DESC) order by a.[ID] DESC";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				content = rs.getString(3) == null ? "[0字贴]" : rs.getString(3);
				if (content.length() > 15)
					content = content.substring(0, 12) + "...";
				CustomerGroupForumListBean customerGroupForums = new CustomerGroupForumListBean();
				customerGroupForums.setBlogID(rs.getString(1));
				customerGroupForums.setBlogTitle(rs.getString(2));
				customerGroupForums.setBlogContent(content);
				customerGroupForums.setBlogAuthor(rs.getString(4));
				customerGroupForums.setBlogIssueTime(rs.getString(5));
				customerGroupForums.setBlogRevert(rs.getString(6));
				customerGroupForums.setBlogAuthorID(rs.getString(7));
				list.add(customerGroupForums);
			}
			return list;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取某群日志回复内容
	public ArrayList<CustomerGroupForumListBean> queryGroups(String blogID,
			String groupID, int currentPage) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String content = null;
		ArrayList<CustomerGroupForumListBean> list = new ArrayList<CustomerGroupForumListBean>();
		String sql = "select top "
				+ pageSize
				+ " a.[ID],a.[Title],a.[Content],b.[Nickname],convert(nvarchar(20),a.[IssueTime],120),a.[AuthorID] from [CustomerGroupList] a,[Customer] b where a.[GroupID]="
				+ groupID + " and a.[TopicID]=" + blogID
				+ " and b.[ID]=a.[AuthorID]" + " and a.[ID] not in(select top "
				+ (currentPage - 1) * pageSize
				+ " a.[ID] from [CustomerGroupList] a where a.[GroupID]="
				+ groupID + " and a.[TopicID]=" + blogID
				+ " order by a.[ID] ASC) order by a.[ID] ASC";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerGroupForumListBean customerGroupForums = new CustomerGroupForumListBean();
				customerGroupForums.setBlogID(rs.getString(1));
				customerGroupForums.setBlogTitle(rs.getString(2));
				customerGroupForums.setBlogContent(rs.getString(3));
				customerGroupForums.setBlogAuthor(rs.getString(4));
				customerGroupForums.setBlogIssueTime(rs.getString(5));
				customerGroupForums.setBlogAuthorID(rs.getString(6));
				list.add(customerGroupForums);
			}
			return list;
		} finally {
			if (ps != null)
				ps.close();
		}
	}
}