Kwery is an SQL library for Kotlin

This is my first object that utilizes auto increment in MySQL. I've set the DAO to utilize generated keys:

/**
 * @author [David Maple](mailto:[email protected])
 */
data class Topic(
        val name: String,
        val dateCreated: LocalDateTime = LocalDateTime.now(),
        val dateModified: LocalDateTime = LocalDateTime.now(),
        val version: Long = 1,
        val id: Int = 0
)

/**
 * @author [David Maple](mailto:[email protected])
 */
object topicTable : Table<Topic, Int>("topic", TableConfiguration(
        standardDefaults + timeDefaults,
        standardConverters + timeConverters,
        camelToLowerUnderscore
)), VersionedWithLong {
    val Id by topicTable.col(Topic::id, id = true)
    val Name by topicTable.col(Topic::name)
    val DateCreated by topicTable.col(Topic::dateCreated)
    val DateModified by topicTable.col(Topic::dateModified)
    val Version by topicTable.col(Topic::version, version = true)

    override fun idColumns(id: Int) = setOf(Id of id)

    override fun create(value: Value<Topic>) = Topic(
            id = value of Id,
            dateCreated = value of DateCreated,
            dateModified = value of DateModified,
            name = value of Name,
            version = value of Version
    )
}

/**
 * @author [David Maple](mailto:[email protected])
 */
class TopicDao(session: Session) : AbstractDao<Topic, Int>(
        session,
        topicTable,
        { it.id },
        "bigint(20) unsigned",
        IdStrategy.Generated,
        0
) {
    init {
        addListener(object : Listener {
            override fun onEvent(session: Session, event: Event) {
                if (event is TransformingEvent && event.new is Topic) {
                    val entity = event.new as Topic
                    if (event is PreInsertEvent) {
                        event.transformed = entity.copy(
                                dateCreated = LocalDateTime.now(),
                                dateModified = LocalDateTime.now()
                        )
                    }
                    if (event is PreUpdateEvent && event.new is Topic) {
                        event.transformed = entity.copy(dateModified = LocalDateTime.now())
                    }
                }
            }
        })
    }
}

SQL looks like this:

CREATE TABLE `topic` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `date_created` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `date_modified` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `version` bigint(20) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

As you can see I do have an id column, yet when performing an INSERT via the DAO I get an unexpected SQLException. Logging shows an expected INSERT syntax:

insert into topic(date_created, date_modified, name, version) 
values ('2016-05-22 11:07:59.876', '2016-05-22 11:07:59.876', 'Love/Romance', 1);

The stacktrace shows the following:

Failed to execute TopicDao.insert in 1.921 ms (761521.415 ms)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
Reason: Column 'id' not found.. TXN: 3

    at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1076)
    at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2580)
    at com.github.andrewoma.kwery.core.Row.int(Row.kt:47)
    at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
    at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
    at com.github.andrewoma.kwery.mapper.Table$rowMapper$1$1.of(Table.kt:300)
    at com.quote.topic.topicTable.create(TopicDao.kt:25)

Any idea what I need to change configuration wise to prevent this?

Looks like there's an insidious bug where Kotlin is interpreting a field containing a String with a dollar sign ($) as an actual token for replacement in bind parameters:

Exception in thread "main" java.lang.IndexOutOfBoundsException: No group 5
    at java.util.regex.Matcher.start(Matcher.java:375)
    at java.util.regex.Matcher.appendReplacement(Matcher.java:880)
    at com.github.andrewoma.kwery.core.DefaultSession.bindParameters(DefaultSession.kt:219)

Filling a String with any text containing '$' and performing an insert/update should recreate the issue.

A quick study seems to indicate that we could fix this in MysqlDialect::bind().

When using a list parameter with any parameters following it, it gets translated into an incorrect parameter list that throws an exception under PostgreSQL. For example: session.update( "UPDATE sometable SET array_column = ARRAY [:arrayValues] WHERE id = :id", mapOf("arrayValues" to listOf(1), "id" to "2") ) This generates a length 9 parameter list. The first parameter will be "1", the second parameter will be "2", the 9th parameter will be null and thus trigger PostgreSQL to throw org.postgresql.util.PSQLException: No value specified for parameter 9. at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:257). (Also, why are parameter slots for collections rounded up to a power of 2?)

Hi Andrew,

Do you ever use pagination in your queries using offset, limit? I try to avoid large offsets but I continue to find examples where it's helpful. Currently I'm implementing it within a DAO with something like this:

val name = "findPaginated"
val sql = sql(name) {
    """
        SELECT
            $columns
        FROM
            ${table.name}
        LIMIT :offset, :limit
    """
}
return session.select(
        sql,
        mapOf("offset" to offset, "limit" to limit),
        options(name),
        table.rowMapper()
);

