JDBC - Select Records

Previous
Next

This chapter provides examples on how to select/ fetch records from a table using JDBC application. 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 select (i.e. fetch ) records from a table.

  • Extract Data − Once SQL query is executed, you can fetch records from the table.

  • 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 executed a query on table REGISTRATION by calling statement.executeQuery() method and we've stored the result in ResultSet. ResultSet is iterated and all records are printed.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

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();
         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"));
         }
      } 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
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:\>

Example: Selecting Records from Multiple Tables

In the last section, we saw how a SELECT works from a single table. Here, we will see how to do a SELECT from multiple tables using INNER JOIN. The SELECT Query is:

SELECT students.studentID, students.FirstName, students.LastName from students INNER JOIN sampledb4 where sampledb4.id = students.studentID

The keyword INNER JOIN is optional. So, the above statement is equivalent to:

SELECT students.studentID, students.FirstName, students.LastName from students, sampledb4 where sampledb4.id = students.studentID

SampleDb4 table details

Sample DB 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 executed a query on table students and sampledb4 by calling statement.executeQuery() method and we've stored the result in ResultSet to find students whose id is present in sampledb4 table as well. ResultSet is iterated and all records are printed.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

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

import java.sql.*;

// This class demonstrates use of INNER JOIN with 2 tables in 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();

         // Example of INNER JOIN
         String query1 = "select students.studentID, students.FirstName, students.LastName"
            +" from students, sampledb4 where sampledb4.id = students.studentID";

         ResultSet rs = stmt.executeQuery(query1);

         while(rs.next()){
            System.out.print(" Student ID: " + rs.getInt(1));
            System.out.print(" FirstName: " + rs.getString(2));
            System.out.println(" LastName: " + rs.getString(3));
         }

         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
Student ID: 1005 FirstName: Kishore LastName: Kumar
Student ID: 1006 FirstName: Ganesh LastName: Khan

C:\>

Example: Selecting Records from a Table with Order By

SELECT statement where the results can be ordered by a particular column in an ascending (using ASC keyword) or descending (using DESC keyword). Check the sample SQL below:

select id, age, first, last from employees order by age asc;

In this example, we've three static strings containing a dababase connection url, username, 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 executed a query on table employees by calling statement.executeQuery() method and we've stored the result in ResultSet. ResultSet is iterated and all records are printed.

In case of any exception while connecting to the database, a catch block handled SQLException and printed the stack trace.

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

import java.sql.*;

// This class demonstrates use of ORDER BY clause of the SELECT statement in SQL
public class SelectOrderBy {

   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();
         // Example of ORDER BY
         String query1 = "select id, age, first, last from employees order by age asc;";
         ResultSet rs = stmt.executeQuery(query1);
         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));
         }
         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
 ID: 1 AGE: 18 FirstName: Zara LastName: Ali
 ID: 3 AGE: 20 FirstName: Zaid LastName: Khan
 ID: 7 AGE: 20 FirstName: Rita LastName: Tez
 ID: 8 AGE: 20 FirstName: Sita LastName: Singh
 ID: 2 AGE: 25 FirstName: Mahnaz LastName: Fatma
 ID: 4 AGE: 28 FirstName: Sumit LastName: Mittal
 ID: 21 AGE: 35 FirstName: Jeevan LastName: Rao
 ID: 25 AGE: 35 FirstName: Jeevan LastName: Rao
 ID: 26 AGE: 35 FirstName: Aditya LastName: Chaube
 ID: 22 AGE: 40 FirstName: Aditya LastName: Chaube
 ID: 34 AGE: 45 FirstName: Ahmed LastName: Ali
 ID: 35 AGE: 50 FirstName: Raksha LastName: Agarwal


C:\>
Print Page
Previous
Next
Advertisements