Hi all,

I am trying to execute the bellow code,

private boolean isUserNameExist(){
Users users = getInstance();
String qry = "from Users where userName=:userName AND tenantId =:tenantId";
Query query = entityManager.createQuery(qry);
query.setParameter("userName",users.getUserName()).
setParameter("tenantId", users.getTenantId());
List lst =query.getResultList();

return lst.size() > 0 ? true : false;
}


but i am getting an entirely different exception like bellow.The exception is throwing at "List lst =query.getResultList(); ". One more thing i have an objec 'Users' in scope.

11:22:54,328 ERROR [JDBCExceptionReporter] ERROR: duplicate key violates unique constraint "ad_users_u2"
11:22:54,328 ERROR [AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:71)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)


Can anybody help me to fix it.

Thanks in advance
vivek

I did experience similar exception before, but it may not be the same case, just put it for your reference.

Try to trace back to see if you did any retrieval of Users objects ; if yes, try to see if you had modified any of the getter classes that return customized value instead of the "raw value" as retrieved from database. Since hibernate will automatically synchronize the objects with db if the objects values are different from the db as returned by the getter methods.

Hope it helps.

Yes you are correct ,i changed the object because,before updating the record i want to check user name is already in the table. So can u please tell me how can i fix it.

Thanks a lot
Vivek

Would you post your model class here ?
And the key of your table is ...?

/*
* Copyright 2008 CAMSoft Limited. All Rights Reserved.
*
* This software is the proprietary information of CAMSoft Limited. Use is
* subject to license terms.
*
* Workfile: Users.java
* Revision: #1
* Author: Manju
* Date: Jul 9, 2008
*
*/
package com.camsoft.camera.administration.user.entity;

import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.UniqueConstraint;

import org.hibernate.validator.Length;
import org.hibernate.validator.NotNull;
import org.hibernate.validator.Pattern;
import org.jboss.seam.annotations.Name;

import com.camsoft.camera.administration.userrole.entity.UserRoles;

/**
* Users generated by hbm2java
*/
@Entity
@Name("users")
@Table(name = "AD_USERS", schema = "public", uniqueConstraints = {
@UniqueConstraint(columnNames = {"tenant_id", "user_name"}),
@UniqueConstraint(columnNames = {"tenant_id", "id"})})
public class Users implements java.io.Serializable {

/**
*
*/
private static final long serialVersionUID = 1L;
private int id;
private Users users;
private int tenantId;
private Date creationDate;
private String createdBy;
private Date updateDate;
private String updatedBy;
private String userName;
private int userTypeId;
private String firstName;
private String lastName;
private Long departmentId;
private Long jobTitleId;
private String encryptedUserPassword;
private Date passwordDate;
private Short passwordLifespanAccesses;
private Short passwordAccessesLeft;
private Short passwordLifespanDays;
private String email;
private String phone1;
private String phone2;
private String fax;
private char enabledFlag;
private Set<UserRoles> adUserRoleses = new HashSet<UserRoles>(0);
private Set<Users> userses = new HashSet<Users>(0);


public Users() {
}

public Users(int id, int tenantId, Date creationDate, String createdBy,
Date updateDate, String updatedBy, String userName,
int userTypeId, String encryptedUserPassword, char enabledFlag) {
this.id = id;
this.tenantId = tenantId;
this.creationDate = creationDate;
this.createdBy = createdBy;
this.updateDate = updateDate;
this.updatedBy = updatedBy;
this.userName = userName;
this.userTypeId = userTypeId;
this.encryptedUserPassword = encryptedUserPassword;
this.enabledFlag = enabledFlag;
}
public Users(int id, Users users, int tenantId, Date creationDate,
String createdBy, Date updateDate, String updatedBy,
String userName, int userTypeId, String firstName,
String lastName, Long departmentId, Long jobTitleId,
String encryptedUserPassword, Date passwordDate,
Short passwordLifespanAccesses, Short passwordAccessesLeft,
Short passwordLifespanDays, String email, String phone1,
String phone2, String fax, char enabledFlag,
Set<UserRoles> adUserRoleses, Set<Users> userses) {
this.id = id;
this.users = users;
this.tenantId = tenantId;
this.creationDate = creationDate;
this.createdBy = createdBy;
this.updateDate = updateDate;
this.updatedBy = updatedBy;
this.userName = userName;
this.userTypeId = userTypeId;
this.firstName = firstName;
this.lastName = lastName;
this.departmentId = departmentId;
this.jobTitleId = jobTitleId;
this.encryptedUserPassword = encryptedUserPassword;
this.passwordDate = passwordDate;
this.passwordLifespanAccesses = passwordLifespanAccesses;
this.passwordAccessesLeft = passwordAccessesLeft;
this.passwordLifespanDays = passwordLifespanDays;
this.email = email;
this.phone1 = phone1;
this.phone2 = phone2;
this.fax = fax;
this.enabledFlag = enabledFlag;
this.adUserRoleses = adUserRoleses;
this.userses = userses;
}

@Id
@SequenceGenerator(name="identifier", sequenceName="ad_users_id_seq", allocationSize=1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "identifier")
@Column(name = "id", unique = true, nullable = false)
@NotNull
public int getId() {
return this.id;
}

public void setId(int id) {
this.id = id;
}
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "supervisor_id")
public Users getUsers() {
return this.users;
}

