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
....
....
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)
.jpg)