toutstoredao.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 com.rfid.global.DbUtil;
import com.rfid.global.Global;
import com.rfid.model.TGetProducts;
import com.rfid.model.TOutStore;

public class TOutStoreDAO {
	public List getOutStore() {
		List list = new ArrayList();

		String sqlStr = "select * from t_outstore 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) {
				TOutStore outstore = null;
				while (rs.next()) {
					outstore = new TOutStore();
					outstore.setId(rs.getInt("id"));
					outstore.setName(rs.getString("name"));
					outstore.setNumber(rs.getInt("number"));
					outstore.setInputtime(rs.getDate("input_time"));
					outstore.setOrderid(rs.getInt("order_id"));
					outstore.setInputuser(rs.getInt("input_user"));
					outstore.setEndplace(rs.getString("end_place"));
					list.add(outstore);
				}
			}
		} 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_outstore 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) {
				TOutStore outstore = null;
				while (rs.next()) {
					outstore = new TOutStore();
					outstore.setId(rs.getInt("id"));
					outstore.setName(rs.getString("name"));
					outstore.setNumber(rs.getInt("number"));
					outstore.setInputtime(rs.getDate("input_time"));
					outstore.setOrderid(rs.getInt("order_id"));
					outstore.setInputuser(rs.getInt("input_user"));
					outstore.setEndplace(rs.getString("end_place"));
					list.add(outstore);
				}
			}
		} 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 searchByID(int id) {
		List list = new ArrayList();

		String sqlStr = "select * from t_outstore where id = " + 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) {
				TOutStore outstore = null;
				while (rs.next()) {
					outstore = new TOutStore();
					outstore.setId(rs.getInt("id"));
					outstore.setName(rs.getString("name"));
					outstore.setNumber(rs.getInt("number"));
					outstore.setInputtime(rs.getDate("input_time"));
					outstore.setOrderid(rs.getInt("order_id"));
					outstore.setInputuser(rs.getInt("input_user"));
					outstore.setEndplace(rs.getString("end_place"));
					list.add(outstore);
				}
			}
		} 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 int outstore(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;
	}
}