以前一直用mysql做测试数据库,多人协作起来每个人都要安装配置数据库,数据源,还得防着不能把自己的jdbc.properties传上去把别人搞晕掉,现在改成轻便的嵌入式数据库hsqldb,麻烦事少了很多。使用hsqldb作为测试数据库,涉及到两个问题,一个是Web应用启动关闭的时候要同时启动和关闭hsqldb server,另外一个就是在执行单元测试的时候也要启动和关闭hsqldb server.
hsqldb有几种启动方式,其中有res,mem等方式数据库结构都是只读的,hibernate hbm2ddl会报The database is in read only mode in statement [create]。所以最终还是决定使用独立的server模式。但是需要自己控制启动和关闭。
1.让Hsqldb随Web应用启动和关闭
这个实际上就是一个listener。代码如下:
这个类修改自SpringSide1.0M3里面的相应类,把从ServletContext中获取参数的过程全部替换成了Config.getString(),这个Config是静态导入的一个Configuration对象,使用了Apache commons Configuration,读取的配置文件如下:
其中指定了数据库文件位置、数据库名称、服务端口。使用种方式来配置启动hsqldb,主要是为了后面的单元测试的情况能够和这里使用相同的一套配置。注意我把数据库文件放在了${app.home}/WEB-INF/classes/hsqldb/目录。
如上配置,在系统的jdbc.properties里面就可以这样写了:
jdbc.driverClassName = org.hsqldb.jdbcDriver
jdbc.url = jdbc:hsqldb:hsql://localhost: 9002 /mwdb
jdbc.username = sa
jdbc.password =
2.单元测试
上面的HsqlListener控制了在web应用启动的时候启动hsqldb,web应用停止的时候停止hsqldb,但是我们在做单元测试的时候,是没有启动Web应用的,于是就有可能导致无法连接到数据库。解决的方法是在单元测试运行开始之前,启动数据库。写单元测试基类如下:
上面的测试基类里面用静态初始化快初始化启动了hsqldb,并最后在onTearDown的时候关闭数据库。这个测试基类继承了Spring的测试类 AbstractTransactionalDataSourceSpringContextTests,如果你使用原始的junit TestCase,那么直接在tearDown方法中关闭数据库就好了。
3.数据库图形化访问
用惯了mysql的mysql query browser,总是想着看数据库里面的内容,怎么办呢。你的系统启动起来以后,在shell里面执行如下命令:
要注意把其中的hsqldb-xxx.jar的地址改成你自己的地址。启动之后按照你配置的数据库端口,名称等等去连接就可以了。
hsqldb有几种启动方式,其中有res,mem等方式数据库结构都是只读的,hibernate hbm2ddl会报The database is in read only mode in statement [create]。所以最终还是决定使用独立的server模式。但是需要自己控制启动和关闭。
1.让Hsqldb随Web应用启动和关闭
这个实际上就是一个listener。代码如下:
/**
* 该类的职责是在WebApp启动时自动开启HSQL服务.
* 依然使用Server方式,不受AppServer的影响.
*
* @author frank
* @author calvin
*/
public class HsqlListener implements ServletContextListener {
protected static Log logger = LogFactory.getLog(HsqlListener. class );
private final static String DEFAULT_DB_PATH = " {user.home}/springside/db " ;
public void contextInitialized(ServletContextEvent sce) {
logger.info( " HsqlListener initialize " );
String dbName = Config.getString( " metawork.hsql.dbName " );
int port = - 1 ;
try {
port = Integer.parseInt(Config.getString( " metawork.hsql.port " ));
}
catch (Exception e) {
}
if (StringUtils.isEmpty(dbName)) {
logger.error( " Cant' get hsqldb.dbName from web.xml Context Param " );
return ;
}
String path = null ;
try {
path = getDbPath(sce);
} catch (URISyntaxException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
File dbDir = new File(path);
if ( ! dbDir.exists()) {
logger.info( " Create Path: " + path);
if ( ! dbDir.mkdirs()) {
logger.error( " Can not create DB Dir for Hsql: " + dbDir);
return ;
}
}
if ( ! path.endsWith( " / " ))
path = path + " / " ;
startServer(path, dbName, port);
}
private String getDbPath(ServletContextEvent sce) throws URISyntaxException {
String path = Config.getString( " metawork.hsql.dbPath " );
if (StringUtils.isEmpty(path)) {
path = DEFAULT_DB_PATH;
}
if (path.startsWith( " {user.home} " )) {
path = path.replaceFirst( " \\{user.home\\} " , System.getProperty(
" user.home " ).replace( ' \\ ' , ' / ' ));
}
if (path.startsWith( " {webapp.root} " )) {
path = path.replaceFirst( " \\{webapp.root\\} " , sce
.getServletContext().getRealPath( " / " ).replace( ' \\ ' , ' / ' ));
}
if (path.startsWith( " {classpath} " )) {
path = path.replaceFirst( " \\{classpath\\} " , this .getClass().getClassLoader().getResource( "" ).toURI().toString().replace( " file:/ " , "" ));
}
return path;
}
private void startServer(String dbPath, String dbName, int port) {
Server server = new Server();
server.setDatabaseName( 0 , dbName);
server.setDatabasePath( 0 , dbPath + dbName);
if (port != - 1 )
server.setPort(port);
server.setSilent( true );
server.start();
logger.info( " hsqldb started " );
// 等待Server启动
try {
Thread.sleep( 800 );
}
catch (InterruptedException e) {
// do nothing
}
}
public void contextDestroyed(ServletContextEvent sce) {
String dbName = Config.getString( " metawork.hsql.dbName " );
Connection conn = null ;
try {
Class.forName( " org.hsqldb.jdbcDriver " );
conn = DriverManager.getConnection( " jdbc:hsqldb:hsql://localhost: " + Config.getString( " metawork.hsql.port " ) + " / " + dbName,
" sa " , "" );
Statement stmt = conn.createStatement();
stmt.executeUpdate( " SHUTDOWN; " );
} catch (Exception e) {
// do nothing
}
}
}
* 该类的职责是在WebApp启动时自动开启HSQL服务.
* 依然使用Server方式,不受AppServer的影响.
*
* @author frank
* @author calvin
*/
public class HsqlListener implements ServletContextListener {
protected static Log logger = LogFactory.getLog(HsqlListener. class );
private final static String DEFAULT_DB_PATH = " {user.home}/springside/db " ;
public void contextInitialized(ServletContextEvent sce) {
logger.info( " HsqlListener initialize " );
String dbName = Config.getString( " metawork.hsql.dbName " );
int port = - 1 ;
try {
port = Integer.parseInt(Config.getString( " metawork.hsql.port " ));
}
catch (Exception e) {
}
if (StringUtils.isEmpty(dbName)) {
logger.error( " Cant' get hsqldb.dbName from web.xml Context Param " );
return ;
}
String path = null ;
try {
path = getDbPath(sce);
} catch (URISyntaxException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
File dbDir = new File(path);
if ( ! dbDir.exists()) {
logger.info( " Create Path: " + path);
if ( ! dbDir.mkdirs()) {
logger.error( " Can not create DB Dir for Hsql: " + dbDir);
return ;
}
}
if ( ! path.endsWith( " / " ))
path = path + " / " ;
startServer(path, dbName, port);
}
private String getDbPath(ServletContextEvent sce) throws URISyntaxException {
String path = Config.getString( " metawork.hsql.dbPath " );
if (StringUtils.isEmpty(path)) {
path = DEFAULT_DB_PATH;
}
if (path.startsWith( " {user.home} " )) {
path = path.replaceFirst( " \\{user.home\\} " , System.getProperty(
" user.home " ).replace( ' \\ ' , ' / ' ));
}
if (path.startsWith( " {webapp.root} " )) {
path = path.replaceFirst( " \\{webapp.root\\} " , sce
.getServletContext().getRealPath( " / " ).replace( ' \\ ' , ' / ' ));
}
if (path.startsWith( " {classpath} " )) {
path = path.replaceFirst( " \\{classpath\\} " , this .getClass().getClassLoader().getResource( "" ).toURI().toString().replace( " file:/ " , "" ));
}
return path;
}
private void startServer(String dbPath, String dbName, int port) {
Server server = new Server();
server.setDatabaseName( 0 , dbName);
server.setDatabasePath( 0 , dbPath + dbName);
if (port != - 1 )
server.setPort(port);
server.setSilent( true );
server.start();
logger.info( " hsqldb started " );
// 等待Server启动
try {
Thread.sleep( 800 );
}
catch (InterruptedException e) {
// do nothing
}
}
public void contextDestroyed(ServletContextEvent sce) {
String dbName = Config.getString( " metawork.hsql.dbName " );
Connection conn = null ;
try {
Class.forName( " org.hsqldb.jdbcDriver " );
conn = DriverManager.getConnection( " jdbc:hsqldb:hsql://localhost: " + Config.getString( " metawork.hsql.port " ) + " / " + dbName,
" sa " , "" );
Statement stmt = conn.createStatement();
stmt.executeUpdate( " SHUTDOWN; " );
} catch (Exception e) {
// do nothing
}
}
}
这个类修改自SpringSide1.0M3里面的相应类,把从ServletContext中获取参数的过程全部替换成了Config.getString(),这个Config是静态导入的一个Configuration对象,使用了Apache commons Configuration,读取的配置文件如下:
metawork.hsql.dbPath
=
{classpath}hsqldb
metawork.hsql.dbName = mwdb
metawork.hsql.port = 9002
metawork.hsql.dbName = mwdb
metawork.hsql.port = 9002
其中指定了数据库文件位置、数据库名称、服务端口。使用种方式来配置启动hsqldb,主要是为了后面的单元测试的情况能够和这里使用相同的一套配置。注意我把数据库文件放在了${app.home}/WEB-INF/classes/hsqldb/目录。
如上配置,在系统的jdbc.properties里面就可以这样写了:
jdbc.driverClassName = org.hsqldb.jdbcDriver
jdbc.url = jdbc:hsqldb:hsql://localhost: 9002 /mwdb
jdbc.username = sa
jdbc.password =
2.单元测试
上面的HsqlListener控制了在web应用启动的时候启动hsqldb,web应用停止的时候停止hsqldb,但是我们在做单元测试的时候,是没有启动Web应用的,于是就有可能导致无法连接到数据库。解决的方法是在单元测试运行开始之前,启动数据库。写单元测试基类如下:
@ContextConfiguration
public class BaseDaoTestCase extends AbstractTransactionalDataSourceSpringContextTests{
private static final Logger logger = Logger.getLogger(BaseDaoTestCase. class );
static {
logger.info( " Start up hsqldb " );
String dbName = Config.getString( " metawork.hsql.dbName " );
int port = - 1 ;
port = Integer.parseInt(Config.getString( " metawork.hsql.port " ));
String path = null ;
path = getDbPath();
File dbDir = new File(path);
if ( ! dbDir.exists()) {
dbDir.mkdirs();
}
if ( ! path.endsWith( " / " ))
path = path + " / " ;
startServer(path, dbName, port);
logger.info( " Hsqldb started successfully. " );
}
@Override
protected String[] getConfigLocations() {
return new String[] { " classpath*:/context/*.xml " };
}
public void testX(){
}
private static String getDbPath(){
String path = Config.getString( " metawork.hsql.dbPath " );
if (path.startsWith( " {classpath} " )) {
try {
path = path.replaceFirst( " \\{classpath\\} " , BaseDaoTestCase. class .getClassLoader().getResource( "" ).toURI().toString().replace( " file:/ " , "" ));
} catch (URISyntaxException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return path;
}
private static void startServer(String dbPath, String dbName, int port) {
Server server = new Server();
server.setDatabaseName( 0 , dbName);
server.setDatabasePath( 0 , dbPath + dbName);
if (port != - 1 )
server.setPort(port);
server.setSilent( true );
server.start();
// 等待Server启动
try {
Thread.sleep( 800 );
}
catch (InterruptedException e) {
// do nothing
}
}
@Override
public void onTearDown(){
logger.info( " Shutdown hsqldb " );
String dbName = Config.getString( " metawork.hsql.dbName " );
Connection conn = null ;
try {
Class.forName( " org.hsqldb.jdbcDriver " );
conn = DriverManager.getConnection( " jdbc:hsqldb:hsql://localhost: " + Config.getString( " metawork.hsql.port " ) + " / " + dbName,
" sa " , "" );
Statement stmt = conn.createStatement();
stmt.executeUpdate( " SHUTDOWN; " );
} catch (Exception e) {
// do nothing
}
logger.info( " Shutdown hsqldb successfully. " );
}
}
public class BaseDaoTestCase extends AbstractTransactionalDataSourceSpringContextTests{
private static final Logger logger = Logger.getLogger(BaseDaoTestCase. class );
static {
logger.info( " Start up hsqldb " );
String dbName = Config.getString( " metawork.hsql.dbName " );
int port = - 1 ;
port = Integer.parseInt(Config.getString( " metawork.hsql.port " ));
String path = null ;
path = getDbPath();
File dbDir = new File(path);
if ( ! dbDir.exists()) {
dbDir.mkdirs();
}
if ( ! path.endsWith( " / " ))
path = path + " / " ;
startServer(path, dbName, port);
logger.info( " Hsqldb started successfully. " );
}
@Override
protected String[] getConfigLocations() {
return new String[] { " classpath*:/context/*.xml " };
}
public void testX(){
}
private static String getDbPath(){
String path = Config.getString( " metawork.hsql.dbPath " );
if (path.startsWith( " {classpath} " )) {
try {
path = path.replaceFirst( " \\{classpath\\} " , BaseDaoTestCase. class .getClassLoader().getResource( "" ).toURI().toString().replace( " file:/ " , "" ));
} catch (URISyntaxException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return path;
}
private static void startServer(String dbPath, String dbName, int port) {
Server server = new Server();
server.setDatabaseName( 0 , dbName);
server.setDatabasePath( 0 , dbPath + dbName);
if (port != - 1 )
server.setPort(port);
server.setSilent( true );
server.start();
// 等待Server启动
try {
Thread.sleep( 800 );
}
catch (InterruptedException e) {
// do nothing
}
}
@Override
public void onTearDown(){
logger.info( " Shutdown hsqldb " );
String dbName = Config.getString( " metawork.hsql.dbName " );
Connection conn = null ;
try {
Class.forName( " org.hsqldb.jdbcDriver " );
conn = DriverManager.getConnection( " jdbc:hsqldb:hsql://localhost: " + Config.getString( " metawork.hsql.port " ) + " / " + dbName,
" sa " , "" );
Statement stmt = conn.createStatement();
stmt.executeUpdate( " SHUTDOWN; " );
} catch (Exception e) {
// do nothing
}
logger.info( " Shutdown hsqldb successfully. " );
}
}
上面的测试基类里面用静态初始化快初始化启动了hsqldb,并最后在onTearDown的时候关闭数据库。这个测试基类继承了Spring的测试类 AbstractTransactionalDataSourceSpringContextTests,如果你使用原始的junit TestCase,那么直接在tearDown方法中关闭数据库就好了。
3.数据库图形化访问
用惯了mysql的mysql query browser,总是想着看数据库里面的内容,怎么办呢。你的系统启动起来以后,在shell里面执行如下命令:
"
%JAVA_HOME%/bin/javaw
"
-classpath ../webapp/WEB-INF/lib/hsqldb-
1.8.0.7
.jar org.hsqldb.util.DatabaseManager
要注意把其中的hsqldb-xxx.jar的地址改成你自己的地址。启动之后按照你配置的数据库端口,名称等等去连接就可以了。