ordermysqldao.java

这是尚学堂马士兵老师讲解的网上购物系统

package cn.edu.chu.order;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.chu.cart.*;
import cn.edu.chu.product.Product;
import cn.edu.chu.user.*;
import cn.edu.chu.util.DB;

public class OrderMySQLDAO implements OrderDAO {

	public void saveOrder(SalesOrder so) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rsKey = null;
		try {
			conn = DB.getConn();
			conn.setAutoCommit(false);
			String sql = "insert into salesorder values(null,?,?,?,?)";
			pstmt = DB.getStmt(conn, sql, true);
			pstmt.setInt(1, so.getUser().getId());
			pstmt.setString(2, so.getAddr());
			pstmt.setTimestamp(3, so.getODate());
			pstmt.setInt(4, so.getStatus());
			pstmt.executeUpdate();
			rsKey = pstmt.getGeneratedKeys();
			rsKey.next();
			int key = rsKey.getInt(1);

			String sqlItem = "insert into salesitem values(null,?,?,?,?)";
			pstmt = DB.getStmt(conn, sqlItem);
			Cart c = so.getCart();
			List<CartItem> itmes = c.getItmes();
			for (int i = 0; i < itmes.size(); i++) {
				CartItem ci = itmes.get(i);
				pstmt.setInt(1, ci.getProductId());
				pstmt.setDouble(2, ci.getMemberPrice());
				pstmt.setInt(3, ci.getCount());
				pstmt.setInt(4, key);
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.setAutoCommit(true);
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}

	}

	public List<SalesOrder> getOrders() {
		List<SalesOrder> list = new ArrayList<SalesOrder>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			String sql = "select * from salesorder";
			rs = DB.executeQuery(conn, sql);
			while (rs.next()) {
				SalesOrder so = new SalesOrder();
				so.setId(rs.getInt("id"));
				so.setAddr(rs.getString("addr"));
				so.setUser(UserManager.getUser(rs.getInt("userid")));
				so.setODate(rs.getTimestamp("odate"));
				list.add(so);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return list;
	}

	public List<SalesOrder> getOrders(int pageNo, int pageSize) {
		List<SalesOrder> list = new ArrayList<SalesOrder>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			String sql = "select * from salesorder limit " + (pageNo - 1)
					* pageSize + "," + pageSize;
			rs = DB.executeQuery(conn, sql);
			while (rs.next()) {
				SalesOrder so = new SalesOrder();
				so.setId(rs.getInt("id"));
				so.setAddr(rs.getString("addr"));
				so.setUser(UserManager.getUser(rs.getInt("userid")));
				so.setODate(rs.getTimestamp("odate"));
				so.setStatus(rs.getInt("status"));
				list.add(so);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return list;
	}

	public void update(int id) {
		Connection conn = DB.getConn();
		if(id==0){
			return;
		}
		String sql = "update salesorder set status =-1 where id=" + id;
System.out.println("update SQL:"+sql);
		PreparedStatement pstmt = DB.getStmt(conn, sql);
		try {
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}

	public List<SalesItem> getOrderItems(int orderId) {
		List<SalesItem> list = new ArrayList<SalesItem>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			String sql = "select * from salesitem where orderid="+orderId;
			rs = DB.executeQuery(conn, sql);
			while (rs.next()) {
				SalesItem si = new SalesItem();
				si.setId(rs.getInt("id"));
				si.setProductId(rs.getInt("productId"));
				si.setUnitPrice(rs.getDouble("unitprice"));
				si.setCount(rs.getInt("pcount"));				
				list.add(si);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(conn);
		}
		return list;
	}

	

}