JAVA EE: August 2014

JDBC : JDBCRowSet Demo

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

Click the below Image to Enlarge

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:

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

See also:

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

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:

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

See also:

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

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:

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

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:

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:

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

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: