java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x91\xBD\xF0\x9F…’

by

What you have is EXTRATERRESTRIAL ALIEN (U+1F47D) and BROKEN HEART (U+1F494) which
are not in the basic multilingual plane. They cannot be even represented in java as one char, "👽💔".length() == 4. They are definitely not null characters and one will see squares if you are not using fonts that support them.

MySQL’s utf8 only supports basic multilingual plane, and you need to use utf8mb4 instead:

For a supplementary character, utf8 cannot store the character at all,
while utf8mb4 requires four bytes to store it. Since utf8 cannot store
the character at all, you do not have any supplementary characters in
utf8 columns and you need not worry about converting characters or
losing data when upgrading utf8 data from older versions of MySQL.

So to support these characters, your MySQL needs to be 5.5+ and you need to use utf8mb4 everywhere. Connection encoding needs to be utf8mb4, character set needs to be utf8mb4 and collaction needs to be utf8mb4. For java it’s still just "utf-8", but MySQL needs a distinction.

I don’t know what driver you are using but a driver agnostic way to set connection charset is to send the query:

SET NAMES 'utf8mb4'

Right after making the connection.

See also this for Connector/J:

14.14: How can I use 4-byte UTF8, utf8mb4 with Connector/J?

To use 4-byte UTF8 with Connector/J configure the MySQL server with
character_set_server=utf8mb4. Connector/J will then use that setting
as long as characterEncoding has not been set in the connection
string. This is equivalent to autodetection of the character set.

Adjust your columns and database as well:

var1 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL

Again, your MySQL version needs to be relatively up-to-date for utf8mb4 support.

More Related Contents:

  1. How to get UTF-8 working in Java webapps?
  2. Java : How to determine the correct charset encoding of a stream
  3. How to convert Strings to and from UTF8 byte arrays in Java
  4. How to Find the Default Charset/Encoding in Java?
  5. What is the default encoding of the JVM?
  6. Guessing the encoding of text represented as byte[] in Java
  7. IntelliJ IDEA incorrect encoding in console output
  8. JDBC character encoding
  9. Java: How to detect (and change?) encoding of System.console?
  10. java.sql.SQLException: No database selected – why?
  11. Hours and seconds are lost when storing a date in the database [closed]
  12. How to import the Mysql database using Java
  13. Java: Insert multiple rows into MySQL with PreparedStatement
  14. Spring MVC UTF-8 Encoding
  15. Reading UTF-8 – BOM marker
  16. Connection to Db dies after >4
  17. How to encode a WAV to a mp3 on a Android device
  18. Connect to remote MySQL database through SSH using Java
  19. Retrieve an Image stored as BLOB on a MYSQL DB
  20. Can I execute multiple queries separated by semicolon with MySQL Connector/J? [duplicate]
  21. No Database selected when retrieving from mysql website
  22. mysql prepared statement error: MySQLSyntaxErrorException
  23. ISO-8859-1 encoding and binary data preservation
  24. What is the character encoding of String in Java?
  25. Populate JFreechart TimeSeriesCollection from Mysql DB?
  26. How to replace � in a string
  27. All inclusive Charset to avoid “java.nio.charset.MalformedInputException: Input length = 1”?
  28. Is there a more efficient way of making pagination in Hibernate than executing select and count queries?
  29. Java Encode file to Base64 string To match with other encoded string
  30. Java compiler platform file encoding problem

Leave a Comment