January 28, 2019January 28, 2019 ocjp

CallableStatement in JDBC MySQL

JDBC

Earlier we have learned PreparedStatement example for insert data. Another method in JDBC for executing query is CallableStatement. CallableStatement is used to execute the StoredProcedures from Java. First of all we have to create Stored Procedure to insert data in our table. We have used the same table that was used in our earlier JDBC Examples.

If you are using phpMyAdmin with XAMPP or WAMP then you will have option routines in Menu to add StoredProcedures. Where you can define the parameters and definition of Stored Procedure.

We can also create storedprocedure by SQL Query like:

CREATE PROCEDURE `insertdata`(IN `webname` VARCHAR(100), IN `webdetails` VARCHAR(255), IN `rank` INT) 
NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER 
Insert into tbldata(strwebsitename,strdetails,intrank) values(webname,webdetails,rank);

To check the StoredProcedure we can execute SQL Like

call insertdata('https://coverwithcare.in','Insurance Details',5);

It will insert a row into table tbldata. Now move to eclipse and open your Project or create a new one. We have used the same Swing Form from our previous articles. Entire Program will be like:

package jdbcdemo;

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JSpinner;
import javax.swing.JTextArea;
import javax.swing.JTextField;

public class CallableDemo extends JFrame {

	Connection con=null;
	CallableStatement ps=null;
	
	JLabel lblTitle;
	JLabel lblWebsiteName;
	JTextField txtWebsiteName;
	
	JLabel lblWebsiteDetails;
	JTextArea txtWebsiteDetails;
	
	JLabel lblWebsiteRank;
	JSpinner spnWebsiteRank;
	
	JButton btnSubmit;
	
	JPanel pnlTop;
	JPanel pnlCenter;
	JPanel pnlBottom;
	
	//Database Details 
	String dbusername="root";
     String dbpassword="";
     String databasename="dbjavademo";
     String port="3306" ;// mostly common for mysql
     
	
	public CallableDemo(){
	
	setLayout(new BorderLayout());
	
	
	
	pnlTop= new JPanel();
	pnlCenter= new JPanel();
	pnlBottom= new JPanel();
	
	
	this.add(pnlTop,BorderLayout.NORTH);
	this.add(pnlBottom,BorderLayout.SOUTH);
	this.add(pnlCenter,BorderLayout.CENTER);
	
	
	pnlTop.setLayout(new FlowLayout(FlowLayout.CENTER));
	
	lblTitle= new JLabel("WEBSITE MANAGEMENT");
	lblTitle.setFont(new Font("Serif", Font.PLAIN, 24));
	pnlTop.add(lblTitle);
	
	
	pnlCenter.setLayout(null);
	
	lblWebsiteName= new JLabel("Enter Webiste Name");
	lblWebsiteName.setBounds(10,10,150,30);
	pnlCenter.add(lblWebsiteName);
	
	txtWebsiteName= new JTextField();
	txtWebsiteName.setBounds(180,10,290,30);
	pnlCenter.add(txtWebsiteName);
	
	
	lblWebsiteDetails= new JLabel("Enter Webiste Details");
	lblWebsiteDetails.setBounds(10,60,150,30);
	pnlCenter.add(lblWebsiteDetails);
	
	txtWebsiteDetails= new JTextArea();
	txtWebsiteDetails.setBounds(180,60,290,130);
	pnlCenter.add(txtWebsiteDetails);
	
	
	lblWebsiteRank= new JLabel("Select Webiste Rank");
	lblWebsiteRank.setBounds(10,220,150,30);
	pnlCenter.add(lblWebsiteRank);
	
	spnWebsiteRank= new JSpinner();
	spnWebsiteRank.setBounds(180,220,90,30);
	pnlCenter.add(spnWebsiteRank);
	
	
	btnSubmit= new JButton("Save");
	btnSubmit.setBounds(180,260,90,50);
	pnlCenter.add(btnSubmit);
	
	setSize(700,600);
	setDefaultCloseOperation(DISPOSE_ON_CLOSE);
	
	
	
	
	btnSubmit.addActionListener(new ActionListener() {
		
		@Override
		public void actionPerformed(ActionEvent arg0) {
			// TODO Auto-generated method stub
			
			String webname= txtWebsiteName.getText().trim();
			String webdetails= txtWebsiteDetails.getText().trim();
			int webRank= Integer.parseInt(spnWebsiteRank.getValue().toString());
			
			//Here you can process your values
	
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
			con=DriverManager.getConnection("jdbc:mysql://localhost:"+ port+"/"+ databasename,dbusername,dbpassword); //Connecting to Database
			
			String q="{call insertdata (?,?,?)}";
			ps= con.prepareCall(q) ;// CallableStatement Created
	        
			ps.setString(1, webname);
			ps.setString(2, webdetails);
			ps.setInt(3, webRank);
			
			int rowsAffected= ps.executeUpdate();
			
			if(rowsAffected>0) {
				
				JOptionPane.showMessageDialog(CallableDemo.this, "Data Inserted");
			}
			
			con.close();
				

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		}
	});
	
	}
	
	public static void main(String []a) {
		
		CallableDemo mainform= new CallableDemo();
		mainform.setVisible(true);
	}
}

Here changes are :

import java.sql.CallableStatement;

Object for Callable :

CallableStatement ps=null;
..
..
	String q="{call insertdata (?,?,?)}"; //Procedure Name with Parameters
	ps= con.prepareCall(q) ;// CallableStatement Created
	        

Execution is same as PreparedStatement.