adminbizgroupdao.java

基于struts的网上商店源码

package com.mole.struts.dao;

/**
 * @author ruzhenchao
 * Creation date: 12-17-2007
 */
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;

import com.mole.struts.bean.AdminBizGroupInfoBean;
import com.mole.struts.form.AdminBizGroupManageForm;

public class AdminBizGroupDAO extends AbstractPageDAO {

	public AdminBizGroupDAO() {
	}

	// 获取页面信息
	public int getPageInfo(AdminBizGroupManageForm form, int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		this.conn = this.getConn();

		String sqlCondition = " where b.[ID]=a.[StoreID] and c.[ID]=SUBSTRING(b.[AreaID],1,4) ";

		if (form != null) {
			if (form.getActivity() != 0) {
				sqlCondition += " and a.[ActivityId] = '" + form.getActivity()
						+ "' ";
			}
			if (!form.getCity().equals("")) {
				sqlCondition += " and c.[Name] LIKE '%" + form.getCity()
						+ "%' ";
			}
			if (!form.getStoreName().equals("")) {
				sqlCondition += " and b.[Name] LIKE '%" + form.getStoreName()
						+ "%'  ";
			}
		}

		this.whereCondition = sqlCondition;

		String sql = "select count(*) from [BizGroup] a,[Store] b,[City] c "
				+ sqlCondition;

		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 AdminBizGroupInfoBean[] getBizGroupInfo(int currentPage) {
		String sqlFirst = "select top "
				+ pageSize
				+ " a.[ID],a.[Name],a.[StartDate],a.[EndDate],a.[State],a.[StoreID],b.[Name],a.[ActivityID],c.[Name] from [BizGroup] a,[Store] b,[City] c ";
		String sqlNext = " and a.[ID] not in (select top " + pageSize
				* (currentPage - 1)
				+ " a.[ID] from [BizGroup] a ,[Store] b,[City] c ";
		String sqlLast = " ) order by a.[ID]";

		String sql = sqlFirst + this.whereCondition + sqlNext
				+ this.whereCondition + sqlLast;

		ArrayList<Object[]> al = this.executeQuery(sql);
		AdminBizGroupInfoBean[] beanList = new AdminBizGroupInfoBean[al.size()];
		Iterator<Object[]> it = al.iterator();
		int i = 0;
		while (it.hasNext()) {
			Object[] obj = it.next();
			AdminBizGroupInfoBean bean = new AdminBizGroupInfoBean();
			bean.setId(obj[0].toString());
			bean.setName(obj[1].toString());
			bean.setStartDate(obj[2].toString().substring(0, 10));
			bean.setEndDate(obj[3].toString().substring(0, 10));
			bean.setState((Integer) (obj[4]));
			bean.setStoreID(obj[5].toString());
			bean.setStoreName(obj[6].toString());
			bean.setActivityID((Integer) obj[7]);
			bean.setCity(obj[8].toString());
			beanList[i++] = bean;
		}
		return beanList;
	}

	// 获取商圈详细信息
	public AdminBizGroupInfoBean getBizGroupDetailInfo(String id) {
		String sql = "select a.[ID],a.[Name],a.[StartDate],a.[EndDate],a.[State],b.[Name],a.[Description],a.[ActivityId],a.[ActivityDes],c.[Name] from [BizGroup] a,[Store] b,[City] c"
				+ " where a.[ID]='"
				+ id
				+ "' and b.[ID]=a.[storeID] and c.[ID]=SUBSTRING(b.[ID],1,4) ";

		ArrayList<Object[]> al = this.executeQuery(sql);

		AdminBizGroupInfoBean bean = new AdminBizGroupInfoBean();
		Object[] obj = al.get(0);

		bean.setId(obj[0].toString());
		bean.setName(obj[1].toString());
		bean.setStartDate(obj[2].toString().substring(0, 10));
		bean.setEndDate(obj[3].toString().substring(0, 10));
		bean.setState((Integer) obj[4]);
		bean.setStoreName(obj[5].toString());
		bean.setDescription(obj[6].toString());
		bean.setActivityID((Integer) obj[7]);
		bean.setActivityDes((String) obj[8]);
		bean.setCity(obj[9].toString());

		return bean;
	}

	// 删除过期商圈
	public void deleteBizGroup(String id) {
		String sql = "delete BizGroup where ID='" + id + "'";
		this.executeUpdate(sql);
	}
}