tstoreproductsdao.java

jsp网页

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));
	}
}