KORMite

KORMite is a library that aims to abstract database operations to simple kotlin functions. Therefore, the library provides a Table interface with methods for database operations. See Usage for a detailed explanation.

Installation

KORMite is hosted on a custom repository at https://maven.mineking.dev. Replace VERSION with the latest version (without the v prefix). Alternatively, you can download the artifacts from jitpack (not recommended).

Gradle

repositories {
    maven("https://maven.mineking.dev/releases")
}

dependencies {
    implementation("de.mineking.KORMite:KORMite-core:VERSION")
}

Maven

<repositories>
    <repository>
        <id>mineking</id>
        <url>https://maven.mineking.dev/releases</url>
    </repository>
</repositories>

<dependencies>
    <dependency>
        <groupId>de.mineking.KORMite</groupId>
        <artifactId>KORMite-core</artifactId>
        <version>VERSION</version>
    </dependency>
</dependencies>

Vendors

The core library only contains the infrastructure for type mapping etc. To actually connect to a database and work with it, you also need the dependency for the database vendor you use, that then contains the actual implementation for SQL generation etc.

For PostgreSQL (recommended), you can use de.mineking.KORMite:KORMite-postgres:VERSION.

If you want to use SQLite instead, you can use de.mineking.KORMite:KORMite-sqlite:VERSION.

Extensions

There are extensions for discord and minecraft available that provide TypeMappers for some types for the corresponding platform. You can get these from de.mineking.KORMite:KORMite-discord:VERSION for JDA or de.mineking.KORMite:KORMite-minecraft:VERSION for Paper.

Usage

The basic usage looks like this:

data class UserDao(
    @Autoincrement @Key @Column val id: Int = 0, //Autoincrementing key column with name id. When having the value '0', the value will not be passed in an insert statement and will therefore be generated by the database 
    @Column val name: String, 
    @Column val age: Int 
)

fun main() { 
    val connection = PostgresConnection("localhost:5432/test", user = "test", password = "test") 
    val table = connection.getTable(name = "users", create = true) { UserDao(name = "", age = 0) } //Create table with name and function to provide a base instance
    //Every time a value is read, an instance is created using this function and will that put in the values from the database into the fields of that instance
    //With 'create = true', the library will automatically create a table with the specified columns. However, this will NOT update an existing table to match the specifications, if your class changed since the table was created  
    
    assertEquals(table.insert(UserDao(name = "Max", age = 25)).isSucces())
    
    val result = table.select().list() //Will read all table rows, create an UserDao instance for every row and then put into a List
    assertEquals(1, result.size)
    assertEquals(1, result[0].id) //The id was generated by the database and also updated in the instance
    assertEquals("Max", result[0].name)
    
    table.update(result[0].copy(name = "Tom")) //We can update a row in the table. The row to update is identified by the key column(s) 
    assertEquals("Tom", table.select().first().name)
    
    table.delete() //This will delete all rows in the table (NOT the table itself)
    assertEquals(0, table.selectRowCount())
}

Note how all properties that should appear in the database have to be annotated with @Column. Using this annotation, you can also specify the name the column should have in the database.

When you don't specify a name, the name of the kotlin property is used. However, the name is passed to a NamingStrategy beforehand. By default, NamingStrategy.SNAKE_CASE is used. However, you can specify your own when calling getTable or in the Connection constructor.

Conditions

In many cases, you need to specify conditions on what to select or delete. There is a simple way to do that:

fun main() {
	//Your connection and table declaration...
	
	table.select(where = (property(UserDao::name) isEqualTo value("Max")) and (property(UserDao::age).isBetween(value(18), value(30))))
}

Conditions are based on two parts: Nodes and Wheres.

Nodes

A node represents either a database column (property) or a constant passed as a parameter (value). Nodes can also be appended together using the + operator.

property

A property node will reference a database column. You can simply create a property node by calling property(YouDaoClass::yourProperty) (Referencing the kotlin property). This way the references will automatically infer the property type and always stay synced to your Dao-Classes.

You can also reference properties by name (property<Type>("yourProperty")). Note: you have to specify the kotlin property name, even if you passed a custom name in @Column.

There is also a special syntax for accessing more complex columns:

