這次備忘記主要是記錄 Spring course lab5 的過程.

来自:
http://blog.matrix.org.cn/page/joeyta

lab5 requirement 的下載位址是：
http://blog.matrix.org.cn/resources/joeyta/spring_lab_05.zip

源程式下載位址是：
http://blog.matrix.org.cn/resources/joeyta/cpttm_spring_lab.zip

要求使用 Spring JDBC 裡的 JDBCTemplate 及 operation modeling object 實作簡單的用戶電話薄系統功能.

開始備忘記：
[1] 安裝 HSQLDB 資料庫
[2] 基本資料
[3] 使用 JDBC Template 實作
[4] 使用 JDBC operation modeling object 實作

[1] 安裝 HSQLDB 資料庫：
下載 hsqldb_1_8_0_1.zip
http://sourceforge.net/project/showfiles.php?group_id=23316
解壓至 D:\cpttm\hsqldb

建立資料庫啟動檔案 D:\cpttm\hsqldb\start_db.bat
內容為:
java -cp ./lib/hsqldb.jar org.hsqldb.Server -database.0 mis -dbname.0 mis

-database.0 mis 定義產生相關的資料庫檔按以 mis 為 prefix
-dbname.0 mis 定義建立資料庫名稱為 mis
上面那個 ".0" 表示建主第一個資料庫的意思

建立資料庫管理啟動檔案 D:\cpttm\hsqldb\start_manager.bat
內容為:
java -cp ./lib/hsqldb.jar org.hsqldb.util.DatabaseManager

執行 D:\cpttm\hsqldb\start_db.bat
如下圖所示:

執行 D:\cpttm\hsqldb\start_manager.bat
如下圖所示

Recent 選擇 url
URL 為 jdbc:hsqldb:hsql://localhost/mis
mis 為資料庫名稱 (即上面 -dbname.0)
HSQLDB 預設 user 為 sa, password 為沒有

按 Ok 後,如下圖所示:

然後執行:
drop table phone if exists;
drop table customer if exists;

create table customer(
 customer_id varchar(10) not null,
 first_name varchar(30) not null,
 last_name varchar(30) not null,
 birthday date,
 address varchar(100),
 primary key(customer_id)
);

create table phone(
 customer_id varchar(10) not null,
 phone_number varchar(10) not null,
 primary key(customer_id, phone_number),
 foreign key(customer_id) references customer
);

按 View -> Refresh Tree 後, 如下圖所示:

HSQLDB 參考文檔:
http://www.hsqldb.org/web/hsqlDocsFrame.html

[2] 基本資料：
<!------------------------- beans-config-lab5.xml ---------------------->
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEA N//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>

 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName">
   <value>org.hsqldb.jdbcDriver</value>
  </property>
  <property name="url">
   <value>jdbc:hsqldb:hsql://localhost/mis</value>
  </property>
  <property name="username">
   <value>sa</value>
  </property>
  <property name="password">
   <value></value>
  </property>
 </bean>
 
 <bean id="customerDaoTemplate" class="mo.org.cpttm.spring.lab5.CustomerDaoTemplateImpl">
  <property name="dataSource">
   <ref bean="dataSource"/>
  </property>
 </bean>
 
 <bean id="customerDaoObject" class="mo.org.cpttm.spring.lab5.CustomerDaoObjectImpl">
  <property name="dataSource">
   <ref bean="dataSource"/>
  </property>
 </bean> 

</beans>
<!------------------------- beans-config-lab5.xml ---------------------->

