1. Java and Databases: JDBC

The JDBC API defines Java classes to represent database connections, SQL statements, result sets, database metadata, etc. It allows a Java programmer to issue SQL statements to read/write a database.

The JDBC consists of an API (the specification of a library or application programmer's interface) and a package containing about 20 Java classes to implement the application program side of the API. Some of the classes are interfaces, and the code that implements them must be provided by an additional database-specific piece of software called a driver.

Java programs call methods in the JDBC package to connect with databses through their drivers, then retrieve, process, and write information. [1]

The package that holds the Java code for the JDBC is called java.sql.

SQL is a specialized programming language used to access just about all (relational) databases. We don't want to make Java replace SQL, we just want Java to be able to bundle up SQL queries, direct them to the right databse, and listen for the answers.

2. The Classes in the JDBC

The JDBC API is implemented by about 20 Java classes. They can be divided into 3 groups:

1. Connectivity Classes

2. Data Processing Classes

3. Support

3. Using the JDBC

There are four steps that all Java programs follow to talk to a database using JDBC:

1. Load any drivers you are going to need

2. Tell the JDBC what database you want to use

3. Connect to that database

4. Send SQL statements across the connection, and get results back.

3.1 Load any drivers you are going to need.

This is a system specific step that depends on how exactly you will connect to the database.

All databases have a driver at their end to accept queries, feed them into the database's internal form and deliver results.

Although just about all databases speak SQL in theory, they all actually have slightly incompatible local dialects of SQL.

What's needed is a driver or a pipeline of drivers that speak to the JDBC at one end, and can speak the database dialect at the other end. Right here is where you can start to get into trouble, because there are no drivers supplied with the JDBC, you have to obtain them from the database vendors or from third party tools companies.

Although these driver components are essential to use the JDBC, this is a "batteries not included" kind of deal. If you want to run the Java program that accesses a database, you need to additionally procure the database and its drivers.

3.2 Specify what database you want to use.

You tell JDBC what database you want to use in the form of a URL. Here's an example:

jdbc:odbc://host.domain.com:2048/data/base/file

As you might guess the form and content of the URL is very specific to individual databases and the drivers you will use for access. Whether or not the port number os necessary, or even required, is a driver-dependent feature, as are the parameters in the URL after the subprotocol (odbc here). Your just have to read the documentation that comes with the driver.

3.3 Connect to that database.

This is the point where your code registers with the JDBC. You will use the connectivity classes to connect to the database of interest.

Your program can be talking to several different databases at once. For this reason the JDBC package is sometimes called a driver manager which can be anything that controls multiple drivers.

3.4 Send SQL statements across the connection and get results back

Once you have a connection open to your database, you will start sending SQL statements across and getting back result sets.

You create a new thread for any SQL statement that might take a long time. You are responsible for providing synchronization and avoiding data races where necessary.

Queries and updates are sent across to the database in the form of SQL statements, which are jusr Strings containing SQL text. The results usually come back as something called a "Result Set".

The result will usually be in the form of a table (several rows each of several columns of data). There are methods to access individual rows by index and to get the data back as a String or some other type.

When you have finished all the processing close everything down in an orderly manner.

As we mentioned above actually extracting information from a database and writing it back is done in SQL (Structured Query Language). SQL has been refined over more than two decades and is the language used to access essentially all modern databases.

Years ago, there used to several fundamentally different architectures for databases: there were

• hierarchical databases
• network databases
• and relational databases

We're also starting to see early use of object-oriented databases, some of which are accessed in a relational fashion.

The collision between object-oriented and relational databases is an area of emerging technology.

The database gurus have their own terminology of relations, tuples, and normal forms, but (in plain words) the central idea to a relational database is that data is kept in tables.

In a way a table in a relational database is like an enormous spreadsheet. it might have millions of rows and hundreds of columns. Each column contains only one kind of data. A row in a table corresponds to a record. The database can contain several tables. A programmer will use SQL statements to merge tables and extract data from them.

That's the basic idea.

At first every database vendor had its own special database query language. Users eventually got fed up enough to create an industry standard around IBM's SQL. There was the SQL'89 standard, followed by the SQL'92 standard, both created under the ANSI umbrella. But SQL is still fragmented into many subtly-different slightly incompatible dialects.

The JDBC classes do their work in terms of SQL, so it takes an understanding of SQL to describe what these are. SQL is a pretty elaborate programming language in its own right, customized to handle tables, rows and columns. Describing SQL would take more room that is available here. Suffice it to say that SQL has statements like SELECT, INSERT, DELETE, and UPDATE. SQL operates on tables; merging, matching and extracting from them and provides its result sets in the form of tables.

In class we will demo jConnect from Sybase.

You can download it for 30 days from Sybase.

Username: jConn
Password: Release4

B. Java Swing and Java Foundation Classes (JFC)

SimpleBrowser.java

Please compare this to the simple web client that we have described in the previous lecture.

tucotuco.cs.indiana.edu% pwd
/nfs/paca/home/user2/dgerman/browser
tucotuco.cs.indiana.edu% ls
SimpleBrowser$TextFieldListener.class  SimpleBrowser.java
SimpleBrowser.class                    test
tucotuco.cs.indiana.edu% cat SimpleBrowser.java
import java.awt.*;
import java.awt.event.*;
import java.io.*;
 
import javax.swing.*;
import javax.swing.text.html.*;
import javax.swing.event.*;
 
public class SimpleBrowser extends JFrame {
 
    static JTextField textField;
    static JEditorPane editor;
 
    public SimpleBrowser(String s) {
        super(s);
 
        JPanel panel = new JPanel();
        panel.setLayout(new BorderLayout());
        panel.setBorder(BorderFactory.createRaisedBevelBorder());
 
        editor = new JEditorPane();
        textField  = new JTextField();
        JScrollPane scrollPane = new JScrollPane(editor);
 
        editor.setEditable(false);
 
        panel.add(new JLabel("Location:  "), BorderLayout.WEST);
        panel.add(textField, BorderLayout.CENTER);
 
        getContentPane().add(panel, BorderLayout.NORTH);
        getContentPane().add(scrollPane, BorderLayout.CENTER);
 
        textField.addActionListener(new TextFieldListener());
    }
 
    public static void main(String args[]) {
        SimpleBrowser frame = new SimpleBrowser("Simple Browser");
        frame.setSize(400,400);
        frame.setVisible(true);
    }
 
    class TextFieldListener implements ActionListener {
 
        public void actionPerformed(ActionEvent e) {
            try {
                editor.setPage(textField.getText());
            } catch (IOException ex) {
                editor.setText("Page could not be loaded");
            }
        }
    }
}
 

tucotuco.cs.indiana.edu% pwd
/nfs/paca/home/user2/dgerman/browser
tucotuco.cs.indiana.edu% setenv CLASSPATH .:/l/jdk1.2fcs/lib/:/l/swing/swingall.jar
tucotuco.cs.indiana.edu% echo $CLASSPATH
.:/l/jdk1.2fcs/lib/:/l/swing/swingall.jar
tucotuco.cs.indiana.edu% setenv PATH /l/jdk1.2fcs/bin/:$PATH 
tucotuco.cs.indiana.edu% echo $PATH
/l/jdk1.2fcs/bin/:/u/dgerman/bin:/usr/local/bin:/usr/bin[...] 
tucotuco.cs.indiana.edu% javac SimpleBrowser.java
tucotuco.cs.indiana.edu% java SimpleBrowser # set DISPLAY variable if needed !!   

C. sybperl (Sybase extension to Perl)

The documentation for an older version of sybperl.

This script available from

http://www.best.indiana.edu/cgi-bin/eclipse/sybase

#!/usr/bin/perl

print qq{
Content-type: text/html

<html>
<head>
<title>
A title for all
</title>
</head>
<body>
<h3> This is a test </h3>
<pre>
};

use Sybase::DBlib;
$dbh = new Sybase::DBlib 'dgerman', '12345678', 'zinc_sybase', 'test'; 
$dbh->dbcmd("select * from test_table\n"); 
$dbh->dbsqlexec; 
$dbh->dbresults;
while (@data = $dbh->dbnextrow) {
  print "\n-------------------------------------------------------\n";
  foreach $d (@data) {
    print $d, "\n"; 
  }
}

print "</pre> </body> </html>";