how to use (useUnicode=yes characterEncoding=UTF-8 ) with DBCP

Question

I am trying to insert Arabic letter in to mysql database, but it only store "????". I am using DBCP for connecting with mysql databse, here is the datasource:

          <Resource name="jdbc/view_db"        auth="Container"           type="javax.sql.DataSource"           username="root"            password=""           autoReconnect="true"           testOnBorrow="true"           validationQuery = "SELECT 1"           driverClassName="com.mysql.jdbc.Driver"           url="jdbc:mysql://localhost/view_db"           maxActive="50"           maxIdle="10"/> 

how to setup UTF-8 encoding with in DBCP configuration or how to use (useUnicode=yes characterEncoding=UTF-8 ) ?

Answer 1

According to the DBCP documentation you need use the parameter connectionProperties with value [propertyName=propertyValue;]*, so in your case you should use something like:

      driverClassName="com.mysql.jdbc.Driver"       url="jdbc:mysql://localhost/view_db"       connectionProperties="useUnicode=yes;characterEncoding=utf8;"       maxActive="50" 

(note the closing ;!)

Answer 2

I tried the following and it worked for me using tomcat 7.0.40 and MySQL 5.5 on Debian Wheezy box:

url="jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8"  

Note that & must be represented as &

also make sure that your my.cnf for your mysql server has the following lines:

[client] default-character-set=utf8  [mysql] default-character-set=utf8