package mypack;

import java.util.Set;
import java.util.Iterator;
import java.util.List;
import java.util.HashSet;
import java.util.HashMap;
import java.util.ArrayList;
import java.sql.*;
import com.mysql.jdbc.Driver;

public class BusinessService{
  private String dbUrl ="jdbc:mysql://localhost:3306/SAMPLEDB";
  private String dbUser="root";
  private String dbPwd="1234";

  public BusinessService() throws Exception{
     //加載MySQL數據庫驅動程序
     Class.forName("com.mysql.jdbc.Driver");
     DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  }

  public Connection getConnection()throws Exception{
      //獲得一個數據庫連接
      return java.sql.DriverManager.getConnection(dbUrl,dbUser,dbPwd);
  }

  /**
   * 持久化參數指定的Customer對象，并且級聯持久化與它關聯的Order對象。
   * 如果Customer對象的name屬性為null，或者Order對象的orderNumber屬性為null，
   * 會拋出BusinessException
   */
  public void saveCustomer(Customer customer) throws Exception {
    Connection con=null;
    PreparedStatement stmt=null;
    try {
      con=getConnection(); //獲得數據庫連接

      //開始一個數據庫事務
      con.setAutoCommit(false);

      //以下是業務邏輯代碼，檢查客戶姓名是否為空
      if(customer.getName()==null)
        throw new BusinessException("客戶姓名不允許為空");

      //以下是數據訪問代碼，持久化Customer對象

      //為新的CUSTOMERS記錄分配惟一的ID
      long customerId=getNextId(con,"CUSTOMERS");
      //把Customer對象映射為面向關系的SQL語句
      stmt=con.prepareStatement("insert into CUSTOMERS(ID,NAME,AGE) values(?,?,?)");
      stmt.setLong(1,customerId);
      stmt.setString(2,customer.getName());
      stmt.setInt(3,customer.getAge());
      stmt.execute();

      Iterator iterator =customer.getOrders().iterator();
      while (iterator.hasNext() ) {
        //以下是業務邏輯代碼，檢查訂單編號是否為空
        Order order=(Order)iterator.next();
        if(order.getOrderNumber()==null)
          throw new BusinessException("訂單編號不允許為空");

        //以下是數據訪問代碼，級聯持久化Order對象

        //為新的ORDERS記錄分配惟一的ID
        long orderId=getNextId(con,"ORDERS");
        //把Order對象映射為面向關系的SQL語句
        stmt=con.prepareStatement("insert into ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID)values(?,?,?,?)");
        stmt.setLong(1,orderId);
        stmt.setString(2,order.getOrderNumber());
        stmt.setDouble(3,order.getPrice());
        stmt.setLong(4,customerId);
        stmt.execute();
      }

      //提交數據庫事務
      con.commit();

    }catch(Exception e){
      e.printStackTrace();
      try{//如果出現異常，撤銷整個事務
        con.rollback();
      }catch(SQLException sqlex){
        sqlex.printStackTrace(System.out);
      }
      throw e;
    }finally{
      try{
         stmt.close();
         con.close();
       }catch(Exception e){
         e.printStackTrace();
       }
    }
  }

  /**
   * 更新參數指定的Customer對象
   */
  public void updateCustomer(Customer customer) throws Exception {
    Connection con=null;
    PreparedStatement stmt=null;
    try {
      con=getConnection(); //獲得數據庫連接

      //開始一個數據庫事務
      con.setAutoCommit(false);

      //以下是數據訪問代碼，更新Customer對象

      //把Customer對象映射為面向關系的SQL語句
      stmt=con.prepareStatement("update CUSTOMERS set NAME=?,AGE=? where ID=?");
      stmt.setString(1,customer.getName());
      stmt.setInt(2,customer.getAge());
      stmt.setLong(3,customer.getId().longValue());
      stmt.execute();

      //提交數據庫事務
      con.commit();

    }catch(Exception e){
      try{//如果出現異常，撤銷整個事務
        con.rollback();
      }catch(SQLException sqlex){
        sqlex.printStackTrace(System.out);
      }
      throw e;
    }finally{
      stmt.close();
      con.close();
    }
  }
  /**
   * 刪除參數指定的Customer對象，并且級聯刪除與它關聯的Order對象。
   */
  public void deleteCustomer(Customer customer) throws Exception {
    Connection con=null;
    PreparedStatement stmt=null;
    try {
      con=getConnection(); //獲得數據庫連接

      //開始一個數據庫事務
      con.setAutoCommit(false);

      //先刪除和Customer對象關聯的Order對象
      stmt=con.prepareStatement("delete from ORDERS where "
                            +"CUSTOMER_ID=?" );
      stmt.setLong(1,customer.getId().longValue());
      stmt.executeUpdate();

      //刪除Customer對象
      stmt=con.prepareStatement("delete from CUSTOMERS where "
                         +"ID=?" );
      stmt.setLong(1,customer.getId().longValue());
      stmt.executeUpdate();

      //提交數據庫事務
      con.commit();

    }catch(Exception e){
      try{//如果出現異常，撤銷整個事務
        con.rollback();
      }catch(SQLException sqlex){
        sqlex.printStackTrace(System.out);
      }
      throw e;
    }finally{
      try{
         stmt.close();
         con.close();
       }catch(Exception e){
         e.printStackTrace();
       }
    }
  }

