CODEWITHSUNDEEP
javamysqlspringhibernatejpa
Md Aslam
Md Aslam

Reputation: 149

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

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

Upvotes: 3

Views: 14261

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153780

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(...)).

Upvotes: 3

Alexandre Santos
Alexandre Santos

Reputation: 8338

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;
}

Upvotes: 1

Related Questions