hsqldb from java app

Question

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

I think by default that hsqldb re-initializes the database on each run. And if it didn't I think that you still wouldn't get your expected results. The creation of the employee table would fail on the second run. See http://stackoverflow.com/questions/3551988/hsqldb-and-hibernate-jpa-not-persisting-to-disk — DaveH, Mar 11 '11 at 15:31
Also, you should consider closing the ResultSet, Statement and connection objects, preferably in a finally block. I don;t think that'll cure your problem, but it won't hurt it. — DaveH, Mar 11 '11 at 15:38

Michael Berry · Answer 1 · 2011-03-11T17:38:18.190

1

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.

edited Mar 11 '11 at 17:38

answered Mar 11 '11 at 15:34

Michael Berry

70,193
21
157
216

4

HSQLDB 2.1 supports IF NOT EXISTS – fredt Mar 11 '11 at 17:05
@fredt thanks for pointing that out, I shall head off and use it in my own project now! I was clearly living a bit behind the times. – Michael Berry Mar 11 '11 at 17:15

score 1 · Answer 2 · answered Mar 11 '11 at 16:04

1

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.

answered Mar 11 '11 at 16:04

Gilbert Le Blanc

50,182
6
67
111

is there a way to set the write_delay value programmatically? – markjason72 Mar 12 '11 at 10:41
You use statement.execute("SET WRITE_DELAY 0"). With HSQLDB 2.x the statement is "SET FILES WRITE DELAY 0". This is done only once per database. With 2.x this can be used on the URL "jdbc:hsqldb:file:C:/code/java/march112011aDB ;hsqldb.write_delay=false" This is enough to ensure data is fully persisted at each commit. – fredt Mar 12 '11 at 18:20

score 0 · Answer 3 · answered Mar 11 '11 at 15:34

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

score 0 · Answer 4 · answered Mar 24 '11 at 08:44

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 ....

answered Mar 24 '11 at 08:44

Alfonso

1

All versions of 2.0 inc RC 1-4 are revealing that the hsqldb.jar is version 1.8.0.1. Your v2.0 DB will not connect to any of these jars. Error code: UNIQUE line 1 not COLLATE (as in v1.8). Serious problems afoot for users thinking they are using v2.0. They are not. A fix is now urgently needed. – Alfonso Mar 24 '11 at 09:12

RPitre · Answer 5 · 2011-07-31T01:14:06.057

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.

hsqldb from java app

5 Answers5