oraclemcdao.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.McDAO;
import com.lmh.dao.vo.McBean;

/**
 * @author Kenneth
 * 
 */
public class OracleMcDAO implements McDAO {
	private Connection conn = null;

	/**
	 * 删除商品.
	 * 
	 * @param nid
	 *            商品编号.
	 * @return 是否成功.
	 */
	public boolean deleteMc(int nid) {

		conn = DBTool.getConn();
		PreparedStatement ps = null;
		String sql = "delete from t_mc where nid=" + nid;

		try {
			ps = conn.prepareStatement(sql);
			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 skey
	 *            关键字 (商品名或描述).
	 * @param prstart
	 *            按单价查询 起始单价 (-1:表无此条件).
	 * @param prend
	 *            按单价查询 终止单价 (-1:表无此条件).
	 * @param tymax
	 *            大类 (-1:表无此条件).
	 * @param tymin
	 *            小类 (-1:表无此条件).
	 * @return 总记录数.
	 */
	public int getCount(String skey, double prstart, double prend, int tymax,
			int tymin) {

		conn = DBTool.getConn();

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

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

		if (skey != null && skey.length() != 0) {
			sql.append(" and sname like '%" + skey + "%'");
		}
		if (prstart != -1) {
			sql.append(" and nprice>=" + prstart);
		}
		if (prend != -1) {
			sql.append(" and nprice<=" + prend);
		}
		if (tymax != -1) {
			sql.append(" and nmaxid=" + tymax);
		}
		if (tymin != -1) {
			sql.append(" and nminid=" + tymin);
		}

		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(null, -1, -1, -1, -1);
	}

	/**
	 * 添加商品.
	 * 
	 * @param mcBean
	 *            添加商品对象.
	 * @return 添加是否成功.
	 */
	public boolean insertMc(McBean mcBean) {

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

		StringBuffer sql = new StringBuffer("insert into t_mc");
		sql = sql
				.append("(nid, sname, sdescription,nprice,simg, smctag,dcdate,nmaxid,nminid)");
		sql = sql.append("values");
		sql = sql.append("( SEQ_T_MC.NEXTVAL,");
		sql = sql.append("'" + mcBean.getSname() + "',");
		sql = sql.append("'" + mcBean.getSdescription() + "',");
		sql = sql.append(mcBean.getNprice() + ",");
		sql = sql.append("'" + mcBean.getSimg() + "',");
		if (mcBean.getSmctag() == null) {
			sql = sql.append("' ',");
		} else {
			sql = sql.append("'" + mcBean.getSmctag() + "',");
		}
		sql = sql.append("sysdate,");
		sql = sql.append(mcBean.getNmaxid() + ",");
		sql = sql.append(+mcBean.getNminid() + ")");

		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 nid
	 *            商品编号 (-1:表无此条件).
	 * @param skey
	 *            关键字(模糊查) (商品名或描述).
	 * @param prstart
	 *            按单价查询 起始单价 (-1:表无此条件).
	 * @param prend
	 *            按单价查询 终止单价 (-1:表无此条件).
	 * @param tymax
	 *            大类 (-1:表无此条件).
	 * @param tymin
	 *            小类 (-1:表无此条件).
	 * @param pastart
	 *            记录行号 起始行 (-1:表无此条件).
	 * @param paend
	 *            记录行号 终止行 (-1:表无此条件).
	 * @return 商品集合.
	 */
	public List selectMc(int nid, String skey, double prstart, double prend,
			int tymax, int tymin, int pastart, int paend) {

		conn = DBTool.getConn();

		List<McBean> mcList = new ArrayList<McBean>();

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

		sql.append("select * from ");
		sql.append("(");
		sql.append("select rownum as myRow, nid, sname,");
		sql.append("sdescription,nprice,simg, smctag,");
		sql.append("to_char(dcdate,'yyyy-mm-dd hh24:mi:ss') as dcdate,");
		sql.append("nmaxid,nminid");
		sql.append(" from t_mc ");
		sql.append(" where 1=1 ");
		if (nid != -1) {
			sql.append(" and nid=" + nid);
		}
		if (skey != null && skey.length() != 0) {
			sql.append(" and sname like '%" + skey + "%'");
		}

		if (prstart != -1) {
			sql.append(" and nprice >=" + prstart);
		}
		if (prend != -1) {
			sql.append(" and nprice <=" + prend);
		}
		if (tymax != -1) {
			sql.append(" and nmaxid =" + tymax);
		}
		if (tymin != -1) {
			sql.append(" and nminid =" + tymin);
		}
		if (paend != -1) {
			sql.append(" and rownum <=" + paend);
		}
		sql.append(" )");
		if (pastart != -1) {
			sql.append(" where myRow >= " + pastart);
		}

		try {
			ps = conn.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			while (rs.next()) {

				McBean mcBean = new McBean();

				mcBean.setNid(rs.getInt("nid"));
				mcBean.setSname(rs.getString("sname"));
				mcBean.setNprice(rs.getDouble("nprice"));
				mcBean.setNmaxid(rs.getInt("nmaxid"));
				mcBean.setNminid(rs.getInt("nminid"));
				mcBean.setDcdate(rs.getString("dcdate"));
				mcBean.setSdescription(rs.getString("sdescription"));
				mcBean.setSimg(rs.getString("simg"));
				mcBean.setSmctag(rs.getString("smctag"));

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

	}

	/**
	 * 查询商品详细信息.
	 * 
	 * @param skey
	 *            关键字 (商品名或描述).
	 * @param prstart
	 *            按单价查询 起始单价 (-1:表无此条件).
	 * @param prend
	 *            按单价查询 终止单价 (-1:表无此条件).
	 * @param tymax
	 *            大类 (-1:表无此条件).
	 * @param tymin
	 *            小类 (-1:表无此条件).
	 * @param pastart
	 *            记录行号 起始行 (-1:表无此条件).
	 * @param paend
	 *            记录行号 终止行 (-1:表无此条件).
	 * @return 商品集合.
	 */
	public List selectMc(String skey, double prstart, double prend, int tymax,
			int tymin, int pastart, int paend) {
		return selectMc(-1, skey, prstart, prend, tymax, tymin, pastart, paend);
	}

	/**
	 * 查询商品详细信息.
	 * 
	 * @param nid
	 *            商品编号.
	 * 
	 * @return 商品对象.
	 */
	public McBean selectMc(int nid) {
		List mcList = selectMc(nid, null, -1, -1, -1, -1, -1, -1);

		if (mcList.size() != 0) {
			return (McBean) mcList.get(0);
		} else {
			return null;
		}

	}

	/**
	 * 修改商品.
	 * 
	 * @param mcBean
	 *            商品对象.
	 * @return 修改是否成功.
	 */
	public boolean updateMc(McBean mcBean) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;
		StringBuffer updateStr = new StringBuffer(" Update t_mc set ");

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

		if (mcBean.getSname() != null && mcBean.getSname().length() != 0) {
			updateStr.append(" ,sname = '" + mcBean.getSname() + "'");
		}
		if (mcBean.getSdescription() != null
				&& mcBean.getSdescription().length() != 0) {
			updateStr.append(",sdescription = '" + mcBean.getSdescription()
					+ "'");
		}
		updateStr.append(" ,dcdate = sysdate");
		if (mcBean.getSimg() != null && mcBean.getSimg().length() != 0) {
			updateStr.append(" ,simg = '" + mcBean.getSimg() + "'");
		}
		if (mcBean.getSmctag() != null && mcBean.getSmctag().length() != 0) {
			updateStr.append(" ,smctag = '" + mcBean.getSmctag() + "'");
		}
		if (mcBean.getNprice() != -1) {
			updateStr.append(" ,nprice = " + mcBean.getNprice());
		}
		if (mcBean.getNmaxid() != -1) {
			updateStr.append(" ,nmaxid = " + mcBean.getNmaxid());
		}
		if (mcBean.getNminid() != -1) {
			updateStr.append(",nminid = " + mcBean.getNminid());
		}
		updateStr.append(" where  nid= " + mcBean.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();
				}
		}

	}

}