Wednesday 30 July 2014

JDBC Batch Processing [Statement] Demo


















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

Click the below Image to Enlarge

JDBCBatchProcessDemo Project Dir Structure
JDBC Batch Processing [Statement] Demo

JDBC Batch Processing [Statement] Demo

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

public class JDBCBatchProcess
{
    // 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)
    {
        JDBCBatchProcess JDBCBatchProcess = new JDBCBatchProcess();
        JDBCBatchProcess.addCityInformation();
    }

    private void addCityInformation()
    {
        Connection connection = null;
        Statement statement = 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);

            /*
             * Set auto-commit to false
             */
            connection.setAutoCommit(false);

            String sql1 = "insert into city values(5000,'Bangalore','IND','Karnataka',400000)";
            String sql2 = "insert into city values(5001,'Channai','IND','TamilNadu',200000)";
            String sql3 = "insert into city values(5002,'Thiruvanathapuram','IND','Kerala',800000)";

            /*
             * Execute the query
             */
            statement = connection.createStatement();

            /*
             * Add sql's to the batch
             */
            statement.addBatch(sql1);
            statement.addBatch(sql2);
            statement.addBatch(sql3);

            int result[] = statement.executeBatch();

            /*
             * Explicitly commit statements to apply changes
             */
            connection.commit();

