merchantstoredao.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.util.ArrayList;

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

import com.mole.struts.bean.CustomerMessageBorderBean;
import com.mole.struts.bean.MerchantGoodsTypeBean;
import com.mole.struts.bean.MerchantStoreInfoBean;

public class MerchantStoreDAO {
	private Connection conn;

	public MerchantStoreDAO() {
		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 sid) {
		String style = "default";
		ResultSet rs = null;
		String sql = "SELECT [BlogStyle] FROM [Store] WHERE [ID]='" + sid + "'";
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				style = rs.getString(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return style;
	}

	public int getCount(String sid) {
		int count = 0;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM [StoreMessageBoard] WHERE [Owner]="
				+ sid;
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	public int getVoucherCount(String sid) {
		int count = 0;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM [MerchantVoucher] WHERE [StoreID]='"
				+ sid + "'";
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	public String getMerchantID(String sid) {
		String mid = "";
		ResultSet rs = null;
		String sql = "SELECT ID FROM [v_MerchantStore] WHERE [StoreID]='" + sid
				+ "'";
		try {
			rs = conn.prepareStatement(sql).executeQuery();
			if (rs.next())
				mid = rs.getString(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return mid;
	}

	public ArrayList<Object[]> getCommends(String sid) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		String sql = "SELECT TOP 10 [CommendID],[commendImage],[commendTitle],[commendAbstract] FROM [CommendList] WHERE [MerchantID] IN (SELECT [ID] FROM [v_MerchantStore] WHERE [StoreID]=?) ORDER BY [CommendTime] DESC";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			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 MerchantStoreInfoBean getStroeInfo(String sid) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		MerchantStoreInfoBean bean = new MerchantStoreInfoBean();
		String sql = "SELECT a.[ID],b.[ID],a.[Name],a.[Address],a.[Scale],a.[Telephone],a.[Image],CONVERT(char(19),a.[CreateDate],120) FROM [Store] a,[Merchant] b WHERE b.[ID]=a.[ChargeID] AND a.[ID]=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			rs = ps.executeQuery();
			if (rs.next()) {
				bean.setStoreID(rs.getString(1));
				bean.setMerchantID(rs.getString(2));
				bean.setStoreName(rs.getString(3));
				bean.setStoreAddress(rs.getString(4));
				bean.setStoreScale(rs.getString(5));
				bean.setStoreTelephone(rs.getString(6).trim());
				bean.setStoreImage(rs.getString(7).trim());
				bean.setStoreCreateDate(rs.getString(8));
			}
			return bean;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public ArrayList<MerchantGoodsTypeBean> getGoodsType(String sid)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<MerchantGoodsTypeBean> al = new ArrayList<MerchantGoodsTypeBean>();
		String sql = "SELECT [ID],[Name] FROM [GoodsType] WHERE [StoreID]=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			rs = ps.executeQuery();
			while (rs.next()) {
				MerchantGoodsTypeBean bean = new MerchantGoodsTypeBean();
				bean.setId(rs.getString(1));
				bean.setName(rs.getString(2));
				al.add(bean);
			}
			return al;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public ArrayList<Object[]> getStoreAds(String sid) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		String sql = "SELECT [ID],[Name] FROM [MerchantBulletin] WHERE [StoreID]=? ORDER BY [CreateDate] DESC";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			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[]> getVoucher(String sid) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		String sql = "SELECT TOP 10 [ID],[Image],CASE WHEN LEN([Name])>9 THEN CONVERT(nvarchar(8),[Name])+'...' ELSE [Name] END [Name],"
				+ "CASE WHEN LEN([Description])>20 THEN CONVERT(nvarchar(19),[Description])+'...' ELSE [Description] END [Description] FROM "
				+ "[MerchantVoucher] WHERE [State]=0 AND [StoreID]=? ORDER BY [CreateDate] DESC";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			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[]> getBizAds(String sid) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		String sql = "SELECT b.[ID],CASE b.[ActivityId] WHEN 1 THEN '[积分互认]' WHEN 2 THEN '[代发优惠券]' WHEN 3 THEN '[共同打折]' END + b.[Name] FROM [v_BizGroupStore] a,[BizGroup] b WHERE a.[BizGroupID]=b.[ID] AND [State]=1 AND a.[StoreID]=? ORDER BY b.[StartDate] DESC";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			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[]> getCommendGoods(String sid) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		String sql = "SELECT TOP 10 [ID],[Name],[Price],[Image] FROM [v_StoreGoods] WHERE [Commend]=1 AND [StoreID]=? ORDER BY [LastUpdate] DESC";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			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[]> getBlog(String sid) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		String sql = "SELECT TOP 5 a.[Title],a.[ReplyCount],CONVERT(char(10),a.[IssueTime],120) FROM [v_MerchantStoreBlog] a,[v_MerchantStore] b WHERE a.[WriterID]=b.[ID] AND a.[TopicID]=0 AND b.[StoreID]=? ORDER BY [IssueTime] DESC";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			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();
		}
	}

	public ArrayList<Object[]> getVoucherList(String sid, int currentPage,
			int pageSize) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		String sql = "SELECT TOP "
				+ pageSize
				+ " [ID],[Image],[Name],[RestAmount],[Description] FROM [v_MerchantVoucher] WHERE [State]=0 AND [StoreID]=? AND [ID] NOT IN ("
				+ "SELECT TOP "
				+ (currentPage - 1)
				* pageSize
				+ " [ID] FROM [v_MerchantVoucher] WHERE [State]=0 AND [StoreID]=? ORDER BY [CreateDate] DESC) ORDER BY [CreateDate] DESC";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			ps.setString(2, sid);
			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 ArrayList<CustomerMessageBorderBean> getMessage(String sid,
			int currentPage, int pageSize) throws Exception {
		ResultSet rs = null;
		CallableStatement stmt = null;
		ArrayList<CustomerMessageBorderBean> al = new ArrayList<CustomerMessageBorderBean>();
		try {
			conn.setAutoCommit(true);
			stmt = conn.prepareCall("{call sp_GetStoreMessageBoard(?,?,?)}");
			stmt.setString(1, sid);
			stmt.setInt(2, currentPage);
			stmt.setInt(3, pageSize);
			rs = stmt.executeQuery();
			while (rs.next()) {
				CustomerMessageBorderBean bean = new CustomerMessageBorderBean();
				bean.setID(rs.getString(1));
				bean.setDeliver(rs.getString(2));
				bean.setDeliverType(rs.getString(3));
				bean.setDeliverName(rs.getString(4));
				bean.setFaceImage(rs.getString(5));
				bean.setFaceWidth(rs.getString(6));
				bean.setFaceHeight(rs.getString(7));
				bean.setContext(rs.getString(8));
				bean.setDeliverTime(rs.getString(9));
				al.add(bean);
			}
			return al;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void insertMessage(String sid, String user, String role,
			String content) throws Exception {
		PreparedStatement ps = null;
		String sql = "INSERT INTO [StoreMessageBoard]([Owner],[Deliver],[DeliverRole],[Content]) VALUES(?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, sid);
			ps.setString(2, user);
			ps.setString(3, role);
			ps.setString(4, content);
			ps.execute();
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	public void delMessage(String mid) throws Exception {
		PreparedStatement ps = null;
		String sql = "DELETE FROM [StoreMessageBoard] WHERE ID=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, mid);
			ps.execute();
		} finally {
			if (ps != null)
				ps.close();
		}
	}

}