Updated date:

Programming in Java Netbeans - A Step by Step Tutorial for Beginners: Lesson 51

Danson Wachira is a certified Trainer in Computer Science, Information Technology and related studies.

Table of Contents

<< Lesson 50 | Lesson 52 >>

Lesson 51: How to connect to a database in Java NetBeans

Having created a successful database connection in the previous lesson, we’ll connect to the database and extract records so that we display them on the console. Selecting records from a database table always begins with creating a successful connection to the database.

Database oriented applications usually consist of a database and GUI (Graphical User Interface) that connects to the database and manipulate database, tables and records. Manipulation can be through inserting records, updating records, deleting records, saving records, navigating through records etc.

programming-in-java-netbeans-a-step-by-step-tutorial-for-beginners-lesson-51

In this lesson, you’ll learn how to connect to a database table and how to extract records from a database table to Java NetBeans console window.

Later, you’ll learn how to connect Java forms to a database and how to populate the form with table records. To access a database table and extract records, you need SQL statements.

Once you extract records, you can then manipulate these records using Java code. To execute SQL statements, we need to import SQL object into the program. Open the program we had in Lesson 50 (Database_Connect) and add the following import statement at the top of the class.

import java.sql.Statement;

programming-in-java-netbeans-a-step-by-step-tutorial-for-beginners-lesson-51

Now, we need to create a statement object in the try part of the try … catch block. This statement object will need a connection object to access the database so, add the following statement just below the connection statement in the try part of the try … catch block.

Statement state = conn.createStatement( ); //Type as a single line

From the above statement, notice that we have named the statement object as state and we are using the createStatement() method to create the connection object.

The connection object will require SQL statement to extract records from the table. We learnt bit of SQL commands in Lesson 49. Add the following SQL statement since we need to extract All records from the Students table.

String SQL = "SELECT * FROM APP.Students"; //Type as a single line

The SQL statement uses a method called executeQuery() to execute SQL query statements but extracted records are returned as Result Set so, we need to create an object of ResultSet. Add the following import statement at the top of class.

import java.sql.ResultSet;

And the following statement in the try part of the try … catch block just below the SQL statement.

ResultSet Rset = state.executeQuery( SQL );

We have called our ResultSet Rset. So far, here is the program, you can copy and paste if you don’t have it yet.

package database_connect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class Database_Connect {

    public static void main(String[] args) {
    try{
        String host = "jdbc:derby://localhost:1527/School";
        String dName = "admin";
        String dPass= "admin";
        Connection conn = DriverManager.getConnection( host, dName, dPass );
        Statement state = conn.createStatement( );
        String SQL = "SELECT * FROM APP.Students";
        ResultSet Rset = state.executeQuery( SQL );   
    }
   catch ( SQLException err ) {
        System.out.println( err.getMessage( ) );
    }
  }
}

A ResultSet is a store to hold and manipulate records returned by the SQL query and there are three types of ResultSet. The choice of each depends on what you want to do with the records.

Types of ResultSets

  1. TYPE_FORWARD_ONLY for moving through records from start to end.
  2. TYPE_SCROLL_SENSITIVE for moving forward and backward through records while detecting changes made on the records.
  3. TYPE_SCROLL_INSENSITIVE for moving forward and backward through records but without detecting changes made on the records.

NB: If you do not specify the type of ResultSet to be used, the default TYPE_FORWARD_ONLY ResultSet will be used.

If you specify TYPE_SCROLL_SENSITIVE or TYPE_SCROLL_INSENSITIVE as ResultSet, then you also need to state whether it will be read only (i.e. CONCUR_READ_ONLY) or updatable (i.e. CONCUR_UPDATABLE.) We’ll use TYPE_SCROLL_SENSITIVE ResultSet with CONCUR_UPDATABLE so modify the Statement object in the program to the following:

Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

Here is the program so far.

package database_connect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class Database_Connect {

    public static void main(String[] args) {
    try{
        String host = "jdbc:derby://localhost:1527/School";
        String dName = "admin";
        String dPass= "admin";
        Connection conn = DriverManager.getConnection( host, dName, dPass );
        Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        String SQL = "SELECT * FROM APP.Students";
        ResultSet Rset = state.executeQuery( SQL );   
    }
   catch ( SQLException err ) {
        System.out.println( err.getMessage( ) );
    }
  }
}

