How to Sort Contents of a Table in JDBC?

Last Updated : 08 Dec, 2020

Sorting the contents of a table means rearranging the records in an organized way to make data more usable. You can sort all the records of a table by choosing a column within a table according to which data has to be sorted. In simple words, when you sort data, you arrange the data in a logical order.

Text and Numbers both can be sorted in any order ie ascending or descending.
One or more columns can be sorted simultaneously.
In SQL, the ORDER BY clause is used to sort the contents of a table in JDBC.
By default, some databases sort the query in ascending order. But we can sort in descending order also.
We can use the ORDER BY clause with one or more columns simultaneously.

Steps to follow:

Import the necessary libraries
Register the Driver Class
Connect to your database by providing address, username, and password
Create your statement
Write your query
Execute your query and store the result in a resultset
Display the result

How ORDER BY works:
       
   query ----->       select * from Table_Name ORDER BY Column1,Column 2; 
   
   user table ----->  --------------------------------------------------
                 ||     id      ||      Name     ||     Age      ||
                 ||      1      ||      Prateek  ||     20       ||
                 ||      4      ||      Chhavi   ||     21       ||
                 ||      3      ||      Aman     ||     22       ||
                 ||      2      ||      Kartikay ||     22       ||
                 ||      5      ||      Prakhar  ||    20        ||
                 --------------------------------------------------
   
   Example 1:         select * from user ORDER BY id;
  
   Output:           --------------------------------------------------
                 ||     id      ||      Name     ||     Age      ||
                 ||      1      ||      Prateek  ||     20       ||
                 ||      2      ||      Kartikay ||     22       ||
                 ||      3      ||      Aman     ||     22       ||
                 ||      4      ||      Chhavi   ||     21       ||
                 ||      5      ||      Prakhar  ||     20       ||
                 --------------------------------------------------

   Example 2:         select * from user ORDER BY name,age;
  
   Output:           --------------------------------------------------
                 ||     id      ||      Name     ||     Age      ||
                 ||      3      ||      Aman     ||     22       ||
                 ||      4      ||      Chhavi   ||     21       ||
                 ||      2      ||      Kartikay ||     22       ||     
                 ||      5      ||      Prakhar  ||     20       ||
                 ||      1      ||      Prateek  ||     20       ||
                 --------------------------------------------------

Example 1:

Java

// Java program to sort contents of a table 
  
import java.sql.*;
  
public class GFG {
  
    // driver code
    public static void main(String[] args) throws Exception
    {
  
        // Register Driver Class
        Class.forName("org.apache.derby.jdbc.ClientDriver");
  
        // Connection to your database, pass all the
        // necessary parameters such as address , username &
        // password
        Connection con = DriverManager.getConnection();
  
        // Create Statement
        Statement stmt = con.createStatement();
  
        // Query to be executed
        String query = "Select * from users ORDER by id";
  
        // Execute SQL query and store the result in any
        // variable
        ResultSet rs = stmt.executeQuery(query);
  
        System.out.println("Id    Name    Age");
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            System.out.println(id + "  " + name + "   "
                               + age);
        }
  
        // close the connection
        con.close();
    }
}

Output

sort by id

Example 2:

Java

// Java program to sort contents of a table 
  
import java.sql.*;
  
public class GFG {
  
    // driver code
    public static void main(String[] args) throws Exception
    {
  
        // Register Driver Class
        Class.forName("org.apache.derby.jdbc.ClientDriver");
  
        // Connection to your database, pass all the
        // necessary parameters such as address , username &
        // password
        Connection con = DriverManager.getConnection();
  
        // Create Statement
        Statement stmt = con.createStatement();
  
        // Query to be executed
        String query
            = "Select * from users ORDER by name,age";
  
        // Execute SQL query and store the result in any
        // variable
        ResultSet rs = stmt.executeQuery(query);
  
        System.out.println("Id    Name    Age");
        while (rs.next()) {
            int id = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            System.out.println(id + "  " + name + "   "
                               + age);
        }
  
        // close the connection
        con.close
    }
}

Output

sort by name

Note: We can’t use Prepared Statement to sort the contents of the table. Prepared statement issues an SQL statement together with bound variables so it cannot be used for columns or table names.

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

Related Articles

Java

Java