About modifying an entity returned from a native query

[Himai Minh]

In , in chapter 11 of JPA, p.310,

One thing to be careful of with SQL queries that return entities is that the resulting entity instances become managed by the persistence context,.....
If you leave out a field from the query, or default it to some value and then modify the resulting entity , there is a possibility that you will overwrite the correct version already stored in the DB.This is because the missing
state will be null (or some default value according to the type) in the entity. When the transaction commits, the persistence context does not know that the state was not properly read in from the query and might just attempt to write out null or the default value.

I tried to demo the statement in bold,
In sqlResultMapping example,

@Stateless
public class OrgStructure {
    @PersistenceContext(unitName="EmployeeService")
    EntityManager em;
    
    
    public List findAllEmployees() {
          //I leave out name field in this query
            Query query = em.createNativeQuery(
                "SELECT emp_id, salary, manager_id, dept_id, address_id " +
                "FROM EMP ", 
                "EmployeeResult");
      
       List resultset= query.getResultList();
       //I tried to change the first employee's name from Sara to Any_Name
       List resultset= query.getResultList();
       Employee managedEmployee =(Employee) resultset.get(0);
       managedEmployee.setName("Any_Name");
       return resultset;
    }
    

But when I checked MySQL database table emp, the first employee's name is successfully changed.
The issue  in the above statement does not happen.

Any other example to demo the above issue?
Thanks.

[L Foster]

Hello, Mimai:

That looks like an interesting question. I could not find that text in the JPA tutorial. Could this be from a book, such as "Pro JPA"?

Either way, the warning does not appear to be about failure to modify something you intend to.  Instead, it appears to be a warning about modifying things that you do not intend to.  I am going out on a limb slightly, here, because I have not explored this topic.  But, I think they are warning us that if we use native SQL to retrieve entities into the EntityManager, the EM won't be "smart enough" to populate things we failed to mention in our query.  If you want an example, you might try

          //I leave out name field in this query
            Query query = em.createNativeQuery(
                "SELECT emp_id, manager_id, dept_id, address_id " +
                "FROM EMP ", 
                "EmployeeResult");

this, which leaves out the salary as well.  Then try persisting that entity again, and see if Salary becomes a default or null value.

Please let me know how it goes.  I am quite curious.  Sorry if this is a goose chase, but at least it doesn't take much to try it.

BTW, I posted because I have gotten curious about JPA.  I tried it out about a year and a half ago, and used the Criteria API.  I recently heard that it can be used for some NoSQL databases, too.
I hope it helps.

[Himai Minh]

Hi, thanks for you suggestion. This example is from chapter 11 sqlresultmapping example in Pro JPA 2nd edition by Apress.
Here is what I did:

//In OrgStructure.java, the stateless bean
 public List findAllEmployees() {
 
        Query query = em.createNativeQuery(
                "SELECT emp_id,  manager_id, dept_id, address_id " +
                "FROM EMP ", 
                "EmployeeResult");
      
       List resultset= query.getResultList();
       Employee managedEmployee =(Employee) resultset.get(0);
       managedEmployee.setName("Any_Nice_Name");
       em.persist(managedEmployee);
      return resultset;  
    }

//In the Employee entity class
  public String toString() {
        return "Employee id: " + getId() + " name: " + getName() + " salary: "+ getSalary()+
               " with MgrId: " + (getManager() == null ? null : getManager().getId());
    }

The screen outputs salary = 0 for all employees.
But the DB still shows the original salaries for all the employees.

[L Foster]

Hi, Himai:
Well, that sort of blew my theory away.  At this point, there are two possibilities:
1. Hibernate is smarter than the spec--this would mean that the text of the book is claiming the spec (JPA) does not cover this case.  But, trying it out in real life, Hibernate works more robustly than that.  (would also explain "may" being used)  I think just my knowledge is not enough to confirm this is the case, however.
2. It could be that this is only a problem under the "update or delete" scenarios, as indicated in the text below from the Javadoc.

createNativeQuery

Query createNativeQuery(java.lang.String sqlString)

   Create an instance of Query for executing a native SQL statement, e.g., for update or delete.

   Parameters:
       sqlString - a native SQL query string
   Returns:
       the new query instance

Either way, I am very curious about this now.  Sorry I could not be of more help.