JDBC Where Clause Demo

Click here to watch in Youtube : https://www.youtube.com/watch?v=rqjmHnLKXOQ

Click the below Image to Enlarge

JDBCWhereClauseDemo Project Dir Structure

JDBC Where Clause Demo

JDBC Where Clause Demo

JDBCWhereClauseDemo.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 JDBCWhereClauseDemo
{
    // 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)
    {

        JDBCWhereClauseDemo jdbcWhereClauseDemo = new JDBCWhereClauseDemo();

        Scanner scanner = new Scanner(System.in);
        System.out.print("Enter countryCodeValue :");
        while (scanner.hasNext())
        {
            String countryCodeValue = scanner.nextLine();
            if (countryCodeValue.equalsIgnoreCase("Exit"))
            {
                break;
            }
            jdbcWhereClauseDemo.getCityInformation(countryCodeValue);
            System.out.print("\nEnter countryCodeValue :");
        }

        scanner.close();

    }

    /*
     * Get City Information based on Country Code
     */

    private void getCityInformation(String countryCodeValue)
    {
        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 = "select * from city where countrycode=?";

            /*
             * Execute the query
             */
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, countryCodeValue);

            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("ID: " + id);
                System.out.print(", Name: " + name);
                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 countryCodeValue :IND
ID: 1024, Name: Mumbai (Bombay), CountryCode: IND, District: Maharashtra, Population: 10500000
ID: 1025, Name: Delhi, CountryCode: IND, District: Delhi, Population: 7206704
ID: 1026, Name: Calcutta [Kolkata], CountryCode: IND, District: West Bengali, Population: 4399819
ID: 1027, Name: Chennai (Madras), CountryCode: IND, District: Tamil Nadu, Population: 3841396
ID: 1028, Name: Hyderabad, CountryCode: IND, District: Andhra Pradesh, Population: 2964638
ID: 1029, Name: Ahmedabad, CountryCode: IND, District: Gujarat, Population: 2876710
ID: 1030, Name: Bangalore, CountryCode: IND, District: Karnataka, Population: 2660088

----

----

Enter countryCodeValue :AFG
ID: 1, Name: Kabul, CountryCode: AFG, District: Kabol, Population: 1780000
ID: 2, Name: Qandahar, CountryCode: AFG, District: Qandahar, Population: 237500
ID: 3, Name: Herat, CountryCode: AFG, District: Herat, Population: 186800
ID: 4, Name: Mazar-e-Sharif, CountryCode: AFG, District: Balkh, Population: 127800

Enter countryCodeValue :USA
ID: 3793, Name: New York, CountryCode: USA, District: New York, Population: 8008278
ID: 3794, Name: Los Angeles, CountryCode: USA, District: California, Population: 3694820
ID: 3795, Name: Chicago, CountryCode: USA, District: Illinois, Population: 2896016
ID: 3796, Name: Houston, CountryCode: USA, District: Texas, Population: 1953631
ID: 3797, Name: Philadelphia, CountryCode: USA, District: Pennsylvania, Population: 1517550
ID: 3798, Name: Phoenix, CountryCode: USA, District: Arizona, Population: 1321045
ID: 3799, Name: San Diego, CountryCode: USA, District: California, Population: 1223400
ID: 3800, Name: Dallas, CountryCode: USA, District: Texas, Population: 1188580
ID: 3801, Name: San Antonio, CountryCode: USA, District: Texas, Population: 1144646
ID: 3802, Name: Detroit, CountryCode: USA, District: Michigan, Population: 951270
---

---

---

Enter countryCodeValue :exit

Environment Used 

JDK version : 1.7.0_51
Mysql Server version : 5.6.19 

To Download JDBCWhereClauseDemoApp Project Click the below link

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

See also:

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial