productdaoimpl.java

自己制作的联通CRM,支持客户分类,管理,升级,积分管理等等..

package com.jn0801.intergral.product;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.jn0801.tools.DBConnection;
import com.jn0801.tools.PageDAO;

public class ProductDaoImpl extends HibernateDaoSupport implements ProductDao {

	/**
	 * 此方法根据条件返回物品列表
	 * 
	 * @param keyword
	 * @param typeid
	 * @param request
	 * @return
	 */
	public List listProduct(String keyword, Long typeid,
			HttpServletRequest request) {
		List productList = new ArrayList<ProductDetail>();
		Connection connection = null;
		String sql = null;
		PageDAO pageDAO = null;
		try {
			connection = DBConnection.getConnection();
			pageDAO = new PageDAO(request);
			sql = "select count(1) as rscount from productdetail where 1=1 ";
			if (typeid != null && typeid != 0) {
				sql = sql + " and typeid=" + typeid;
			}
			if (keyword != null && !"".equals(keyword)) {
				sql = sql + " and productname like '%" + keyword
						+ "%' or productintroduce like '%" + keyword + "%'";
			}
			int rscount = pageDAO.getRsCountForSQL(sql);
			int pagesize = 10;
			pageDAO.setPagesize(pagesize);
			pageDAO.setRscount(rscount);
			pageDAO.getPageCount();
			int currentpage = pageDAO.getCurrentPage();
			String pagetool = pageDAO.pagetool(PageDAO.BbsText);
			request.setAttribute("pagetool", pagetool);

			System.out.println(sql);

			sql = "select * from "
					+ "(select productid,productname,productintroduce,productimage,point,typeid,productor,productstorage,rownum rn "
					+ "from productdetail where 1=1 ";
			if (typeid != null && typeid != 0) {
				sql = sql + " and typeid=" + typeid;
			}
			if (keyword != null && !"".equals(keyword)) {
				sql = sql + " and productname like '%" + keyword
						+ "%' or productintroduce like '%" + keyword + "%'";
			}
			sql = sql + " and rownum <=" + currentpage * pagesize + ") t ";
			sql = sql + " where t.rn >=" + ((currentpage - 1) * pagesize + 1);

			System.out.println(sql);

			QueryRunner queryRunner = new QueryRunner();
			productList = (List) queryRunner.query(connection, sql,
					new BeanListHandler(ProductDetail.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.closeQuietly(connection);
		}
		return productList;
	}

	/**
	 * 此方法根据物品ID返回物品的详细信息
	 * 
	 * @param productid
	 * @return
	 */
	public ProductDetail findProduct(Long productid) {
		ProductDetail productDetail = null;
		productDetail = (ProductDetail) this.getHibernateTemplate().get(
				ProductDetail.class, productid);
		return productDetail;
	}

	/**
	 * 此方法用于保存或更新物品详细信息
	 * 
	 * @param productDetail
	 * @return
	 */
	public boolean saveProduct(ProductDetail productDetail) {
		boolean flag = false;
		try {
			this.getHibernateTemplate().saveOrUpdate(productDetail);
			flag = true;
		} catch (DataAccessException e) {
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * 此方法用于删除一条物品信息
	 * 
	 * @param productid
	 */
	public boolean deleteProduct(Long productid) {
		boolean flag = false;
		Connection connection = null;
		Statement statement = null;
		String sql = null;
		try {
			sql = "delete from productdetail where productid=" + productid;
			connection = DBConnection.getConnection();
			statement = connection.createStatement();
			statement.execute(sql);
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, null, statement, connection);
		}
		return flag;
	}
	
	/**
	 * 返回物品类型列表
	 * 
	 * @return
	 */
	public List listType() {
		List<Map<String, Object>> typeList = null;
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		String sql = null;
		try {
			connection = DBConnection.getConnection();
			statement = connection.createStatement();
			typeList = new ArrayList<Map<String,Object>>();
			sql = "select * from producttype";
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				Map<String, Object> type = new HashMap<String, Object>();
				type.put("typeid", resultSet.getObject(1));
				type.put("sortname", resultSet.getObject(3));
				typeList.add(type);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return typeList;
	}
}