jdbcdaoimpl.java

一个小型的购物商店

/*
 * JdbcDaoImpl.java
 *
 * Created on 2006年9月14日, 下午1:25
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.shopping.dao;

import com.shopping.model.*;
import com.shopping.resource.DBConnection;
import java.util.*;
import java.sql.*;
/**
 *
 * @author 曹昊
 */
public class JdbcDaoImpl implements ShoppingCartDao{
    
    public Map selectAllProducts(){
        Connection con = DBConnection.getConnection();
        if(con == null)
            return null;
        Map<String,Product> products = new HashMap<String,Product>();
        String sql = "SELECT * FROM cart_product";
        PreparedStatement prep = null;
        ResultSet rs = null;
        try {
            prep = con.prepareStatement(sql);
            prep.executeQuery();
            rs = prep.getResultSet();
            
            while(rs.next()){
                Product product = new Product();
                product.setId(rs.getInt(1));
                product.setName(rs.getString(2));
                product.setDescription(rs.getString(3));
                product.setPrice(rs.getDouble(4));
                products.put(product.getId()+"",product);
            }
            
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally{
            try {
                rs.close();
                prep.close();
                con.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return products;
    }
    
    public int getOrderId() {
        Connection con = DBConnection.getConnection();
        if(con == null)
            return -1;
        Statement stat = null;
        ResultSet rst = null;
        String sql = "select max(id) from cart_order";
        int id = 1;
        try {
            stat = con.createStatement();
            rst = stat.executeQuery(sql);
            if (rst.next()) {
                id = rst.getInt(1) + 1;
            } else {
                id = 1;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally{
            try {
                rst.close();
                stat.close();
                con.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return id;
    }
    
    public int getItemId(Connection con) {
        if(con == null)
            return -1;
        Statement stat = null;
        ResultSet rst = null;
        String sql = "select max(id) from cart_item";
        int id = 1;
        try {
            stat = con.createStatement();
            rst = stat.executeQuery(sql);
            if (rst.next()) {
                id = rst.getInt(1) + 1;
            } else {
                id = 1;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally{
            try {
                rst.close();
                stat.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return id;
    }
    
    public Order generateOrder(User user, Cart cart) {
        Connection con = DBConnection.getConnection();
        if(con == null)
            return null;
        PreparedStatement prep = null;
        
        int orderid = getOrderId();
        int itemid = 1;
        int amount;
        int product_id;
        double cost = cart.getCost();
        Map<String,Item> items = cart.getItems();
        Set<Item> values = new HashSet<Item>(items.values());

        //生成订单对象
        Order order = new Order();
        order.setId(orderid);
        order.setItems(values);
        order.setUser(user);
        order.setStatus(orderid+100);
        order.setCost(cost);
        
        try {
            //插入到订单表
            con.setAutoCommit(false);
            String sql = "INSERT INTO cart_order VALUES(?,?,?,?)";
            prep = con.prepareStatement(sql);
            prep.setInt(1,orderid);
            prep.setInt(2,order.getStatus());
            prep.setInt(3,order.getUser().getId());
            prep.setDouble(4,order.getCost());
            prep.execute();
            
            //插入到商品表
            Iterator it = items.values().iterator();
            while(it.hasNext()){
                sql = "INSERT INTO cart_item VALUES(?,?,?,?)";
                prep = con.prepareStatement(sql);
                Item item =(Item) (it.next());
                itemid = this.getItemId(con);
                amount = item.getNumber();
                product_id = item.getProduct().getId();
                prep.setInt(1,itemid);
                prep.setInt(2,amount);
                prep.setInt(3,product_id);
                prep.setInt(4,orderid);
                prep.execute();
            }
            con.commit();
        } catch(SQLException e){
            e.printStackTrace();
            try {
                con.rollback();
                throw new SQLException();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally{
            try {
                prep.close();
                con.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return order;
    }
    
    public boolean validateLogin(String name, String password) {
        Connection con = DBConnection.getConnection();
        if(con == null)
            return false;
        String sql = "SELECT * FROM cart_user WHERE name=? and password=?";
        PreparedStatement prep = null;
        ResultSet rs = null;
        try {
            prep = con.prepareStatement(sql);
            prep.setString(1,name);
            prep.setString(2,password);
            rs = prep.executeQuery();
            if(rs.next()){
                return true;
            } else{
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        } finally{
            try {
                rs.close();
                prep.close();
                con.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
    
    public User queryUserInfo(String name) {
        Connection con = DBConnection.getConnection();
        if(con == null)
            return null;
        User user = new User();
        Statement stat = null;
        ResultSet rst = null;
        String sql = "select * from cart_user where name='" + name + "'";
        try {
            stat = con.createStatement();
            rst = stat.executeQuery(sql);
            while (rst.next()) {
                user.setId(rst.getInt(1));
                user.setName(rst.getString(2));
                user.setPassword(rst.getString(3));
                user.setAddress(rst.getString(4));
                user.setPostcode(rst.getString(5));
                user.setEmail(rst.getString(6));
                user.setHomephone(rst.getString(7));
                user.setCellphone(rst.getString(8));
                user.setOfficephone(rst.getString(9));
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally{
            try {
                rst.close();
                stat.close();
                con.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        return user;
    }
    
    public JdbcDaoImpl() {
    }
    
}