How to connect from Java to OpenOffice database

| July 4, 2011 | 1 Comment

We have a database created from OpenOffice, using Base that is a application to create simple databases like Microsoft Access. The question we will try to answer in this post is:

How do you connect from a Java application to this kind of databases?

To begin, we must to clarify three important things about OO (OpenOffice) Base:

  • HSQLDB is the internal engine of OpenOffice db.
  • The files extensions generates by  Base are .odb
  • This files are in fact zipped files.

Then, we build a test case.

Steps to follow

1. Prepare ODB database with OpenOffice.

  • Create a new database (i.e mydb.odb).
  • Create a table inside (i.e User).
  • Create three columns inside the above table (i.e ID, firtsname, lastname).
  • Populate the table with some rows.

2. 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, properties, script.
  • Copy the files into a separate location, i.e C:\mydb
  • Rename all the files by putting the same prefix before the file name, thus leaving: mydb.backup, mydb.data, mydb.properties, mydb.script.
  • These files is the HSQLDB.

3. Using API JDBC to access database.

Source code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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 prefix
    "sa", // user
    "");  // pass
    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"));
    }
    //cerramos la conexiones
    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);
   }
  }
}

Conclusión

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. To avoid this we can do the same steps from your own Java code, for that we can use the Java API for dealing with compressed files. We will see in a later post, leave time to build the example.

 

Tags: ,

Category: Databases, Development, Java

Comments (1)

Trackback URL | Comments RSS Feed

  1. Claris says:

    Really liked what you had to say in your post, How to connect from Java to OpenOffice database – MergeTag : MergeTag, thanks for the good read!
    — Claris

    http://www.terrazoa.com

Leave a Reply