servlets struts hibernate spring maven java
 

JDBC - Stored Procedure



We have figured out how to utilize Stored Procedures in JDBC while examining the JDBC - Statementschapter. This part is like that segment, yet it would give you extra data about JDBC SQL escape syntax.

Just as a Connection object makes the Statement and PreparedStatement objects, it additionally makes the CallableStatement object, which would be utilized to execute a call to a database put away procedure.

Creating CallableStatement Object

Suppose, you have to execute the accompanying Oracle put away strategy −

CREATE OR REPLACE PROCEDURE getEmpName 

(EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS 

BEGIN 

SELECT first INTO EMP_FIRST 

FROM Employees 

WHERE ID = EMP_ID; 

END;

NOTE: Above put away methodology has been composed for Oracle, however we are working with MySQL database along these lines, let us compose same put away system for MySQL as pursues to make it in EMP database −

DELIMITER $$ 

DROP PROCEDURE IF EXISTS 'EMP'.'getEmpName' $$ 

Make PROCEDURE 'EMP'.'getEmpName' 

(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255)) 

BEGIN 

SELECT first INTO EMP_FIRST 

FROM Employees 

WHERE ID = EMP_ID; 

END $$ 

DELIMITER ;

Three kinds of parameters exist: IN, OUT, and INOUT. The PreparedStatement object just uses the IN parameter. The CallableStatement item can utilize all the three.

Here are the meanings of each −

Parameter Description
IN A parameter whose esteem is obscure when the SQL articulation is made. You tie esteems to IN parameters with the setXXX() methods.
OUT A parameter whose esteem is provided by the SQL articulation it returns. You recover values from the OUT parameters with the getXXX() methods.
INOUT A parameter that gives both information and yield esteems. You tie factors with the setXXX() strategies and recover values with the getXXX() methods.

The following code scrap tells the best way to utilize the Connection.prepareCall() technique to instantiate a CallableStatement object dependent on the previous put away system −

CallableStatement cstmt = null; 

try { 

String SQL = "{call getEmpName (?, ?)}"; 

cstmt = conn.prepareCall (SQL); 

. . . 

} 

catch (SQLException e) { 

. . . 

} 

finally { 

. . . 

}

The String variable SQL speaks to the put away system, with parameter placeholders.

Using CallableStatement objects is much similar to utilizing PreparedStatement objects. You should tie esteems to every one of the parameters before executing the announcement, or you will get a SQLException.

If you have IN parameters, simply pursue similar standards and strategies that apply to a PreparedStatement object; utilize the setXXX() technique that compares to the Java information type you are binding.

When you use OUT and INOUT parameters, you should utilize an extra CallableStatement strategy, registerOutParameter(). The registerOutParameter() technique ties the JDBC information type to the information type the put away strategy is relied upon to return.

Once you call your put away system, you recover the incentive from the OUT parameter with the proper getXXX() technique. This strategy throws the recovered estimation of SQL type to a Java information type.

Closing CallableStatement Object

Just as you close other Statement object, for a similar reason you should likewise close the CallableStatement object.

A basic call to the nearby() strategy will carry out the responsibility. In the event that you close the Connection object first, it will close the CallableStatement object too. Be that as it may, you should in every case unequivocally close the CallableStatement article to guarantee appropriate cleanup.

CallableStatement cstmt = null; 

try { 

String SQL = "{call getEmpName (?, ?)}"; 

cstmt = conn.prepareCall (SQL); 

. . . 

} 

catch (SQLException e) { 

. . . 

} 

finally { 

cstmt.close(); 

}

JDBC SQL Escape Syntax

The escape sentence structure gives you the adaptability to utilize database explicit highlights inaccessible to you by utilizing standard JDBC strategies and properties.

The general SQL escape language structure position is as per the following −

{keyword 'parameters'}

Here are the accompanying getaway arrangements, which you would discover valuable while playing out the JDBC programming −

d, t, ts Keywords

They help distinguish date, time, and timestamp literals. As you probably are aware, no two DBMSs speak to time and date a similar way. This departure punctuation advises the driver to render the date or time in the objective database's organization. For Example −

{d 'yyyy-mm-dd'}

Where yyyy = year, mm = month; dd = date. Utilizing this grammar {d '2009-09-03'} is March 9, 2009.

Here is a basic model appearing at INSERT date in a table −

//Create a Statement object 

stmt = conn.createStatement(); 

//Insert information ==> ID, First Name, Last Name, DOB 

String sql="INSERT INTO STUDENTS VALUES" + 

"(100,'Zara','Ali', {d '2001-12-16'})"; 

stmt.executeUpdate(sql);

Similarly, you can utilize one of the accompanying two grammar, either t or ts

{t 'hh:mm:ss'}

Where hh = hour; mm = minute; ss = second. Utilizing this grammar {t '13:30:29'} is 1:30:29 PM.

{ts 'yyyy-mm-dd hh:mm:ss'}

This is consolidated sentence structure of the over two punctuation for 'd' and 't' to speak to timestamp.

escape Keyword

This catchphrase distinguishes the getaway character utilized in LIKE statements. Valuable when utilizing the SQL special case %, which matches at least zero characters. For instance −

String sql = "SELECT image FROM MathSymbols 

WHERE image LIKE '\%' {escape '\'}"; 

stmt.execute(sql);

If you utilize the oblique punctuation line character (\) as the departure character, you additionally need to utilize two oblique punctuation line characters in your Java String strict, on the grounds that the oblique punctuation line is likewise a Java escape character.

fn Keyword

This catchphrase speaks to scalar capacities utilized in a DBMS. For instance, you can utilize SQL work length to get the length of a string −

{fn length('Hello World')}

This returns 11, the length of the character string 'Hi World'.

call Keyword

This watchword is utilized to call the put away methods. For instance, for a put away system requiring an IN parameter, utilize the accompanying linguistic structure −

{call my_procedure(?)};

For a put away strategy requiring an IN parameter and restoring an OUT parameter, utilize the accompanying language structure −

{? = call my_procedure(?)};

oj Keyword

This catchphrase is utilized to imply external joins. The sentence structure is as per the following −

{oj outer-join}

Where external join = table {LEFT|RIGHT|FULL} OUTERJOIN {table | external join} on inquiry condition. For instance −

String sql = "SELECT Employees 

FROM {oj ThisTable RIGHT 

External JOIN ThatTable on id = '100'}"; 

stmt.execute(sql);






© Javacodegeeks 2019 - 2019
All Right Reserved and you agree to have read and accepted our term and condition.

Python 3