You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Teresa Kan (JIRA)" <ji...@apache.org> on 2007/06/08 16:12:26 UTC
[jira] Created: (OPENJPA-254) Bulk update did not work on an entity
that is mapped to multiple tables
Bulk update did not work on an entity that is mapped to multiple tables
-----------------------------------------------------------------------
Key: OPENJPA-254
URL: https://issues.apache.org/jira/browse/OPENJPA-254
Project: OpenJPA
Issue Type: Bug
Affects Versions: 0.9.7
Environment: Window XP, Java SE
Reporter: Teresa Kan
I have an entity that is mapped to two tables:
@Entity(name="Dog2Table")
@Table(name="DOG1TABLE")
@SecondaryTable(name="DOG2TABLE",pkJoinColumns={
@PrimaryKeyJoinColumn(name="DOG_STORE", referencedColumnName="datastoreid"),
@PrimaryKeyJoinColumn(name="DOG_ID", referencedColumnName="id2")})
@Id
private int id2;
@Id
private int datastoreid;
private String name;
private float price;
@Column(table="DOG2TABLE")
private boolean domestic;
I experienced two issues in the bulk updates:
(1) If I only updated the name, the bulk updates worked. The sql statement was generated as:
UPDATE DOG1TABLE SET name = ? [params=(String) UpdateDog]
However, when I try to query the updates back in a new transaction, the old dog names were returned (Dog1 and Dog2). I looked at the database, the names were updated with "UpdateDog" correclty.
Here is the bulk Update code:
em.getTransaction().begin();
Query updateqry = em.createQuery("Update Dog2Table d Set d.name = :dogname");
updateqry.setParameter("dogname", "UpdateDog");
int updates = updateqry.executeUpdate();
em.getTransaction().commit();
em.getTransaction().begin();
em.flush();
Query qryx = em.createQuery("select d from Dog2Table d where d.datastoreid=12"); // same result if I didn't use the wehre clause)
List resultx = qryx.getResultList();
for (int index = 0; index < resultx.size(); index++)
{
Dog2Table dog4 = (Dog2Table)resultx.get(index);
System.out.println("Dog"+index+" name = " +dog4.getName());
}
em.getTransaction().commit();
If I created a new EntityManager to do the query, then it returned the "UpdateDog" names. I don't think this behavior is correct. I should be able to query the updated value in a new transaction. I looked at the trace output, the select statement was executed in database, but somehow, the getResultList ()retrieved the old values -- may be from the cache??.
3406 TestDog TRACE [main] openjpa.Query - Executing query: select d from Dog2Table d where d.datastoreid=12
3406 TestDog TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 246288046> executing prepstmnt 1041645078 SELECT t0.datastoreid, t0.id2, t1.domestic, t0.name, t0.price FROM DOG1TABLE t0 INNER JOIN DOG2TABLE t1 ON t0.datastoreid = t1.DOG_STORE AND t0.id2 = t1.DOG_ID WHERE (CAST(t0.datastoreid AS BIGINT) = CAST(? AS BIGINT)) [params=(long) 12]
3422 TestDog TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 246288046> [16 ms] spent
(2) If I updated the name and domestic fields, then I got the sqlcode -204 because the sql statement was generated incorrectly. The bulk updates statements were:
Query updateqry = em.createQuery("Update Dog2Table d Set d.name = :dogname, d.domestic=:dom");
updateqry.setParameter("dogname", "UpdateDog");
updateqry.setParameter("dom",true);
int updates = updateqry.executeUpdate();
The sql statement was generated:
3219 TestDog TRACE [main] openjpa.Query - Executing query: [Update Dog2Table d Set d.name = :dogname, d.domestic=:dom] with parameters: {dogname=UpdateDog, dom=true}
3234 TestDog TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1860202208> executing prepstmnt 1168917932 UPDATE DOG1TABLE SET domestic = ?, name = ? [params=(int) 1, (String) UpdateDog]
3234 TestDog TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1860202208> [0 ms] spent
Therefore, I got the sqlcode -204:
Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: DOMESTIC {prepstmnt 1168917932 UPDATE DOG1TABLE SET domestic = ?, name = ? [params=(int) 1, (String) UpdateDog]} [code=-206, state=42703]
at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
Beacuse the domestic field was not on the DOG1TABLE, it was on the DOG2TABLE.
I expected two SQL statements to be generated:
UPDATE DOG1TABLE SET name = ? [params=(String) UpdateDog]
UPDATE DOG2TABLE SET domestic = ? [params=(int) 1]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-254) Bulk update did not work on an entity
that is mapped to multiple tables
Posted by "Patrick Linskey (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-254?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Patrick Linskey updated OPENJPA-254:
------------------------------------
Issue Type: Sub-task (was: Bug)
Parent: OPENJPA-290
> Bulk update did not work on an entity that is mapped to multiple tables
> -----------------------------------------------------------------------
>
> Key: OPENJPA-254
> URL: https://issues.apache.org/jira/browse/OPENJPA-254
> Project: OpenJPA
> Issue Type: Sub-task
> Affects Versions: 0.9.7
> Environment: Window XP, Java SE
> Reporter: Teresa Kan
>
> I have an entity that is mapped to two tables:
> @Entity(name="Dog2Table")
> @Table(name="DOG1TABLE")
> @SecondaryTable(name="DOG2TABLE",pkJoinColumns={
> @PrimaryKeyJoinColumn(name="DOG_STORE", referencedColumnName="datastoreid"),
> @PrimaryKeyJoinColumn(name="DOG_ID", referencedColumnName="id2")})
> @Id
> private int id2;
> @Id
> private int datastoreid;
> private String name;
> private float price;
> @Column(table="DOG2TABLE")
> private boolean domestic;
> I experienced two issues in the bulk updates:
> (1) If I only updated the name, the bulk updates worked. The sql statement was generated as:
> UPDATE DOG1TABLE SET name = ? [params=(String) UpdateDog]
> However, when I try to query the updates back in a new transaction, the old dog names were returned (Dog1 and Dog2). I looked at the database, the names were updated with "UpdateDog" correclty.
> Here is the bulk Update code:
> em.getTransaction().begin();
> Query updateqry = em.createQuery("Update Dog2Table d Set d.name = :dogname");
> updateqry.setParameter("dogname", "UpdateDog");
> int updates = updateqry.executeUpdate();
> em.getTransaction().commit();
> em.getTransaction().begin();
> em.flush();
> Query qryx = em.createQuery("select d from Dog2Table d where d.datastoreid=12"); // same result if I didn't use the wehre clause)
> List resultx = qryx.getResultList();
> for (int index = 0; index < resultx.size(); index++)
> {
> Dog2Table dog4 = (Dog2Table)resultx.get(index);
> System.out.println("Dog"+index+" name = " +dog4.getName());
> }
> em.getTransaction().commit();
> If I created a new EntityManager to do the query, then it returned the "UpdateDog" names. I don't think this behavior is correct. I should be able to query the updated value in a new transaction. I looked at the trace output, the select statement was executed in database, but somehow, the getResultList ()retrieved the old values -- may be from the cache??.
> 3406 TestDog TRACE [main] openjpa.Query - Executing query: select d from Dog2Table d where d.datastoreid=12
> 3406 TestDog TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 246288046> executing prepstmnt 1041645078 SELECT t0.datastoreid, t0.id2, t1.domestic, t0.name, t0.price FROM DOG1TABLE t0 INNER JOIN DOG2TABLE t1 ON t0.datastoreid = t1.DOG_STORE AND t0.id2 = t1.DOG_ID WHERE (CAST(t0.datastoreid AS BIGINT) = CAST(? AS BIGINT)) [params=(long) 12]
> 3422 TestDog TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 246288046> [16 ms] spent
> (2) If I updated the name and domestic fields, then I got the sqlcode -204 because the sql statement was generated incorrectly. The bulk updates statements were:
> Query updateqry = em.createQuery("Update Dog2Table d Set d.name = :dogname, d.domestic=:dom");
> updateqry.setParameter("dogname", "UpdateDog");
> updateqry.setParameter("dom",true);
> int updates = updateqry.executeUpdate();
> The sql statement was generated:
> 3219 TestDog TRACE [main] openjpa.Query - Executing query: [Update Dog2Table d Set d.name = :dogname, d.domestic=:dom] with parameters: {dogname=UpdateDog, dom=true}
> 3234 TestDog TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1860202208> executing prepstmnt 1168917932 UPDATE DOG1TABLE SET domestic = ?, name = ? [params=(int) 1, (String) UpdateDog]
> 3234 TestDog TRACE [main] openjpa.jdbc.SQL - <t 1094861122, conn 1860202208> [0 ms] spent
> Therefore, I got the sqlcode -204:
> Caused by: org.apache.openjpa.lib.jdbc.ReportingSQLException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: DOMESTIC {prepstmnt 1168917932 UPDATE DOG1TABLE SET domestic = ?, name = ? [params=(int) 1, (String) UpdateDog]} [code=-206, state=42703]
> at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:191)
> Beacuse the domestic field was not on the DOG1TABLE, it was on the DOG2TABLE.
> I expected two SQL statements to be generated:
> UPDATE DOG1TABLE SET name = ? [params=(String) UpdateDog]
> UPDATE DOG2TABLE SET domestic = ? [params=(int) 1]
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.