Java – Order by clause example using JDBC Statement interface

In this article, we will use JDBC API to sort fetched records using order by clause from newly created table in MySQL database from Java application (i.e.; using Statement Interface)

Order-by clause :

  • Sorting can be performed on the selected records in either ways i.e.; ascending or descending
    1. ASC –> to arrange selected records in ascending order (this is default)
    2. DESC –> to arrange selected records in descending order

1. Pre-requisite :

  • Java JDK 1.8.0_77
  • MySQL database 5.5.16
  • Eclipse Luna IDE 4.4.0
  • mysql-connector-java-5.1.38.jar file

2. Database parameters :

Let us move on and code an example to connect MySQL database from Java application to sort selected records using order by clause either in ascending/descending order using JDBC API. But before that, we will list down required things to connect database

  • database server IP or address (localhost)
  • sever port (3306)
  • database name (PLAYER_INFO)
  • username (root)
  • password (root@123)

Note: All bold are database values to connect MySQL database

3. Order-By clause using JDBC Statement Interface :

  • As we are ready with required things to connect MySQL database from Java application
  • We can use one of the methods from Statement Interface to execute and arrange “selected records in ascending or descending order” SQL query
    1. execute(String sqlQuery);
    2. executeUpdate(String sqlQuery);
    3. executeQuery(String sqlQuery);
  • Let us code a simple example using Statement Interface

OrderByClauseUsingJDBCStatement.java

package in.bench.resources.mysql.db.clause;

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

public class OrderByClauseUsingJDBCStatement {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		// Step 1: Loading or registering MySQL JDBC driver class
		try {
			Class.forName("com.mysql.jdbc.Driver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in loading MySQL JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and get connection using DriverManager
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/PLAYER_INFO",
					"root",
					"root@123"); 

			// Step 2.B: Creating JDBC Statement
			statement = connection.createStatement();

			// to get all players with more than 130 matches in desc
			String sqlSelectQuery = "SELECT * FROM PLAYER"
					+ " WHERE MATCHES >= 130"
					+ " ORDER BY NAME DESC ";

			// Step 2.C: Executing SQL & retrieve data into ResultSet
			resultSet = statement.executeQuery(sqlSelectQuery);

			System.out.println("ID\tName\t\t\tAge\tMatches");
			System.out.println("==\t================\t===\t=======");

			// processing returned data and printing into console
			while(resultSet.next()) {
				System.out.println(resultSet.getInt(1) + "\t" +
						resultSet.getString(2) + "\t" +
						resultSet.getInt(3) + "\t" +
						resultSet.getInt(4));
			}
		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {
			// Step 3: Closing database connection
			try {
				if(null != connection) {
					// cleanup resources, once after processing
					statement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

ID	Name			Age	Matches
==	================	===	=======
11	Steve Waugh		50	168
2	Shane Warne		46	145
1	Sachin Tendulkar	43	200
8	Jacques Kallis	        40	166
5	Brian Lara		45	131

4. Download :

Related Articles :

References :

Happy Coding !!
Happy Learning !!

Java – Metadata of database using JDBC DatabaseMetaData interface
Java – Like clause example using JDBC Statement interface