CallableStatement em Java é usado para chamar procedimentos armazenados a partir de um programa Java. Procedimentos Armazenados são um grupo de instruções que compilamos no banco de dados para alguma tarefa. Procedimentos armazenados são benéficos quando lidamos com várias tabelas em cenários complexos e, em vez de enviar várias consultas ao banco de dados, podemos enviar os dados necessários para o procedimento armazenado e ter a lógica executada no próprio servidor do banco de dados.
CallableStatement
A API JDBC fornece suporte para executar Procedimentos Armazenados por meio da interface CallableStatement. Procedimentos armazenados precisam ser escritos na sintaxe específica do banco de dados e, para este tutorial, vou usar o banco de dados Oracle. Vamos examinar as características padrão do CallableStatement com parâmetros de entrada e saída. Mais tarde, vamos explorar exemplos específicos do Oracle, como STRUCT e Cursor. Primeiro, vamos criar uma tabela para nossos programas de exemplo de CallableStatement com a seguinte consulta SQL. create_employee.sql
-- Para o banco de dados Oracle
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")
);
Vamos primeiro criar uma classe de utilidade para obter o objeto de Conexão com o banco de dados Oracle. Certifique-se de que o arquivo JAR Oracle OJDBC esteja no caminho de construção do projeto. 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 {
// Carregue a Classe do Driver
Class.forName(DB_DRIVER_CLASS);
// Crie a conexão agora
con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
Exemplo de CallableStatement
Vamos escrever uma simples stored procedure para inserir dados na tabela 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;
Como você pode ver, a procedure insertEmployee espera entradas do chamador que serão inseridas na tabela Employee. Se a instrução de inserção funcionar corretamente, ela retornará TRUE e, em caso de qualquer exceção, retornará FALSE. Vamos ver como podemos usar CallableStatement para executar a stored procedure insertEmployee e inserir dados do funcionário. 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;
// Leia as Entradas do Usuário
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);
// Registre o parâmetro OUT antes de chamar a stored procedure
stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
stmt.executeUpdate();
// Leia o parâmetro OUT agora
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();
}
}
}
}
Estamos lendo a entrada do usuário para ser armazenada na tabela de Funcionários. A única diferença do PreparedStatement é a criação do CallableStatement através de “{call insertEmployee(?,?,?,?,?,?)}” e a configuração do parâmetro OUT com o método CallableStatement registerOutParameter(). Temos que registrar o parâmetro OUT antes de executar a stored procedure. Uma vez que a stored procedure é executada, podemos usar o método CallableStatement getXXX() para obter os dados do objeto OUT. Observe que ao registrar o parâmetro OUT, precisamos especificar o tipo de parâmetro OUT através de java.sql.Types. O código é genérico por natureza, então se tivermos a mesma stored procedure em outro banco de dados relacional como MySQL, podemos executá-la com este programa também. Abaixo está a saída quando estamos executando o programa de exemplo de CallableStatement acima várias vezes.
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
Observe que a segunda execução falhou porque o nome passado é maior do que o tamanho da coluna. Estamos tratando a exceção na stored procedure e retornando falso neste caso.
Exemplo de CallableStatement – Parâmetros OUT de Stored Procedure
Agora vamos escrever uma stored procedure para obter os dados do funcionário por id. O usuário irá inserir o id do funcionário e o programa irá exibir as informações do funcionário. 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;
Exemplo de programa Java CallableStatement usando a stored procedure getEmployee para ler os dados do funcionário é; 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;
// Ler Entradas do Usuário
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);
// registrar o parâmetro OUT antes de chamar a 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();
// ler o parâmetro OUT agora
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();
}
}
}
}
Novamente, o programa é genérico e funciona para qualquer banco de dados que tenha a mesma stored procedure. Vamos ver qual é a saída quando executamos o programa de exemplo CallableStatement acima.
Enter Employee ID (int):
1
Employee Name=Pankaj,Role=Developer,City=Bangalore,Country=India
Exemplo de CallableStatement – Stored Procedure Oracle CURSOR
Como estamos lendo as informações do funcionário por ID, estamos obtendo um único resultado e os parâmetros OUT funcionam bem para ler os dados. Mas se pesquisarmos por cargo ou país, podemos obter várias linhas e, nesse caso, podemos usar o CURSOR do Oracle para lê-las como um conjunto de resultados. 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;
// Ler Entradas do Usuário
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);
// registrar o parâmetro OUT antes de chamar a stored procedure
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
// ler o parâmetro OUT agora
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();
}
}
}
}
Este programa está utilizando classes específicas do Oracle OJDBC e não funcionará com outros bancos de dados. Estamos definindo o tipo de parâmetro OUT como OracleTypes.CURSOR e depois fazendo o cast para o objeto ResultSet. Outra parte do código é programação JDBC simples. Quando executamos o programa de exemplo de CallableStatement acima, obtemos a seguinte saída.
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
Sua saída pode variar dependendo dos dados em sua tabela de funcionários.
Exemplo de CallableStatement – Objeto e STRUCT do Oracle DB
Se olharmos para os procedimentos armazenados insertEmployee e getEmployee, estou tendo todos os parâmetros da tabela de funcionários no procedimento. Quando o número de colunas aumenta, isso pode levar a confusão e a mais erros. O banco de dados Oracle fornece a opção de criar objetos de banco de dados e podemos usar o STRUCT do Oracle para trabalhar com eles. Vamos primeiro definir o objeto DB Oracle para as colunas da tabela de funcionários. 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)
);
Agora vamos reescrever o procedimento armazenado insertEmployee usando 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;
Vamos ver como podemos chamar o procedimento armazenado insertEmployeeObject no programa 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;
//Criar Array de Objetos para chamada de Procedimento Armazenado
Object[] empObjArray = new Object[5];
//Ler Entradas do Usuário
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);
//registrar o parâmetro DE SAÍDA antes de chamar o procedimento armazenado
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.executeUpdate();
//ler o parâmetro DE SAÍDA agora
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();
}
}
}
}
Primeiramente, estamos criando um array de objetos com o mesmo comprimento do objeto de banco de dados EMPLOYEE_OBJ. Em seguida, definimos valores de acordo com as variáveis do objeto EMPLOYEE_OBJ. Isso é muito importante, caso contrário, os dados serão inseridos nas colunas erradas. Em seguida, criamos o objeto oracle.sql.STRUCT com a ajuda de oracle.sql.StructDescriptor e nosso array de objetos. Uma vez que o objeto STRUCT é criado, o definimos como parâmetro de ENTRADA para o procedimento armazenado, registramos o parâmetro DE SAÍDA e o executamos. Este código está fortemente acoplado com a API OJDBC e não funcionará para outros bancos de dados. Aqui está a saída ao executar este programa.
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
Podemos usar o objeto do banco de dados como parâmetro DE SAÍDA também e lê-lo para obter os valores do banco de dados. Isso é tudo para o exemplo de CallableStatement em Java para executar Procedimentos Armazenados. Espero que você tenha aprendido algo com isso.
Source:
https://www.digitalocean.com/community/tutorials/callablestatement-in-java-example