To navigate through table records, we use a record pointer, also called table Cursor. A Cursor points the position of the table row and it will be positioned just before the first row of the table when you first load the records in ResultSet. Before we start record navigation, we need to specify the direction movement method of the Cursor. Here are the available movement methods.

ResultSet Row Movements

Movement methodDescription

next()

Moves the Cursor to the next row in the table. If the next row is not available, a False value will be returned.

previous()

Moves the Cursor to the previous row in the table. If the previous row is not available, a False value will be returned.

first()

Moves the Cursor to the first row of the table.

last()

Moves the Cursor to the last row of the table.

absolute()

Moves the Cursor to a particular table row e.g. absolute(3) moves Cursor to row 3 of the table.

In the table we created in Lesson 48, we had the following table columns; StdID, StdName, Faculty, Course, City, Address, Telephone, Remarks. The table fields (columns) can be accessed by their names or by their positions. For example, to access column StdID we can either use:

Int Id = Rset.getInt(“StdID”);

or

Int Id = Rset.getInt(1);

To get StdName column we can either use:

String sName = Rset.getString(“StdName”); //Type as single line

or

String sName = Rset.getString(2);

Notice the difference in data type, to access an integer field, we use Rset.getInt(); and to access a String filed, we use Rset.getString(); remember Rset is our ResultSet object.

When we extract records to ResultSet, the cursor will be at a position just before the first row. So, we need to use the next() movement to move to the first record. Here is how we get the first record of the table.

Rset.next( );

int Id = Rset.getInt("StdID");

String sName = Rset.getString("StdName");

String sFaculty = Rset.getString("Faculty");

String sCourse = Rset.getString("Course");

String sCity = Rset.getString("City");

String sAddress = Rset.getString("Address");

String sTelephone = Rset.getString("Telephone");

String sRemarks = Rset.getString("Remarks");

Notice that in the above statements, Rset.next() is placed at the top before we start accessing records. This is so because we need to be sure that the Cursor is at the correct position. After that, we are just creating new String variables and assigning them with the record values. Now we can add statements to display the record on the console.

System.out.println("Id"+" "+"NAME"+" "+"FACULTY"+" "+"COURSE"+" "+"CITY"+" "+"ADDRESS"+" "+"TELEPHONE"+" "+"REMARKS");

System.out.println(Id+" "+sName+" "+sFaculty+" "+sCourse+" "+sCity+" "+sAddress+" "+sTelephone+" "+sRemarks);

In the above output code, we are creating column labels in the first statement and then the real record values in the second statement. Here is everything now in the program.

package database_connect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class Database_Connect {

    public static void main(String[] args) {
    try{
        String host = "jdbc:derby://localhost:1527/School";
        String dName = "admin";
        String dPass= "admin";
        Connection conn = DriverManager.getConnection( host, dName, dPass );
        Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        String SQL = "SELECT * FROM APP.Students";
        ResultSet Rset = state.executeQuery( SQL );
       
        Rset.next( );
        int Id = Rset.getInt("StdID");
        String sName = Rset.getString("StdName");
        String sFaculty = Rset.getString("Faculty");
        String sCourse = Rset.getString("Course");
        String sCity = Rset.getString("City");
        String sAddress = Rset.getString("Address");
        String sTelephone = Rset.getString("Telephone");
        String sRemarks = Rset.getString("Remarks");
        
        System.out.println("Id"+"   "+"NAME"+"  "+"FACULTY"+"   "+"COURSE"+"    "+"CITY"+"  "+"ADDRESS"+"   "+"TELEPHONE"+" "+"REMARKS");
        System.out.println(Id+" "+sName+"   "+sFaculty+"    "+sCourse+" "+sCity+"   "+sAddress+"    "+sTelephone+"  "+sRemarks);
    }
   catch ( SQLException err ) {
        System.out.println( err.getMessage( ) );
    }
  }
}

Output for a single record

programming-in-java-netbeans-a-step-by-step-tutorial-for-beginners-lesson-51

As you can see, we are able to access the table and display the first record. To display all records from the table, we need to loop through the ResultSet as we display each record. Include the WHILE loop as shown in the following code. The WHILE loop will continue so long as the Cursor has not gone past the last record. If the Cursor returns False, the loop will stop. Notice in the code that we need to bring the label output statement outside of WHILE loop to avoid outputting it more than once.

package database_connect;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
public class Database_Connect {

