Skip to content
Related Articles

Related Articles

Improve Article

Java Program to Search the Contents of a Table in JDBC

  • Last Updated : 13 Jul, 2021

In order to deal with JDBC standard 7 steps are supposed to be followed:

  1. Import the database
  2. Load and register drivers
  3. Create a connection
  4. Create a statement
  5. Execute the query
  6. Process the results
  7. Close the connection

Procedure:

  1. Creating a database irrespective of SQL or NoSQL. Creating a database using sqlyog and creating some tables in it and fill data inside it in order to search for the contents of a table. For example, the database is named as “hotelman” and table names are “cuslogin” and “adminlogin”.
  2. Create a connection: Open any IDE where the java executable file can be generated following the standard methods. Creating a package further creating the class. Inside the package, open a new java file and type the below code for JDBC connectivity and save the filename with connection.java.
  3. Searching content in a table, let’s suppose my “cuslogin” table has columns namely “id”, “name”, “email”, “password” and we want to search the customer whose id is 1.
  4. Initialize a string with the SQL query as follows
String sql="select * from cuslogin where id=1";

If we want to search for any id in general, then the SQL query becomes

String sql="select * from cuslogin where id="+Integer.parseInt(textfield.getText());

The textfield is the area(in Jframe form) where the user types the id he wants to search in the “cuslogin” table.

4.1: Initialize the below objects of Connection class, PreparedStatement class, and ResultSet class(needed for JDBC) and connect with the database as follows 



Connection con = null;
PreparedStatement p = null;
ResultSet rs = null;
con = connection.connectDB();

4.2: Now, add the SQL query of step 3.1 inside prepareStatement and execute it as follows:

p =con.prepareStatement(sql);
rs =p.executeQuery();

4.3: We check if rs.next() is not null, then we display the details of that particular customer present in “cuslogin” table

4.4: Open a new java file (here, its result.java) inside the same package and type the full code (shown below) for searching the details of the customer whose id is 1, from table “cuslogin”.

Note: both the file viz result.java and connection.java should be inside the same package, else the program won’t give desired output!!

Implementation: 

Connection class of JDBC by making an object to be invoked in main(App) java program below in 1B

Java




// Java program to search the contents of
// a table in JDBC Connection class for JDBC
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class connectionDB {
 
    final String DB_URL
 
    //  Database credentials
 
    // name
    final String USER = "root";
    // Password to fetch database
    final String PASS = "Imei@123";
 
    public Connection connectDB()
    {
        // Initially setting NULL
        // to connection class object
        Connection con = null;
 
        // Try block to check exceptions
        try {
 
            // Loading DB(SQL) drivers
            Class.forName("com.mysql.cj.jdbc.Driver");
 
            // Registering SQL drivers
            con = DriverManager.getConnection(DB_URL, USER,
                                              PASS);
        }
 
        // Catch block to handle database exceptions
        catch (SQLException e) {
 
            // Print the line number where exception occurs
            e.printStackTrace();
        }
 
        // Catch block to handle exception
        // if class not found
        catch (ClassNotFoundException e) {
 
            // Function prints the line number
            // where exception occurs
            e.printStackTrace();
        }
 
        // Returning Connection class object to
        // be used in (App/Main) GFG class
        return con;
    }
}

 
App/Main Class where the program is compiled and run calling the above connection class object 

Java




// Java program to search the
// contents of a table in JDBC
 
// Main Java program (App Class) of JDBC
 
// Step 1: Importing database files
 
// Importing SQL libraries
import java.sql.*;
 
// Main class-GFG,
// it's Connection class shown above
public class GFG {
 
    // Main driver method
    public static void main(String[] args)
    {
        // Step 2: Establishing a connection
        connectionDB connection = new connectionDB();
 
        // Assigning NULL to object of Connection class
        // as shown returned by above program
        Connection con = null;
        PreparedStatement p = null;
        ResultSet rs = null;
 
        // Step 3: Load and registered drivers
        // Loaded and registered in Connection class
        // shown in above program
        con = connection.connectDB();
 
        // Try block to check exceptions
        try {
 
            // Step 4: Write a statement
            String sql
                = "select * from cuslogin where id=1";
 
            // Step 5: Execute the query
            p = con.prepareStatement(sql);
            rs = p.executeQuery();
 
            // Step 6: Process the results
            System.out.println(
                "id\t\tname\t\temail\t\tpassword");
 
            // Condition check
            if (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String password = rs.getString("password");
                System.out.println(id + "\t\t" + name
                                   + "\t\t" + email + "\t\t"
                                   + password);
            }
        }
 
        // Catch block to handle exceptions
        catch (SQLException e) {
 
            // Print the exception
            System.out.println(e);
        }
    }
}

Output: Based on the values stored inside the “cuslogin” table. 

Attention reader! Don’t stop learning now. Get hold of all the important Java Foundation and Collections concepts with the Fundamentals of Java and Java Collections Course at a student-friendly price and become industry ready. To complete your preparation from learning a language to DS Algo and many more,  please refer Complete Interview Preparation Course.




My Personal Notes arrow_drop_up
Recommended Articles
Page :