The ResultSet interface found in JDK’s JDBC API may be used to delete rows in a database individually. Here is a Kotlin example that deletes rows from a Burgers.Menu table in the database that match the supplied primary keys.
fun deleteRows(connection: Connection, pks: List<Int>) {
//Create an updatable Statement (using ResultSet.CONCUR_UPDATABLE)
val rs = connection
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
.executeQuery("SELECT * FROM BURGERS.MENU WHERE ID IN (${pks.joinToString(", ")})")
while(rs.next()){
//Delete each row in the ResultSet
rs.deleteRow()
}
}
The crux of the operation is found on line 9 with the call to ResultSet::deleteRow(). The method call will simply delete the row from the database. However, it’s important to have the proper ResultSet object prior to calling deleteRow(). On lines 3-5, we create a Statement object (Connection::createStatment) and pass in the ResultSet.CONCUR_UPDATABLE constant to the second paramter of createStaement. If we fail to do so, we will get a SQLException.
It should be noted that while this example shows how to delete records from the database, it should not be a preferred route. The reason is that we are duplicating logic the database already provides. A properly formed SQL statement will perform a bulk delete operation much more efficiently than using ResultSet::deleteRow. ResultSet::deleteRow is much better suited in application where a user is browsing through records and delete items one at a time.
Example Program
Here is a complete Kotlin program that demonstrates the topic discussed in this post.
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)
println("\nAfter delete")
deleteRows(connection, listOf(1, 3, 5, 7))
queryRows(connection)
}
}
fun deleteRows(connection: Connection, pks: List<Int>) {
//Create an updatable Statement (using ResultSet.CONCUR_UPDATABLE)
val rs = connection
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
.executeQuery("SELECT * FROM BURGERS.MENU WHERE ID IN (${pks.joinToString(", ")})")
while(rs.next()){
//Delete each row in the ResultSet
rs.deleteRow()
}
}
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) {
//Obtain an updatable ResultSet object (ResultSet.CONCUR_UPDATABLE)
val resultSet = connection
.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE)
.executeQuery("SELECT * FROM BURGERS.MENU")
with(resultSet){
//Move the ResultSet to the insert row
moveToInsertRow()
//Populate each column using the relevant update method
updateInt("ID", id)
updateString("ITEM", name)
updateDouble("PRICE", price)
//Now insert the row
insertRow()
}
}
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 