How to get data from DB by trim spaces using Hibernate?

Question

I written a controller such that is following

  @RequestMapping(value="find/{roleName}", method=GET)
    public UserRole getByRoleName(@PathVariable("roleName") String roleName){ 
     UserRole userRole = userRoleService.findByRoleName(roleName);
        return userRole;   
    }
UserRole is nothing but that is given below as shown that

 @Entity

@Table(name = "uro_user_roles") public class UserRole {

/* Properties */
@Id 
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "uro_role_id")
private Integer roleId;

@Column(name = "uro_role_name")
private String roleName;

@Column(name = "uro_create_user")
private String createUser;

@Column(name = "uro_active")
private String createActive;

/* Getter / Setters */ 

Now i got the DB data when i give the roleName by using the following Hibernate function such thats is

     public UserRole findByRoleName(String roleName) {
     UserRole userPermission = (UserRole)  
      criteria().setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                .add(eq("roleName", roleName)).uniqueResult();
        return userPermission;
    }

Here my problem is when i give the exact name then only it return the object because its case sensitive.I mean if table data have some space before its value then it doesn't return. So how to get the data by given a name without space and case sensitive.Is there any option in hibernate to get data from DB with eliminating the spaces? If there is a option then no need to write a Trim() method that's y asking. plz anybody help

Solution 1

You can use an SQL Restriction:

public UserRole findByRoleName(String roleName) {
    UserRole userPermission = (UserRole) criteria()
            .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            .add(Restrictions.sqlRestriction("TRIM(LOWER({alias}.roleName)) = ?", roleName.trim().toLowerCase()), StringType.INSTANCE))
            .uniqueResult();
    return userPermission;
}

This works in MySQL but not all databases have a TRIM() function. Other DB have LTRIM() and RTRIM() so you'd have to call it like LTRIM(RTRIM(...)).

Solution 2

Do a lower case on the field before comparing. I am assuming the field is never null.

 public UserRole findByRoleName(String roleName) {
 UserRole userPermission = (UserRole)  
  criteria().setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
            .add(eq("rolename", roleName.trim().toLowerCase())).uniqueResult();
    return userPermission;
}

Comments

[Md Aslam]

Sorry , here not case sensitive problem here space problem.If space is present before the name in DB table then it does return null.So is there any Trim() functions like u gave toLowerCase() method.

[Md Aslam]

No changes.Same problem.It return nothing.Any other options plz help

[Md Aslam]

The space present in table data.So how to get?

[Alexandre Santos]

Updated the answer. Try now.