如何在Spring Boot+Hibernate中调用带自定义Oracle对象参数的存储过程

Got it, let's work through this problem together. You've got an Oracle custom person_type object and a stored procedure that accepts it as input, and you want to call this from Spring Boot using Hibernate without relying on a corresponding database table. Here's a step-by-step solution:

Step 1: Map the Oracle Object to a Java Class #

First, we need to create a Java class that mirrors your Oracle person_type object. Hibernate's @Struct annotation (available in Hibernate 5.2+) lets us map directly to an Oracle OBJECT type without needing a table.

import jakarta.persistence.Embeddable;
import org.hibernate.annotations.Struct;

@Embeddable
@Struct(name = "PERSON_TYPE", schema = "YOUR_SCHEMA") // Replace YOUR_SCHEMA with your actual Oracle schema
public class PersonType {
    private Long id;
    private String name;
    private Integer age;

    // Required: Hibernate needs a no-arg constructor
    public PersonType() {}

    // Convenience constructor for creating instances
    public PersonType(Long id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    // Getters and Setters
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public Integer getAge() { return age; }
    public void setAge(Integer age) { this.age = age; }
}

• Note: If you're using an older Spring Boot version with JPA 2.x, replace jakarta.persistence with javax.persistence.
• Double-check the name and schema in @Struct to match your Oracle object's actual name and schema (Oracle is case-sensitive here if you created the object with quoted identifiers).

Step 2: Call the Stored Procedure #

There are two straightforward ways to call the stored procedure using Hibernate/EntityManager.

Option 1: Using EntityManager (JPA Standard) #

This is the most portable approach since it uses JPA's standard API.

import jakarta.persistence.EntityManager;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class PersonService {

    private final EntityManager entityManager;

    // Constructor injection (preferred over field injection)
    public PersonService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Transactional
    public void addPerson(PersonType person) {
        // Create a stored procedure query for "ADD_PERSON"
        var procedureQuery = entityManager.createStoredProcedureQuery("ADD_PERSON");

        // Register the input parameter: name matches the procedure's IN parameter, type is our PersonType
        procedureQuery.registerStoredProcedureParameter(
            "IN_PERSON",
            PersonType.class,
            jakarta.persistence.ParameterMode.IN
        );

        // Set the parameter value
        procedureQuery.setParameter("IN_PERSON", person);

        // Execute the procedure
        procedureQuery.execute();
    }
}

Option 2: Using Hibernate Session API (More Control) #

If you want to use Hibernate's native API for more flexibility:

import org.hibernate.Session;
import org.hibernate.procedure.ProcedureCall;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class PersonService {

    private final EntityManager entityManager;

    public PersonService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Transactional
    public void addPerson(PersonType person) {
        // Unwrap EntityManager to get Hibernate Session
        Session session = entityManager.unwrap(Session.class);
        
        // Create a procedure call for "ADD_PERSON"
        ProcedureCall procedureCall = session.createStoredProcedureCall("ADD_PERSON");

        // Register and bind the input parameter
        procedureCall.registerParameter("IN_PERSON", PersonType.class, jakarta.persistence.ParameterMode.IN)
                     .bindValue(person);

        // Execute the procedure
        procedureCall.execute();
    }
}

Critical Notes to Avoid Issues #

• Fix Your Stored Procedure: I noticed a typo in your procedure code: VALUES(in_person.id, in_person.name, person.age); should be in_person.age instead of person.age—this will cause an error otherwise.
• JDBC Driver Compatibility: Use Oracle JDBC driver 8 (ojdbc8) or higher. Older drivers have limited support for custom OBJECT types with Hibernate.
• Field Name Matching: Ensure the Java class field names exactly match the Oracle object's attribute names (case matters if your Oracle object uses case-sensitive names). If needed, use @Column(name = "ATTRIBUTE_NAME") to explicitly map fields.
• Transaction Management: The @Transactional annotation is required here since we're executing a write operation (INSERT) via the stored procedure.

内容的提问来源于stack exchange，提问作者Iwaneez