020Serializing Objects into Database

Author: Dr. Heinz M. KabutzDate: 2001-05-24Java Version: 1.3Category: Language

Abstract: In this newsletter, we serialize Java objects into a byte[] and then write that into a PreparedStatement with setBytes().

Welcome to the 20th issue of The Java(tm) Specialists' Newsletter, where I look at how we can serialize objects into a database using JDBC. Thank you for your continued support, it makes the hours of research and writing worthwhile :-)

This week's newsletter has been the most frustrating one to write; databases usually have that effect on me. Forgive me if this newsletter seems too elementary, I've actually in the past had to dig into the C code of the JDBC-ODBC bridge to figure out why it wasn't working, i.e. it's more complicated than it seems. Java truly is a "write-once-debug-everywhere" language, especially when it comes to JDBC drivers.

If you're crazy enough to use MSSQL as your database, which isn't THAT crazy considering the beautiful administrative tools available and the ease with which it can be set up, you might consider using the JDBC-ODBC bridge to access the database. It's a dog, full of bugs, but it's free (if you don't count the developer's time in writing workarounds as significant). Also, "better the devil you know", which according to "The Wordsworth Dictionary of Cliche" means "Trust the person or thing you are familiar with rather than risking the unknown." Other JDBC drivers are bound to also contain bugs, and at least we know the bugs in the bridge!

javaspecialists.teachable.com: Please visit our new self-study course catalog to see how you can upskill your Java knowledge.

Serializing Objects into Database

A problem I faced a few months ago, before JDK 1.3 was released with an improved JDBC-ODBC bridge, was how to serialize objects into a relational database using JDBC. At the time, it was quite tricky to do because of bugs in the ODBC bridge. As soon as the object became bigger than 2000 bytes, the method shown below ceased to work. I don't need to mention that I tested it with less than 2000 bytes the first time I wrote the code?

Before we look at how we can write binary objects into a database table, what type of data type should we use? Should we use VARBINARY or IMAGE? The answer, I suppose, depends on how big the object is that you want to write to the database, and which database you're actually talking to (hence my comments regarding write-once-debug-everywhere).

We also have to consider the underlying database when we get a SQLException, so that we can determine what actually went wrong. If the connection goes down temporarily due to a flaky network, it is no good for our application server to crash permanently. In a future newsletter, I will demonstrate how you can find out what went wrong by looking at the SQLException.

Back to the problem of writing serialized Java objects into the database. The most intuitive way of doing it, which as I mentioned before only works in the ODBC bridge of JDK 1.3, is to take the Object, stream it to a ByteArrayOutputStream via an ObjectOutputStream, convert the ByteArrayOutputStream to a byte array and then call the setBytes method on the prepared statement. Note that you have to use PreparedStatement, rather than the normal Statement class, if you want to serialize objects into the database.

To convert it back to a Java object, we simply do the reverse process of reading the byte array, putting it into a ByteArrayInputStream and passing that to an ObjectInputStream. We then read the object, and voila, we have read the object back again.

//: DatabaseTest.java
import java.sql.*;
import java.io.*;
import java.util.Vector;
public class DatabaseTest {
  public static void write(
      Object obj, PreparedStatement ps, int parameterIndex)
      throws SQLException, IOException {
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    ObjectOutputStream oout = new ObjectOutputStream(baos);
    oout.writeObject(obj);
    oout.close();
    // This will NOT work in JDBC-ODBC bridge under JDK 1.2.2
    // as soon as the size of the byte array is bigger than 2000
    ps.setBytes(parameterIndex, baos.toByteArray());
  }
  public static Object read(ResultSet rs, String column)
      throws SQLException, IOException, ClassNotFoundException {
    // This will NOT work in JDBC-ODBC bridge under JDK 1.2.2
    // as a SQL NULL data value is not handled correctly.
    byte[] buf = rs.getBytes(column);
    if (buf != null) {
      ObjectInputStream objectIn = new ObjectInputStream(
        new ByteArrayInputStream(buf));
      return objectIn.readObject();
    }
    return null;
  }
  public static void main(String[] args) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection(
      "jdbc:odbc:MailingList", "sa", "");

