tproductdao.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.TProduct;
import com.rfid.model.TOrder;

public class TProductDAO {
	/**
	 * 将product信息插入数据库t_products表中
	 * @param order
	 * @return
	 */
	public int insert(TProduct product) {
		
		int result = 0;
		Connection conn = DbUtil.getConnection();
		PreparedStatement stmt = null;
		
		String sqlStr = "INSERT INTO  t_products(order_id,rfid_id,name,product_time) VALUES (?,?,?,?)";

		try {
			stmt = conn.prepareStatement(sqlStr);
			conn.setAutoCommit(false);
			stmt.setInt(1, product.getOrder_id());
			stmt.setString(2,product.getRfid_id());
			
			stmt.setString(3, product.getName());
			
			//stmt.setTimestamp(4, Timestamp.valueOf(product.getProduct_time().toLocaleString()));
			stmt.setDate(4, (java.sql.Date)product.getProduct_time());
			System.out.println(product.getOrder_id());
			System.out.println(product.getRfid_id());
			System.out.println(product.getName());
			System.out.println((java.sql.Date)product.getProduct_time());
			System.out.println("eeeeeeeee");
			result = stmt.executeUpdate();
			System.out.println(result);
			System.out.println("ddddddd");
			
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return result;
	}
	public int update(TOrder order){
		int result = 0;
		Connection conn = DbUtil.getConnection();
		PreparedStatement stmt = null;
	
		String sqlStr = "update t_order set status=? where id=" + order.getId();

		try {
			stmt = conn.prepareStatement(sqlStr);
			conn.setAutoCommit(false);

			stmt.setInt(1, order.getStatus());

			result = stmt.executeUpdate();
			conn.commit();
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		return result;
	}
	

	public List getProduct() {
		List list = new ArrayList();

		String sqlStr = "select * from t_products 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) {
				TProduct product  = null;
				while (rs.next()) {
					product = new TProduct();
					product.setId(rs.getInt("rfid_id"));
					product.setProduct_time(rs.getDate("product_time"));
					product.setInStore_id(rs.getInt("instore_id"));
					product.setOutStore_id(rs.getInt("outstore_id"));
					product.setName(rs.getString("name"));
					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_products 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) {
				TProduct product  = null;
				while (rs.next()) {
					product = new TProduct();
					product.setId(rs.getInt("id"));
					product.setProduct_time(rs.getDate("product_time"));
					product.setInStore_id(rs.getInt("instore_id"));
					product.setOutStore_id(rs.getInt("outstore_id"));
					product.setName(rs.getString("name"));
					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 searchByRFID(String rfid){
		List list = new ArrayList();

		String sqlStr = "select * from t_products where rfid_id = '" + rfid + "'";
		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) {
				TProduct product  = null;
				while (rs.next()) {
					product = new TProduct();
					product.setId(rs.getInt("id"));
					product.setRfid_id(rfid);
					product.setProduct_time(rs.getDate("product_time"));
					product.setInStore_id(rs.getInt("instore_id"));
					product.setOrder_id(rs.getInt("order_id"));
					product.setOutStore_id(rs.getInt("outstore_id"));
					product.setName(rs.getString("name"));
					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 static void main(String args[]){
		System.out.println(new TInStoreDAO().searchByName("1").size());
	}

}