logo标签列表

如何从Java和JPA中调用存储过程

javasqlstored-proceduresjpajboss
104

我正在编写一个简单的Web应用程序，调用一个存储过程并检索一些数据。 这是一个与客户数据库交互的非常简单的应用程序。 我们传递员工ID和公司ID，存储过程将返回员工详细信息。

Web应用程序无法更新/删除数据，并且正在使用SQL Server。

我将我的Web应用程序部署在Jboss AS上。 在这种情况下，我应该使用JPA访问存储过程还是CallableStatement？使用JPA的任何优点吗？

此外，调用此存储过程的SQL语句将是什么？ 我以前从未使用过存储过程，这个让我很困惑。 谷歌并没有提供太多帮助。
以下是存储过程：

CREATE procedure getEmployeeDetails (@employeeId int, @companyId int)
as
begin
    select firstName, 
           lastName, 
           gender, 
           address
      from employee et
     where et.employeeId = @employeeId
       and et.companyId = @companyId
end

更新：

对于其他使用JPA调用存储过程遇到问题的人。

Query query = em.createNativeQuery("{call getEmployeeDetails(?,?)}",
                                   EmployeeDetails.class)           
                                   .setParameter(1, employeeId)
                                   .setParameter(2, companyId);

List<EmployeeDetails> result = query.getResultList();

我注意到的事情：

  1. 参数名对我没用，尝试使用参数索引。
  2. 正确的SQL语句为 {call sp_name(?,?)} 而不是 call sp_name(?,?)
  3. 如果存储过程返回结果集，即使您知道只有一行，getSingleResult 也无法工作。
  4. 传递一个 resultSetMapping 名称或结果类的详细信息。
2
在 native 查询中你不能使用命名参数，命名参数仅支持JPQL查询。如果你喜欢命名参数，你可以编写自己的类来将命名参数转换为数字参数。 - Viliam Búr
我一直都是使用具名参数与createNativeQueries，并且从未遇到任何问题。我刚刚看了一下我一直在工作的当前系统，有大量的带有命名参数的本地查询。您能为您的断言提供一些参考资料吗？我们的设定是JPA 2和Hibernate 4+。 - Jaumzera
19个回答
1
这篇回答可能对拥有实体管理器的人有所帮助。
我有一个用于创建下一个数字的存储过程，在服务器端我使用了Seam框架。
客户端：
 Object on = entityManager.createNativeQuery("EXEC getNextNmber").executeUpdate();
        log.info("New order id: " + on.toString());

数据库端（SQL Server）我有一个名为getNextNumber的存储过程。

executeUpdate() 返回 int。您确定正在接收 sproc 的输出吗？ - Constantine Gladky
1

JPA 2.0不支持返回值，只支持调用。

我的解决方案是创建一个调用存储过程的函数。

因此，在JAVA代码中，您可以执行调用Oracle函数的本地查询。

1
你可以在你的存储库中使用@Query(value = "{call PROC_TEST()}", nativeQuery = true)。这对我很有用。
注意：一定要使用 '{' 和 '}'，否则它将无法工作。
如何传递参数？ - java dev
1
从JPA 2.1开始，JPA支持使用动态StoredProcedureQuery和声明性@NamedStoredProcedureQuery调用存储过程。
0

persistence.xml

 <persistence-unit name="PU2" transaction-type="RESOURCE_LOCAL">
<non-jta-data-source>jndi_ws2</non-jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties/>

代码Java

  String PERSISTENCE_UNIT_NAME = "PU2";
    EntityManagerFactory factory2;
    factory2 = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);

    EntityManager em2 = factory2.createEntityManager();
    boolean committed = false;
    try {

        try {
            StoredProcedureQuery storedProcedure = em2.createStoredProcedureQuery("PKCREATURNO.INSERTATURNO");
            // set parameters
            storedProcedure.registerStoredProcedureParameter("inuPKEMPRESA", BigDecimal.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("inuPKSERVICIO", BigDecimal.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("inuPKAREA", BigDecimal.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("isbCHSIGLA", String.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("INUSINCALIFICACION", BigInteger.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("INUTIMBRAR", BigInteger.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("INUTRANSFERIDO", BigInteger.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("INTESTADO", BigInteger.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("inuContador", BigInteger.class, ParameterMode.OUT);

            BigDecimal inuPKEMPRESA = BigDecimal.valueOf(1);
            BigDecimal inuPKSERVICIO = BigDecimal.valueOf(5);
            BigDecimal inuPKAREA = BigDecimal.valueOf(23);
            String isbCHSIGLA = "";
            BigInteger INUSINCALIFICACION = BigInteger.ZERO;
            BigInteger INUTIMBRAR = BigInteger.ZERO;
            BigInteger INUTRANSFERIDO = BigInteger.ZERO;
            BigInteger INTESTADO = BigInteger.ZERO;
            BigInteger inuContador = BigInteger.ZERO;

            storedProcedure.setParameter("inuPKEMPRESA", inuPKEMPRESA);
            storedProcedure.setParameter("inuPKSERVICIO", inuPKSERVICIO);
            storedProcedure.setParameter("inuPKAREA", inuPKAREA);
            storedProcedure.setParameter("isbCHSIGLA", isbCHSIGLA);
            storedProcedure.setParameter("INUSINCALIFICACION", INUSINCALIFICACION);
            storedProcedure.setParameter("INUTIMBRAR", INUTIMBRAR);
            storedProcedure.setParameter("INUTRANSFERIDO", INUTRANSFERIDO);
            storedProcedure.setParameter("INTESTADO", INTESTADO);
            storedProcedure.setParameter("inuContador", inuContador);

            // execute SP
            storedProcedure.execute();
            // get result

            try {
                long _inuContador = (long) storedProcedure.getOutputParameterValue("inuContador");
                varCon = _inuContador + "";
            } catch (Exception e) {
            } 
        } finally {

        }
    } finally {
        em2.close();
    }
4
请将文本从英语翻译成中文。仅返回翻译后的文本：请不要犹豫在您的回答中添加任何评论（除了纯代码）。 - ivan.mylyanyk
0

最简单的方法是使用JpaRepository

1- Create a stored procedure
CREATE PROCEDURE dbo.getEmployeeDetails
(
@employeeId         int,
@companyId          int
)  AS
BEGIN
 SELECT firstName,lastName,gender,address
 FROM employee et
 WHERE et.employeeId = @employeeId and et.companyId = @companyId
END


2- Create Entity
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class EmployeeDetails {
    @Id
    private String firstName;
    private String lastName;
    private String gender;
    private String address;
 }


3- Create Repository
public interface EmployeeDetailsRepository extends 
JpaRepository<EmployeeDetails,String> {
@Query(value = "EXEC dbo.getEmployeeDetails @employeeId=:empId, 
                                          @companyId=:compId",nativeQuery =true)
List<EmployeeDetails> getEmployeeList(@Param("employeeId") Integer empId, 
                                      @Param("companyId") Integer compId);
}

4- create Controller
@CrossOrigin(origins = "*")
@RestController
@RequestMapping(value = "/api/employee")
public class EmployeeController {

@Autowired
private EmployeeDetailsRepository empRepo;

@GetMapping(value = "/details")
public ResponseEntity<List<EmployeeDetails>> getEmployeeDetails(@RequestParam 
            String empId, @RequestParam String compId) {
try {
   List<EmployeeDetails> result = empRepo.getEmployeeList(
                                Integer.valueOf(empId),Integer.valueOf(compId));
        return ResponseEntity.status(HttpStatus.OK).body(result);
    }
    catch (Exception ex)
    {
        return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(null);
    }
}
}

现在您可以调用http://localhost:8080/api/employee/details?empId=1&compId=25

0

尝试这段代码：

return em.createNativeQuery("{call getEmployeeDetails(?,?)}",
                               EmployeeDetails.class)           
                               .setParameter(1, employeeId)
                               .setParameter(2, companyId).getResultList();
0

如果您对使用JPA或JDBC调用此特定过程不是太依赖，您可以使用jOOQ——一个第三方库，它为所有您的存储过程生成存根来简化调用它们，并使调用类型安全。

调用返回未指定游标的过程

在您的特定情况下，该过程返回一个未命名的、未声明的游标（它可能返回多个游标和交错的更新计数）。因此，您可以使用jOOQ这样调用该过程：

GetEmployeeDetails proc = new GetEmployeeDetails();
proc.setEmployeeId(1);
proc.setCompanyId(2);
proc.execute(configuration);

// Iterate over potentially multiple results
for (Result<?> result : proc.getResults()) {

    // Print the first result set (your employee query)
    System.out.println(result);

    // Use your implicit knowledge of the content of the query
    // Without type safety
    for (Record record : result) {

        // All tables / columns are also generated
        System.out.println("First name: " + record.get(EMPLOYEE.FIRSTNAME));
        System.out.println("Last name: " + record.get(EMPLOYEE.LASTNAME));
        System.out.println("Gender: " + record.get(EMPLOYEE.GENDER));
        System.out.println("Address: " + record.get(EMPLOYEE.ADDRESS));
    }
}

使用实际的表值函数

个人而言，我并不喜欢一些关系型数据库管理系统（包括SQL Server、MySQL）返回任意未分类游标的功能。为什么不声明结果类型呢？SQL Server有强大的表值函数。例如，在这里使用以下语法：

CREATE FUNCTION getEmployeeDetails (@employeeId int, @companyId int)
RETURNS TABLE
AS RETURN
  SELECT
    firstName,
    lastName,
    gender,
    address
  FROM employee et
  WHERE et.employeeId = @employeeId
  AND et.companyId = @companyId

现在，您的目录中已经有了与此函数相关的完整类型信息，如果您仍在使用jOOQ，则该信息将可用于代码生成器，因此您可以像这样调用函数：

for (GetEmployeeDetailsRecord record : ctx.selectFrom(getEmployeeDetails(1, 2))) {
    System.out.println("First name: " + record.getFirstName());
    System.out.println("Last name: " + record.getLastName());
    System.out.println("Gender: " + record.getGender());
    System.out.println("Address: " + record.getAddress());
}

声明：我是 jOOQ 背后公司的员工

0

要调用存储过程，我们可以使用 java.sql 包中的 Callable Statement。

感谢您的回复。因此，可调用语句的SQL将是{ ? = call getEmployeeDetails (?,?) }还是需要指定所有输出参数？ - user431514
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接