CallableStatement in Java wordt gebruikt om een stored procedure aan te roepen vanuit een Java-programma. Opgeslagen procedures zijn een groep statements die we compileren in de database voor een bepaalde taak. Stored procedures zijn handig wanneer we te maken hebben met meerdere tabellen in een complex scenario. In plaats van meerdere queries naar de database te sturen, kunnen we de benodigde gegevens naar de stored procedure sturen en de logica in de databaseserver zelf laten uitvoeren.
CallableStatement
JDBC API biedt ondersteuning voor het uitvoeren van stored procedures via de CallableStatement-interface. Stored procedures moeten worden geschreven in de syntaxis die specifiek is voor de database, en voor deze tutorial zal ik de Oracle-database gebruiken. We zullen kijken naar de standaardfuncties van CallableStatement met IN- en OUT-parameters. Later zullen we voorbeelden bekijken van Oracle-specifieke STRUCT en Cursor. Laten we eerst een tabel maken voor onze CallableStatement-voorbeeldprogramma’s met de onderstaande SQL-query. create_employee.sql
-- Voor Oracle DB
CREATE TABLE EMPLOYEE
(
"EMPID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(10 BYTE) DEFAULT NULL,
"ROLE" VARCHAR2(10 BYTE) DEFAULT NULL,
"CITY" VARCHAR2(10 BYTE) DEFAULT NULL,
"COUNTRY" VARCHAR2(10 BYTE) DEFAULT NULL,
PRIMARY KEY ("EMPID")
);
Laten we eerst een hulpprogrammaklasse maken om het Oracle databaseverbindingobject te krijgen. Zorg ervoor dat het Oracle OJDBC-jarbestand in het build-pad van het project staat. DBConnection.java
package com.journaldev.jdbc.storedproc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
private static final String DB_DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
private static final String DB_USERNAME = "HR";
private static final String DB_PASSWORD = "oracle";
public static Connection getConnection() {
Connection con = null;
try {
// Laad de Driver Class
Class.forName(DB_DRIVER_CLASS);
// Maak nu de verbinding
con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
Voorbeeld van CallableStatement
Laten we een eenvoudige opgeslagen procedure schrijven om gegevens in te voegen in de Employee-tabel. insertEmployee.sql
CREATE OR REPLACE PROCEDURE insertEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
in_name IN EMPLOYEE.NAME%TYPE,
in_role IN EMPLOYEE.ROLE%TYPE,
in_city IN EMPLOYEE.CITY%TYPE,
in_country IN EMPLOYEE.COUNTRY%TYPE,
out_result OUT VARCHAR2)
AS
BEGIN
INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY)
values (in_id,in_name,in_role,in_city,in_country);
commit;
out_result := 'TRUE';
EXCEPTION
WHEN OTHERS THEN
out_result := 'FALSE';
ROLLBACK;
END;
Zoals je kunt zien, verwacht de insertEmployee-procedure invoer van de beller die in de Employee-tabel zal worden ingevoegd. Als het invoegingsstatement goed werkt, retourneert het TRUE en in geval van een uitzondering retourneert het FALSE. Laten we eens kijken hoe we CallableStatement kunnen gebruiken om de opgeslagen procedure insertEmployee uit te voeren om werknemersgegevens in te voegen. JDBCStoredProcedureWrite.java
package com.journaldev.jdbc.storedproc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
public class JDBCStoredProcedureWrite {
public static void main(String[] args) {
Connection con = null;
CallableStatement stmt = null;
// Lees gebruikersinvoer
Scanner input = new Scanner(System.in);
System.out.println("Enter Employee ID (int):");
int id = Integer.parseInt(input.nextLine());
System.out.println("Enter Employee Name:");
String name = input.nextLine();
System.out.println("Enter Employee Role:");
String role = input.nextLine();
System.out.println("Enter Employee City:");
String city = input.nextLine();
System.out.println("Enter Employee Country:");
String country = input.nextLine();
try{
con = DBConnection.getConnection();
stmt = con.prepareCall("{call insertEmployee(?,?,?,?,?,?)}");
stmt.setInt(1, id);
stmt.setString(2, name);
stmt.setString(3, role);
stmt.setString(4, city);
stmt.setString(5, country);
// Registreer de OUT-parameter voordat u de opgeslagen procedure aanroept
stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
stmt.executeUpdate();
// Lees nu de OUT-parameter
String result = stmt.getString(6);
System.out.println("Employee Record Save Success::"+result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
stmt.close();
con.close();
input.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
We lezen gebruikersinvoer om op te slaan in de tabel Employee. Het enige verschil met PreparedStatement is de creatie van CallableStatement via “{call insertEmployee(?,?,?,?,?,?)}” en het instellen van de OUT-parameter met de methode CallableStatement registerOutParameter(). We moeten de OUT-parameter registreren voordat we de opgeslagen procedure uitvoeren. Zodra de opgeslagen procedure is uitgevoerd, kunnen we de methode CallableStatement getXXX() gebruiken om de OUT-objectgegevens te krijgen. Let op dat bij het registreren van de OUT-parameter, we het type OUT-parameter moeten specificeren via java.sql.Types. De code is generiek van aard, dus als we dezelfde opgeslagen procedure hebben in een andere relationele database zoals MySQL, kunnen we ze ook met dit programma uitvoeren. Hieronder staat de uitvoer wanneer we het bovenstaande CallableStatement-voorbeeldprogramma meerdere keren uitvoeren.
Enter Employee ID (int):
1
Enter Employee Name:
Pankaj
Enter Employee Role:
Developer
Enter Employee City:
Bangalore
Enter Employee Country:
India
Employee Record Save Success::TRUE
-----
Enter Employee ID (int):
2
Enter Employee Name:
Pankaj Kumar
Enter Employee Role:
CEO
Enter Employee City:
San Jose
Enter Employee Country:
USA
Employee Record Save Success::FALSE
Let op dat de tweede uitvoering is mislukt omdat de doorgegeven naam groter is dan de kolomgrootte. We verbruiken de uitzondering in de opgeslagen procedure en retourneren in dit geval false.
Voorbeeld van CallableStatement – OUT-parameters van opgeslagen procedure
Lat…We zullen nu een opgeslagen procedure schrijven om de gegevens van de werknemer op te halen op basis van id. De gebruiker voert het werknemers-id in en het programma toont de werknemersinformatie. getEmployee.sql
create or replace
PROCEDURE getEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
out_name OUT EMPLOYEE.NAME%TYPE,
out_role OUT EMPLOYEE.ROLE%TYPE,
out_city OUT EMPLOYEE.CITY%TYPE,
out_country OUT EMPLOYEE.COUNTRY%TYPE
)
AS
BEGIN
SELECT NAME, ROLE, CITY, COUNTRY
INTO out_name, out_role, out_city, out_country
FROM EMPLOYEE
WHERE EMPID = in_id;
END;
Het Java CallableStatement voorbeeldprogramma dat de opgeslagen procedure getEmployee gebruikt om de werknemersgegevens te lezen, is; JDBCStoredProcedureRead.java
package com.journaldev.jdbc.storedproc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
public class JDBCStoredProcedureRead {
public static void main(String[] args) {
Connection con = null;
CallableStatement stmt = null;
//Lees gebruikersinvoer
Scanner input = new Scanner(System.in);
System.out.println("Enter Employee ID (int):");
int id = Integer.parseInt(input.nextLine());
try{
con = DBConnection.getConnection();
stmt = con.prepareCall("{call getEmployee(?,?,?,?,?)}");
stmt.setInt(1, id);
//Registreer de OUT-parameter voordat u de opgeslagen procedure aanroept
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
stmt.registerOutParameter(4, java.sql.Types.VARCHAR);
stmt.registerOutParameter(5, java.sql.Types.VARCHAR);
stmt.execute();
//Lees nu de OUT-parameter
String name = stmt.getString(2);
String role = stmt.getString(3);
String city = stmt.getString(4);
String country = stmt.getString(5);
if(name !=null){
System.out.println("Employee Name="+name+",Role="+role+",City="+city+",Country="+country);
}else{
System.out.println("Employee Not Found with ID"+id);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try {
stmt.close();
con.close();
input.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Het programma is opnieuw generiek en werkt voor elke database met dezelfde opgeslagen procedure. Laten we eens kijken wat de uitvoer is wanneer we het bovenstaande CallableStatement-voorbeeldprogramma uitvoeren.
Enter Employee ID (int):
1
Employee Name=Pankaj,Role=Developer,City=Bangalore,Country=India
Voorbeeld van CallableStatement – Opgeslagen procedure Oracle CURSOR
Aangezien we de werknemersinformatie lezen op basis van ID, krijgen we een enkel resultaat en werken de OUT-parameters goed om de gegevens te lezen. Maar als we zoeken op rol of land, kunnen we meerdere rijen krijgen en in dat geval kunnen we Oracle CURSOR gebruiken om ze te lezen zoals resultaatset. getEmployeeByRole.sql
create or replace
PROCEDURE getEmployeeByRole
(in_role IN EMPLOYEE.ROLE%TYPE,
out_cursor_emps OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN out_cursor_emps FOR
SELECT EMPID, NAME, CITY, COUNTRY
FROM EMPLOYEE
WHERE ROLE = in_role;
END;
JDBCStoredProcedureCursor.java
package com.journaldev.jdbc.storedproc;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import oracle.jdbc.OracleTypes;
public class JDBCStoredProcedureCursor {
public static void main(String[] args) {
Connection con = null;
CallableStatement stmt = null;
ResultSet rs = null;
//Lees gebruikersinvoer
Scanner input = new Scanner(System.in);
System.out.println("Enter Employee Role:");
String role = input.nextLine();
try{
con = DBConnection.getConnection();
stmt = con.prepareCall("{call getEmployeeByRole(?,?)}");
stmt.setString(1, role);
//Registreer de OUT-parameter voordat u de opgeslagen procedure aanroept
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
//Lees nu de OUT-parameter
rs = (ResultSet) stmt.getObject(2);
while(rs.next()){
System.out.println("Employee ID="+rs.getInt("empId")+",Name="+rs.getString("name")+
",Role="+role+",City="+rs.getString("city")+
",Country="+rs.getString("country"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
try {
rs.close();
stmt.close();
con.close();
input.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Dit programma maakt gebruik van specifieke klassen van Oracle OJDBC en werkt niet met andere databases. We stellen het OUT-parameter type in als OracleTypes.CURSOR en casten het vervolgens naar een ResultSet-object. Het andere deel van de code is eenvoudige JDBC-programmering. Wanneer we het bovenstaande CallableStatement-voorbeeldprogramma uitvoeren, krijgen we de onderstaande uitvoer.
Enter Employee Role:
Developer
Employee ID=5,Name=Kumar,Role=Developer,City=San Jose,Country=USA
Employee ID=1,Name=Pankaj,Role=Developer,City=Bangalore,Country=India
Uw uitvoer kan variëren afhankelijk van de gegevens in uw werknemerstabel.
CallableStatement Voorbeeld – Oracle DB Object en STRUCT
Als je kijkt naar de insertEmployee en getEmployee opgeslagen procedures, heb ik alle parameters van de werknemerstabel in de procedure. Wanneer het aantal kolommen groeit, kan dit tot verwarring leiden en foutgevoeliger zijn. Oracle database biedt de mogelijkheid om databaseobjecten te maken en we kunnen Oracle STRUCT gebruiken om ermee te werken. Laten we eerst een Oracle DB-object definiëren voor de kolommen van de werknemerstabel. EMPLOYEE_OBJ.sql
create or replace TYPE EMPLOYEE_OBJ AS OBJECT
(
EMPID NUMBER,
NAME VARCHAR2(10),
ROLE VARCHAR2(10),
CITY VARCHAR2(10),
COUNTRY VARCHAR2(10)
);
Laten we nu de opgeslagen procedure insertEmployee herschrijven met behulp van EMPLOYEE_OBJ. insertEmployeeObject.sql
CREATE OR REPLACE PROCEDURE insertEmployeeObject
(IN_EMPLOYEE_OBJ IN EMPLOYEE_OBJ,
out_result OUT VARCHAR2)
AS
BEGIN
INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY) values
(IN_EMPLOYEE_OBJ.EMPID, IN_EMPLOYEE_OBJ.NAME, IN_EMPLOYEE_OBJ.ROLE, IN_EMPLOYEE_OBJ.CITY, IN_EMPLOYEE_OBJ.COUNTRY);
commit;
out_result := 'TRUE';
EXCEPTION
WHEN OTHERS THEN
out_result := 'FALSE';
ROLLBACK;
END;
Laten we eens kijken hoe we de opgeslagen procedure insertEmployeeObject kunnen aanroepen in een javaprogramma. JDBCStoredProcedureOracleStruct.java
package com.journaldev.jdbc.storedproc;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class JDBCStoredProcedureOracleStruct {
public static void main(String[] args) {
Connection con = null;
OracleCallableStatement stmt = null;
//Maak Object Array voor Stored Procedure oproep
Object[] empObjArray = new Object[5];
//Lees Gebruikersinvoer
Scanner input = new Scanner(System.in);
System.out.println("Enter Employee ID (int):");
empObjArray[0] = Integer.parseInt(input.nextLine());
System.out.println("Enter Employee Name:");
empObjArray[1] = input.nextLine();
System.out.println("Enter Employee Role:");
empObjArray[2] = input.nextLine();
System.out.println("Enter Employee City:");
empObjArray[3] = input.nextLine();
System.out.println("Enter Employee Country:");
empObjArray[4] = input.nextLine();
try{
con = DBConnection.getConnection();
StructDescriptor empStructDesc = StructDescriptor.createDescriptor("EMPLOYEE_OBJ", con);
STRUCT empStruct = new STRUCT(empStructDesc, con, empObjArray);
stmt = (OracleCallableStatement) con.prepareCall("{call insertEmployeeObject(?,?)}");
stmt.setSTRUCT(1, empStruct);
//registreer de OUT-parameter voordat u de opgeslagen procedure aanroept
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.executeUpdate();
//lees nu de OUT-parameter
String result = stmt.getString(2);
System.out.println("Employee Record Save Success::"+result);
}catch(Exception e){
e.printStackTrace();
}finally{
try {
stmt.close();
con.close();
input.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Ten eerste maken we een Object array met dezelfde lengte als het EMPLOYEE_OBJ database object. Vervolgens stellen we waarden in volgens de variabelen van het EMPLOYEE_OBJ object. Dit is erg belangrijk, anders worden de gegevens ingevoegd in verkeerde kolommen. Vervolgens maken we een oracle.sql.STRUCT object met behulp van oracle.sql.StructDescriptor en ons Object array. Zodra het STRUCT-object is gemaakt, stellen we het in als IN-parameter voor de opgeslagen procedure, registreren we de OUT-parameter en voeren we deze uit. Deze code is sterk gekoppeld aan de OJDBC API en werkt niet voor andere databases. Hier is de output wanneer we dit programma uitvoeren.
Enter Employee ID (int):
5
Enter Employee Name:
Kumar
Enter Employee Role:
Developer
Enter Employee City:
San Jose
Enter Employee Country:
USA
Employee Record Save Success::TRUE
We kunnen het Database object ook als OUT-parameter gebruiken en het lezen om de waarden uit de database te halen. Dat is alles voor CallableStatement in java voorbeeld om Stored Procedures uit te voeren, ik hoop dat je er iets van hebt geleerd.
Source:
https://www.digitalocean.com/community/tutorials/callablestatement-in-java-example