import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Wrapper-Class for a Standalone SQL-Database with HSQLDB
* @author (c) Alexander Christian, 2007, HHN
*
*/
public class HsqldbStandalone {
/** the connection to the internal database */
Connection conn; //our connnection to the db - presist for life of program
/**
* Creates a new database-object based on a standalone file-db.
* @param db_file_name_prefix the fileprefix where the database is stored, can also contain pathinformatin, e.g. /home/mydatabase
* @throws Exception if there's an error
*/
public HsqldbStandalone(String db_file_name_prefix) throws Exception { // note more general exception
// Load the HSQL Database Engine JDBC driver
// hsqldb.jar should be in the class path or made part of the current jar
Class.forName("org.hsqldb.jdbcDriver");
// connect to the database. This will load the db files and start the
// database if it is not alread running.
// db_file_name_prefix is used to open or create files that hold the state
// of the db.
// It can contain directory names relative to the
// current working directory
conn = DriverManager.getConnection("jdbc:hsqldb:file:"
+ db_file_name_prefix, // filenames
"sa", // username
""); // password
}
/**
* Executes a sql-expression.
* Use for SQL commands like SELECT and for everything that returns a <code>ResultSet</code>
* @param expression the expression to execute
* @return the <code>ResultSet</code>
* @throws SQLException if there's a mistake in the expression
*/
public synchronized ResultSet query(String expression) throws SQLException {
Statement st = null;
ResultSet rs = null;
ResultSet returnResultSet = null;
st = conn.createStatement(); // statement objects can be reused with
// repeated calls to execute but we
// choose to make a new one each time
rs = st.executeQuery(expression); // run the query
returnResultSet = rs; // copy it
st.close(); // NOTE!! if you close a statement the associated ResultSet is
// closed too
// so you should copy the contents to some other object.
// the result set is invalidated also if you recycle an Statement
// and try to execute some other query before the result set has been
// completely examined.
return returnResultSet;
}
/**
* Executes a sql-expression.
* Use for SQL commands CREATE, DROP, INSERT and UPDATE
* @param expression the sql-expression to execute
* @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
* or <code>DELETE</code> statements, or <code>0</code> for SQL statements
* that return nothing
* @throws SQLException if there's a mistake in the expression
*/
public synchronized int update(String expression) throws SQLException {
Statement st = null;
st = conn.createStatement(); // statements
int i = st.executeUpdate(expression); // run the query
if (i == -1) throw new SQLException("db error with expression: "+expression);
st.close();
return i;
}
/**
* Prints the resultset of a query to the console
* @param rs the resultset of a query
* @throws SQLException
*/
public static void dump(ResultSet rs) throws SQLException {
// the order of the rows in a cursor
// are implementation dependent unless you use the SQL ORDER statement
ResultSetMetaData meta = rs.getMetaData();
int colmax = meta.getColumnCount();
int i;
Object o = null;
// the result set is a cursor into the data. You can only
// point to one row at a time
// assume we are pointing to BEFORE the first row
// rs.next() points to next row and returns true
// or false if there is no next row, which breaks the loop
for (; rs.next(); ) {
for (i = 0; i < colmax; ++i) {
o = rs.getObject(i + 1); // Is SQL the first column is indexed with 1 not 0
System.out.print(o.toString() + " | ");
}
System.out.println(" | ");
}
}
/**
*
* @throws SQLException
*/
public void shutdown() throws SQLException {
Statement st = conn.createStatement();
// db writes out to files and performs clean shuts down
// otherwise there will be an unclean shutdown
// when program ends
st.execute("SHUTDOWN");
conn.close(); // if there are no other open connection
}
}
