This chapter provides examples on how to select records from a table using JDBC application. This would add additional conditions using LIKE clause while selecting records from the table. Before executing the following example, make sure you have the following in place −

• To execute the following example you can replace the username and password with your actual user name and password.

• Your MySQL or whatever database you are using, is up and running.

Required Steps

The following steps are required to create a new Database using JDBC application −

• Import the packages − Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

• Open a connection − Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

• Execute a query − Requires using an object of type Statement for building and submitting an SQL statement to fetch records from a table which meet given condition. This Query makes use of LIKE clause to select records to select all the students whose first name starts with “za”.

• Clean up the environment − try with resources automatically closes the resources.

Example: Selecting Record from a Table

In this example, we”ve four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared and executed a query on table REGISTRATION by calling statement.executeQuery() method where all records are fetched and stored in a ResultSet object. ResultSet is iterated and all records are printed.

As next, we”ve prepared a SQL query with LIKE clause to get result where first name is having “za”. Query is executed using statement.executeQuery() method where relevant records are fetched and stored in a ResultSet object. ResultSet is iterated and records are printed

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Registration";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();) {		      
         System.out.println("Fetching records without condition...");
         ResultSet rs = stmt.executeQuery(QUERY);
         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }

         // Select all records having ID equal or greater than 101
         System.out.println("Fetching records with condition...");
         String sql = "SELECT id, first, last, age FROM Registration" +
            " WHERE first LIKE ''%za%''";
         rs = stmt.executeQuery(sql);

         while(rs.next()){
            //Display values
            System.out.print("ID: " + rs.getInt("id"));
            System.out.print(", Age: " + rs.getInt("age"));
            System.out.print(", First: " + rs.getString("first"));
            System.out.println(", Last: " + rs.getString("last"));
         }
         rs.close();
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:>javac JDBCExample.java
C:>

When you run JDBCExample, it produces the following result −

C:>java JDBCExample
Fetching records without condition...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
Fetching records with condition...
ID: 100, Age: 30, First: Zara, Last: Ali
ID: 102, Age: 30, First: Zaid, Last: Khan
C:>

Example: Selecting Record from a Table

In this example, we”ve three static strings containing a dababase connection url, username and password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared and executed a query on table STUDENTS by calling statement.executeQuery() method where records are fetched where last name starts with A and stored in a ResultSet object. ResultSet is iterated and all records are printed.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.*;

public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   static final String PASS = "guest123";

   public static void main(String args[]) {
      try{
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         String sel_qry = "select studentID, firstName, lastName from students where lastName like ''A%''";
         ResultSet rs = stmt.executeQuery(sel_qry);
         System.out.println("Displaying records where LastName begins with ''A''" );
         System.out.println("-------------------------------------------------------");
         while(rs.next()){
            System.out.print("studentID: " + rs.getInt(1));
            System.out.print(", FirstName: " + rs.getString(2));
            System.out.println(", LastName: " + rs.getString(3));
         }
         System.out.println("--------------------------------------------");
         rs.close();
         stmt.close();
         conn.close();
      }catch(SQLException e){
         e.printStackTrace();
      }
   }
}

Now let us compile the above example as follows −

C:>javac JDBCExample.java
C:>

Output

When you run JDBCExample, it produces the following result −

C:>java JDBCExample
Displaying records where LastName begins with ''A''
-------------------------------------------------------
studentID: 1000, FirstName: Bonny, LastName: Agarwal
studentID: 1004, FirstName: Mohammed, LastName: Ali
--------------------------------------------

C:>

Example: Selecting Record from a Table

In this example, we”ve three static strings containing a dababase connection url, username and password. Now using DriverManager.getConnection() method, we”ve prepared a database connection. Once connection is prepared, we”ve prepared a Statement object using createStatement() method. As next step, We”ve prepared and executed a query on table EMPLOYEES by calling statement.executeQuery() method where records are fetched where age begins with 5 and have only one literal after it and stored in a ResultSet object. ResultSet is iterated and all records are printed.

Copy and paste the following example in JDBCExample.java, compile and run as follows −

import java.sql.*;

// This class demonstrates use of LIKE with underscore ''_''
public class JDBCExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "root";
   static final String PASS = "guest123";

   public static void main(String args[]) {
      try{
         Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
         String sel_qry = "select * from employees where age like ''5_''";
         ResultSet rs = stmt.executeQuery(sel_qry);
         System.out.println("Displaying records where age begins with 5 and has only one literal after it." );
         System.out.println("-------------------------------------------------------");
         while(rs.next()){
            System.out.print(" ID: " + rs.getInt(1));
            System.out.print(", AGE: " + rs.getInt(2));
            System.out.print(", FirstName: " + rs.getString(3));
            System.out.println(", LastName: " + rs.getString(4));
         } 
         System.out.println("--------------------------------------------");
         rs.close();
         stmt.close();
         conn.close(); 
      }catch(SQLException e){
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:>javac JDBCExample.java
C:>

When you run JDBCExample, it produces the following result −

C:>java JDBCExample
Displaying records where age begins with 5 and has only one literal after it.
-------------------------------------------------------
 ID: 1, AGE: 50, FirstName: Shahbaz, LastName: Ali
 ID: 35, AGE: 51, FirstName: Raksha, LastName: Agarwal
--------------------------------------------


C:>