JDBC Sorting Data Demo

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

Environment Used 

JDK version : 1.7.0_51
Mysql Server version : 5.6.19 

To Download JDBCSortingDataDemoApp Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCSortingDataDemoApp.zip?attredirects=0&d=1

See also:

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial