Getting Started with database programming in Java

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

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. I'd fill them in appropriately:

Click next and choose that you want to connect with SQL Server authentication, and unselect the "Connect to SQL Server to obtain default settings ..." option. Click "Next".

On the next windows, just click "Next" again, until you're done (until you reach the Finish button).

Getting started with Visual J++

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")

We've put on reserve in the engineering library two 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'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 "444". You can copy this file into your newly created project, erase the "Class1" class and use the hint above (previous paragraph) to change your main class into the connect file.

When you run this example you need to wait about 10seconds until you see some results. It's a bit slow. But it works. I'll explain all of the really important parts here.

Getting Started with JDBC

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:cse444","cse444","cse444");
    java.sql.Statement s= c.createStatement();
    java.sql.ResultSet rs;
    rs = s.executeQuery("Select * from beers");
    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) + " | ");
      }
    }
    rs.close();
  } 
  catch (Exception e){
    e.printStackTrace();
    System.out.println("something went wrong in database land");
  }
}		

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:cse444","cse444","cse444");
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:cse444". 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.

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 beers"); executes the above query.

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

That's all folks