JAVA EE: Mysql

Showing posts with label Mysql. Show all posts

Friday, 15 May 2015

JDBC - CallableStatement Multiple In Out Parameters (Mysql)

Click here to watch in Youtube : https://www.youtube.com/watch?v=gOX3kvMKrRs&list=UUhwKlOVR041tngjerWxVccw&index=4

Click the below Image to Enlarge

JDBC - CallableStatement Multiple In Out Parameters (Mysql)

Create Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `world`.`getCityInfo` $$
CREATE PROCEDURE `world`.`getCityInfo` 
   (IN CITY_ID_INPUT INT,
    IN CITY_POPULATION_INPUT INT,
    OUT CITY_NAME_OUT VARCHAR(255),
    OUT CITY_COUNTRY_CODE_OUT VARCHAR(255),
    OUT CITY_DISTRICT_OUT  VARCHAR(255),
    OUT CITY_POPULATION_OUT INT(11))
BEGIN
   SELECT Name,CountryCode,District,Population INTO CITY_NAME_OUT,CITY_COUNTRY_CODE_OUT,CITY_DISTRICT_OUT, 
   CITY_POPULATION_OUT
   FROM city
   WHERE ID = CITY_ID_INPUT AND Population > CITY_POPULATION_INPUT;
END $$

DELIMITER ;

Call Stored Procedure

set @CITY_ID_INPUT=1;
set @CITY_POPULATION_INPUT=1000;
call getCityInfo(@CITY_ID_INPUT,@CITY_POPULATION_INPUT,@CITY_NAME_OUT,@CITY_COUNTRY_CODE_OUT,
     @CITY_DISTRICT_OUT,@CITY_POPULATION_OUT);
select @CITY_NAME_OUT,@CITY_COUNTRY_CODE_OUT,@CITY_DISTRICT_OUT,@CITY_POPULATION_OUT;

JDBCCallableStatementDemo.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCCallableStatementDemo
{
    // 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 )
    {
        JDBCCallableStatementDemo jdbcCallableStatementDemo = new JDBCCallableStatementDemo();

        Scanner scanner = new Scanner(System.in);
        while( true )
        {
            System.out.print("Enter City Id :");
            int cityId = scanner.nextInt();
            System.out.print("Enter City population :");
            int population = scanner.nextInt();

            if( cityId == 0 && population == 0)
            {
                break;
            }

            jdbcCallableStatementDemo.getCityInfo(cityId,population);

        }
        scanner.close();
    }

    private void getCityInfo( int cityId , int population )
    {
        Connection connection = null;
        CallableStatement callableStatement = 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 plSql = "{call getCityInfo (?,?,?,?,?,?)}";

            callableStatement = connection.prepareCall(plSql);

            /*
             * Bind IN parameter first, then bind OUT parameters
             */

            callableStatement.setInt(1, cityId);
            callableStatement.setInt(2, population);

            /*           
             * Register OUT Parameters            
             */
            callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(4, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(5, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(6, java.sql.Types.INTEGER);

            /*
             * Use execute method to run the stored procedure.
             */
            callableStatement.execute();

            /*
             * Retrieve cityName,countryCode,district and cityPopulation with getXXX method
             */
            String cityName = callableStatement.getString(3);
            String countryCode = callableStatement.getString(4);
            String district = callableStatement.getString(5);
            int cityPopulation = callableStatement.getInt(6);

            System.out.println("city Name : " + cityName+", countryCode : " +countryCode 
                    +", district : "+district+", population : "+cityPopulation);

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

    }
}

Output

Enter City Id :1
Enter City population :1000
city Name : Kabul, countryCode : AFG, district : Kabol, population : 1780000
Enter City Id :0
Enter City population :0

To Download JDBCCallableStatementDemoMultipleInOutParamsMysqlApp Project Click the below link

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

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JDBC - CallableStatement (Mysql)

Click here to watch in Youtube : https://www.youtube.com/watch?v=iC7J6EFhjZQ&list=UUhwKlOVR041tngjerWxVccw&index=5

Click the below Image to Enlarge

JDBC - CallableStatement (Mysql)

Create Stored Procedure

USE `world`;
DROP procedure IF EXISTS `getAllCities`;

DELIMITER $$
USE `world`$$
CREATE PROCEDURE `getAllCities` ()
BEGIN
  select * from city;
END$$

DELIMITER ;

Call Stored Procedure

call getAllCities();

JDBCCallableStatementDemo.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCCallableStatementDemo
{
    // 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)
    {
        JDBCCallableStatementDemo jdbcCallableStatementDemo = new JDBCCallableStatementDemo();

        jdbcCallableStatementDemo.getAllCitiesInfo();

    }

    private void getAllCitiesInfo()
    {
        Connection connection = null;
        CallableStatement callableStatement = 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 plSql = "{call getAllCities()}";

            /*
             * Creates a CallableStatement object for calling database stored
             * procedures. The CallableStatement object provides methods for
             * setting up its IN and OUT parameters, and methods for executing
             * the call to a stored procedure.
             */

            callableStatement = connection.prepareCall(plSql);
            
            /*
             * Use execute method to run the stored procedure.
             */
            ResultSet rs = callableStatement.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);
            }

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

    }
}