    public static void main(String[] args) {
    try{
        String host = "jdbc:derby://localhost:1527/School";
        String dName = "admin";
        String dPass= "admin";
        Connection conn = DriverManager.getConnection( host, dName, dPass );
        Statement state = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        String SQL = "SELECT * FROM APP.Students";
        ResultSet Rset = state.executeQuery( SQL );
       
        System.out.println("Id"+"   "+"NAME"+"  "+"FACULTY"+"   "+"COURSE"+"    "+"CITY"+"  "+"ADDRESS"+"   "+"TELEPHONE"+" "+"REMARKS");
        while(Rset.next( )){;
        int Id = Rset.getInt("StdID");
        String sName = Rset.getString("StdName");
        String sFaculty = Rset.getString("Faculty");
        String sCourse = Rset.getString("Course");
        String sCity = Rset.getString("City");
        String sAddress = Rset.getString("Address");
        String sTelephone = Rset.getString("Telephone");
        String sRemarks = Rset.getString("Remarks");
       
        System.out.println(Id+" "+sName+"   "+sFaculty+"    "+sCourse+" "+sCity+"   "+sAddress+"    "+sTelephone+"  "+sRemarks);
       }
    }
   catch ( SQLException err ) {
        System.out.println( err.getMessage( ) );
    }
  }
}

Output for all records

programming-in-java-netbeans-a-step-by-step-tutorial-for-beginners-lesson-51

If your code is correct and running, you have completed a very important stage in programming with Java NetBeans. Having known how to connect to a database table, extract records and display them on console is one important step in Java NetBeans programming and databases. In the next lesson, we’ll learn how to connect Java forms to a database and populate the form with table records.

<< Lesson 50 | Lesson 52 >>

Other related Hubs...

  • How to work with Arrays of string type in Java
    In some cases in Java programming, you would need to store values of string type in an array like days of the week or Boolean (True or False) values. In this article, we shall learn how we can implement and work with arrays of string type in Java.
  • Working with GUI controls: Java Menus
    A Menu is a navigation control that enables users to access application options in a hierarchical manner. Each menu item may contain a menu list of items which subsequently may contain sub-items and so on. Menus come in different forms and functional
  • Using Java Save File dialog box to save files
    Java Save dialog box, unlike Java Open dialog box, is used to display various computer locations where users can save files. This article discusses how to implement Java Save dialog box in Java NetBeans programming environment.
  • Control Structures in Java – The WHILE Loop in Java
    The WHILE loop in Java execute a statement or a group of statements so long as the specified condition remains TRUE. The loop starts with the keyword “while”. Just after the keyword you open the round brackets and specify the condition to be tested.

Want to make money online?

Comments

Danson Wachira (author) from Nairobi, Kenya on April 18, 2013:

Hi SidKemp,

We can always start from somewhere and being a student many years ago, i wished if i could get information i needed from one place. Thanks for the visit and comment.

Sid Kemp from Boca Raton, Florida (near Miami and Palm Beach) on April 18, 2013:

This is excellent, clearly written instruction for programmers. Thanks. Voted up and useful.

Danson Wachira (author) from Nairobi, Kenya on April 17, 2013:

Hi Ingenira,

Thanks for the positive comment, i do appreciate the visit and comment.

Ingenira on April 17, 2013:

Useful instruction, dwachira. Thanks !

Danson Wachira (author) from Nairobi, Kenya on April 17, 2013:

Hi Bill,

Agreed, there is always hope and soon you'll make headway, only that if you jump onto programming we may have go without your wonderful articles, personally i won't like to miss that. Thanks for the visit and comment.

Danson Wachira (author) from Nairobi, Kenya on April 17, 2013:

Hi kidscrafts,

I like to see students gaining knowledge less painfully, the only problem is that my area is restricted mostly to programming. Thanks for the visit and comment.

kidscrafts from Ottawa, Canada on April 17, 2013:

Great hub Dwachira! I hope that you can teach all this information live to students who will benefit of all your knowledge!

Bill Holland from Olympia, WA on April 17, 2013:

This is on my to do list my friend, but it's a long list and I'm not making much headway. You give such great advice and your tutorials are always so detailed, that it's a shame I haven't done this yet. Hope...there's always hope, right? :) Thanks for the information and I have bookmarked it along with the rest of your fine hubs on this subject.

Related Articles