Saturday 30 August 2014

JDBC : JDBCRowSet Event Handling


















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

Click the below Image to Enlarge
JDBCRowSet Event Handling
JDBCRowSet Event Handling
JDBCRowSet Event Handling
See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC : JDBCRowSet Demo


















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

    Click the below Image to Enlarge
    JDBCRowSet 
    JDBCRowSet 
    JDBCRowSet 

    JDBCRowSetDemo.java
    import java.sql.SQLException;
    import java.util.Scanner;
    
    import javax.sql.rowset.JdbcRowSet;
    import javax.sql.rowset.RowSetFactory;
    import javax.sql.rowset.RowSetProvider;
    
    public class JDBCRowSetDemo
    {
        // 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 )
        {
            JDBCRowSetDemo jdbcRowSetDemo = new JDBCRowSetDemo();
            Scanner scanner = new Scanner(System.in);
            while( true )
            {
                System.out.print("Enter City Country Code :");
                String cityCountrycode = scanner.nextLine();
    
                if( cityCountrycode.equals("exit") )
                {
                    break;
                }
    
                jdbcRowSetDemo.getCityInformation(cityCountrycode);
    
            }
            scanner.close();
        }
    
        private void getCityInformation( String cityCountrycode )
        {
            JdbcRowSet jdbcRowSet = null;
            try
            {
                /*
                 * Using RowSetFactory create the JdbcRowSet object.
                 */
                RowSetFactory rowSetFactory = RowSetProvider.newFactory();
                jdbcRowSet = rowSetFactory.createJdbcRowSet();
    
                /*
                 * Set the JdbcRowSet properties [URL,username,password,command]
                 */
                jdbcRowSet.setUrl(DB_URL);
                jdbcRowSet.setUsername(USERNAME);
                jdbcRowSet.setPassword(PASSWORD);
    
                /*
                 * Sets the command property with a query that produces a ResultSet
                 * object containing all the data in the table
                 */
    
                jdbcRowSet.setCommand("select * from city where countrycode=?");
                jdbcRowSet.setString(1, cityCountrycode);
    
                /*
                 * The execute method does many things for you in the background:
                 * 
                 *  1.It makes a connection to the database using the values you assigned to the url,
                 *    username, and password properties.
                 *    
                 *  2.It executes the query you set in the command property.
                 *  
                 *  3.It reads the data from the resulting ResultSet object into the jdbcRs object.
                 *  
                 */         
                jdbcRowSet.execute();
    
                /*
                 *  Iterate the jdbcRowSet and get each row information
                 *  of city table.
                 */
                
                while( jdbcRowSet.next() )
                {
                    int id = jdbcRowSet.getInt(1);
                    String name = jdbcRowSet.getString(2);
                    String countryCode = jdbcRowSet.getString(3);
                    String district = jdbcRowSet.getString(4);
                    int population = jdbcRowSet.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);
                }
                
            }
            catch( SQLException se )
            {
                se.printStackTrace();
            }
            catch( Exception e )
            {
                e.printStackTrace();
            }   
            
            finally
            {
                if(jdbcRowSet!=null)
                {
                    try
                    {
                        jdbcRowSet.close();
                    }
                    catch( SQLException e )
                    {
                        e.printStackTrace();
                    }
                }
            }
    
        }
    }
    
    
    Output
    Enter City Country Code :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
    
    ---
    ---
    
    
    Enter City Country Code :pak
    ID: 2822, Name: Karachi, CountryCode: PAK, District: Sindh, Population: 9269265
    ID: 2823, Name: Lahore, CountryCode: PAK, District: Punjab, Population: 5063499
    ID: 2824, Name: Faisalabad, CountryCode: PAK, District: Punjab, Population: 1977246
    ID: 2825, Name: Rawalpindi, CountryCode: PAK, District: Punjab, Population: 1406214
    
    ---
    ---
    
    Enter City Country Code :exit
    
    
    Environment Used 

    JDK version : 1.7.0_51
    Mysql Server version : 5.6.19 

    To Download /JDBCRowSet_Read_DemoApp Project Click the below link

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

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • Wednesday 27 August 2014

    JDBCRowSet


















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

    Click the below Image to Enlarge
    JDBCRowSet
    JDBCRowSet
    JDBCRowSet
    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC Transaction Management Demo


















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

    Click the below Image to Enlarge

    JDBCTransactionManagementDemo Project Dir Structure
    JDBC Transaction Management Demo
    JDBC Transaction Management Demo

    JDBCTransactionManagementDemo.java
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    public class JDBCTransactionManagementDemo
    {
        // 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 )
        {
            JDBCTransactionManagementDemo jdbcTransactionManagementDemo =
                                                 new JDBCTransactionManagementDemo();
            jdbcTransactionManagementDemo.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(6000,'Bangalore','IND','Karnataka',400000)";
                String sql2 = "insert into city values(6001,'Channai','IND','TamilNadu',200000)";
                String sql3 = "insert into city values(6002,'Thiruvanathapuram','IND','Kerala',800000)";
    
                /*
                 * Execute the query
                 */
                statement = connection.createStatement();
    
                /*
                 * Execute each sql one by one
                 */
                statement.executeUpdate(sql1);
                System.out.println(sql1 + " : Sql1 is Successfully executed");
                statement.executeUpdate(sql2);
                System.out.println(sql2 + ": Sql2 is Successfully executed");
                statement.executeUpdate(sql3);
                System.out.println(sql3 + ": Sql3 is Successfully executed");
    
                /*
                 * If there is no error
                 */
                connection.commit();
    
                System.out
                        .println("Changes are committed and applied to the Database");
    
            }
            catch( SQLException se )
            {
                /*
                 * Handle errors for JDBC
                 */
                try
                {
                    /*
                     * If there is any error.
                     */
                    connection.rollback();
                    System.out
                            .println("Changes are rollbacked and not applied to the Database");
                }
                catch( SQLException e )
                {
                    e.printStackTrace();
                }
                se.printStackTrace();
            }
            catch( ClassNotFoundException e )
            {
                /*
                 * Handle errors for Class.forName
                 */
                e.printStackTrace();
            }
            catch( Exception e )
            {
                try
                {
                    /*
                     * If there is any error.
                     */
                    connection.rollback();
                    System.out
                            .println("Changes are rollbacked and not applied to the Database");
                }
                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
    insert into city values(6000,'Bangalore','IND','Karnataka',400000) : Sql1 is Successfully executed
    insert into city values(6001,'Channai','IND','TamilNadu',200000): Sql2 is Successfully executed
    insert into city values(6002,'Thiruvanathapuram','IND','Kerala',800000): Sql3 is Successfully executed
    Changes are committed and applied to the Database
    
    
    Environment Used 

    JDK version : 1.7.0_51
    Mysql Server version : 5.6.19 

    To Download JDBCTransactionManagementDemoApp Project Click the below link

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

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC Transaction Management


















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

    Click the below Image to Enlarge
    JDBC Transaction Management
    JDBC Transaction Management
    JDBC Transaction Management
    JDBC Transaction Management
    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • Saturday 23 August 2014

    JDBC : Retrieve File


















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

    Click the below Image to Enlarge
    JDBC : Retrieve File
    JDBC : Retrieve File
    JDBC : Retrieve File

    JDBC : Retrieve File
    JDBC : Retrieve File
    JDBC : Retrieve File

    JDBCRetriveFileDemo.java
    import java.io.FileWriter;
    import java.io.Reader;
    import java.sql.Clob;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class JDBCRetriveFileDemo
    {
        // 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 )
        {
            JDBCRetriveFileDemo jdbcRetriveFileDemo = new JDBCRetriveFileDemo();
            jdbcRetriveFileDemo.retriveFile();
        }
    
        private void retriveFile()
        {
            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_HISTORY";
    
                /*
                 * Execute the query
                 */
                preparedStatement = connection.prepareStatement(sql);
                ResultSet rs = preparedStatement.executeQuery();
    
                while( rs.next() )
                {
                    String name = rs.getString(2);
                    Clob cityHistoryClob = rs.getClob(3);
                    Reader reader = cityHistoryClob.getCharacterStream();
    
                    FileWriter fw = new FileWriter("D:/Downloads/" + name );
    
                    int i;
                    while( (i = reader.read()) != -1 )
                    {
                        fw.write((char) i);
                    }
                    System.out.println("D:/Downloads/" + name +" has saved");
                    fw.close();
                }
    
                rs.close();
    
            }
            catch( SQLException se )
            {
    
                se.printStackTrace();
            }
            catch( ClassNotFoundException e )
            {
                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
    D:/Downloads/Newyork History.txt has saved
    
    
    Environment Used 

    JDK version : 1.7.0_51
    Mysql Server version : 5.6.19 

    To Download JDBCRetriveFileDemoApp Project Click the below link

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

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC : Store File


















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

    Click the below Image to Enlarge
    JDBC : Store File
    JDBC : Store File
    JDBC : Store File
    JDBC : Store File
    JDBC : Store File
    JDBCStoreFileDemo.java
    import java.io.File;
    import java.io.FileReader;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCStoreFileDemo
    {
        // 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)
        {
            JDBCStoreFileDemo jdbcStoreFileDemo = new JDBCStoreFileDemo();
            jdbcStoreFileDemo.storeFile();
        }
    
        private void storeFile()
        {
            Connection connection = null;
            PreparedStatement preparedStatement = null;
            FileReader fileReader = 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 = "insert into CITY_HISTORY values(?,?,?);";
    
                preparedStatement = connection.prepareStatement(sql);
    
                File file = new File("D:/Downloads/Newyork History.txt");
                fileReader = new FileReader(file);
    
                preparedStatement.setInt(1, 1);
                preparedStatement.setString(2, file.getName());
                preparedStatement.setCharacterStream(3, fileReader,
                        (int) file.length());
    
                int numberOfRowsInserted = preparedStatement.executeUpdate();
    
                System.out
                        .println("numberOfRowsInserted : " + numberOfRowsInserted);
    
            }
            catch (SQLException se)
            {
    
                se.printStackTrace();
            }
            catch (ClassNotFoundException e)
            {
                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();
                }
    
                if (fileReader != null)
                {
                    try
                    {
                        fileReader.close();
                    }
                    catch (IOException e)
                    {
    
                        e.printStackTrace();
                    }
                }
            }
    
        }
    }
    
    
    Output
    numberOfRowsInserted : 1
    
    
    SQL
    CREATE TABLE `CITY_HISTORY` (  
    `ID` int(11) NOT NULL AUTO_INCREMENT,  
    `Name` char(35) NOT NULL DEFAULT '',  
    `History_Details` LONGTEXT  NULL ,   
    PRIMARY KEY (`ID`)) 
    ENGINE=InnoDB;
    
    
    Environment Used 

    JDK version : 1.7.0_51
    Mysql Server version : 5.6.19 

    To Download JDBCStoreFileDemoApp Project Click the below link

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

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC : TYPE_SCROLL_INSENSITIVE Vs. TYPE_SCROLL_SENSITIVE ResultSet


















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

    Click the below Image to Enlarge
    JDBC : TYPE_SCROLL_INSENSITIVE Vs. TYPE_SCROLL_SENSITIVE ResultSet
    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial
  • JDBC : Retrieve Image


















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

    Click the below Image to Enlarge
    JDBC : Retrieve Image
    JDBC : Retrieve Image
    JDBC : Retrieve Image
    JDBC : Retrieve Image
    JDBC : Retrieve Image
    JDBC : Retrieve Image
    JDBCRetriveImageDemo.java
    import java.io.FileOutputStream;
    import java.sql.Blob;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class JDBCRetriveImageDemo
    {
        // 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)
        {
            JDBCRetriveImageDemo jdbcRetriveImageDemo = new JDBCRetriveImageDemo();
            jdbcRetriveImageDemo.retriveImage();
        }
    
        private void retriveImage()
        {
            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_IMAGE";
    
                /*
                 * Execute the query
                 */
                preparedStatement = connection.prepareStatement(sql);
                ResultSet rs = preparedStatement.executeQuery();
    
                while (rs.next())
                {
                    String imageName = rs.getString(2);
                    Blob imageBlob = rs.getBlob(3);
                    FileOutputStream fileOutputStream = new FileOutputStream(
                            "D:/Downloads/" + imageName);
    
                    fileOutputStream.write(imageBlob.getBytes(1,
                            (int) imageBlob.length()));
                    
                    System.out.println(imageName + " has been stored in D:/Downloads/" );
                    
                    fileOutputStream.close();
                }
    
                rs.close();
    
            }
            catch (SQLException se)
            {
    
                se.printStackTrace();
            }
            catch (ClassNotFoundException e)
            {
                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
    New york.jpg has been stored in D:/Downloads/
    
    
    Environment Used 

    JDK version : 1.7.0_51
    Mysql Server version : 5.6.19 

    To Download JDBCRetrieveImageDemoApp Project Click the below link

    https://sites.google.com/site/javaee4321/jdbc/JDBCRetrieve%20ImageDemoApp.zip?attredirects=0&d=1

    See also:

  • Servlets Tutorial
  • All Design Patterns Links
  • JDBC Tutorial