Native Query (JPA ) not reset and return the same old result

Question

I have a native sql query as the following :

for (init i=0; i<=2 ; i++) {
    String sql = "Select * from accounts where id = ?"; 
    Query query = em.createNativeQuery(sql,AccountBean.class); 
    query.setParameter(1, i ); 

    AccountBean accountBean = (AccountBean)query.getSingleResult(); 
} 

For the first loop it works correctly but any loop after the first one returns the same result as the first one , i debug it, the parameter changed , it works correctly if i change

Query query = em.createNativeQuery(sql,AccountBean.class); 

to

Query query = em.createNativeQuery(queryString); 

Regards Wish79

Answer 1

Every JPA entity must have a primary key. Your JPA entities may not properly reflect the primary key, if any, on the database table.

I ran into the same problem. In my model class I had only one class variable annotated with @Id. However, that was not an accurate reflection of the table itself, which has a composite primary key. Thus, my query results returned the correct number of rows, but each confoundingly contained the same values, even though the actual data was different in the db. For example, this query:

Query query = entityManager.createQuery
("SELECT tbl FROM Tbl tbl WHERE tbl.id = 100 
  and tbl.code in ('A','B','C')");

...returned 10 rows, each showing a code of 'A'. But in actuality 9 of those 10 rows had a different code value ('B' or 'C'). It seemed as if the results were being cached and/or the tbl.code predicate was ignored. (That happened whether I used JPQL or Native SQL.) Very confusing.

To fix this I added an additional @Id annotation to my model to reflect the composite primary key:

@Id
@Column(name = "Code")
public String getCode() {
    return this.code;
}

Now the query returns the data correctly and the code select criteria is no longer effectively ignored.

Edit: Although the above worked for me, on further research it seems a better approach to configure a separate JPA Entity composite primary key class. See http://docs.oracle.com/cd/E16439_01/doc.1013/e13981/cmp30cfg001.htm.

For example, here's an Entity class with an embedded primary key (see @EmbeddedId):

/**
 * The persistent class for the SOME_TABLE database table.
 */
@Entity
@Table(name = "SOME_TABLE")
public class SomeTable implements Serializable {

    @EmbeddedId
    private SomeTablePk id;

    @Column(name = "NUMBER_HRS")
    private BigDecimal numberHrs; 
    ...

...and here's the composite primary key class (see @Embeddable):

@Embeddable
public class SomeTablePk implements Serializable {

    @Column(name = "SOME_ID")
    private String someId;

    @Column(name = "ANOTHER_ID")
    private BigDecimal anotherId;

    public String getSomeId() {
        return someId;
    }
    ...