[jboss-user] [EJB 3.0] - JPQL WHERE syntax works for SELECT but not DELETE
I'm experiencing some unexpected behavior when I try to delete an entity in which a
JPQL WHERE condition behaves as expected for a SELECT query but not for a DELETE query.
Background: I'm using JBoss 4.0.5.GA in the ejb3 configuration with a MySQL 5.0 data
source. This problem involves three entities and one stateless session bean. The entities
are:
| @Entity
| public class Account implements Serializable {
| @Id
| @GeneratedValue(strategy = GenerationType.AUTO)
| private Long id;
|
| @OneToOne(cascade = CascadeType.ALL,fetch=FetchType.EAGER)
| @PrimaryKeyJoinColumn
| private Profile profile;
|
| ...
| }
|
| @Entity
| public class Profile {
| @Id
| @GeneratedValue(strategy = GenerationType.AUTO)
| private Long id;
|
| @OneToOne(cascade=CascadeType.ALL, fetch=FetchType.EAGER,
mappedBy="profile")
| private Account account;
|
| @Column(unique=true)
| private String email;
|
| ...
| }
|
| @Entity
| @Inheritance(strategy=InheritanceType.JOINED)
| public class Notification implements Serializable {
|
| @Id
| @GeneratedValue(strategy = GenerationType.AUTO)
| private Long id;
|
| @ManyToOne
| private Account recipient;
|
| ...
| }
|
The problem comes when I try to remove an instance of Notification while applying a filter
based on part of the user's login credential (specifically, the email address). The
following three code snippets were used in the stateless session bean:
| /* Version 1 - Select query with email; then remove */
| Notification n = (Notification) em.createQuery("SELECT object(n) " +
| "FROM Notification n " +
| "WHERE n.id=:id AND n.recipient.profile.email=:email "
| )
| .setParameter("id", id)
| .setParameter("email", email)
| .getSingleResult();
| em.remove(n);
|
| /* Version 2 - Delete query with Email */
| int res= em.createQuery("DELETE " +
| "FROM Notification n " +
| "WHERE n.id=:id AND n.recipient.profile.email=:email "
| )
| .setParameter("id", id)
| .setParameter("email", email)
| .executeUpdate();
|
| /* Version 3 - Delete query with Id */
| int res= em.createQuery("DELETE " +
| "FROM Notification n " +
| "WHERE n.id=:id AND n.recipient.profile.id=:pid"
| )
| .setParameter("id", id)
| .setParameter("pid", profileId)
| .executeUpdate();
|
Versions 1 and 3 both work. Version 2 fails with a multi-part exception, the root of which
is:
| Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column
'email' in 'where clause'
| at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
| at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
| at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
| at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
| at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
| at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
| at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1404)
| at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1318)
| at
com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1303)
| at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
| at
org.hibernate.hql.ast.exec.MultiTableDeleteExecutor.execute(MultiTableDeleteExecutor.java:93)
| ... 64 more
|
|
The exception seems strange, however, because the email column does exist on the Profile
table and because the same WHERE syntax works for the SELECT.
Obviously, I can work around this ("Don't use version 2!"), but I'd like
to figure out if this is a bug[1] or if I'm doing something wrong.
[1] I checked Jira for bugs relating to "executeUpdate" and
"MultiTableDeleteExecutor", but I didn't spot anything relevent.
View the original post :
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4008875#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...