www.pudn.com > RFID_98798798698698.rar > TStoreProductsDAO.java

package com.rfid.dao; 
 
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Timestamp; 
import java.util.ArrayList; 
import java.util.Date; 
import java.util.List; 
 
import org.apache.struts.action.ActionForward; 
 
import com.rfid.global.DbUtil; 
import com.rfid.global.Global; 
import com.rfid.model.TOrder; 
import com.rfid.model.TStoreProducts; 
 
public class TStoreProductsDAO { 
	public List getProduct(){ 
		List list = new ArrayList(); 
 
		String sqlStr = "select * from t_storeproducts order by id"; 
		Connection conn = DbUtil.getConnection(); 
		PreparedStatement stmt = null; 
		ResultSet rs = null; 
		try { 
			stmt = conn.prepareStatement(sqlStr); 
			conn.setAutoCommit(false); 
			rs = stmt.executeQuery(); 
			conn.commit(); 
 
			/* 遍历将rs中的结果插入list中返回 */ 
			if (rs != null) { 
				TStoreProducts product = null; 
				while (rs.next()) { 
					product = new TStoreProducts(); 
					product.setId(rs.getInt("id")); 
					product.setName(rs.getString("name")); 
					product.setNumber(rs.getInt("number")); 
					list.add(product); 
				} 
			} 
		} catch (SQLException e) { 
			// TODO Auto-generated catch block 
			e.printStackTrace(); 
		} finally { 
			try { 
				rs.close(); 
				stmt.close(); 
				conn.close(); 
			} catch (SQLException e) { 
				// TODO Auto-generated catch block 
				e.printStackTrace(); 
			} 
		} 
		return list; 
	} 
	 
	public List searchByName(String name){ 
		List list = new ArrayList(); 
 
		String sqlStr = "select * from t_storeproducts where name like '%" + name + "%' order by id"; 
		Connection conn = DbUtil.getConnection(); 
		PreparedStatement stmt = null; 
		ResultSet rs = null; 
		try { 
			stmt = conn.prepareStatement(sqlStr); 
			conn.setAutoCommit(false); 
			rs = stmt.executeQuery(); 
			conn.commit(); 
 
			/* 遍历将rs中的结果插入list中返回 */ 
			if (rs != null) { 
				TStoreProducts product = null; 
				while (rs.next()) { 
					product = new TStoreProducts(); 
					product.setId(rs.getInt("id")); 
					product.setName(rs.getString("name")); 
					product.setNumber(rs.getInt("number")); 
					list.add(product); 
				} 
			} 
		} catch (SQLException e) { 
			// TODO Auto-generated catch block 
			e.printStackTrace(); 
		} finally { 
			try { 
				rs.close(); 
				stmt.close(); 
				conn.close(); 
			} catch (SQLException e) { 
				// TODO Auto-generated catch block 
				e.printStackTrace(); 
			} 
		} 
		return list; 
	} 
	 
	/** 
	 * 插入产品 
	 * @param name 
	 * @param number 
	 * @return 
	 */ 
	public int insert(String name , int number , int orderid , int userid){ 
		int result = 0; 
		 
		String sqlStr1 = "select * from t_storeproducts where name='" + name + "'"; 
		String sqlStr2 = ""; 
		Connection conn = DbUtil.getConnection(); 
		PreparedStatement stmt = null; 
		ResultSet rs = null; 
		try { 
			stmt = conn.prepareStatement(sqlStr1); 
			conn.setAutoCommit(false); 
			rs = stmt.executeQuery(); 
			conn.commit(); 
 
			if(rs.next()){ 
				/* 数据库中已经有该种酒 */ 
				sqlStr2 = "update t_storeproducts set number=number+" + number + " where id=" + rs.getInt("id"); 
			} else{ 
				/* 数据库中还没有这种酒 */ 
				sqlStr2 = "insert into t_storeproducts(name,number) values('"+name+"',"+number+")"; 
			} 
			 
			/*填写入库单*/ 
			String sqlStr3 = "insert into t_instore(order_id,name,number,input_user,input_time)" + 
					" values(?,?,?,?,?)"; 
			/* 刷新订单的状态 */ 
			String sqlStr4 = "update t_order set status="+Global.ORDER_STATUS_WAREHOUSE2+" where id=" + orderid; 
			 
			stmt = conn.prepareStatement(sqlStr2); 
			conn.setAutoCommit(false); 
			result = stmt.executeUpdate(); 
			 
			stmt = conn.prepareStatement(sqlStr3); 
 
			stmt.setInt(1, orderid); 
			stmt.setString(2, name); 
			stmt.setInt(3, number); 
			stmt.setInt(4, userid); 
			stmt.setTimestamp(5, Timestamp.valueOf(new Date().toLocaleString())); 
			 
			 
			result = stmt.executeUpdate(); 
			 
			 
			stmt = conn.prepareStatement(sqlStr4); 
			result = stmt.executeUpdate(); 
			conn.commit(); 
		} catch (SQLException e) { 
			// TODO Auto-generated catch block 
			e.printStackTrace(); 
		} finally { 
			try { 
				rs.close(); 
				stmt.close(); 
				conn.close(); 
			} catch (SQLException e) { 
				// TODO Auto-generated catch block 
				e.printStackTrace(); 
			} 
		} 
		 
		return result; 
	} 
	 
