kotlin-orm / ktorm Goto Github PK

View Code? Open in Web Editor NEW

2.0K 36.0 144.0 98.21 MB

A lightweight ORM framework for Kotlin with strong-typed SQL DSL and sequence APIs.

Home Page: https://www.ktorm.org

License: Apache License 2.0

Kotlin 99.85% Java 0.15%

kotlin orm sql ktorm

ktorm's People

Contributors

Stargazers

Watchers

Forkers

zhuhengli hslooooooool yinhe billowliu2 godpan joshuayan waluo gckjdev cybernetics wdoeland disneyliao theoxao ofekagmon y1024 hc747 noahlann cjdxhjj croceit xialinbing youyeyuki clydebarrow kharchenkovitaliypt liphis jensenqi kefzce weijunfeng pninoh abvadabra lumierex dut3062796s darkredz merisen hazer easykotlin shiveenp arustleund utamori antonydenyer woxiehaode hypertrophy scorsi kangzq0530 thesky0108 amrjlg zhuomingliang lyndsysimon dmitchell peymang tapiohuang gsy13213009 hoangpq dean-chung fortescarlet abhay379201 mik629 misuda12 swai-collins yancyyu jackfung1 xujif zhangzhaoliuqun saeedfielmann sinzed pedrod uasouz evwang doomsdayrs sigmanil efenderbosch afezeria nlochschmidt asad-awadia jerryuzm crazymoonkin paskaa wisdomqq llaiajiale ecopoesis battlecupcake lvfengxian2021 tursom servicefoundation seeday slavonnet lightningkite droidchief nieole testfailed liduanwei bogao70 kocproz ahqht lookup-cat liukeshao xiefangxiang manucher96 kirk88 luobeimu zuisong hhhxxjj

ktorm's Issues

mariadb timestamp issue

When trying to update a timestamp column using an entity with type Instant, the timestamp value looks something like: "2019-09-05T00:00:56.287169Z".
Looking at the logged SQL, everything looks fine (no errors), but the db is not updated.

Manually running the sql shows an error like:
"Incorrect datetime value: '2019-09-05T00:00:56.287169Z' for column..." (but the column has type "timestamp")

Shouldn't ktorm report an error when the query fails? I found that if I remove the 'Z' from the end of the value, the query works. What is the recommended way to correctly format a timestamp value from an Instant?

UPDATE: I just tried using a datetime/LocalDateTime in ktorm (even though the db column is still a timestamp) and the value doesn't have the Z at the end, so it works when I run it manually. But for some reason, the flushChanges() is not working even though I am getting the entity from the findOne() method.

Quering/Updating/Inserting extra columns despite of field mappings

If I have a table like following:

object Departments : Table<Nothing>("t_department") {
    val id by int("id").primaryKey()
    val name by varchar("name"))
    val location by varchar("location")
}

