itemdao.java

一套购物车项目。电子商务系统。实现了前台和后台的业务逻辑。

package com.softfz.jn0708.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.softfz.jn0708.bean.ItemBean;
import com.softfz.jn0708.util.SqlUtil;

/**
 * 商品数据操作DAO
 * @author student
 *
 */

public class ItemDAO {
	
	/**
	 * 获取商品列表
	 * @param currentPage 当前页码
	 * @param count 显示记录数
	 * @param item 商品Bean对象
	 * @return
	 */
	public List getItemList(int currentPage,int count,ItemBean item){
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List itemList = null;
		
		StringBuffer centent = new StringBuffer();
		centent.append(" select t.*,tc1.classname as bigtype,tc2.classname as smalltype from t_iteminfo t ");
		centent.append(" left join t_classinfo tc1 on t.classid = tc1.classid");
		centent.append(" left join t_classinfo tc2 on t.nclassid = tc2.classid");
		centent.append(" where 1=1 ");
		
	
		
		if(item.getTitle()!=null&&!item.getTitle().trim().equals("")){
			centent.append(" and t.title like '%").append(item.getTitle().trim()).append("%'");
		}
		if((item.getPrice()!=0&&!String.valueOf(item.getPrice()).trim().equals(""))||(item.getEndprice()!=0&&!String.valueOf(item.getEndprice()).trim().equals(""))){
			centent.append(" and t.price between'").append(String.valueOf(item.getPrice()).trim()).append("'");
			centent.append(" and '").append(String.valueOf(item.getEndprice()).trim()).append("'");
		}
		if(item.getClassid()!=0&&!String.valueOf(item.getClassid()).trim().equals("")){
			centent.append(" and t.classid =").append(String.valueOf(item.getClassid())).append("");
		}
		if(item.getNclassid()!=0&&!String.valueOf(item.getNclassid()).trim().equals("")){
			centent.append(" and t.nclassid = ").append(String.valueOf(item.getNclassid())).append("");
		}

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM(")
		.append("SELECT  m.itemid,m.classid,m.nclassid,m.title,m.content,m.price,m.remark,m.filepath,m.filename,m.addtime,m.bigtype,m.smalltype,ROWNUM AS num FROM (")
		.append(centent)
		.append(") m ) t WHERE ");
		sql.append(" t.num<=").append(currentPage*count);
		sql.append(" AND t.num>").append((currentPage-1)*count);
		sql.append(" order by t.itemid");
				
	
		
		
		try {
			conn = SqlUtil.getConn();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			itemList = new ArrayList();
			while(rs.next()){
				ItemBean bean = buildItem(rs);
				itemList.add(bean);
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			SqlUtil.tryClose(rs,st,conn);
		}
		
		return itemList;
	}

	
	/**
	 * 获得商品类别Bean
	 * @param rs 结果集
	 * @return
	 * @throws SQLException
	 */
//	private ClassBean buildClassItem(ResultSet rs) throws SQLException {
//		
//		ClassBean classbean = new ClassBean();
//		classbean.setClassid(rs.getInt("classid"));//商品类别编号
//		classbean.setClassname(rs.getString("classname")); // 商品类别名称
//		classbean.setParentid(rs.getInt("parented")); // 商品类别父类编号
//		return classbean;
//	}




	/**
	 * 添加商品
	 * @param bean 商品Bean对象
	 * @return
	 */
	public boolean create(ItemBean bean) {
		StringBuffer sql = new StringBuffer();
		sql.append("insert into T_itemInfo (itemid,classid,nclassid,title,content,price,remark,filepath,filename,addtime) values(").append("SEQ_T_ITEMINFO.nextval,");
		sql.append(bean.getClassid()+",").append(bean.getNclassid()+",'").append(bean.getTitle()+"','").append(bean.getContent()+"',");
		sql.append(bean.getPrice()+",'").append(bean.getRemark()+"','").append(bean.getFilepath()+"','");
		sql.append(bean.getFilename()+"','").append(bean.getAddtime()+"')");
		return SqlUtil.execteUpdate(sql.toString());	
	}

	/**
	 * 通过ID获取商品Bean对象
	 * @param itemid 商品编号
	 * @return
	 */
	public ItemBean getItemById(String itemid) {
		
		String sql = "select t.*,tc1.classname as bigtype,tc2.classname as smalltype from t_iteminfo t left join t_classinfo tc1 on t.classid = tc1.classid left join t_classinfo tc2 on t.nclassid = tc2.classid where itemid="+itemid;
		sql = sql+" order by itemid";
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		conn = SqlUtil.getConn();
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);			
			if(rs.next()){
				return buildItem(rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			SqlUtil.tryClose(rs,st,conn);
		}
		return null;
		
	}
	
	
	/**
	 * 通过结果集获得商品Bean对象
	 * @param rs 结果集
	 * @return
	 * @throws SQLException
	 */
	private ItemBean buildItem(ResultSet rs) throws SQLException{
		
		ItemBean bean = new ItemBean();	
		bean.setItemid(rs.getInt("itemid"));// 商品编号
		bean.setClassid(rs.getInt("classid"));// 商品大类编号
		bean.setClassname(rs.getString("bigtype"));
		bean.setNclassid(rs.getInt("nclassid"));// 商品小类编号
		bean.setNclassname(rs.getString("smalltype"));
		bean.setTitle(rs.getString("title"));// 商品名称
		bean.setContent(rs.getString("content"));// 商品描述
		bean.setPrice(rs.getDouble("price"));// 商品价格
		bean.setRemark(rs.getString("remark"));// 商品备注
		bean.setFilepath(rs.getString("filepath"));// 商品图片路径
		bean.setFilename(rs.getString("filename"));// 商品图片名称
		bean.setAddtime(rs.getString("addtime"));// 商品添加时间
		bean.setQuantity("0");
//		bean.setOrderdetailid(OrderDAO.getOrderDetailID());
		return bean;
	}

	/**
	 * 更新商品信息
	 * @param bean 商品Bean对象
	 * @return
	 */
	public boolean update(ItemBean bean) {
		StringBuffer sql = new StringBuffer();
		sql.append("update t_iteminfo set");
		sql.append(" classid='").append(bean.getClassid()).append("',");
		sql.append(" nclassid='").append(bean.getNclassid()).append("',");
		sql.append(" title='").append(bean.getTitle()).append("',");
		sql.append(" price=").append(bean.getPrice()).append(",");
		sql.append(" content='").append(bean.getContent()).append("',");
		sql.append(" remark='").append(bean.getRemark()).append("',");
		sql.append(" filepath='").append(bean.getFilepath()).append("',");
		sql.append(" filename='").append(bean.getFilename()).append("'");
		sql.append(" where itemid=").append(bean.getItemid());
		return SqlUtil.execteUpdate(sql.toString());
	}

	/**
	 * 删除商品信息
	 * @param itemid
	 * @return
	 */
	public boolean delete(String itemid) {
		String sql = "delete t_iteminfo where itemid="+itemid;
		return SqlUtil.execteUpdate(sql.toString());
	}

	/**
	 * 由商品类别ID获取商品列表
	 * @param currentPage 当前页码
	 * @param count 当前显示页数
	 * @param bean 商品bean对象
	 * @return
	 */
	public List getItemListbyClassid(int currentPage, int count, ItemBean bean) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List itemList = null;
		
		StringBuffer centent = new StringBuffer();
		centent.append(" select t.*,tc1.classname as bigtype,tc2.classname as smalltype from t_iteminfo t ");
		centent.append(" left join t_classinfo tc1 on t.classid = tc1.classid");
		centent.append(" left join t_classinfo tc2 on t.nclassid = tc2.classid");
		centent.append(" where 1=1 ");
		
		if(String.valueOf(bean.getClassid())!=null&&!String.valueOf(bean.getClassid()).trim().equals("")){
			centent.append(" and t.classid =").append(String.valueOf(bean.getClassid()).trim());
		}

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM(")
		.append("SELECT  m.itemid,m.classid,m.nclassid,m.title,m.content,m.price,m.remark,m.filepath,m.filename,m.addtime,m.bigtype,m.smalltype,ROWNUM AS num FROM (")
		.append(centent)
		.append(") m ) t WHERE ");
		sql.append(" t.num<=").append(currentPage*count);
		sql.append(" AND t.num>").append((currentPage-1)*count);
		sql.append(" order by t.itemid");
				
		StringBuffer classsql = new StringBuffer();
		classsql.append("SELECT * FROM t_classinfo");
		
		
		try {
			conn = SqlUtil.getConn();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			itemList = new ArrayList();
			while(rs.next()){
				ItemBean beans = buildItem(rs);
				itemList.add(beans);
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			SqlUtil.tryClose(rs,st,conn);
		}
		
		return itemList;
	}

	/**
	 * 通过商品小类别ID获取商品列表
	 * @param currentPage 当前页码
	 * @param count 当前显示页数
	 * @param bean 商品bean 对象
	 * @return
	 */
	public List getItemListbyNclassid(int currentPage, int count, ItemBean bean) {
		// TODO 自动生成方法存根
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List itemList = null;
		
		StringBuffer centent = new StringBuffer();
		centent.append(" select t.*,tc1.classname as bigtype,tc2.classname as smalltype from t_iteminfo t ");
		centent.append(" left join t_classinfo tc1 on t.classid = tc1.classid");
		centent.append(" left join t_classinfo tc2 on t.nclassid = tc2.classid");
		centent.append(" where 1=1 ");
		
		if(String.valueOf(bean.getNclassid())!=null&&!String.valueOf(bean.getNclassid()).trim().equals("")){
			centent.append(" and t.nclassid =").append(String.valueOf(bean.getNclassid()).trim());
		}

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM(")
		.append("SELECT  m.itemid,m.classid,m.nclassid,m.title,m.content,m.price,m.remark,m.filepath,m.filename,m.addtime,m.bigtype,m.smalltype,ROWNUM AS num FROM (")
		.append(centent)
		.append(") m ) t WHERE ");
		sql.append(" t.num<=").append(currentPage*count);
		sql.append(" AND t.num>").append((currentPage-1)*count);
		sql.append(" order by t.itemid");
				
		StringBuffer classsql = new StringBuffer();
		classsql.append("SELECT * FROM t_classinfo");
		
		
		try {
			conn = SqlUtil.getConn();
			st = conn.createStatement();
			rs = st.executeQuery(sql.toString());
			itemList = new ArrayList();
			while(rs.next()){
				ItemBean beans = buildItem(rs);
				itemList.add(beans);
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			SqlUtil.tryClose(rs,st,conn);
		}
		
		return itemList;
	}
}