How to read Bulgarian characters from MySQL database using Java?

Question

In the database, I have a field storing the string values in the Bulgarian language.

jdbc.url=jdbc:mysql://128.0.0.1:3303/databaseName?useUnicode=true&characterEncoding=UTF-8

My table definition:

CREATE TABLE category (
  cat_id int(11) NOT NULL AUTO_INCREMENT,
  cat_name varchar(50) NOT NULL,
  cat_display_name varchar(50) NOT NULL,
  cat_parent_id int(10) unsigned DEFAULT NULL,
  CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  service_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (cat_id),
  KEY fk_category_service_id (service_id),
  CONSTRAINT fk_category_service_id FOREIGN KEY (service_id) 
       REFERENCES services (service_id) 
       ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8

Now, when I'm displaying the values on webview, the characters from database appears like ??????

On Java console as well the characters appear as '?????'.

Any suggestions on how to read the Bulgarian String in the right manner from MySQL?

Take off the characterEncoding,=UTF-8, or change it to something like UTF-16, or UTF-32. — Neil, Feb 06 '18 at 12:06
Also, the console window might not be able to display non-ASCII characters, and yours are probably Unicode. — Neil, Feb 06 '18 at 12:07
If you view the data in MySql workbench, does it display correctly ? — Neil, Feb 06 '18 at 12:08
@Neil Yes in workbench it displays the data as it should be. — Deepak Jangra, Feb 06 '18 at 12:09
@Neil - Do not use UTF-16 or UTF-32. Those are essentially useless today. — Rick James, Feb 09 '18 at 13:34

score 1 · Answer 1 · answered Feb 06 '18 at 12:15

1

utf8 Unicode data (in your case the data holding your Bulgarian-language characters) needs a utf8 unicode renderer if you will display it correctly.

Web pages can be set to render utf8. (https://www.w3.org/International/questions/qa-changing-encoding).

Workbench is clever: it is aware of column character sets when rendering data. Your data is stored correctly: Workbench's correct rendering is evidence of that.

It sounds like your java console is not so clever. That's strange, because Java text strings internally generally use Unicode.

answered Feb 06 '18 at 12:15

O. Jones

103,626
17
118
172

I did render the web page under UTF-8. <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" isELIgnored="false" import="java.util.*"%> But was unable to achieve the desired result. – Deepak Jangra Feb 06 '18 at 12:22
However, the same web page is able to display text in the Bulgarian language when added in form of static data... hex codes or direct Bulgarian text. But failed to do so when values are fetched from the database but then if Java is failed to fetch the correct string how can web page display the correct String. – Deepak Jangra Feb 06 '18 at 12:25

score 0 · Answer 2 · answered Feb 09 '18 at 13:37

The column definition (Let's see SHOW CREATE TABLE) must be utf8 or utf8mb4.

The connection must specify the client encoding something like

⚈  spring.jpa.properties.hibernate.connection.characterEncoding=utf-8
⚈  spring.jpa.properties.hibernate.connection.CharSet=utf-8
⚈  spring.jpa.properties.hibernate.connection.useUnicode=true

More: See "question mark" in Trouble with UTF-8 characters; what I see is not what I stored

How to read Bulgarian characters from MySQL database using Java?

2 Answers2