Output

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
ID: 5, Name: Amsterdam, CountryCode: NLD, District: Noord-Holland, Population: 50000
ID: 6, Name: Rotterdam, CountryCode: NLD, District: Zuid-Holland, Population: 4000
ID: 7, Name: Haag, CountryCode: NLD, District: Zuid-Holland, Population: 440900
ID: 8, Name: Utrecht, CountryCode: NLD, District: Utrecht, Population: 234323
....
....

To Download JDBCCallableStatementDemoMysqlApp Project Click the below link

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

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

JDBC - CallableStatement with Input Parameter(Mysql)

Click here to watch in Youtube : https://www.youtube.com/watch?v=PN6UVKjACac&list=UUhwKlOVR041tngjerWxVccw&index=6

Click the below Image to Enlarge

JDBC - CallableStatement with Input Parameter(Mysql)

Create Stored Procedure

DELIMITER $$
 
CREATE PROCEDURE world.getCitiesBasedOnCountryCode (IN CITY_COUNTRY_CODE_INPUT CHAR(35))

BEGIN
  
  SELECT * from city where CountryCode=CITY_COUNTRY_CODE_INPUT;      
 
END$$
 
DELIMITER ;

Call Stored Procedure

CALL `getCitiesBasedOnCountryCode`('IND');

CALL `getCitiesBasedOnCountryCode`('PAK');

