HSQLDB - Java In-Memory Database

I've started using HSQLDB because of its in-memory database capabilities. My app reads a file, builds a database and populates it with data. It then allows me to run SQL queries against the data very easily. HSQLDB has many other useful features (like persistence) which I have not tried (nor have the need for) yet.

It is really easy to get up and running with HSQLDB.

• Download HSQLDB from here.
• Add hsqldb.jar to your classpath.
• Create a connection like this:

  Class.forName("org.hsqldb.jdbc.JDBCDriver");
  Connection conn = DriverManager.getConnection(
                       "jdbc:hsqldb:mem:mydb", "SA", "");
  

• Create a table like this:

  String bookTableSQL = "create memory table MY_TABLE ("+
  " TITLE varchar(256) not null primary key,"+
  " AUTHOR varchar(256) not null"+
  ");"
  Statement st = conn.createStatement();
  st.execute(bookTableSQL);
  

• You can then insert records into the table and query it just as you would with an ordinary database table.

HSQLDB also has Text Tables which allows you to treat CSV (or other delimited) files as SQL tables.

Freezing columns in a JTable

If you have a really large JTable, you might sometimes want to "freeze" the first column, or the first X columns, so that they do not scroll with the rest of the table. It is quite easy to do so. All you have to do is create a second JTable with the data you want to freeze and use it as the row header view on your scroll pane. I have wrapped this functionality up, in a class called FrozenTablePane to which you provide a JTable and the number of columns to freeze.

/**
 * A ScrollPane which "freezes" the specified number of
 * columns of a JTable.
 *
 * @author fahdshariff
 *
 */
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JViewport;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableModel;

public class FrozenTablePane extends JScrollPane{

  public FrozenTablePane(JTable table, int colsToFreeze){
    super(table);

    TableModel model = table.getModel();

    //create a frozen model
    TableModel frozenModel = new DefaultTableModel(
                                model.getRowCount(),
                                colsToFreeze);

    //populate the frozen model
    for (int i = 0; i < model.getRowCount(); i++) {
      for (int j = 0; j < colsToFreeze; j++) {
        String value = (String) model.getValueAt(i, j);
        frozenModel.setValueAt(value, i, j);
      }
    }

    //create frozen table
    JTable frozenTable = new JTable(frozenModel);

    //remove the frozen columns from the original table
    for (int j = 0; j < colsToFreeze; j++) {
      table.removeColumn(table.getColumnModel().getColumn(0));
    }
    table.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);

    //format the frozen table
    JTableHeader header = table.getTableHeader();
    frozenTable.setBackground(header.getBackground());
    frozenTable.setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
    frozenTable.setEnabled(false);

    //set frozen table as row header view
    JViewport viewport = new JViewport();
    viewport.setView(frozenTable);
    viewport.setPreferredSize(frozenTable.getPreferredSize());
    setRowHeaderView(viewport);
    setCorner(JScrollPane.UPPER_LEFT_CORNER, frozenTable.getTableHeader());
  }
}

Display any ResultSet in a JTable

I have created an extension of JTable, called ResultSetTable, to display any SQL ResultSet. It first gets the column names by looking at the meta data of the result set and then builds the table.

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

/**
 * A JTable used to display a SQL ResultSet.
 * @author fahdshariff
 *
 */
public class ResultSetTable extends JTable{

  private final DefaultTableModel dataModel;

  public ResultSetTable(ResultSet rs)
                       throws SQLException{

    super();
    dataModel = new DefaultTableModel();
    setModel(dataModel);

    try {
      //create an array of column names
      ResultSetMetaData mdata = rs.getMetaData();
      int colCount = mdata.getColumnCount();
      String[] colNames = new String[colCount];
      for (int i = 1; i <= colCount; i++) {
        colNames[i - 1] = mdata.getColumnName(i);
      }
      dataModel.setColumnIdentifiers(colNames);

      //now populate the data
      while (rs.next()) {
        String[] rowData = new String[colCount];
        for (int i = 1; i <= colCount; i++) {
          rowData[i - 1] = rs.getString(i);
        }
        dataModel.addRow(rowData);
      }
    }
    finally{
      try {
        rs.close();
      }
      catch (SQLException ignore) {
      }
    }
  }
}

Eclipse Memory Utilisation

On my PC, Eclipse is currently hogging 518 MB of RAM! Want to find out why Eclipse is using up so much memory? This is how:

• Find the process id of Eclipse. You can use the jps command to help.
• Now dump out the java object heap using the jmap command. For example: jmap -dump:format=b,file=C:\temp\eclipse_heap.bin 3080
• Open the heap file (C:\temp\eclipse_heap.bin) in Eclipse.

Eclipse will take a couple of minutes to load the object heap into its Memory Analyser. It will then provide lots of useful information such as:

• A Leak Suspects Report: My main suspect was org.maven.ide.components.nexus_indexer occupying 24,201,456 (24.29%) bytes.
• A Histogram which lists the number of instances per class. There were 583,760 char[] objects and 439,572 String objects on my heap.
• Dominator Tree which lists the biggest objects and what they keep alive. Mine were:
  • org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader @ 0x3f46368 org.maven.ide.components.nexus_indexer (23.1 MB)
  • org.eclipse.jdt.internal.core.JavaModelManager @ 0x4710728 (15.2 MB)
  • org.eclipse.jdt.internal.core.search.indexing.IndexManager @ 0x475c3e8 (9.7 MB)
  • org.eclipse.jdt.internal.ui.text.spelling.SpellCheckEngine @ 0x3d617a8 (6.5 MB)
• Top Consumers which lists the most expensive objects grouped by class and package.

As a result of my findings, I have now removed the Maven Plugin and have also disabled the Spell Checker. Memory is down to 356 MB and the biggest object is org.eclipse.equinox.internal.p2.metadata.repository.MetadataRepositoryManager (26.1 MB).

Further Reading:
Eclipse Memory Analyzer, 10 useful tips/articles

Swapping Variables

Here is a neat way you can swap two integer variables, x and y, without using an intermediary temporary variable:

If, x = 5 and y = 7, then:

x = x + y = 12
y = x - y = 5
x = x - y = 7

So, x = 7 and y = 5.

However, I would recommend that you keep your code simple and understandable, by using a temporary variable, as follows:

If, x = 5 and y = 7, then:

temp = x = 5
x = y = 7
y = temp = 5

So, x = 7 and y = 5.

Pidgin SIPE Plugin for OCS

We recently switched from Reuters Messaging to Microsoft Office Communicator 2007 (OCS) at work and I was disappointed to find that OCS:

• does not have tabbed conversation windows, which leads to a cluttered desktop and cramped taskbar.
• does not keep conversation history logs. I believe this feature only works if you use Outlook and we use Lotus Notes.

In order to get around these issues, I am now using Pidgin with a plugin to connect to OCS and don't need the OCS client anymore!

Install SIPE Plugin for Pidgin:
This is how you can install the plugin to use Pidgin for OCS:

• Download and install Pidgin from here.
• Download SIPE from here.
• Unzip pidgin-sipe-1.8.0-msi.zip and run pidgin-sipe-1.8.0.msi to install it.
• Start the Pidgin client.
• Add a new Account with the following details:
• Protocol: Office Communicator
• Username: firstname.lastname@company.com
• Login user: firstname.lastname@company.com
• Password: xpPassword
• On the Advanced tab, tick "Use Kerberos" and untick "Use Single Sign-On".

That's all you need to do to connect to OCS from Pidgin. You can now enjoyed tabbed conversations and view logs stored in %APPDATA%\.purple\logs\sipe.

You can also add the following useful plugins to Pidgin:

• Text replacement
• Guifications
• Pidgin GTK+ Theme Control - you can download themes and install using Theme Selector (which should be present in your Start Menu).

XMLBeans for Handling XML in Java

XMLBeans is a useful library which allows you to convert XML files into Java objects, call methods on the objects and then write the objects back to XML. This post shows you how you can use XMLBeans to:

• Generate an XSD schema from an XML file
• Compile the schema into Java classes
• Use the classes to create an XML structure
• Use the classes to read an XML file

Generating a Schema from an XML File
If you have an XML file, you can generate a schema using the inst2xsd command. For example, say you have the following xml:

<books>
    <book id="1">
        <title>Snow Crash</title>
        <author>Neal Stephenson</author>
    </book>
    <book id="2">
        <title>Neuromancer</title>
        <author>William Gibson</author>
    </book>
</books>

You can generate an XSD, using the following different design types: Russian Doll, Salami Slice or Venetian Blind. This is what the output looks like:

Russian Doll Design:

inst2xsd -design rd -enumerations never C:\temp\books.xml

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="books">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="book" maxOccurs="unbounded" minOccurs="0">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="title"/>
              <xs:element type="xs:string" name="author"/>
            </xs:sequence>
            <xs:attribute type="xs:byte" name="id" use="optional"/>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Salami Slice Design:

inst2xsd -design ss -enumerations never C:\temp\books.xml

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="books">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="book" maxOccurs="unbounded" minOccurs="0"/>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
  <xs:element name="author" type="xs:string"/>
  <xs:element name="title" type="xs:string"/>
  <xs:element name="book">
    <xs:complexType>
      <xs:sequence>
        <xs:element ref="title"/>
        <xs:element ref="author"/>
      </xs:sequence>
      <xs:attribute type="xs:byte" name="id" use="optional"/>
    </xs:complexType>
  </xs:element>
</xs:schema>

Venetian Blind Design:

inst2xsd -design vb -enumerations never C:\temp\books.xml

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="books" type="booksType"/>
  <xs:complexType name="booksType">
    <xs:sequence>
      <xs:element type="bookType" name="book" maxOccurs="unbounded" minOccurs="0"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="bookType">
    <xs:sequence>
      <xs:element type="xs:string" name="title"/>
      <xs:element type="xs:string" name="author"/>
    </xs:sequence>
    <xs:attribute type="xs:byte" name="id" use="optional"/>
  </xs:complexType>
</xs:schema>

Compiling a Schema into Java Classes
Once you have generated a schema, you can use the Maven xmlbeans plugin to compile it into Java classes:

<plugin>
  <groupId>org.codehaus.mojo</groupId>
  <artifactId>xmlbeans-maven-plugin</artifactId>
  <version>2.3.2</version>
  <executions>
    <execution>
      <goals>
        <goal>xmlbeans</goal>
      </goals>
    </execution>
  </executions>
  <inherited>true</inherited>
  <configuration>
    <schemaDirectory>src/main/xsd</schemaDirectory>
    <sourceSchemas>
      <sourceSchema>books.xsd</sourceSchema>
    </sourceSchemas>
    <sourceGenerationDirectory>target/generated-sources/xmlbeans</sourceGenerationDirectory>
  </configuration>
</plugin>

You can also use the scomp command which will create a jar file containing the compiled classes.

C:\xmlbeans-2.4.0\bin> scomp -compiler %JAVA_HOME%\bin\javac books.xsd

Creating an XML Document Using the Compiled Classes
Add the jars to your classpath. Build an XML document as shown below (note that I am using the schema with the Russian Doll design. Different design types will produce different classes.):

BooksDocument doc = BooksDocument.Factory.newInstance();
Books books = doc.addNewBooks();
Book book = books.addNewBook();
book.setId((byte) 1);
book.setAuthor("Isaac Asimov");
book.setTitle("I, Robot");
System.out.println(doc.toString());

Produces:

<books>
  <book id="1">
    <title>I, Robot</title>
    <author>Isaac Asimov</author>
  </book>
</books>

Reading an XML Document Using the Compiled Classes
You can read an xml document and parse it into objects in the following way:

BooksDocument doc = BooksDocument.Factory.parse(
                    new File("C:\\temp\\books.xml"));
Books books = doc.getBooks();
Book[] bookArr = books.getBookArray();
for (Book book : bookArr) {
 String author = book.getAuthor();
 String title = book.getTitle();
 byte id = book.getId();
 System.out.println(author + '\t' + title);
}