MySQL PreparedStatement an exception after opening rewritebatchedstatements

"Problem description":

After MySQL turns on the Rewritebatchedstatements property, PreparedStatement has an exception when parsing an insert form of SQL, and the test code is as follows The MySQL driver used for 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");                &nbsP;pstmt.executeupdate ();            }             catch  (exception e)              {                 e.printstacktrace ();             }        }         catch  (classnotfoundexception e)         {             e.printstacktrace ();         }    }}

Exception information is

Java.sql.sqlexception: java.lang.stringindexoutofboundsexception: string index out of  range: -36at 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: - 36at 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.<init> (preparedstatement.java:358) at com.mysql.jdbc.preparedstatement$parseinfo.<init > (preparedstatement.java:175) at com.mysql.jdbc.preparedstatement.<init> (PreparedStatement.java : 836) at com.mysql.jdbc.jdbc4preparedstatement.<init> (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

Tracking code, found that the MySQL driver after the  rewritebatchedstatements property is turned on, the following conditions are satisfied with the SQL to override

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);}

The overridden method can refer to the following two methods of the Com.mysql.jdbc.PreparedStatement class

private void Buildrewritebatchedparams (String sql, Mysqlconnection conn, DatabaseMetaData metadata, string encoding, Singlebytecharsetconverter Converter) private string Extractvaluesclause (String sql, String quotecharstr) throws SQLException

"Problem Solving":

The revised measure is to

PreparedStatement pstmt = conn.preparestatement ("INSERT into _1033 SET F01 =?, F02 =?") On DUPLICATE KEY UPDATE F02 = VALUES (F02))

Switch

PreparedStatement pstmt = conn.preparestatement ("INSERT into _1033 (F01, F02) VALUES (?,?) On DUPLICATE KEY UPDATE F02 = VALUES (F02))

This article is from the "Technical Drip" blog, please be sure to keep this source http://wangzhichao.blog.51cto.com/2643325/1687940

MySQL PreparedStatement an exception after opening rewritebatchedstatements