seratch / kotliquery Goto Github PK

A handy Database access library in Kotlin

License: MIT License

Kotlin 99.90% Shell 0.10%

kotliquery's Issues

Thanks for this Library!

Hi,
I'm new to Kotlin world, and I just wanna say thanks for sharing this library publicly. I'm making an Android application that depends on lots of SQLite query. I figured I don't wanna having to always connect my Android device/emulator whenever writing new SQLite queries. And so for that, I separated my SQL queries into an independent Kotlin library so I can test the queries correctness without depending on Android. For that, I needed straight-forward SQL library for Kotlin that would just run raw query quickly. I have been searching for it, and most of what I found was more like ORM, until I found this library!

Thanks again for the work :)

Named parameters with array type

We are trying to do an insert to a table that contains a field of type text[] (postgres). We are doing something like this

sessionProvider.createSession().use { session ->
    session.run(queryOf(
            "INSERT INTO t(x, xs) VALUES(:x, :xs)",
            mapOf("x" to "hello", "xs" to listOf("1", "2"))
    ).asUpdate)
}

We are getting an error because of the type os xs.

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.Collections$SingletonList. Use setObject() with an explicit Types value to specify the type to use.

I don't see any example or documentation related to this. Is this supported?

Thanks!

extension functions

This is just raw idea, how about adding extension functions to existing JDBC interfaces so that users could reuse the library in existing JDBC frameworks such as spring-jdbc?

e.g.

fun ResultSet.row() = Row(this, ...)

Usage of colon in a comment tries to bind it as a variable

If I execute a query that has a comment that contains a colon, kotliquery attempts to use that colon as a bind parameter and we get the following error:

The column index is out of range: 1, number of columns: 0.
org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.
	at org.postgresql.jdbc.PgPreparedStatement.setNull(PgPreparedStatement.java:192)
	at kotliquery.Session.populateParams$lambda-1(Session.kt:291)
	at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:721)
	at kotliquery.Session.populateParams(Session.kt:91)
	at kotliquery.Session.createPreparedStatement(Session.kt:116)
	at kotliquery.Session.execute(Session.kt:204)
	at kotliquery.action.ExecuteQueryAction.runWithSession(ExecuteQueryAction.kt:9)
	at kotliquery.Session.run(Session.kt:231)

Reproduction:

val sql = """
-- Some dumb comment :shrug:
SELECT 1;
"""

val url = "jdbc:postgresql://$host:$port/$database"
val dbSession = sessionOf(url, username, password)

using(dbSession) { session ->
    session.run(queryOf(sql, emptyMap()).asExecute)
}

Executing the same code straight against Postgres does not fail, as the commented out code is properly ignored.

Kotliquery version: 1.6.1

asUpdateAndReturnGeneratedKey does not work with postgresql returning id

When we execute a postgresql insert statement that does returning id, we get the following exception:
A result was returned when none was expected

The below example will throw the exception: (Untested but created from other code that did)

 val insertQuery = "insert into test(name)  values (?) RETURNING id;"

using(kotliquery.sessionOf(HikariCP.dataSource())){session ->
                val query = queryOf(insertQuery,
                        "test name",
                ).asUpdateAndReturnGeneratedKey
                session.run(query)
        }

Stacktrace:

org.postgresql.util.PSQLException: A result was returned when none was expected.

at org.postgresql.jdbc.PgStatement.getNoResultUpdateCount(PgStatement.java:254)
at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:134)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at kotliquery.Session$updateAndReturnGeneratedKey$1.invoke(Session.kt:158)
at kotliquery.Session$updateAndReturnGeneratedKey$1.invoke(Session.kt:17)
at kotliquery.LoanPattern.using(LoanPattern.kt:11)
at kotliquery.PackageKt.using(package.kt:30)
at kotliquery.Session.updateAndReturnGeneratedKey(Session.kt:157)
at kotliquery.action.UpdateAndReturnGeneratedKeyQueryAction.runWithSession(UpdateAndReturnGeneratedKeyQueryAction.kt:9)
at kotliquery.Session.run(Session.kt:175)

