itemdao4oracleimpl.java

用java开发的一个企业的DRP系统源码

package com.bjsxt.drp.basedata.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.bjsxt.drp.basedata.dao.ItemDao;
import com.bjsxt.drp.basedata.model.Item;
import com.bjsxt.drp.exception.AppException;
import com.bjsxt.drp.util.DB;
import com.bjsxt.drp.util.PageModel;
import com.bjsxt.drp.util.datadict.ItemCategory;
import com.bjsxt.drp.util.datadict.ItemUnit;

/**
 * 物料维护Oracle实现
 * @author Administrator
 *
 */
public class ItemDao4OracleImpl implements ItemDao {

	/**
	 * 添加物料
	 * @param conn
	 * @param item Item
	 */
	public void addItem(Connection conn, Item item) {
		String sql = "insert into t_items(item_no, item_name, spec, pattern, category, unit) " +
				"values(?, ?, ?, ?, ?, ?) ";
		System.out.println("ItemDao4OracleImpl->>addItem()-sql=" + sql);
		PreparedStatement pstmt = null;
		try {
			if (findItemById(item.getItemNo()) != null) {
				throw new AppException("物料代码[" + item.getItemNo() + "]存在！");
			}
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, item.getItemNo());
			pstmt.setString(2, item.getItemName());
			pstmt.setString(3, item.getSpec());
			pstmt.setString(4, item.getPattern());
			pstmt.setString(5, item.getCategory().getId());
			pstmt.setString(6, item.getUnit().getId());
			pstmt.executeUpdate();
		}catch(SQLException e) {
			System.out.println("增加物料失败," + e);
			throw new AppException("增加物料失败！");
		}finally {
			DB.closeStmt(pstmt);
		}
	}

	/**
	 * 根据id删除物料
	 * @param conn
	 * @param itemNo 物料代码
	 */
	public void deleteItemById(Connection conn, String itemNo) {
		// TODO Auto-generated method stub

	}

	/**
	 * 分页查询物料信息
	 * @param pageNo 第几页
	 * @param pageSize 每页多少条
	 * @return
	 */
	public PageModel findAllItem(int pageNo, int pageSize, String queryStr) {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * 根据Id查询物料信息
	 * 
	 * 关于类似t_items表，多个字段关联一个表可以采用多种方式进行查询,我们采用sql嵌套方式实现
	 * 
	 * @param itemNo 物料代码
	 * @return
	 */
	public Item findItemById(String itemNo) {
		//方法一：
		StringBuffer sbfSql = new StringBuffer();
		sbfSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category, ")
		.append("(select b.name from t_data_dict b where b.id=a.category) as category_name, ")
		.append("a.unit, (select c.name from t_data_dict c where c.id=a.unit) as unit_name ")
		.append("from t_items a  where a.item_no=?");
		
//方法二：		
//		sbfSql.append("select a.item_no, a.item_name, a.spec, a.pattern, b.id as category_id, ")
//			  .append("b.name as category_name, c.id as unit_id, c.name as unit_name ")
//			  .append("from t_items a, t_data_dict b, t_data_dict c ")
//			  .append(" where a.category=b.id and a.unit=c.id and a.item_no=? ");
		
		System.out.println("ItemDao4OracleImpl->>findItemById()-sql=" + sbfSql.toString());
		PreparedStatement pstmt = null;
		Connection conn = null;
		ResultSet rs = null;
		Item item = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sbfSql.toString());
			pstmt.setString(1, itemNo);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				item = new Item();
				item.setItemNo(rs.getString(1));
				item.setItemName(rs.getString(2));
				item.setSpec(rs.getString(3));
				item.setPattern(rs.getString(4));
				ItemCategory category = new ItemCategory();
				category.setId(rs.getString(5));
				category.setName(rs.getString(6));
				item.setCategory(category);
				ItemUnit unit = new ItemUnit();
				unit.setId(rs.getString(7));
				unit.setName(rs.getString(8));
				item.setUnit(unit);
			}
		} catch (SQLException e) {
			System.out.println("根据物料代码[" + itemNo + "]查询出错"+ e);
			throw new AppException("根据物料代码[" + itemNo + "]查询出错!");
		} finally {
			DB.closeRs(rs);
			DB.closeStmt(pstmt);
			DB.closeConn(conn);
		}
		return item;	
	}

	/**
	 * 修改物料
	 * @param conn
	 * @param item Item
	 */
	public void modifyItem(Connection conn, Item item) {
		// TODO Auto-generated method stub

	}

}