orderdaojdbcimpl.java

shoppingCar 购物车

package com.tarena.shoppingcar.dao;

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 com.tarena.shoppingcar.entity.Item;
import com.tarena.shoppingcar.entity.Order;
import com.tarena.shoppingcar.entity.Product;
import com.tarena.shoppingcar.entity.User;
import com.tarena.shoppingcar.util.ConnectionFactory;
import com.tarena.shoppingcar.util.JdbcUtil;

public class OrderDaoJdbcImpl implements OrderDao {

	public void save(Order order) {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "insert into t_order(user_id,cost,oid) values(?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, order.getUser().getId());
			pstmt.setDouble(2, order.getCost());
			pstmt.setString(3, order.getOid());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JdbcUtil.close(con,pstmt);
		}
	}

	public Order findById(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	public int findId() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int  i = -1;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select max(id) from t_order";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			rs.next();
			i = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally{
			JdbcUtil.close(con, pstmt, rs);
		}
		return i;
	}

	public List<Order> getOrders(User user) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Order> orders = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select oid,cost from t_order where user_id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, user.getId());
			rs = pstmt.executeQuery();
			orders = getOrders(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orders;
	}
	public List<Order> getOrders(ResultSet rs) throws SQLException{
		List<Order> orders = new ArrayList<Order>();
		while(rs.next()) {
			Order order = new Order();
			order.setOid(rs.getString(1));
			order.setCost(rs.getDouble(2));
			orders.add(order);
		}
		return orders;
	}
	public int[] getIds(User user) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int[] ids = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select id from t_order where user_id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, user.getId());
			rs = pstmt.executeQuery();
			ids = getIds(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ids;
	}
	public int[] getIds(ResultSet rs) throws SQLException {
		int[] ids = new int[128];
		int i = 0;
		while(rs.next()) {
			ids[i]=rs.getInt(1);
			i++;
		}
		return ids;
	}
	public List<Item> getItems(int i) {
		List<Item> items = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionFactory.getConnection1();
			String sql = "select id,amount,product_id from item where order_id=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, i);
			rs = pstmt.executeQuery();
			items = getItems(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return items;
	}
	public List<Item> getItems(ResultSet rs) throws SQLException{
		List<Item> items = new ArrayList<Item>();
		while(rs.next()) {
			Item item = new Item();
			Product product = new Product();
			product.setId(rs.getInt(3));
			item.setId(rs.getInt(1));
			item.setAmount(rs.getInt(2));
			item.setProduct(product);
			items.add(item);
		}
		return items;
	}
	public Product getProduct(Item item) {
		Product product = null;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionFactory.getConnection1();
			String sql = "select p.pname from product p,item i where p.id=? ";
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, item.getProduct().getId());
			rs = pstmt.executeQuery();
			product.setPname(rs.getString(1));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return product;
	}

	public List<Order> getOrders() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Order> orders = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select u.id,u.userName,o.id,o.oid,o.cost from user u,t_order o where o.user_id=u.id";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			orders = getorders(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orders;
	}
	public List<Order> getorders(ResultSet rs) throws SQLException{
		List<Order> orders = new ArrayList<Order>();
		while(rs.next()) {
			User user = new User();
			user.setId(rs.getInt(1));
			user.setUserName(rs.getString(2));
			Order order = new Order();
			order.setId(rs.getInt(3));
			order.setOid(rs.getString(4));
			order.setCost(rs.getDouble(5));
			order.setUser(user);
			orders.add(order);
		}
		return orders;
	}

	public List<Order> findByOid(String oid) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Order> orders = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select oid,cost,user_id from t_order where oid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, oid);
			rs = pstmt.executeQuery();
			orders = getorders(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return orders;
	}
}