/*
* Copyright (c) MuleSoft, Inc. All rights reserved. http://www.mulesoft.com
* The software in this package is published under the terms of the CPAL v1.0
* license, a copy of which has been included with this distribution in the
* LICENSE.txt file.
*/
package org.mule.tck.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
/**
* Helper class for creating a derby database.
*/
public class MuleDerbyTestDatabase
{
public static final String DERBY_PROPERTIES_FILE = "derby.properties";
private final String databaseNameProperty;
private String connectionString;
/**
* @param databaseNameProperty name of the property with the database name in derby.properties file.
* To add a new database change derby.properties file.
*/
public MuleDerbyTestDatabase(String databaseNameProperty)
{
this.databaseNameProperty = databaseNameProperty;
}
/**
* Creates an empty database with a table named TEST.
*
* @throws Exception
*/
public void startDatabase() throws Exception
{
String dbName = MuleDerbyTestUtils.loadDatabaseName(DERBY_PROPERTIES_FILE, databaseNameProperty);
MuleDerbyTestUtils.defaultDerbyCleanAndInit("derby.properties", databaseNameProperty);
connectionString = "jdbc:derby:" + dbName;
emptyTestTable();
}
/**
* Stops the created database.
*
* @throws SQLException
*/
public void stopDatabase() throws SQLException
{
MuleDerbyTestUtils.stopDatabase();
}
/**
* Remove all the rows from TEST table.
*
* @throws SQLException
*/
public void emptyTestTable() throws Exception
{
try
{
execSqlUpdate("DELETE FROM TEST");
}
catch (Exception e)
{
execSqlUpdate("CREATE TABLE TEST(ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,DATA VARCHAR(255), TYPE NUMERIC, ACK NUMERIC)");
}
}
/**
* Inserts a record in the TEST table.
*/
public void insertIntoTest(String data, int type)
{
try
{
execSqlUpdate("INSERT into Test (data, type) VALUES ('" + data + "', " + type + ")");
}
catch (Exception e)
{
throw new RuntimeException(e);
}
}
/**
* @return a new Connection to access the database.
* @throws Exception
*/
public Connection getConnection() throws Exception
{
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
return DriverManager.getConnection(connectionString);
}
/**
* Executes a query against the database.
*
* @param sql SQL query
* @return
* @throws Exception
*/
public List execSqlQuery(String sql) throws Exception
{
Connection con = null;
try
{
con = getConnection();
return (List) new QueryRunner().query(con, sql, new ArrayListHandler());
}
finally
{
if (con != null && !con.isClosed())
{
con.close();
}
}
}
/**
* Executes an SQL statement against the database.
* For queries use execSqlQuery.
*
* @param sql SQL query
* @return
* @throws Exception
*/
public int execSqlUpdate(String sql) throws Exception
{
Connection con = null;
try
{
con = getConnection();
return new QueryRunner().update(con, sql);
}
finally
{
if (con != null && !con.isClosed())
{
con.close();
}
}
}
}