The Artima Developer Community
Sponsored Link

Java Buzz Forum
JDBC - CallableStatement with Input and Output Parameters(Mysql)

0 replies on 1 page.

Welcome Guest
  Sign In

Go back to the topic listing  Back to Topic List Click to reply to this topic  Reply to this Topic Click to search messages in this forum  Search Forum Click for a threaded view of the topic  Threaded View   
Previous Topic   Next Topic
Flat View: This topic has 0 replies on 1 page
Ram N

Posts: 2777
Nickname: ramram
Registered: Jul, 2014

Ram N is Java Programmer
JDBC - CallableStatement with Input and Output Parameters(Mysql) Posted: May 8, 2015 11:47 PM
Reply to this message Reply

This post originated from an RSS feed registered with Java Buzz by Ram N.
Original Post: JDBC - CallableStatement with Input and Output Parameters(Mysql)
Feed Title: JAVA EE
Feed URL: http://ramj2ee.blogspot.com/feeds/posts/default?alt=rss
Feed Description: This blog has viedo tutorials and Sample codes related to below Technologies. 1.J2EE 2.Java 3.Spring 4.Hibernate 5.Database 6.Oracle 7.Mysql 8.Design Patterns
Latest Java Buzz Posts
Latest Java Buzz Posts by Ram N
Latest Posts From JAVA EE

Advertisement
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)
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
  • Read: JDBC - CallableStatement with Input and Output Parameters(Mysql)

    Topic: JDBC Create Table example Previous Topic   Next Topic Topic: 17 JavaScript tools breathing new life into old code

    Sponsored Links



    Google
      Web Artima.com   

    Copyright © 1996-2019 Artima, Inc. All Rights Reserved. - Privacy Policy - Terms of Use