seratch / kotliquery Goto Github PK
View Code? Open in Web Editor NEWA handy Database access library in Kotlin
License: MIT License
A handy Database access library in Kotlin
License: MIT License
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 :)
Hi
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!
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, ...)
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
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)
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
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)
}
kotliquery/src/main/kotlin/kotliquery/Row.kt
Line 245 in d234101
The first argument to ZonedDateTime.ofInstant
mustn't be null:
public static ZonedDateTime ofInstant(Instant var0, ZoneId var1) {
Objects.requireNonNull(var0, "instant");
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
)
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.
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.
To count the rows returned by the query
session.run(queryOf("select * from table-1").map { it.count() }.asSingle)
session.run(queryOf("select count(1) from table-1").map { it.int(0) }.asSingle)
Am I doing anything wrong ?
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!
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.
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
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)
}
}
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:
For example room has an implementation for this:
https://stackoverflow.com/questions/48406228/room-select-query-with-in-condition
It would be really nice if you could look into this issue :)
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).
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?
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.
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.
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)
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)
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")
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.
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'
You can check it for example here:
kotliquery/sample/src/main/kotlin/Example.kt
Lines 9 to 15 in 252016a
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?
TY
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
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
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
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.
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.
How can I create a Read-Only transaction?
The issue has been described in 2011 here and I've just stumbled upon it in my project using kotliquery.
There's a suggested way around it, using PreparedStatement.setObject(id, null, type)
instead of PreparedStatement.setObject(id, null)
.
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);
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.