tinstoredao.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.List;

import com.rfid.global.DbUtil;
import com.rfid.model.TInStore;
import com.rfid.model.TOrder;

public class TInStoreDAO {
	/**
	 * 将instore信息插入数据库t_instore表中
	 * @param order
	 * @return
	 */
	public int insert(TInStore instore) {
		int result = 0;
		Connection conn = DbUtil.getConnection();
		PreparedStatement stmt = null;
		
		String sqlStr = "insert into t_instore(order_id,name,number,input_time,input_user) ";
		sqlStr += "values (?,?,?,?,?,?,?,?)";

		try {
			stmt = conn.prepareStatement(sqlStr);
			conn.setAutoCommit(false);
			stmt.setInt(1, instore.getOrder_id());
			stmt.setString(2, instore.getName());
			stmt.setInt(3, instore.getNumber());
			
			stmt.setTimestamp(4, Timestamp.valueOf(instore.getInput_time().toLocaleString()));
			
			stmt.setInt(5, instore.getInput_user());

			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 getInStore() {
		List list = new ArrayList();

		String sqlStr = "select * from t_instore 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) {
				TInStore instore = null;
				while (rs.next()) {
					instore = new TInStore();
					instore.setId(rs.getInt("id"));
					instore.setInput_time(rs.getDate("input_time"));
					instore.setInput_user(rs.getInt("input_user"));
					instore.setName(rs.getString("name"));
					instore.setNumber(rs.getInt("number"));
					list.add(instore);
				}
			}
		} 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_instore 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) {
				TInStore instore = null;
				while (rs.next()) {
					instore = new TInStore();
					instore.setId(rs.getInt("id"));
					instore.setInput_time(rs.getDate("input_time"));
					instore.setInput_user(rs.getInt("input_user"));
					instore.setName(rs.getString("name"));
					instore.setNumber(rs.getInt("number"));
					list.add(instore);
				}
			}
		} 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_instore 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) {
				TInStore instore = null;
				while (rs.next()) {
					instore = new TInStore();
					instore.setId(rs.getInt("id"));
					instore.setInput_time(rs.getDate("input_time"));
					instore.setInput_user(rs.getInt("input_user"));
					instore.setName(rs.getString("name"));
					instore.setNumber(rs.getInt("number"));
					list.add(instore);
				}
			}
		} 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());
	}

}