DBMS and Java - CRUD using Instance Methods

   This program uses Methods to execute each CRUD operation.   We have two types of methods, Class method and Object/Instance method.   Class methods are static because they are binded during compilation process,   whereas Object/Instance methods are dynamic and they are binded during   run time, when objects/instances are created.

  Question is, which one to use?   That depends on purpose of the methods.   Here, DBMS manipulation is dynamic, it involves read/write situations   in a multi user environment.   So, Object/Instance methods are preferred over Class methods    to avoid complications that may arise.

   Anyway,   Class/Static methods (like 'Main')  have their own significance in Java.   Thumb rule is unless one is very sure, static methods are not preferred.   Moreover, Java is OOP language, so defining a class(Template),   creating an object/instance from it and working on it is default.

   Another problem as far as this program is concerned is,   'Main' being static, 

      public static void main(String[] args)

we can call only Static methods.    This constraint is overcome by creating an object of the class and   using its Instance to call Object/Instance Methods.

Now to the menu driven program of CRUD  with methods for CRUD operations.
Software used is  NetBeans IDE 8.0.2. 
Note : ClientId is unique and do not allow duplicates.

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.

Database Name : ClientDB

Table Name        :       ClientContact

Table Attributes  :         ClientId             N   

                                        ClientName  A/N  

                                        ClientEmail  A/N  

No other checks were made.

Program  -

import java.sql.Connection;//  connection object

import java.sql.DriverManager; // Driver and Connection to DB
import java.sql.Statement; //Simple Statement object to send query.
import java.sql.PreparedStatement;//  Statement Objects for SQL with parameters
import java.sql.ResultSet;
import java.sql.SQLException; // Error trapping
import java.util.Scanner; // for user input, not connected to JDBC/DB

public class CRUDmenu {
String host = "jdbc:derby://localhost:1527/ClientDB";
String UserName = "parthiban";
String UserPassword = "password";
String sql;
Connection con;
ResultSet rs;
PreparedStatement ps;// Prepared Statement object for SQL with parameters
Statement ss; // Simple Statement object for query
Scanner AcceptInput = new Scanner(System.in);
int WClientId; // Work Variables
String WClientName, WClientEmail; // ''

    public static void main(String[] args) {
        // Cmo is created to enable non static method calling from static Main
       CRUDmenu Cmo = new CRUDmenu();
       String OptionInput;
        // ConnectMethod is called using Cmo instance
        Cmo.ConnectMethod(); // Calling Connection Method
        do { // do while is used to loop till user Exits
             // Calling OptionMethod for user option
              OptionInput =  Cmo.OptionMethod();
            // Switch control is used to call a Method based on user input
         switch (OptionInput) {
            case "1":
                Cmo.CreateRecord();
                break;
            case "2":
                Cmo.RetrieveRecord();
                break;
            case "3":              
                Cmo.UpdateRecord();
                break;
            case "4":            
               Cmo.DeleteRecord();
                break;
            case "0":
         // For input 0, program is terminated
                 System.out.println("Ending Session");
                System.exit(0);
                break;
            default:
                System.out.println("Invalid selection - Reenter");
                break;
         } // end switch
        // Note, how string value is checked for not = "0"
        }  while (!OptionInput.equals("0"));// end while    
    }// end main

//* Connection Method */

    public void ConnectMethod(){
         try{
         con = DriverManager.getConnection(host,UserName,UserPassword);                  
            System.out.println(" Connected..");// Message if connection is successful
            }
        catch (SQLException err){
            System.out.println("ERROR:"+err.getMessage());// Error Message, if anything fails
        }
    }

/* Option Method */

    public String OptionMethod(){
        String OptionInput;
        System.out.println( " ** Select an Operation **");
        System.out.println( " Creation of Record  - 1");
        System.out.println( " Retrieval of Record - 2");
        System.out.println( " Update of Record    - 3");
        System.out.println( " Deletion of Record  - 4");
        System.out.println( " Exit                - 0");
        System.out.println( " ENTER OPTION - ");
        OptionInput = AcceptInput.next();
        return OptionInput; // User input is returned
    }

/* Creation Method */ 

