1

So I have set up hibernate with Wildfly and MySql.

Querying works, but have problem querying UTF-8 character names.

On MySql:

SELECT * FROM users WHERE firstname = "ნიკა";

Doesn't result in empty set.

While in java:

Session session = HibernateUtils.getSession();

String name = request.getParameter("name");

CriteriaBuilder builder = session.getCriteriaBuilder();

CriteriaQuery<UsersEntity> query = builder.createQuery(UsersEntity.class);
Root<UsersEntity> usersRoot = query.from(UsersEntity.class);
query.select(usersRoot)
        .where(builder.equal(usersRoot.get("firstname"), name));

List<UsersEntity> list = session.createQuery(query).list();

session.close();

writeUsers(list, response.getWriter());

Results in an empty set when querying localhost:8080/main?name=ნიკა

How do I fix this ?

UPDATE:

I also have issue that when querying all data and displaying them non english characters are replaced with question marks.

UPDATE 2:

This code results in showing up question marks on browser:

response.getWriter().write("ნიკა ჩხარტიშვილი");

While this one works properly:

response.setCharacterEncoding("UTF-8");
response.getWriter().write("ნიკა ჩხარტიშვილი");

When querying database with this query SELECT * FROM users WHERE username='ნიკა' database receives question marks instead of ნიკა (That's what shows up in wireshark).

I think it's wildfly issue because it received UTF-8 correctly but sends question marks unless it's specified to send UTF-8.

8bra1nz
  • 717
  • 7
  • 19
  • and if for the test you could hardcode the name in the code, does it work also. If it works like that, it's the translation done by the browser. – davejal Feb 21 '17 at 14:53
  • @davejal Hardcoding it doesn't work as well. – 8bra1nz Feb 21 '17 at 14:54
  • Your SQL example uses `firstname = "ნიკა"`. Your URL example uses `localhost:8080/main?name=ნიკა`.What happens if you close the session after the `writeUsers` call? What does the `writeUsers` call do? – Lew Bloch Feb 21 '17 at 15:15
  • It just outputs the names into the `response.getWriter()`, it mustn't be the case because `localhost:8080/main?name=dato` works properly. – 8bra1nz Feb 21 '17 at 15:17

5 Answers5

0

This is because URLs can only be sent over the Internet using the ASCII character-set.

If your call to the server is from javascript you should encode your name value with encodeURIComponent("ნიკა") , your call should look like this:

localhost:8080/main?name=%E1%83%9C%E1%83%98%E1%83%99%E1%83%90

If you are just pasting the URL to the brownser just do it as usual

localhost:8080/main?name=ნიკა

and then in your server after you get the name value from the request you encode it again as UTF-8 like this:

 //String name = request.getParameter("name");
 String name = new String(request.getParameter("name").getBytes("ISO-8859-1"), "UTF-8");
jonhid
  • 2,075
  • 1
  • 11
  • 18
0

If you get question marks when reading from a MySQL database, see here

  • The bytes to be stored are not encoded as utf8/utf8mb4. Fix this.
  • The column in the database is CHARACTER SET utf8 (or utf8mb4). Fix this.
  • Also, check that the connection during reading is UTF-8.

The question marks were generated when INSERTing into the database, so the original text cannot be recovered.

I'm pretty sure that any mention of "ISO-8859-1" is going to be wrong. %E1%83%9C is the urlencoding of ნ. Fetching the name should undo that for you, without any action on your part. Hence, doing getBytes("ISO-8859-1") is asking for ნიკა to be provided in that encoding. But that encoding only covers Western Europe. I suggest trying

new String(request.getParameter("name").getBytes("UTF-8"));

(Is that Java? Or what?)

Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • When I select from command line no question marks are present. And select with where clause works properly. I think it must be connection issue. – 8bra1nz Feb 21 '17 at 19:55
  • I just checked another thing. I logged names on server and they are properly encoded but when sending to browser they are questionmarked. – 8bra1nz Feb 21 '17 at 19:59
  • There is no _direct_ connection from MySQL to the browser. It _must_ be going through some app, and that app must need fixing. – Rick James Feb 21 '17 at 22:50
  • It doesn't work. Literal or encoded server sends query with question marks to mysql. How to make sure connection to database is using UTF-8 ? – 8bra1nz Feb 23 '17 at 09:22
  • See "Best Practice" in they link. – Rick James Feb 23 '17 at 16:07
0

I fixed it.

First connection to client wasn't UTF-8. Fixed it with:

response.setCharacterEncoding("UTF-8");

This works for one servlet though. Updated wildfly standalone.xml configuration file to

<servlet-container name="default" default-encoding="UTF-8">
    <jsp-config/>
    <websockets/>
</servlet-container>

Added default-encoding property to servlet-container, now every response will have default UTF-8 encoding.

JDBC Communication wasn't UTF-8.

Fixed with updating connection.url, appended

?useUnicode=yes&amp;characterEncoding=UTF-8 at the end.

<property name="connection.url">jdbc:mysql://localhost:3306/cnick?useUnicode=yes&amp;characterEncoding=UTF-8</property>

When last time I did this I thought it didn't work because what I was querying wasn't in the database. Now it's working.

8bra1nz
  • 717
  • 7
  • 19
0

You can Use Hibernate @Type attribute,Based on your requirement you can customize the annotation and apply on top of the fied. like :

public class PhoneNumberType implements UserType {
@Override
public int[] sqlTypes() {
    return new int[]{Types.INTEGER, Types.INTEGER, Types.INTEGER};
}

@Override
public Class returnedClass() {
    return PhoneNumber.class;
}

// other methods
}

First, the null SafeGet method:

@Override
public Object nullSafeGet(ResultSet rs, String[] names, 
SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException {
int countryCode = rs.getInt(names[0]);
 if (rs.wasNull())
    return null;
 int cityCode = rs.getInt(names[1]);
int number = rs.getInt(names[2]);
PhoneNumber employeeNumber = new PhoneNumber(countryCode, cityCode, number);
 return employeeNumber;
 }

Next, the null SafeSet method:

@Override
 public void nullSafeSet(PreparedStatement st, Object value, 
  int index, SharedSessionContractImplementor session) 
   throws HibernateException, SQLException {
    if (Objects.isNull(value)) {
    st.setNull(index, Types.INTEGER);
    } else {
    PhoneNumber employeeNumber = (PhoneNumber) value;
    st.setInt(index,employeeNumber.getCountryCode());
    st.setInt(index+1,employeeNumber.getCityCode());
    st.setInt(index+2,employeeNumber.getNumber());
  }
 }

Finally, we can declare our custom PhoneNumberType in our OfficeEmployee entity class:

@Entity
@Table(name = "OfficeEmployee")
 public class OfficeEmployee {

  @Columns(columns = { @Column(name = "country_code"), 
  @Column(name = "city_code"), @Column(name = "number") })
  @Type(type = "com.baeldung.hibernate.customtypes.PhoneNumberType")
   private PhoneNumber employeeNumber;

 // other fields and methods
  }

This might solve your problem, This will work for all database. if you want more info refer :: https://www.baeldung.com/hibernate-custom-types

TryChai
  • 51
  • 3
0

There are below common locations where UTF8 encoding is to be enabled

1) Make sure you create the database and its tables using character set utf8 and collation utf8_unicode_ci

2) Under hibernate configuration file

<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/DATABASE_NAME?useUnicode=true&amp;characterEncoding=UTF-8</property>
<property name="hibernate.connection.CharSet">utf8</property>
<property name="hibernate.connection.characterEncoding">utf8</property>
<property name="hibernate.connection.useUnicode">true</property>

3) Under main html template header section

<meta http-equiv="content-type" content="text/html;charset=UTF-8" />

4) Under apache tomcat server.xml

<Connector connectionTimeout="20000" port="8080" protocol="HTTP/1.1" redirectPort="8443" URIEncoding="UTF-8"/>

<Connector port="8009" protocol="AJP/1.3" redirectPort="8443" URIEncoding="UTF-8"/>
Nikhil
  • 861
  • 11
  • 15