Connecting a Database in Java

Connecting  a Database in Java

Connecting to the Database

• To connect to a database you need a Connection object. The Connection object uses a DriverManager. 
• The DriverManager passes in your database username, your password, and the location of the database.
• Add these three import statements to the top of your code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

To set up a connection to a database, the code is this:

Connection con = DriverManager.getConnection( host, username, password );

• So the DriverManager has a method called getConnection. 
• This needs a host name (which is the location of your database), a username, and a password.
• If a connection is successful, a Connection object is created, which we've called con.
• You can get the host address by looking at the Services tab on the left of NetBeans:
• Image services_host

The address of the highlighted database above is:

jdbc:derby://localhost:1527/Employees

The first part, jdbc:derby://localhost, is the database type and server that you're using. 

The 1527 is the port number. The database is Employees. This can all go in a String variable:

String host = "jdbc:derby://localhost:1527/Employees";

Two more strings can be added for the username and password:

String uName = "Your_Username_Here";
String uPass= " Your_Password_Here ";

Add these three string before the connection object and your code would look like this:
Image connection_code

As you can see in the image above, there is a wavy underline for the Connection code. The reason for this is because we haven't trapped a specific error that will be thrown up when connecting to a database - the SQLException error.

It's the DriverManager that attempts to connect to the database. If it fails (incorrect host address, for example) then it will hand you back a SQLException error. You need to write code to deal with this potential error. 
In the code below, we're trapping the error in catch part of the try … catch statement:

try {

}
catch ( SQLException err ) {
System.out.println( err.getMessage( ) );
}

In between the round brackets of catch, we've set up a SQLException object called err. We can then use the getMessage method of this err object.

Add the above try …catch block to your own code, and move your four connection lines of code to the try part. Your code will then look like this:
Image connection_code2
Try running your code and see what happens.

You may get this error message in the console window:

"java.net.ConnectException : Error connecting to server localhost on port 1527 with message Connection refused: connect."

If you do, it means you haven't connected to your database server. In which case, right click on Java DB in the Services window. From the menu that appears, click Start Server:

Image services_start_server

• You need to make sure that any firewall you may have is not blocking the connection to the server.
• A good firewall will immediately display a message alerting you that something is trying to get through, and asking if you want to allow or deny it. 
• When you allow the connection, your NetBeans output window should print the following message:

"Apache Derby Network Server - 10.4.1.3 - (648739) started and ready to accept connections on port 1527 at DATE_AND_TIME_HERE"

Once your server is started, run the programme again. There's a very good chance you'll get another error message:

"No suitable driver found for jdbc:derby://localhost:1527/Employees"

The reason for this error is that the DriverManager needs a Driver in order to connect to the database. Examples of drivers are Client Drivers and Embedded Drivers. 
You can import one of these so that the DriverManager can do its job.

