torderdao.java

这是一个网站在线订单系统.可以删除,查询,修改

package com.bsw.order.database;

import com.bsw.order.base.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Iterator;

/**
 * 实现数据库的各种操作。包括添加，查询，修改，删除的功能。
 * 
 * @author 蜗牛
 * @version 1.1
 */
public class TorderDAO {

	/** Statement 对象 */
	Statement stmt = null;

	/** preparedStatementr对象 */
	PreparedStatement pstmt = null;

	/** Result 对象 */
	ResultSet rs = null;

	/** Connection 对象 */
	Connection conn = null;

	/** 实例化一个torder对象 */
	Torder torder = new Torder();

	int id;

	String ordercode;

	String buyer;

	double sum;

	String deliverymethod;

	String lastmodifydate;

	String createddat;

	/** DAManaget对象 */
	DBManager db = new DBManager();

	/**
	 * 返回所有Orders
	 * 
	 * @return
	 */
	public List queryTorders() {
		List torders = new ArrayList();
		String sql = "select * from torder";
		try {
			conn = db.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				id = rs.getInt(1);
				ordercode = rs.getString(2);
				buyer = rs.getString(3);
				sum = rs.getDouble(4);
				deliverymethod = rs.getString(5);
				lastmodifydate = rs.getString(6);
				createddat = rs.getString(7);
				Torder torder = new Torder(id, ordercode, buyer, sum,
						deliverymethod, lastmodifydate, createddat);
				torders.add(torder);
			}
			return torders;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, stmt, conn);
		}
	}

	/**
	 * 通过指定购买者的名字返回该用户的所有Orders
	 * 
	 * @return
	 */
	public List queryUserOrders(String name) {
		List torders = new ArrayList();
		String sql = "select * from torder where buyer=\'" + name + "\'";
		try {
			conn = db.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				id = rs.getInt(1);
				ordercode = rs.getString(2);
				buyer = rs.getString(3);
				sum = rs.getDouble(4);
				deliverymethod = rs.getString(5);
				lastmodifydate = rs.getString(6);
				createddat = rs.getString(7);
				Torder torder = new Torder(id, ordercode, buyer, sum,
						deliverymethod, lastmodifydate, createddat);
				torders.add(torder);
			}
			return torders;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, stmt, conn);
		}
	}

	/**
	 * 通过指定订单的ID返回该Order对象
	 * 
	 * @return
	 */
	public Torder queryId(String id) {
		try {
			int iid = Integer.parseInt(id);
			String sql = "select * from torder where orderid = " + iid + " ";
			conn = db.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				id = rs.getString(1);
				ordercode = rs.getString(2);
				buyer = rs.getString(3);
				sum = rs.getDouble(4);
				deliverymethod = rs.getString(5);
				lastmodifydate = rs.getString(6);
				createddat = rs.getString(7);

			}
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, stmt, conn);
		}
		torder = new Torder(Integer.parseInt(id), ordercode, buyer, sum,
				deliverymethod, lastmodifydate, createddat);
		return torder;
	}

	/**
	 * 通过选择指定订单的买者和定单名称以及金额，送货方式返回该Order对象
	 * 
	 * @return
	 */
	public List queryUserOrders(String buyer, String ordercode, String total,
			String method) {
		List torders = new ArrayList();

		String sql = "select * from torder where ";

		if (buyer != null && !buyer.equals("")) {
			sql += "buyer=\'" + buyer + "\' ";

		}
		if (ordercode != null && !ordercode.equals("")) {

			sql += "and ordercode=\'" + ordercode + "\' ";

		}
		if (total != null && !total.equals("")) {

			sql += "and sum=" + total + " ";

		}
		if (method != null && !method.equals("")) {

			sql += "and deliverymethod=\'" + method + "\' ";

		}
		try {
			System.out.println(sql);
			conn = db.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				id = rs.getInt(1);
				ordercode = rs.getString(2);
				buyer = rs.getString(3);
				sum = rs.getDouble(4);
				deliverymethod = rs.getString(5);
				lastmodifydate = rs.getString(6);
				createddat = rs.getString(7);
				Torder torder = new Torder(id, ordercode, buyer, sum,
						deliverymethod, lastmodifydate, createddat);
				torders.add(torder);
			}
			return torders;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, stmt, conn);
		}
	}

	/**
	 * 插入一条新Order记录
	 * 
	 * @param torder
	 */
	public void insertTorder(Torder torder) {
		String sql = "insert into torder (ordercode,buyer,sum,"
				+ "deliverymethod,lastmodifydate,createddat) VALUES (?,?,?,?,?,?)";
		System.out.println(torder.getCreateddate());
		try {
			conn = db.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, torder.getOrdercode());
			pstmt.setString(2, torder.getBuyer());
			pstmt.setDouble(3, torder.getSum());
			pstmt.setString(4, torder.getDeliverymethod());
			pstmt.setString(5, torder.getLastmodifydate());
			pstmt.setString(6, torder.getCreateddate());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, pstmt, conn);
		}
	}

	/**
	 * 更新一条order信息
	 * 
	 * @param torder
	 */
	public void updateTorder(Torder torder) {
		String sql = "update torder set ordercode=?,buyer=?,sum=?,"
				+ "deliverymethod=?,lastmodifydate=?,createddat=? where orderid=?";
		java.util.Date d=new java.util.Date();
		try {
			
			conn = db.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, torder.getOrdercode());
			pstmt.setString(2, torder.getBuyer());
			pstmt.setDouble(3, torder.getSum());
			pstmt.setString(4, torder.getDeliverymethod());
			pstmt.setString(5,torder.getLastmodifydate());
			pstmt.setString(6, torder.getCreateddate());
			pstmt.setInt(7, torder.getOrderid());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, pstmt, conn);
		}
	}

	/**
	 * 删除一条order信息
	 * 
	 * @param id
	 */
	public void deleteTorder(int id) {
		String sql = "delete from torder where orderid=" + id + "";
		try {
			conn = db.getConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, stmt, conn);
		}
	}

	
}