Tuesday 6 August 2013

Top Most JDBC Callable Statement Example with MySQL


Step 1) First get mysql-connector-java-5.0.5.jar from jar finder website and put this on your class path. If you are using eclipse then configure this jar in your project as :
Right Click On Your Project-> Build Path->Configure Build Path->Libraries Tab-->Add External Jar choose your jar location and then jar and then Click on Open Then  Click on Open


Step 2) Create a Procedure as follows for fetching data from Employee table as below blog in mysql. Procedure should be like below. Make sure inserts some data in Employee table

DELIMITER $$

DROP PROCEDURE IF EXISTS `getEmpName` $$
CREATE PROCEDURE `getEmpName`(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN

SELECT name INTO EMP_FIRST
   FROM Employee
   WHERE ID = EMP_ID;

END $$

DELIMITER ;


Step 3) Call Procedure From Java Code as Follows

package test.myapp.jdbc;

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

//In This call we use only interfaces to refere objects. JDBC Objects class exist in mysql jar file 
public class CallableStatementExample {
public static void main(String[] args) {
// jdbc:mysql is protocol by which i can request for connect with
// database and 3306 is port number where my server can listen request
String dbUrl = "jdbc:mysql://localhost:3306/testdb";// testdb is
// database in mysql
Connection conn = null;
CallableStatement stmt = null;// this is CallableStatement reference
// variable
try {
// STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");

// STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(dbUrl, "root", "root");

// STEP 4: Execute a query
System.out.println("Creating statement...");
String sql = "{call getEmpName (?, ?)}";
stmt = conn.prepareCall(sql);

// Bind IN parameter first, then bind OUT parameter
int empID = 1;
stmt.setInt(1, empID); // This would set ID as 102
// Because second parameter is OUT so register it
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);

// Use execute method to run stored procedure.
System.out.println("Executing stored procedure...");
stmt.execute();

// Retrieve employee name with getXXX method
String empName = stmt.getString(2);
System.out.println("Emp Name with ID:" + empID + " is " + empName);
stmt.close();
conn.close();
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (stmt != null)
stmt.close();
} catch (SQLException se2) {
}// nothing we can do
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}// end finally try
}// end try
System.out.println("Goodbye!");
}// end main
}

LinkWithin

Related Posts Plugin for WordPress, Blogger...

Labels

Core Java programming core java interview question Core Java Faq's Servlets coding database jsp-servlet spring Java linux unix interview questions java investment bank Web Services Interview investment bank mysql Senior java developer interviews best practices java collection tutorial RMI SQL Eclipse FIX protocol tutorial tibco J2EE groovy java questions SCJP grails java 5 tutorial jdbc beginner error and exception Design Patterns Java Programming Tutorials fundamentals general object oriented programming xml Java Programs Hibernate Examples Flex JAMon Java xml tutorial logging Jsp Struts 2.0 Sybase and SQL Server debugging java interviews performance FIX Protocol interview questions JUnit testing WebSphere date and time tutorial experienced java IO tutorial java concurrency thread Ejb Freshers Papers IT Management Java Exapmle Java Script SQL and database tutorial examples Scwcd ant tutorials concurrency example and tutorial future state homework java changes java threading tricky Agile Business of IT Development JSTL Java JSON tutorial Java multithreading Tutorials PM Scrum data structure and algorithm java puzzles java tips testing tips windows 8 5 way to create Singleton Object Architect Interview Questions and Answers Architecture Architecure Bluetooth server as swing application that searches bluetooth device in 10 meter circle and show all devices. You can send file to any bluetooth device. C Programming CIO Callable Statement in Java Circular dependency of Objects in Java Comparable Example in Collection Custom annotation in Java Developer Interview Divide and rule example in java Drupal Example of Singleton Pattern FIX protocol ForkJoin Example in Java 7 Get data from dynamic table with Java Script Git HTML and JavaScript Health Hello World TCP Client Server Networking Program Hibernate Basics Hibernate Interview Question Answer J2EE Interview Question And Answers J2ME GUI Program JEE Interview QA JMS interview question Java J2EE Hibernate Spring Struts Interview Question Java System Property Java Threads Manager Portlets Provident Fund Read data from any file in same location and give the required result. Reading Properties File in Java Redpoint Rest WebService Client Rest Webservice Test SAL join with ven diagram SCP UNIX COMMAND SSL Singleton Pattern in Java Spring Bean Initialization methods and their order Spring Interview Questions Struts Struts 2.0 Basics Struts 2.0 Design Pattern Submit Html Form With Java Script On The Fly Unix executable For Java Program XOM DOM SAX XP books computers core java; core java; object oriented programming data structure; java investment bank; design pattern dtd duplicate rows in table get browser name with jquery grails podcast inner class java beginners tutorial java cache java networking tutorial java spring java util; java collections; java questions java.java1.5 linked list mailto function with all browser oracle database oracle duplicate rows orm schema social spring mvc questions struts transaction tricks tweet windows xslt