Issue with a JPA query

Question

I am trying to execute the following JPA query:

    public static final String UPDATE_INVENTORY_CUSTOMER_FOR_AMS_MAPPING = "UPDATE Inventory inventory SET" 
    + " inventory.customer.id = :" + DataAccessConstants.PARAM_CUSTOMER_ID
    + " ,inventory.lastUpdateUserId = :" + DataAccessConstants.PARAM_USER_ID
    + " where inventory.amsConsignorName = :" + DataAccessConstants.PARAM_AMS_CONSIGNOR_NAME
    + " and inventory.amsConsignorOrgCd = :" + DataAccessConstants.PARAM_AMS_CONSIGNOR_ORG_CD
    + " and inventory.amsConsignorTypeName = :" + DataAccessConstants.PARAM_AMS_CONSIGNOR_TYPE
    + " and inventory.status.code in (:" + DataAccessConstants.PARAM_STATUS + ")";  

but it is seeing the following:

update ATL_INVENTORY,  set CONSIGNOR_ID=?, LAST_UPDATE_USER_ID=? where AMS_CONSIGNOR_NAME=? and AMS_CONSIGNOR_ORG_CD=? and AMS_CONSIGNOR_TYPE_NAME=? and (CODE in (? , ? , ? , ?))

Any ideal as to why there is a comma after the table name?

---

Solution

I had to change the original query to the following:

update Inventory inv set " 
    + "inv.customer.id = :" + DataAccessConstants.PARAM_CUSTOMER_ID + " "
    + "where inv.amsConsignorName =:" + DataAccessConstants.PARAM_AMS_CONSIGNOR_NAME + " "
    + "and inv.amsConsignorOrgCd =:" + DataAccessConstants.PARAM_AMS_CONSIGNOR_ORG_CD + " "
    + "and inv.amsConsignorTypeName =:" + DataAccessConstants.PARAM_AMS_CONSIGNOR_TYPE + " "
    + "and exists(select 1 from Code code where inv.status = code and code.code in (:" + DataAccessConstants.PARAM_STATUS + "))

Which then produced this:

update ATL_INVENTORY set CONSIGNOR_ID=? where AMS_CONSIGNOR_NAME=? and AMS_CONSIGNOR_ORG_CD=? and AMS_CONSIGNOR_TYPE_NAME=? and (exists (select 1 from ATL_CODE code1_ where ATL_INVENTORY.STATUS=CODE_ID and (code1_.CODE in (? , ? , ? , ?))))

Based on a clarification located here: Incorrect SQL generated for JPA QL Update statement involving multiple entities

Answer 1

Your query is code as UPDATE Inventory inventory SET, but the generated SQL says update ATL_INVENTORY, set. Why is the literal SQL string not what you coded? When I encounter mysteries like this, they're usually caused by assuming that one thing is being done when in fact another is in play.

This suggests that the SQL you coded isn't being used to generate that SQL the way you're assuming. See where else this query might be coming from. I'd bet that the real source has a misplaced comma in it.

Which JPA implementation are you using? If I'm incorrect about a bad assumption, it says that there's a bug in the implementation. Have you used it before? Have you had success with UPDATE? If yes, it's definitely buried somewhere in your code base.

You have an interface with a bunch of constants in it. Personally, I don't care for a design like that. It's an anti-pattern with a name.

Answer 2

A bug in the JPA provider is very unlikely in my opinion so, as @duffymo said, are you sure you're using the right constant, that the code or maybe dependencies are up-to-date? I'd dig in that direction.

That being said, I really wonder why you're not using named queries (that are most of time pre-compiled by the persistence implementation at deployment time), either in the Java code or in meta-data mapping files (the fun part is that people didn't find having EJB-QL queries externalized in XML very manageable in EJB 2.x, hence the @NamedQuery annotation of JPA).