I'm wondering if this would be something worthy of adding to AbstractDao. Your thoughts?

I'm always getting this exception when passing IdStrategy.Generated to insert.

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Column "id" not found [42122-196]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
	at org.h2.message.DbException.get(DbException.java:179)
	at org.h2.message.DbException.get(DbException.java:155)
	at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3180)
	at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3246)
	at org.h2.jdbc.JdbcResultSet.getInt(JdbcResultSet.java:350)
	at com.github.andrewoma.kwery.core.Row.int(Row.kt:47)
	at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
	at com.github.andrewoma.kwery.mapper.intConverter$1.invoke(Converters.kt:80)
	at com.github.andrewoma.kwery.mapper.Table$rowMapper$1$1.of(Table.kt:206)
	at com.company.thing.ThingTable.create(EventServer.kt:28)
	at com.company.thing.ThingTable.create(EventServer.kt:23)
	at com.github.andrewoma.kwery.mapper.Table$rowMapper$1.invoke(Table.kt:204)
	at com.github.andrewoma.kwery.mapper.Table$rowMapper$1.invoke(Table.kt:39)
	at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1$1.invoke(AbstractDao.kt:289)
	at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1$1.invoke(AbstractDao.kt:32)
	at com.github.andrewoma.kwery.core.DefaultSession$insert$1.invoke(DefaultSession.kt:142)
	at com.github.andrewoma.kwery.core.DefaultSession$insert$1.invoke(DefaultSession.kt:46)
	at com.github.andrewoma.kwery.core.DefaultSession.withPreparedStatement(DefaultSession.kt:252)
	at com.github.andrewoma.kwery.core.DefaultSession.insert(DefaultSession.kt:135)
	at com.github.andrewoma.kwery.mapper.AbstractDao$insert$1.invoke(AbstractDao.kt:288)
	at com.github.andrewoma.kwery.mapper.AbstractDao.withTransaction(AbstractDao.kt:100)
	at com.github.andrewoma.kwery.mapper.AbstractDao.insert(AbstractDao.kt:277)
	at com.company.thing.main(Main.kt:48)

Process finished with exit code 1

package com.company.thing

import com.github.andrewoma.kwery.core.DefaultSession
import com.github.andrewoma.kwery.core.Session
import com.github.andrewoma.kwery.core.dialect.HsqlDialect
import com.github.andrewoma.kwery.mapper.AbstractDao
import com.github.andrewoma.kwery.mapper.IdStrategy
import com.github.andrewoma.kwery.mapper.Table
import com.github.andrewoma.kwery.mapper.Value
import java.sql.DriverManager

class Thing(
        var id: Int,
        var name: String
)

object ThingTable: Table<Thing, Int>("thing") {
    val Id   by col(Thing::id, id = true)
    val Name by col(Thing::name)

    override fun create(value: Value<Thing>): Thing {
        return Thing(value of Id, value of Name)
    }
    override fun idColumns(id: Int) = setOf(Id of id)
}

class ThingDao(session: Session): AbstractDao<Thing, Int>(session, ThingTable, Thing::id)

fun main(args: Array<String>) {

    val conn = DriverManager.getConnection("jdbc:h2:mem:test")
    val session = DefaultSession(conn, HsqlDialect())

    session.update("""
        create table thing (
            id   integer auto_increment not null primary key,
            name character varying(255) not null,
        )
    """)

    val dao = ThingDao(session)
    dao.insert(Thing(0, "foo"), IdStrategy.Generated)
}

Hi there,

I added some basic support for Joins.

I know that they can be added with the select() fun already, but for my own use cases I would prefer something slightly more elaborate. Have a look and tell me if you are interested in adding this kind of functionality to kwery. Otherwise I will probably start my own project consisting solely of the QueryBuilder.

Here is an example of the syntax:

query {
    select("select p.PERS_ID, p.PERS_NAME, pp.POSITION from PERSON p")

    joinGroup {
        innerJoin("PERS_POS") { on("$it.PERS_ID = p.PERS_ID") }
        leftJoin("LOCATION") { on("$it.LOC_ID = p.LOC_ID") } and { "$it.LOC_ID IS NULL" }
    }

}

Which yields the following sql (formatted):

select p.PERS_ID, p.PERS_NAME, pp.POSITION from PERSON p
 inner join PERS_POS pp on pp.PERS_ID = p.PERS_ID 
  left join LOCATION l on l.LOC_ID = p.LOC_ID and l.LOC_ID IS NULL

