JDBC will set up utf8mb4 in properties

I can write utf8mb4 characters (like 👽💔) to the MySQL db table if I first run the command: SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'

on the connection:

  connection = DriverManager.getConnection(url, prop)
  val config: PreparedStatement = connection.prepareStatement("SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci'");
  config.executeUpdate();
  val insert: PreparedStatement = connection.prepareStatement("INSERT INTO test "
    + "(ID, NAME) VALUES (?,?)");
  insert.setInt(1, 20);
  insert.setString(2, "test 👽💔");
  insert .executeUpdate();

      

However, I need to write a Dataframe in a db using Spark and the API doesn't give me access to the connection. I can only pass the properties that will be used to establish the connection (or pass them by url):

val prop = new Properties()
prop.put("user", username)
prop.put("driver", "com.mysql.cj.jdbc.Driver")
prop.put("characterEncoding", "UTF-8") // doesn't accept utf8mb4
prop.put("connectionCollation", "utf8mb4_unicode_ci")

df.write.mode("overwrite").jdbc(s"jdbc:mysql://$dbUrl/$dbName", dbTable, prop)

      

And it won't work. I cannot pass utf8mb4 as encoding because Java lib throws an error and these settings prevent my application from crashing, but that saves ??? instead of db instead of symbols. Any idea how to solve this?

+3
scala mysql jdbc utf8mb4 spark-dataframe


source to share


No one has answered this question yet

Check out similar questions:

257
What is the difference between utf8mb4 and utf8 encodings in MySQL?
3
Thrown: java.sql.SQLException: Unsupported character encoding 'utf8mb4'
2
Is it safe to use utf8 tables and columns in mysql utf8mb4 database?
2
Can't change charset and charset from utf8mb4 to utf8 using JDBC for mysql 5.6
2
SET NAMES utf8mb4
1
Can't insert emojis into mysql database
1
mysqli doesn't set charset to utf8mb4
1
mariadb utf8mb4 with dynamic column and procedure
0
jdbc - Unable to save Russian Chinese Arabic letters in mysql
-1
How to support emoji in Azure mysql database



All Articles
Loading...
X
Show
Funny
Dev
Pics