/***************************** Customer.java *********************/
package mo.org.cpttm.spring.lab5;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class Customer {
 private String customerId;
 private String firstName;
 private String lastName;
 private Date birthday;
 private String address;
 private List phones;
 
 @Override
 public String toString() {
  String sOutput = "Customer ID: " + this.customerId + "\n";
  sOutput += "Name: " + this.firstName + " " + this.lastName + "\n";
  sOutput += "Birthday: " + this.birthday + "\n";
  sOutput += "Address: " + this.address+ "\n";
  sOutput += "Phones: " + this.getPhones().toString() + "\n";  
  return sOutput;
 }
 
 public Customer(String customerId, String firstName, String lastName, Date birthday, String address, List phones) {
  super();
  this.customerId = customerId;
  this.firstName = firstName;
  this.lastName = lastName;
  this.birthday = birthday;
  this.address = address;
  this.phones = phones;
 }

 public Customer() {
 }

 public String getAddress() {
  return address;
 }
 public void setAddress(String address) {
  this.address = address;
 }
 public Date getBirthday() {
  return birthday;
 }
 public void setBirthday(Date birthday) {
  this.birthday = birthday;
 }
 public String getCustomerId() {
  return customerId;
 }
 public void setCustomerId(String customerId) {
  this.customerId = customerId;
 }
 public String getFirstName() {
  return firstName;
 }
 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }
 public String getLastName() {
  return lastName;
 }
 public void setLastName(String lastName) {
  this.lastName = lastName;
 }
 public List getPhones() {
  if(phones == null){
   phones = new ArrayList();
  }
  return phones;
 }
 public void setPhones(List phones) {
  this.phones = phones;
 }
}
/***************************** Customer.java *********************/

/************************** ICustomerDao.java *********************/
package mo.org.cpttm.spring.lab5;

import java.util.List;

public interface ICustomerDao {
 public void insert(Customer customer);
 public void update(Customer customer);
 public void delete(Customer customer); 
 public Customer findById(String customerId);
 public List findAll();
 public int countAll();
}
/************************** ICustomerDao.java *********************/

[3] 使用 JDBC Template 實作：

/*********************** CustomerDaoTemplateImpl.java ******************/
package mo.org.cpttm.spring.lab5;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

public class CustomerDaoTemplateImpl implements ICustomerDao {
 private JdbcTemplate jdbcTemplate;

 public void setDataSource(DataSource dataSource) {
  jdbcTemplate = new JdbcTemplate(dataSource);
 }

 public int countAll() {
  return jdbcTemplate.queryForInt("select count(*) from customer");
 }

 public void delete(Customer customer) {
  jdbcTemplate.update("delete from phone where customer_id=?",
    new Object[] { customer.getCustomerId() });
  jdbcTemplate.update("delete from customer where customer_id=?",
    new Object[] { customer.getCustomerId() });
 }

 @SuppressWarnings("unchecked")
 public List findAll() {
  List rows = jdbcTemplate
    .queryForList("select * from customer,phone where customer.customer_id=phone.customer_id order by customer.customer_id");

  Customer customer = new Customer();
  List list = new ArrayList();
  String old_customer_id = "";

  for (Iterator iter = rows.iterator(); iter.hasNext();) {
   Map rowMap = (Map) iter.next();
   if (!old_customer_id.equals(rowMap.get("customer_id").toString())) {
    customer = new Customer();
    customer.setCustomerId(rowMap.get("customer_id").toString());
    customer.setFirstName(rowMap.get("first_name").toString());
    customer.setLastName(rowMap.get("last_name").toString());
    customer.setBirthday((Date) rowMap.get("birthday"));
    customer.setAddress(rowMap.get("address").toString());
   }
   customer.getPhones().add(rowMap.get("phone_number").toString());
   if (!old_customer_id.equals(rowMap.get("customer_id").toString())) {
    list.add(customer);
    old_customer_id = rowMap.get("customer_id").toString();
   }
  }

  return list;
 }

 @SuppressWarnings("unchecked")
 public Customer findById(String customerId) {
  SqlRowSet rs = jdbcTemplate.queryForRowSet(
    "select * from customer where customer_id=?",
    new Object[] { customerId });

  Customer customer = new Customer();

  if (rs.next()) {
   customer.setCustomerId(rs.getString("customer_id"));
   customer.setFirstName(rs.getString("first_name"));
   customer.setLastName(rs.getString("last_name"));
   customer.setBirthday(rs.getDate("birthday"));
   customer.setAddress(rs.getString("address"));
  }

  rs = jdbcTemplate.queryForRowSet(
    "select * from phone where customer_id=?",
    new Object[] { customerId });

  while (rs.next()) {
   customer.getPhones().add(rs.getString("phone_number"));
  }
  return customer;
 }

 public void insert(Customer customer) {
  jdbcTemplate
    .update(
      "insert into customer(customer_id,first_name,last_name,birthday,address) values(?,?,?,?,?)",
      new Object[] { customer.getCustomerId(),
        customer.getFirstName(),
        customer.getLastName(), customer.getBirthday(),
        customer.getAddress() });

  insertPhoneBatch(customer);
 }

