/* Copyright (c) 2001-2010, 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.test;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Calendar;
import java.util.GregorianCalendar;
/**
* @author karl
*
*/
public class TestKarl {
final static String SHUTDOWN = "SHUTDOWN";
final static String SHUTDOWN_IMMEDIATELY = "SHUTDOWN IMMEDIATELY";
final static String SHUTDOWN_COMPACT = "SHUTDOWN COMPACT";
/**
*
* @param args
*/
public static void main(String[] args) {
Connection c = null;
try {
//copy db
File f = new File("db");
if (!f.exists()) {
f.mkdir();
} else {
File[] list = f.listFiles();
for (int i = 0; i < list.length; i++) {
list[i].delete();
}
}
copy("save/brwahl.properties", "db/brwahl.properties");
copy("save/brwahl.script", "db/brwahl.script");
copy("save/waehler.csv", "db/waehler.csv");
// config test
String shutdown = SHUTDOWN;
boolean autocommit = false;
Class.forName("org.hsqldb.jdbc.JDBCDriver");
c = DriverManager.getConnection("jdbc:hsqldb:file:db/brwahl",
"sa", "");
c.setAutoCommit(autocommit);
// open ok
printMeta(c);
printTable(c, "WAEHLER");
// test existing
doUpdateInsertDeleteWaehler(c);
printTable(c, "WAEHLER");
// test new
doCreateTableTest(c);
printTable(c, "TEST");
// colse
Statement st = c.createStatement();
st.execute(shutdown);
st.close();
c.close();
c = null;
// reopen test
System.out.println("\nDB OK? ...");
c = DriverManager.getConnection("jdbc:hsqldb:file:db/brwahl",
"sa", "");
c.setAutoCommit(false);
printTable(c, "WAEHLER");
printTable(c, "TEST");
doUpdateInsertDeleteWaehler(c);
c.rollback();
if (!autocommit) {
doCreateTableTest(c);
c.rollback();
}
c.close();
c = null;
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if (c != null) {
try {
c.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/**
* @param p_connection
* @throws SQLException
*/
private static void doCreateTableTest(Connection p_connection)
throws SQLException {
System.out.println("CREATE TESTTABLE START ...");
Statement st = p_connection.createStatement();
st.executeUpdate("DROP TABLE TEST IF EXISTS");
st.executeUpdate("CREATE TABLE TEST (TEST INTEGER)");
st.close();
System.out.println("END CREATE TESTTABLE");
System.out.println("INSERT INTO TESTTABLE START ...");
PreparedStatement p = p_connection.prepareStatement(
"INSERT INTO TEST (TEST) values (?)");
p.setInt(1, 123);
p.execute();
p.close();
System.out.println("END INSERT INTO TESTTABLE");
}
/**
* @param p_connection
* @throws SQLException
*/
private static void doUpdateInsertDeleteWaehler(Connection p_connection)
throws SQLException {
System.out.println("UPDATE WAEHLER START ...");
PreparedStatement p = p_connection.prepareStatement(
"UPDATE WAEHLER SET AUSTRITTSDATUM=? WHERE NAME=?");
p.setDate(1, null);
p.setString(2, "Muster1");
p.execute();
p.close();
System.out.println("END UPDATE WAEHLER");
System.out.println("INSERT INTO WAEHLER START ...");
p = p_connection.prepareStatement(
"INSERT INTO WAEHLER (NAME, AUSTRITTSDATUM) VALUES (?,?)");
Calendar cal = GregorianCalendar.getInstance();
p.setString(1, "Muster3");
p.setDate(2, new Date(cal.getTimeInMillis()), cal);
p.execute();
p.close();
System.out.println("END INSERT INTO WAEHLER");
System.out.println("DELETE FROM WAEHLER START ...");
p = p_connection.prepareStatement(
"DELETE FROM WAEHLER WHERE NAME = ?");
p.setString(1, "Muster2");
p.execute();
p.close();
System.out.println("END DELETE FROM WAEHLER");
}
/**
* @param p_connection
* @param p_table
* @throws SQLException
*/
private static void printTable(Connection p_connection,
String p_table) throws SQLException {
System.out.println("GET TABLE " + p_table + " START ...");
Statement st = p_connection.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM " + p_table);
int col_count = rs.getMetaData().getColumnCount();
for (int i = 1; i <= col_count; i++) {
System.out.print(rs.getMetaData().getColumnLabel(i) + "\t");
}
System.out.println("");
while (rs.next()) {
for (int i = 1; i <= col_count; i++) {
System.out.print(rs.getObject(i));
System.out.print("\t");
}
System.out.println("");
}
st.close();
System.out.println("... END GET TABLE " + p_table);
}
/**
* @param p_connection
* @throws SQLException
*/
private static void printMeta(Connection p_connection)
throws SQLException {
System.out.println("GET METADATA START ...");
ResultSet rs = p_connection.getMetaData().getTables(null, null, null,
null);
System.out.println(rs.toString());
int col_count = rs.getMetaData().getColumnCount();
for (int i = 1; i <= col_count; i++) {
System.out.print(rs.getMetaData().getColumnLabel(i) + "\t");
}
System.out.println("");
while (rs.next()) {
for (int i = 1; i <= col_count; i++) {
System.out.print(rs.getObject(i));
System.out.print("\t");
}
System.out.println("");
}
System.out.println("END GET METADATA");
}
private static void copy(String src, String dest) throws IOException {
File inputFile = new File(src);
File outputFile = new File(dest);
if (!inputFile.exists()) {
return;
}
FileInputStream in = new FileInputStream(inputFile);
FileOutputStream out = new FileOutputStream(outputFile);
int c;
while ((c = in.read()) != -1) {
out.write(c);
}
in.close();
out.close();
}
}