< Zurück | Inhalt | Weiter >

Chapter 15


Accessing the Data:

An Introduction to JDBC


image

Java provides a back-end-independent interface to databases called Java DataBase Connectivity classes, or JDBC. We introduce the basics of the system here, and illustrate portability that makes it possible for our application to switch between two different database back ends.

image



15.1 WHAT YOU WILL LEARN


We will cover the basics of interaction with a database in Java. This involves

• Establishing and tearing down connections to a database

• Querying data in the database and reading the results

• Performing other database operations that modify data, but do not return data results

We assume that you are familiar with simple SQL constructs.


327


15.2 INTRODUCING JDBC


For many real-world applications, there are copious amounts of data associated with them. Programmers first learn to store data into files, but serious applica- tions require a database. Java programs can connect to and query a database with the help of several Java classes which together make up the Java DataBase Connectivity API, or JDBC.

With JDBC, your Java programs will be able to connect to databases any- where on the network and to query data from the database with the simple syntax that database programmers have been using for years—SQL.

JDBC provides an abstraction, a way to talk about the various aspects of working with a database which is largely vendor-independent. Implementations of JDBC can be, and are, built for many different databases and even other data sources, such as flat files and spreadsheets.

image

The Linux environment offers several choices of databases, the two most popular being MySQL and PostgreSQL. Both are Open Source projects avail- able with most major Linux distributions, as well as online for downloading. For many Java and Linux developers, however, the use of Java and Linux will include their development environment and the servers to which they deploy their applications, but the database to which they connect will still be the cor- porate database. For most commercial applications this is an Oracle database, the industry leader, and due to its major presence in the marketplace we will use Oracle in our examples as well.



NOTE

JDBC interfaces are available for almost any commercial database with any significant marketshare. See Section 15.9 for a URL that has a list of such choices. Most of what you will learn in this chapter will apply regardless of the database you connect to.


To make even the most basic use of JDBC, you must understand three basic operations:

• First, establishing and tearing down connections to your database server

• Second, querying data

• Finally, reading up the results of that query


These three operations correspond to JDBC objects for doing these very things, namely the classes Connection, PreparedStatement, and ResultSet. Let’s jump right in and look at some code. Example 15.1 will make a connection to a MySQL database, prepare a query statement, execute the query,

then read up the results.

Let’s also look at a similar example, but this time for an Oracle database (Example 15.2). Notice how much is the same between the two examples, and which parts are different.

The only real difference between the two programs has to do with the connections. Once the connection to the database is established, the rest of the code is exactly the same—which is what you’d hope for in an abstraction. This is a good news for developers: “Learn once, use anywhere.”


15.3 MAKING CONNECTIONS


The most complicated part of JDBC is establishing the connection. There are several ways to make a connection, depending on how much information about the connection driver you want hard-coded into your application. We are going to keep it simple and describe one straightforward way to connect.

The DriverManager class is where our application goes to get its connection to the database, as shown in our example. Many different JDBC drivers can register with the DriverManager, and it can make the connection to the kind of driver that you want based on the URL that you provide in the call to getConnection(). So where did our example register anything with the DriverManager? Well, it happened indirectly, via the Class.forName(...).newInstance(); call. That loaded the class and cre- ated an instance of it. The JDBC specification says that when a Driver class initializes it must register with the DriverManager. So it happened “under the covers,” in loading the driver class.

Another difference between the two examples deals with how the username and password are supplied to the database. Both are supplied in the URL, though in different syntax. That syntax is at the discretion of those who imple- mented the JDBC driver for that particular flavor of database. If we were to construct the URL at runtime, so that the user could supply a username and password dynamically, we’d want to remove the difference in how the URL is constructed. To do that we could use a call to getConnection() with a signa- ture that includes the username and password as separate String parameters:


image

Example 15.1 Simple sample program using JDBC for MySQL

import java.sql.*;


public class MyCon

{

public static void main(String [] args)

{

try {

// A simple connection example looks like this: Class.forName("com.mysql.jdbc.Driver").newInstance();

String url = "jdbc:mysql://host.domain.com/test"+ "?user=blah&password=blah";


Connection conn = DriverManager.getConnection(url);


// query

String mySQL = "SELECT id, pw FROM Users WHERE name = ?";


PreparedStatement stmt = conn.prepareStatement(mySQL); stmt.setString(1, args[0]);


// execute the query

ResultSet rs = stmt.executeQuery();


// read the results while(rs.next()) {

int id = rs.getInt("id"); String pw = rs.getString("pw");


System.out.println("id="+id);

}

} catch (Exception e) { System.out.println("Exception: "+e); e.printStackTrace();

}


} // main


} // class MyCon


image


image

Example 15.2 Simple sample program using JDBC for Oracle

// import oracle.jdbc.driver.*; import java.sql.*;


public class MyCon

{

public static void main(String [] args)

{

try {

// A simple connection example looks like this: Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

String url = "jdbc:oracle:thin:mydbusername/mydbpasswd"+ "@host.domain.com:1521:dbname";


Connection conn = DriverManager.getConnection(url);


// query

String mySQL = "SELECT id, pw FROM Users WHERE name = ?";


PreparedStatement stmt = conn.prepareStatement(mySQL); stmt.setString(1, args[0]);


// execute the query

ResultSet rs = stmt.executeQuery();


// read the results while(rs.next()) {

int id = rs.getInt("id"); String pw = rs.getString("pw");


System.out.println("id="+id);

}

} catch (Exception e) { System.out.println("Exception: "+e); e.printStackTrace();

}


} // main


} // class MyCon


image


Connection conn = DriverManager.getConnection(url, username, password);


Getting this to compile and run requires you to have the appropriate JDBC JAR files available. For Oracle, see your Oracle DBA, or see pages 228–229 of Java Oracle Database Development by David Gallardo. For MySQL, it’s an easy download you can install from the Internet.


 


15.3.1 Downloading JDBC for MySQL