Hibernate is returning query result in lower case

Question

I am building a URL shortener service. while retrieving the long URL from short url hibernate is automatically changing the case to smaller case. for example, In my SQL DB I have two rows one has data with "bN" & another one has "bn"

image of table: https://i.stack.imgur.com/lwPjD.png

my table looks like this

id | longurl  | shorturl |
1  | http://..|    bn    |
2  | httP://..|    bN    |

I am querying for "bN" but hibernate is giving me the result of "bn".

Here is the definition of LINK object

package com.saikat.model;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="urldetails")
public class Link {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name ="id")
    private int key;
    
    @Column(name ="shortUrl")
    private String shortUrl;
    
    @Column(name ="longUrl")
    private String longUrl;
    
    public int getKey() {
        return key;
    }
    public void setKey(int key) {
        this.key = key;
    }
    public String getShortUrl() {
        return shortUrl;
    }
    public void setShortUrl(String shortUrl) {
        this.shortUrl = shortUrl;
    }
    public String getLongUrl() {
        return longUrl;
    }
    public void setLongUrl(String longUrl) {
        this.longUrl = longUrl;
    }
    

}

below is the code for fetching details

    @Transactional
    public Link getLink(String Link) {
        Session currentSession = sessionFactory.getCurrentSession();
        //Link l  =currentSession.get(Link.class,8);
        Query query=currentSession.createQuery("from Link where shortUrl= :shortUrl");
        query.setParameter("shortUrl", Link);
        List<Link> link = query.getResultList();
        Link l = new Link();
        try {
            l = link.get(0);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return l;
    }

In the function getLink, I am passing the short link which is "bN". but when hibernate is returning the result it is giving the result of "bn" row. I have checked it in debugging mode.

image of debuging mode:

debug mode 1

debug mode 2

debug mode 3

Here is the log of query

Hibernate: select link0_.id as id1_0_, link0_.longUrl as longurl2_0_, link0_.shortUrl as shorturl3_0_ from urldetails link0_ where link0_.shortUrl=?
13:55:11,135 TRACE BasicBinder:64 - binding parameter [1] as [VARCHAR] - [bN]
13:55:11,138 TRACE BasicExtractor:60 - extracted value ([id1_0_] : [INTEGER]) - [1]
13:55:11,146 TRACE BasicExtractor:60 - extracted value ([longurl2_0_] : [VARCHAR]) - [https://www.google.com]
13:55:11,146 TRACE BasicExtractor:60 - extracted value ([shorturl3_0_] : [VARCHAR]) - [bn]
13:55:11,147 TRACE BasicExtractor:60 - extracted value ([id1_0_] : [INTEGER]) - [2]
13:55:11,147 TRACE BasicExtractor:60 - extracted value ([longurl2_0_] : [VARCHAR]) - [https://stackoverflow.com/questions/68155013/hibernate-is-returning-query-result-in-lower-case?noredirect=1#comment120462353_68155013]
13:55:11,147 TRACE BasicExtractor:60 - extracted value ([shorturl3_0_] : [VARCHAR]) - [bN]

I am using MYSQL & have changed the collation of my DB to latin1_general_cs

In log I observed that hibernate is querying bN which is correct bt the result is coming for both 'bn' & 'bN'

[Enable logging](https://springhow.com/spring-boot-show-sql/) to see the actual parameters going to the database. If the parameters are what they should be, then show the settings of your database and the table. — Kayaman, Jun 27 '21 at 20:53
Something is really fishy about this. Your query does not seem to appear in the logs, instead there is a query that queries for short OR longUrl in the logs. Also note that Hibernate is most likely not converting anything but finding a Link with the value `bn`. this might actually be a database configuration thing if it is configured to consider lower and upper case letters the same. Please show the definition of `Link` and what database you are using. And the full SQL executed for inserting and selecting the `Link` values including the values of bind parameters. — Jens Schauder, Jun 28 '21 at 06:11
And please avoid screenshots. Use log output instead. This way it can be searched, edited and copied. — Jens Schauder, Jun 28 '21 at 06:12
Your database probably uses case insensitive collation. https://stackoverflow.com/questions/11030921/case-sensitive-search-in-hibernate-criteria — Roger Lindsjö, Jun 28 '21 at 06:17
@JensSchauder I have attached the definition of ```LINK``` & also updated the logs. — saikat, Jun 28 '21 at 08:45

score 0 · Answer 1 · answered Jun 28 '21 at 12:17

0

This is an interesting feature of MySQL.

nonbinary string comparisons are case-insensitive by default

You can switch to a binary string:

For binary strings (BINARY, VARBINARY, BLOB), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons are case-sensitive.

More options and an explanation why BINARY is not what you want can be found in How can I make SQL case sensitive string comparison on MySQL?

answered Jun 28 '21 at 12:17

Jens Schauder

77,657
34
181
348

Thank you for the information. Can I alter the column to VARBINARY & insert data directly into it as we do usually or the data need to be converted into binary first? – saikat Jun 28 '21 at 14:48

Hibernate is returning query result in lower case

1 Answers1