Name	Example	Description
Array Index	array[0]	Access an index of an array. This is zero based (like in kotlin) as opposed to how it behaves by default in SQL
Reference	user->name	Access a referenced tables' column (See References).
Virtual Column	location.world	Access a virtual child of this column (See Virtual Columns)

value

You can specify any object here. As long as a TypeMapper for that object is found, it will be correctly passed as a statement parameter. All values are passed using prepared statement, so you are safe to use them without having to worry about sql injection.

Wheres

Nodes can easily be converted to a where condition. You can simply call Where(node). However, in most cases you shouldn't use this but instead make use of the default functions.

For example Where(property(UserDao::age) + " isEqualTo " + value(1)) can be written as property(UserDao::age) isEqualTo value(1), because all default operations have (infix) functions available.

SQL Functions

Sometimes you want to use some SQL functions when selecting values or other operations. KORMite simply allows you to use SQL functions with the following syntax: "yourFunction"(node1, node2, ...). Foe example, to get the uppercase value of a string using the SQL uppper function you can do "upper"(property(UserDao::name)). This will be translated to upper("users"."name") (This will work for both value and property nodes).

For the most basic SQL functions there are also default extension functions available. Instead of "upper"(property(UserDao::name)) you can write property(UserDao::name).uppercase(). These extension functions are type sensitive, so you can only call the uppercase extension function on String properties.

Postgres

There are also some Postgres SQL features supported by default (only when importing the KORMite-postgres module as well):

• Array Indexing (value(listOf(1, 2))[0], also supports nodes as indices. Note: The indexing is zero based and NOT 1 based like in SQL)
• Array contains check (value(listOf(1, 2)) contains value(1). Supports any combination of value and property nodes)
• Array size (value(listOf(1, 2)).size)

And a few more...

Single-Column access

There are also situations where you want to only select or update a single column, without handling the entire object for the row.

fun main() {
    //Your connection and table declaration...
	
    val names = table.selectValue(property(UserDao::name), where = property(UserDao::age) isGreaterThan value(5)).list() //This will only select the name column. You can also specify conditions and all parameters that the normal select supports
    
    table.update(property(UserDao::name) to value("Test"), where = property(UserDao::age) isGreaterThan value(5)) //Update only the name column. You can also optionally specify a condition here 
}

Multi-Column access

You can also select only specific columns and still let them automatically be converted to you Dao Class:

fun main() {
    //Your connection declaration...
    
    val user = table.select(property(UserDao::name)).first()
    assertEquals("Max", user.name)
    assertEquals(0, user.age) //The age is 0 because the column was not selected so the value provided in the instance creator is not changed
}

This also allows you to modify your classes before selecting. For example:

fun main() {
    //Your connection declaration...
    
    val user = table.select(property(UserDao::name).uppercase()).first()
    assertEquals("MAX", user.name) //Because we selected the uppercase value of the property (executed by the SQL host), the users name will now be MAX instead of Max
}

Type Mappers

TypeMappers are used by this library to define how a kotlin property is mapped to a database column and vice versa.

Default Types

