CODEWITHSUNDEEP
javaspringoracle-databaseprocedure
njnu_zhoubo
njnu_zhoubo

Reputation: 15

StoredProcedureQuery multiple output type

I have a procedure which have 2 output parameter, varchar2 and ref_cursor

PROCEDURE login_check (id_in         IN     edit_users.userid%TYPE,
                      password_in   IN     edit_users.usr_password%TYPE,
                      status_out       OUT VARCHAR2,
                      cur_out          OUT SYS_REFCURSOR
                     )

I defined a namedStoredProcedureQuery

  @NamedStoredProcedureQuery(name = "login_check",
            procedureName = "edit_maintenance.login_check",
    parameters = {
            @StoredProcedureParameter(mode = ParameterMode.IN, name ="id_in",type = String.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, name ="password_in",type = String.class),
            @StoredProcedureParameter(mode = ParameterMode.OUT, name ="status_out",type = String.class),
            @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name ="cur_out", type = void.class)

    })

when I called this procedure by storedProcedureQuery, I can get the "status_out",but I got a nullpoint exception when called the getResultList method.

 StoredProcedureQuery storedProcedureQuery = em.createNamedStoredProcedureQuery("login_check");
    storedProcedureQuery.setParameter("id_in",username);
    storedProcedureQuery.setParameter("password_in",password);
    storedProcedureQuery.execute();
    String status = (String)storedProcedureQuery.getOutputParameterValue("status_out");
    System.out.println(status);
    List<UserTo> userToList = storedProcedureQuery.getResultList();
    return userToList.get(0);

ava.lang.NullPointerException
at org.hibernate.loader.custom.JdbcResultMetadata.<init>(JdbcResultMetadata.java:32)
at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:476)
at org.hibernate.result.internal.OutputsImpl$CustomLoaderExtension.processResultSet(OutputsImpl.java:285)
at org.hibernate.result.internal.OutputsImpl.extractResults(OutputsImpl.java:136)
at org.hibernate.procedure.internal.ProcedureOutputsImpl.access$500(ProcedureOutputsImpl.java:26)
at org.hibernate.procedure.internal.ProcedureOutputsImpl$ProcedureCurrentReturnState.lambda$buildExtendedReturn$0(ProcedureOutputsImpl.java:95)
at org.hibernate.result.internal.ResultSetOutputImpl.getResultList(ResultSetOutputImpl.java:41)
at org.hibernate.procedure.internal.ProcedureCallImpl.getResultList(ProcedureCallImpl.java:756)
at com.schenker.editrade.repository.UserRepositoryImpl.loginCheck(UserRepositoryImpl.java:23)
at com.schenker.editrade.repository.UserRepositoryImpl$$FastClassBySpringCGLIB$$61af36ef.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)

Upvotes: 1

Views: 1195

Answers (1)

Elyor Murodov
Elyor Murodov

Reputation: 1028

Looks like you are getting this exception, because the metadata for your cursor output is missing. You can try using an appropriate Java type for your cursor output instead of "void.class".

According to the documentation here, cursors can be mapped to either of the following Java types:

  • java.sql.ResultSet
  • sqlj.runtime.ResultSetIterator

For example, you can try this way:

@StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, name ="cur_out", type = java.sql.ResultSet.class)

Upvotes: 1

Related Questions