The Motivation
I've used lots of methods to transform data between databases and object code. From hand-coded SQL to JDO to EJB. I've never found a method I liked particularly well. This distaste has become especially acute since adopting test-driven development (TDD) as a guiding philosophy.
Unit-testing should have as few barriers as possible. For relational databases those barriers range from external dependencies (is the database running?) to speed to keeping the relational schema synchronized with your object model. For these reasons it is vital to keep database access code away from the core object model and to test as much as possible without touching a real database.
This has often led me to one of two patterns. The first is externalizing all data access to domain objects and their relationships to separate classes or interfaces. These are typically data store objects that can retrieve, edit, delete and add domain entities. This is the easiest to mock-out for unit-testing, but tends to leave your domain model objects as data-only objects with little or no related behavior. Ideally access to child records would be directly from the parent object rather than handing the parent object to some third-party class to determine the children.
The other method has been to have the domain objects have access to an interface into the data-mapping layer a la Martin Fowler’s Data Mapper pattern. This has the advantage of pushing object relationships inside the domain model where the object-relational interface can be expressed once. Classes that use the domain model are unaware of the persistence mechanism because it is internalized into the domain model itself. This keeps your code focused on the business problem you are trying to solve and less about the object-relational mapping mechanism.
My current project involves crunching a number of baseball statistics and running simulations with the data. Since the data was already in a relational database it was a chance for me to explore the Hibernate object-relational mapping system. I have been very impressed with Hibernate, but I ran into the problem was trying to insert a layer of indirection while using Hibernate as my data mapper for unit-testing. The extra layer was so flimsy that it felt embarrassing to write it. The real deployed version was simply a pass-through to a Hibernate-specific implementation. Even worse, the mock versions had more complexity in them than the real "production" version simply because they didn't have some of the basic object storage and mapping that came with Hibernate.
I also had enough complex Hibernate query usage that I wanted to unit-test this significat portion of the application. However, testing against a ‘live’ database is a bad idea, because it almost invariably introduces a maintenance nightmare. In addition, since tests are best when they are independent from each other, using the same obvious primary keys in test fixture data means you have to create code to clean the database before each test case, which is a real problem when lots of relationships are involved
By using HSQLDB and Hibernate's powerful schema-generation tool I was able to unit-test the mapping layer of the application and find numerous bugs in my object queries I would not have found as easily by manual testing. With the techniques outlines below I was able unit-test my entire application during development with no compromises in test coverage.
Setting up HSQLDB
I used version 1.7.3.0 of HSQLDB. To use an in-memory version of the database you need to invoke the static loader for the org.hsqldb.jdbcDriver. Then when you get a JDBC connection you use JDBC url such as jdbc:hsqldb:mem:yourdb where 'yourdb' is the name of the in-memory database you want to use.
Since I'm using Hibernate (3.0 beta 4), I hardly ever need to touch real-live JDBC objects. Instead I can let Hibernate do the heavy lifting for me--including automatically creating the database schema from my Hibernate mapping files. Since Hibernate creates its own connection pool it will automatically load the HSQLDB JDBC driver based on the configuration code lives in a class called TestSchema. Below is the static initializer for the class.
?1?public?class?TestSchema?{
?2?
?3?????static?{
?4?????????Configuration?config?=?new?Configuration().
?5?????????????setProperty("hibernate.dialect",?"org.hibernate.dialect.HSQLDialect").
?6?????????????setProperty("hibernate.connection.driver_class",?"org.hsqldb.jdbcDriver").
?7?????????????setProperty("hibernate.connection.url",?"jdbc:hsqldb:mem:baseball").
?8?????????????setProperty("hibernate.connection.username",?"sa").
?9?????????????setProperty("hibernate.connection.password",?"").
10?????????????setProperty("hibernate.connection.pool_size",?"1").
11?????????????setProperty("hibernate.connection.autocommit",?"true").
12?????????????setProperty("hibernate.cache.provider_class",?"org.hibernate.cache.HashtableCacheProvider").
13?????????????setProperty("hibernate.hbm2ddl.auto",?"create-drop").
14?????????????setProperty("hibernate.show_sql",?"true").
15?????????????addClass(Player.class).
16?????????????addClass(BattingStint.class).
17?????????????addClass(FieldingStint.class).
18?????????????addClass(PitchingStint.class);
19?
20?????????HibernateUtil.setSessionFactory(config.buildSessionFactory());
21?????}
22?
Hibernate provides a number of different ways to configure the framework, including programmatic configuration. The code above sets up the connection pool. Note that the user name 'sa' is required to use HSQLDB's in-memory database. Also be sure to specify a blank as the password. To enable Hibernate's automatic schema generation set the hibernate.hbm2ddl.auto property to 'create-drop'.
Testing In Practice
My project is crunching a bunch of baseball statistics so I add the four classes that I'm mapping ( Player, PitchingStint, BattingStint and FieldingStint). Finally I create a Hibernate SessionFactory and insert it into the HibernateUtil class which simply provides a single access method for my entire application for Hibernate sessions. The code for the HibernateUtil is below:
?1?import?org.hibernate.*;
?2?import?org.hibernate.cfg.Configuration;
?3?
?4?public?class?HibernateUtil?{
?5?
?6?????private?static?SessionFactory?factory;
?7?
?8?????public?static?synchronized?Session?getSession()?{
?9?????????if?(factory?==?null)?{
10?????????????factory?=?new?Configuration().configure().buildSessionFactory();
11?????????}
12?????????return?factory.openSession();
13?????}
14?
15?????public?static?void?setSessionFactory(SessionFactory?factory)?{
16?????????HibernateUtil.factory?=?factory;
17?????}
18?}
19?
Since all of my code (production code as well as unit-tests) get their Hibernate sessions from the HibernateUtil I can configure it in one place. For unit-tests the first bit of code to access the TestSchema class will invoke the static initializer which will setup Hibernate and inject the test SessionFactory into the HibernateUtil. For production code the SessionFactory will be initialized lazily using the standard hibernate.cfg.xml configuration mechanism.
So what does this look like in the unit-tests? Below is a snippet of a test that checks the the logic for determining what positions a player is eligible to play at for a fantasy baseball league:
?1??public?void?testGetEligiblePositions()?throws?Exception?{
?2?????????Player?player?=?new?Player("playerId");
?3?????????TestSchema.addPlayer(player);
?4?
?5?????????FieldingStint?stint1?=?new?FieldingStint("playerId",?2004,?"SEA",?Position.CATCHER);
?6?????????stint1.setGames(20);
?7?????????TestSchema.addFieldingStint(stint1);
?8?
?9?????????Set<Position>?positions?=?player.getEligiblePositions(2004);
10?????????assertEquals(1,?positions.size());
11?????????assertTrue(positions.contains(Position.CATCHER));
12?????}
13?
I first create a new Player instance and add it to the TestSchema via the addPlayer() method. This step
must occur first because the FieldingStint class has a foreign-key relationship to the Player class. If I didn't add this instance first I would get a foreign-key constraint violation when I try to add the FieldingStint. Once the test-fixture is in place I can test the getEligiblePositions() method to see that it retrieves the correct data. Below is the code for the addPlayer() method in the TestSchema. You will notice that Hibernate is used instead of bare-metal JDBC code:
?1?public?static?void?addPlayer(Player?player)?{
?2?????????if?(player.getPlayerId()?==?null)?{
?3?????????????throw?new?IllegalArgumentException("No?primary?key?specified");
?4?????????}
?5?
?6?????????Session?session?=?HibernateUtil.getSession();
?7?????????Transaction?transaction?=?session.beginTransaction();
?8?????????try?{
?9?????????????session.save(player,?player.getPlayerId());
10?????????????transaction.commit();
11?????????}
12?????????finally?{
13?????????????session.close();
14?????????}
15?????}
16?
One of the most important things in unit-testing is to keep your test-cases isolated. Since this method still involves a database, you need a way to clean your database prior to each test case. I have four tables in my schema so I wrote a reset() method on the TestSchema that removes all rows from the tables using JDBC. Note because HSQLDB knows about foreign keys, the order in which the tables are deleted is important. Here is the code:
?1?public?static?void?reset()?throws?SchemaException?{
?2?????????Session?session?=?HibernateUtil.getSession();
?3?????????try?{
?4?????????????Connection?connection?=?session.connection();
?5?????????????try?{
?6?????????????????Statement?statement?=?connection.createStatement();
?7?????????????????try?{
?8?????????????????????statement.executeUpdate("delete?from?Batting");
?9?????????????????????statement.executeUpdate("delete?from?Fielding");
10?????????????????????statement.executeUpdate("delete?from?Pitching");
11?????????????????????statement.executeUpdate("delete?from?Player");
12?????????????????????connection.commit();
13?????????????????}
14?????????????????finally?{
15?????????????????????statement.close();
16?????????????????}
17?????????????}
18?????????????catch?(HibernateException?e)?{
19?????????????????connection.rollback();
20?????????????????throw?new?SchemaException(e);
21?????????????}
22?????????????catch?(SQLException?e)?{
23?????????????????connection.rollback();
24?????????????????throw?new?SchemaException(e);
25?????????????}
26?????????}
27?????????catch?(SQLException?e)?{
28?????????????throw?new?SchemaException(e);
29?????????}
30?????????finally?{
31?????????????session.close();
32?????????}
33?????}
34?
When bulk deletes become finalized in Hibernate 3.0 we should able to remove this last bit of direct JDBC from our application. Until then we have to get a Connection and issue direct SQL to the database.
Be sure not to close your Connection, closing the Session is sufficient for resource cleanup. Out of habits developed from writing lots of hand-crafted JDBC code, the first version closed the JDBC Connection. Since I configured Hibernate to create a connection pool with only one Connection I completely torpedoed any tests after the first one.Be sure to watch out for this!
Since you can never be sure what state the database may be in when your test class is running (imagine running all of your test cases), you should include database cleanup in your setUp() methods like so:
1?public?void?setUp()?throws?Exception?{
2?????????TestSchema.reset();
3?????}
4?
Conclusion
Being able to test against a real-live RDBMS without all of the hassles of trying to run tests against your deployed database is essential, even when working with sophisticated O/R mappers like Hibernate. The example I showed here is not exclusive to Hibernate and could probably be made to work with JDO or TopLink, though Hibernate makes this kind of testing particularly easy since it has a built-in schema generation tool. With a setup like the one described above you don't ever have to leave the comfort of your IDE and still have extensive test coverage over your code.
一 什么是HSQLDB
HSQLDB具有以下特點：
l
????????
是一個開放源代碼的JAVA
數據庫
l
????????
具有標準的SQL
語法和JAVA
接口
l
????????
HSQLDB
可以自由使用和分發
l
????????
非常簡潔和快速的
l
????????
具有內存數據庫，獨立數據庫和C/S
數據庫三種方式
l
????????
可是在APPLET
中使用
更多的細節：
l
????????
索引可以被創建和自動使用
l
????????
支持事務處理
l
????????
允許表關聯
l
????????
完整性引用和約束
l
????????
支持JAVA存儲過程和函數
l
????????
數據庫可以生成SQL腳本
l
????????
使用用戶名，密碼，訪問權限等安全機制
l
????????
可以被JAVA1.1和JAVA2編譯
建立在HypersonicSQL基礎上的HSQLDB，是一個通用目的的數據庫，非常的小，而且易于安裝和使用。可以用于APPLETS中 ，測試中，應用系統中。
由于提供了標準SQL和JDBC接口，HSQLDB可以方便的和其他數據庫之間進行數據轉換。
HSQLDB的當前最新版本是1.7.1，以壓縮包的形式提供，包括可以使用的JAR文件，文檔，源代碼，測試程序，例子等。
二
操作模式介紹
HSQLDB有兩種操作模式：
l
????????
進程內模式（只用在同一個JVM里的應用程序才可以訪問數據庫）
l
????????
C/S模式（多個計算機/系統可以訪問同一個數據庫）
?
進程內訪問模式
進程內訪問模式也就是獨立模式。這里的獨立模式是相對于C/S模式（客戶端程序訪問數據庫服務器）而言的。這里，數據庫和應用程序運行在同一個JVM下。這個時候的數據庫實際上就是相當于被應用程序調用的代碼庫。程序和數據庫通過通用的JDBC調用進行通訊，但是這種調用是內部調用，不需要通過網絡通訊。
在這個模式下，同一時間一個數據庫只能有一個應用程序訪問，否則，就要使用C/S模式（允許多個JVM或者計算機在同一時間訪問同一個數據庫）。
?
這種模式下的JDBC的URL如下：
jdbc:hsqldb:test
這里，test是數據庫文件名。另一個例子（WINDOWS系統下）：
jdbc:hsqldb:c:\db\test
?
?
C/S
訪問模式
這種模式下數據庫和應用程序不是運行在同一個JVM進程下，而是有自己獨立的進程或者是獨立的機器。 不需要客戶端程序進入服務器的文件系統。這種模式下的數據庫操作模式和一些大的數據庫（比如SQL SERVER,ORACLE等）沒什么區別的。可以在INTERNET或者INTRANET。
HSQLDB除了有自己的訪問協議，還支持標準的HTTP協議，從而可以穿越防火墻或者代理服務器來訪問數據庫。
In all Server modes the actual database file name is specified in the Java command that starts the server. This can be the dot "." for all-in-memory operation or the path for the database name
?
服務器模式一共有三種：SERVER,WEBSERVER和SERVLET。
?
l
????????
SERVER
這種模式下的通訊協議是建立在TCP/IP基礎上的HSQL專有協議。每個客戶端都有一個獨立的連接。這種模式的響應速度是非常快的，如果使用C/S模式，應該更多的采用這種服務模式。
這種模式下的JDBC URL是：
jdbc:hsqldb:hsql://hsqldbsrv
這里，hsqldbsrv是機器名稱。如果一臺機器上跑多個服務器，需要指定端口，例如：jdbc:hsqldb:hsql://hsqldbsrv:9002，如果是本地計算機，則使用localhost：jdbc:hsqldb:hsql://localhost。
?
l
????????
WEBSERVER
有些時候，由于防火墻或者代理服務器的存在，需要使用HTTP協議進行通訊，系統提供一個小而簡單的WEBSERVER用來相應針對數據庫的查詢，例如：
jdbc:hsqldb:http://websrv
?
l
????????
SERVLET
這種模式和WEBSERVER模式很類似，數據庫運行在一個SERVLET里，而SERVLET可以運行在幾乎所有的WEBSERVER里。而且和JAVA SERVLETE API兼容（測試環境是J2DK2.1）。這是通過網絡直接訪問的。如果你的SERVLET不能直接訪問這個數據庫，就不要使用這種模式。
?
全內存訪問（All-In-Memory
）模式
所謂全內存訪問模式，就是所有的數據（包括索引和記錄）都保存在主內存里。這意味著數據庫的大小是受到內存大小的限制的（不能超過內存的大小）。支持這種模式的原因是：
l
????????
在非日志模式下，這種模式稍微快些
l
????????
可以在APPLET下使用
l
????????
用來存儲臨時數據（應用系統的數據緩存）All-In-Memory
JDBC URL如下：
jdbc:hsqldb:.
?
內存和硬盤結合訪問模式
?
在這種模式下，數據庫的改變會寫入到硬盤中，這就意味著在數據庫啟動時，內存里的表會根據他們的數據重新創建。或者說，可以創建表來保存數據，在訪問數據庫時，只有少量記錄時保存在內存里的。可以在創建的時候使用''''CREATE CACHED TABLE''''來代替''''CREATE TABLE''''。從而支持大表（這些表的記錄相對于內存來說太大了）。被緩存的表的索引也可以保存到硬盤中。因此，數據庫的大小就可以不受到內存大小的限制。進入緩存表要比從內存表里獲取數據要慢些。從1.7.0版本開始，支持第三種模式：數據可以存儲在文本文件（如CSV格式的文件）中。對應的語句時：''''CREATE TEXT TABLE''''。
在關閉數據庫前，當前狀態會被保存到磁盤中。緩存表中的數據會被保存到一個單獨的文件中。啟動HSQLDB時，數據庫從磁盤中載入數據（SQL腳本被執行），如果數據庫被毀壞（比如使用Ctrl+C或者斷電），數據也不會丟失。這是因為當下次數據庫重新啟動時，它使用腳本恢復到最近一次（有腳本文件的那次）的狀態。
?
?
混合綁定模式
所有的模式都可以在一個程序里使用，系統可以在統一時間使用這四種模式，去連接四種不同的數據庫，例如：
c1=DriverManager.getConnection("jdbc:hsqldb:.","sa","");
c2=DriverManager.getConnection("jdbc:hsqldb:test","sa","");
c3=DriverManager.getConnection("jdbc:hsqldb:http://dbserver","sa","");
c4=DriverManager.getConnection("jdbc:hsqldb:hsql://dbserver","sa","");
在這個例子中，四個連接被打開：
c1是內存數據庫；c2打開的是本地數據庫test；c3使用http協議連接dbserver數據庫；c4也是連接dbserver機器，但是使用的是更快的hsql協議。這里的限制就是：只有一個進程內的全內存進程是可用的。
?
比較
每種模式或配置都有不同的細節和好壞兩個方面：
l
????????
事務處理
對于webserver和servlet模式而言，由于HTTP協議是無狀態的，因此，每個查詢數據庫都建立新的連接。每次查詢都需要發送用戶名和密碼到數據庫中，然后建立一個新的連接，同時也建立一個新的事務（因為事務是綁定到連接中的）。可以使用’cookies’,但是現在還沒有實現。
l
????????
并發訪問
SERVER模式允許系統和管理工具（比如DatabaseManager同時訪問數據庫）。
l
????????
數據庫性能優化因素
內存數據庫不需要訪問系統，因此是最快的。其他模式的數據庫需要訪問文件系統，每個INSERT/UPDATE/DELETE操作都要保存到磁盤中，因此速度慢些。如果select和delete查詢命中了緩存表的信息，則速度幾乎和內存表速度一樣快，否則就要慢許多（因為要和操作系統的文件系統交互）。
l
????????
每個statement的傳輸時間
在SERVER模式，每個statement都需要通過TCP/IP協議傳送到服務端，然后將結果返回到客戶端。而webserver和servlet模式則需要更多的時間，因為每次statement都需要重新建立連接。相對照的，進程內模式則是在一個系統內部傳送數據，就快多了。
?
l
????????
以APPLET方式運行
這就是全內存操作。