customerhomedao.java

基于struts的网上商店源码

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.CustomerBlogListBean;
import com.mole.struts.bean.CustomerFriendBean;
import com.mole.struts.bean.CustomerGroupListBean;
import com.mole.struts.bean.CustomerInfoBean;

public class CustomerHomeDAO {
	private Connection conn;

	public CustomerHomeDAO() {
		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 CustomerInfoBean getCustomerInfo(String ID) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		CustomerInfoBean bean = new CustomerInfoBean();
		String sql = "SELECT [Nickname],[FaceImage],[FaceWidth],[FaceHeight],CONVERT(varchar(30),[LastLogin],120),[BlogStyle],[ShowRecord],[ShowShopped] FROM [Customer] WHERE [ID]=?";
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			if (rs.next()) {
				bean.setID(ID);
				bean.setNickname(rs.getString(1));
				bean.setFaceImage(rs.getString(2).trim());
				bean.setFaceWidth(rs.getString(3));
				bean.setFaceHeight(rs.getString(4));
				bean.setLastLogin(rs.getString(5));
				bean.setBlogStyle(rs.getString(6).trim());
				bean.setShowRecord(rs.getString(7));
				bean.setShowShopped(rs.getString(8));
			}
			return bean;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取日志信息
	public ArrayList<CustomerBlogListBean> getDiary(String ID) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT TOP 10 a.[ID],b.[Name],a.[Title],(SELECT COUNT(*) FROM [BlogLogList] c WHERE c.[TopicID]=a.[ID]) AS [Reply] FROM [BlogLogList] a,[BlogLogType] b WHERE a.[TypeID]=b.[ID] AND a.[TopicID]=0 AND a.[WriterID]=? ORDER BY a.[IssueTime] DESC";
		ArrayList<CustomerBlogListBean> al = new ArrayList<CustomerBlogListBean>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerBlogListBean bean = new CustomerBlogListBean();
				bean.setID(rs.getString(1));
				bean.setName(rs.getString(2));
				bean.setType(rs.getString(3));
				bean.setReply(rs.getString(4));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取好友列表
	public ArrayList<CustomerFriendBean> getFriendList(String ID)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT TOP 6 [FriendID],[FriendName],[FaceImage],[BlogTitle] FROM [v_CustomerFriend] WHERE [CustomerID]=? ORDER BY [CreateDate] DESC";
		ArrayList<CustomerFriendBean> al = new ArrayList<CustomerFriendBean>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerFriendBean bean = new CustomerFriendBean();
				bean.setFriendID(rs.getString(1));
				bean.setFriendName(rs.getString(2));
				bean.setFaceImage(rs.getString(3));
				bean.setBlogTitle(rs.getString(4));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取好友分组列表
	public ArrayList<CustomerGroupListBean> getGroupList(String ID)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "select top 3 a.[ID],a.[Name],a.[KeyWord],a.[Description],a.[GroupImage],a.[GroupMemberNum],a.[HostID],a.[HostName] from [v_CustomerGroupDetail] a,[CustomerGroupMember] b where  a.[State]=0 and a.[ID]=b.[GroupID] AND b.[ID]=? ORDER BY b.[JoinDate] DESC";
		ArrayList<CustomerGroupListBean> al = new ArrayList<CustomerGroupListBean>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			while (rs.next()) {
				String desp = rs.getString(4) == null ? "无" : rs.getString(4);
				if (desp.length() > 15)
					desp = desp.substring(0, 12) + "...";
				CustomerGroupListBean bean = new CustomerGroupListBean();
				bean.setGroupID(rs.getString(1));
				bean.setGroupName(rs.getString(2));
				bean.setGroupKeyWord(rs.getString(3));
				bean.setGroupDesc(desp);
				bean.setGroupImage(rs.getString(5));
				bean.setGroupMemberNum(rs.getString(6));
				bean.setGroupCreatorID(rs.getString(7));
				bean.setGroupCreator(rs.getString(8));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取消费记录信息
	public ArrayList<Object[]> getRecord(String ID) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT TOP 10 [ID],[StoreName],CONVERT(char(19),[DealTime],120),ISNULL([Grade],0) FROM [v_CustomerRecord] WHERE [CustomerID]=? ORDER BY [DealTime] DESC";
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] o = new Object[4];
				o[0] = rs.getString(1);
				o[1] = rs.getString(2);
				o[2] = rs.getString(3);
				o[3] = rs.getString(4);
				al.add(o);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取消费多的店铺
	public ArrayList<Object[]> getShopped(String ID) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT DISTINCT TOP 4 a.[ID],a.[Name],a.[Image] FROM [Store] a,[SwiftNumber] b WHERE a.[ID]=b.[StoreID] AND b.[CustomerID]=?";
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			while (rs.next()) {
				Object[] o = new Object[3];
				o[0] = rs.getString(1);
				o[1] = rs.getString(2);
				o[2] = rs.getString(3);
				al.add(o);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}
}