hsqldb from java app

I am writing a desktop app in java to add/display employees.I thought of using hsqldb.I created the program as below

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class HSQLDBManualOp {

    /**
     * @param args
     */
    public static void main(String[] args) {
        Connection connection = null;  
        ResultSet resultSet = null;  
        Statement statement = null;
        String createtablestr = "CREATE TABLE employeedetails (EMPNAME varchar(20));";
        String insertstr1 = "INSERT INTO employeedetails (EMPNAME) VALUES ('EMPNAME1')";    
        String insertstr2 = "INSERT INTO employeedetails (EMPNAME) VALUES ('EMPNAME2')";    
        String insertstr3 = "INSERT INTO employeedetails (EMPNAME) VALUES ('EMPNAME3')"; 
        try {
            Class.forName("org.hsqldb.jdbcDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace开发者_如何学编程();
        }
        try {
            connection = DriverManager.getConnection("jdbc:hsqldb:file:C:/code/java/march112011aDB", "SA", "");
        } catch (SQLException e) {
            e.printStackTrace();
        }  
        if (connection == null){
            System.out.println(" connection null");
            return;
        }
        try {
            statement = connection.createStatement();
            statement.executeUpdate(createtablestr);

            statement.executeUpdate(insertstr1);

            statement.executeUpdate(insertstr2);
            statement.executeUpdate(insertstr3);

            resultSet = statement.executeQuery("SELECT EMPNAME FROM EMPLOYEEDETAILS");
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            while (resultSet.next()) {  
                System.out.println("EMPLOYEE NAME:" + resultSet.getString("EMPNAME"));  
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

}

}

when I ran the program ,I got the output

EMPLOYEE NAME:EMPNAME1
EMPLOYEE NAME:EMPNAME2
EMPLOYEE NAME:EMPNAME3

Then I changed the insert statements to add EMPNAME4,EMPNAME5,EMPNAME6

I expected it to print employees 1,2,3,4,5,6 but when I ran the code,I got only the new values.

EMPLOYEE NAME:EMPNAME4
EMPLOYEE NAME:EMPNAME5
EMPLOYEE NAME:EMPNAME6

Is it not possible to persist the values when using jdbc:hsqldb:file ? I also would like to check if the table already exists..and execute the create table statement only if the table does not exist.Can anyone tell me how to do this?

Or do I have to use server mode?If so how do I create db from within the program?

please help..this is my first attempt at jdbc/hsqldb..

thanks,

mark

We encountered this problem in our Java application.

Your create table statement creates an in-memory table.

In addition to the SET WRITE_DELAY 0 statement, you'll need to do an SQL COMMIT after the inserts, and an SQL SHUTDOWN when you want HSQL to actually write the in-memory rows to the configuration file.

As far as I know, the table won't override the previous one if you attempt to create it on a second run - it'll just fail with a SQLException. So the horrible way is to create the table, then catch a SQL exception if its already there and do something else. I don't know of a nicer way with HSqlDB, it's the exact same technique I use with a program I'm working on at the moment.

There is a CREATE TABLE IF NOT EXISTS command in many SQL based languages, but unfortunately it's not in HSqlDB.

EDIT: As per the comment below, it looks like something similar is actually in version 2.1.

I had a problem with using an in-process HSQLDB before where it wasn't persisting to the database. I found that changing the WRITE_DELAY parameter fixed it. Try adding the following to your .script file (it should go near the top, probably after something like GRANT DBA TO SA):

SET WRITE_DELAY 0

Current download versions of HSQLDB v2.1.0 are not version 2.1.0 but the old 1.8.1 version. This has been the case since beginning of February.

Anyone using a version 2.0 for their DB will not be able to use it.

it appears there a problems afoot with the current 2.0 version and OpenOffice v3.3

Please revert back to version 1.8 if you can ....

I ran into this problem and spent an hour or so trying to figure it out. Here's what helped me. When you start up a file based HSQLDB for the first time in your java app, several files are created in the root of the db directory that is defined in the jdbc url. One of those files is named mydbname.script, in that file look for the line SET FILES WRITE DELAY and set the value to 0. You only need to do this if the first time you started your HSQLDB database without hsqldb.write_delay=false property. So, if you forget to add that write_delay property the first time you start up your HSQLDB database, then you'll need to update the 'mydbname.script' file. Every time you start up the db that script is executed. Hope that helps.