orderdao.java

servlet实现网上书店查询

package com.ghy.bookstore.order.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.ghy.base.dao.BaseDao;
import com.ghy.bookstore.order.model.OrderItem;
import com.ghy.bookstore.order.model.OrderVO;
import com.ghy.bookstore.user.model.UserVO;

public class OrderDao extends BaseDao {

	public String createOrder(OrderVO order) throws IOException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Connection conn = this.getDatamanager().getConnection();
		String sql = "insert into orders(id,user_id,username,otime,totalprice) values (?,?,?,?,?)";
		PreparedStatement statement = conn.prepareStatement(sql);
		String id = this.generateId(conn);
		statement.setString(1, id);
		statement.setString(2, order.getUserid());
		statement.setString(3, order.getUsername());
		statement.setString(4, order.getOtime());
		statement.setDouble(5, order.getTotal());
		statement.execute();
		this.getDatamanager().closeDBConnection();
		return id;
	}

	public void createOrderItem(OrderItem shopcart, String orderid)
			throws IOException, InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Connection conn = this.getDatamanager().getConnection();
		String sql = "insert into orderitem(order_id,book_id,mount,price,bookname)values (?,?,?,?,?)";
		PreparedStatement statement = conn.prepareStatement(sql);
		statement.setString(1, orderid);
		statement.setString(2, shopcart.getBookid());
		statement.setInt(3, shopcart.getMount());
		statement.setDouble(4, shopcart.getPrice());
		statement.setString(5, shopcart.getBookname());
		statement.execute();
		this.getDatamanager().closeDBConnection();
	}

	public ArrayList searchOrderByUser(UserVO user) throws IOException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Connection conn = this.getDatamanager().getConnection();
		String sql = "select * from orders where user_id='" + user.getId()
				+ "'";
		ArrayList orderlist = new ArrayList();
		OrderVO order = null;
		PreparedStatement statement = conn.prepareStatement(sql);
		ResultSet rt = statement.executeQuery();
		while (rt.next()) {
			order = new OrderVO();
			order.setId(rt.getString("id"));
			order.setUserid(rt.getString("user_id"));
			order.setUsername(rt.getString("username"));
			order.setTotal(rt.getDouble("totalprice"));
			order.setOtime(rt.getString("otime"));
			orderlist.add(order);
			
		}
		return orderlist;

	}

	public void deleteOrder(String orderId) throws IOException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Connection conn = this.getDatamanager().getConnection();
		String sql = "delete from orders where id='" + orderId + "'";
		Statement stat = conn.createStatement();
		stat.execute(sql);
		this.getDatamanager().closeDBConnection();
	}
}