Support batched queries

Hey. First off, been using kotliquery for almost a year, and it is working great for us. Like that it is just a relative small wrapper around jdbc, so thanks for your work.

We have seen the need for batched queries, specifically batched inserts. Which I cannot see kotliquery supporting at the moment. Would that be a feature you would be interested in adding?

We've implemented it ourselves, for our specific purpose, but I would be happy to contribute some code back.

If not, could you make private inline fun <reified T> PreparedStatement.setTypedParam(idx: Int, param: Parameter<T>) public? This is the only part of the kotliquery code we had to copy to make it work for us.

Cheers,
Alf

Possibility for empty list?

When I run a query .asList and map that to one of my domain classes of provided in your docs, I get a

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

Now I was wondering if it is possible to select an empty list? (since I can't expect it to have values all the time).

My current implementation:

// converts a Row to an Event object
	val toEvent: (Row) -> Event = { row ->
		Event(
				row.int("id"),
				row.string("title"),
				DateUtils.convertToFormattedString(
						row.sqlTimestamp("eventdate")),
				row.string("kind"),
				row.string("startingtime"),
				row.string("endtime"),
				row.string("description"),
				row.int("maxParticipants"),
				row.stringOrNull("address") ?: "New York",
				row.string("imageUrl")
		)
	}

        private fun getMultipleEvents(query: ListResultQueryAction<Event>): List<Event> {
		var returnedList: List<Event> = arrayOf<Event>().toList()
		using(sessionOf(super.datasource)) { session ->
			returnedList = session.run(query).orEmpty()
			session.close()
		}
		return returnedList
	}

        fun getMyEvents(apiKey: String): List<Event> {
		val query = "SELECT e.* "
					"FROM event_table e"+
					"JOIN registration_table r "+
					"  ON e.id = r.event_id "+
					"JOIN participant_table p "+
					"  ON p.id = r.participant_id "+
					"  AND p.uid = ? "+
					"ORDER BY eventdate DESC"
		
		return getMultipleEvents(queryOf(query, apiKey).map(toEvent).asList)
	}

NPE on method `Row.zonedDateTimeOrNull`

kotliquery/src/main/kotlin/kotliquery/Row.kt

Line 245 in d234101

    
           return nullable(ZonedDateTime.ofInstant(sqlTimestampOrNull(columnIndex)?.toInstant(), ZoneId.systemDefault()))

The first argument to ZonedDateTime.ofInstant mustn't be null:

    public static ZonedDateTime ofInstant(Instant var0, ZoneId var1) {
        Objects.requireNonNull(var0, "instant");

Add support for named parameters

It would be a whole lot more convenient to have named parameters in the queries instead of ?.

Especially this makes sense when one has to use the same param more than once in a query.

For instance, instead of

SELECT * FROM table t WHERE (? IS NULL OR t.a = ?) AND (? IS NULL OR t.b = ?)

would be nice to write

SELECT * FROM table t WHERE (:param1 IS NULL OR t.a = :param1) AND (:param2 IS NULL OR t.b = param2)

and to pass parameters in a map, e.g.

mapOf(
    "param1" to param1,
    "param2" to param2
)

How to get auto-generated ID following an Insert?

Using the example code as an example

val insertQuery: String = "insert into members (name,  created_at) values (?, ?)"

session.run(queryOf(insertQuery, "Alice", Date()).asUpdate) // returns effected row count
session.run(queryOf(insertQuery, "Bob", Date()).asUpdate)

How can we get hold of the statement that executed the query to be able to get the auto-generated ID? The asUpdate returns the effected row count, so in the example will always return 1.

Possible defect in initializing connection with same name in HikariCP.kt

In line 25 we check if a connection of a given name already exists. If it does, we check if it is already closed: if (existing != null && existing.isClosed)

If those conditions are satisfied, the connection is attempted to be closed on line 26: existing.close()

Seems to me line 25 should check if the connection is NOT closed.

Why is count function not running ?

What do I want to do ?

To count the rows returned by the query

What works ?

session.run(queryOf("select * from table-1").map { it.count() }.asSingle)

Expected

session.run(queryOf("select count(1) from table-1").map { it.int(0) }.asSingle)

Am I doing anything wrong ?

java.time.Instant stored without nanoseconds

In Session.kt in fun PreparedStatement.setParam(idx: Int, v: Any?) we have

is Instant -> this.setTimestamp(idx, Timestamp(Date.from(v).time))

In result Instant is converted to Date, and than to Timestamp

Instant and Timestamp support nanoseconds,
But Date does not. So value is truncated.
We can map value directly using Timestamp.from(Instant)

Another issue in same function

when (v) {
  ...
  is java.util.Date -> this.setTimestamp(idx, Timestamp(v.time))
  is java.sql.Timestamp -> this.setTimestamp(idx, v)
  ...

Timestamp in sub class of Date. So Timestamp is not executed. And we loose nanos here as well

P.S. Thanks for great lib!

Condiser using stringOrNull instead of "!!"

Hi @seratch,
I think in reality we have more non-null data than nullable data, it is handier and more readable if default function return non-null type:

fun string() : String { ... }
fun stringOrNull: String? { ... }

kotlin.collections also use this pattern such as single and singleOrNull.

I would like to submit a PR if you agree on this.

Support for MySQL IN-Statement

Explaination

In MySQL the IN operator allows you to determine if a value matches any value in a list of values.
Unfortunately there is currently no support vor passing a list as a query parameter

This works

fun testSelect(): List<String> {
    val sql = """
        SELECT column_1, column_2 FROM TEST_TABLE WHERE column_1 IN ('a', 'b')
    """.trimIndent()

    val query = queryOf(sql).map { row -> row.string("column_2") }.asList

    return scanappDb.useConnection { dbSession ->
        dbSession.run(query)
    }
}

This doesn't work

fun testSelect2(): List<String> {
    val sql = """
        SELECT column_1, column_2 FROM TEST_TABLE WHERE column_1 IN :values
    """.trimIndent()

    val query = queryOf(sql, mapOf("values" to arrayOf("a", "b")))
        .map { row -> row.string("column_2") }.asList

    return scanappDb.useConnection { dbSession ->
        dbSession.run(query)
    }
}

I tried:

Passing a list or array
Using Brackets "(" around :values
None of the combinations work

Where it works

For example room has an implementation for this:
https://stackoverflow.com/questions/48406228/room-select-query-with-in-condition

Final words

It would be really nice if you could look into this issue :)

Suggestion: Replace use of joda-time with plain java time handling?

It would be nice if this library could come without external dependencies, or at least as close as possible.

I believe that the use of Joda-time here could be replaced with plain Java time handling (using the Date and Time API introduced in Java 8).

This is not an issue, just q question

fun getMemberById(id: String): Member? {
val aMember = using(sessionOf(dataSource)) { session ->
val query = queryOf("select * from t_member where id = ?", id).map {toMember}.asSingle
val maybeMember = query.runWithSession(session)
}
return aMember???
}

The question is , how can I return a member?

Regex for named parameters matches double colons

Hi,

the current regex for named parameters matches double colons, that are used e.g., in PostgreSQL to cast variables.

For example, the following query (used to get the list of partitions in PostgreSQL v.10) does not work:

SELECT c.oid::pg_catalog.regclass, pg_catalog.pg_get_expr(c.relpartbound, c.oid)
FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i
WHERE c.oid=i.inhrelid AND i.inhparent = 
(
	SELECT c.oid
	FROM pg_catalog.pg_class c
	     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
	WHERE c.relname OPERATOR(pg_catalog.~) '^(sometable)$'
	  AND n.nspname OPERATOR(pg_catalog.~) '^(someschema)$'
)
ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;

The regex matches the two colons after c.oid, and tries to find a named parameter, which is not there. At this point, the query is transformed and fails when sent to the driver.

Own parsing of the batch insert/update result set

Another thought, the generated keys doesn't really have to be a long. They could be any type. I made it Long to be consistent with updateAndReturnGeneratedKey, but it would perhaps be more useful if it took an Row -> T as a param for mapping the generated keys from the resultset.

Originally posted by @magott in #52 (comment)

This (i.e - adding Row -> T) is actually very useful, instead of just receiving the ids
As a matter of fact, it can save an additional roundtrip to the db, just to get the extra columns from that table that we batch-inserted values to.

If this is something that can be done, I'd be happy to join as a reviewer.

Cannot set parameter in INTERVAL statement (PostgreSQL)

It seems it's not possible to set a parameter in a INTERVAL '? SECONDS' statement, using postgres:

This minimal test case:

    @Test
    fun `should be able to replace parameters in INTERVAL statements`() {
        using(sessionOf(HikariCP.dataSource())) { session ->
            val query = queryOf(
                    """SELECT INTERVAL '? SECONDS' """.trimMargin(),
                    300)
                    .map { row -> row.string(1) }
                    .asSingle
            val interval: String? = session.run(query)
            assertEquals("00:05:00", interval)
        }
    }

Produces this stack trace:

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

	at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:65)
	at org.postgresql.core.v3.SimpleParameterList.setBinaryParameter(SimpleParameterList.java:132)
	at org.postgresql.jdbc.PgPreparedStatement.bindBytes(PgPreparedStatement.java:1007)
	at org.postgresql.jdbc.PgPreparedStatement.setInt(PgPreparedStatement.java:283)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setInt(HikariProxyPreparedStatement.java)
	at kotliquery.Session.setParam(Session.kt:46)
	at kotliquery.Session.access$setParam(Session.kt:17)
	at kotliquery.Session.populateParams(Session.kt:213)
	at kotliquery.Session.createPreparedStatement(Session.kt:103)
	at kotliquery.Session.rows(Session.kt:107)
	at kotliquery.Session.single(Session.kt:123)
	at kotliquery.action.NullableResultQueryAction.runWithSession(NullableResultQueryAction.kt:12)
	at kotliquery.Session.run(Session.kt:183)

How to pass an indefinite amount of parameters to an sql query?

Hi there,
Is there a right way to pass a list of parameters to an sql query?
select * from table where id in ( 'a', 'list', 'of', 'ids' )

Now I prepare a variable as String, but it looks unsecure.

val query = """ select * from table where id in ( ${idList} ) """.trimMargin()
l = kotliquery.queryOf(sqlMI).map(toMyObject).asList
session.run(l)

jsonb_insert function bug - sets the column as null

I'm trying to run a query that inserts an jsonb element to jsonb at specific index, like this :

UPDATE boards

SET    order_json = jsonb_insert(order_json '{1}', '{"id": 123}'::JSONB)
       
WHERE  id = 1
RETURNING id, modified_at

When I run this query in a postgres client manually - it works perfectly.
When I run this query in KotliQuery - the 'order_json' column turns "NULL"

My dependencies :
implementation("com.zaxxer:HikariCP:5.0.1")
implementation("org.postgresql:postgresql:42.3.3")
implementation("com.github.seratch:kotliquery:1.7.0")

Insertion of timestamps triggers namedParameter feature

When performing a simple insert like this one:

insert into log (action, timestamp) values ('user logged in', '2019-01-01 00:00:00')

A jdbc error is triggered because the replacementMap in the Query object is initialized with the :00 params as if they were really params.

The following error insues:

java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1402)
	at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1415)
	at com.mysql.cj.jdbc.ClientPreparedStatement.setNull(ClientPreparedStatement.java:1680)
	at kotliquery.Session$populateParams$1.accept(Session.kt:251)
	at kotliquery.Session$populateParams$1.accept(Session.kt:18)
	at java.base/java.util.LinkedHashMap.forEach(LinkedHashMap.java:684)
	at kotliquery.Session.populateParams(Session.kt:81)
	at kotliquery.Session.createPreparedStatement(Session.kt:106)
	at kotliquery.Session.execute(Session.kt:181)
	at kotliquery.action.ExecuteQueryAction.runWithSession(ExecuteQueryAction.kt:9)
	at kotliquery.Session.run(Session.kt:208)

