Java and DBMS - Elementary JDBC & CRUD operations (NetBeans 8.1.0)

Java and DBMS are different environments, having their own protocol, data formats etc.

So, it is not possible to access data stored in a DBMS directly from Java.

We need a bridge - an interface to establish communication between the two. 

This interface basically should be able to - 

-  Connect to the DBMS and open a session with the database

-  Access data in the database through SQL statements

-  Retrieve records and store them in the Java environment for manipulation

-  Trap run time errors if any.

Java provides such an interface called as JDBC (Java Database Connectivity).

To start with, JDBC connectivity provide the following ,

1. JDBC Connection - Connection to DBMS and a session with the Database.

2. JDBC Statement  - Embedded SQL statements to access data stored in Database.

3. JDBC Result set  - Storage of records retrieved from DB in the Java environment for manipulation .
4. JDBC Exceptions  -Capturing run time errors 

 There is no need to write code for the above,

 resources can be imported into the Java program using import statement.
We will see this through examples.

Focus is on only JDBC fundamentals and not on program logic or content of database.

Software used is  NetBeans IDE 8.0.2. 

For simplicity,

database is created in JavaDB which is part of JDK package.

Database ClientDB and table ClientEmailList are created through the wizard.

Programs also created using Netbeans IDE 8.0.2.

Same code holds good for other DBMS with the corresponding driver program.

Now to the programs of CRUD (Creation,Retrieval,Update and Deletion of a Record).

Database Name : ClientDB

Table Name        :       ClientContact

Table Attributes  :         ClientId             N   

                                        ClientName  A/N  

                                        ClientEmail  A/N  

Note : ClientId is unique and do not allow duplicates.
No other checks were made.

If you are working on NetBeans, make sure - 
1. START the server
      NetBeans => Services => DataBases => Java DB (Rt Click) => Start Server
2. CONNECT the database
    NetBeans => Services => DataBases =>  "jdbc:javaderby...your-database.."  (Rt Click) => Connect
