Can I use NamedStoredProcedureQuery annotation?

Support
#1

Hi:
I’m trying to call an Oracle stored procedure where the first parameter is an in-out ref cursor. Docs I’ve read on the web suggest using @NamedStoredProcedureQuery and then using createNamedStoredProcedureQuery to access it. However, there doesn’t seem to be a createNamedStoredProcedureQuery method on the EntityManager. Here’s my code:


@NamedStoredProcedureQuery(
        name = "bad_debt_ref",
        procedureName = "bad_debt_ref",
        resultClasses = BadDebt.class,
        parameters = {
                @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class)
        }
)

@Service(BadDebtorService.NAME)
public class BadDebtorServiceBean implements BadDebtorService {

    @Inject
    private Persistence persistence;

    @Override
    public List<BadDebt> check_bad_debt(String lname, String postalCode) {
        List<BadDebt> result = null;
        try (Transaction tx = persistence.getTransaction()) {
            EntityManager em = persistence.getEntityManager();
            result = em.createNamedStoredProcedureQuery("bad_debt_ref")
                    .setParameter(2, lname)
                    .setParameter(3, postalCode)
                    .getResultList();
        }
        return result;
    }
}

The line


em.createNamedStoredProcedureQuery("bad_debt_ref")

won’t compile. The method doesn’t exist on the EntityManager object.

How should I do this instead?

#2

Hi,

The reason it does not compile is that in cuba middlware you don’t interact directly with the javax.persistence.EntityManager but with a cuba wrapper of it. The wrapper just don’t have this method. You can either call getDelegate on it to get the JPA entity manager and call the method on that.

Or use this approach instead: Use stored procedure MSSQL - CUBA.Platform

Bye Mario

#3

Please use Persistence and EntityManager interfaces only from the com.haulmont.cuba.core package. The underlying JPA mechanisms cannot be used directly in CUBA applications.

#4

Ok, then what is the best way to provide an IN-OUT ref cursor to call an Oracle stored procedure using createNativeQuery? It is the first argument of my stored procedure. I couldn’t figure out how to do it and I couldn’t see any example for it in the link provided. That’s why I switched to NamedStoredProcedureQuery, where I can specify the parameter types.

#5

Hi Eric,

com.haulmont.cuba.core.EntityManager has getDelegate() method which returns standard JPA 2.1 javax.persistence.EntityManager. You can use it for reading operations, though some platform mechanisms will be bypassed (e.g. query cache). We haven’t tested it with stored procedures, but it should work. Please let us know about results.

#6

Just to keep you updated, I can’t get the system to find the stored procedure call. I get the error:


IllegalArgumentException: NamedQuery of name: bad_debt_ref not found.

I first had the StoredProcedureQuery definition in my Custom Datasource Service. I then tried moving it to the non-persistent Entity I created to hold the result list. Neither one worked. I am now going to try programming the whole thing in procedure calls as described in this link.

I will post again with further info. As always, please jump in and give suggestions! :slight_smile:

#7

So, here’s another question for you. I have declared a non-persistent entity (implements AbstractNotPersistentEntity) for the data returned by my stored procedure. I read this on the web where the explanation was that the annotations won’t get processed if the entity is not within the persistence unit.

Is the problem that my entity is NOT persistent? Is there a way to get the @NamedStoredProcedureQuery annotation processed anyway? How can I tell IF it is being processed?

#8

ORM is unaware of CUBA mechanisms such as datasources or non-persistent entities. So try to place your annotation on any persistent entity. I think it may be not related to the procedure, it’s just a place where ORM can find your definition.

#9

Thanks, but that is something I already tried. :wink: It did not help. In any event, I gave up on the NamedStoredProcedureQuery annotation and found a method that worked. Here is the final code:


package com.paslists.rade.service;

import com.haulmont.cuba.core.EntityManager;
import com.haulmont.cuba.core.Persistence;
import com.haulmont.cuba.core.Transaction;
import com.paslists.rade.entity.BadDebt;
import org.springframework.stereotype.Service;
import javax.inject.Inject;
import javax.persistence.ParameterMode;
import javax.persistence.StoredProcedureQuery;
import java.math.BigDecimal;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

@Service(BadDebtorService.NAME)
public class BadDebtorServiceBean implements BadDebtorService {

    @Inject
    private Persistence persistence;

    @Override
    public Set<BadDebt> check_bad_debt(String lname, String postalCode) {
        Set<BadDebt> retval = new HashSet<>();

        try (Transaction tx = persistence.getTransaction()) {
            EntityManager em = persistence.getEntityManager();

            StoredProcedureQuery storedProcedure =
                  em.getDelegate().createStoredProcedureQuery("RESPFORM_PKG.BAD_DEBTOR_REF")
                          .registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR)
                          .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
                          .registerStoredProcedureParameter(3, String.class, ParameterMode.IN)
                          .setParameter(2, lname)
                          .setParameter(3, postalCode);

            List<Object[]> result = storedProcedure.getResultList();

            for (Object[] row : result) {
                BadDebt badDebt = new BadDebt();
                badDebt.setCusId( (BigDecimal) row[0]);
                badDebt.setFname( (String) row[1]);
                badDebt.setLname( (String) row[2]);
                badDebt.setAddress1( (String) row[3]);
                badDebt.setAddress2( (String) row[4]);
                badDebt.setCity( (String) row[5]);
                badDebt.setProvince( (String) row[6]);
                badDebt.setPostalCode( (String) row[7]);
                badDebt.setCountryCode( (String) row[8]);
                retval.add(badDebt);
             }
        }
        return retval;
    }
}

I learned that I could set the types of the parameters, including the REF_CURSOR parameter, while creating the StoredProcedureQuery object. Then I discovered I had to unpack the resulting List<Object []> into my non-persistent entity objects.

So, we can probably call this one solved. More issues to come! :slight_smile:

#10

@ericraskin Thanks for posting your solution, I was looking exactly for that. I got rid of my non persistent entity though and was able to generate entities using annotations.

I am almost happy but have a problem with relations to other entities. For example I got an order entity with a relation to customer and a total price. When I load the orders with the procedure CUBA/JPA creates order entities but is only setting the total price but leaves the customer at null. The reason for this is because the customer attribute is set so lazy loading and if I use eager loading it works, but I don’t need the whole customer entity, I just need the customer.id filled so that lazy loading can be invoked later. Right now on saving those order entities I get an error “Cannot get unfetched attribute from detached object”.

Long story short: did you encounter that problem or were you able to use views with stored procedures? Thanks for any hint! :slight_smile:

#11

I believe your problem is that, using this approach, we are bypassing the Cuba cache system entirely and calling the underlying JPA Entitymanager directly (getdelegate() call). So, lazy loading won’t work because Cuba isn’t doing the work.

At least, that’s how I understand it.