public void setUsers(Users users) {
this.users = users;
}

@Column(name = "tenant_id", nullable = false)
@NotNull
public int getTenantId() {
return this.tenantId;
}

public void setTenantId(int tenantId) {
this.tenantId = tenantId;
}
@Temporal(TemporalType.DATE)
@Column(name = "creation_date", nullable = false, length = 13)
@NotNull
public Date getCreationDate() {
return this.creationDate;
}

public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}

@Column(name = "created_by", nullable = false)
@NotNull
public String getCreatedBy() {
return this.createdBy;
}

public void setCreatedBy(String createdBy) {
this.createdBy = createdBy;
}
@Temporal(TemporalType.DATE)
@Column(name = "update_date", nullable = false, length = 13)
@NotNull
public Date getUpdateDate() {
return this.updateDate;
}

public void setUpdateDate(Date updateDate) {
this.updateDate = updateDate;
}

@Column(name = "updated_by", nullable = false)
@NotNull
public String getUpdatedBy() {
return this.updatedBy;
}

public void setUpdatedBy(String updatedBy) {
this.updatedBy = updatedBy;
}

@Column(name = "user_name", nullable = false, length = 100)
@NotNull
@Length(max = 100)
public String getUserName() {
return this.userName;
}

public void setUserName(String userName) {
this.userName = userName;
}

@Column(name = "user_type_id", nullable = false)
@NotNull
public int getUserTypeId() {
return this.userTypeId;
}

public void setUserTypeId(int userTypeId) {
this.userTypeId = userTypeId;
}

@Column(name = "first_name", length = 80)
@Length(max = 80)
public String getFirstName() {
return this.firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

@Column(name = "last_name", length = 80)
@Length(max = 80)
public String getLastName() {
return this.lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

@Column(name = "department_id", precision = 15, scale = 0)
public Long getDepartmentId() {
return this.departmentId;
}

public void setDepartmentId(Long departmentId) {
this.departmentId = departmentId;
}

@Column(name = "job_title_id", precision = 15, scale = 0)
public Long getJobTitleId() {
return this.jobTitleId;
}

public void setJobTitleId(Long jobTitleId) {
this.jobTitleId = jobTitleId;
}

@Column(name = "encrypted_user_password", nullable = false, length = 100)
@NotNull
@Length(max = 100)
public String getEncryptedUserPassword() {
return this.encryptedUserPassword;
}

public void setEncryptedUserPassword(String encryptedUserPassword) {
this.encryptedUserPassword = encryptedUserPassword;
}
@Temporal(TemporalType.DATE)
@Column(name = "password_date", length = 13)
public Date getPasswordDate() {
return this.passwordDate;
}

public void setPasswordDate(Date passwordDate) {
this.passwordDate = passwordDate;
}

@Column(name = "password_lifespan_accesses", precision = 3, scale = 0)
public Short getPasswordLifespanAccesses() {
return this.passwordLifespanAccesses;
}

public void setPasswordLifespanAccesses(Short passwordLifespanAccesses) {
this.passwordLifespanAccesses = passwordLifespanAccesses;
}

@Column(name = "password_accesses_left", precision = 3, scale = 0)
public Short getPasswordAccessesLeft() {
return this.passwordAccessesLeft;
}

public void setPasswordAccessesLeft(Short passwordAccessesLeft) {
this.passwordAccessesLeft = passwordAccessesLeft;
}

@Column(name = "password_lifespan_days", precision = 3, scale = 0)
public Short getPasswordLifespanDays() {
return this.passwordLifespanDays;
}

public void setPasswordLifespanDays(Short passwordLifespanDays) {
this.passwordLifespanDays = passwordLifespanDays;
}

@Column(name = "email", length = 40)
@Length(max = 40)
@Pattern(regex=".+@.+\\.[a-z]{2,6}+", message="#{messages['validator.email']}")
public String getEmail() {
return this.email;
}

public void setEmail(String email) {
this.email = email;
}

@Column(name = "phone1", length = 40)
@Length(max = 40)
public String getPhone1() {
return this.phone1;
}

public void setPhone1(String phone1) {
this.phone1 = phone1;
}

@Column(name = "phone2", length = 40)
@Length(max = 40)
public String getPhone2() {
return this.phone2;
}

public void setPhone2(String phone2) {
this.phone2 = phone2;
}

@Column(name = "fax", length = 40)
@Length(max = 40)
public String getFax() {
return this.fax;
}

public void setFax(String fax) {
this.fax = fax;
}

@Column(name = "enabled_flag", nullable = false, length = 1)
@NotNull
public char getEnabledFlag() {
return this.enabledFlag;
}

public void setEnabledFlag(char enabledFlag) {
this.enabledFlag = enabledFlag;
}
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "users")
public Set<UserRoles> getAdUserRoleses() {
return this.adUserRoleses;
}

public void setAdUserRoleses(Set<UserRoles> adUserRoleses) {
this.adUserRoleses = adUserRoleses;
}
@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "users")
public Set<Users> getUserses() {
return this.userses;
}

