java中jdbc/sql出现编码问题

Question

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column '???è??é“?è??' in 'field list'

at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1402)
at ...

我其他地方以及传进SQL里面的参数的编码都没有问题，就是执行jdbc中的executeUpdate()方法执行sql插入数据时就出现这个问题。
比如：
String book_name = request.getParameter("book_name");//中文显示正常
String sql = "insert into shopping_car(name,book_name) values("123",book_name)";//编码显示正常，为中文
stmt.executeUpdate(sql);//执行该方法的时候就出现上面的乱码错误

Answer 1

First send a sql command "set names utf8" specifying the character set, and then perform the insertion operation.

Answer 2

What is the character set of your database server? Execute the following command on the database server to check the character set first:

SHOW VARIABLES LIKE 'CHAR%'；

Finished, set the character set of the data submitted by the client to be the same as the character set of the database server...

Answer 3

Okay, it turns out that in the original jdbc operation, the string must be enclosed in single quotes

Answer 4

It is recommended that both the MySQL database server and client use UTF-8 encoding. How to set the MySQL server encoding and client connection URL is similar to the following:

url="jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8"

Reference: JDBC url for MySQL configuration to use utf8 character encoding

Answer 5

String sql = "insert into shopping_car(name,book_name) values(?,?)";
stmt.setString(1, "123");
stmt.setString(2, book_name);

Answer 6

It is not recommended to use sql in this way, because sql injection is easy to occur. It is recommended to use another placeholder method, or ORM, such as mybatis, hibernate, etc.
Correction:

String sql = "insert into shopping_car(name,book_name) values(\"123\","+book_name+")"