 private void insertPhoneBatch(final Customer customer) {
  jdbcTemplate.batchUpdate(
    "insert into phone(customer_id,phone_number) values (?,?)",
    new BatchPreparedStatementSetter() {

     public int getBatchSize() {
      return customer.getPhones().size();
     }

     public void setValues(PreparedStatement pstmt, int rowIndex)
       throws SQLException {
      String phoneNumber = customer.getPhones().get(rowIndex)
        .toString();
      pstmt.setString(1, customer.getCustomerId());
      pstmt.setString(2, phoneNumber);
     }

    });
 }

 public void update(final Customer customer) {
  jdbcTemplate
    .update(
      "update customer set first_name=?,last_name=?,birthday=?,address=? where customer_id=?",
      new Object[] { customer.getFirstName(),
        customer.getLastName(), customer.getBirthday(),
        customer.getAddress(), customer.getCustomerId() });

  jdbcTemplate.update("delete from phone where customer_id=?",
    new Object[] { customer.getCustomerId() });

  insertPhoneBatch(customer);

 }

}
/*********************** CustomerDaoTemplateImpl.java ******************/

/*********************** CustomerTemplateDemo.java ******************/
package mo.org.cpttm.spring.lab5;

import java.util.Arrays;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Random;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class CustomerTemplateDemo {
 public static void main(String[] args) throws Exception {
  ApplicationContext context = new ClassPathXmlApplicationContext(
    "mo/org/cpttm/spring/lab5/beans-config-lab5.xml");
  ICustomerDao dao = (ICustomerDao) context
    .getBean("customerDaoTemplate");

  Calendar calendar = new GregorianCalendar();
  calendar.set(1980, 11 - 1, 25);

  Customer customer = new Customer();
  String randomCustomerId = "c" + new Random().nextInt(999999);
  customer.setCustomerId(randomCustomerId);
  customer.setFirstName("joeyta");
  customer.setLastName("chan");
  customer.setAddress("Rua de Areia Preto");
  customer.setBirthday(calendar.getTime());
  customer.setPhones(Arrays.asList(new Object[] { "999", "234567" }));

  dao.insert(customer);

  System.out.println("\n Before modify:");
  System.out.println(dao.findById(randomCustomerId));

  System.out.println(" Print All:");
  System.out.println(dao.findAll());

  calendar.set(1985, 3 - 1, 15);
  customer.setFirstName("Jane");
  customer.setLastName("Law");
  customer.setAddress("Rua de Casa Forte");
  customer.setBirthday(calendar.getTime());
  customer.setPhones(Arrays.asList(new Object[] { "8888", "7890" }));

  dao.update(customer);

  System.out.println();
  System.out.println("\n After modified:");
  System.out.println(dao.findById(randomCustomerId));

  System.out.println("Total " + dao.countAll() + " records");

  dao.delete(customer);
  System.out.println("\n Print All(After deleted):");
  System.out.println(dao.findAll());
  System.out.println("Total " + dao.countAll() + " records");

 }
}
/*********************** CustomerTemplateDemo.java ******************/

執行結果如下所示：
 Before modify:
Customer ID: c701550
Name: joeyta chan
Birthday: 1980-11-25
Address: Rua de Areia Preto
Phones: [234567, 999]

 Print All:
[Customer ID: c701550
Name: joeyta chan
Birthday: 1980-11-25
Address: Rua de Areia Preto
Phones: [234567, 999]
]

 After modified:
Customer ID: c701550
Name: Jane Law
Birthday: 1985-03-15
Address: Rua de Casa Forte
Phones: [7890, 8888]

Total 1 records

 Print All(After deleted):
[]
Total 0 records

[4] 使用 JDBC operation modeling object 實作
/*********************** CustomerCountOperation.java ******************/
package mo.org.cpttm.spring.lab5;

import javax.sql.DataSource;

import org.springframework.jdbc.object.SqlFunction;

public class CustomerCountOperation extends SqlFunction {

 public CustomerCountOperation(DataSource dataSource) {
  super(dataSource, "select count(*) from customer");
  compile();
 }
 
}
/*********************** CustomerCountOperation.java ******************/

/*********************** CustomerInsertOperation.java ******************/
package mo.org.cpttm.spring.lab5;

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.object.SqlUpdate;

