adminoperatemerchantdao.java

基于struts的网上商店源码

//作者：王亮亮
//时间：2007年12月15日
//功能：管理员操作商家信息（数据库接口）
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.AdminOperateMerchantBean;

public class AdminOperateMerchantDAO {
	private Connection conn;

	public AdminOperateMerchantDAO() {
		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 queryType, String value, int pageSize)// 分页
	{
		int count = 0;
		String sql;
		String where;
		if (queryType.equals("ID") == true) {
			where = "where a.ID='" + value + "' and ";
		} else if (queryType.equals("loginName") == true) {
			where = "where a.LoginName like '" + value + "%' and ";
		} else if (queryType.equals("name") == true) {
			where = "where a.Name like '" + value + "%' and ";
		} else if (queryType.equals("storeName") == true) {
			where = "where b.Name like '" + value + "' and ";
		} else if (queryType.equals("storeType")) {
			where = "join StoreType c on b.TypeID=c.ID and c.Name like '"
					+ value + "' and ";
		} else if (queryType.equals("storeCity")) {
			where = "join Area c on c.ID=b.AreaID join City d on c.CityID=d.ID where d.Name like '"
					+ value + "%' and ";
		} else if (queryType.equals("createTime")) {
			where = "where convert(char(10),a.CreateDate,126) >= '" + value
					+ "' and ";
		} else {
			where = "where  ";
		}
		sql = "select count(*) from Merchant a join Store b on a.StoreID=b.ID "
				+ where + "a.Role=1";
		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<AdminOperateMerchantBean> queryMerchant(String queryType,
			String value, int currentPage, int pageSize) throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql;
		String where;
		if (queryType.equals("ID") == true) {
			where = " and a.ID='" + value + "' ";
		} else if (queryType.equals("loginName") == true) {
			where = " and a.LoginName like '" + value
					+ "%' order by LoginName ";
		} else if (queryType.equals("name") == true) {
			where = " and a.Name like '" + value + "%' order by a.Name ";
		} else if (queryType.equals("storeName") == true) {
			where = " and b.Name like '" + value + "' ";
		} else if (queryType.equals("storeType")) {
			where = " and e.Name like '" + value + "' ";
		} else if (queryType.equals("storeCity")) {
			where = " and d.Name like '" + value + "%' ";
		} else if (queryType.equals("createTime")) {
			where = " and convert(char(10),a.CreateDate,126) >= '" + value
					+ "' order by a.createDate ";
		} else {
			where = "";
		}
		sql = "select top "
				+ pageSize
				+ " a.ID,a.LoginName,a.Name,convert(char(10),a.CreateDate,126),b.ID,d.Name,e.Name,a.State from Merchant a join Store b on a.StoreID=b.ID join Area c on c.ID=b.AreaID join City d on c.CityID=d.ID join StoreType e on e.ID=b.TypeID where a.Role=1 and a.ID not in(select top "
				+ (currentPage - 1)
				* pageSize
				+ " a.ID from Merchant a join Store b on a.StoreID=b.ID join Area c on c.ID=b.AreaID join City d on c.CityID=d.ID join StoreType e on e.ID=b.TypeID where a.Role=1"
				+ where + ")" + where;
		ArrayList<AdminOperateMerchantBean> a1 = new ArrayList<AdminOperateMerchantBean>();
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				AdminOperateMerchantBean merchantInfo = new AdminOperateMerchantBean();
				merchantInfo.setID(rs.getString(1));
				merchantInfo.setLoginName(rs.getString(2));
				merchantInfo.setName(rs.getString(3));
				merchantInfo.setCreateDate(rs.getString(4));
				merchantInfo.setStoreID(rs.getString(5));
				merchantInfo.setStoreCity(rs.getString(6));
				merchantInfo.setStoreType(rs.getString(7));
				merchantInfo.setState(rs.getInt(8));
				a1.add(merchantInfo);
			}
			return a1;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

}