    Statement st = con.createStatement();
    st.executeUpdate("INSERT BlobTable (Data) VALUES (NULL)");
    st.close();

    PreparedStatement ps = con.prepareStatement(
      "INSERT INTO BlobTable (Data) VALUES (?)");
    write(new Vector(2) {{ add("Hello"); add("World");}}, ps, 1);
    ps.execute();
    Vector veryBig = new Vector(10);
    for (int i=0; i<10; i++) veryBig.add(new byte[10000]);
    write(veryBig, ps, 1);
    ps.execute();
    write("What Gives?", ps, 1);
    ps.execute();
    write(null, ps, 1);
    ps.execute();
    ps.close();

    st = con.createStatement();
    ResultSet rs = st.executeQuery("SELECT Data FROM BlobTable");
    while(rs.next()) {
      System.out.println(read(rs, "Data"));
    }
    rs.close();
    st.close();
  }
}

To test this code, you'll have to set up a DSN to point to your MSSQL database (or a real database), and set up a table in your database called BlobTable with an IMAGE type for the column called "Data". In my example, I am writing a SQL NULL value into the table, then a small Vector (using dynamic aggregate initialization described in Newsletter 002) and after that a very big Vector, followed by a String and a Java null object. Lastly, I close the PreparedStatement, and read all the objects back and print them to the screen.

The output on my system is:

null
[Hello, World]
[[B@2f0db, [B@12d342, [B@6b97fd, [B@478e57, [B@5224ee, [B@76a746, 
[B@5ff48b, [B@2ffc70, [B@663e3d, [B@4901] What Gives?
null

All very simple, except that in a real environment we don't really want to use IMAGE types in our database, unless absolutely necessary. We would prefer using VARBINARY as they take less space and form part of the block of data in the row. How do we know the size of a Java object in memory? I've worked out some formulae for "guessing" the size of a Java object by looking at its data members, which is a topic for another newsletter. In the meantime, the easiest is to take the biggest set of data that must be supported by your object and simply try out how much space it will take. There is no "sizeof()" method in Java to do that for you, unfortunately.

Some other issues related to the above problem is that a PreparedStatement is bound to a Connection, which means that we cannot rebind it to another Connection. The result is that we cannot use Connection pooling very well for this, as we then have to construct a new PreparedStatement each time we want to insert a Java object into the database, which defeats the reason for having a PreparedStatement. In the JDBC 3.0 specification, there is support for rebinding PreparedStatements, but we'll have to wait a bit before we'll have JDBC 3.0 compliant drivers.

How do you do this with JDK 1.2.x? You have to use the PreparedStatement.setBinaryStream(...) method to write the binary stream directly into the database. The problem is that when you read the binary stream again, and the database contained a SQL NULL, you get some nasty exceptions that are hard to figure out. Obviously, the JDK 1.3.x ODBC bridge throws different exceptions, so I don't advocate using the JDK 1.2.x bridge for writing objects into databases.

---
Warning Advanced:
A memory leak, discovered by the best and most ruthless Java bug finder I know, occurs when you call the ResultSet.getTimestamp() method. Rather call ResultSet.getString() and convert the resultant String to a Date object. He's recently been granted permanent residence in our beautiful country of South Africa, which is quite a feat, considering the tough immigration laws. Congratulations!
---

Please send me your comments / experiences on this. I always appreciate any feedback, both positive and negative, and please remember to send this newsletter to others who might be interested in Java.

Heinz

Comments

We are always happy to receive comments from our readers. Feel free to send me a comment via email or discuss the newsletter in our JavaSpecialists Slack Channel (Get an invite here)

Related Articles

Browse the Newsletter Archive