Connecting to MS Access Files via JDBC in 64-bit Java

In 32-bit Java, the technique for connecting to a Microsoft Access file via JDBC connection is well-known and available as part of the Sun JDK. There are a number of errors, though, if you attempt to use a 64-bit version of Java that are not as well documented. This article points out some of those issues and a working strategy for how to successfully connect to a Microsoft Access file via JDBC in 64-bit Java.

1. Review of the 32-bit Connection Strategy

The well-known syntax for connecting to an Microsoft Access file via JDBC is as follows:

final String fileName = "c:/myDataBase.mdb";
Connection con = null;
try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ="+fileName;
	con = DriverManager.getConnection(url,"","");
} catch (Exception e) {
	// Handle exceptions
	...
} finally {
	try { if(con!=null) {con.close();} } catch (Exception e) {}
}

If you are using the Sun JDK, then the driver will be available in the classpath automatically. Notice, I make sure to close my connection object in a finally block as all good JDBC developers know to do.

2. Errors in the 64-bit World

Attempting to run the proceeding code returns the following error when using a 64-bit JDK:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

If you would prefer to stick with the 32-bit version of the driver, there are two options available:

  • Use the 64-bit Java installation but run java.exe with the “-D32” flag.
  • Use a 32-bit Java installation

Both of these solutions limit the amount of memory a Java application can use.

3. Adapting JDBC to 64-bit Java

If you would prefer to use the 64-bit Java, there is a solution, although for some users this may require removing Microsoft Office.

Step #1: Download the Microsoft Access Database Engine 2010 Redistributable, specifically the AccessDatabaseEngine_x64.exe file.

Step #2: Install the Microsoft Access Database Engine. If you are running a 32-bit version of Microsoft Office, you will likely get the following error when you try to install it:

You cannot install the 64-bit version of Office 2010 because you have 32-bit Office products installed.

At this point, you must decide whether or not to uninstall the 32-bit version of Microsoft Office. Newer versions of Office, such as 2010, often contain both 32-bit and 64-bit versions on the installation DVD, although the 32-bit version is used by default when installed via the AutoRun process. In this case, you would first uninstall the 32-bit version of Office. Restart the computer. Then, ignore the AutoRun on the DVD and manually open the setup executable in the x64 directory. After a 64-bit Office version is installed, continue with the Microsoft Access Database Engine installation.

Note: If you are installing a recent 64-bit version of Microsoft Office, you may be able to skip installing the Microsoft Access Database Engine, as it is often included in the Office installation.

If a 64-bit version of Office is not available, then you will unable to run the 32-bit version of Microsoft Office alongside the 64-bit Microsoft Access Database Engine, and must decide which is more important.

Step #3 Update the JDBC code to use the following revised connection string as follows:

final String fileName = "c:/myDataBase.mdb";
Connection con = null;
try {
	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ="+fileName;
	con = DriverManager.getConnection(url,"","");
} catch (Exception e) {
	// Handle exceptions
	...
} finally {
	try { if(con!=null) {con.close();} } catch (Exception e) {}
}

After making these changes, you should be able to connect to your Access database in 64-bit Java.

Java Enums can implement Interfaces

Something which came as a surprise to me but ended up quite useful, was that enums in Java can also implement interfaces.

A typical enumeration in Java, introduced in Java 5, looks like a String value, but also introduces other features which make them a particularly useful language feature. Besides looking like an typical enumeration with a finite ordered list of values, it is also possible to attach additional information to each enumeration value.

A basic enumeration, which defines the four suits in a typical set of cards looks like this:

public enum Suit 
{ CLUBS, DIAMONDS, HEARTS, SPADES }

To attach additional information to the enumerated type, add a private constructor, add a private field, and get each enumeration value to call the new constructor.

public enum Numbers {
	One(1), Two(2), Three(3);

	private Numbers(int value)
	{
		this.value = value;
	}
	
	public int getValue()
	{
		return this.value;
	}
	
	private int value;
}

In the example above, we also introduced the getValue() method. This introduces the concept of behaviour to our enumeration; not something we would normally consider. It also lends itself to the idea of common behaviour between enumerated types.

To show how this works, consider an interface which defines the mapping between a class and a table.

public interface DatabaseMapping {
	public String getType();
	public boolean isKey();
	public boolean isNullable();
	//public String name();
}

The method name() is commented for now, we’ll get back to that in a minute.

To use this, we can now create a new Java class and then define the mapping of that class to a database.

