CallableStatement in Java viene utilizzato per chiamare una stored procedure dal programma Java. Le Stored Procedures sono un gruppo di istruzioni che compiliamo nel database per svolgere determinati compiti. Le stored procedure sono utili quando si gestiscono più tabelle con scenari complessi e, anziché inviare molteplici query al database, possiamo inviare i dati necessari alla stored procedure e far eseguire la logica nel server del database stesso.
CallableStatement
La API JDBC fornisce il supporto per eseguire le stored procedure tramite l’interfaccia CallableStatement. Le stored procedure devono essere scritte nella sintassi specifica del database e, per questo tutorial, utilizzerò il database Oracle. Esamineremo le caratteristiche standard di CallableStatement con parametri IN e OUT. Successivamente, esamineremo gli esempi specifici di Oracle con STRUCT e Cursor. Iniziamo creando prima una tabella per i programmi di esempio di CallableStatement con la query SQL seguente. create_employee.sql
-- Per 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")
);
Creiamo prima una classe di utilità per ottenere l’oggetto di connessione al database Oracle. Assicurati che il jar Oracle OJDBC sia nel percorso di compilazione del progetto. 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 {
// carica la classe del driver
Class.forName(DB_DRIVER_CLASS);
// crea ora la connessione
con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
Esempio di CallableStatement
Scriviamo una semplice stored procedure per inserire dati nella tabella Employee. 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;
Come puoi vedere, la procedura insertEmployee si aspetta input dal chiamante che verranno inseriti nella tabella Employee. Se l’istruzione di inserimento funziona correttamente, restituisce TRUE e in caso di eccezione restituisce FALSE. Vediamo come possiamo utilizzare CallableStatement per eseguire la stored procedure insertEmployee per inserire i dati dell’impiegato. 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;
//Leggi input utente
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);
//registra il parametro OUT prima di chiamare la stored procedure
stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
stmt.executeUpdate();
//leggi il parametro OUT ora
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();
}
}
}
}
Stiamo leggendo l’input dell’utente da memorizzare nella tabella dei dipendenti. L’unica differenza rispetto a `PreparedStatement` è la creazione di `CallableStatement` tramite “{call insertEmployee(?,?,?,?,?,?)}” e l’impostazione del parametro OUT con il metodo `CallableStatement registerOutParameter()`. Dobbiamo registrare il parametro OUT prima di eseguire la stored procedure. Una volta eseguita la stored procedure, possiamo utilizzare il metodo `CallableStatement getXXX()` per ottenere i dati dell’oggetto OUT. Si noti che durante la registrazione del parametro OUT, è necessario specificare il tipo di parametro OUT tramite `java.sql.Types`. Il codice è di natura generica, quindi se abbiamo la stessa stored procedure in altri database relazionali come MySQL, possiamo eseguirli anche con questo programma. Di seguito è riportato l’output quando eseguiamo il programma di esempio sopra più volte.
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
Si noti che la seconda esecuzione è fallita perché il nome passato è più grande della dimensione della colonna. Stiamo gestendo l’eccezione nella stored procedure e restituendo false in questo caso.
Esempio di CallableStatement – Parametri OUT della stored procedure
Ora scriviamo una stored procedure per ottenere i dati dell’utente per id. L’utente inserirà l’id del dipendente e il programma visualizzerà le informazioni del dipendente. `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;
Il programma di esempio Java CallableStatement che utilizza la stored procedure getEmployee per leggere i dati dell’utente è; 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;
// Leggere gli input dell'utente
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);
// Registrare il parametro OUT prima di chiamare la stored procedure
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();
// Leggere il parametro OUT adesso
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();
}
}
}
}
Di nuovo, il programma è generico e funziona per qualsiasi database che ha la stessa stored procedure. Vediamo quale è l’output quando eseguiamo il programma di esempio CallableStatement sopra.
Enter Employee ID (int):
1
Employee Name=Pankaj,Role=Developer,City=Bangalore,Country=India
Esempio CallableStatement – Stored Procedure Oracle CURSOR
Dato che stiamo leggendo le informazioni dell’impiegato tramite l’ID, otteniamo un singolo risultato e i parametri OUT funzionano bene per leggere i dati. Ma se cerchiamo per ruolo o paese, potremmo ottenere più righe e in tal caso possiamo usare il CURSOR di Oracle per leggerle come un result set. 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;
// Leggere gli input dell'utente
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);
// Registrare il parametro OUT prima di chiamare la stored procedure
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
// Leggere il parametro OUT adesso
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();
}
}
}
}
Questo programma utilizza classi specifiche di Oracle OJDBC e non funzionerà con altri database. Stiamo impostando il tipo di parametro OUT come OracleTypes.CURSOR e poi lo convertiamo in un oggetto ResultSet. Un’altra parte del codice è semplice programmazione JDBC. Quando eseguiamo il programma di esempio del CallableStatement sopra, otteniamo l’output seguente.
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
Il tuo output può variare a seconda dei dati nella tabella Employee.
Esempio di CallableStatement – Oggetto e STRUCT di Oracle DB
Se guardi le procedure memorizzate insertEmployee e getEmployee, ho tutti i parametri della tabella Employee nella procedura. Quando il numero di colonne cresce, ciò può portare a confusione e a maggiori possibilità di errore. Il database Oracle fornisce l’opzione di creare oggetti di database e possiamo utilizzare Oracle STRUCT per lavorare con essi. Per prima cosa definiamo l’oggetto DB Oracle per le colonne della tabella Employee. 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)
);
Ora riscriviamo la procedura memorizzata insertEmployee utilizzando 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;
Vediamo come possiamo chiamare la procedura memorizzata insertEmployeeObject nel programma Java. 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;
//Creare un array di oggetti per la chiamata della procedura memorizzata
Object[] empObjArray = new Object[5];
//Leggere gli input dell'utente
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);
//Registrare il parametro OUT prima di chiamare la procedura memorizzata
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.executeUpdate();
//Leggere ora il parametro OUT
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();
}
}
}
}
Prima di tutto stiamo creando un array di oggetti della stessa lunghezza dell’oggetto database EMPLOYEE_OBJ. Quindi stiamo impostando i valori in base alle variabili dell’oggetto EMPLOYEE_OBJ. Questo è molto importante altrimenti i dati verranno inseriti nelle colonne sbagliate. Quindi stiamo creando l’oggetto oracle.sql.STRUCT con l’aiuto di oracle.sql.StructDescriptor e il nostro array di oggetti. Una volta creato l’oggetto STRUCT, lo impostiamo come parametro IN per la procedura memorizzata, registriamo il parametro OUT ed eseguiamo. Questo codice è strettamente legato all’API OJDBC e non funzionerà per altri database. Ecco l’output quando eseguiamo questo programma.
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
Possiamo usare anche l’oggetto Database come parametro OUT e leggerlo per ottenere i valori dal database. Questo è tutto per l’esempio di CallableStatement in Java per eseguire Stored Procedure, spero tu abbia imparato qualcosa da esso.
Source:
https://www.digitalocean.com/community/tutorials/callablestatement-in-java-example