But let's say I've an extra column in table t_department called created_at (doesn't matter the sql type), is it possible to load all the columns while querying/inserting/updating a record in the table?

This is useful to me, because as from a framework author, I could provide a base table objects (like Departments) in the above example, and based on whether a user has added extra columns or not in the table, (like created_at), I could set the value automatically. Something along the lines of:

Departments.insertAndGenerateKey {
            it.name to "IT"
           it.location to "New York"
           it.get("created_at")?.to(LocalDateTime.now())
        }

I understand that it kind of breaks the static type advantage that Ktorm provides but this is so much useful both for a framework author and the users. I've plenty of other examples where this would create it even more delightful experience for the users.

关于SpringBoot使用的问题

可以添加使用SpringBoot进行开发的案例Demo么？感谢。

QueryRowSet 的 hasColumn 存在问题

hasColumn会忽略table alias，导致多个表有同名字段时会返回错误

这会导致使用sql dsl时无法写出通用(可以同时处理是否联表的情况)的map:QueryRowSet -> Model

可以在JoinTest中用以下代码复现问题

    data class Names(val name: String, val managerName: String?, val departmentName: String)

    val emp = Employees.aliased("emp")
    val mgr = Employees.aliased("mgr")
    val dept = Departments.aliased("dept")

    val results = emp
        .select(emp.name)
        .map {
            Names(
                name = it[emp.name]!!,
                managerName = if(it.hasColumn(mgr.name)) it[mgr.name]!! else "",
                departmentName = if(it.hasColumn(dept.name)) it[dept.name]!! else ""
            )
        }

    assert(results.all { it.managerName == "" })
    assert(results.all { it.departmentName == "" })

Support kotlinx.serialization

Consider add kotlinx.serialization support for entity serialization.

database threadload解耦

Employees.findOne 最终会变成一个Query，Query使用DataBase.global ，以及threadLocal来切换。多数据源的场景下，需要在一个context下执行，比如示例中的mysql {Employees.findOne}.
建议在Table定义的时候指定DataBase。另外，在一些协程场景下，比如ktor框架内，用ThreadLocal，可能会产生一些隐含的bug。

Eager and Lazy Loading

Unless I'm wrong, I was pleasantly surprised to see that queries are eager loaded and thus avoids N+1 query problem. I didn't find this being documented. Is this correct or I'm just stupid?

If the queries are in fact eagerly loaded, is there a way to make them lazily loaded?

实现jpa的@CreatedDate、@CreatedBy、@LastModifiedDate、@LastModifiedBy

怎样实现类似jpa的@CreatedDate、@createdby、@LastModifiedDate、@LastModifiedBy功能

Add something to easily handle the mapping of Enum in database

Hello.

I wonder if it would be possible to add something to easily handle mapping of Enum type in database ? A mapper which map an Enum Kotlin field into a VARCHAR database field.

Maybe adding a specific SqlType for the enums ?

What do you think about that ?

Add a new SQL type to handle UUID type in database

Hello.

First of all, thank you for this awesome Kotlin ORM framework !

I just wonder if it would be possible to add a new common SQL type for UUID type. They are common used in SQL databases like PostgreSQL and it would be a simple addition in the code base.

Maybe we could do something like that :

fun <E : Entity<E>> BaseTable<E>.uuid(name: String): BaseTable<E>.ColumnRegistration<UUID> {
    return registerColumn(name, UuidSqlType)
}

object UuidSqlType : SqlType<UUID>(Types.OTHER, "uuid") {

    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: UUID) {
        ps.setObject(index, parameter)
    }

    override fun doGetResult(rs: ResultSet, index: Int): UUID {
        return rs.getObject(index) as UUID
    }
}

Thanks in advance for your response.

写sql 想要map成一个自定义的Entity<？>的class 是可以支持的吗

我自己写sql执行有很方面的方式map成一个自定义的Entity<？>的class吗

 var testResult = db.useConnection { conn ->
            var sql ="select `Tid`,`Key`,`Value` from config where tid = ? limit 1" 
            conn.prepareStatement(sql).use { statement->
                statement.setLong(1,2)
                statement.executeQuery().iterable().map { it.getString(1) }
            }
        }
上面的代码 有没有办法很方便的能后让结果集 map成我下面的class？？

interface Test : Entity<Test> {
    val Tid: Long
    var Value:String
    var Key:String
}

多数据源切换的一个建议.

ktorm是支持多数据源的,但是感觉用起来有点不太方便.
比如我一直用的是单一数据源(primary)
但有一天我需要加一个数据源,访问一个配置数据库(second).
结果是我原来的所有primary的dao方法都受到影响了,
现在只要访问一次second,我得将primary invoke一次,不然primary对应的方法就会使second数据源.

有两个想法:
1.可以给Database设置生效的aspect,Database只对这个aspect下面的table objects生效
2.可以为table objects设置Database,优先使用自己的db,而不是全部使用Database.global

