salesordermysqldao.java

基于mysql5.0完成的shopping网站后台

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.shopping.dao;


import com.shopping.model.Cart;
import com.shopping.model.CartItem;
import com.shopping.model.SalesOrder;
import com.shopping.model.User;
import com.shopping.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author ruirui
 */
public class SalesOrderMysqlDAO {
  public void save(SalesOrder so){
           Connection conn = null;
           PreparedStatement stmt = null;
           String sql = "insert into salesorder values(null,?,?,?,?)";
           ResultSet rs = null;


        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            stmt = DBUtil.getPStatement(conn, sql);
            stmt.setInt(1, so.getUser().getId());
            stmt.setString(2, so.getAddr());
            stmt.setTimestamp(3, so.getOdate());
            stmt.setInt(4, so.getStatus());
            int rsc = stmt.executeUpdate();

            rs = stmt.getGeneratedKeys();
            rs.next();
            int key = rs.getInt(1);
System.out.println("..................."+key);
            //....................................
            //����order��Ĺ������Ҫ����orderItem;
            //saleitem����Ϣ����ڣ�Cart �е�cartitem��
            String sql1 = "insert into salesitem values(null,?,?,?,?)";
            stmt = DBUtil.getPStatement(conn, sql);
            Cart c = so.getCart();
            List<CartItem> list = c.getItems();
            
            for(int i = 0 ; i < list.size();i++){
              CartItem item = list.get(i);
              stmt.setInt(1, item.getProductId());
              stmt.setDouble(2, item.getPrice());
              stmt.setInt(3, item.getCount());
              stmt.setInt(4, key);
              stmt.addBatch();
            
            }
            stmt.executeBatch();
            conn.setAutoCommit(true);
            
        } catch (SQLException e) {
            try {
                conn.rollback();
                e.printStackTrace();
            } catch (SQLException ex) {
                Logger.getLogger(SalesOrderMysqlDAO.class.getName()).log(Level.SEVERE, null, ex);
            }

        } finally {
            DBUtil.close(conn, stmt, rs);

        }



    }
      public int getOrders(List<SalesOrder> list, int pageNo, int pageSize) {
        Connection conn = null;
        ResultSet rs = null;
        Statement stmt1 = null;
        Statement stmt = null;
        ResultSet rs1 = null;
        int pageCount = -1;
        try {
            conn = DBUtil.getConnection();
            stmt1 = DBUtil.getStatement(conn);
            //...............
            //��ѯ��¼�ĸ���...
            String sql1 = "select count(*) from salesorder";
            rs1 = DBUtil.query(stmt, sql1);
            rs1.next();
            pageCount = (rs1.getInt(1) + pageSize - 1) / pageSize;





            String sql = "select so.id sid, so.userid, so.addr, so.odate, so.status,"+
                    " u.id, u.username, u.password, u.addr uaddr, u.phone, u.rdate"+
                    " from salesorder so join user  u  on "+
                    " (so.userid = u.id)  limit "
                    +(pageNo - 1) * pageSize +" , "+ pageSize ;

System.out.println(sql);
           stmt = DBUtil.getStatement(conn);
            rs = DBUtil.query(stmt, sql);

            while (rs.next()) {
               SalesOrder so = new SalesOrder();
               so.setId(rs.getInt("sid"));
               so.setAddr(rs.getString("addr"));
               so.setOdate(rs.getTimestamp("odate"));
               so.setStatus(rs.getInt("status"));
               User user = new User();
               user.setId(rs.getInt("id"));
               user.setUsername(rs.getString("username"));
               user.setPassword(rs.getString("password"));
               user.setAddr(rs.getString("uaddr"));
               user.setPhone(rs.getString("phone"));
               user.setRdate(rs.getTimestamp("rdate"));
               so.setUser(user);
               list.add(so);

            }
        } catch (SQLException ex) {
            Logger.getLogger(SalesOrderMysqlDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.close(conn, stmt, rs);
        }

        return pageCount;

    }

}