When PreparedStatement is enabled, multiple insert operations on tables containing data types such as blob、text; the inserted value contains the value of the previous SQL insert/update. #1939
Description
- dble version：2.20.04.0 ,use develop version as example
- preconditions ：
preparedStatement is enabled - configs：
schema.xml
rule.xml
server.xml
user.xml
<shardingUser name="root" password="123456" schemas="testdb,testdb2" readOnly="false" maxCon="20"/>
- steps：
step1.
drop table if exists testdb2.hotnews
create table testdb2.hotnews (
id int primary key auto_increment,
title varchar(200),
content text,
image0 blob,
image1 blob,
image2 mediumblob,
image3 longblob
) engine = innodb default character set = 'utf8';
step2.
public class ServerPrepareTest {
public static void testComStmtSendLondData() throws IOException {
Connection conn = null;
PreparedStatement pstmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://192.168.2.157:8066/testdb2?useServerPrepStmts=true", "root", "1234567");
pstmt = conn.prepareStatement("insert into hotnews(id, title, content, image0, image1, image2, image3) values(?,?,?,?,?,?,?)");
pstmt.setInt(1, 1311);
pstmt.setString(2, "hotnew");
pstmt.setBinaryStream(3, new ByteArrayInputStream("this is a content of hotnew".getBytes("UTF-8")));
pstmt.setBlob(4, new ByteArrayInputStream("image0Blob".getBytes("UTF-8")));
pstmt.setBlob(5, new ByteArrayInputStream("image1Blob".getBytes("UTF-8")));
pstmt.setBlob(6, new ByteArrayInputStream("image2Blob".getBytes("UTF-8")));
pstmt.setBlob(7, new ByteArrayInputStream("image3Blob".getBytes("UTF-8")));
pstmt.execute(); // first
pstmt.setInt(1, 1312);
pstmt.setString(2, "hotnew");
pstmt.setBinaryStream(3, new ByteArrayInputStream("this is a content of hotnew".getBytes("UTF-8")));
pstmt.setBlob(4, new ByteArrayInputStream("image0Blob".getBytes("UTF-8")));
pstmt.setBlob(5, new ByteArrayInputStream("image1Blob".getBytes("UTF-8")));
pstmt.setBlob(6, new ByteArrayInputStream("image2Blob".getBytes("UTF-8")));
pstmt.setBlob(7, new ByteArrayInputStream("image3Blob".getBytes("UTF-8")));
pstmt.execute(); // second
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public static void main(String[] args) {
try {
testComStmtSendLondData();
} catch (IOException e) {
e.printStackTrace();
}
}//end main
}
step3.
Execute the main method of the ServerPrepareTest class on the Eclipse or IDEA tool
-
expect result：
1.
+------+--------+-----------------------------+------------+------------+------------+------------+
| id | title | content | image0 | image1 | image2 | image3 |
+------+--------+-----------------------------+------------+------------+------------+------------+
| 1311 | hotnew | this is a content of hotnew | image0Blob | image1Blob | image2Blob | image3Blob |
| 1312 | hotnew | this is a content of hotnew | image0Blob | image1Blob | image2Blob | image3Blob |
+------+--------+-----------------------------+------------+------------+------------+------------+ -
real result：
1.
+------+--------+--------------------------------------------------------+----------------------+----------------------+----------------------+----------------------+
| id | title | content | image0 | image1 | image2 | image3 |
+------+--------+--------------------------------------------------------+----------------------+----------------------+----------------------+----------------------+
| 1311 | hotnew | this is a content of hotnew | image0Blob | image1Blob | image2Blob | image3Blob |
| 1312 | hotnew | this is a content of hotnewthis is a content of hotnew | image0Blobimage0Blob | image1Blobimage1Blob | image2Blobimage2Blob | image3Blobimage3Blob |
+------+--------+--------------------------------------------------------+----------------------+----------------------+----------------------+----------------------+ -
supplements：
1.