JDBC Update operation return value and insert operation return PRIMARY key

JDBC Update operation return value

When you manipulate the database, the update operation returns the number of database update rows, but in the case of JDBC, the number of database update rows is not returned by default, which may result in unexpected results in the actual operation. When using the ORM framework, such as MyBatis and Hibernate, there is also an appeal issue because the JDBC API is also used at the bottom.

JDBC API

First, let's take a look at the JDBC API that is worth defining for the update operation, mainly involving Statement.executeupdate () and Preparedstatement.executeupdate () two operations, Its return value is defined as:

either (1forLanguageor (20forreturn nothing

That is, the DDL statement returns a value of 0, and the DML statement return value simply says return row count, not the return affect count.

Several examples of JDBC update operations

Let's write a few examples and actually look at the return value of the JDBC update. Note: The MYSQ database is used for testing:

JDBC Connection Properties:

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://120.55.59.65:3306/study_jdbc?characterEncoding=utf8username=rootpassword=********

JDBC DDL:

Connection connection = DriverUtils.getConnection"DROP TABLE IF EXISTS `update_return_value`;"" CREATE TABLE `update_return_value`"    "(`id` bigint(20) PRIMARY KEY NOT NULL AUTO_INCREMENT, `name` varchar(255))"    "ENGINE=InnoDB AUTO_INCREMENT=1;";PreparedStatement preparedStatement = connection.prepareStatement(sql1);preparedStatement.executeUpdate();PreparedStatement statement = connection.prepareStatement(Sql2);int updateValue = statement.executeUpdate();System.out.println("Update Return Value: " + updateValue);

Console output: Update Return value:0

JDBC Insert:

Connection connection = DriverUtils.getConnection"INSERT INTO update_return_value(name) VALUES (?)";PreparedStatement preparedStatement = connection.prepareStatement(sql);preparedStatement.setString(1"2222");int updateValue = preparedStatement.executeUpdate();System.out.println("Update Return Value: " + updateValue);

Console output: Update Return value:1

JDBC Correct update operation, note here, we repeat the execution 2 times:

Connection connection = DriverUtils.getConnection"UPDATE update_return_value SET name = ? WHERE id = ?";PreparedStatement preparedStatement = connection.prepareStatement(sql);preparedStatement.setString(1"11111");preparedStatement.setLong(2, 1L);int updateValue = preparedStatement.executeUpdate();System.out.println("Update Return Value: " + updateValue);

Console output: Update return value:1/n update return Value:1

JDBC Incorrect update operation:

Connection connection = DriverUtils.getConnection"UPDATE update_return_value SET name = ? WHERE id = ?";PreparedStatement preparedStatement = connection.prepareStatement(sql);preparedStatement.setString(1"11111");preparedStatement.setLong(2, 2L);int updateValue = preparedStatement.executeUpdate();System.out.println("Update Return Value: " + updateValue);

Console output: Update Return value:0

As we can see from the above, the unexpected is that the correct update operation performed 2 times the result is 1, if the return value is the number of rows affected the second should be 0, because the database did not change the second update. Then we change the URL in the JDBC properties and then re-execute it, and the second update returns a value of 0.

driver=com.mysql.jdbc.Driverurl=jdbc:mysql://120.55.59.65:3306/study_jdbc?characterEncoding=utf8&useAffectedRows=trueusername=rootpassword=*********

Summarize

The JDBC update operation DML statement returns the number of rows-matches by default, not the number of rows-affect, and the number of rows-affect that can be added to the URL userAffectedRows=true

The JDBC insert operation returns the primary key

JDBC Returns a primary key by specifying the return primary key when creating PreparedStatement, and then querying through the Getgeneratekeys () method. Note: Here the primary key must be auto Increment

Most databases return primary key methods, such as MySQL

Connection connection = DriverUtils.getConnection"INSERT INTO insert_return_key(name) VALUES (?)";PreparedStatement preparedStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);preparedStatement.setString(1"3333");preparedStatement.executeUpdate();ResultSet generatedKeys = preparedStatement.getGeneratedKeys();while (generatedKeys.next()) {    long generateKey = generatedKeys.getLong(1);}

Oracle returns primary key, and of course it can be used for databases such as MySQL

Because Oracle generates primary keys in a more specific way, the above approach does not apply to Oracle

Connection connection = DriverUtils.getConnection"INSERT INTO insert_return_key(name) VALUES (?)";String[] keysName = {"id"};PreparedStatement preparedStatement = connection.prepareStatement(sql, keysName);preparedStatement.setString(1"3333");preparedStatement.executeUpdate();ResultSet generatedKeys = preparedStatement.getGeneratedKeys();while (generatedKeys.next()) {      long generateKey = generatedKeys.getLong(1);}

JDBC Update operation return value and insert operation return PRIMARY key