JDBC - Statements, PreparedStatement and CallableStatement


Once an association is gotten we can interface with the database. The JDBC Statement, CallableStatement, and PreparedStatement interfaces characterize the techniques and properties that empower you to send SQL or PL/SQL directions and get information from your database.

They likewise characterize techniques that assistance connect information type contrasts among Java and SQL information types utilized in a database.

The following table gives a synopsis of every interface's motivation to choose the interface to use.

Interfaces Recommended Use
Statement Use the for broadly useful access to your database. Valuable when you are utilizing static SQL explanations at runtime. The Statement interface can't acknowledge parameters.
PreparedStatement Use the when you intend to utilize the SQL explanations ordinarily. The PreparedStatement interface acknowledges input parameters at runtime.
CallableStatement Use the when you need to get to the database put away methods. The CallableStatement interface can likewise acknowledge runtime input parameters.

The Statement Objects

Creating Statement Object

Before you can utilize a Statement item to execute a SQL articulation, you have to make one utilizing the Connection article's createStatement( ) technique, as in the accompanying model −

Statement stmt = null; 

try { 

stmt = conn.createStatement( ); 

. . . 

} 

catch (SQLException e) { 

. . . 

} 

finally { 

. . . 

}

Once you've made a Statement object, you would then be able to utilize it to execute a SQL articulation with one of its three execute methods.

  • boolean execute (String SQL): Returns a boolean estimation of genuine if a ResultSet article can be recovered; else, it returns false. Utilize this strategy to execute SQL DDL articulations or when you have to utilize really unique SQL.

  • int executeUpdate (String SQL): Returns the quantity of lines influenced by the execution of the SQL proclamation. Utilize this technique to execute SQL articulations for which you hope to get various lines influenced - for instance, an INSERT, UPDATE, or DELETE statement.

  • ResultSet executeQuery (String SQL): Returns a ResultSet object. Utilize this strategy when you hope to get an outcome set, as you would with a SELECT statement.

Closing Statement Object

Just as you close a Connection item to spare database assets, for a similar reason you should likewise close the Statement object.

A basic call to the nearby() strategy will carry out the responsibility. On the off chance that you close the Connection object first, it will close the Statement object also. Nonetheless, you should in every case unequivocally close the Statement article to guarantee appropriate cleanup.

Statement stmt = null; 

try { 

stmt = conn.createStatement( ); 

. . . 

} 

catch (SQLException e) { 

. . . 

} 

finally { 

stmt.close(); 

}

The PreparedStatement Objects

The PreparedStatement interface broadens the Statement interface, which gives you included usefulness with two or three points of interest over a conventional Statement object.

This articulation gives you the adaptability of providing contentions dynamically.

Creating PreparedStatement Object

PreparedStatement pstmt = null; 

try { 

String SQL = "Update Employees SET age = ? WHERE id = ?"; 

pstmt = conn.prepareStatement(SQL); 

. . . 

} 

catch (SQLException e) { 

. . . 

} 

finally { 

. . . 

}

All parameters in JDBC are spoken to by the ? image, which is known as the parameter marker. You should supply esteems for each parameter before executing the SQL statement.

The setXXX() strategies tie esteems to the parameters, where XXX speaks to the Java information kind of the esteem you wish to tie to the information parameter. On the off chance that you neglect to supply the qualities, you will get a SQLException.

Each parameter marker is alluded by its ordinal position. The principal marker speaks to position 1, the following position 2, etc. This technique contrasts from that of Java cluster lists, which begins at 0.

All of the Statement object's strategies for cooperating with the database (an) execute(), (b) executeQuery(), and (c) executeUpdate() additionally work with the PreparedStatement object. In any case, the strategies are altered to utilize SQL explanations that can include the parameters.

Closing PreparedStatement Object

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

A straightforward call to the nearby() technique will carry out the responsibility. In the event that you close the Connection object first, it will close the PreparedStatement object too. Notwithstanding, you should in every case unequivocally close the PreparedStatement item to guarantee appropriate cleanup.

PreparedStatement pstmt = null; 

try { 

String SQL = "Update Employees SET age = ? WHERE id = ?"; 

pstmt = conn.prepareStatement(SQL); 

. . . 

} 

catch (SQLException e) { 

. . . 

} 

finally { 

pstmt.close(); 

}

The CallableStatement Objects

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 methodology −

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 method 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 sorts of parameters exist: IN, OUT, and INOUT. The PreparedStatement object just uses the IN parameter. The CallableStatement article can utilize all the three.

Here are the meanings of each −

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

The following code piece tells the best way to utilize the Connection.prepareCall() strategy 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 methodology, with parameter placeholders.

Using the CallableStatement objects is much similar to utilizing the 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 procedures 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 that the put away strategy is relied upon to return.

Once you call your put away strategy, you recover the incentive from the OUT parameter with the suitable 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() technique will carry out the responsibility. In the event that you close the Connection object first, it will close the CallableStatement object also. Be that as it may, you should in every case unequivocally close the CallableStatement article to guarantee legitimate cleanup.

CallableStatement cstmt = null; 

try { 

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

cstmt = conn.prepareCall (SQL); 

. . . 

} 

catch (SQLException e) { 

. . . 

} 

finally { 

cstmt.close(); 

}





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

Python 3