The Join automatically derives a handler for the joined table. For example if you join the table Actor, it will output join Actor a. This derived handler is then accessible in the on() and and() functions as it.

    @Test
    fun `test adding more columns after table instance construction`() {
        val usersTable = UsersTable()
        val dataColumnsBeforeModification = usersTable.dataColumns

        @Suppress("UNCHECKED_CAST")
        val emailColumn = Column(
                User::email, null,
                standardConverters[String::class.java] as Converter<String?>,
                "email", false, false, true, false
        )

        usersTable.addColumn(emailColumn)
        val dataColumnsAfterModification = usersTable.dataColumns

        assert(dataColumnsBeforeModification != dataColumnsAfterModification)
    }


    private data class User(val id: String, val name: String, val age: Int) {
        val email: String? = null
    }


    private class UsersTable : Table<User, UUID>("users") {

        val id by col(User::id, id = true)
        val userName by col(User::name)
        val age by col(User::age)

        override fun create(value: Value<User>): User {
            return User(value of id, value of userName, value of age)
        }

        override fun idColumns(id: UUID): Set<Pair<Column<User, *>, *>> {
            return setOf(this.id to id)
        }
    }

I noticed that I needed to call an Oracle Stored Procedure (would be nice to have a dialect for Oracle) and this framework had no support for doing it. I had to resort to obtaining the native connection and writing java style code to make it happen.

Would be really nice if you added support for CallableStatements so that they work roughly the same way as your select/update statements do. I also didn't see it on the roadmap either.

Hi you mentioned the kwery don't use Proxies or reflection ... but you use Proxies in transactional project. There isn't a better solution to implement this feature ?

Thanks.

I wanted to model simple domain with 1-N relationship. I have this query working in core:

select * from scores s join users u where s.created_at > '${date.toString("yyyy-MM-dd")}' order by s.score desc limit 20

But don't know how to model it with mapper. With Exposed it's as simple as this:

object Users : Table() {
    val id    = integer("id").primaryKey()
    val login = text("login")
}

object Scores : Table() {
    val score       = integer("score")
    val createdAt   = date("created_at")
    val userId      = integer("user_id") references Users.id
}

   override fun findAll(): List<Score> {
        return (Scores innerJoin Users).select { Scores.createdAt.greater(DateTime.now().minusDays(2)) }
                .orderBy(Scores.score, false)
                .limit(20)
                .map { r -> Score(r[Scores.score], r[Users.login]) }
}

I got something like this but, obviously, login is set to empty string:

class User(val id: Int, val login: String = "")

class PersistentScore(val id: Int, val user: User, score: Int) : Score(score, user.login)

object userConverter : SimpleConverter<User>(
        { row, c -> User(row.int(c)) },
        User::id
)

val tableConfig = TableConfiguration(
        defaults = standardDefaults + reifiedValue(User(0)),
        converters = standardConverters + reifiedConverter(userConverter)
)

object userTable: Table<User, Int>("users", tableConfig) {
    val Id          by col(User::id, id = true)
    val LoginVal    by col(User::login)

    override fun create(value: Value<User>): User =
        User(value of Id, value of LoginVal)

    override fun idColumns(id: Int) = setOf(Id of id)
}

object scoreTable : Table<PersistentScore, Int>("scores", tableConfig) {
    val Id          by col(PersistentScore::id, id = true)
    val ScoreVal    by col(PersistentScore::score, name = "score")
    val UserId      by col(PersistentScore::user)

    override fun create(value: Value<PersistentScore>): PersistentScore =
            PersistentScore(value of Id, value of UserId, value of ScoreVal)

    override fun idColumns(id: Int) = setOf(Id of id)
}
   fun findScores(): List<PersistentScore> {
        val name = "findScores"
        val sql = sql(name) { "select * from ${table.name} where created_at > :date order by score desc limit 10" }
        return session.select(sql, mapOf("date" to DateTime.now().minusDays(2).toString("yyyy-MM-dd")), options(name), table.rowMapper())
    }

Any help?

While Table instance is not initialized, columns are getting added. If any other thread sees a Table instance after that and try to initialize it, writes into columns made by other thread may not be visible.

Unfortunately, I don't know any easy solutions to address the issue.

Hi, Andrew.

Thank you for your awesome job!

I've been stuck a little with generated keys using core "insert" function with MySQL, because 'GENERATED_KEY' should be specified instead of key column name. I.e.

val (count, key) = session.insert("insert blablabla", mapOf("field" to "value" ...)) { it.long("GENERATED_KEY") }

Can you add a small remark about this into documentation? Thank you. Sorry for my English.

Are there any plans for a 0.18 release? There are a couple of bugfixes on master missing from version 0.17.. It would have been nice to see those fixes released soon 👍

Is there any possibility to use a colon ':' character in a query? It seems to be always used as a query parameter. For instance there is no way to use a string 'HH:mm' or PostgreSQL casting '3'::int.