Java: Creating a JDBC Connection to OpenOffice.Org Databases

Previously I had posted that I had figured out how why I couldn't make JDBC connections to OpenOffice.org files. Now, I wrote the solution. The following example will open an OpenOffice.org Base file via the Java ZIP classes, extract the necessary files to the temporary folder and extract the necessary files, create the JDBC connection using the HSQLDB JDBC driver, query, then delete the temp files. The file in question is located at ~/OODatabase/employeeDatabase.odb. The query will get the first names and last names (nm_emp_first, nm_emp_last respectivly) from the database.

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();
          }
     }
}