orders.java

网络商店源代码用NetBeans6.1Beta开发

package model;
import java.util.*;
import java.sql.*;
import model.db.*;
import util.ObjectSerial;

public class Orders {
  private static final String table = "orders";

  static void createTable() throws Exception {
    final String table_def = "id INT AUTO_INCREMENT NOT NULL," 
                             + "user VARCHAR(20)," 
                             + "items MEDIUMBLOB," 
                             + "created DATETIME," 
                             + "PRIMARY KEY(id)";

    Connection cx = Database.connect();
    Statement st = cx.createStatement();

    String sql_op = "DROP TABLE IF EXISTS " + table;

    System.out.println("=> " + sql_op);
    
    st.executeUpdate(sql_op);

    sql_op = "CREATE TABLE " + table + "(" + table_def + ")";

    System.out.println("=> " + sql_op);
    st.executeUpdate(sql_op);
  }

  static void create(Order o) throws Exception {
    Connection cx = Database.connect();
    String sql_op = "INSERT INTO " + table + "(user,items,created)" + " VALUES (?, ?, NOW())";

    System.out.println("=> " + sql_op);

    PreparedStatement pst = cx.prepareStatement(sql_op);

    List<OrderItem> m = o.getItems();
    
    pst.setString(1, o.getUser());
    pst.setBytes(2, ObjectSerial.serialize(m));

    pst.executeUpdate();
  }

  static List<Order> findAll() throws Exception {
    Connection cx = Database.connect();
    Statement st = cx.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM " + table);

    List<Order> L = new ArrayList<Order>();
    while (rs.next()) {
      byte[] blob = rs.getBytes("items");
      
      @SuppressWarnings("unchecked")
      List<OrderItem> items = (List<OrderItem>) ObjectSerial.unserialize(blob);

      L.add( new Order(
              rs.getString("user"), 
              items,
              rs.getInt("id"),
              rs.getTimestamp("created")
              )
            );
    }
    return L;
  }
  
   static void delete(int id) throws Exception {
      Connection cx = Database.connect();
        String sql_op = " DELETE FROM " + table + " WHERE " + "id = " + id;

        PreparedStatement pst = cx.prepareStatement(sql_op);
        pst.executeUpdate();
  } 
   
  static Order find(int id) throws Exception {
    Connection cx = Database.connect();
    Statement st = cx.createStatement();

    String query = "SELECT * FROM " + table + " WHERE id=" + id;

    ResultSet rs = st.executeQuery(query);
    if (!rs.next()) 
      return null;
    
    byte[] blob = rs.getBytes("items");
    
    @SuppressWarnings("unchecked")
    List<OrderItem> items = (List<OrderItem>) ObjectSerial.unserialize(blob);
    return new Order(
              rs.getString("user"), 
              items,
              rs.getInt("id"),
              rs.getTimestamp("created")
            );
  }
static List<Order> findUser(String user) throws Exception {
    Connection cx = Database.connect();
    Statement st = cx.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM " + table + " WHERE user='" + user + "' ORDER BY  created DESC");
 
 List<Order> L = new ArrayList<Order>();
    while (rs.next()) {
      byte[] blob = rs.getBytes("items");
      
      @SuppressWarnings("unchecked")
      List<OrderItem> items = (List<OrderItem>) ObjectSerial.unserialize(blob);

      L.add( new Order(
              rs.getString("user"), 
              items,
              rs.getInt("id"),
              rs.getTimestamp("created")
              )
            );
    }
    return L;
  }
}