    public void CreateRecord(){
        System.out.println("RECORD CREATION");
         WClientId  = 1;    
       try {
            /* INSERT sql statement with place holders ?,?,? */
            sql ="INSERT INTO  CLIENTEMAILLIST (ClientId,ClientName,ClientEmail)"
                  + "values(?,?,?)";
              /* preparedstatemnt object 'ps' is created through 'prepareStaement' method of connection object */    
             ps = con.prepareStatement(sql);
            /* Following code accepts user input for Client Id, Name and Email */
           while (WClientId != 0) {
             System.out.println("Enter Client Code (0- Back to Menu): ");
             WClientId = AcceptInput.nextInt(); // nextInt() method for integer input
             AcceptInput.nextLine(); // Dummy to avoid skipping NAME input
             
             if (WClientId != 0){
                 System.out.println("Enter Client Name : ");
           
             WClientName = AcceptInput.nextLine(); // nextLine() method for string value
              System.out.println("Enter Client EMail : ");
             WClientEmail = AcceptInput.nextLine();
              /* Settler method of statement object is used to assign actual values
                 with respective column position in the sql statement */
              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 is used to execute the statement */
            ps.executeUpdate();
             System.out.println("Record Created in the database");
             }// if end
             } // while end
               }
        catch (SQLException err){
            System.out.println("ERROR:"+err.getMessage());// Error Message, if anything fails
        }// try end
    }

/* Retrieval Method */

    public void RetrieveRecord(){
        System.out.println("RECORD RETRIEVAL");
        try {
           /* 'createStatement' method of connection object to create statement object */
            ss = con.createStatement();
              /* Required SQL statement is assigned to sql string */
            sql ="SELECT * FROM CLIENTEMAILLIST ";
            /* executeQuery method of statement object to execute the statement
             and the result is assigned to Result Set object rs */
            rs = ss.executeQuery(sql);
         
            while (rs.next()){
            /* getInt or getString method of Result Set 'rs' is used accordingly
               to get the value of the column */
            WClientId = rs.getInt(1); // Column position  in the 'sql' is used as parameter
            WClientName = rs.getString(2);
            WClientEmail = rs.getString("ClientEMail"); // Column Name can be used as parameter
            System.out.println("CLIENT ID: "+ WClientId +  " , NAME : " + WClientName + " , EMAIL : " + WClientEmail);
                       }
        }
        catch (SQLException err){
            System.out.println("ERROR:"+err.getMessage());// Error Message, if anything fails
        }
    }

/* Update Method */

    public void UpdateRecord(){
       System.out.println("RECORD UPDATE");
        WClientId = 1;
       try {
             while (WClientId != 0) {
           System.out.println("Enter Client Id (0 to EXIT): ");
              WClientId = AcceptInput.nextInt(); // nextInt() method for integer input
               AcceptInput.nextLine();// Dummy to avoid skipping next input
               if (WClientId !=0){
               sql ="SELECT Clientname,clientemail from  CLIENTEMAILLIST where CLIENTID = ? ";
               ps = con.prepareStatement(sql);
              ps.setInt(1, WClientId);
              rs=ps.executeQuery(); // executeQuery method of ps to get resultset
               rs.next(); // move cursor to first record in the result set
               WClientName = rs.getString(1);
               WClientEmail = rs.getString(2);
               System.out.println("Client Id : " + WClientId + ", Name : "
                       + WClientName + ", Current EMail : " + WClientEmail);
               System.out.println("Enter New Email or press ENTER for no chnage):");
               WClientEmail = AcceptInput.nextLine();// New Email
            sql ="UPDATE CLIENTEMAILLIST SET ClientEmail = ?  where ClientId = ? ";
            ps = con.prepareStatement(sql);
            ps.setInt(2, WClientId);
            ps.setString(1,WClientEmail);
              /* executeUpadate method of statement object */                  
            ps.executeUpdate();
               } // if end
             } // while end
        }
        catch (SQLException err){
            System.out.println("ERROR:"+err.getMessage());
        }
    }

/* Deletion Method /*
     
    public void DeleteRecord(){
        System.out.println("RECORD DELETION");
        WClientId= 1;
     try {
         System.out.println("Enter Client Id of the record to Delete ('0' - Back to Menu ): ");
              WClientId = AcceptInput.nextInt(); // nextInt() method for integer input
          /*SQL select statement with placeholder for ClientId to display detail*/
           sql ="SELECT Clientname,clientemail from  CLIENTEMAILLIST where CLIENTID = ? ";
              ps = con.prepareStatement(sql); // prepared statement with sql as parameter
              ps.setInt(1, WClientId); // settler method of ps to assign value to
                                                    // ClientId placeholder
              rs=ps.executeQuery(); // executequery method of ps to get resultset
               rs.next();   // move cursor to first record in the result set
        
               WClientName = rs.getString(1);
               WClientEmail = rs.getString(2);
               System.out.println("Client Id : " + WClientId + ", Name : "
                       + WClientName + ", Old EMail : " + WClientEmail);
         /* SQL statement to delete existing record */
            sql ="DELETE FROM CLIENTEMAILLIST where ClientId = ? ";
            ps = con.prepareStatement(sql);
            ps.setInt(1,WClientId);            
            ps.executeUpdate();
            System.out.println("Above record is deleted from the database"); // Message after completion 
                }
        catch (SQLException err){
            System.out.println("ERROR:"+err.getMessage());
    } // menu
}

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.