【问题描述】:
MySQL 开启 RewriteBatchedStatements 属性后,PreparedStatement在解析一种Insert形式的SQL时发生异常,测试代码如下,使用的MySQL驱动为mysql-connector-java-5.1.36-bin.jar:import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Demo
{
public static final String DBDRIVER = "com.mysql.jdbc.Driver";
public static final String DBURL = "jdbc:mysql://127.0.0.1:3306/S10?rewriteBatchedStatements=true";
public static final String DBUSER = "root";
public static final String DBPASS = "123456";
public static void main(String[] args)
{
try
{
Class.forName(DBDRIVER);
try (Connection conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
PreparedStatement pstmt = conn.prepareStatement(
"INSERT INTO _1033 SET F01 = ?, F02 =? ON DUPLICATE KEY UPDATE F02 = VALUES(F02)"))
{
pstmt.setLong(1, 1);
pstmt.setString(2, "2");
pstmt.executeUpdate();
}
catch (Exception e)
{
e.printStackTrace();
}
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
}
}
异常信息为java.sql.SQLException: java.lang.StringIndexOutOfBoundsException: String index out of range: -36
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:937)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:872)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:904)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:894)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:418)
at com.mysql.jdbc.PreparedStatement.getInstance(PreparedStatement.java:762)
at com.mysql.jdbc.ConnectionImpl.clientPrepareStatement(ConnectionImpl.java:1455)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4205)
at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4109)
at org.querydemo.QueryDemo.main(QueryDemo.java:22)
Caused by: java.lang.StringIndexOutOfBoundsException: String index out of range: -36
at java.lang.String.substring(String.java:1911)
at com.mysql.jdbc.PreparedStatement$ParseInfo.extractValuesClause(PreparedStatement.java:442)
at com.mysql.jdbc.PreparedStatement$ParseInfo.buildRewriteBatchedParams(PreparedStatement.java:371)
at com.mysql.jdbc.PreparedStatement$ParseInfo.(PreparedStatement.java:358)
at com.mysql.jdbc.PreparedStatement$ParseInfo.(PreparedStatement.java:175)
at com.mysql.jdbc.PreparedStatement.(PreparedStatement.java:836)
at com.mysql.jdbc.JDBC4PreparedStatement.(JDBC4PreparedStatement.java:45)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:400)
... 5 more
跟踪代码,发现MySQL的驱动程序在开启 RewriteBatchedStatements 属性后,会对满足如下条件的SQL进行重写protected static boolean canRewrite(String sql, boolean isOnDuplicateKeyUpdate, int locationOfOnDuplicateKeyUpdate, int statementStartPos)
{
boolean rewritableOdku = true;
if (isOnDuplicateKeyUpdate) {
int updateClausePos = StringUtils.indexOfIgnoreCase(locationOfOnDuplicateKeyUpdate, sql, " UPDATE ");
if (updateClausePos != -1) {
rewritableOdku = StringUtils.indexOfIgnoreCase(updateClausePos, sql, "LAST_INSERT_ID", "\"‘`", "\"‘`", StringUtils.SEARCH_MODE__MRK_COM_WS) == -1;
}
}
return (StringUtils.startsWithIgnoreCaseAndWs(sql, "INSERT", statementStartPos)) && (StringUtils.indexOfIgnoreCase(statementStartPos, sql, "SELECT", "\"‘`", "\"‘`", StringUtils.SEARCH_MODE__MRK_COM_WS) == -1) && (rewritableOdku);
}
重写的方法可以参考com.mysql.jdbc.PreparedStatement类的下面两个方法private void buildRewriteBatchedParams(String sql, MySQLConnection conn, DatabaseMetaData metadata, String encoding, SingleByteCharsetConverter converter)
private String extractValuesClause(String sql, String quoteCharStr) throws SQLException
【问题解决】:
修正的措施是将PreparedStatement pstmt = conn.prepareStatement("INSERT INTO _1033 SET F01 = ?, F02 =? ON DUPLICATE KEY UPDATE F02 = VALUES(F02)")
改为PreparedStatement pstmt = conn.prepareStatement("INSERT INTO _1033 (F01, F02 ) VALUES (?, ?) ON DUPLICATE KEY UPDATE F02 = VALUES(F02)")
原文:http://wangzhichao.blog.51cto.com/2643325/1687940