itemdao4mysqlimpl.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 java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

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;

/**
 * 物料维护MySql实现
 * @author Administrator
 * 
 */
public class ItemDao4MySqlImpl 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("ItemDao4MySqlImpl->>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) {
		String sql = "delete from t_items where item_no=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			//判断物料是否重复
			if (findItemById(itemNo) == null) {
				throw new AppException("删除的物料不存在，代码[" + itemNo + "]");
			}
			pstmt.setString(1, itemNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("删除物料失败-->>" + e);
			throw new AppException("删除物料失败");
		} finally {
			DB.closeStmt(pstmt);
		}
	}

	/**
	 * 分页查询物料信息
	 * @param pageNo 第几页
	 * @param pageSize 每页多少条
	 * @return
	 */
	public PageModel findAllItem(int pageNo, int pageSize, String queryStr) {
		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  ");
//		if (queryStr != null && queryStr.length() != 0) {
//			sbfSql.append("where a.item_no like '" + queryStr + "%' or a.item_name like '" + queryStr + "%' ");
//		}	
//		sbfSql.append(" order by 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 ");

		if (queryStr != null && queryStr.trim().length() != 0) {
			sbfSql.append("and (a.item_no like '" + queryStr + "%' or a.item_name like '" + queryStr + "%') ");
		}	
		sbfSql.append("order by a.item_no ")
		.append("limit ")
		.append((pageNo-1) * pageSize)
		.append(", ")
		.append(pageSize);
		System.out.println("ItemDao4MySqlImpl->>findAllItem()->>sql->>" + sbfSql.toString());
		Statement stmt = null;
		Connection conn = null;
		ResultSet rs = null;
		PageModel pageModel = null;
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sbfSql.toString());
			List itemList = new ArrayList(); 
			while (rs.next()) {
				Item 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);
				itemList.add(item);
			}
			int totalRecords = getTotalRecord(conn, queryStr);
			pageModel = new PageModel();
			pageModel.setPageNo(pageNo);
			pageModel.setPageSize(pageSize);
			pageModel.setTotalRecords(totalRecords);
			pageModel.setList(itemList);
		} catch (SQLException e) {
			System.out.println("查询物料失败-->>" + e);
			throw new AppException("查询物料失败");
		} finally {
			DB.closeRs(rs);
			DB.closeStmt(stmt);
			DB.closeConn(conn);
		}
		return pageModel;
	}

	private int getTotalRecord(Connection conn, String queryStr) {
		String sql = "select count(*) from t_items ";
		if (queryStr != null && queryStr.length() != 0) {
			sql += "where item_no like '" + queryStr + "%' or item_name like '" + queryStr + "%' ";
		}
		Statement stmt = null;
		ResultSet rs = null;
		int totalRecords = 0;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				totalRecords = rs.getInt(1);
			}
		}catch(SQLException e) {
			System.out.println("取得记录总数失败-->>" + e);
			throw new AppException("查询物料失败");
		}finally {
			try {
				if (rs != null) {rs.close();}
				if (stmt != null) {stmt.close();}
			}catch(SQLException e) {}
		}
		return totalRecords;
	}	
	/**
	 * 根据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("ItemDao4MySqlImpl->>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) {
		String sql = "update t_items set item_name=?, spec=?, pattern=?, category=?, unit=? " +
				"where item_no=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, item.getItemName());
			pstmt.setString(2, item.getSpec());
			pstmt.setString(3, item.getPattern());
			pstmt.setString(4, item.getCategory().getId());
			pstmt.setString(5, item.getUnit().getId());
			pstmt.setString(6, item.getItemNo());
			pstmt.executeUpdate();
		}catch(SQLException e) {
			System.out.println("ItemDao4MySqlImpl.modifyItem() failure" + e);
			throw new AppException("修改物料失败,代码[" + item.getItemNo() + "]");
		}finally {
			DB.closeStmt(pstmt);
		}
	}

	
}