oracleorderdao.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 com.lmh.dao.db.DBTool;
import com.lmh.dao.inf.OrderDAO;
import com.lmh.dao.vo.OrderBean;

public class OracleOrderDAO implements OrderDAO {
	private Connection conn = null;

	/**
	 * 获取订单编号.
	 * 
	 * @return 订单编号.
	 */
	public long getOrderId() {
		conn = DBTool.getConn();
		long num = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sql = "select SEQ_T_ORDER.NEXTVAL as orderId from dual";

		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				num = rs.getLong("orderId");
			}
			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 nid
	 *            订单编号 (-1:表无此条件).
	 * @param scuser
	 *            下单人(精确查) (null:表无此条件).
	 * @param sstatus
	 *            审核状态 (null:表无此条件).
	 * @param sauser
	 *            审核人(精确查) (null:表无此条件).
	 * @param smcname
	 *            商品名(模糊查) (null:表无此条件).
	 * @return 订单总数.
	 */
	public int getCount(long nid, String scuser, String sstatus, String sauser,
			String smcname) {

		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 where 1=1");

		if (nid != -1) {
			sql.append(" and nid = " + nid);
		}
		if (scuser != null && !scuser.equals("")) {
			sql.append(" and scuser  like '%" + scuser + "%'");
		}
		if (smcname != null && !smcname.equals("")) {
			sql
					.append(" and nid in (select norderid from t_order_item where smcname like '%"
							+ smcname + "%')");
		}
		if (sstatus != null && !sstatus.equals("")) {
			sql.append(" and sstatus  like '%" + sstatus + "%'");
		}
		if (sauser != null && !sauser.equals("")) {
			sql.append(" and sauser like '%" + sauser + "%'");
		}
		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();
				}
		}

	}

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

	/**
	 * 添加订单.
	 * 
	 * @param orderBean
	 *            订单对象.
	 * @return 添加是否成功.
	 */
	public boolean insertOrder(OrderBean orderBean) {

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

		StringBuffer sql = new StringBuffer("insert into t_order ");
		sql = sql.append("( nid,scuser, dgdate,spaytype,ssendtype,");
		sql = sql.append("nmctypesize,nmcsize,ntotalprice,sstatus,");
		sql = sql.append("smsg,sauser, ssname, ssaddress,");
		sql = sql.append("sscode, ssphone,ssemail)");
		sql = sql.append("values");
		sql = sql.append("( SEQ_T_ORDER.NEXTVAL,'");
		sql = sql.append(orderBean.getScuser() + "',");
		if (orderBean.getDgdate() != null
				&& orderBean.getDgdate().length() != 0) {
			sql = sql.append("to_date('" + orderBean.getDgdate()
					+ "','yyyy-mm-dd'),'");
		} else {
			sql = sql.append("sysdate,'");
		}
		sql = sql.append(orderBean.getSpaytype() + "','"
				+ orderBean.getSsendtype() + "',");
		sql = sql.append(orderBean.getNmctypesize() + ","
				+ orderBean.getNmcsize() + ",");
		sql = sql.append(orderBean.getNtotalprice() + ",'"
				+ orderBean.getSstatus() + "','");
		sql = sql.append(orderBean.getSmsg() + "','" + orderBean.getSauser()
				+ "','");

		sql = sql.append(orderBean.getSsname() + "','");
		sql = sql.append(orderBean.getSsaddress() + "','"
				+ orderBean.getSscode() + "','");
		sql = sql.append(orderBean.getSsphone() + "','"
				+ orderBean.getSsemail() + "')");

		try {
			ps = conn.prepareStatement(sql.toString());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.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();
				}
		}
	}

	/**
	 * 修改订单.
	 * 
	 * @param orderBean
	 *            订单对象.
	 * @return 添加是否成功.
	 */
	public boolean updateOrder(OrderBean orderBean) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;

		StringBuffer updateStr = new StringBuffer(" Update t_order set ");

		updateStr.append(" nid = " + orderBean.getNid());

		if (orderBean.getDgdate() != null) {
			updateStr.append(",dgdate = sysdate ");
		}
		if (orderBean.getSsname() != null) {
			updateStr.append(",ssname = '" + orderBean.getSsname() + "'");
		}
		if (orderBean.getSsaddress() != null) {
			updateStr.append(",ssaddress = '" + orderBean.getSsaddress() + "'");
		}
		if (orderBean.getSscode() != null) {
			updateStr.append(",sscode = '" + orderBean.getSscode() + "'");
		}
		if (orderBean.getSsphone() != null) {
			updateStr.append(",ssphone = '" + orderBean.getSsphone() + "'");
		}
		if (orderBean.getSsemail() != null) {
			updateStr.append(",ssemail = '" + orderBean.getSsemail() + "'");
		}
		if (orderBean.getSsendtype() != null) {
			updateStr.append(",ssendtype = '" + orderBean.getSsendtype() + "'");
		}
		if (orderBean.getSpaytype() != null) {
			updateStr.append(",spaytype = '" + orderBean.getSpaytype() + "'");
		}
		updateStr.append(" where  nid= " + orderBean.getNid());

		try {
			ps = conn.prepareStatement(updateStr.toString());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.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();
				}
		}

	}

	/**
	 * 订单审核.
	 * 
	 * @param orderBean
	 *            订单对象.
	 * 
	 * @return 操作是否成功.
	 */
	public boolean auditingOrder(OrderBean orderBean) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;

		StringBuffer updateStr = new StringBuffer(" Update t_order set ");
		
		updateStr.append(" nid = " + orderBean.getNid());
		
		if (orderBean.getSstatus() != null) {
			updateStr.append(",sstatus = '" + orderBean.getSstatus() + "'");
		}
		if (orderBean.getSmsg() != null) {
			updateStr.append(" ,smsg = '" + orderBean.getSmsg() + "'");
		}

		if (orderBean.getSauser() != null) {
			updateStr.append(" ,sauser = '" + orderBean.getSauser() + "'");
		}
		if (orderBean.getSstatus() != null
				&& !orderBean.getSstatus().equals("1")) {
			updateStr.append(" ,dadate = sysdate ");
		}
		updateStr.append(" where  nid= " + orderBean.getNid());

		try {
			ps = conn.prepareStatement(updateStr.toString());
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.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();
				}
		}

	}

	/**
	 * 获取所有订单.
	 * 
	 * @param nid
	 *            订单编号 (-1:表无此条件).
	 * @param scuser
	 *            下单人(精确查) (null:表无此条件).
	 * @param sstatus
	 *            审核状态 (null:表无此条件).
	 * @param sauser
	 *            审核人(精确查) (null:表无此条件).
	 * @param smcname
	 *            商品名(模糊查) (null:表无此条件).
	 * @param pastart
	 *            记录 开始行 (-1:表无此条件).
	 * @param paend
	 *            记录 结束行 (-1:表无此条件).
	 * @param startDate
	 *            (小值)下订日期 (null:表无此条件).
	 * @param endDate
	 *            (大值)下订日期 (null:表无此条件).
	 * 
	 * @return 订单集合.
	 */
	public List searchOrder(long nid, String scuser, String sstatus,
			String sauser, String smcname, String startDate, String endDate,
			int pastart, int paend) {

		conn = DBTool.getConn();

		List<OrderBean> obList = new ArrayList<OrderBean>();

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

		allStr.append("select * from ");
		allStr.append("(");
		allStr.append("select rownum as myRow,nid,scuser,");
		allStr.append("to_char(dgdate,'yyyy-mm-dd') as dgdate,");
		allStr.append("spaytype,ssendtype,nmctypesize,");
		allStr.append("nmcsize,ntotalprice,sstatus,smsg,sauser,");
		allStr.append("to_char(dadate,'yyyy-mm-dd') as dadate,");
		allStr.append(" ssname, ssaddress, sscode, ssphone,ssemail ");
		allStr.append("	from t_order ");
		allStr.append(" where 1=1 ");

		if (nid != -1) {
			allStr.append(" and nid = " + nid);
		}
		if (scuser != null && !scuser.equals("")) {
			allStr.append(" and scuser ='" + scuser+"'");
		}
		if (smcname != null && !smcname.equals("")) {
			allStr
					.append(" and nid in (select norderid from t_order_item where smcname like '%"
							+ smcname + "%')");
		}
		if (sstatus != null && !sstatus.equals("")) {
			allStr.append(" and sstatus  =" + sstatus);
		}
		if (sauser != null && !sauser.equals("")) {
			allStr.append(" and sauser ='" + sauser+"'");
		}
		if (startDate != null && !startDate.equals("")) {
			allStr.append(" and dgdate >=to_date('" + startDate
					+ "','yyyy-mm-dd')");
		}
		if (endDate != null && !endDate.equals("")) {
			allStr.append(" and dgdate <=to_date('" + endDate
					+ "','yyyy-mm-dd')");
		}

		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()) {
				OrderBean ob = new OrderBean();

				ob.setNid(rs.getInt("nid"));
				ob.setScuser(rs.getString("scuser"));
				ob.setDgdate(rs.getString("dgdate"));
				ob.setSpaytype(rs.getString("spaytype"));
				ob.setSsendtype(rs.getString("ssendtype"));
				ob.setNmcsize(rs.getInt("nmcsize"));
				ob.setNmctypesize(rs.getInt("nmctypesize"));
				ob.setNtotalprice(rs.getDouble("ntotalprice"));
				ob.setSstatus(rs.getString("sstatus"));
				ob.setSmsg(rs.getString("smsg"));
				ob.setSauser(rs.getString("sauser"));
				ob.setDadate(rs.getString("dadate"));
				ob.setSsname(rs.getString("ssname"));
				ob.setSsaddress(rs.getString("ssaddress"));
				ob.setSscode(rs.getString("sscode"));
				ob.setSsphone(rs.getString("ssphone"));
				ob.setSsemail(rs.getString("ssemail"));

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

	}

	/**
	 * 获取所有订单.
	 * 
	 * @param nid
	 *            订单编号 (-1:表无此条件).
	 * @param scuser
	 *            下单人(精确查) (null:表无此条件).
	 * @param sstatus
	 *            审核状态 (null:表无此条件).
	 * @param sauser
	 *            审核人(精确查) (null:表无此条件).
	 * @param smcname
	 *            商品名(模糊查) (null:表无此条件).
	 * @return 订单集合.
	 */
	public List searchOrder(long nid, String scuser, String sstatus,
			String sauser, String smcname) {
		return searchOrder(nid, scuser, sstatus, sauser, smcname, null, null,
				-1, -1);
	}

	/**
	 * 获取所有订单.
	 * 
	 * @param scuser
	 *            下单人(精确查) (null:表无此条件).
	 * @param sstatus
	 *            审核状态 (null:表无此条件).
	 * @param pastart
	 *            记录 开始行 (-1:表无此条件).
	 * @param paend
	 *            记录 结束行 (-1:表无此条件).
	 * 
	 * @return 订单集合.
	 */
	public List searchOrder(String scuser, String sstatus, int pastart,
			int paend) {
		return searchOrder(-1, scuser, sstatus, null, null, null, null,
				pastart, paend);
	}

	/**
	 * 获取所有订单.
	 * 
	 * @param scuser
	 *            下单人(精确查) (null:表无此条件).
	 * @param sstatus
	 *            审核状态 (null:表无此条件).
	 * @return 订单集合.
	 */
	public List searchOrder(String scuser, String sstatus) {
		return searchOrder(-1, scuser, sstatus, null, null, null, null, -1, -1);
	}

	/**
	 * 获取订单对象.
	 * 
	 * @param nid
	 *            订单编号.
	 * @return 订单对象.
	 */
	public OrderBean searchOrder(long nid) {
		List obList = searchOrder(nid, null, null, null, null, null, null, -1,
				-1);

		if (obList.size() != 0) {
			return (OrderBean) obList.get(0);
		} else {
			return null;
		}
	}

	/**
	 * 获取所有订单.
	 * 
	 * @return 订单集合.
	 */
	public List searchOrder() {
		return searchOrder(-1, null, null, null, null, null, null, -1, -1);
	}

}