3. Add JAR (Java ARchive to the Library
    |NetBeans => Prsojects => "your project" (expand) => Libraries (Rt Click) =>Add JAR/Folder (Click) => open "derbyclient"

Example A. (Creation Of Record)

import java.sql.Connection;//  connection objects
import java.sql.DriverManager; // driver and Connection to DB
import java.sql.PreparedStatement;//  statement Objects for SQL with parameters
import java.sql.SQLException; // error trapping
import java.util.Scanner; // for user input, not connected to JDBC/DB

public class RecordCreation{

    public static void main(String[] args) {
       
        /* Variable host contain Protocol,Driver, Database location,port Number and name in that order in the URL format */
          String host = "jdbc:derby://localhost:1527/ClientDB"
      /* UserName and UserPassword  have Database credentials -
         This depends on what one gave while creating the DB */
        String UserName = "parthiban";
        String UserPassword = "password";
        Scanner Accept = new Scanner(System.in); //  Accept object for user Input
        Connection con;// Connection object con
        PreparedStatement ps;// Prepared Statement object ps
        String sql;   // String variable sql to hold SQL statements 
             
       try {
            /* getconnection method of DriverManager establish the connection with
 host, UserName, userPassword as parameters */
            con = DriverManager.getConnection(host,UserName,UserPassword);
         System.out.println(" Connected..");// Message if connection is successful
 /* INSERT sql statement for CREATION of record, with place holders ?,?,? */
 sql ="INSERT INTO  CLIENTEMAILLIST (ClientId,ClientName,ClientEmail)"
                  + "values(?,?,?)";
              /*statement object 'ps' is created by prepareStaement method 
                of connection object con */
            ps = con.prepareStatement(sql);
 /* Following code accepts user input for Client Id, Name and Email */ 
            System.out.println("Enter Client Code (Numeric): ");
             int WclientId = Accept.nextInt(); // nextInt() method for integer input 
             Accept.nextLine(); // Dummy to consume ENTER key stroke of the above 
                                                and  avoid skipping  NAME input                                                             
              System.out.println("Enter Client Name : ");
             String WclientName = Accept.nextLine(); // nextLine() method for string
              System.out.println("Enter Client EMail : ");
              String WclientEmail = Accept.nextLine();
              /* Settler method of  ps  to assign input values 
              to place holders using respective column index */
              ps.setInt(1,WclientId);      //setInt() for integer column 
              ps.setString(2,WclientName); // setString() for varchar column
              ps.setString(3,WclientEmail);
              /* executeUpadate method of statement object executes the statement */
            ps.executeUpdate();
             System.out.println("Record Created in the database"); // Completion Message
     
        }
        catch (SQLException err){
            System.out.println("ERROR:"+err.getMessage());// Error Message, if anything fails
        }
     
     
    }
    }

Example B (Retrieval Of Record):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement; //Simple/Static Statement object to send query. 
                           //prepared statement not required for static query (no parameters)
import java.sql.ResultSet; //Object to store retrieved data from DBMS 
                                             in Java environment

public class RecordRetrieval{

    public static void main(String[] args) {
        String host = "jdbc:derby://localhost:1527/ClientDB";
        String UserName = "parthiban";
        String UserPassword = "password";
        Connection con;
        String sql;
        ResultSet rs; // rs as result set object to store retrieved data  ;
        Statement ss;// 'static' statement object
       
        try {
       
            con = DriverManager.getConnection(host,UserName,UserPassword);                  
            System.out.println(" Connected..");
           //Required SQL query for RETRIEVAL of records 
            sql ="SELECT * FROM CLIENTEMAILLIST "; 
          //createStatement method of con to create statement object 
            ss = con.createStatement();
         //executeQuery method  of ss execute the statement result  assigned to  rs 
            rs = ss.executeQuery(sql);

             while (rs.next()){
            /* getInt or getString method of rs to get the value of the column with 
                column index as parameter */
            int WClientId = rs.getInt(1);
            String WClientName = rs.getString(2);
            /*Instead of  column index, 
              Column Name (ClientEMail)  can also be used as parameter */
            String WClientEmail = rs.getString("ClientEMail");
            System.out.println("CLIENT ID: "+ WClientId +  " , NAME : " + WClientName + " , EMAIL : " + WClientEmail);
                       }
        }
        catch (SQLException err){
            System.out.println("ERROR:"+err.getMessage());// Error Message, if anything fails
        }
                 
        }
     
    }


Example C (Update Of Record):

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner; 

public class UpdateOfRecord {

   

    public static void main(String[] args) {

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

    String UserName = "parthiban";

    String UserPassword = "password";

    Scanner Accept = new Scanner(System.in); // Accept object for user input

    Connection con;

    PreparedStatement ps;// prepared statement object ps

    ResultSet rs; // result set object rs to display  record detail

    String sql;   // String variable sql to hold SQL statements

    int WclientId;

    String WclientName, WclientEmail;// Work variables

    

       try {

            con = DriverManager.getConnection(host,UserName,UserPassword);                     

            System.out.println(" Connected..");             

     
/* SQL statement for display of the record, that is to be UPDATED with  placeholder for ClientId */
sql ="SELECT Clientname,clientemail from  CLIENTEMAILLIST where CLIENTID = ? ";
       /* prepareStatement method of connection object con to create ps */ 

               ps = con.prepareStatement(sql);
    System.out.println("Enter Client Id (Numeric): ");
           WclientId = Accept.nextInt(); // nextInt() method for integer input
           Accept.nextLine();// Dummy to avoid skipping next input
/* Settler method of ps to set input value as ClientId in the place holder */

              ps.setInt(1, WclientId);
/* executeQuery method of ps and result is assigned to rs */

              rs=ps.executeQuery(); 

               rs.next();   // move cursor to first record in the rs
/ * get method of rs to assign values to work variables from rs */ 

               WclientName = rs.getString(1); //  column index as in sql 

               WclientEmail = rs.getString(2); 
/* display the existing detail of the record */

               System.out.println("Client Id : " + WclientId + ", Name : "

                       + WclientName + ", Current EMail : " + WclientEmail);

               System.out.println("Enter New Email :"); // prompt for new email

               WclientEmail = Accept.nextLine(); // user input
/* SQL  statement with placeholders for ClintEmail and ClientId  to UPDATE the record*/

 sql ="UPDATE CLIENTEMAILLIST SET ClientEmail = ?  where ClientId = ? ";

            ps = con.prepareStatement(sql);
/* Settler method of ps to assign values to the placeholders in SQL string
    using the column index */

            ps.setInt(2, WclientId);

            ps.setString(1,WclientEmail);

              /* executeUpadate method of statement object */                    

            ps.executeUpdate();

         

        }

        catch (SQLException err){

            System.out.println("ERROR:"+err.getMessage());

        }

    }

    

}

Example D (Deletion  Of Record):

import java.sql.Connection;

import java.sql.DriverManager;

//import java.sql.Statement;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Scanner; //

public class DeletionOfRecord {

  

    public static void main(String[] args) {

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

    String UserName = "parthiban";

    String UserPassword = "password";

    Scanner Accept = new Scanner(System.in); // Accept object for user input

    Connection con;

    PreparedStatement ps;

    ResultSet rs; // result set rs to display record

    String sql;   // String variable sql to hold SQL statements

     int WclientId;

     String WclientName, WclientEmail; // work variables

      try {

         

            con = DriverManager.getConnection(host,UserName,UserPassword);                     

            System.out.println(" Connected..");                        

       

        System.out.println("Enter Client Id of the record to be Deleted (Numeric): ");
           WclientId = Accept.nextInt(); // nextInt() method for integer input

              ps.setInt(1, WclientId); // settler method of ps to assign value to

                                                   // ClientId placeholder using column index

   

          /*SQL select statement with placeholder for ClientId to DISPLAY record detail*/

           sql ="SELECT Clientname,clientemail from  CLIENTEMAILLIST where CLIENTID = ? ";  

              ps = con.prepareStatement(sql); // prepared statement with sql as parameter

              rs=ps.executeQuery(); // executequery method of ps to get resultset 

               rs.next();   // move cursor to first record in the result set
/ * get method of rs to assign values to work variables from rs  using column index*/        

               WclientName = rs.getString(1);

               WclientEmail = rs.getString(2);
/*Display record detail */

               System.out.println("Client Id : " + WclientId + ", Name : "

                       + WclientName + ", Old EMail : " + WclientEmail);

/* SQL delete statement with placeholder for ClientID to DELETE that particular record */                           

            sql ="DELETE FROM CLIENTEMAILLIST where ClientId = ? ";

             ps = con.prepareStatement(sql);
/* Settler method of ps to assign values to the placeholder in SQL string
    using the column index */

           ps.setInt(1,WclientId);
 /* executeUpadate method of statement object is used to execute the statement */                    

            ps.executeUpdate();

            System.out.println("Above record is deleted from the database"); 

                }

        catch (SQLException err){

            System.out.println("ERROR:"+err.getMessage());

        }

    }

    

}

By adding While loop, one can improve the functionality of the programs.
One can also combine all the programs into one by making each
CRUD operation into a method.