Java: Creating a JDBC Connection to OpenOffice.Org Databases

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

Extracted [adapted] from: http://digiassn.blogspot.com/2006/07/java-creating-jdbc-connection-to.html

Java: Creating a JDBC Connection to OpenOffice.Org Databases

The following example will open an OpenOffice.org Base file via the Java ZIP classes, extract the necessary files to the temporary folder, create the JDBC connection using the HSQLDB JDBC driver, query, then delete the temp files.

import java.sql.*;
import java.util.zip.*;
import java.io.*;
import org.hsqldb.jdbcDriver;
import java.util.*;

public class Test {
public static void main(String[] args) {
jdbcDriver j = new jdbcDriver(); //Instantiate the jdbcDriver from HSQL
Connection con = null; //Database objects
Statement com = null;
ResultSet rec = null;
ZipFile file = null; //For handeling zip files
ZipEntry ent = null;
Enumeration en = null; //For the entries in the zip file
BufferedOutputStream out = null; //For the output from the zip class
InputStream in = null; //for reading buffers from the zip file
File f = null; //Used to get a temporary file name, not actually used for anything
int len; //General length counter for loops
List v = new ArrayList(); //Stores list of unzipped file for deletion at end of program

//Unzip zip file, via info from
//http://www.devx.com/getHelpOn/10MinuteSolution/20447

try
{
//Open the zip file that holds the OO.Org Base file
file = new ZipFile("/home/digiassn/OODatabase/employeeDatabase.odb");

//Create a generic temp file. I only need to get the filename from
//the tempfile to prefix the extracted files for OO Base
f = File.createTempFile("ooTempDatabase", "tmp");
f.deleteOnExit();

//Get file entries from the zipfile and loop through all of them
en = file.entries();
while (en.hasMoreElements())
{
//Get the current element
ent = (ZipEntry)en.nextElement();

//If the file is in the database directory, extract it to our
//temp folder using the temp filename above as a prefix
if (ent.getName().startsWith("database/"))
{
System.out.println("Extracting File: " + ent.getName());
byte[] buffer = new byte[1024];

//Create an input stream file the file entry
in = file.getInputStream(ent);

//Create a output stream to write out the entry to, using the
//temp filename created above
out = new BufferedOutputStream(new FileOutputStream("/tmp/" + f.getName() + "." + ent.getName().substring(9)));

//Add the newly created temp file to the tempfile vector for deleting
//later on
v.add("/tmp/" + f.getName() + "." + ent.getName().substring(9));

//Read the input file into the buffer, then write out to
//the output file
while((len = in.read(buffer)) >= 0)
out.write(buffer, 0, len);

//close both the input stream and the output stream
out.close();
in.close();
}
}
//Close the zip file since the temp files have been created
file.close();

//Create our JDBC connection based on the temp filename used above
con = DriverManager.getConnection("jdbc:hsqldb:file:/tmp/" + f.getName(), "SA", "");

//Create a command object and execute, storing the results in the rec object
com = con.createStatement();
rec = com.executeQuery("select * from \"employees\"");

//GO through the resultset, and output the results
while (rec.next())
System.out.println("Last Name: " + rec.getString("nm_emp_last") + " First Name: " + rec.getString("nm_emp_first"));

//Close all the database objects
rec.close();
com.close();
con.close();

//Delete the temporary files, which file names are stored in the v vector
for (len = 0; len <> v.size(); len++)
(new File((String)v.get(len))).delete();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}