Java and OpenOffice BASE db through HSQLDB jdbc

Java and OpenOffice BASE db
G.Morreale

Introduction:

A Base document can create an HSQLDB database that is stored inside of the Base document.
OOo documents are stored as zip files and Base documents are no exception.

So HSQLDB is the internal engine of OpenOffice db

In this article we see how can interact by java source code with OpenOffice base db using the hsqldb jdbc driver.

The Steps:

In order to write the example follow these steps

• Prepare ODB database with openoffice 
  

• Create a new office base database(i.e. mydb.odb).
  
• Create a new table(i.e. User) into base database.
  
• Make the coloumns inside the table(ID,firstname,lastname)
  
• Populate it with some rows
  

• Prepare HSQLDB extracting it from ODB
  

• Rename the mydb.odb file in mydb.zip, extract "database" directory from it, so you can find these files:
  
• backup
  
• data
  
• properities
  
• script
  
• Copy the files into c:\mydbdir\ location
  
• rename all the files by putting the same prefix before the file name, example:
  
• mydb.backup
  
• mydb.data
  
• mydb.properities
  
• mydb.script
  

            The prefix and filename are separated by dot. These files is the HSQLDB.

• Prepare HSQLDB JDBC API

• Download "hsqldb_1_8_0_10.zip" (HSQLD DB ENGINE) from http://sourceforge.net/project/downloading.php?group_id=23316&use_mirror=ovh&filename=hsqldb_1_8_0_10.zip&84223716
• Extract the zip file, search and find jdbc driver files inside lib directory: hsqldb.jar
• Create a new Java Project
• Put hsqldb.jar and into classpath project.
  

We are ready for source code!

The Source Code

import java.text.ParseException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.logging.Level;

import java.util.logging.Logger;

public class Main

{

    /**

     * @param args the command line arguments

     */

    public static void main(String[] args) throws ParseException

    {

        try

        {            

            String db_file_name_prefix = "c:\\mydbdir\\mydb";

            

            Connection con = null;

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

            con = DriverManager.getConnection("jdbc:hsqldb:file:" + db_file_name_prefix, // filenames

                    "sa", // username

                    "");  // password

            Statement statement = con.createStatement();

            //look at " for table name

            ResultSet rs = statement.executeQuery("SELECT * FROM \"User\"");

            //print the result set

            while (rs.next())

            {

                System.out.print("ID: " + rs.getString("ID"));

                System.out.print(" first name: " + rs.getString("firstname"));

                System.out.println(" last name: " + rs.getString("lastname"));

            }

            statement.close();

            con.close();

        } catch (SQLException ex)

        {

            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);

            ex.printStackTrace();

        } catch (ClassNotFoundException ex)

        {

            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);

        }

    }

}

Conclusion 

The odb is a zip file, so the beginning steps is uncomfortable because you must rename the odb in zip, rename, work on it and then recompress all in order to edit the odb again with openoffice.

In order to avoid this incovenient way it is possible to make the beginning steps automatically by using java.util.zip package:

You can find the example at http://digiassn.blogspot.com/2006/07/java-creating-jdbc-connection-to.html

Please leave a feedback in the comment to this post.