JsonMappingException when return response;

I modifier a new project from ktorm-example-spring-boot ,it works well.

but then I try to integration ktorm to my old project.
just simple http api like this:

the list can query success from db,but throw ex after return

This is my maven config(other libs is omit ):
kt version is 1.3.41

  <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-stdlib-jdk8</artifactId>
            <version>${kotlin.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.kotlin</groupId>
            <artifactId>kotlin-reflect</artifactId>
            <version>${kotlin.version}</version>
        </dependency>
  <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.module</groupId>
            <artifactId>jackson-module-kotlin</artifactId>
            <version>2.9.9</version>
        </dependency>
        <dependency>
            <groupId>me.liuwj.ktorm</groupId>
            <artifactId>ktorm-core</artifactId>
            <version>2.4</version>
        </dependency>
        <dependency>
            <groupId>me.liuwj.ktorm</groupId>
            <artifactId>ktorm-jackson</artifactId>
            <version>2.4</version>
        </dependency>
        <dependency>
            <groupId>me.liuwj.ktorm</groupId>
            <artifactId>ktorm-support-mysql</artifactId>
            <version>2.4</version>
        </dependency>

the error log

17:06:55.071 default [http-nio-8090-exec-3] ERROR c.z.p.d.c.ExceptionHandlerAdvice - 错误发生在:/txTest/test3
17:06:55.072 default [http-nio-8090-exec-3] ERROR c.z.p.d.c.ExceptionHandlerAdvice - handleBadRequest StackTrace error 
org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: org.jetbrains.kotlin.name.ClassId; nested exception is com.fasterxml.jackson.databind.JsonMappingException: org.jetbrains.kotlin.name.ClassId (through reference chain: java.util.ArrayList[0]->com.sun.proxy.$Proxy191["entityClass"])
	at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal(AbstractJackson2HttpMessageConverter.java:296)
	at org.springframework.http.converter.AbstractGenericHttpMessageConverter.write(AbstractGenericHttpMessageConverter.java:103)
	at org.springframework.web.servlet.mvc.method.annotation.AbstractMessageConverterMethodProcessor.writeWithMessageConverters(AbstractMessageConverterMethodProcessor.java:290)
	at org.springframework.web.servlet.mvc.method.annotation.RequestResponseBodyMethodProcessor.handleReturnValue(RequestResponseBodyMethodProcessor.java:180)
	at org.springframework.web.method.support.HandlerMethodReturnValueHandlerComposite.handleReturnValue(HandlerMethodReturnValueHandlerComposite.java:82)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:122)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:118)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
	at org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:96)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:41002)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:853)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1587)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)
Caused by: com.fasterxml.jackson.databind.JsonMappingException: org.jetbrains.kotlin.name.ClassId (through reference chain: java.util.ArrayList[0]->com.sun.proxy.$Proxy191["entityClass"])
	at com.fasterxml.jackson.databind.JsonMappingException.wrapWithPath(JsonMappingException.java:394)
	at com.fasterxml.jackson.databind.JsonMappingException.wrapWithPath(JsonMappingException.java:353)
	at com.fasterxml.jackson.databind.ser.std.StdSerializer.wrapAndThrow(StdSerializer.java:316)
	at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:727)
	at com.fasterxml.jackson.databind.ser.BeanSerializer.serialize(BeanSerializer.java:155)
	at com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serializeContents(CollectionSerializer.java:145)
	at com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serialize(CollectionSerializer.java:107)
	at com.fasterxml.jackson.databind.ser.std.CollectionSerializer.serialize(CollectionSerializer.java:25)
	at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider._serialize(DefaultSerializerProvider.java:480)
	at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:400)
	at com.fasterxml.jackson.databind.ObjectWriter$Prefetch.serialize(ObjectWriter.java:1392)
	at com.fasterxml.jackson.databind.ObjectWriter.writeValue(ObjectWriter.java:913)
	at org.springframework.http.converter.json.AbstractJackson2HttpMessageConverter.writeInternal(AbstractJackson2HttpMessageConverter.java:287)
	... 41 common frames omitted
