CODEWITHSUNDEEP
spring-bootunicodejdbctemplatehikaricp
Ayub
Ayub

Reputation: 599

spring boot application showing ??? characters instead of unicode

While reading from database the logs in my dao class shows junk characters in place of unicode characters. Sql developer shows correct values from oracle database also correct NLS language encoding is set on the database.

Below code works for standard jdbc:

connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe", "adminuser", "oracle");
Statement st=connection.createStatement();
ResultSet res=st.executeQuery("SELECT menu_item_name from pending_menu_item 
where menu_item_id=6062");

    while(res.next()){
        System.out.println("itemName: "+res.getString(1));
    }

Below is the url for springboot project which shows junk characters, I uploaded to git hub.https://github.com/AyubOpen/spring-boot-jdbc/

package com.mkyong;

import com.mkyong.dao.CustomerRepository;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;
import java.util.List;
import static java.lang.System.exit;

@SpringBootApplication
public class SpringBootConsoleApplication implements CommandLineRunner {

@Autowired
DataSource dataSource;

@Autowired
private CustomerRepository customerRepository;

@Autowired
private JdbcTemplate jdbcTemplate;

  public static void main(String[] args) throws Exception {
      SpringApplication.run(SpringBootConsoleApplication.class, args);
}

@Override
public void run(String... args) throws Exception {

// If you want to check the HikariDataSource settings
 HikariDataSource newds = (HikariDataSource)dataSource;
  System.out.println("getMaximumPoolSize = " + ((HikariDataSource) 
  dataSource).getMaximumPoolSize());
  System.out.println("DATASOURCE = " + 
  newds.getDataSourceProperties().getProperty("hikari.useUnicode"));

  if (args.length <= 0) {
    System.err.println("[Usage] java xxx.jar {display}");
   } else {
        if (args[0].equalsIgnoreCase("display")) {
        System.out.println("Display items...");
        List<String> list = customerRepository.findAll();
        list.forEach(x -> System.out.println(x));
      }
      System.out.println("Done!");
   }
  exit(0);
 }
}

package com.mkyong.dao;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;

@Repository
public class CustomerRepository {

   @Autowired
   private JdbcTemplate jdbcTemplate;

   public List<String> findAll() {

    List<String> result = jdbcTemplate.query(
        "SELECT menu_item_name from pending_menu_item where 
            menu_item_id=6062",
        (rs, rowNum) -> rs.getString("menu_item_name")
    );
  return result;
  }
}

application.properties  
----------------------
spring.main.banner-mode=off
spring.datasource.initialize=true
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.url=jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.username=jahezdbapp
spring.datasource.password=oracle
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver 
spring.datasource.hikari.useUnicode=true
spring.datasource.hikari.characterEncoding=utf-8 
spring.datasource.hikari.characterSetResults=utf8 

# HikariCP settings
#60 sec
spring.datasource.hikari.connection-timeout=60000
# max 5
spring.datasource.hikari.maximum-pool-size=5

Upvotes: 7

Views: 13168

Answers (5)

Heshan Sandaruwan
Heshan Sandaruwan

Reputation: 1

Had the same issue when reading unicodes. Add this line to the .bat file -Dfile.encoding=UTF8

Upvotes: 0

xuthus
xuthus

Reputation: 144

Especially for Oracle. At least I was in the same situation.

It seems, your database uses some national (non-unicode) encoding. So JDBC cannot translate it to UNICODE.

Oracle JDBC by default supports only few character sets: US7ASCII, WE8DEC, WE8ISO8859P1, WE8MSWIN1252, and UTF8. So all strings encoded in different encodings will be displayed as questions. To add support of all other character sets add to the application classpath the file orai18n.jar.

See more here: https://docs.oracle.com/database/121/JJDBC/global.htm#JJDBC28643

Upvotes: 2

todaynowork
todaynowork

Reputation: 1026

Record: worked url for DB2, jdb

jdbc:db2://x.x.x.x:5010/xxxx:useUnicode=yes;characterEncoding=UTF-8;

Upvotes: 0

Ayub
Ayub

Reputation: 599

This issue does not seem to be with jdbc datasource, I will create a simple github project for springboot and followup on this issue with the below question:

spring boot commandline runner is using windows default character encoding

Upvotes: 0

Abhijit Sarkar
Abhijit Sarkar

Reputation: 24593

  1. Add ?useUnicode=yes&characterEncoding=UTF-8 at the end of spring.datasource.url.

  2. Set spring.datasource.sqlScriptEncoding=UTF-8 in application.properties

1 itself should solve the issue, 2 may not be necessary.

Upvotes: 6

Related Questions