            for (int i = 0; i < result.length; i++)
            {
                System.out.println(result[i]);
            }

        }
        catch (SQLException se)
        {
            /*
             * Handle errors for JDBC
             */
            try
            {
                connection.rollback();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
            se.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            /*
             * Handle errors for Class.forName
             */
            e.printStackTrace();
        }
        catch (Exception e)
        {
            try
            {
                connection.rollback();
            }
            catch (SQLException e1)
            {

                e1.printStackTrace();
            }
            e.printStackTrace();
        }
        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if (statement != null)
                {
                    statement.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
            try
            {
                if (connection != null)
                {
                    connection.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
        }

    }
}

Output
1
1
1

Environment Used 

JDK version : 1.7.0_51
Mysql Server version : 5.6.19 

To Download JDBCBatchProcessDemo_Statement_App Project Click the below link

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

See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC Batch Processing - Introduction


















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

    Click the below Image to Enlarge
    JDBC Batch Processing - Introduction
    JDBC Batch Processing - Introduction
    JDBC Batch Processing - Introduction
    JDBC Batch Processing - Introduction
    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • Monday 28 July 2014

    JDBC Drop Table Demo


















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

    Click the below Image to Enlarge

    JDBCDropTableDemo Project Dir Structure
    JDBC Drop Table Demo
    JDBC Drop Table Demo
    JDBCDropTable.java
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCDropTable
    {
        // 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 )    
        {
    
            JDBCDropTable jdbcDropTable = new JDBCDropTable();
            jdbcDropTable.dropTable();
    
        }
    
        private void dropTable()
        {
            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 = "DROP TABLE REGISTRATION";
    
                /*
                 * Execute the query
                 */
                preparedStatement = connection.prepareStatement(sql);
    
                int returnValue = preparedStatement.executeUpdate();
    
                System.out.println("returnValue : " + returnValue);
    
                System.out.println("Table 'REGISTRATION' has been dropped");
    
            }
            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
    returnValue : 0
    Table 'REGISTRATION' has been dropped
    
    
    Environment Used 

    JDK version : 1.7.0_51
    Mysql Server version : 5.6.19 

    To Download JDBCDropTableDemoApp Project Click the below link

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

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC Create Table Demo


















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

    Click the below Image to Enlarge

    JDBCCreateTableDemo Project Dir Structure
    JDBC Create Table Demo
    JDBC Create Table Demo
    JDBCCreateTable.java
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCCreateTable
    {
        // 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 )
        {
    
            JDBCCreateTable jdbcCreateTable = new JDBCCreateTable();
            jdbcCreateTable.createTable();
    
        }
    
        private void createTable()
        {
            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 = "CREATE TABLE REGISTRATION " + "(id INT not NULL, "
                        + " first VARCHAR(255), " + " last VARCHAR(255), "
                        + " age INT, " + " PRIMARY KEY ( id ))";
    
                System.out.println(sql);
    
                /*
                 * Execute the query
                 */
                preparedStatement = connection.prepareStatement(sql);
    
                int returnValue = preparedStatement.executeUpdate();
    
                System.out.println("returnValue : " + returnValue);
    
                System.out.println("Table REGISTRATION has been created");
    
            }
            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
    CREATE TABLE REGISTRATION (id INT not NULL,  first VARCHAR(255),  last VARCHAR(255),  age INT,  PRIMARY KEY ( id ))
    returnValue : 0
    Table REGISTRATION has been created
    

    Environment Used 

    JDK version :1.7.0_51
    Tomcat version : 7.0.50 

    To Download JDBCCreateTableDemoApp Project Click the below link

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

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC Drop Database Demo


















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

    Click the below Image to Enlarge

    JDBCDropDatabaseDemo Project Dir Structure
    JDBC Drop Database Demo
    JDBC Drop Database Demo

    JDBCDropDatabase.java
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCDropDatabase
    {
        // JDBC driver name and database URL
        static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        static final String DB_URL      = "jdbc:mysql://localhost:3306/";
    
        // Database credentials
        static final String USERNAME    = "root";
        static final String PASSWORD    = "root";
    
        public static void main( String[] args )
        {
    
            JDBCDropDatabase jdbcDropDatabase = new JDBCDropDatabase();
            jdbcDropDatabase.dropDatabase();
    
        }
    
        private void dropDatabase()
        {
            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 = "DROP DATABASE STUDENTS";
    
                /*
                 * Execute the query
                 */
                preparedStatement = connection.prepareStatement(sql);
                            
                int returnValue = preparedStatement.executeUpdate();
    
                System.out.println("returnValue : " + returnValue);
                
                System.out.println("STUDENTS database has been Droped");
    
            }
            catch( SQLException se )
            {
                /*
                 * Handle errors for JDBC
                 */
                se.printStackTrace();
            }
            catch( ClassNotFoundException e )
            {
                /*
                 * Handle errors for Class.forName
                 */
                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
    returnValue : 0
    STUDENTS database has been Droped
    
    
    Environment Used 

    JDK version : 1.7.0_51
    Mysql Server version : 5.6.19 

    To Download JDBCDropDatabaseDemoApp Project Click the below link

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

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • Saturday 26 July 2014

    JDBC Create Database Demo


















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

    Click the below Image to Enlarge

    JDBCCreateDatabaseDemo Project Dir Structure
    JDBC Create Database Demo
    JDBC Create Database Demo
    JDBCCreateDatabase.java
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCCreateDatabase
    {
        // JDBC driver name and database URL
        static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
        static final String DB_URL = "jdbc:mysql://localhost:3306/";
    
        // Database credentials
        static final String USERNAME = "root";
        static final String PASSWORD = "root";
    
        public static void main(String[] args)
        {
    
            JDBCCreateDatabase jdbcCreateDatabase = new JDBCCreateDatabase();
            jdbcCreateDatabase.createDatabase();
    
        }
    
        private void createDatabase()
        {
            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 = "CREATE DATABASE STUDENTS";
    
                /*
                 * Execute the query
                 */
                preparedStatement = connection.prepareStatement(sql);
    
                int returnValue = preparedStatement.executeUpdate();
    
                System.out.println("returnValue : " + returnValue);
    
            }
            catch (SQLException se)
            {
                /*
                 * Handle errors for JDBC
                 */
                se.printStackTrace();
            }
            catch (ClassNotFoundException e)
            {
                /*
                 * Handle errors for Class.forName
                 */
                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
    returnValue : 1
    

    Environment Used 

    JDK version : 1.7.0_51
    Mysql Server version : 5.6.19 

    To Download JDBCCreateDatabaseDemoApp Project Click the below link

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

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • Friday 25 July 2014

    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