Caused by: java.lang.ClassNotFoundException: org.jetbrains.kotlin.name.ClassId
	at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
	at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
	at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
	at kotlin.reflect.jvm.internal.KDeclarationContainerImpl.parseType(KDeclarationContainerImpl.kt:277)
	at kotlin.reflect.jvm.internal.KDeclarationContainerImpl.loadReturnType(KDeclarationContainerImpl.kt:292)
	at kotlin.reflect.jvm.internal.KDeclarationContainerImpl.findMethodBySignature(KDeclarationContainerImpl.kt:223)
	at kotlin.reflect.jvm.internal.KPropertyImplKt.computeCallerForAccessor(KPropertyImpl.kt:223)
	at kotlin.reflect.jvm.internal.KPropertyImplKt.access$computeCallerForAccessor(KPropertyImpl.kt:1)
	at kotlin.reflect.jvm.internal.KPropertyImpl$Getter$caller$2.invoke(KPropertyImpl.kt:156)
	at kotlin.reflect.jvm.internal.KPropertyImpl$Getter$caller$2.invoke(KPropertyImpl.kt:147)
	at kotlin.reflect.jvm.internal.ReflectProperties$LazyVal.invoke(ReflectProperties.java:62)
	at kotlin.reflect.jvm.internal.ReflectProperties$Val.getValue(ReflectProperties.java:31)
	at kotlin.reflect.jvm.internal.KPropertyImpl$Getter.getCaller(KPropertyImpl.kt)
	at kotlin.reflect.jvm.ReflectJvmMapping.getJavaMethod(ReflectJvmMapping.kt:62)
	at com.fasterxml.jackson.module.kotlin.KotlinAnnotationIntrospector.getCorrespondingGetter(KotlinAnnotationIntrospector.kt:101)
	at com.fasterxml.jackson.module.kotlin.KotlinAnnotationIntrospector.hasRequiredMarker(KotlinAnnotationIntrospector.kt:66)
	at com.fasterxml.jackson.module.kotlin.KotlinAnnotationIntrospector.access$hasRequiredMarker(KotlinAnnotationIntrospector.kt:23)
	at com.fasterxml.jackson.module.kotlin.KotlinAnnotationIntrospector$hasRequiredMarker$1.invoke(KotlinAnnotationIntrospector.kt:33)
	at com.fasterxml.jackson.module.kotlin.KotlinAnnotationIntrospector$hasRequiredMarker$1.invoke(KotlinAnnotationIntrospector.kt:23)
	at com.fasterxml.jackson.module.kotlin.ReflectionCache.javaMemberIsRequired(KotlinModule.kt:92)
	at com.fasterxml.jackson.module.kotlin.KotlinAnnotationIntrospector.hasRequiredMarker(KotlinAnnotationIntrospector.kt:26)
	at com.fasterxml.jackson.databind.introspect.AnnotationIntrospectorPair.hasRequiredMarker(AnnotationIntrospectorPair.java:307)
	at com.fasterxml.jackson.databind.introspect.AnnotationIntrospectorPair.hasRequiredMarker(AnnotationIntrospectorPair.java:307)
	at com.fasterxml.jackson.databind.introspect.POJOPropertyBuilder$4.withMember(POJOPropertyBuilder.java:655)
	at com.fasterxml.jackson.databind.introspect.POJOPropertyBuilder$4.withMember(POJOPropertyBuilder.java:652)
	at com.fasterxml.jackson.databind.introspect.POJOPropertyBuilder.fromMemberAnnotations(POJOPropertyBuilder.java:1143)
	at com.fasterxml.jackson.databind.introspect.POJOPropertyBuilder._findRequired(POJOPropertyBuilder.java:652)
	at com.fasterxml.jackson.databind.introspect.POJOPropertyBuilder.getMetadata(POJOPropertyBuilder.java:220)
	at com.fasterxml.jackson.databind.ser.BeanSerializerFactory._constructWriter(BeanSerializerFactory.java:771)
	at com.fasterxml.jackson.databind.ser.BeanSerializerFactory.findBeanProperties(BeanSerializerFactory.java:583)
	at com.fasterxml.jackson.databind.ser.BeanSerializerFactory.constructBeanSerializer(BeanSerializerFactory.java:368)
	at com.fasterxml.jackson.databind.ser.BeanSerializerFactory.findBeanSerializer(BeanSerializerFactory.java:279)
	at com.fasterxml.jackson.databind.ser.BeanSerializerFactory._createSerializer2(BeanSerializerFactory.java:231)
	at com.fasterxml.jackson.databind.ser.BeanSerializerFactory.createSerializer(BeanSerializerFactory.java:165)
	at com.fasterxml.jackson.databind.SerializerProvider._createUntypedSerializer(SerializerProvider.java:1388)
	at com.fasterxml.jackson.databind.SerializerProvider._createAndCacheUntypedSerializer(SerializerProvider.java:1336)
	at com.fasterxml.jackson.databind.SerializerProvider.findPrimaryPropertySerializer(SerializerProvider.java:668)
	at com.fasterxml.jackson.databind.ser.impl.PropertySerializerMap.findAndAddPrimarySerializer(PropertySerializerMap.java:64)
	at com.fasterxml.jackson.databind.ser.BeanPropertyWriter._findAndAddDynamic(BeanPropertyWriter.java:897)
	at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:705)
	at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:719)
	... 50 common frames omitted

