CODEWITHSUNDEEP
javamysqljspcollation
KuKeC
KuKeC

Reputation: 4610

Java Server Pages - collation when inserting into MySql?

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.

Upvotes: 1

Views: 722

Answers (2)

KuKeC
KuKeC

Reputation: 4610

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", "");

Upvotes: 0

Rick James
Rick James

Reputation: 142218

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.

Upvotes: 1

Related Questions