	public int outProduct(String name , int number , int orderid ,int userid){ 
		int result = 0; 
		 
		/** 
		 * 首先查询数据库中是否有足够的该种产品 
		 * 有的话减去该数量产品,同时填写出库单,提货单中去掉相应记录 
		 * 没有的话直接result=0返回 
		 */ 
		String sqlStr1 = "select number from t_storeproducts where name='" + name + "' and number>=" +number ; 
		Connection conn = DbUtil.getConnection(); 
		PreparedStatement stmt = null; 
		ResultSet rs = null; 
		try { 
			stmt = conn.prepareStatement(sqlStr1); 
			conn.setAutoCommit(false); 
			rs = stmt.executeQuery(); 
			conn.commit(); 
		} catch (SQLException e) { 
			// TODO Auto-generated catch block 
			e.printStackTrace(); 
		} 
		 
		try { 
			if((rs != null) && (rs.next())){ 
				/* 有足够的产品 */ 
				int true_number = rs.getInt(1); 
				/* 刷新库中产品的数量 */ 
				String sqlStr2 = ""; 
				if(true_number > number){ 
					true_number -= number;  
					sqlStr2 = "update t_storeproducts set number=" + true_number + " where name='" + name + "'"; 
				}else{ 
					sqlStr2 = "delete from t_storeproducts where name='"+name+"'"; 
				} 
				stmt = conn.prepareStatement(sqlStr2); 
				conn.setAutoCommit(false); 
				result = stmt.executeUpdate(); 
				 
				System.out.println(result); 
				 
				/* 删除相应的提货单记录 */ 
				String sqlStr3 = "delete from t_getproducts where order_id ="+ orderid; 
				stmt = conn.prepareStatement(sqlStr3); 
				conn.setAutoCommit(false); 
				result = stmt.executeUpdate(); 
 
				System.out.println(result); 
				/* 添加出库记录 */ 
				String sqlStr4 = "insert into t_outstore(order_id,name,number,input_user,input_time,end_place) values(?,?,?,?,?,?)"; 
				stmt = conn.prepareStatement(sqlStr4); 
				stmt.setInt(1, orderid); 
				stmt.setString(2, name); 
				stmt.setInt(3, number); 
				stmt.setInt(4, userid); 
				stmt.setTimestamp(5, Timestamp.valueOf(new Date().toLocaleString())); 
				stmt.setString(6, ""); 
				result = stmt.executeUpdate(); 
 
				System.out.println(result); 
				/* 刷新订单的状态 */ 
				String sqlStr5 = "update t_order set status=" + Global.ORDER_STATUS_OUT + " where id=" + orderid; 
				stmt = conn.prepareStatement(sqlStr5); 
				conn.setAutoCommit(false); 
				result = stmt.executeUpdate(); 
 
				System.out.println(result); 
				conn.commit(); 
			} 
		} catch (SQLException e) { 
			// TODO Auto-generated catch block 
			e.printStackTrace(); 
		} finally { 
			try { 
				rs.close(); 
				stmt.close(); 
				conn.close(); 
			} catch (SQLException e) { 
				// TODO Auto-generated catch block 
				e.printStackTrace(); 
			} 
		} 
		 
		return result; 
	} 
	 
	public boolean isEnough(String name,int num){ 
		List list; 
		boolean enough = false; 
		TStoreProducts product = null; 
	 
		 
		if (searchByName(name) == null) 
			enough = false; 
		else 
		{ 
			try{ 
				list = searchByName(name);			   
	            product = (TStoreProducts)list.get(0); 
		        if (product.getNumber() >= num) 
			        enough = true; 
		        else enough = false; 
		        } 
		    catch (Exception e) 
		    { 
			    e.printStackTrace(); 
		    } 
	    } 
		return enough; 
	} 
	 
	public static void main(String args[]){ 
		System.out.println(new TStoreProductsDAO().outProduct("3", 1, 1, 1)); 
	} 
}