databaseutil.java

JSF+Hibernate+Spring三层架构的一个例子

package pizzaria;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;

public class DatabaseUtil {

  String jdbcDriver;
  String dbUrl;
  String dbUserName;
  String dbPassword;

  public void setDbUrl(String dbUrl) {
    this.dbUrl = dbUrl;
  }
  public void setJdbcDriver(String jdbcDriver) {
    this.jdbcDriver = jdbcDriver;
  }
  public void setDbUserName(String dbUserName) {
    this.dbUserName = dbUserName;
  }
  public void setDbPassword(String dbPassword) {
    this.dbPassword = dbPassword;
  }

  //获取所有产品的摘要.返回一个ArrayList对象。
  public ArrayList getProductSummaries() {
    ArrayList productSummaries = new ArrayList();
    String sql = "SELECT ProductId, Name FROM Products";
    try {
      Connection connection = getConnection();
      Statement statement = connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sql);
      while (resultSet.next()) {
        String id = resultSet.getString(1);
        String name = resultSet.getString(2);
        ProductSummary summary = new ProductSummary(id, name);
        productSummaries.add(summary);
      }
      resultSet.close();
      statement.close();
      connection.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return productSummaries;
  }

  //根据产品ID获取产品的详细信息
  public ProductBean getProductDetails(String productId) {
    String sql = "SELECT ProductId, Name, Description, Price FROM Products" +
      " WHERE ProductId=" + productId;
    ProductBean result = null;
    try {
      Connection connection = getConnection();
      Statement statement = connection.createStatement();
      ResultSet resultSet = statement.executeQuery(sql);
      if (resultSet.next()) {
        String id = resultSet.getString(1);
        String name = resultSet.getString(2);
        String description = resultSet.getString(3);
        float price = resultSet.getFloat(4);
        result = new ProductBean(id, name, description, price);
      }
      resultSet.close();
      statement.close();
      connection.close();
    }
    catch (SQLException e) {
    }
    catch (Exception e) {
    }
    return result;
  }

//插入定单，添加定单的详细信息
  public synchronized void insertOrder(
    OrderBean order, ShoppingCartBean shoppingCart) {

    long orderId = System.currentTimeMillis();
    ArrayList products = new ArrayList();
    String contactName = order.getContactName();
    String deliveryAddress = order.getDeliveryAddress();
    String creditCardName = order.getCreditCardName();
    String creditCardNumber = order.getCreditCardNumber();
    String creditCardExpiryDate = order.getCreditCardExpiryDate();
    String sql = "INSERT INTO Orders" +
      " (OrderId, ContactName, DeliveryAddress, CCName, CCNumber, CCExpiryDate)" +
      " VALUES" +
      " (" + orderId + "," +
      "'" + contactName + "'," +
      "'" + deliveryAddress + "'," +
      "'" + creditCardName + "'," +
      "'" + creditCardNumber + "'," +
      "'" + creditCardExpiryDate + "')";
    try {
      Connection connection = getConnection();
      Statement statement = connection.createStatement();
      statement.executeUpdate(sql);

      Iterator shoppingItems = shoppingCart.getShoppingItems().iterator();
      while (shoppingItems.hasNext()) {
        ShoppingItemBean item = (ShoppingItemBean) shoppingItems.next();
        String productId = item.getProductId();
        int quantity = item.getQuantity();
        float price = item.getPrice();
        sql = "INSERT INTO OrderDetails" +
          " (OrderId, ProductId, Quantity, Price)" +
          " VALUES" +
          " (" + orderId + "," +
          productId + "," +
          quantity + "," +
          price + ")";
        statement.executeUpdate(sql);
      }
      statement.close();
      connection.close();
    }
    catch (SQLException e) {
      System.out.println(e.toString());
    }
    catch (Exception e) {
    }
  }

  private Connection getConnection() {
    // use connection pooling for production
    Connection connection = null;
    try {
      Class.forName(jdbcDriver);
      connection = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    return connection;
  }
  

}