oracleorderitemdao.java

电子商城

package com.lmh.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;

import com.lmh.dao.db.DBTool;
import com.lmh.dao.inf.OrderItemDAO;
import com.lmh.dao.vo.OrderItemBean;

public class OracleOrderItemDAO implements OrderItemDAO {
	private Connection conn = null;

	/**
	 * 获取订单条目总数.
	 * 
	 * @return 订单条目总数.
	 */
	public int getCount() {
		return getCount(-1);
	}

	/**
	 * 获取订单条目总数.
	 * 
	 * @param norderid
	 *            订单号.
	 * @return 订单条目总数.
	 */
	public int getCount(long norderid) {

		conn = DBTool.getConn();

		PreparedStatement ps = null;
		ResultSet rs = null;
		int num = 0;

		StringBuffer sql = new StringBuffer();
		sql.append("select  count(*)as countAll from t_order_item where 1=1");

		if (norderid != -1) {
			sql.append(" and norderid=" + norderid);
		}
		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				num = rs.getInt("countAll");
			}
			return num;
		} catch (SQLException e) {
			e.printStackTrace();
			return num;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 根据订单号查询所有订单条目.
	 * 
	 * @param norderid
	 *            订单号.
	 * @param pastart
	 *            结果 开始行(-1:表无此条件).
	 * @param paend
	 *            结果 结束行(-1:表无此条件).
	 * @return 订单条目集.
	 */
	public List searchOrderItem(long norderid, int pastart, int paend) {

		conn = DBTool.getConn();

		List<OrderItemBean> orderList = new ArrayList<OrderItemBean>();

		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer allStr = new StringBuffer();

		allStr.append("select * from ");
		allStr.append("(");
		allStr
				.append("select rownum as myRow,nid,norderid,nmcid, smcname,sdescription,simg,ncount,nprice,ntotalprice ");
		allStr.append("	from t_order_item ");
		allStr.append(" where 1=1 ");

		if (norderid != -1) {
			allStr.append(" and norderid = " + norderid);
		}
		if (paend != -1) {
			allStr.append(" and rownum <=" + paend);
		}
		allStr.append(" )");
		if (pastart != -1) {
			allStr.append(" where myRow >= " + pastart);
		}

		try {
			ps = conn.prepareStatement(allStr.toString());
			rs = ps.executeQuery();
			while (rs.next()) {
				OrderItemBean oib = new OrderItemBean();
				oib.setNid(rs.getInt("nid"));
				oib.setNmcid(rs.getInt("nmcid"));
				oib.setNcount(rs.getInt("ncount"));
				oib.setNorderid(rs.getInt("norderid"));
				oib.setNprice(rs.getDouble("nprice"));
				oib.setNtotalprice(rs.getDouble("ntotalprice"));
				oib.setSdescription(rs.getString("sdescription"));
				oib.setSimg(rs.getString("simg"));
				oib.setSmcname(rs.getString("smcname"));

				orderList.add(oib);
			}
			return orderList;
		} catch (SQLException e) {
			e.printStackTrace();
			return orderList;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}

	/**
	 * 根据订单号查询所有订单条目.
	 * 
	 * @param norderid
	 *            订单号.
	 * @return 订单条目集.
	 */
	public List searchOrderItem(long norderid) {

		return searchOrderItem(norderid, -1, -1);

	}

	/**
	 * 添加订单条目.
	 * 
	 * @param orderItemBean
	 *            订单条目对象.
	 * @return 添加是否成功.
	 */
	public boolean insertOrderItem(OrderItemBean orderItemBean) {

		conn = DBTool.getConn();
		PreparedStatement ps = null;

		StringBuffer sql = new StringBuffer("insert into t_order_item");
		sql = sql
				.append("(nid,norderid,nmcid, smcname,sdescription,simg,ncount,nprice,ntotalprice)");
		sql = sql.append("values");
		sql = sql.append("( SEQ_T_ORDER_ITEM.NEXTVAL,?,?,?,?,?,?,?,?)");

		Object[] params = { orderItemBean.getNorderid(),
				orderItemBean.getNmcid(), orderItemBean.getSmcname(),
				orderItemBean.getSdescription(), orderItemBean.getSimg(),
				orderItemBean.getNcount(), orderItemBean.getNprice(),
				orderItemBean.getNtotalprice() };

		try {
			QueryRunner qr = new QueryRunner(DBTool.getDS());
			qr.update(sql.toString(), params);
			return true;
		} catch (SQLException e1) {
			e1.printStackTrace();
			return false;
		}finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (ps != null)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}

	}
}