New!

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.

Adding an ODBC connection to the machine you're working on

In order to talk to the database, you'll need to put an ODBC connection on the machine that you're working on. To do this, do Start-> Settings -> Control Panel, then choose "ODBC Data Sources". You'll want to add a "System DSN". User DSN means that only you will be able to access this data source, and System DSN means that anyone who is on the machine should be able to access the data source (though only if they provide the necessary NT credentials). A User DSN (which seems like it would be what you want) won't work, so make sure that you choose a System DSN.

So choose System DSN and click on "Add"

At this point, it'll ask you for which driver you want to use. Since you'll be connecting to your project database, you'll want to choose SQL Server. So select "SQL Server", and click finish (no, of course that doesn't mean that you're done). Next you'll want to specify which database you want to talk to. You'll see a dialogue box with three boxes for you to fill in. Pretending that I'm a member of the group "entities", I'd fill them in appropriately:

Click next and choose that you want to connect with SQL Server authentication, and then use the DSN name, login ID, and password that I sent you for the ASP stuff. If you deleted that info, send me mail, and I'll send it to you again. Note that this has changed from the original specification

Click "Next" again, and it'll contact the database to make sure that you have the appropriate permissions. Make sure that you have your group's database selected as the default database (it shouldn't be a problem for you, because you have permissions for one database on ISQL01, so it should figure that out automatically, but check it just in case).

Click next again, then finish, and finally, click "Test Data Source" to make sure that everything ran properly.

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. I'll try to add links to online references here; if you find any, please e-mail me (rap@cs.washington.edu), and I'll add them to the list.

I've created a short file that will give you all the basics of making a connection of jdbc, again, assuming that you've created your ODBC connection and called it "entities". I'll explain all of the really important parts here.

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 I have here 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 I've 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!