How do I update multiple tables inside a named query?

Question

I am using Eclipselink v2.3.3

I am getting this error

Error compiling the query [Credential.updateExistingCredentialNoPW: UPDATE Credential c SET c.active = :active, c.employee.address.streetAddress = :stadd, c.employee.address.city = :city, c.employee.address.province = :prov, c.employee.address.zip = :zip WHERE c.id = :id], line 1, column 46: invalid navigation expression [c.employee], cannot navigate association field [employee] in the SET clause target.

when I try to run my code.

Here are the affected named queries that prevents running my code

@NamedQuery(name = "Credential.updateExistingCredentialNoPW",
query = "UPDATE Credential c "
+ "SET c.active = :active, "
+ "c.employee.address.streetAddress = :stadd, "
+ "c.employee.address.city = :city, "
+ "c.employee.address.province = :prov, "
+ "c.employee.address.zip = :zip "
+ "WHERE c.id = :id"),

@NamedQuery(name = "Credential.updateExistingCredential",
query = "UPDATE Credential c "
+ "SET c.password = :pw, "
+ "c.active = :active, "
+ "c.employee.address.streetAddress = :stadd, "
+ "c.employee.address.city = :city, "
+ "c.employee.address.province = :prov, "
+ "c.employee.address.zip = :zip "
+ "WHERE c.id = :id"),

@NamedQuery(name = "Credential.updateSalary",
query = "UPDATE Credential c "
+ "SET c.employee.salary.value = :val WHERE c.id = :id")

Here is the class where I put all the named queries specified above

public class Credential implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(generator = "CRED_GEN", strategy = GenerationType.IDENTITY)
@Column(name = "CREDENTIAL_ID")
private long id;
// unique & not nullabe username
@Column(nullable = false, unique = true)
private String username;
@Column(nullable = false, name = "USER_KEY")
private String password;
@Column(nullable = false)
private boolean active;
@Column(nullable = false)
private int userLevel;
@OneToOne(cascade = CascadeType.PERSIST, fetch = FetchType.EAGER)
private Employee employee;

public Credential() {
}

public Credential(String username, String password, boolean active,
        int userLevel, Employee employee) throws NoSuchAlgorithmException {
    this.username = username;
    this.setPassword(password);
    this.active = active;
    this.userLevel = userLevel;
    this.employee = employee;
}

public long getId() {
    return id;
}

public void setId(long id) {
    this.id = id;
}

public int getUserLevel() {
    return userLevel;
}

public void setUserLevel(int userLevel) {
    this.userLevel = userLevel;
}

public boolean isActive() {
    return active;
}

public void setActive(boolean active) {
    this.active = active;
}

public Employee getEmployee() {
    return employee;
}

public void setEmployee(Employee employee) {
    this.employee = employee;
}

public String getPassword() {
    return password;
}

public void setPassword(String password) throws NoSuchAlgorithmException {
    this.password = Cryptography.getHash(password);
}

public String getUsername() {
    return username;
}

public void setUsername(String username) {
    this.username = username;
}

Am I doing it wrong?

score 0 · Accepted Answer · answered Sep 26 '12 at 05:58

0

It is not possible to update multiple tables with single sql query.

See https://forums.oracle.com/forums/thread.jspa?threadID=2223393.

So it's also not possible with Eclipselink.

You'll have to split this it two queries, or better use setters.

answered Sep 26 '12 at 05:58

Vadzim

24,954
11
143
151

Thanks for your reply and the link. I've been searching the net for an hour or so finding answers. – ltlynx Sep 26 '12 at 06:47

score 0 · Answer 2 · answered Sep 26 '12 at 05:58

0

You can't. Either use a SQL query, or use Java code:

Credential c = em.find(Credential.class, credentialId);
c.setActive(active);
c.getEmployee().getAddress().setStreet(street);
...

answered Sep 26 '12 at 05:58

JB Nizet

678,734
91
1,224
1,255

How do I update multiple tables inside a named query?

2 Answers2