CSC-341
Laboratory 7: Using JDBC to Construct Database Applications in Java
Overview
As we have discussed in class, JDBC is a very powerful collection of Java classes and interfaces that allow Java programs to communicate with different database management systems, including Access, Oracle and MySQL. In this lab activity we will explore the details of how this works.
Goals of this laboratory
To reach a solid understanding of the basic JDBC principles discussed in class. To learn the basics of the Java Developers Kit (JDK) programming environment.
To experiment with a working Java program that accesses Access, Oracle and MySQL databases with no change in code.
To see how this program can be improved to be more efficient and general.
Not to learn how to write extensive Java graphical user interfaces. Our focus will remain on getting Java programs to “talk to” databases. Any Java programming experience that you bring to the lab will be a bonus.
Preparation for Lab
1. If you haven’t already done it, create a Z:\csc341 folder for today’s lab. (If you create a working folder somewhere else, be sure you know the full pathname for it. Also, it would be best if none of the folders in the pathname had any spaces in it’s name, though we can get around it if they do).
2. Copy the lab7 folder to your Z:\csc341 folder. This material can be found in the OUT subfolder of the shared CSC-341 folder.
3. Use Windows to look at what you just copied. You should have a folder called dbpackage and files named
env.bat (a batch file to assist in configuring your environment variables)
classes12.jar (the Oracle8 JDBC package, including the Driver implementation) mm.mysql-2.0.6.jar (the MySQL JDBC package, including the Driver implementation) jdbc-example.properties (the default settings for connection properties)
access.properties (the connection properties for Access) oracle.properties (the connection properties for Oracle8), mysql.properties (the connection properties for MySQL) myFirst.java (a sample Java program)
BigHitVideo.mdb (the BigHitVideo database in Access)
In the dbpackage folder you’ll find a subfolder called jdbc, which in turn contains the following files, which contain the Java applications that we’ll experiment with today:
JDBCTest.java
PreparedStatementExample.java
For the rest of the lab activity, I will describe the steps as if all of the files described above are in a folder with the path Z:\csc341\lab7. You will need to replace this path with the actual path that you use.
Note: Many thanks to Michael Elder, who provided these files along with very helpful explanations.
Introduction to JDK 1.6.0
Many different development environments exist for Java (including Eclipse, CodeWarrior, TogetherSoft and JBuilder, to name just four), but the original, freely available Java Developers Kit is what we’ll be using for this lab. It is the bare-boned approach – no fancy graphical user interface here, only command-line commands – but when you know how to use the JDK it is easier to learn the other tools.
1. Click Start, choose the Run option, and type in cmd to get a command window. Use the cd (“change directory”) command to move into your working directory, for instance:
cd csc341\lab7
2. Your PATH variable specifies the directories that the operating system will look in to find commands that you run. It is necessary that the JDK folder is found on this path. To check, run the command
echo %PATH%
Look for C:\Program Files\Java\jdk1.6.0_25\bin; in the path. If it isn’t there, run this command right now:
set PATH= C:\Program Files\Java\jdk1.6.0_25\bin;%PATH%
3. Now you should be able to compile and run a simple Java program. Run the following command, which compiles the source file (myFirst.java).
javac myFirst.java
4. Now run the dir command to see what’s in your directory. There should be an additional file called myFirst.class. This is the compiled version of the Java source program. To run it, execute this command:
java myFirst
There you have the basics of the JDK. We’ll get more practice as we move along.
Establishing Connections to Databases with JDBC
To make a Java program talk to a database, we’ll use the JDBC tools, which are provided as part of the java.sql package. Recall from our class discussion that many of the classes and methods defined by Java as part of the java.sql package are actually interfaces, meaning that only the expected behaviors are defined. It is up to each database vendor (Oracle, Microsoft, the MySQL people, etc.) to provide an appropriate JDBC package which
implements these interfaces for their specific product. The most important class that is implemented by a JDBC
package is the Driver for a particular database.
Connecting to an Access Database
To talk to an Access database, all that we need to do is provide a JDBC package that can talk to the ODBC API already on our Windows system. This means that we’ll also have to manually register the appropriate driver with the driver manager. (This is done by defining a data source name, or DSN.)
1. Get out the printout of JCBCTest.java for reference as you go through this activity. 2. Take a look at the access.properties file by executing
notepad access.properties
in the command window. Notice the values that are defined in this file: a url, a driver and a query. The URL specifies the exact location of the database that we wish to connect to, including a login ID and password if necessary. (We’ll see those used in later examples.) The driver variable specifies the location of the implementation for the Driver class that is part of the JDBC package being used. The query is the SQL statement that we will try to run on the database.
The driver for this example shows that we’re using the JDBC-ODBC Bridge strategy for database connection. The URL includes the word “BigHit” at the end. This is the DSN that ODBC requires. It must be registered with the ODBC driver manager. We’ll do that next.
3. Run Start >Control Panel. Open the “Administrative Tools” folder, then double-click on “Data Sources (ODBC)”. Click on the “System DSN” tab and then on the Add button. Choose “Microsoft Access Driver” and click Finish. Use BigHit for the Data Source Name, then click Select to specify the actual database file. Browse to the BigHitVideo.mdb database in your working folder (though it could actually be anywhere), then close all the windows by repeatedly clicking OK. Now your Java program will know what database you are talking about when you refer to “BigHit”, and it will know what driver object to use.
4. The program JDBCTest.java is a simple application that will make the connection to your Access database, run a query on it, and display the results in a rudimentary fashion. To begin this process, first compile the program. The following instructions assume that you are in your working directory (the one with the java subfolder in it). Compile using
javac dbpackage\jdbc\JDBCTest.java 5. Run using:
java dbpackage.jdbc.JDBCTest access.properties The results of the query given in the access.properties file should be displayed.
6. Now try entering some other appropriate queries by editing the access.properties file. You will not have to recompile the program to try them. Just run it.
7. Next, take some time to read through the JDBCTest.java listing. Make a genuine effort to understand what is going on, and ask questions about it.
Connecting to an Oracle Database
This is where it gets interesting. To change our program to connect to an Oracle database, we do not have to change a single line of code. Instead, we simply need to change the driver, change the URL (which now requires a login and password), and make the JDK aware of where the Oracle JDBC package is stored.
1. We’ll start with that last thing – telling the JDK where the Oracle JDBC package is located. As it happens, it’s located in your working directory in the file classes12.jar. To ensure that the JDK can find it, you’ll need to add the path to the file to the CLASSPATH environment variable. This can be done using the simple batch file env.bat that has been provided to you. First, use
notepad env.bat
to look at the file. The lines beginning with REM are commented out. (Note that one of them runs the “set path” command, so in the future you may prefer simply to use this script for that task.) At this point you should edit all of the paths (if necessary) to reflect the correct location of your working folder. If you’re working in Z:\csc341\lab7, no changes should be required.
3. Now open the oracle.properties file using Notepad. Note that you have similar variables as in the Access file, but now you also need to enter a user ID and password. Edit the file to use yours for Oracle. 4. That’s it. You can now re-run the program. Just use the oracle.properties file instead of
access.properties as your input parameter on the command line.
5. Again, experiment with some different queries, and look over the code to make sure that you understand it – and that you understand why no changes were required.
Connecting to a MySQL Database
Re-tooling our program to connect to a MySQL database is no more complicated than changing it over to Oracle. If you used the env.bat script earlier, the location of the MySQL JDBC package (mm.mysql-2.0.6.jar) is already on the classpath. Just edit the mysql.properties file appropriately and re-run the program. Experiment with some different queries.
For additional practice, you might want to try running your Java program from your Unix account (on the departmental server). Since the Unix environment also runs the JDK, most of the details are the same.
1. Use WinSCP to connect to your account on the CS server. Create a new folder called lab7. Then transfer your entire dbpackage folder, the mysql.properties file and the
mm.mysql-2.0.6.jar file into the lab7 folder. 2. Quit WinSCP and use Putty to telnet to your server account.
3. At the command line, enter the command bash and hit enter. This changes the shell program that interprets your commands, and will simplify the next few steps.
4. Once again, we need to set the CLASSPATH so the JDK (already installed on the server) will know where the MySQL JDBC package is. Here is the command to enter:
export CLASSPATH=/home/ktreu/lab7:/home/ktreu/lab7/mm.mysql-2.0.6.jar
Obviously, you should replace my ID with your own, and this assumes that you are working in a folder called “lab7” as directed. If you called it something else, make the appropriate change.
5. We should be good to go. Again, note that no change has been made to the JDBCTest program at all. Run the following commands to compile and execute:
cd lab7 (This puts you in your lab7 directory, if you’re not in it already.) javac dbpackage/jdbc/JDBCTest.java
java dbpackage.jdbc.JDBCTest mysql.properties
6. Again, modify the mysql.properties file to try some different queries. Do you have any questions about the code?
Improving the Application
Next we’ll spend a bit of time improving on JDBCTest. But the main JDBC stuff is over. Now is the time to ask questions about it if you have any.
ConnectionFactory.java
One thing to note about the JDBCTest program is that it is not very general. It is a class, but not one that any other program can use. The ConnectionFactory.java example generalizes the program so that it can be used easily and effectively by other programs to create connections and run queries. In addition, it can also be used as a standalone application.
You might also find it more convenient to be able to specify your SQL query as an argument in the command line, rather than in the properties file. ConnectionFactory.java also allows this functionality.
1. Have your printout of ConnectionFactory.java handy, and return to the Windows environment. Compile the program like this:
javac dbpackage\jdbc\ConnectionFactory.java 2. To run it using the Access database, use:
java dbpackage.jdbc.ConnectionFactory access.properties “SQL query” 3. Use any appropriate SQL query and try the program several times.
4. Now re-run it using your Oracle database and several appropriate queries. Can you figure out how to do this?
5. Try this application with MySQL.
6. Again, spend some time thoroughly reading over the source code for this program, and ask any questions that you have about it.
PreparedStatementExample.java
One of the objectives in creating the ConnectionFactory class was to provide an easy way for other programs to connect to various databases. PreparedStatementExample is a new program that will allow us to try this out. In addition, it introduces a new, useful interface from the java.sql package – called PreparedStatement. It allows us to pre-code a specific SQL statement and pass in only the variables that we want. For this example, we’ll search for student information from the Oracle student database, allowing their class year to be a parameter. In other words, we’ll be able to simply run the program using 1, 2, 3 or 4 as a parameter, returning only the student information for freshmen, sophomores, juniors and seniors, respectively. The PreparedStatement class is what allows us to do this.
In addition, make note of the fact that this program uses ConnectionFactory to make the appropriate database connection.
1. Take a look at the code listing for this program. Can you find where the PreparedStatement object is used? Do you see what it’s doing? What is the SQL statement that we’re going to run? Can you see that this program is hard-coded to use the Oracle database?
2. Compile the program:
javac dbpackage\jdbc\PreparedStatementExample.java 3. Run it:
java dbpackage.jdbc.PreparedStatementExample 3
to get a list of all juniors. Try it for other values as well. Then try to modify it for a different query.
Final Notes
Don’t worry too much about the details that have been skipped in this lab. We’ll be talking more about JDBC in class and in future lab activities. Hopefully this has given you a good basis from which to start.
To learn more about the capabilities of JDBC, it couldn’t hurt to browse around the Java documentation provided by Sun:
