[CONJ-322] ResultSet.update* methods aren't implemented Created: 2016-07-08  Updated: 2020-08-25  Resolved: 2017-07-23

Status: Closed
Project: MariaDB Connector/J
Component/s: JDBC 4.2 compatibility
Affects Version/s: None
Fix Version/s: 2.1.0

Type: Task Priority: Major
Reporter: Diego Dupin Assignee: Diego Dupin
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by CONJ-165 Updates to DECIMAL field through Java... Closed
is duplicated by CONJ-472 Unable to update ResultSet Closed
Sprint: Sprint connector/j 2.1.0

 Description   

ResultSet.update* methods aren't implemented

statement using ResultSet.CONCUR_UPDATABLE must be able to update record.
exemple:

       Statement stmt = con.createStatement(
 
                                      ResultSet.TYPE_SCROLL_INSENSITIVE,
 
                                      ResultSet.CONCUR_UPDATABLE);
 
       ResultSet rs = stmt.executeQuery("SELECT age FROM TABLE2");
 
       // rs will be scrollable, will not show changes made by others,
 
       // and will be updatable
 
	while(rs.next()){
 
		//Retrieve by column name
 
		int newAge = rs.getInt(1) + 5;
 
		rs.updateDouble( 1 , newAge );
 
		rs.updateRow();
 
	}



 Comments   
Comment by Archimedes Trajano [ 2017-05-16 ]

Does MariaDB support updating resultsets meaning it just needs to be implemented in the Java connector or does it fundamentally not support it?

Comment by Diego Dupin [ 2017-05-17 ]

Protocol doesn't support it.
But still, MySQL Driver support this functionality by executing lot of underlying queries.
It's currently in study for next version.

Using UPDATE query will always have better performance.

Comment by Archimedes Trajano [ 2017-05-17 ]

Well if underlying it does the queries then for sure it will have better performance. Perhaps MariaDB should implement it on the server level instead basically when it requests a result set that is ResultSet.CONCUR_UPDATABLE the rows are "locked" on the database side which will then take in a non standard SQL construct of multi-update. A further optimization can be done by stating...

Updatable Result Sets must contain all the primary key columns of a table AND must not be the result of a join AND updates on the primary key(s) is not allowed.

Comment by Diego Dupin [ 2017-07-23 ]

available using SNAPSHOT : 

<repositories>
 
    <repository>
 
        <id>sonatype-nexus-snapshots</id>
 
        <name>Sonatype Nexus Snapshots</name>
 
        <url>https://oss.sonatype.org/content/repositories/snapshots</url>
 
    </repository>
 
</repositories>
 
 
 
<dependencies>
 
    <dependency>
 
        <groupId>org.mariadb.jdbc</groupId>
 
        <artifactId>mariadb-java-client</artifactId>
 
        <version>2.1.0-SNAPSHOT</version>
 
    </dependency>
 
</dependencies>

There is different restrictions :

  • cannot be use if different tables are in resultset
  • for update/delete, primary key must be in resultset
  • for insert, primary key is not mandatory if generated with AUTO_INCREMENT
Generated at Thu Feb 08 03:14:49 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.