customerfrienddao.java

基于struts的网上商店源码

package com.mole.struts.dao;

import java.sql.CallableStatement;
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.CustomerFriendBean;
import com.mole.struts.bean.CustomerFriendGroupBean;
import com.mole.struts.bean.CustomerInfoBean;

public class CustomerFriendDAO {
	private Connection conn;

	public CustomerFriendDAO() {
		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 CustomerInfoBean getCustomerInfo(String ID) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		CustomerInfoBean bean = new CustomerInfoBean();
		String sql = "SELECT [LoginName],[Name],[FaceImage],[FaceWidth],[FaceHeight],CONVERT(varchar(30),[LastLogin],120),CONVERT(varchar(30),[CreateDate],120),[Nickname],[BlogTitle],[BlogSubTitle],[BlogStyle],[Gender],[Interest],[Description] FROM [Customer] WHERE [ID]=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			if (rs.next()) {
				bean.setID(ID);
				bean.setLoginName(rs.getString(1));
				bean.setName(rs.getString(2));
				bean.setFaceImage(rs.getString(3).trim());
				bean.setFaceWidth(rs.getString(4));
				bean.setFaceHeight(rs.getString(5));
				bean.setLastLogin(rs.getString(6));
				bean.setCreateDate(rs.getString(7));
				bean.setNickname(rs.getString(8));
				bean.setBlogTitle(rs.getString(9));
				bean.setBlogSubTitle(rs.getString(10));
				bean.setBlogStyle(rs.getString(11).trim());
				bean.setGender(rs.getString(12));
				bean.setInterest(rs.getString(13));
				bean.setDescription(rs.getString(14));
			}
			return bean;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取好友数量
	public int getFriendCount(String ID, String group) {
		int count = 0;
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT COUNT(*) FROM [CustomerFriend] WHERE CustomerID=?";
		if (group != null)
			sql += " AND [GroupID]=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			if (group != null)
				ps.setObject(2, group);
			rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	// 获取好友分组的列表
	public ArrayList<CustomerFriendGroupBean> getFriendGroupList(String ID)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT a.[ID],a.[Name],(SELECT COUNT(*) FROM [CustomerFriend] b WHERE b.[CustomerID]=? AND b.[GroupID]=a.[ID] AND (b.[CustomerID]=a.[CustomerID] OR a.[CustomerID] IS NULL)) AS [MemberCount] FROM [CustomerFriendGroup] a WHERE a.[CustomerID] IS NULL OR a.[CustomerID]=?";
		ArrayList<CustomerFriendGroupBean> al = new ArrayList<CustomerFriendGroupBean>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			ps.setObject(2, ID);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerFriendGroupBean bean = new CustomerFriendGroupBean();
				bean.setGroupID(rs.getString(1));
				bean.setGroupName(rs.getString(2));
				bean.setMemberCount(rs.getString(3));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取好友列表
	public ArrayList<CustomerFriendBean> getFriendList(String ID, String group,
			int currentPage, int pageSize) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "";
		if (group == null) {
			sql = "SELECT TOP "
					+ pageSize
					+ " [ID],[FriendID],[FriendName],[FriendGender],[BlogTitle],[FaceImage],[LastLogin] FROM [v_CustomerFriend] "
					+ "WHERE CustomerID=? AND [ID] NOT IN (SELECT TOP "
					+ (currentPage - 1) * pageSize
					+ " [ID] FROM [v_CustomerFriend] WHERE CustomerID=?)";
		} else {
			sql = "SELECT TOP "
					+ pageSize
					+ " [ID],[FriendID],[FriendName],[FriendGender],[BlogTitle],[FaceImage],[LastLogin] FROM [v_CustomerFriend] "
					+ "WHERE CustomerID=? AND [GroupID]=? AND [ID] NOT IN (SELECT TOP "
					+ (currentPage - 1)
					* pageSize
					+ " [ID] FROM [v_CustomerFriend] WHERE CustomerID=? AND [GroupID]=?)";
		}
		ArrayList<CustomerFriendBean> al = new ArrayList<CustomerFriendBean>();
		try {
			ps = conn.prepareStatement(sql);
			if (group == null) {
				ps.setObject(1, ID);
				ps.setObject(2, ID);
			} else {
				ps.setObject(1, ID);
				ps.setObject(2, group);
				ps.setObject(3, ID);
				ps.setObject(4, group);
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerFriendBean bean = new CustomerFriendBean();
				bean.setID(rs.getString(1));
				bean.setFriendID(rs.getString(2));
				bean.setFriendName(rs.getString(3));
				bean.setFriendGender(rs.getString(4));
				bean.setBlogTitle(rs.getString(5));
				bean.setFaceImage(rs.getString(6));
				bean.setLastLogin(rs.getString(7).substring(0, 19));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 获取好友分组信息
	public ArrayList<CustomerFriendGroupBean> getGroupList(String ID)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT * FROM [CustomerFriendGroup] WHERE [ID]>1 AND [CustomerID]=?";
		ArrayList<CustomerFriendGroupBean> al = new ArrayList<CustomerFriendGroupBean>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			rs = ps.executeQuery();
			while (rs.next()) {
				CustomerFriendGroupBean bean = new CustomerFriendGroupBean();
				bean.setGroupID(rs.getString(1));
				bean.setCustomerID(rs.getString(2));
				bean.setGroupName(rs.getString(3));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 添加好友分组
	public void insertGroup(String ID, String Name) {
		PreparedStatement ps = null;
		String sql = "INSERT INTO [CustomerFriendGroup]([CustomerID],[Name]) VALUES(?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, ID);
			ps.setObject(2, Name);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 更新好友分组
	public void updateGroup(String ID, String Name) {
		PreparedStatement ps = null;
		String sql = "UPDATE [CustomerFriendGroup] SET [Name]=? WHERE [ID]=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, Name);
			ps.setObject(2, ID);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 删除好友分组
	public void deleteGroup(String ID) {
		CallableStatement stmt = null;
		try {
			stmt = conn.prepareCall("{call sp_DeleteFriendGroup(?)}");
			stmt.setObject(1, ID);
			stmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 更新好友分组
	public void updateFriendGroup(String uid, String fid, String gid) {
		PreparedStatement ps = null;
		String sql = "UPDATE [CustomerFriend] SET [GroupID]=? WHERE [CustomerID]=? AND [FriendID]=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setObject(1, gid);
			ps.setObject(2, uid);
			ps.setObject(3, fid);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 删除好友
	public void deleteFriend(String uid, String fid) {
		PreparedStatement ps = null;
		try {
			ps = conn
					.prepareCall("DELETE FROM [CustomerFriend] WHERE [CustomerID]=? AND [FriendID]=?");
			ps.setObject(1, uid);
			ps.setObject(2, fid);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}