带有jooq的kotlin并手动编写表模型而无需代码生成 [英] kotlin with jooq and write table models manually without code generation

问题描述

我正在尝试jOOQ和Kotlin，并看了一些教程和文档，它看起来非常不错.

I'm experimenting with jOOQ and Kotlin and seen some tutorials and docs and it looks really nice.

但是，如果jOOQ有一些非常令人讨厌的地方，那就是代码生成.它似乎太复杂了，最终无法维护.我决定创建自己的表模型(类似于休眠方式).

But if there is something very annoying with jOOQ is the code generation. It seems too complex, and eventually impossible to maintain. I decided to create my own table models (similar to how hibernate works).

我创建了两个表格模型:

I created two table models:

用户

data class User(
    val id: String = UUID.randomUUID().toString(),
    val name: String,
    val email: String,
    val password: String? = null
) {
    companion object {
        val TABLE: Table<Record> = DSL.table("user")
        val ID: Field<String> = DSL.field("id", String::class.java)
        val USER_NAME: Field<String> = DSL.field("user_name", String::class.java)
        val EMAIL: Field<String> = DSL.field("email", String::class.java)
        val PASSWORD: Field<String> = DSL.field("password", String::class.java)
    }
}

关注者

data class Followers(
    val id: String,
    val followerId: String,
    val userId: String
) {
    companion object {
        val TABLE: Table<Record> = DSL.table("followers")
        val ID: Field<String> = DSL.field("id", String::class.java)
        val FOLLOWER_ID: Field<String> = DSL.field("follower_id", String::class.java)
        val USER_ID: Field<String> = DSL.field("user_id", String::class.java)
    }
}

当我执行一些琐碎的SQL语句并能很好地工作时，但是当我尝试下一条语句时，却遇到了异常.

When I did some trivial SQL statements and it worked perfectly, but when I tried the next statement, I'm getting exception.

return dsl.select().from(u.TABLE)
            .rightJoin(f.TABLE).on(u.ID.eq(f.FOLLOWER_ID))
            .where(u.ID.eq(id)).fetch().into(User::class.java)

此代码的预期语句为:

select *
from user u
right outer join followers f
on u.id = f.follower_id
where u.id = 'e30919bf-5f76-11e8-8c96-701ce7e27f83';

但是我从这段代码中得到的语句是:

But the statement I got from this code is:

select *
from user
  right outer join followers
  on id = follower_id
where id = 'e30919bf-5f76-11e8-8c96-701ce7e27f83'

当然，这正确地给了我错误 where子句中的列'id'不明确

And of course, this givse me (rightfully) the error Column 'id' in where clause is ambiguous

它提出了一些问题:

1. 有没有一种更好的方法来声明表模型而无需代码生成.
2. 为什么DSL select无法转换为正确的SQL语句?我在做什么错了?

1. Is there a better way to declare table model without code generation.
2. Why the DSL select does not transform to proper SQL statement? What I'm doing wrong?

推荐答案

首先，关于不愿使用代码生成的一些建议:

我似乎太复杂了，最终无法维护. 因此，我决定创建自己的表模型(类似于休眠方式).

i seems too complex, and eventually impossible to maintain. so, i decided to create my own table models (similar to how hibernate works).

(可能)您经历了很长的痛苦和磨难.首先，您现在已经需要考虑数据库迁移，最好使用数据库的DDL语言来完成.这意味着，从长远来看，数据的数据库模型对您而言应该比客户端模型更为重要.实际上，您的客户端模型是数据库模型的副本，而不是您想要独立维护的东西.有了这种思想，让代码生成器从数据库模型生成客户端模型更为合理，反之亦然.

You're (probably) going down a long path of pain and suffering. First off, you will already now need to think of database migrations, which are best done using your database's DDL language. This means, your database model of your data should be more important to you in the long run, than your client model. In fact, your client model is a copy of your database model, not something you'd like to maintain independently. With this mindset, it is more reasonable to have a code generator generate your client model from the database model, not vice versa.

当然，在启动项目时，Hibernate还使客户端优先方法也变得容易.但是，一旦投入生产，您将 必须迁移数据库，然后此模型将崩溃.您首先要返回数据库，现在就应该进行所有设置.

Sure, Hibernate makes the client first approach easy as well, when you start a project. Yet, once you go to production, you will have to migrate your database, and then this model will break. You're back to database first, and it's worth setting up everything already now.

所以，不.代码生成可能会引入一些 now 复杂性，但是与创建自己的表模型相比，维护起来更容易 .

So, no. Code generation might introduce some complexity now, but it will be much more easy to maintain down the road, than you creating your own table models.

I've written up a longer blog post about this topic, here.

return dsl.select().from(u.TABLE)
          .rightJoin(f.TABLE).on(u.ID.eq(f.FOLLOWER_ID))
          .where(u.ID.eq(id)).fetch().into(User::class.java)

此代码的预期语句为:[...]

the expected statement from this code is: [...]

好吧，这取决于u和f是什么.您不能只是将Kotlin引用重命名为表并期望jOOQ知道它们的含义. IE.您可能按如下方式创建了引用:

Well, that depends on what u and f are. You cannot just rename your Kotlin references to your table and expect jOOQ to know what they mean. I.e. you probably created the references as follows:

val u = User.TABLE;
val f = Follower.TABLE;

如果这是您创建引用的方式，那么根据身份，这两件事就是同一件事. jOOQ不会神奇地对您的Kotlin代码进行逆向工程以发现您要别名您的表.您必须告诉jOOQ:

If that's how you created the reference, then the two things are the same thing by identity. jOOQ doesn't magically reverse engineer your Kotlin code to find out that you meant to alias your table. You have to tell jOOQ:

val u = User.TABLE.as("u");
val f = Follower.TABLE.as("f");

但是现在您还没有完成.您使用普通SQL API构造了User.TABLE参考，这意味着jOOQ的运行时不了解该表中的列.您不能再从别名表中引用这些列，因为普通SQL表的别名表的类型是Table<?>，而不是User.

But now you're not done. You constructed the User.TABLE reference using the plain SQL API, which means that jOOQ's runtime has no idea about the columns in that table. You cannot reference those columns anymore from the aliased table, because the type of the aliased table for plain SQL tables is Table<?>, not User.

当然，您可以创建TableImpl实例并在TableImpl实例中注册所有列-就像代码生成器一样.在这种情况下，您将具有与它们关联的表和列，并且即使使用别名表也可以安全地使用它们.

You could, of course, create TableImpl instances and register all columns inside of your TableImpl instance - just like the code generator does. In that case, you would have tables and columns associated with them, and could use them type safely even with aliased tables.

所有这些东西都是由生成的代码自动处理的，同样，我建议您将其与jOOQ一起使用.任何人都不会将代码生成器与jOOQ一起使用的主要原因是因为数据模型是动态的，即在编译时未知.否则，您将自动重复代码生成器已经为您完成的大量工作.而且，如前所述，当您开始迁移架构时，您将在以后做更多的工作.

All of this stuff is handled automatically by generated code, which again, I recommend you use with jOOQ. The main reason why anyone would not use the code generator with jOOQ is because the data model is dynamic, i.e. not known at compile time. Otherwise, you're just going to repeat tons of work that the code generator already does for you, automatically. And, as mentioned before, you will have much more work later on, when you start migrating your schema.

这篇关于带有jooq的kotlin并手动编写表模型而无需代码生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！