For the basic types, this library already comes with a set of TypeMappers (otherwise you wouldn't be able to use it for even the simplest cases). These include numbers, string, booleans etc.

Enums and arrays are also supported by default. Enums are simply stored as strings. Arrays, Lists and Sets will simply create a column with an array type. Every type will automatically support a corresponding array type by default, even for your custom type mappers.

Custom Type Mappers

you can also register custom TypeMappers for your own classes, to be able to store them as a database column. You can either create an implementation of the TypeMapper interface directly, however, there are two functions to simplify the process for easy cases:

fun main() {
    //Your connection declaration...
	
    //This is how you create and register a custom TypeMapper, on the example of the default implementation of Strings (you don't have to do this to use strings as this mapper is already added by default)
    connection.typeMappers += typeMapper<String?>(PostgresType.TEXT, ResultSet::getString) { value, statement, position -> statement.setString(position, value) }
    
    //You might have cases where you want to map a complex type, so a simpler type, that is then used for the column
    connection.typeMappers += typeMapper<Color?, String?>(PostgresMappers.INTEGER, { it?.let { Color(it) } }, Color::rgb)
    //This creates a TypeMapper with intermediate type INTEGER that then only has to specify how to convert between Color and Int, while the specified intermediate TypeMapper handles the direct access
}

References

References are a way to store complex objects in a different table and store a reference to the corresponding row in a single column.

data class AuthorDao(
    @Autoincrement @Key @Column val id: Int = 0, 
    @Column val name: String
)

data class BookDao(
    @Autoincrement @Key @Column val id: Int = 0, 
    @Column val name: String,
    @Reference("authors") @Column val author: Author
)

fun main() {
    val connection = PostgresConnection("localhost:5432/test", user = "test", password = "test")
    val authorTable = connection.getTable(name = "authors", create = true) { AuthorDao(name = "") }
    val bookTable = connection.getTable(name = "authors", create = true) { BookDao(name = "", author = AuthorDao(name = "")) }
    
    val author = authorTable.insert(AuthorDao(name = "Test Author")).value!!
    val book = bookTable.insert(BookDao(name = "Test Book", author = author)).value!!
    
    assertEquals(author, bookTable.selectValue(property(BookDao::author)).first())
    assertEquals("Test Author", bookTable.select(property(BookDao::author, AuthorDao::name)).first())
}

Virtual Columns

Virtual columns can be used to store additional information for a property in a different column in the database. An example are Locations in Minecraft, where you might want to store the location itself as an array of doubles and the world as a separate column. (This is already implemented in the minecraft extension)

fun main() {
    //Your connection and table declaration...
	
    val world = table.selectValue(property<World>("location.world")).first() //Select only the world
    table.update(property<World>("location.world") to value(world2)) //Update only the world
}

Data Objects

DataObject is an interface that your row classes can implement to simplify some operations. It allows calling .insert(), .update() and .delete() directly on the object. It also has the methods beforeWrite and afterRead that you can override to listen to the corresponding event.

Lastly, it has a utility method that allows you to select a list of rows from another table which reference this object with some column.

Custom Tables

You can also create custom interfaces that allow you to predefine certain operations that can then be used later:

interface UserTable : Table<UserDao> {
    @Select //You can use the @Select annotation to automatically generate a select statement
    fun getAllUsers(): List<UserDao>
    
    @Select //You can also select a single element. The return type of the function will determine this behavior
    //All parameters with the @Condition annotation will be used as a where condition when selecting (All parameters have to match)
    fun getUserByName(@Condition name: String): UserDao? 
    
    @Insert //You can create insert statement functions with @Insert
    //All method parameters annotated with @Parameter will be passed to the insert. All properties that are not defined here will have the value they have after the instance was created by your instance creator
    fun createUser(@Parameter name: String, @Parameter age: Int): UserDao
    
    @Delete //You can create delete statement functions with @Delete
    //As above, all parameters with @Condition will be used as condition. For example a delete function without any parameters will delete all rows
    fun deleteUser(@Condition id: Int): Int
    
    //Custom function
    fun getAdults() = select(where = property(UserDao::age) isGreaterThan value(18))
    
    @Select
    //Will select all users older than minAge. You can pass a custom comparison operation as parameter to the @Condition annotation. The default is " = "
    fun getOlderThan(@Parameter(name = "age", operation = " > ") minAge: Int): List<UserDao>
    
    @Update // You can create update statements with @Update
    //You can combine @Condition and @Parameter in @Update. As above, @Condition will be used as condition while the parameters with @Parameter will update the respective columns in the rows matching the condition
    fun updateName(@Condition id: Int, @Parameter name: String): Int
}

fun main() {
    //Your connection and table declaration...
    
    //Pass the type of your interface as type parameter. KORMite will create an instance of this interface via dynamic proxy
    val table = connection.getTable<_, UserTable>(name = "users", create = true) { UserDao(name = "", age = 0) }
    
    val tom = table.createUser(name = "Tom", age = 16)
    val alex = table.createUser(name = "Alex", age = 20)
    
    assertEquals(2, table.getAllUsers().size)
    assertEquals(1, table.getAdults().size)
    
    table.deleteUser(tom.id)
    assertEquals(1, table.getAllUsers().size)
    
    assertEquals(1, table.updateName(alex.id, "Test"))
    assertEquals("Test", table.getAllUsers().first().name)
}

Warning

Using the autogenerated functions with annotations requires you to keep you parameter names. In gradle you can do this with:

kotlin {
    compilerOptions {
        javaParameters = true
    }
}

Alternatively, you can pass the name of the property as parameter to @Parameter (e.g. @Parameter(name = "id")). The same applies to @Condition.