Monday, July 24, 2006

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

20 comments:

Anonymous said...

Hi, thanks for the sample code. I updated the code to compile under Java 6 without warning and used the Northwind database from Microsoft Access. Attached is my code.

Regards,

James Loh
================
http://dore.mi.com.my
"IT's as fundamental as do-re-dot-mi"

//C:>set CLASSPATH=.;C:\hsqldb\lib\hsqldb.jar
//C:>javac odb_read.java

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

public class odb_read
{
public static void main(String[] args) {

try {
Class.forName("org.hsqldb.jdbcDriver");
}
catch(Exception x) {
System.out.println("Unable to load the driver class!");
}


Connection con = null; //Database objects
Statement com = null;
ResultSet rec = null;
ZipFile file = null; //For handling 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<String> v = new ArrayList<String>(); //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("/Northwind.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

try {
con = DriverManager.getConnection("jdbc:hsqldb:file:/tmp/" + f.getName(), "SA", "");
}
catch (SQLException x) {
System.out.println("Couldn't get connection!");
}


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

//GO through the resultset, and output the results
while (rec.next())
System.out.println("Company Name: " + rec.getString("CompanyName") + " Contact Name: " + rec.getString("ContactName"));

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

Anonymous said...

thanks for this code. it was exactly wat i needed.
only one small advice put your file locatios on top for easy rejuse of the code.

Lord Nobady

Anonymous said...

Thanks for the useful code.

www.ipmango.com

Frans said...

Hi Guys and Girls,

Very nice! About to try this code, does it work with OO 2.4? I'd like to read *.xls files and export them as jdbc connections, just as ms excel odbc driver. Any clue how i can read a file with OO-calc and 'give it' to OO-base? Then I could use the code above.

Thanks Frans

John Ward said...

Not sure. I am not sure if OO2.4 uses the same format for Base files. I think it does. In which case, you can use Calc just fine, take the data, and pump it into Base using the JDBC connection.

edwardsongy said...

request help in 'building' a link or bridge between java & OOo. your sample code fails to find the reference to 'import org.hsqldb.jbdcDriver;' note that java works w/ MS Access, & that the OOo Base lets me create,view,edit.....database & tables. Thanks and regards. Ed Songy

John Ward said...

Ed,

If your not finding the HSQLDB driver, its because the hsqldb.jar file is not in your classpath. Make sure you include the jar files in OO to keep compatible versions, that should solve the problem.

John

Anonymous said...

I am getting the below error .. Please help out.... I am doing it in WinXp.

C:\Program Files\Java\jdk1.6.0_07\bin>echo %classpath%
.;C:\Program Files\Java\jdk1.6.0_07\lib;C:\hsqldb\lib;C:\Program Files\QuickTime
\QTSystem\QTJava.zip

C:\Program Files\Java\jdk1.6.0_07\bin>javac Test.java
Test.java:4: package org.hsqldb does not exist
import org.hsqldb.jdbcDriver;
^
Test.java:9: cannot find symbol
symbol : class jdbcDriver
location: class Test
jdbcDriver j = new jdbcDriver(); //Instantiate the jdbcDriver from HSQ
L
^
Test.java:9: cannot find symbol
symbol : class jdbcDriver
location: class Test
jdbcDriver j = new jdbcDriver(); //Instantiate the jdbcDriver from HSQ
L
^
Note: Test.java uses unchecked or unsafe operations.
Note: Recompile with -Xlint:unchecked for details.
3 errors

jacks said...

Very Very Thanks Dude ......
I will try this........
Lets see dose that work .,

jacks said...

Tell me how to execute execute insert and update Queries ??
As above example reads data from a temporary file and not the actual database . after inserting/ updating database how to ensure that the original database has been changed??

Anonymous said...

I have a problem. I copy and paste your code in NetBeans IDE 6.5, and...

Illegal start of expresion ...
for (len=0; len<>v.size();len++)

Why?

tugalsan said...

what is <> mean man

John Ward said...

Looks like a typo. This was posted before I started using the code formatted for HTML. That should read:
for (len=0; len > v.size();len++).

Deo said...

hi how i insert or delete a register

oakie said...

for some reason the last of the extracted zip file entries(ooTempDatabaseXXXXtmp.data) is not being deleted by the command:-

(new File((String)v.get(len))).delete();

This method actually returns a boolean that indicates whether the file was successfully deleted. From this I have been able to deduce that the command to delete has been called but that the deleting operation has failed. Any idea what could be preventing the deletion?

Anonymous said...

Looky here, what goofy code scripters have left.......
Me thinks to try and see if it reveals any banking passwordy.

Me lucky thing... it do

Kyle said...

Hey this probably won't work if I also want to be able to add and remove data from the database will it? Because just removing something from the zip file will do nothing

Apurve said...

Hey have you tried running any other query like
SELECT \"some_col\" , \"some_other_col\" FROM \"Customers\" WHERE \"some_col\" = 'value'

???
I have been able to setup the connection thanks to your post but whenever i try to run a query like the one above it is not able to show any result always saying column not found, i have cross checked the query by running it in OpenOffice Query design it works but in my java code it dosen't.. One thing i have noticed is that if i run SELECT * FROM ____ WHERE ____ then it runs but instead of *(all) if i write some column then it dosen't.. Plz help..

Apurve said...

Oh hey sorry, it was a mistake in my logic.. Its working now, thank you very much..

yassine said...

can u help me on this i'm getting this error

Test.java:4: error: package org.hsqldb does not exist
import org.hsqldb.jdbcDriver;
^
Test.java:9: error: cannot find symbol
jdbcDriver j = new jdbcDriver(); //Instantiate the jdbcDriver from HSQL
^
symbol: class jdbcDriver
location: class Test
Test.java:9: error: cannot find symbol
jdbcDriver j = new jdbcDriver(); //Instantiate the jdbcDriver from HSQL
^
symbol: class jdbcDriver
location: class Test

Can't see why
yassine