/* Copyright (c) 2001-2011, The HSQL Development Group
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
*
* Redistributions of source code must retain the above copyright notice, this
* list of conditions and the following disclaimer.
*
* Redistributions in binary form must reproduce the above copyright notice,
* this list of conditions and the following disclaimer in the documentation
* and/or other materials provided with the distribution.
*
* Neither the name of the HSQL Development Group nor the names of its
* contributors may be used to endorse or promote products derived from this
* software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
* AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
* IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
* ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
* OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
* EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
* PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
*/
package org.hsqldb.sample;
import java.sql.
Connection;
import java.sql.
DriverManager;
import java.sql.
ResultSet;
import java.sql.
ResultSetMetaData;
import java.sql.
SQLException;
import java.sql.
Statement;
/**
* Title: Testdb
* Description: simple hello world db example of a
* standalone persistent db application
*
* every time it runs it adds four more rows to sample_table
* it does a query and prints the results to standard out
*
* Author: Karl Meissner karl@meissnersd.com
*/
public class
Testdb {
Connection conn; //our connnection to the db - presist for life of program
// we dont want this garbage collected until we are done
public
Testdb(
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.jdbc.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:"
+
db_file_name_prefix, // filenames
"SA", // username
""); // password
}
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
}
//use for SQL command SELECT
public synchronized void
query(
String expression) throws
SQLException {
Statement st = null;
ResultSet rs = 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
// do something with the result set.
dump(
rs);
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.
}
//use for SQL commands CREATE, DROP, INSERT and UPDATE
public synchronized void
update(
String expression) throws
SQLException {
Statement st = null;
st =
conn.
createStatement(); // statements
int
i =
st.
executeUpdate(
expression); // run the query
if (
i == -1) {
System.
out.
println("db error : " +
expression);
}
st.
close();
} // void update()
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(" ");
}
} //void dump( ResultSet rs )
public static void
main(
String[]
args) {
Testdb db = null;
try {
db = new
Testdb("db_file");
} catch (
Exception ex1) {
ex1.
printStackTrace(); // could not start db
return; // bye bye
}
try {
//make an empty table
//
// by declaring the id column IDENTITY, the db will automatically
// generate unique values for new rows- useful for row keys
db.
update(
"CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER)");
} catch (
SQLException ex2) {
//ignore
//ex2.printStackTrace(); // second time we run program
// should throw execption since table
// already there
//
// this will have no effect on the db
}
try {
// add some rows - will create duplicates if run more then once
// the id column is automatically generated
db.
update(
"INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100)");
db.
update(
"INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200)");
db.
update(
"INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300)");
db.
update(
"INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400)");
// do a query
db.
query("SELECT * FROM sample_table WHERE num_col < 250");
// at end of program
db.
shutdown();
} catch (
SQLException ex3) {
ex3.
printStackTrace();
}
} // main()
} // class Testdb