torderdao.java

jsp网页

/**
 * T_Order表操作类
 * @description:封装对t_order的相关操作
 * @author:严伟
 * @lastmodyfied:2006/11/03 by 严伟
 */
package com.rfid.dao;

import java.io.UnsupportedEncodingException;
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.model.TOrder;
import com.rfid.model.TStoreProducts;
import com.rfid.global.*;

public class TOrderDAO {
	
	/**
	 * 将order信息插入数据库t_order表中
	 * @param order
	 * @return
	 */
	public int insert(TOrder order) {
		int result = 0;
		Connection conn = DbUtil.getConnection();
		PreparedStatement stmt = null;
		
		String sqlStr = "insert into t_order(name,number,last_time,remark,category,input_user,input_time,status) ";
		sqlStr += "values (?,?,?,?,?,?,?,?)";

		try {
			stmt = conn.prepareStatement(sqlStr);
			conn.setAutoCommit(false);
			stmt.setString(1, order.getName());
			stmt.setInt(2, order.getNumber());
			stmt.setTimestamp(3, Timestamp.valueOf(order.getLast_time()
					.toLocaleString()));
			stmt.setString(4, order.getRemark());
			stmt.setInt(5, order.getCategory());
			stmt.setInt(6, order.getInput_user());
			stmt.setTimestamp(7, Timestamp.valueOf(order.getInput_time()
					.toLocaleString()));
			stmt.setInt(8, 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 getOrder() {
		List list = new ArrayList();

		String sqlStr = "select * from t_order 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) {
				TOrder order = null;
				while (rs.next()) {
					order = new TOrder();
					order.setId(rs.getInt("id"));
					order.setCategory(rs.getInt("category"));
					order.setInput_time(rs.getDate("input_time"));
					order.setInput_user(rs.getInt("input_user"));
					order.setLast_time(rs.getDate("last_time"));
					order.setName(rs.getString("name"));
					order.setNumber(rs.getInt("number"));
					order.setRemark(rs.getString("remark"));
					order.setStatus(rs.getInt("status"));
					list.add(order);
				}
			}
		} 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 status
	 * @return
	 */
	public List getStatusOrder(int status) {
		List list = new ArrayList();

		String sqlStr = "select * from t_order where status=" + status + " 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) {
				TOrder order = null;
				while (rs.next()) {
					order = new TOrder();
					order.setId(rs.getInt("id"));
					order.setCategory(rs.getInt("category"));
					order.setInput_time(rs.getDate("input_time"));
					order.setInput_user(rs.getInt("input_user"));
					order.setLast_time(rs.getDate("last_time"));
					order.setName(rs.getString("name"));
					order.setNumber(rs.getInt("number"));
					order.setRemark(rs.getString("remark"));
					order.setStatus(rs.getInt("status"));
					list.add(order);
				}
			}
		} 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 TOrder getOrder(int id) {
		String sqlStr = "select * from t_order where id=" + id;
		Connection conn = DbUtil.getConnection();
		PreparedStatement stmt = null;
		ResultSet rs = null;
		TOrder order = null;
		try {
			stmt = conn.prepareStatement(sqlStr);
			conn.setAutoCommit(false);
			rs = stmt.executeQuery();
			conn.commit();

			/* 遍历将rs中的结果插入order中返回 */
			if (rs.next()) {
				order = new TOrder();
				order.setId(rs.getInt("id"));
				order.setCategory(rs.getInt("category"));
				order.setInput_time(rs.getDate("input_time"));
				order.setInput_user(rs.getInt("input_user"));
				order.setLast_time(rs.getDate("last_time"));
				order.setName(rs.getString("name"));
				order.setNumber(rs.getInt("number"));
				order.setRemark(rs.getString("remark"));
				order.setStatus(rs.getInt("status"));
			}
		} 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 order;
	}

	public List searchByName(String name){
		List list = new ArrayList();

		String sqlStr = "select * from t_order 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) {
				TOrder order = null;
				while (rs.next()) {
					order = new TOrder();
					order.setId(rs.getInt("id"));
					order.setCategory(rs.getInt("category"));
					order.setInput_time(rs.getDate("input_time"));
					order.setInput_user(rs.getInt("input_user"));
					order.setLast_time(rs.getDate("last_time"));
					order.setName(rs.getString("name"));
					order.setNumber(rs.getInt("number"));
					order.setRemark(rs.getString("remark"));
					order.setStatus(rs.getInt("status"));
					list.add(order);
				}
			}
		} 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 delOrder(int id){
		String sqlStr = "delete from t_order where id=" + id;
		Connection conn = DbUtil.getConnection();
		PreparedStatement stmt = null;
		int result = 0;
		try {
			stmt = conn.prepareStatement(sqlStr);
			conn.setAutoCommit(false);
			result = stmt.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.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;
		Date date = new Date();
		String sqlStr = "update t_order set name=?,number=?,last_time=?,remark=?," +
				"category=?,input_user=?,input_time=?,status=? where id=" + order.getId();

		try {
			stmt = conn.prepareStatement(sqlStr);
			conn.setAutoCommit(false);
			stmt.setString(1, order.getName());
			stmt.setInt(2, order.getNumber());
			stmt.setTimestamp(3, Timestamp.valueOf(order.getLast_time()
					.toLocaleString()));
			stmt.setString(4, order.getRemark());
			stmt.setInt(5, order.getCategory());
			stmt.setInt(6, order.getInput_user());
			stmt.setTimestamp(7, Timestamp.valueOf(order.getInput_time()
					.toLocaleString()));