Java Server Pages - collation when inserting into MySql?

Question

I am using Java Server Pages and got problem with collation when inserting data from my application. My insert code looks like this:

<%@page import="java.sql.*" %>
  <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
    <% Class.forName( "com.mysql.jdbc.Driver"); 
       Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/myapp", "root", ""); 
       Statement st=con.createStatement(); 
       ResultSet rs; 
       st.executeUpdate("insert into table values (default,1,2,šđžćč)"); %>

In my database result looks like this š?ž?? (so he can see understand 2 letters but not all). How to fix it so all letters will be in my database?

EDIT : I can insert data with those characters via phpmyadmin, but i can't from .jsp file to database. It's like charset = utf-8 on my jsp file is not working.

Rick James · Accepted Answer · 2015-07-30 04:15:26Z

1

Question marks come from this:

The client has a valid character (good), and
The SET NAMES agrees with the encoding that the client has (good), but
The target column's CHARACTER SET does not include the intended character (bad).

Examples:

latin1 handles only Western European characters; trying to put an Eastern European character or any Asian character in it won't fit.
latin2 and cp1250 can handle Czech, so conversions between them are mostly OK, but not between either of them and latin1
utf8mb4 is a superset of utf8. Putting a utf8 character into utf8mb4 is ok, but the reverse will result in a '?' in some cases.

The characters that were converted to '?' can not be recovered from the table.

How to fix future INSERTs?

Using utf8mb4 on the table column(s) probably works in all cases.
Otherwise, pick some CHARACTER SET for the table column(s) that reasonably matches the client data.

The reason for only some of the characters being ? (in š?ž??) is because šž exist in latin1 but the others do not.

Bottom line: Change the CHARACTER SET in the table definition.

answered Jul 30, 2015 at 4:15

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

KuKeC Over a year ago

It's not problem with table definition. In phpmyadmin i can insert those kind of characters into table and they can be read from table into page with sql query. Inserting into database just ignores those characters and insert "?" instead of them. So i think the problem is in jsp page and don't know hot to fix it. I changed my collation in table definiton and for those columns in that table to "utf8mb4" and still "??" show up.

Rick James Over a year ago

š and ž are available in latin1, but the other three characters are not, hence the ?. I believe the only way to get š?ž?? is to have (1) utf8 bytes (good), (2) connection to mysql specifying utf8(good), but (3) CHARACTER SET latin1 on the column/table (bad).

Rick James Over a year ago

Please provide SHOW CREATE TABLE.

KuKeC Over a year ago

Problem with collation on database wasn't the problem because i could enter those values in phpmyAdmin, can fetch that kind of data from tables and with using useUnicode=true&characterEncoding=UTF-8 can enter that kind of characters into database. I tried to use "super utfmb4" but still the result was the same... after adding those params into connection link problem was solved.

KuKeC · Accepted Answer · 2015-07-30 06:28:33Z

0

Problem solved with adding to my connection from old path

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/myapp", "root", "");

to

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/myapp?useUnicode=true&characterEncoding=UTF-8", "root", "");

answered Jul 30, 2015 at 6:28

KuKeC

4,6305 gold badges34 silver badges62 bronze badges

Collectives™ on Stack Overflow

Java Server Pages - collation when inserting into MySql?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related