Getting Started with database programming in Java

In this example, I'm going to assume that you'll be using Visual J++, because that's what we have in 232, etc. If you're not using Visual J++, then you'll just need to change around a few things, such as the driver used. If you're using Linux, you'll need to figure out the equivalent way of creating an ODBC entry for your machine, and then most of the same methods should work.

Getting started with Visual J++

First, start up Visual J++ using the start, programs, where ever Visual J++ is; either at that level, under Visual Studio 6.0, or somewhere more cryptic.

The first time that you do open up Visual J++, it will ask you what kind of application you want to start up. We recommend that you do a console application; this will allow you to make the most portable Java application, and this will allow what you learn to be the most general. What I'll be showing you here is how to create a simple application that uses pure java and does not rely on any Microsoft extensions. If you feel a desperate need to do a windows application or use ADO, we won't stop you, but we won't be able to help you much, either. So your selections should look something like:

You should now have a new project open with a Project Explorer somewhere in the setting, probably either on the left or right hand side of the window. The name of your project will be the top, and there will be a little + next to it. If you expand that, you'll see the names of all the java file currently in the project. Currently you'll see a file called Class1.java.

Don't forget that if you want to use some other class as your main class (the one that will get called when you start the application), you need to select that by changing the project properties (Use the "Project" drop down, select "Properties")

Next you'll want to actually begin programming the application. As we said in class, we've put on reserve in the engineering library 2 books on programming in JDBC. The two titles are Client/Server Programming with Java and CORBA by Robert Orfal and Database Programming with JDBC and Java by George Reese.

There is a short file that will give you all the basics of making a connection with Java, again, assuming that you've created your ODBC connection and called it "entities". Here are the important parts.

Getting Started with JDBC

At this point, assuming that you have a basic understanding of Java, and have done all of the steps above, you should be ready to start programming with the JDBC stuff. It's really pretty easy (well, once someone tells you exactly how to get it to work, at any rate. ;)

The sample file that creates a new TextArea, then polls the database and extracts all the tuples for one table and puts them into the TextArea. Not very exciting, but with this you should be able to figure out how to do anything you want.

The important stuff is in the not terribly well named function "doIt", which is reproduced here:

public void doIt(){
 try {
 Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
 java.sql.Connection c = DriverManager.getConnection("jdbc:odbc:entities2","","");
 java.sql.Statement s= c.createStatement();
 java.sql.ResultSet rs;
 rs = s.executeQuery("Select * from [csepclab\\hongyu].Office");
 java.sql.ResultSetMetaData md = rs.getMetaData();
 while (rs.next()){
 area.append("\nTUPLE: |");
 for (int i = 1; i <= md.getColumnCount();i++){
 area.append(rs.getString(i) + " | ");
 }
 }
 //s.executeUpdate("Insert into Paper values(13,1999,'Interesting')");
 rs.close();
 } 
 catch (Exception e){
 e.printStackTrace();
 System.out.println("something went wrong in database land");
 }
} 

Let's look at this line by line...

The first interesting like is Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
This tells java that you're going to be using the MS version of the jdbc drivers. If you're using Visual Cafe, or some other development system, you'll need to use their drivers instead.

Next, we have java.sql.Connection c = DriverManager.getConnection("jdbc:odbc:entities","","");
This line has several parts. First, we create a new SQL connection, and we name it C. Then we tell the Driver Manager (which we've just added the JdbcOdbc driver to in the Class.forName line) that we're going to get a connection and we're going to use the following pieces of information. First, the URL is going to be "jdbc:odbc:entities". The URL tells the device manager not only where to look, but what protocols to use (yes, I know, this seems redundant with the Class.forName line, but that's how it is). It has the format Protocol:subprotocol:location. So we are telling it that we're going to use a jdbc connection, then the odbc driver of it, and finally that the database that we're going to use is called entities. Next we have two sets of empty quotation marks. These hold the login name and password. Since we're using NT authentication, SQL Server won't be using them, and it doesn't care what you put in. If you use SQL Server authentication, however, you'd put the name and password given to you (not your CSEPCLAB login and password) there.

Next we have java.sql.Statement s= c.createStatement();, which creates a new SQL statement

java.sql.ResultSet rs; creates a new ResultSet, which is where Java will store the answers to the query.

rs = s.executeQuery("Select * from [csepclab\\hongyu].Office"); executes the above query (note that since \ is an escape character, we needed it twice).

The rest of the lines get the meta data, print out the tuples to the text area, and close it up.

You'll note that there's one commented line there that begins with s.executeUpdate. That line shows you how to execute an update, which is exactly how you'd expect it to work, but be extra careful about quotation marks and apostrophes. The above worked for me (well, on a different database).

That's all folks

Enjoy your JDBC hacking!