  /*
   * 根據OID加載一個Customer對象
   */
  public Customer loadCustomer (long customerId) throws Exception {
    Connection con=null;
    PreparedStatement stmt=null;
    ResultSet rs=null;
    try {
      con=getConnection(); //獲得數據庫連接

      //以下是數據訪問代碼，加載Customer對象
      stmt=con.prepareStatement("select ID,NAME,AGE from CUSTOMERS where ID=?");
      stmt.setLong(1,customerId);
      rs=stmt.executeQuery();

      if(rs.next()) {
        Customer customer=new Customer();
        customer.setId(new Long(rs.getLong(1)));
        customer.setName(rs.getString(2));
        customer.setAge(rs.getInt(3));
        return customer;
      }else{
        throw new BusinessException("OID為"+customerId+"的Customer對象不存在");
      }

    }finally{
      try{
         rs.close();
         stmt.close();
         con.close();
       }catch(Exception e){
         e.printStackTrace();
       }
    }
  }

  /*
   * 按照姓名查詢滿足條件的Customer對象，同時加載與它關聯的Order對象
   */
  public List findCustomerByName(String name) throws Exception{
    HashMap map=new HashMap();
    List result=new ArrayList();

    Connection con=null;
    PreparedStatement stmt=null;
    ResultSet rs=null;
    try{
      con=getConnection(); //獲得數據庫連接

      String sqlString=" select c.ID CUSTOMER_ID,c.NAME,c.AGE,o.ID ORDER_ID, "
      +"o.ORDER_NUMBER,o.PRICE "
      +"from CUSTOMERS c left outer join ORDERS o "
      +"on c.ID =o.CUSTOMER_ID where c.NAME=?";
      stmt = con.prepareStatement(sqlString);
      stmt.setString(1,name); //綁定參數
      rs=stmt.executeQuery();
      while (rs.next())
      {
        //編歷JDBC ResultSet結果集
        Long customerId =new Long( rs.getLong(1));
        String customerName= rs.getString(2);
        int customerAge= rs.getInt(3);
        Long orderId =new Long( rs.getLong(4));
        String orderNumber= rs.getString(5);
        double price=rs.getDouble(6);

        //映射Customer對象
        Customer customer=null;
        if(map.containsKey(customerId))
          //如果在map中已經存在OID匹配的Customer對象，就獲得此對象的引用，這樣
          //就避免創建重復的Customer對象。
          customer=(Customer)map.get(customerId);
        else{
          //如果在map中不存在OID匹配的Customer對象，就創建一個Customer對象，
          //然后把它保存到map中。
          customer=new Customer();
          customer.setId(customerId);
          customer.setName(customerName);
          customer.setAge(customerAge);
          map.put(customerId,customer);
        }

        //映射Order對象
        Order order=new Order();
        order.setId(orderId);
        order.setOrderNumber(orderNumber);
        order.setPrice(price);

        //建立Customer對象與Order對象的關聯關系
        customer.getOrders().add(order);
        order.setCustomer(customer);
      }
      //把map中所有的Customer對象加入到result集合中
      Iterator iter =map.values().iterator();
      while ( iter.hasNext() ) {
          result.add(iter.next());
      }
      return result;
    }finally{
       try{
         rs.close();
         stmt.close();
         con.close();
       }catch(Exception e){
         e.printStackTrace();
       }
    }
  }

  /**
   * 生成一個新的主鍵值，取值為表的當前最大主鍵值+1，如果表不包含記錄，就返回1
   */
  private long getNextId(Connection con,String tableName) throws Exception {
    long nextId=0;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    try {
      stmt = con.prepareStatement("select max(ID) from "+tableName);
      rs = stmt.executeQuery();
      if ( rs.next() ) {
        nextId = rs.getLong(1) + 1;
        if ( rs.wasNull() ) nextId = 1;
      }
      else {
        nextId = 1;
      }
      return nextId;
    }finally {
      try{
         rs.close();
         stmt.close();
      }catch(Exception e){
         e.printStackTrace();
      }
    }
  }

  public void test()throws Exception{
    Customer customer=new Customer("Tom",21,new HashSet());
    Order order1=new Order("Tom_Order001",100,customer);
    Order order2=new Order("Tom_Order002",200,customer);
    customer.getOrders().add(order1);
    customer.getOrders().add(order2);

    saveCustomer(customer);

    customer=loadCustomer(1);
    customer.setAge(22);
    updateCustomer(customer);

    List result=findCustomerByName("Tom");
    Iterator iterator=result.iterator();
    while(iterator.hasNext()){
      customer=(Customer)iterator.next();
      System.out.println("Customer:"+customer.getId()+" "+customer.getName()+" "+customer.getAge());
      Iterator orderIterator=customer.getOrders().iterator();
      while(orderIterator.hasNext()){
        Order order=(Order)orderIterator.next();
        System.out.println("Order:"+order.getId()+" "+order.getOrderNumber()+" "+order.getPrice());
      }
    }
    deleteCustomer(customer);
  }

  public static void main(String args[])throws Exception{
    new BusinessService().test();
  }
}