JDBCCallableStatementDemo.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCCallableStatementDemo
{
    // 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)
    {
        JDBCCallableStatementDemo jdbcCallableStatementDemo = new JDBCCallableStatementDemo();

        Scanner scanner = new Scanner(System.in);
        while (true)
        {
            System.out.print("Enter City Country Code :");
            String cityCountrycode = scanner.nextLine();

            if (cityCountrycode.equals("exit"))
            {
                break;
            }

            jdbcCallableStatementDemo.getCitiesInfo(cityCountrycode);

        }
        scanner.close();
    }

    private void getCitiesInfo(String cityCountrycode)
    {
        Connection connection = null;
        CallableStatement callableStatement = 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 plSql = "{call getCitiesBasedOnCountryCode(?)}";

            /*
             * Creates a CallableStatement object for calling database stored
             * procedures. The CallableStatement object provides methods for
             * setting up its IN and OUT parameters, and methods for executing
             * the call to a stored procedure.
             */

            callableStatement = connection.prepareCall(plSql);

            /*
             * Bind IN parameter first, then bind OUT parameters
             */

            callableStatement.setString(1, cityCountrycode);

            /*
             * Use execute method to run the stored procedure.
             */
            ResultSet rs = callableStatement.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);
            }

        }
        catch (SQLException se)
        {
            se.printStackTrace();
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            /*
             * finally block used to close resources
             */
            try
            {
                if (callableStatement != null)
                {
                    callableStatement.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.printStackTrace();
            }
            try
            {
                if (connection != null)
                {
                    connection.close();
                }
            }
            catch (SQLException sqlException)
            {
                sqlException.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
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
ID: 1031, Name: Kanpur, CountryCode: IND, District: Uttar Pradesh, Population: 1874409
....
....
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
ID: 2826, Name: Multan, CountryCode: PAK, District: Punjab, Population: 1182441
ID: 2827, Name: Hyderabad, CountryCode: PAK, District: Sindh, Population: 1151274
ID: 2828, Name: Gujranwala, CountryCode: PAK, District: Punjab, Population: 1124749
....
....

To Download JDBCCallableStatementDemoinputparam-mysql-App Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCCallableStatementDemoinputparam-mysql-App.zip?attredirects=0&d=1

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

Friday, 8 May 2015

JDBC - CallableStatement with Input and Output Parameters(Mysql)

Click here to watch in Youtube : https://www.youtube.com/watch?v=jrTKujzil-w&index=1&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge

JDBC - CallableStatement with Input and Output Parameters(Mysql)

Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `world`.`getCityName` $$
CREATE PROCEDURE `world`.`getCityName` 
   (IN CITY_ID INT, OUT CITY_NAME VARCHAR(255))
BEGIN
   SELECT Name INTO CITY_NAME
   FROM city
   WHERE ID = CITY_ID;
END $$

DELIMITER ;

------------------------------------------------------------

set @CITY_ID=1;
call getCityName(@CITY_ID,@CITY_NAME);
select @CITY_NAME;

JDBCCallableStatementDemo.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCCallableStatementDemo
{
    // 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)
    {
        JDBCCallableStatementDemo jdbcCallableStatementDemo = new JDBCCallableStatementDemo();

        Scanner scanner = new Scanner(System.in);
        while (true)
        {
            System.out.print("Enter City Id :");
            int cityId = scanner.nextInt();

            if (cityId == 0)
            {
                break;
            }

            jdbcCallableStatementDemo.getCityName(cityId);

        }
        scanner.close();
    }

    private void getCityName(int cityId)
    {
        Connection connection = null;
        CallableStatement callableStatement = 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 plSql = "{call getCityName (?, ?)}";

            /*
             * Creates a CallableStatement object for calling database stored
             * procedures. The CallableStatement object provides methods for
             * setting up its IN and OUT parameters, and methods for executing
             * the call to a stored procedure.
             */

            callableStatement = connection.prepareCall(plSql);

            /*
             * Bind IN parameter first, then bind OUT parameter
             */

            callableStatement.setInt(1, cityId);

            /*
             * Second parameter is OUT so register it
             */
            callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);

            /*
             * Use execute method to run stored procedure.
             */
            callableStatement.execute();

            /*
             * Retrieve cityName with getXXX method
             */
            String cityName = callableStatement.getString(2);

            System.out.println("city Name : " + cityName);

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

    }
}

Output

Enter City Id :1
city Name : Kabul
Enter City Id :5000
city Name : Bangalore
Enter City Id :5001
city Name : Channai
Enter City Id :0

To Download JDBCCallableStatementDemoINOUT_Mysql Project Click the below link

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

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

All JAVA EE Links

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Java Collection Framework Tutorial

Mysql - Stored Procedure with Input and Output Parameters

Click here to watch in Youtube : https://www.youtube.com/watch?v=9gueIEwUHnA&index=2&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge

Mysql - Stored Procedure with Input and Output Parameters

DELIMITER $$

DROP PROCEDURE IF EXISTS `world`.`getCityName` $$
CREATE PROCEDURE `world`.`getCityName` 
   (IN CITY_ID INT, OUT CITY_NAME VARCHAR(255))
BEGIN
   SELECT Name INTO CITY_NAME
   FROM city
   WHERE ID = CITY_ID;
END $$

DELIMITER ;

----------------------------------------------------------

set @CITY_ID=3;
call getCityName(@CITY_ID,@CITY_NAME);
select @CITY_NAME;

Mysql - Stored Procedure with multiple Input Parameters

Click here to watch in Youtube : https://www.youtube.com/watch?v=UFrKfcCch8w&index=3&list=UUhwKlOVR041tngjerWxVccw

Click the below Image to Enlarge

Mysql - Stored Procedure with multiple Input Parameters

DELIMITER $$

CREATE PROCEDURE `getCityInfo` (IN POPULATION_INPUT INT,IN COUNTRY_CODE_INPUT VARCHAR(255))
BEGIN

select Name,District from city where Population = POPULATION_INPUT and
Countrycode = COUNTRY_CODE_INPUT;

END

CALL `world`.`getCityInfo`(4000, 'NLD');

Friday, 15 May 2015

JDBC - CallableStatement Multiple In Out Parameters (Mysql)

JDBC - CallableStatement (Mysql)

JDBC - CallableStatement with Input Parameter(Mysql)

Friday, 8 May 2015

JDBC - CallableStatement with Input and Output Parameters(Mysql)

Mysql - Stored Procedure with Input and Output Parameters

Mysql - Stored Procedure with multiple Input Parameters

Tutorials