如何从java类中获取用户定义的sql过程输出参数 [英] how to get userdefined sql procedure out parameter from java class

问题描述

我有一个具有 3 个 IN 和 1 个 OUT 参数的 sql 过程.其中 OUT 参数具有用户定义的数据类型，这意味着它是一种表类型，所以我想从java类中获取这个表类型的输出.我试图通过创建一个实现了 java.sql.Struct 的 java 类来实现这一点，并通过 CallableStatement registerOutParameter 作为 java.sql 在一个支持 bean 中使用它.Struct type ，但有错误.

I have one sql procedure having 3 IN and 1 OUT parameter. In which the OUT parameter has user defined datatype that means it is one type of table, so I want to get this table type output from java class. I tried to get this by making one java class which is implemented java.sql.Struct and used it in one backing bean by CallableStatement registerOutParameter as java.sql.Struct type , but got errors.

实际上下面是我想要解决方案的情况.

Actually the below is the situation for which I want the solution.

就我而言，我想保存 500 条或更多条记录，通过 Java 类一一保存.但是我想要 Oracle 进程，在该进程中，如果我们传递一些参数以将数据插入表中，那么该过程会一一插入所有数据，如果在任何位置发生任何错误，那么该记录将进入一个用户定义的表类型对象，使用管道行().所以我想使用java类获取用户定义的tabletype对象.

In my case I want to save 500 or more records saving one by one by Java class. But I want the Oracle process instead in which if we pass some parameter for inserting the data into table then the procedure inserts all data one by one and by doing that if any error occure at any position then that record goes into one userdefined tabletype object using pipe row(). So I want to get the userdefined tabletype object using java class.

您对此类问题有什么想法吗?

Do you have any idea for this type of problem?

推荐答案

您可以定义多种 oracle 对象类型.在 oracle 对象的情况下，它们被映射到 java.sql.Struct.对于集合(如您所引用的表类型，据我所知)它们被映射到 java.sql.Array.只需将您的输出参数注册为 java.sql.Array.如果是较旧的 oracle JDBC 驱动程序 (10g)，请记住使用对类型(包括架构)的完整引用，否则您可能会收到诸如无效类型"之类的错误:

You can define several oracle object types. In case of oracle objects, they are mapped to java.sql.Struct. In case of collections (like table type you are referencing, as far as I understand) they are mapped to java.sql.Array. Just register your out parameter as java.sql.Array. In case of older oracle JDBC drivers (10g) remember to use full reference to the type (including schema), otherwise you might get errors like "invalid type" or something:

示例:

stmt.registerOutParameter(4, Types.ARRAY, "SCHEMA.TABLE_TYPE");
stmt.execute();
Array array = stmt.getArray(4);

如果你有很多这样的代码，你可以从eclipselink的方法中受益:

If you have a lot of code like this, you can benefit from eclipselink's approach:

@Entity
@NamedStoredProcedureQueries({
    @NamedStoredProcedureQuery(
            name = "Company.getCompanies",
            procedureName = "SQL_PACKAGE.GET_COMPANIES",
            parameters = { 
                    @StoredProcedureParameter(queryParameter = "p_filter", direction = Direction.IN), 
                    @StoredProcedureParameter(queryParameter = "p_result", direction = Direction.OUT_CURSOR),
                    },
            resultClass = Company.class)
})
public class Company {

    @Id
    @Column(name = "COMPANY_NO")
    private Long companyNo;

    @Column(name = "COMPANY_NAME")
    private String companyName;

    public Long getCompanyNo() {
        return companyNo;
    }

    public String getCompanyName() {
        return companyName;
    }

}

然后在 DAO 中使用这样的实体:

And then use such an entity in DAO:

@Transactional(readOnly = true)
public List<Company> getCompanies(String filter) {
    EntityManager em = entityManagerProvider.get();
    Query query = em.createNamedQuery("Company.getCompanies");
    query.setParameter("p_filter", filter);
    return query.getResultList();
}

在这种情况下，您不必为输出定义特殊的 oracle 集合类型，只需在 PL/SQL 中返回 ref cursor.

In such a scenario you don't have to define special oracle collection type for output, just return ref cursor in PL/SQL.

这篇关于如何从java类中获取用户定义的sql过程输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！