Auto detect a logger implementation

Ktorm: issues & suggestions

i use postgresql dialect (not mysql) but when invoke method insertorupadate i see this exception:

Unsupported expression type: class me.liuwj.ktorm.support.mysql.InsertOrUpdateExpression

how to release (disconnect) from database when finish (i use Database.connect { ..... }) i not found any method in Database class to disconnect from db
add interceptor when database disconnect
add ktorm support for : sqlite, firebird, mssql server, mongo

Recommend a common pagination tool

Use sqlhelper, it is unnecessary to build a module for each database

Few questions

I've couple of questions regarding the usage of Ktorm:

Is SQLite supported? (Both file and or memory based)
How does one create the tables (equivalent of Exposed's SchemaUtils.createMissingTablesAndColumns or are the tables automagically created on the first invoke?
Any plan to support entity relationships?

Sorry to add multiple questions in one issue (if you want, I can close this and create separate issues).

Column sizes for supporting migrations

I'm writing some migration scripts where I plan to migrate a Ktorm table by doing something like this:
migrator.migrate(Departments)

I was thinking of looping through the columns of the table and then building a CREATE query and then executing it. But, alas, I ran into a big blocker: when declaring fields for a table there is no way to set the size of a field such as for varchar. You cannot set other metadata as well such as a default value.

Is there anyway I can attach metadata to a field. If not, are you willing to modify the declaring functions to accept a metadata map?

Does it work with SQLDroid drivers?

SQLite databases are often used in Android apps. Unfortunately the Xerial drivers are bugged for Android and the only viable JDBC driver are the SQLDroid ones. Since the JDBC URL is different from the SQLite one, would it work?

Generic caching interface

It would be nice if Ktorm offered a generic caching interface, so users could implement their own caching systems for db queries using eg. Redis or Ehcache.

The interface could look something like this:

interface KtormCache {
  fun getFromCache(generatedSql: String): QueryRowSet?
}

And a sample implementation:

class KtormCacheRedisImpl : KtormCache {
  override fun getFromCache(generatedSql: String): QueryRowSet? {
    // Return query results from redis by the generated sql, or null if not found
  }
}

Thanks.

MySQL throws `Duplicate column name campaign_id` when using totalRecords

Query:

Promotions
    .leftJoin(Campaigns, on = Promotions.campaignId eq Campaigns.campaignId)
    .select()
    .whereWithConditions { ... }
    .limit(req.page.offset, req.page.limit)

Generated SQL:

select count(*) from (
    select * 
    from p_promotion 
    left join p_campaign on p_promotion.campaign_id = p_campaign.campaign_id 
    where ...
) tmp_count

Simplify the column binding syntax via dynamic proxy.

Consider simplifying the column binding syntax via jdk dynamic proxy.

Now the syntax is not so elegant, we have to repeat the entity class's name on every column:

object Employees : Table<Employee>("t_employee") {
    val id by int("id").primaryKey().bindTo(Employee::id)
    val managerId by int("manager_id").bindTo(Employee::manager, Employee::id)
    val departmentId by int("department_id").references(Departments, onProperty = Employee::department)
}

It could be implemented like this:

object Employees : Table<Employee>("t_employee") {
    val id by int("id").primaryKey().bindTo { it.id }
    val managerId by int("manager_id").bindTo { it.manager.id }
    val departmentId by int("department_id").references(Departments) { it.department }
}

Reactive support via R2DBC and Coroutines Flow

It would be awesome if Ktorm could be non-blocking and reactive by leveraging R2DBC instead of JDBC exposed via Coroutines and its Flow API instead of Sequence.

Android connection

我得到了以下错误

Array support

Hey. Is there any solution to store array data type via Ktorm?

实体查询find* 函数只能一个条件?

val employees = Employees.findList {
it.departmentId eq 1
it.name eq 'test'
}

这样写只有第2个条件生效，第一个无效

Joining a last activity record

I've following two entities where I'm trying to get the latest activity for an employee:

interface Activity : Entity<Activity> {
    val id: Long
    val desc: String
    val employee: Employee
    val createdAt: LocalDateTime?
    companion object : Entity.Factory<Activity>()
}

interface Employee : Entity<Employee> {
    var id: Long
    var name: String
    
    val latestActivity: Activity?
        get() {
            var activity = this["latestActivity"]
            if (activity == null) {
                activity = Activities.asSequenceWithoutReferences().sortedByDescending { it.createdAt }
                    .filter { it.employeeId eq id }.firstOrNull()
                this["latestActivity"] = activity
            }
            return activity as? Activity
        }
    companion object : Entity.Factory<Employee>()
}

This all works great but unfortunately for each employee getting the latestActivity is an extra query. What's the best way to fetch Employees and have it returned with latestActivity joined?

How to use entities with many to many relations ?

Let's say I have a table Recipe(id, name) and another Tag(id, label). Each recipe may have several tags and each tag can be used for several recipes, so i create a table TagRecipe(idTag, idRecipe).
How can I declare a Recipe entity having a list of tags ? This is what I have for now, but I don't know how to populate Recipe.tags :

interface Recipe: Entity<Recipe>{
    val id: Int
    val name: String
    val tags: List<Tag>
}

object Recipes: Table<Recipe>("recipes"){
    val id by int("id").primaryKey().bindTo { it.id }
    val name by varchar("name").bindTo { it.name }
}

interface Tag: Entity<Tag> {
    val id: Int
    val label: String
}

object Tags: Table<Tag>("tags"){
    val id by int("id").primaryKey().bindTo { it.id }
    val label by varchar("name").bindTo { it.label }
}

interface TagRecipe: Entity<TagRecipe>{
    val idTag: Int
    val idRecipe: Int
}

object TagRecipes: Table<TagRecipe>("tags_recipes"){
    val idTag by int ("idTag").primaryKey().bindTo{it.idTag}
    val idRecipe by int ("idRecipe").primaryKey().bindTo{it.idRecipe}
}

Provide a plugin for Intellij IDEA to generate table and entity definitions.

Use ServiceLoader to determine dialects and driver class names

Use ServiceLoader to determine dialects and driver class names, inspired by Spring Boot's org.springframework.boot.jdbc.DatabaseDriver.

So we don't have to provide the driver and dialect params when creating a Database object:

Database.connect(
    url = "jdbc:mysql://127.0.0.1:3306/ktorm",
    driver = "com.mysql.jdbc.Driver",
    user = "root",
    dialect = MySqlDialect
)

Parent / Child relationships

Hi,

is it currently possible to model parent / child relationships, something like this:

interface ContextElement : Entity<ContextElement> {
    val id: Long?
    var name: String
    val pId: ContextElement
}

object ContextElements : Table<ContextElement>("context_element") {
    val id by long("rowid").primaryKey().bindTo { it.id }
    val name by text("name").bindTo { it.name }
    val parentId by long("parent_id").references(ContextElements) {it.pId}
}

I get a circular reference error: Caused by: java.lang.IllegalArgumentException: Circular reference detected, current table: context_element, reference route: [context_element].

是否考虑添加使用数据库迁移来管理数据表的功能

手动管理数据表的话可能会造成表结构跟实体对应出错

specify nullable in schema definition

Would it be possible to add a "nullable()" method to the ColumnRegistration so that when mapping a QueryRowSet to a data class there is no need to use "?" for column types that cannot be null?

Exposed does this, and it's pretty nice - no need to do a bunch of unnecessary null checks.

mysql match operator

Is it possible to query using the MySQL match operator?

Use kapt to generate boilerplate codes.

Consider introducing annotation processor tools to generate boilerplate codes like:

interface Department : Entity<Department> {
    companion object : Entity.Factory<Department>()
    val id: Int
    var name: String
    var location: String
}

open class Departments(alias: String?) : Table<Department>("t_department", alias) {
    companion object : Departments(null)
    override fun aliased(alias: String) = Departments(alias)

    val id by int("id").primaryKey().bindTo(Department::id)
    val name by varchar("name").bindTo(Department::name)
    val location by varchar("location").bindTo(Department::location)
}

It's annoying to write those companion objects and to override aliased functions...

关于ktorm-jackson序列化对象

请问在使用ktorm-jackson序列化对象的时候，如何使用 @JsonProperty 注解为对象的属性改名

    val deviceTypes = DeviceTypes.findById(1);
    val objectMapper =  ObjectMapper()
    objectMapper.registerModule(KtormModule())
    println(objectMapper.writeValueAsString(deviceTypes))

得到结果
{"id":1,"type":2,"systemId":1,"name":"供水设施","status":1}

我需要把 systemId 转为 system_id 这种格式，加注解的话应该加在哪里，还是需要用别的办法？

The discardChanges function should not be recursive.

For example:

val department = Departments.findById(1) ?: return
department.name = "tech"

val employee = Employee()
employee.department = department
employee.name = "vince"
Employees.add(employee)

department.location = "Guangzhou"
department.flushChanges()

Then we will lost the update of department.name = "tech" unexpectedly.

Make Ktorm multiplatform

Would it be possible to make this library multiplatform? I guess that most of the SQL generation code may be already JVM independent.
I suppose the connection of course is not of course.

creating an alias with the AS operator

How would I do something like this:
SELECT id, body, MATCH (body) AGAINST ('mysql tutorial' IN BOOLEAN MODE) AS score FROM articles HAVING score > 0 ORDER BY score DESC;

How do I create an alias with AS?
How can I use that alias in a HAVING / ORDER BY clause?

Getting the last record of a table

Sorry I couldn't find this in the documentation. Is there a built-in efficient way to retrieve the last record from a table without loading all the rows and calling .lastOrNull() on it?

Max/min aggregation functions' type arguments should be `Comparable` instead of `Number`

SQLite Datetime column class cast exception

I've a simple entity that has a hired_at datetime type column. I can insert a new record successfully but trying to fetch it back throws a casting exception:

java.base/java.lang.Long cannot be cast to java.sql/java.sql.Date

This only happens with a SQLite connection and not with MySQL connection. Here is a repro repo: https://github.com/ashokgelal/ktorm-sqlite-repro
You need to copy ktrom_repro.sqlite file to your home folder.

Ktorm 'Contains' aggregate

when i we want to indicate if string contains an other string use contains method, how ktorm resolve this

Add a facade layer for logging frameworks.

Add a facade layer for logging frameworks, remove the dependency of slf4j.

Provide Sequence like APIs, introducing map/filter functions

Provide Sequence like APIs, introducing map/filter functions, for example:

val depts = Departments
    .filter { it.location eq "Guangzhou" }
    .sortedBy { it.id }
    .take(2)
    .skip(2)
    .toList()

The code above will generate sql like:

select * 
from t_department t
where t.location = ?
order by t.id
limit 2, 2

It'll be great to have such APIs, It seems that we are operating a kotlin.Sequence, but actually, SQLs are generated and executed.

DataBase 和Query解耦

query，最后map的时候会调用global的DataBase执行。是否可以类似：slick
// DB.run(DBIOActions) 这种方式。

项目非常棒，💪

doCreateEntity withReferences

Just saw the new functionality in 2.5:
https://ktorm.liuwj.me/en/define-entities-as-any-kind-of-classes.html

Creating an entity data class seems pretty straightforward, but how should the withReferences parameter be used?

InstantSqlType 的时区问题

您好，在使用 Ktorm 时遇到一点小问题，不知是不是我的用法有误。
简单来说，问题是字段的时间值在不同时区的执行环境下不一致，下面是具体说明。

我使用的数据库是 sql server，UpdateTime 字段类型是 datetime，其存储的是 +8 时区时间值。datetime 类型本身不带时区信息。

实体类和表定义如下：

interface Meter : Entity<Meter> {
    ...
    var updateTime: Instant
}

object Meters : Table<Meter>("Meters") {
    ...
    val updateTime by timestamp("UpdateTime").bindTo { it.updateTime }
}

在不同时区的系统上执行如下代码时，输出的结果不同：

Meters.asSequence().first().also { println(it.updateTime) }

// 数据库中 UpdateTime 字段内容为 2019-09-29 10:00:00.000
// +8时区系统（开发机）输出：2019-09-29T10:00:00.000Z
// 0时区系统（Docker）输出：2019-09-29T02:00:00.000Z

即 updateTime 总是按照执行环境的时区来进行转换，若数据库字段的时区和执行环境的时区不同，则得到的时间就不正确了。

下面是我针对这个问题写的一个扩展类型，使用时可传入数据库字段的实际时区。这能够解决我的问题。

class InstantSqlType(private val columnTimeZone: ZoneOffset) :
    SqlType<Instant>(java.sql.Types.TIMESTAMP, typeName = "instant") {

    override fun doSetParameter(ps: PreparedStatement, index: Int, parameter: Instant) {
        ps.setTimestamp(index, Timestamp.valueOf(parameter.atOffset(columnTimeZone).toLocalDateTime()))
    }

    override fun doGetResult(rs: ResultSet, index: Int): Instant? {
        val timestamp = rs.getTimestamp(index) ?: return null
        return timestamp.toLocalDateTime().atOffset(columnTimeZone).toInstant()
    }
}

fun <E : Any> BaseTable<E>.instant(name: String, columnTimeZone: ZoneOffset = ZoneOffset.UTC):
        BaseTable<E>.ColumnRegistration<Instant> {
    return registerColumn(name, InstantSqlType(columnTimeZone))
}

How to deserialize from JSON?

Say you are exposing some REST endpoint and the client POSTs you a modified entity, is it possible to deserialize it directly in a Ktorm object? If so, can you update the entity on the database as well?