A simple fix for this would be to disallow digits as the first character of the named parameter and adjust the regex that finds them in the query statements.

[feature] get final SQL before execute

Hi,

Is there anyway to get the final SQL (after parameter inserted) before execution? It's a good way to check if parameters are properly inserted.

For example

val id = "123"
val query = queryOf("SELECT * FROM T WHERE id=?", id)

query.getRawSQL() // SELECT * FROM T WHERE id='123'

session 'run' and 'execute' always returns false for 'create table' request

You can check it for example here:

kotliquery/sample/src/main/kotlin/Example.kt

Lines 9 to 15 in 252016a

    
                   session.run(queryOf(""" 
        
             create table members ( 
        
               id serial not null primary key, 
        
               name varchar(64), 
        
               created_at timestamp not null 
        
             ) 
        
           """).asExecute)

List<T> as value for parameter in IN clause

Hello,
i'm trying to express a query of type "SELECT * from X where X.id IN(?)" as, for example, in Hibernate and i cant make it work.
My hope is that a simple solution can be implemented, but i dont figure how.
For completion, here is my code:

override fun get(woeidList: List<Int>): List<WeatherInfo> {
        return using(sessionOf(datasource)) { session ->
            session.run(queryOf(GET_BY_LIST, woeidList).map(toWeatherInfo).asList)
        }
    }

