ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

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

Updated on October 16, 2019
dwachira profile image

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

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.

Source

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;

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 method
Description
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

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

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 >>

Comments

    0 of 8192 characters used
    Post Comment
    • dwachira profile imageAUTHOR

      Danson Wachira 

      6 years ago from Nairobi, Kenya

      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.

    • SidKemp profile image

      Sid Kemp 

      6 years ago from Boca Raton, Florida (near Miami and Palm Beach)

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

    • dwachira profile imageAUTHOR

      Danson Wachira 

      6 years ago from Nairobi, Kenya

      Hi Ingenira,

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

    • Ingenira profile image

      Ingenira 

      6 years ago

      Useful instruction, dwachira. Thanks !

    • dwachira profile imageAUTHOR

      Danson Wachira 

      6 years ago from Nairobi, Kenya

      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.

    • dwachira profile imageAUTHOR

      Danson Wachira 

      6 years ago from Nairobi, Kenya

      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 profile image

      kidscrafts 

      6 years ago from Ottawa, Canada

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

    • billybuc profile image

      Bill Holland 

      6 years ago from Olympia, WA

      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.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
    ClickscoThis is a data management platform studying reader behavior (Privacy Policy)