The ResultSet interface obtained from Statement::executeQuery(String) provides the ability to update individual rows and columns in a database table. Once we have created an updatable ResultSet, we can use the provided update methods to update the row by specifying the column name and the new value. There is an update method for each type of object, so we have updateDouble, updateString, etc.
Let’s suppose we have the following database table called Menu.
| ID | PRICE | NAME |
| 1 | 5.95 | New Bacon-ings |
| 2 | 5.95 | Chorizo Your Own Adventure Burger |
Our goal is to update the PRICE column in all records in the table. Here’s how we would update each PRICE in Kotlin.
val sql = "SELECT * FROM MENU"
val rs = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE) //This is required to make the ResultSet updatable
.executeQuery(sql)
while(rs.next()){
with(rs) {
//Update the value of price
updateDouble("PRICE", 6.95)
//Commit the change to the DB
updateRow()
}
}
Our first job is the create an updatable ResultSet object. We do this by passing ResultSet.CONCUR_UPDATABLE the second parameter of Connection::createStatement. The first first paramater is also required, but offers flexibility. I chose ResultSet.TYPE_SCROLL_SENSITIVE but there are other ones that are available to the developer.
We will get the following exception if we forget ResultSet.CONCUR_UPDATABLE
Caused by: ERROR XJ083: 'updateDouble' not allowed because the ResultSet is not an updatable ResultSet. at org.apache.derby.iapi.error.StandardException.newException(Unknown Source) at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(Unknown Source) ... 12 more
Once we create the proper type of Statement and call it’s executeQuery method, we will have an updatable ResultSet object. The ResultSet object has a variety of update methods that accept both the column name or the column index along with the type of data we are updating. In other words, there is an updateDouble(Int, Double) and an updateDouble(String, Double), updateInt(Int, Int) updateInt(String, Int), etc. We use these methods to update each cell in the database table.
Once we have finished updating a value, we must call the udpateRow() method before moving on to the next row in the result set. If we fail to do so, the changes will not get written to the database. Once updateRow() is called, the changes are persisted to the database and we can move onto the next row in the result set.
Example Program
Below is a complete example program that demonstrates how to update rows in a database table.
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>OCJP-DB</groupId>
<artifactId>ocjpdb</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<kotlin.version>1.2.0</kotlin.version>
<main.class>stonesoupprogramming.MainKt</main.class>
</properties>
<dependencies>
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>10.14.1.0</version>
</dependency>
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-stdlib-jre8</artifactId>
<version>${kotlin.version}</version>
</dependency>
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-test</artifactId>
<version>${kotlin.version}</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<sourceDirectory>src/main/kotlin</sourceDirectory>
<plugins>
<plugin>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-plugin</artifactId>
<version>${kotlin.version}</version>
<executions>
<execution>
<id>compile</id>
<phase>compile</phase>
<goals>
<goal>compile</goal>
</goals>
</execution>
<execution>
<id>test-compile</id>
<phase>test-compile</phase>
<goals>
<goal>test-compile</goal>
</goals>
</execution>
</executions>
<configuration>
<jvmTarget>1.8</jvmTarget>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<version>2.6</version>
<configuration>
<archive>
<manifest>
<addClasspath>true</addClasspath>
<mainClass>${main.class}</mainClass>
</manifest>
</archive>
</configuration>
</plugin>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>1.2.1</version>
<executions>
<execution>
<phase>test</phase>
<goals>
<goal>java</goal>
</goals>
</execution>
</executions>
<configuration>
<mainClass>${main.class}</mainClass>
</configuration>
</plugin>
</plugins>
</build>
</project>
BurgerMenu.kt
package stonesoupprogramming
import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.util.*
private const val SCHEMA = "BURGERS"
private const val TABLE = "MENU"
fun main(args: Array<String>) {
val properties = Properties()
//Populate the properties file with user name and password
with(properties) {
put("user", "admin")
put("password", "pw")
}
//Open a connection to the database
DriverManager
.getConnection("jdbc:derby:stonesoup;create=true", properties)
.use { connection ->
prepareTable(connection)
insertItems(connection)
queryRows(connection)
updatePrice(connection, 6.95)
println("\nAfter update")
queryRows(connection)
}
}
fun updatePrice(connection: Connection, price: Double) {
val sql = "SELECT * FROM $SCHEMA.$TABLE"
val rs = connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE) //This is required to make the ResultSet updatable
.executeQuery(sql)
while(rs.next()){
with(rs) {
updateDouble("PRICE", price)
updateRow()
}
}
}
private fun queryRows(connection: Connection) {
val sql = "SELECT * FROM $SCHEMA.$TABLE"
val rs = connection.createStatement().executeQuery(sql)
while (rs.next()) {
println("ID: ${rs.getInt("ID")}\t" +
"PRICE: $${rs.getDouble("PRICE")}\t" +
"NAME: ${rs.getString("ITEM")}")
}
}
private fun insertItems(connection: Connection) {
insertRow(connection, 1, "'New Bacon-ings'", 5.95)
insertRow(connection, 2, "'Chorizo Your Own Adventure Burger'", 5.95)
insertRow(connection, 3, "'Not If I Can Kelp It Burger'", 5.95)
insertRow(connection, 4, "'The Longest Chard Burger'", 5.95)
insertRow(connection, 5, "'Peas and Thank You Burger'", 5.95)
insertRow(connection, 6, "'Cole came, cole slaw, cole conquered burger'", 5.95)
insertRow(connection, 7, "'Chili Wonka Burger'", 5.95)
insertRow(connection, 8, "'The Clear and Present Ginger Burger'", 5.95)
}
private fun insertRow(connection: Connection, id: Int, name: String, price: Double) {
val sql = "INSERT INTO $SCHEMA.$TABLE VALUES ($id, $name, $price)"
with(connection) {
createStatement().execute(sql)
commit()
}
}
private fun prepareTable(connection: Connection) {
val metaData = connection.metaData
val rs = metaData.getTables(null, SCHEMA, TABLE, null)
if (!rs.next()) {
createTable(connection)
} else {
truncateTable(connection)
}
}
private fun truncateTable(connection: Connection) {
val sql = "TRUNCATE TABLE $SCHEMA.$TABLE"
with (connection) {
createStatement().execute(sql)
commit()
}
}
private fun createTable(connection: Connection) {
//SQL statement to create a table
val sql = """
CREATE TABLE $SCHEMA.$TABLE (
ID int primary key,
ITEM varchar(255),
PRICE float)
""".trimMargin()
with(connection) {
//Get and instance of statement from the connection and use
//the execute() method to execute the sql
createStatement().execute(sql)
//Commit the change to the database
commit()
}
}
Stone Soup Programming 