The original request (JPA) does not reset and returns the same old result

Question

I have my own SQL query:

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 cycle, it works correctly, but any cycle after the first returns the same result to the first, I debug it, the parameter is changed, it works correctly if I change

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

to

 Query query = em.createNativeQuery(queryString); 

Relationship Wish79

Answer 1

Each JPA must have a primary key. Your JPA objects may not display the primary key correctly, if any, in the database table.

I ran into the same problem. In my model class, I only had one class variable annotated with @Id. However, this was not an exact reflection of the table itself, which has a composite primary key. Thus, my query results returned the correct number of rows, but each of them contained the same values, although the actual data was different in 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 lines, each of which shows the code 'A'. But actually 9 of these 10 lines had a different code value ("B" or "C"). It seemed that the results were cached and / or the tbl.code predicate was ignored. (This happened if 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 selection criteria are no longer ignored.

Edit: although the above worked for me, further research seems to be the best approach for setting up a separate JPA Entity primary key class. See http://docs.oracle.com/cd/E16439_01/doc.1013/e13981/cmp30cfg001.htm .

For example, here is the Entity class with a built-in 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 is the composite class of the primary key (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; } ...