Entity Manipulation

In addition to querying, sequence APIs also support the manipulation of entity objects. We need to define some extension properties first, creating sequence objects via sequenceOf:

1
2

val Database.departments get() = this.sequenceOf(Departments)
val Database.employees get() = this.sequenceOf(Employees)

Insert

Function add is an extension of EntitySequence class, it inserts an entity object into the database, and returns the effected record number after the insertion completes. Here is the signature:

1

fun <E : Entity<E>, T : Table<E>> EntitySequence<E, T>.add(entity: E): Int

To use this function, we need to create an entity object first. As we mentioned in the former sections, we can create entity objects by calling the Entity.create function, or using a companion object extending from Entity.Factory. Here we choose the later way. The following code creates an employee object, and insert it into the database:

1
2
3
4
5
6
7
8
9

val employee = Employee {
    name = "jerry"
    job = "trainee"
    hireDate = LocalDate.now()
    salary = 50
    department = database.departments.find { it.name eq "tech" }
}

database.employees.add(employee)

In this example, we create an employee object and fill its properties with some initial values. Please note the property department, whose value is an entity object just obtained from the database via sequence APIs. When we call the add function, the ID of the referenced entity will be saved into Employees table. The generated SQL is as follows:

1
2

insert into t_employee (name, job, hire_date, salary, department_id) 
values (?, ?, ?, ?, ?) 

It can be seen that the generated SQL contains all the existing properties in the entity object, and if we remove the assignment of a property, then it’s also removed from the SQL. For instance, if we create the employee object with only a name given Employee { name = "jerry" }, then the generated SQL will change to insert into t_employee (name) values (?).

If we use an auto-increment key in our table, we just need to tell Ktorm which column is the primary key by calling the primaryKey function on the column registration, then the add function will obtain the generated key from the database and fill it into the corresponding property after the insertion completes. But this requires us not to set the primary key’s value beforehand, otherwise, if you do that, the given value will be inserted into the database, and no keys will be generated.

Let’s review the example above, we didn’t set the value of property id, then we could retrieve the generated key via employee.id after the add function returned. But if we set the id to some value, then the value would be inserted into the database, and the employee.id would not change after the insertion completed.

Update

We’ve known that Ktorm’s entity classes are defined as interfaces extending from Entity which injects many useful functions to our entity objects, so let’s learn its definition now:

1
2
3
4
5
6
7
8
9
10
11
12

interface Entity<E : Entity<E>> : Serializable {
    
    fun flushChanges(): Int

    fun discardChanges()

    fun delete(): Int

    operator fun get(name: String): Any?

    operator fun set(name: String, value: Any?)
}

It can be seen that there is a flushChanges function in the Entity interface. This function updates all the changes of the current entity into the database and returns the affected record number after the update completes. Typical usage is to obtain entity objects via sequence APIs first, then modify their property values according to our requirements, finally call the flushChanges function to save the modifications.

1
2
3
4

val employee = database.employees.find { it.id eq 5 } ?: return
employee.job = "engineer"
employee.salary = 100
employee.flushChanges()

The code above generates two SQLs. While the first one is generated by find, and the second one is generated by flushChanges, that is:

1

update t_employee set job = ?, salary = ? where id = ? 

Let’s try to remove the assignment employee.salary = 100 and only modify the job property, then the generated SQL will change to update t_employee set job = ? where id = ?; And if we call flushChanges without any properties changed, then nothing happens. This indicates that Ktorm can track the status changes of entity objects, that’s implemented by JDK dynamic proxy, and that’s why Ktorm requires us to define entity classes as interfaces.

The discardChanges function clears the tracked property changes in an entity object, after calling this function, the flushChanges doesn’t do anything anymore because the property changes are discarded. Additional, if the flushChanges is called twice or more continuously, only the first calling will do the update, all the following callings will be ignored, that’s because the property changes are already updated into the database after the first calling, and Ktorm clears the tracked status after the update completes.

Using flushChanges, we also need to note that:

1. The function requires a primary key specified in the table object via primaryKey, otherwise Ktorm doesn’t know how to identify entity objects and will throw an exception.
2. The entity objects calling flushChanges must be ATTACHED to the database first. In Ktorm’s implementation, every entity object holds a reference fromDatabase. For entity objects obtained by sequence APIs, their fromDatabase references point to the database they are obtained from. For entity objects created by Entity.create or Entity.Factory, their fromDatabase references are null initially, so we can not call flushChanges on them. But once we use them with add or update function, fromDatabase will be modified to the current database, so we will be able to call flushChanges on them afterwards.

For the second point above, a simple explanation is that the entity objects calling flushChanges must be obtained from sequence APIs or already saved into the database via add or update function. Please also note that when we are serializing entities, Ktorm will save their property values only, any other data (including fromDatabase) that used to track entity status are lost (marked as transient). So we can not obtain an entity object from one system, then flush its changes into the database in another system.

In version 3.1, Ktorm also provides an update function that can update all the existing properties of an entity object to the database. Using this function, the entity object is not required to be attached to the database first. That means, comparing to flushChanges, we don’t have to obtain an entity object from the database first before performing the update. The usage is as follows:

1
2
3
4
5
6
7

val employee = Employee {
    id = 5
    job = "engineer"
    salary = 100
}

database.employees.update(employee)

Generated SQL:

1

update t_employee set job = ?, salary = ? where id = ? 

Delete

Entity interface also provides a delete function, which deletes the entity object in the database, and returns the affected record number after the deletion completes. Typical usage is to obtain entity objects via sequence APIs first, then call the delete function to delete them according to our requirements.

1
2

val employee = database.employees.find { it.id eq 5 } ?: return
employee.delete()

The delete function generates a SQL like:

1

delete from t_employee where id = ? 

Similar to flushChanges, we also need to note that:

1. The function requires a primary key specified in the table object via primaryKey, otherwise, Ktorm doesn’t know how to identify entity objects.
2. The entity object calling this function must be ATTACHED to the database first.

There are also some other functions that can delete entities, they are removeIf and clear. While removeIf deletes records in the table that matches a given condition, and clear deletes all records in a table. Here, we use removeIf to delete all the employees in department 1:

1

database.employees.removeIf { it.departmentId eq 1 }

Generated SQL:

1

delete from t_employee where department_id = ?