public void setUserses(Set<Users> userses) {
this.userses = userses;
}

}

Table Key is id

There is no problem with the model class.
But the logic behind the program is a bit errorneous.

e.g.: There are two users in the table : say Tom and Thomas,
you retrieved the [Thomas] object,
-> Set the [Thomas] object name to [Tom]
-> Retrieve from database if there is any object named [Tom] <-- Error here
Because the [Thomas] object is holding a tenantId and a (modified) name, it try to synchronize the DB for this object BEFORE you retrieve from the database, so it will try to update the [Thomas] object to [Tom] <-- (Duplicate object in DB thus violating the unique constraint)

One alternative may be NOT TO SET the new value into an object from database and then test for its existence, instead, just pass in the tenantId and username as a parameter to the isUserNameExist function to test, so that Hibernate will not synchronize the object with database before you retrieve from it.

Hope it helps.

Sorry Man It didnt fix that issue. I fixed it using native quey. Is it wrong?

String qry = select user_name from ad_users where uer_name=:userName
AND tenant_id =:tenantId


entityManager.createNativeQuery(qry)



Itried this not working


private boolean isUserNameExist(String userName,int tenentId){
//Users users = getInstance();
//String qry = "select user_name from ad_users where user_name=:userName AND tenant_id =:tenantId";
String qry = "select userName from Users where userName=:userName AND tenantId =:tenantId";
// Query query = entityManager.createNativeQuery(qry);
Query query = entityManager.createQuery(qry);
query.setParameter("userName",userName).
setParameter("tenantId", tenentId);
List lst =query.getResultList();

return lst.size() > 0 ? true : false;
}

Anyway thanks a lot.........

The "not working" do you mean it still throws the same exception or else ?

Did you set the objects' properties before calling this function?

I am interested what is the SQL that was executed, could you switch on the properties "show_sql" and paste the SQL here ?
Including the retrieve and update statements. Thanks

I am firing this method from front end

public String updateUser() {
if(isUserNameExist(getInstance().getUserName(),getInstance().getTenantId())){
facesMessages.add("Username Already Exist");
return "equal";
}
Users updateUsers = getInstance();
updateUsers.setUpdateDate(new Date());

if(!isPasswordChanged()){
String password = getPassword(updateUsers.getId());
updateUsers.setEncryptedUserPassword(password);
entityManager.merge(updateUsers);
}else{
updateUsers.setEncryptedUserPassword(DigestUtils.md5Hex(updateUsers.getEncryptedUserPassword()));
}

updatedMessage();
raiseAfterTransactionSuccessEvent();

return "updated";
}


Th following method is throwing the exception

private boolean isUserNameExist(String userName,int tenentId){
//Users users = getInstance();
String qry = "select user_name from ad_users where user_name=:userName AND tenant_id =:tenantId";
//String qry = "select userName from Users where userName=:userName AND tenantId =:tenantId";
// Query query = entityManager.createNativeQuery(qry);
Query query = entityManager.createNativeQuery(qry);
query.setParameter("userName",userName).
setParameter("tenantId", tenentId);
List lst =query.getResultList();

return lst.size() > 0 ? true : false;
}

Sorry that I may be unclear in the previous post.
I mean, you may try to pass the UserName and TenantId from front end to a temporary field first, like
private String tmp1, tmp2;
and then pass these two values into the function :

isUserNameExist(tmp1, tmp2);

See if it helps. Good luck.