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

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

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

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

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

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

	/** 实例化一个Orderitem对象 */
	Orderitem item = new Orderitem();

	/**
	 * 订单项的索引号
	 */
	private int itemId;

	/**
	 * 打折类型
	 */
	private int discounttype;

	/**
	 * 促销价格
	 */
	private double promotionprice;

	/**
	 * 促销数目
	 */
	private int promotionnum;

	/**
	 * 订单ID
	 */
	private int orderid;

	/**
	 * 折扣
	 */
	private double discount;

	/**
	 * 总额
	 */
	private double sum;

	/**
	 * 单价
	 */
	private double unitprice;

	/**
	 * 数目
	 */
	private int unitnum;

	/**
	 * 商品的名字
	 */
	private String name;

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

	/**
	 * 返回所有数据库中的Orderitems
	 */
	public List queryOrderitems() {
		List orderitems = new ArrayList();
		String sql = "select * from orderitem";
		try {
			conn = db.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				discounttype = rs.getInt(2);
				promotionprice = rs.getDouble(3);
				discount = rs.getDouble(4);
				promotionnum = rs.getInt(5);
				orderid = rs.getInt(6);
				sum = rs.getDouble(7);
				unitprice = rs.getDouble(8);
				unitnum = rs.getInt(9);
				name = rs.getString(10);
				item = new Orderitem(discounttype, promotionprice,
						promotionnum, discount, orderid, sum, unitprice,
						unitnum, name);
				orderitems.add(item);
			}
			return orderitems;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, stmt, conn);
		}
	}

	/**
	 * 通过指定的orderid返回数据库中的所有的Orderitems
	 */
	public ArrayList queryOrderitems(String id) {

		String sql = "select * from orderitem where orderid = \'" + id + "\'";
		try {
			
			ArrayList list = new ArrayList();
			conn = db.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				itemId = rs.getInt(1);
				discounttype = rs.getInt(2);
				promotionprice = rs.getDouble(3);
				discount = rs.getDouble(4);
				promotionnum = rs.getInt(5);
				orderid = rs.getInt(6);
				sum = rs.getDouble(7);
				unitprice = rs.getDouble(8);
				unitnum = rs.getInt(9);
				name = rs.getString(10);
				item = new Orderitem(itemId, discounttype, promotionprice,
						promotionnum, orderid, discount, sum, unitprice,
						unitnum, name);
				
				list.add(item);

			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, stmt, conn);
		}
	}

	/**
	 * 通过指定itemid返回数据库中的一个Orderitem
	 */
	public Orderitem queryItem(String id) {

		String sql = "select * from orderitem where itemid = \'" + id + "\'";
		try {
			conn = db.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				itemId = rs.getInt(1);
				discounttype = rs.getInt(2);
				promotionprice = rs.getDouble(3);
				discount = rs.getDouble(4);
				promotionnum = rs.getInt(5);
				orderid = rs.getInt(6);
				sum = rs.getDouble(7);
				unitprice = rs.getDouble(8);
				unitnum = rs.getInt(9);
				name = rs.getString(10);
				item = new Orderitem(itemId, discounttype, promotionprice,
						promotionnum, orderid, discount, sum, unitprice,
						unitnum, name);

			}
			return item;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, stmt, conn);
		}
	}

	/**
	 * 插入一条新Order记录
	 */
	public void insertOrderitem(Orderitem orderitem) {
		String sql = "insert into orderitem (discounttype,promotionprice,discount,promotionnum"
				+ ",orderid, sum,  unitprice,unitnum,  name) VALUES (?,?,?,?,?,?,?,?,?)";
		try {
			conn = db.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, orderitem.getDiscounttype());
			pstmt.setDouble(2, orderitem.getPromotionprice());
			pstmt.setDouble(3, orderitem.getDicount());
			pstmt.setInt(4, orderitem.getPromotionnum());
			pstmt.setInt(5, orderitem.getOrderid());
			pstmt.setDouble(6, orderitem.getSum());
			pstmt.setDouble(7, orderitem.getUnitprice());
			pstmt.setInt(8, orderitem.getUnitnum());
			pstmt.setString(9, orderitem.getName());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, pstmt, conn);
		}
	}

	/**
	 * 更新一条orderitem信息
	 * 
	 */
	public void updateTorder(Orderitem orderitem) {
		String sql = "update orderitem set discounttype=?,promotionprice=?,discount=?,promotionnum=?"
				+ ",orderid=?, sum=?,  unitprice=?,unitnum=?,  name=? where itemid=?";
		try {
			
			conn = db.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, orderitem.getDiscounttype());
			pstmt.setDouble(2, orderitem.getPromotionprice());
			pstmt.setDouble(3, orderitem.getDicount());
			pstmt.setInt(4, orderitem.getPromotionnum());
			pstmt.setInt(5, orderitem.getOrderid());
			pstmt.setDouble(6, orderitem.getSum());
			pstmt.setDouble(7, orderitem.getUnitprice());
			pstmt.setInt(8, orderitem.getUnitnum());
			pstmt.setString(9, orderitem.getName());
			pstmt.setInt(10, orderitem.getItemid());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			/** 在任何情况下，都要关闭数据库。 */
			db.closeAll(rs, pstmt, conn);
		}
	}

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

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

}