public class CustomerInsertOperation extends SqlUpdate {
 public CustomerInsertOperation(DataSource dataSource) {
  super(
    dataSource,
    "insert into customer(customer_id,first_name,last_name,birthday,address) values(?,?,?,?,?)");
  int[] types = { Types.VARCHAR, Types.VARCHAR, Types.VARCHAR,
    Types.DATE, Types.VARCHAR };
  setTypes(types);
  compile();
 }
}
/*********************** CustomerInsertOperation.java ******************/

/*********************** PhoneInsertOperation.java ******************/
package mo.org.cpttm.spring.lab5;

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.object.SqlUpdate;

public class PhoneInsertOperation extends SqlUpdate {
 public PhoneInsertOperation(DataSource dataSource){
  super(dataSource, "insert into phone(customer_id,phone_number) values(?,?)");
  int [] types = {Types.VARCHAR, Types.VARCHAR};
  setTypes(types);
  compile();
 }
}
/*********************** PhoneInsertOperation.java ******************/

/*********************** CustomerDeleteOperation.java ******************/
package mo.org.cpttm.spring.lab5;

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.object.SqlUpdate;

public class CustomerDeleteOperation extends SqlUpdate {
 public CustomerDeleteOperation(DataSource dataSource, String tableName){
  super(dataSource, "delete from " + tableName + " where customer_id=?");
  setTypes(new int[]{Types.VARCHAR});
  compile();  
 }
 
 public void delete(String customerId){
  Object[] params = new Object[]{customerId};
  update(params);
 }
}
/*********************** CustomerDeleteOperation.java ******************/

/*********************** CustomerUpdateOperation.java ******************/
package mo.org.cpttm.spring.lab5;

import java.sql.Types;

import javax.sql.DataSource;

import org.springframework.jdbc.object.SqlUpdate;

public class CustomerUpdateOperation extends SqlUpdate {
 public CustomerUpdateOperation(DataSource dataSource){
  super(dataSource, "update customer set first_name=?,last_name=?,birthday=?,address=? where customer_id=?");
  int [] types = {Types.VARCHAR, Types.VARCHAR, Types.DATE, Types.VARCHAR, Types.VARCHAR};
  setTypes(types);
  compile();
 }
}
/*********************** CustomerUpdateOperation.java ******************/

/*********************** CustomerDaoObjectImpl.java ******************/
package mo.org.cpttm.spring.lab5;

import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

public class CustomerDaoObjectImpl implements ICustomerDao {

 private DataSource dataSource;

 public void setDataSource(DataSource dataSource) {
  this.dataSource = dataSource;
 }

 public int countAll() {
  CustomerCountOperation operation = new CustomerCountOperation(
    dataSource);
  return operation.run();
 }

 public void delete(Customer customer) {
  CustomerDeleteOperation customerPhoneDeleteOperation = new CustomerDeleteOperation(
    dataSource, "phone");
  customerPhoneDeleteOperation.delete(customer.getCustomerId());

  CustomerDeleteOperation customerDeleteOperation = new CustomerDeleteOperation(
    dataSource, "customer");
  customerDeleteOperation.delete(customer.getCustomerId());
 }

 @SuppressWarnings("unchecked")
 public List findAll() {
  CustomerQueryOperation cqQperation = new CustomerQueryOperation(
    dataSource);
  List customerList = cqQperation.execute();
  return combineCustomer(customerList);
 }

 @SuppressWarnings("unchecked")
 public List combineCustomer(List customerList) {
  Map customerMap = new HashMap();
  for (Iterator iter = customerList.iterator(); iter.hasNext();) {
   Customer customer = (Customer) iter.next();
   if (customerMap.containsKey(customer.getCustomerId())) {
    ((Customer) customerMap.get(customer.getCustomerId()))
      .getPhones()
      .add(customer.getPhones().get(0).toString());
   } else {
    customerMap.put(customer.getCustomerId(), customer);
   }
  }
  return Arrays.asList(customerMap.values().toArray(new Customer[] {}));
 } 
 
 @SuppressWarnings("unchecked")
 public Customer findById(String customerId) {
  CustomerQueryOperation cqQperation = new CustomerQueryOperation(
    dataSource, customerId);
  List customerList = cqQperation.execute();
  if (customerList.size() == 0) {
   return new Customer();
  }
  return (Customer) combineCustomer(customerList).get(0);
 }

