Of course, you need a database program for which a JDBC driver is available. There are many excellent choices, such as IBM DB2, Microsoft SQL Server, MySQL, Oracle, and PostgreSQL.
You must also create a database for your experimental use. We assume you name it COREJAVA. Create a new database, or have your database administrator create one with the appropriate permissions. You need to be able to create, update, and drop tables in the database.
If you have never installed a client/server database before, you might find setting up the database to be somewhat complex—diagnosing causes of failure can be difficult. It might be best to seek expert help if your setup is not working correctly.
If this is your first experience with databases, we recommend that you use the Apache Derby database, which is available from http://db.apache.org/derby.
You need to gather a number of items before you can write your first database program. The following sections cover these items.
1. Database URLs
When connecting to a database, you must use various database-specific parameters such as host names, port numbers, and database names.
JDBC uses a syntax similar to that of ordinary URLs to describe data sources. Here are examples of the syntax:
These JDBC URLs specify a Derby database and a PostgreSQL database named COREJAVA.
The general syntax is
where a subprotocol selects the specific driver for connecting to the database.
The format for the other stuff depends on the subprotocol used. You will need to look up your vendor’s documentation for the specific format.
2. Driver JAR Files
You need to obtain the JAR file in which the driver for your database is located. If you use Derby, you need the file derbyctient.jar. With another database, you need to locate the appropriate driver. For example, the PostgreSQL drivers are available at http://jdbc.postgresqt.org.
Include the driver JAR file on the class path when running a program that accesses the database. (You don’t need the JAR file for compiling.)
When you launch programs from the command line, simply use the command
java -classpath driverPath:. ProgramName
On Windows, use a semicolon to separate the current directory (denoted by the . character) from the driver JAR location.
3. Starting the Database
The database server needs to be started before you can connect to it. The details depend on your database.
With the Derby database, follow these steps:
- Open a command shell and change to a directory that will hold the database files.
- Locate the file derbyrun.jar. With some versions of the JDK, it is contained in the jdk/db/tib directory. If it’s not there, install Apache Derby and locate the JAR file in the installation directory. We will denote the directory containing tib/derbyrun.jar with
- Run the command
java -jar derby/tib/derbyrun.jar server start
- Double-check that the database is working correctly. Create a file ij.properties that contains these lines:
From another command shell, run Derby’s interactive scripting tool (called ij) by executing
java -jar derby/tib/derbyrun.jar ij -p ij.properties
Now you can issue SQL commands such as
CREATE TABLE Greetings (Message CHAR(20));
INSERT INTO Greetings VALUES (‘Hetto, World!’);
SELECT * FROM Greetings;
DROP TABLE Greetings;
Note that each command must be terminated by a semicolon. To exit, type
- When you are done using the database, stop the server with the command
java -jar derby/tib/derbyrun.jar server shutdown
If you use another database, you need to consult the documentation to find out how to start and stop your database server, and how to connect to it and issue SQL commands.
4. Registering the Driver Class
Many JDBC JAR files (such as the Apache Derby driver) automatically register the driver class. In that case, you can skip the manual registration step that we describe in this section. A JAR file can automatically register the driver class if it contains a file META-INF/services/java.sqt.Driver. You can simply unzip your driver’s JAR file to check.
If your driver’s JAR file doesn’t support automatic registration, you need to find out the name of the JDBC driver classes used by your vendor. Typical driver names are
There are two ways to register the driver with the DriverManager. One way is to load the driver class in your Java program. For example,
Class.forName(“org.postgresql.Driver”); // force loading of driver class
This statement causes the driver class to be loaded, thereby executing a static initializer that registers the driver.
Alternatively, you can set the jdbc.drivers property. You can specify the property with a command-line argument, such as
java -Djdbc.drivers=org.postgresql.Driver ProgramName
Or, your application can set the system property with a call such as
You can also supply multiple drivers; separate them with colons, for example:
5. Connecting to the Database
In your Java program, open a database connection like this:
String url = “jdbc:postgresql:COREJAVA”;
String username = “dbuser”;
String password = “secret”;
Connection conn = DriverManager.getConnection(url, username, password);
The driver manager iterates through the registered drivers to find a driver that can use the subprotocol specified in the database URL.
The getConnection method returns a Connection object. In the following sections, you will see how to use the Connection object to execute SQL statements.
To connect to the database, you will need a user name and password for your database.
The test program in Listing 5.1 puts these steps to work. It loads connection parameters from a file named database.properties and connects to the database. The database.properties file supplied with the sample code contains connection information for the Derby database. If you use a different database, put your database-specific connection information into that file. Here is an example for connecting to a PostgreSQL database:
After connecting to the database, the test program executes the following SQL statements:
CREATE TABLE Greetings (Message CHAR(20))
INSERT INTO Greetings VALUES (‘Hello, World!’)
SELECT * FROM Greetings
The result of the SELECT statement is printed, and you should see an output of
Then the table is removed by executing the statement
DROP TABLE Greetings
To run this test, start your database as described previously, and launch the program as
java -classpath .:driverJAR test.TestDB
(As always, Windows users need to use ; instead of : to separate the path elements.)
Source: Horstmann Cay S. (2019), Core Java. Volume II – Advanced Features, Pearson; 11th edition.