oraclemctypedao.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.McTypeDAO;
import com.lmh.dao.vo.McTypeBean;

/**
 * @author Kenneth
 * 
 */
public class OracleMcTypeDAO implements McTypeDAO {

	private Connection conn = null;

	/**
	 * 删除类别及子类别;删除类别下所有商品.
	 * 
	 * @param nid
	 *            类别编号.
	 * @return 操作是否成功.
	 */
	public boolean deleteMcType(int nid) {
		conn = DBTool.getConn();
		PreparedStatement ps_t_mc = null;
		PreparedStatement ps_t_mc_type = null;
		String sql_t_mc = "delete from t_mc where nmaxid=" + nid
				+ " or nminid=" + nid;

		String sql_t_mc_type = "delete from t_mc_type where nid=" + nid
				+ " or npid=" + nid;

		try {
			conn.setAutoCommit(false);

			ps_t_mc = conn.prepareStatement(sql_t_mc);
			ps_t_mc_type = conn.prepareStatement(sql_t_mc_type);
			ps_t_mc.executeUpdate();
			ps_t_mc_type.executeUpdate();
			conn.commit();
			return true;
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (Exception es) {

			}
			return false;
		} finally {
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps_t_mc != null) {
				try {
					ps_t_mc.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps_t_mc_type != null) {
				try {
					ps_t_mc_type.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	/**
	 * 添加商品类别.
	 * 
	 * @param mcTypeBean
	 *            商品类别对象.
	 * @return 添加是否成功.
	 */

	public boolean insertMcType(McTypeBean mcTypeBean) {
		conn = DBTool.getConn();
		PreparedStatement ps = null;

		StringBuffer sql = new StringBuffer("insert into t_mc_type");
		sql = sql.append("(nid, sname, npid,norder)");
		sql = sql.append("values");
		sql = sql.append("(SEQ_T_MC_TYPE.NEXTVAL" + ",'"
				+ mcTypeBean.getSname() + "'," + mcTypeBean.getNpid() + ","
				+ mcTypeBean.getNorder() + ")");

		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
	 *            (精确查)商品类别编号(nid).
	 * @param sname
	 *            (模糊查)商品类别名(sname).
	 * @return 商品类别集合.
	 */
	public List searchMcType(int nid, String sname) {
		conn = DBTool.getConn();
		List<McTypeBean> mcTypeList = new ArrayList<McTypeBean>();

		PreparedStatement pstmt = null;
		ResultSet rs = null;

		StringBuffer sql = new StringBuffer();
		sql.append("select * from t_mc_type where 1=1 ");
		if (nid != -1) {
			sql.append(" and nid= " + nid);
		}
		if (sname != null && sname.length() != 0) {
			sql.append(" and sname like '%" + sname + "%'");
		}
		sql.append(" order by npid asc ");

		try {
			pstmt = conn.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			while (rs.next()) {
				McTypeBean mcTypeBean = new McTypeBean();
				mcTypeBean.setNid(rs.getInt(1));
				mcTypeBean.setSname(rs.getString("sname"));
				mcTypeBean.setNpid(rs.getInt("npid"));
				mcTypeBean.setNorder(rs.getInt("norder"));
				mcTypeList.add(mcTypeBean);
			}
			return mcTypeList;
		} catch (SQLException e) {
			e.printStackTrace();
			return mcTypeList;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

	/**
	 * 根据商品类别编号(nid)获取商品类别信息.
	 * 
	 * @param nid
	 *            (精确查)商品类别编号(nid).
	 * @return 商品类别.
	 */
	public McTypeBean searchMcType(int nid) {

		List mcTypeList = searchMcType(nid, null);

		if (mcTypeList.size()!= 0) {
			return (McTypeBean) mcTypeList.get(0);
		} else {
			return null;
		}
	}

	/**
	 * 获取所有商品类别信息.
	 * 
	 * @return 商品类别集合.
	 */
	public List searchMcType() {
		List mcTypeList = null;
		mcTypeList = searchMcType(-1, null);
		return mcTypeList;
	}

	/**
	 * 修改商品(只需根据商品类别id 修改商品类别名即可,不允许修改类别id)
	 * 
	 * @param mcTypeBean
	 *            商品类别对象.
	 * @return 修改是否成功.
	 */
	public boolean updateMcType(McTypeBean mcTypeBean) {
		conn = DBTool.getConn();
		PreparedStatement pstmt = null;
		StringBuffer sql = new StringBuffer();
		sql.append("update  t_mc_type set ");
		sql.append(" npid=" + mcTypeBean.getNpid() + ",");
		sql.append(" norder=" + mcTypeBean.getNorder() + ",");
		sql.append(" sname='" + mcTypeBean.getSname() + "'");
		sql.append(" where nid=" + mcTypeBean.getNid());
		try {
			pstmt = conn.prepareStatement(sql.toString());
			pstmt.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (conn != null)
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

}