and the query is:

private val GET_BY_LIST = """SELECT WOEID, WIND_CHILL, WIND_DIRECTION,
                              WIND_SPEED, ATM_HUMIDITY, ATM_PRESSURE,
                              ATM_RISING, ATM_VISIBILITY, CON_CODE,
                              CON_DATE, CON_TEMP, CON_TEXT
                              FROM MONITORS_HISTORY
                              WHERE WOEID IN(?) """

Any advise?

Kotlinquery is not available for Kotlin Native as it forces use of JDK8

by using:

map.forEach { k, v ->

implemented with jdk8 instead of kotlin's native

map.forEach { entry ->

there is no possibility tu use kotliquery with java7

Update query with RETURNING not invoking `extractor.invoke(row)`

I have a simple query :

UPDATE  boards
SET     version = version + 1
WHERE   id = :boardId
AND     version = :boardVersion
RETURNING id, version, modified_at

while debugging I can see it's just skipping extractor.invoke(row) in this function :

 private fun <A> rows(query: Query, extractor: (Row) -> A?): List<A> {
        return using(createPreparedStatement(query)) { stmt ->
            using(stmt.executeQuery()) { rs ->
                val rows = Row(rs).map { row -> extractor.invoke(row) }  <-------- skipping this invoke
                rows.filter { r -> r != null }.map { r -> r!! }.toList()
            }
        }
    }

This results in null response of course.

my code is as follows :

 return tx.run(queryOf(stmt, params)
                .map( { row -> .......... } )
                .asSingle)

I also tried using .asList , but it still skips the extractor.invoke

Not closing postgresql connections

Hi there, first of all awesome framework.

Just ran into a problem. I'm using a remote PostgreSQL database. After a bit of calling the database I get the following error Failure: too many clients already. Which is caused by 100 connections being idle.

I'm trying to create 1 point where I have to do the config. This is what I call my BaseDAO. The relevant code for that class looks like this:

import com.zaxxer.hikari.HikariConfig
import com.zaxxer.hikari.HikariDataSource
import kotliquery.Session
import kotliquery.sessionOf
import javax.sql.DataSource

class BaseDAO {
    companion object {
        var url: String = "jdbc:postgresql://server.local:5432/myDatabase"
        var user: String = "postgres"
        var pass: String = "postgres"

        val config: HikariConfig = HikariConfig()

        private fun dataSource(): DataSource
        {
            var hikariConfig: HikariConfig =  HikariConfig();
            hikariConfig.setDriverClassName("org.postgresql.Driver");
            hikariConfig.setJdbcUrl(url);
            hikariConfig.setUsername(user);
            hikariConfig.setPassword(pass);

            hikariConfig.setMaximumPoolSize(5);
            hikariConfig.setConnectionTestQuery("SELECT 1");
            hikariConfig.setPoolName("springHikariCP");

            hikariConfig.addDataSourceProperty("dataSource.cachePrepStmts", "true");
            hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSize", "250");
            hikariConfig.addDataSourceProperty("dataSource.prepStmtCacheSqlLimit", "2048");
            hikariConfig.addDataSourceProperty("dataSource.useServerPrepStmts", "true");

            var dataSource: HikariDataSource  = HikariDataSource(hikariConfig);

            return dataSource;
        }

        @JvmStatic fun getSession(): Session {
            return sessionOf(dataSource())
        }
    }

}

And one of my DAO's:

class UserDAO {

    val toUser: (Row) -> User = { row ->
        User(
                row.int("id"),
                row.string("username"),
                row.string("usertype")
        )
    }

    fun getAllUsers(): List<User> {
        var returnedList: List<User> = arrayOf<User>().toList()
        using(BaseDAO.getSession()) { session ->

            val allUsersQuery = queryOf("select * from quintor_user").map(toUser).asList
            returnedList = session.run(allUsersQuery)
            session.connection.close() 
            session.close()
        }

        return returnedList
    }
}

After looking into Kotliquery's source code I realized the session.connection.close() and session.close wouldn't even be neccessary when using using but without them I got the same error. (had to restart postgresql database -- 100 idle connections).

I was wondering if there is an error in my code or if this is an error in Kotliquery?

Kind regards,
Ivaro

asExecute return value does not work as expected

In README, there is an example showing how to run a DDL query. I would expect that the boolean value returned in the example to represent a success/failure of the DDL query execution.

But the boolean value returned from ExecuteQueryAction is in fact the return value of execute method from PreparedStatemement. The documentation for this method says:

Returns:
true if the first result is a ResultSet object; false if the first result is an update count or there is no result

This means that the return value of running an ExecuteQueryAction is useless as neither the update count or ResultSet can be accessed.

Support NULL values in query params

Nullable columns in the DB currently can't be optionally filled during insert into the table.

For instance, let's assume a table:

CREATE TABLE test (
  id      VARCHAR(10),
  name    VARCHAR(20),
  PRIMARY KEY(id)
)

Somewhere in the code later, I'll have a function to insert rows:

fun createTestRow(id: String, name: String?) {
  using(sessionOf(HikariCP.dataSource())) { session ->
    session.run(queryOf("INSERT INTO test (id, name) VALUES (?, ?)", id, name).asUpdate)
  }
}

Obviously, name cannot be used the way I've put it here, as queryOf expects params of type Any.

The logic that I'm trying to accommodate includes an empty name though, so it'd be nice to pass null references.

val users = listOf(
    listOf("Bill", 20),
    listOf("Andy", 18)
)
queryOf("INSERT INTO User SET (name, age) VALUES ?", users)

// becomes:
INSERT INTO User SET (name, age) VALUES ("Bill", 20), ("Andy", 18);

	session.run(queryOf("""
	create table members (
	id serial not null primary key,
	name varchar(64),
	created_at timestamp not null
	)
	""").asExecute)