 public void insert(Customer customer) {
  CustomerInsertOperation ciOperation = new CustomerInsertOperation(
    dataSource);
  ciOperation.update(new Object[] { customer.getCustomerId(),
    customer.getFirstName(), customer.getLastName(),
    customer.getBirthday(), customer.getAddress() });

  insertPhoneBatch(customer);
 }

 private void insertPhoneBatch(final Customer customer) {
  for (Iterator iter = customer.getPhones().iterator(); iter.hasNext();) {
   String phoneNumber = (String) iter.next();
   PhoneInsertOperation piOperation = new PhoneInsertOperation(
     dataSource);
   piOperation.update(new Object[] { customer.getCustomerId(),
     phoneNumber });
  }
 }

 public void update(final Customer customer) {
  CustomerUpdateOperation cuOperation = new CustomerUpdateOperation(
    dataSource);
  cuOperation.update(new Object[] { customer.getFirstName(),
    customer.getLastName(), customer.getBirthday(),
    customer.getAddress(), customer.getCustomerId() });

  CustomerDeleteOperation customerPhoneDeleteOperation = new CustomerDeleteOperation(
    dataSource, "phone");
  customerPhoneDeleteOperation.delete(customer.getCustomerId());

  insertPhoneBatch(customer);

 }

}
/*********************** CustomerDaoObjectImpl.java ******************/

/*********************** CustomerObjectDemo.java ******************/
package mo.org.cpttm.spring.lab5;

import java.util.Arrays;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Random;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class CustomerObjectDemo {
 public static void main(String[] args) throws Exception {
  ApplicationContext context = new ClassPathXmlApplicationContext(
    "mo/org/cpttm/spring/lab5/beans-config-lab5.xml");
  ICustomerDao dao = (ICustomerDao) context.getBean("customerDaoObject");

  Calendar calendar = new GregorianCalendar();
  calendar.set(1980, 11 - 1, 25);

  Customer customer = new Customer();
  String randomCustomerId = "c" + new Random().nextInt(999999);
  customer.setCustomerId(randomCustomerId);
  customer.setFirstName("joeyta");
  customer.setLastName("chan");
  customer.setAddress("Rua de Areia Preto");
  customer.setBirthday(calendar.getTime());
  customer.setPhones(Arrays.asList(new Object[] { "999", "234567" }));

  dao.insert(customer);

  System.out.println("\n Before modify:");
  System.out.println(dao.findById(randomCustomerId));

  System.out.println(" Print All:");
  System.out.println(dao.findAll());

  calendar.set(1985, 3 - 1, 15);
  customer.setFirstName("Jane");
  customer.setLastName("Law");
  customer.setAddress("Rua de Casa Forte");
  customer.setBirthday(calendar.getTime());
  customer.setPhones(Arrays.asList(new Object[] { "8888", "7890" }));

  dao.update(customer);

  System.out.println();
  System.out.println("\n After modified:");
  System.out.println(dao.findById(randomCustomerId));

  System.out.println("Total " + dao.countAll() + " records");

  dao.delete(customer);
  System.out.println("\n Print All(After deleted):");
  System.out.println(dao.findAll());
  System.out.println("Total " + dao.countAll() + " records");
 }
}
/*********************** CustomerObjectDemo.java ******************/

執行結果如下所示：
 Before modify:
Customer ID: c615566
Name: joeyta chan
Birthday: 1980-11-25
Address: Rua de Areia Preto
Phones: [234567, 999]

 Print All:
[Customer ID: c615566
Name: joeyta chan
Birthday: 1980-11-25
Address: Rua de Areia Preto
Phones: [234567, 999]
]

 After modified:
Customer ID: c615566
Name: Jane Law
Birthday: 1985-03-15
Address: Rua de Casa Forte
Phones: [7890, 8888]

Total 1 records

 Print All(After deleted):
[]
Total 0 records

項目結構如下圖所示：

注意, 必須加下以下 jar 檔：
spring.jar
log4j.jar
commons-logging.jar
junit.jar
hsqldb.jar
commons-pool.jar
commons-dbcp.jar
commons-collections.jar

參考官方文檔
http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html