Friday 25 July 2014

JDBC Like Clause Demo


















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

Click the below Image to Enlarge

JDBCLikeClauseDemo Project Dir Structure
JDBC Like Clause Demo
JDBC Like Clause Demo

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

        JDBCLikeClauseDemo jdbcLikeClauseDemo = new JDBCLikeClauseDemo();

        Scanner scanner = new Scanner(System.in);
        System.out.print("Enter city name Starting letters :");
        while( scanner.hasNext() )
        {
            String value = scanner.nextLine();
            if( value.equalsIgnoreCase("Exit") )
            {
                break;
            }
            jdbcLikeClauseDemo.getCityInformation(value);
            System.out.print("\nEnter city name Starting letters :");
        }

        scanner.close();

    }

    private void getCityInformation( String value )
    {
        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 name LIKE '"+value+"%'";
            
            System.out.println("\nsql : "+sql + "\n");
            
            /*
             * 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 city name Starting letters :ab

sql : select * from city WHERE name LIKE 'ab%'

Name: Abu Dhabi,ID: 65, CountryCode: ARE, District: Abu Dhabi, Population: 398695
Name: Abaetetuba,ID: 395, CountryCode: BRA, District: Pará, Population: 111258
Name: Aberdeen,ID: 478, CountryCode: GBR, District: Scotland, Population: 213070
Name: Abohar,ID: 1309, CountryCode: IND, District: Punjab, Population: 107163
Name: Abadan,ID: 1404, CountryCode: IRN, District: Khuzestan, Population: 206073
Name: Abiko,ID: 1703, CountryCode: JPN, District: Chiba, Population: 126670
Name: Abbotsford,ID: 1849, CountryCode: CAN, District: British Colombia, Population: 105403
Name: Abeokuta,ID: 2747, CountryCode: NGA, District: Ogun, Population: 427400
Name: Abuja,ID: 2754, CountryCode: NGA, District: Federal Capital Dist, Population: 350100

--
--
--

Enter city name Starting letters :ba

sql : select * from city WHERE name LIKE 'ba%'

Name: Batna,ID: 39, CountryCode: DZA, District: Batna, Population: 183377
Name: Béjaïa,ID: 45, CountryCode: DZA, District: Béjaïa, Population: 117162
Name: Béchar,ID: 49, CountryCode: DZA, District: Béchar, Population: 107311
Name: Bahía Blanca,ID: 96, CountryCode: ARG, District: Buenos Aires, Population: 239810
Name: Baku,ID: 144, CountryCode: AZE, District: Baki, Population: 1787800
Name: Barisal,ID: 157, CountryCode: BGD, District: Barisal, Population: 170232
Name: Banja Luka,ID: 202, CountryCode: BIH, District: Republika Srpska, Population: 143079
Name: Bauru,ID: 263, CountryCode: BRA, District: São Paulo, Population: 313670
Name: Barueri,ID: 305, CountryCode: BRA, District: São Paulo, Population: 208426

---
---

Enter city name Starting letters :ban

sql : select * from city WHERE name LIKE 'ban%'

Name: Banja Luka,ID: 202, CountryCode: BIH, District: Republika Srpska, Population: 143079
Name: Bandar Seri Begawan,ID: 538, CountryCode: BRN, District: Brunei and Muara, Population: 21484
Name: Bani Suwayf,ID: 627, CountryCode: EGY, District: Bani Suwayf, Population: 172032
Name: Banha,ID: 632, CountryCode: EGY, District: al-Qalyubiya, Population: 145792
Name: Banjul,ID: 904, CountryCode: GMB, District: Banjul, Population: 42326
Name: Bandung,ID: 941, CountryCode: IDN, District: West Java, Population: 2429000
Name: Bandar Lampung,ID: 948, CountryCode: IDN, District: Lampung, Population: 680332

--
--
--

Enter city name Starting letters :exit

Environment Used 

JDK version : 1.7.0_51
Mysql Server version : 5.6.19 

To Download JDBCLikeClauseDemoApp Project Click the below link

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

See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • No comments:

    Post a Comment