public class Person {
	public String getFirstname() {
		return firstname;
	}
	public void setFirstname(String firstname) {
		this.firstname = firstname;
	}
	public String getLastname() {
		return lastname;
	}
	public void setLastname(String lastname) {
		this.lastname = lastname;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	private String firstname;
	private String lastname;
	private int age;
}

(I realise there are better ways to map a class to a database table, this example is contrived) When we define the mapping from our class to our table, we can define a separate mapping class. We create this class, we will make it an enumeration and allow each class/database field to be a member in an enumeration.

public enum PersonMapping {
	personId, firstname, lastname, age;
}

Using the trick where we are able to attach additional information to enumerated types, we define some of the database mapping details:

public enum PersonMapping {
	personId("INTEGER", true, false), 
	firstname("TEXT", false, false), 
	lastname("TEXT", false, false),
	age("INTEGER", false, false);
	private PersonMapping(String type, boolean key, boolean nullable )
	{
		this.type = type;
		this.key = key;
		this.nullable = nullable;
	}
	public String getType() {
		return type;
	}
	public boolean isKey() {
		return key;
	}
	public boolean isNullable() {
		return nullable;
	}
	private final String type;
	private final boolean key;
	private final boolean nullable;
}

So now we are able to use the enumeration to iterate the fields and do some database stuff. For the moment we’ll hold off including the interface.

public String dbTableCreate(String tableName) {
	StringBuilder builder = new StringBuilder("Create table ");
	builder.append(tableName);
	builder.append("(");
	for (PersonMapping column : PersonMapping.values()) {
		builder.append(column.name());
		builder.append(" ");
		builder.append(column.getType());
		builder.append(column.isKey() ? " primary key" : "");
		builder.append(", ");
	}
	builder = new StringBuilder(builder.substring(0, builder.length() - 2));
	builder.append(");");
	return builder.toString();
}

Which looks fine for our PersonMapping enumerated type, but doesn’t allow us to plug in different class-to-database mappings. Time to introduce the interface. Looking closely at the code above, we need to use two of the additional methods available to all enumerations, name() and values(). The first returns the text name of the current enumerated value, and the second allows us to access the values that make up this enumerated type.

First add the interface to the enumeration:

public enum PersonMapping implements MappingEnum{

Uncomment the name() method so that it can be accessed from the interface.

public interface MappingEnum {
	public String getType();
	public boolean isKey();
	public boolean isNullable();
	public String name();
}

Final piece of the puzzle is to write the dbTableCreate() above to allow it to be reused with other enumerated types. The problem we need to work around is that the values() method is static, making it difficult to pass the enumerated type to the method. The simple solution is to call the method explicitly and pass the values:

dbTableCreate("tableName", PersonMapping.values()));

… and realise that we can now receive the values using the interface …

public String dbTableCreate(String tableName, MappingEnum[] values) {
	StringBuilder builder = new StringBuilder("Create table ");
	builder.append(tableName);
	builder.append("(");
	for (MappingEnum column : values) {
		builder.append(column.name());
		builder.append(" ");
		builder.append(column.getType());
		builder.append(column.isKey() ? " primary key" : "");
		builder.append(", ");
	}
	builder = new StringBuilder(builder.substring(0, builder.length() - 2));
	builder.append(");");
	return builder.toString();
}

Conclusion

* Enumerations can store additional data and have additional methods,
* Enumerations can use interfaces to define behaviour,
* Enumerated item behaviour can be accessed via an interface, the same as regular Java classes,
* The values() method from enumerations can be used to return an array of a interface.

Combined, it allows us to treat enumerated types as both regular Java classes and enumerated types and interact using common behaviour rather than being tied to a single implementation.

GWT: Bounded TextField in GXT

This article provides a solution for creating a TextField with bounded user input in Sencha GXT (aka Ext GWT), for use in GWT applications.

The Problem

Recently, I was looking for a way to limit the amount of text a user could enter on an HTML form within a GXT application – for example, limiting a zip code input field to five characters. I wanted something that functions identically to the maxlength attribute found in HTML <input type=”text”> fields. While GXT does offer a maxlength attribute within a TextField object, it does not function as one might expect. Instead of limiting the number of characters the user could enter, it interacts with the validation process and displays a validation error (such as highlighting the TextField in red) after the fact if a user entered too many characters.

The Solution

I discovered a post on the Sencha support forum that shows how to create a subclass of TextField with the expected functionality, ie, to limit the number of characters the user can type to be the maxlength attribute. Since I believe such a class is desirable throughout my application, I generalized the original anonymous inner class solution to be a regular Java class.

Simply add the following class to your application and replace all your instances of TextField with BoundedTextField and the maxlength attribute will function as a text limiter. I hope Sencha will eventually include support for this form of maxlength functionality within the API. Thanks to Sven at Sencha for providing the original solution.

package net.selikoff.gxt.ui.widget;

import com.extjs.gxt.ui.client.widget.form.TextField;
import com.google.gwt.user.client.Element;

/**
 * Class works like TextField class but Max Length specifies the maximum
 * amount user can type, instead of being used as limit for validation.
 * 
 * @author Scott Selikoff
 */
public class BoundedTextField<D> extends TextField<D> {
	@Override
	public void setMaxLength(int m) {
		super.setMaxLength(m);
		if (rendered) {
			getInputEl().setElementAttribute("maxLength", m);
		}
	}

	@Override
	protected void onRender(Element target, int index) {
		super.onRender(target, index);
		getInputEl().setElementAttribute("maxLength", getMaxLength());
	}
}