Ok so you have played around with Java for awhile, written some functions, created a class or two and now you want to get into connecting Java to an example database. How do you start? Well most enterprise applications typically use Java with a more robust and powerful relational database than MS Access. But if you are just learning, and want something simple to play with, MS Access can be just the ticket. Sometimes Access is easier to get access to, can be installed during MS Office installation and is easier to create/manipulate databases from a home PC. We show a basic program template for connecting to an example Access database. We will print out some data from a table called “names” right here on the Programming Underground!
Setting up for our example:
You have MS office installed on your PC and along with that you chose to install MS Access. You are a master of your own domain! Perhaps you even have a sample database you have been working with as you are learning SQL. Because you are just that cool and “leet”. That is fantastic. If not, be sure to install MS Access before you work with our example. You can start by creating a sample database called “example.mdb”. To make things easier, be sure to save the database in the c:\ folder so we have a nice short path to work with.
After you have created the database, you need to create a table called “names” that will have two fields. One will be an id field which is an auto-increment primary key field called “id” (clever name huh?) and another field called “name” which will be the name of people. Be sure to save it and insert some rows into the table through Access. Now you should be all set.
Now the Java:
Java, like other programming languages, can connect to databases through two main ways. The first way is through a file called a driver. Drivers tell Java how to interact with, and what operations are available to, a particular database. Most of the time when you install database software the software will also install the drivers on the system for you. If not, you can find vendor drivers typically on their website or a 3rd party software vendor will provide some. So keep in mind that any time a database is having trouble with drivers, know that you can usually find it on the web through a general search.
The second way Java can connect to a database is through the use of a Database Source Name (DSN). This is a particular setup that tells an application, that attempts to connect to this “name”, it should use a particular driver. So in both cases you are still going to need the driver, one just makes the connection directly and the other uses a generic name that languages/applications can use to connect to the driver on its behalf.
Our example is going to use the first method. We are going to create a standard Java Database Connectivity (JDBC) connection directly to the MS Access driver which should now be installed on your system. If you wish to use this database with other applications, setting up a DSN may be the way to go. At least that way all applications can use the same name. Just keep in mind that if you go that route your connection string (the string of data that tells Java how to connect) will specify the DSN instead of the driver like in our example.
Below are two graphics showing you the difference. Keep in mind that Java, .NET or applications can all connect directly to the driver or go through this intermediary DSN. DSNs typically simplify connection strings as well as impose restrictions on how the driver can be used.
Our example below is going to use this process for simplicity as well as being explicit….
As you can see from the graphic above, Java uses its JDBC API to connect directly to the MS Access driver file (using our connection string as you will see in a minute) and talks to MS Access through it. Because of this scheme, if you ever see something odd about how data is flowing back and forth between your app and Access… it might be a driver limitation. Which is why there are some 3rd party vendors out there that offer their “enhanced” versions of database driver files.
The below example is how the DSN works and is not going to be covered in this introduction to Java and MS Access…
In the graphic above you see that this DSN is going to be a “middle man” to the driver. Setting up the DSN to begin with is going to require you specifying which driver you want for the DSN. Once it is setup, multiple different languages, frameworks, or independent applications can use it to broker communication to the driver. The advantage here is that you can setup multiple DSNs for the same driver and each can have special attributes for it.
Since we have that out of the way, we are ready to get onto a basic example of how we would setup Java to connect to our access file c:\example.mdb.
// Import the Java SQL functionality
import java.sql.*;
public class DatabaseTest
{
public static void main(String[] args)
{
try {
// Tell Java's JDBC to use the ODBC Driver connectivity model
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// Now tell it that you are connecting directly to the Access Driver and
// which database we want to access. This is the "connection string" and
// can be more complex with passwords and usernames as well as security settings.
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=c:/example.mdb;}";
// now we can get the connection from the DriverManager
Connection con = DriverManager.getConnection(database ,"","");
// We are connected, so lets create a statement to ask for names from our table "names"
Statement s = con.createStatement();
s.execute("select name from names");
// Execute and get the results
ResultSet rs = s.getResultSet();
if (rs != null) {
// If we had results, loop through them...
while ( rs.next() )
{
// Just print out the values
System.out.println("Data: " + rs.getString(1) );
}
}
// Cleanup our statement and connection by closing them.
s.close();
con.close();
}
// If there was an error connection or querying, show the error.
catch (Exception e) {
System.out.println("Error: " + e);
}
}
}
First of all we start by importing the functions and objects we need from Java to make database connections possible. We do this by importing java.sql.*;. We start our class, as normal, and within a try catch block (in case we have troubles connecting to the database or reading it we want it to catch the error) we bring in the JDBC API by pulling in the sun.jdbc.odbc.JdbcOdbcDriver class. This class should be part of the JDK that was installed on your system. This part is known as dynamic loading of a class at runtime and we are asking the JDBC to use the ODBC (open database connectivity) class to handle communication with the MS Access Driver.
Next up is creating our connection string. This step is important because it will tell JDBC how to connect to the driver and its underlying database. If your database has a username and password on it, this is where you would specify it. If the database needs special permissions, this is where you would provide credentials to access it. This also specifies the path to the file, of if this was via DSN, the DSN name instead of the driver name.
Now that we have specified the details, we ask the DriverManager object to please go out and get us a connection to this database from the MS Access driver. We do this using the getConnection() method. I don’t think it uses the “please” part through. I will have to check on how polite the DriverManager really is. 😉
Once we have the connection, assuming it was successful, we create a statement object. Here a statement object is essentially an object which wraps up an SQL statement and tells Java how to go about executing it. We can execute a statement in various ways using this object and it can provide information on how results are returned (such as fetch size, max rows, getting access to the result set so that we can iterate through the results etc).
We ask the statement for a ResultSet object which will be the records returned from the database. Using this object we can simply loop through the results and print out their values in a nice simple while loop. We use the next() method to go to the next record until no more records can be retrieved.
Next we clean up the statement object and the connection object by closing them. Forgetting to leave any recordset or connection open is a major no no in just about any language. If you don’t close them, they may not be returned to the connection pool for other applications to use. In addition, this will result in connections being eaten up and not released until eventually the system has no more connections it can give out and starts producing errors. If you open something, look to close it and you will be alright.
Lastly, we have the catch block of our try..catch which is there to catch any errors that might result from the connecting mechanism failing or executing a bad SQL statement.
So that is all there is to it. Please feel free to use this code as a general skeleton for connecting to your access databases and trying out your own queries. Just be sure to change the connection string to reflect where your database is located as well as how it will be connected to.
You can find more information on connection strings at http://www.connectionstrings.com. There you can get connection strings for various databases. One last tip to keep in mind is that if you are going to connect to another type of database be sure you get the proper classes imported and specify the proper permissions.
Now with this code you can practice your Java with databases locally on your computer without having to go out through the Internet. Enjoy the code and thanks for reading! 🙂
