JDBC Sorting Data Demo
VIDEO
Enjoying Java & Database Tutorials?
Subscribe to Ram N Java on YouTube for beginner-friendly coding lessons and practical demos!
🔔 Subscribe to Channel
How to Sort Database Records Using JDBC in Java
Organizing data in a meaningful order is essential when displaying records to users. While Java collections can sort data in memory, letting the database engine handle sorting using SQL's ORDER BY clause is far more efficient. Through JDBC (Java Database Connectivity) , you can easily execute queries that fetch sorted rows into a ResultSet.
The SQL ORDER BY Clause
To sort records, append the ORDER BY keyword followed by the target column name and sort direction:
SELECT id, first_name, last_name, age FROM students ORDER BY age ASC;
Use ASC for ascending order (default) or DESC for descending order.
Step-by-Step Java Implementation
Follow these standard steps to retrieve and display sorted records using JDBC:
Establish Connection: Connect to your target database schema using DriverManager.getConnection().Create Statement: Instantiate a Statement or PreparedStatement with your sorting query.Execute Query: Call executeQuery() to obtain the sorted ResultSet.Iterate Results: Use a while (resultSet.next()) loop to read and display the organized column data.Close Resources: Ensure the ResultSet, Statement, and Connection are closed using try-with-resources.
Watch More JDBC Tutorials
Click here to watch in Youtube : https://www.youtube.com/watch?v=Z0tSN2Bz_Ro Click the below Image to Enlarge
JDBCSortingDataDemo Project Dir Structure
JDBC Sorting Data Demo
JDBC Sorting Data Demo
JDBCSortingData.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class JDBCSortingData
{
// JDBC driver name and database URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver" ;
static final String DB_URL = "jdbc:mysql://localhost:3306/world" ;
// Database credentials
static final String USERNAME = "root" ;
static final String PASSWORD = "root" ;
public static void main (String[] args )
{
JDBCSortingData jdbcSortingData = new JDBCSortingData();
Scanner scanner = new Scanner(System. in);
System. out. print("Enter Asc or Desc Order :" );
while (scanner. hasNext())
{
String orderby = scanner. nextLine();
if (orderby. equalsIgnoreCase("Exit" ))
{
break ;
}
jdbcSortingData. getCityInformation(orderby);
System. out. print("Enter Asc or Desc Order :" );
}
scanner. close();
}
private void getCityInformation (String orderby )
{
Connection connection = null ;
PreparedStatement preparedStatement = null ;
try
{
/*
* Register the JDBC driver in DriverManager
*/
Class. forName(JDBC_DRIVER );
/*
* Establish connection to the Database using DriverManager
*/
connection = DriverManager
.getConnection(DB_URL , USERNAME , PASSWORD );
String sql;
if (orderby. equalsIgnoreCase("asc" ))
{
sql = "select * from city order by name asc" ;
}
else
{
sql = "select * from city order by name desc" ;
}
System. out. println("sql : " + sql);
/*
* Execute the query
*/
preparedStatement = connection. prepareStatement(sql);
ResultSet rs = preparedStatement. executeQuery();
while (rs. next())
{
int id = rs. getInt(1 );
String name = rs. getString(2 );
String countryCode = rs. getString(3 );
String district = rs. getString(4 );
int population = rs. getInt(5 );
/*
* Display values
*/
System. out. print("Name: " + name);
System. out. print(",ID: " + id);
System. out. print(", CountryCode: " + countryCode);
System. out. print(", District: " + district);
System. out. println(", Population: " + population);
}
rs. close();
}
catch (SQLException se)
{
/*
* Handle errors for JDBC
*/
se. printStackTrace();
}
catch (ClassNotFoundException e)
{
/*
* Handle errors for Class.forName
*/
e. printStackTrace();
}
catch (Exception e)
{
e. printStackTrace();
}
finally
{
/*
* finally block used to close resources
*/
try
{
if (preparedStatement != null )
{
preparedStatement. close();
}
}
catch (SQLException sqlException)
{
sqlException. printStackTrace();
}
try
{
if (connection != null )
{
connection. close();
}
}
catch (SQLException sqlException)
{
sqlException. printStackTrace();
}
}
}
}
Output
Enter Asc or Desc Order : asc
sql : select * from city order by name asc
Name: A Coruña (La Coruña),ID : 670 , CountryCode: ESP , District: Galicia, Population: 243402
Name: Aachen,ID : 3097 , CountryCode: DEU , District: Nordrhein- Westfalen, Population: 243825
Name: Aalborg,ID : 3318 , CountryCode: DNK , District: Nordjylland, Population: 161161
Name: Aba,ID : 2760 , CountryCode: NGA , District: Imo & Abia, Population: 298900
Name: Abadan,ID : 1404 , CountryCode: IRN , District: Khuzestan, Population: 206073
Name: Abaetetuba,ID : 395 , CountryCode: BRA , District: Pará, Population: 111258
Name: Abakan,ID : 3683 , CountryCode: RUS , District: Hakassia, Population: 169200
Name: Abbotsford,ID : 1849 , CountryCode: CAN , District: British Colombia, Population: 105403
Name: Abeokuta,ID : 2747 , CountryCode: NGA , District: Ogun, Population: 427400
Name: Aberdeen,ID : 478 , CountryCode: GBR , District: Scotland, Population: 213070
Name: Abha,ID : 3191 , CountryCode: SAU , District: Asir, Population: 112300
-- -
-- -
Enter Asc or Desc Order : desc
sql : select * from city order by name desc
Name: [San Cristóbal de] la Laguna,ID : 698 , CountryCode: ESP , District: Canary Islands, Population: 127945
Name: Zytomyr,ID : 3446 , CountryCode: UKR , District: Zytomyr, Population: 297000
Name: Zwolle,ID : 28 , CountryCode: NLD , District: Overijssel, Population: 105819
Name: Zwickau,ID : 3145 , CountryCode: DEU , District: Saksi, Population: 104146
Name: Zunyi,ID : 2025 , CountryCode: CHN , District: Guizhou, Population: 261862
Name: Zumpango,ID : 2669 , CountryCode: MEX , District: México, Population: 99781
Name: Zukovski,ID : 3756 , CountryCode: RUS , District: Moskova, Population: 96500
Name: Zonguldak,ID : 3404 , CountryCode: TUR , District: Zonguldak, Population: 111542
Name: Zoetermeer,ID : 26 , CountryCode: NLD , District: Zuid- Holland, Population: 110214
Name: Zlatoust,ID : 3671 , CountryCode: RUS , District: Tšeljabinsk, Population: 196900
Name: Zixing,ID : 2207 , CountryCode: CHN , District: Hunan, Population: 110048
-- -
-- -
Enter Asc or Desc Order : exit
No comments:
Post a Comment