Click on the Projects tab to the left of the Services window in NetBeans. (If you can't see a Projects tab, click Window > Projects from the menu bar at the top of NetBeans.)

Locate your project and expand the entry. Right-click Libraries. From the menu that appears, selectAdd Jar/Folder:

Image projects_add_jar

When you click on Add Jar/Folder a dialogue box appears. What you're doing here is adding a Java Archive file to your project. 
But the JAR file you're adding is for the derby Client Drivers. So you need to locate this folder. On a computer running Windows this will be in the following location:

C:\Program Files\Sun\JavaDB\lib

The file you're looking for is called derbyclient.jar. If you can't find it, or are using an operating system other than Windows, then do a search for this file. Note the location of the file.

In the dialogue box, select the derbyclient.jar file:

Image projects_add_jar_DB
Click Open and the file will be added to your project library:
Image projects_add_jar_client
Now that you have a Client driver added to your project, run your programme again. You should now be error free. (The Output window will just say Run, and Build Successful.)

Connecting  a Database Table

Now that you have connected to the database, the next step is to access the table in your database. 
For this, you need to execute a SQL Statement, and then manipulate all the rows and columns that were returned.
To execute a SQL statement on your table, you set up a Statement object. So add this import line to the top of your code:

import java.sql.Statement;

In the try part of the try … catch block add the following line (add it just below your Connection line):

Statement stmt = con.createStatement( );

Here, we're creating a Statement object called stmt. The Statement object needs a Connection object, with the createStatment method.

We also need a SQL Statement for the Statement object to execute. So add this line to your code:

String SQL = "SELECT * FROM Workers";

The above statement selects all the records from the database table called Workers.
We can pass this SQL query to a method of the Statement object called executeQuery. 
The Statement object will then go to work gathering all the records that match our query.
However, the executeQuery method returns all the records in something called a ResultSet. Before we explain what these are, add the following import line to the top of your code:

import java.sql.ResultSet;

Now add this line just below your SQL String line:

ResultSet rs = stmt.executeQuery( SQL );

So our ResultSet object is called rs. This will hold all the records from the database table. Before we go any further, though, here's an explanation of what ResultSets are.

ResultSets in Java

A ResultSet is a way to store and manipulate the records returned from a SQL query. ResultSets come in three different types. The type you use depends on what you want to do with the data:

1.     Do you just want to move forward through the records, from beginning to end?

2.     Do you want to move forward AND backward through the records, as well as detecting any changes made to the records? 

3.     Do you want to move forward AND backward through the records, but are not bothered about any changes made to the records?

Type number 1 on the list above is called a TYPE_FORWARD_ONLY ResultSet. 

Number 2 on the list is a TYPE_SCROLL_SENSITIVE ResultSet. 

The third ResultSet option is called TYPE_SCROLL_INSENSITIVE.

The ResultSet type goes between the round brackets of createStement:

Statement stmt = con.createStatement( );

Because we've left the round brackets empty, we'll get the default RecordSet, which is TYPE_FORWARD_ONLY.

 In the next section, we'll use one of the other types. But you use them like this:

Statement stmt = con.createStatement( RecordSet.TYPE_SCROLL_SENSITIVE );

So you first type the word RecordSet. After a dot, you add the RecordSet type you want to use.

However, it doesn't end there. If you want to use TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE you also need to specify whether the ResultSet is Read Only or whether it is Updatable. You do this with two built-in constants: CONCUR_READ_ONLY and CONCUR_UPDATABLE. Again, these come after the word RecordSet:

ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE

This leads to a rather long line of code:

Statement stmt = con.createStatement( RecordSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

One more thing to get used to with ResultSets is something called a Cursor. 
A Cursor is really just a pointer to a table row. When you first load the records into a ResultSet, the Cursor is pointing to just before the first row in the table. You then use methods to manipulate the Cursor. But the idea is to identify a particular row in your table.

Using a ResultSet

Once you have all the records in a Results set, there are methods you can use to manipulate your records. Here are the methods you'll use most often:

Image resultset_options

The ResultSet also has methods you can use to identify a particular column (field) in a row. 
You can do so either by using the name of the column, or by using its index number. For our Workers table we set up four columns. 
They had the following names: ID, First_Name, Last_Name, and Job_Title. The index numbers are therefore 1, 2, 3, 4.
We set up the ID column to hold Integer values. The method you use to get at integer values in a column is getInt:

int id_col = rs.getInt("ID");

Here, we've set up an integer variable called id_col. 
We then use the getInt method of our ResultSet object, which is called rs. In between the round brackets, we have the name of the column. We could use the Index number instead:

int id_col = rs.getInt(1);

Notice that the Index number doesn't have quote marks, but the name does.

For the other three columns in our database table, we set them up to hold Strings. We, therefore, need the getString method:

String first_name = rs.getString("First_Name");

Or we could use the Index number:

String first_name = rs.getString(2);

Because the ResultSet Cursor is pointing to just before the first record when the data is loaded, we need to use the next method to move to the first row. 
The following code will get the first record from the table:

rs.next( );
int id_col = rs.getInt("ID");
String first_name = rs.getString("First_Name");
String last_name = rs.getString("Last_Name");
String job = rs.getString("Job_Title");

Notice that rs.next comes first in this code. This will move the Cursor to the first record in the table.

You can add a print line to your code to display the record in the Output window:

System.out.println( id_col + " " + first_name + " " + last_name + " " + job );

Here's what your code should look like now (we've adapted the print line because it's a bit too long):

Image code_record1

If you want to go through all the records in the table, you can use a loop. Because the next method returns true or false, you can use it as the condition for a while loop:

while ( rs.next( ) ) {

}

• In between the round brackets of while we have rs.next. 
• This will be true as long as the Cursor hasn't gone past the last record in the table. If it has, rs.next will return a value of false, and the while loop will end. 
• Using rs.next like this will also move the Cursor along one record at a time. 
• Here's the same code as above, but using a while loop instead. Change your code to match:
• Image code_record_loop
• When you run the above code, the Output window should display the following:
• Image code_record_loop_output
• Now that you have an idea of how to connect to a database table and display records we'll move on and write a more complex programme using forms and buttons to scroll through the records.