0%

JPA with DB-First Approach: Surgery on JOOQ's POJO into Base @Entity Class

October 20, 2024

Jooq

Jpa

Kotlin

Springboot

Contents

1. Surgery on JOOQ generated POJO

1.1. Introduction to the POJO and the Strategy of the Surgery

We have set jooq to generate pojo file with withJpaAnnotation.

A typical example of a JOOQ generated pojo:

@Suppress("UNCHECKED_CAST")
@Entity
@Table(
    name = "Order",
    schema = "public",
    indexes = [
        Index(name = "Order_id_userEmail_idx", columnList = "id ASC, userEmail ASC")
    ]
)
data class Order(
    @get:Id
    @get:Column(name = "id")
    var id: UUID? = null,
    @get:Column(name = "error")
    var error: String? = null,
    @get:Column(name = "status")
    var status: Status? = null,
    @get:Column(name = "succeededAt")
    var succeededat: Double? = null,
    @get:Column(name = "failedAt")
    var failedat: Double? = null,
    @get:Column(name = "userEmail", nullable = false)
    var useremail: String,
    @get:Column(name = "orderType")
    var ordertype: Ordertype? = null,
    @get:Column(name = "createdAt")
    var createdat: Double? = null,
    @get:Column(name = "createdAtHK")
    var createdathk: String? = null
): Serializable {
    ...
}

What we will be automating:

  • Get rid of all kotlin-specific @get:'s which cause error by experiment
  • Rename Order to OrderEntity_
  • Detect all enum type, for each declaration of enum we add 
    @Enumerated(EnumType.STRING)
@JdbcType(PostgreSQLEnumJdbcType::class)
    this is to enable jpa to correctly convert kotlin's enum into postgres' enum (jpa treats enum as small int by default)
  • For every id: UUID? = null we add 
    @GeneratedValue(generator = "ulid_as_uuid")
    since we have custom generation method for uuid.
  • Remove index part in @Table() as indexing has been done in db-first step and @Table must be bundled with @Entity
  • Remove Serializable
  • Get rid of everything inside { ... } since no method declaration is needed

1.2. Sample Result After Surgery

import com.billie.db.enums.Ordertype
import com.billie.db.enums.Status
import org.hibernate.annotations.DynamicInsert
import org.hibernate.dialect.PostgreSQLEnumJdbcType
import org.hibernate.annotations.JdbcType
import jakarta.persistence.Column
import jakarta.persistence.Entity
import jakarta.persistence.Enumerated
import jakarta.persistence.EnumType
import jakarta.persistence.Id
import jakarta.persistence.Table
import java.util.UUID

@Entity
@DynamicInsert
@Table(
    name = "Order",
    schema = "public"
)
class OrderEntity_(
    @Id
    @Column(name = "id")
     var id: UUID? = null,
    @Column(name = "error")
     var error: String? = null,
    @Column(name = "status")
    @Enumerated(EnumType.STRING)
    @JdbcType(PostgreSQLEnumJdbcType::class)
     var status: Status? = null,
    @Column(name = "succeededAt")
     var succeededat: Double? = null,
    @Column(name = "failedAt")
     var failedat: Double? = null,
    @Column(name = "userEmail", nullable = false)
     var useremail: String,
    @Column(name = "orderType")
    @Enumerated(EnumType.STRING)
    @JdbcType(PostgreSQLEnumJdbcType::class)
     var ordertype: Ordertype? = null,
    @Column(name = "createdAt")
     var createdat: Double? = null,
    @Column(name = "createdAtHK")
     var createdathk: String? = null
)

2. Execution of the Surgery via Customized Gradle Task in build.gradle.kts

2.1. The Original Configuration of JOOQ Generation Task

tasks.create("generate") {
    val pojoDir = File("$projectDir/src/main/kotlin/com/billie/db/tables/pojos")
    val preEntityDir = File("$projectDir/src/main/kotlin/com/billie/db/tables/preentities")

    if (pojoDir.exists()) {
        pojoDir.deleteRecursively()
    }
    if (preEntityDir.exists()) {
        preEntityDir.deleteRecursively()
    }

    GenerationTool.generate(
        Configuration()
            .withJdbc(
                Jdbc()
                    .withDriver("org.postgresql.Driver")
                    .withUrl("xxx")
                    .withUser("james.lee")
                    .withPassword("xxx")
            )
            .withGenerator(
                Generator()
                    .withName("org.jooq.codegen.KotlinGenerator")
                    .withDatabase(
                        Database()
                            .withInputSchema("public")
                            .withExcludes("pgp_armor_headers")
                    )
                    .withGenerate(
                        Generate()
                            .withPojos(true)
                            .withDaos(true)
                            .withSpringAnnotations(true)
                            .withJpaAnnotations(true)
                            .withKotlinNotNullPojoAttributes(true)
                            .withKotlinDefaultedNullablePojoAttributes(true)
                    )
                    .withTarget(
                        Target()
                            .withPackageName("$srcPackage.db")
                            .withDirectory("$projectDir/src/main/kotlin")
                    )
            )
    )
    adjustJooqFilesForJPA(pojoDir = pojoDir,
                          preEntityDir = preEntityDir)
}

The adjustJooqFilesForJPA will

  1. Copy the pojos to another folder

  2. Modify every file in that new folder

2.2. The Extra Amendment Step: adjustJooqFilesForJPA

  • Here we copy all pojos/XXX.kt into preentities/XXXEntity_.kt.

  • We then do the text manipulation in adjustJooqFilesForJPA to simplify the jooq's @Entity classes

  • We later copy the whole definition by creating our AbstractAggregateRoot where

    • We can define our custom join-column behaviour (aggregates) and

    • custom domain behaviours.

fun getEnumList(): Sequence<String> {
    val enumDir = File("$projectDir/src/main/kotlin/com/billie/db/enums")

    val enumNameList = enumDir.walkTopDown()
        .filter { it.isFile && it.extension == "kt" }
        .map { file -> file.name.replace(".kt", "") }
    return enumNameList
}

fun adjustJooqFilesForJPA(pojoDir: File, preEntityDir: File) {
    if (pojoDir.exists()) {
        val enumNameList = getEnumList()
        preEntityDir.deleteRecursively()
        pojoDir.copyRecursively(preEntityDir)

        preEntityDir.walkTopDown().filter { it.isFile && it.extension == "kt" }.forEach { file ->
            val content = file.readText()

            val modifiedContent = content
                .replace("package com.billie.db.tables.pojos", "package com.billie.db.tables.preentities")
                .replace(Regex("^(data )?class ", RegexOption.MULTILINE),
                         "open class ")
                .replace(Regex("""open class (\w+)\("""),
                         """class $1Entity_\(""")
                .replace(Regex("""(@get:.+\n\s*)var""", RegexOption.MULTILINE),
                         "$1 var")
                .replace("import jakarta.persistence.GeneratedValue",
                         "")
                .replace("import jakarta.persistence.GenerationType",
                         "")
                .replace(Regex("""indexes\s+=.*?\]""", RegexOption.DOT_MATCHES_ALL), "")
                .replace(Regex("""import jakarta.persistence.Entity""".trimIndent()),
                         """
                     import jakarta.persistence.Entity
                     import jakarta.persistence.MappedSuperclass
                     import jakarta.persistence.Enumerated
                     import jakarta.persistence.EnumType
                     import jakarta.persistence.Convert
                     import jakarta.persistence.GeneratedValue
                     import jakarta.persistence.GenerationType
                     import org.hibernate.annotations.DynamicInsert
                     import org.hibernate.dialect.PostgreSQLEnumJdbcType
                     import org.hibernate.annotations.JdbcType
                     """.trimIndent().trimMargin())
                .replace(Regex(""": Serializable""", RegexOption.DOT_MATCHES_ALL),
                         "")
                .replace(Regex("""@get:""", RegexOption.MULTILINE),
                         "@")
                .replace(Regex("""@Column\(name = "id"\).*?open var id: UUID\? = null""", RegexOption.DOT_MATCHES_ALL),
                         """
                     |@Column(name = "id")
                     |    @GeneratedValue(generator = "ulid_as_uuid")
                     |    var id: UUID? = null
                     """.trimMargin()
                )
                .replace("@Entity", """
                    @Entity
                    @DynamicInsert
                """.trimIndent())
                .replace("@Generate()", "")
                .replace("var id: Int? = null",
                         """
                         @Generate()
                         |    var id: Int? = null
                         """.trimIndent().trimMargin())
                .replace(Regex("""\{.*\}""", RegexOption.DOT_MATCHES_ALL), "")
                .split("\n")
                .map { line ->
                    val isEnumDeclared = enumNameList.any { cls ->
                        val enumFound = line.indexOf(": $cls") > -1
                        enumFound
                    }
                    if (isEnumDeclared) {
                        """
                        |    <<enum_annotations>>
                        $line
                        """.trimIndent().trimMargin()
                    } else {
                        line
                    }
                }.joinToString("\n")
                .replace("<<enum_annotations>>",
                         "@Enumerated(EnumType.STRING)\n    @JdbcType(PostgreSQLEnumJdbcType::class)")

            if (content != modifiedContent) {
                file.writeText(modifiedContent)
                val newFilepath = "${file.parent}/${file.nameWithoutExtension}Entity_.kt"
                file.renameTo(File(newFilepath))
            }
        }
    } else {
        println("Source POJO folder does not exist")
    }
}

3. How to Create AbstractAggregateRoot?

3.1. OrderEntity extending AbstractAggregateRoot<OrderEntity>

Let's consider the following Order aggregate:

Now we copy the definition of simplified @Entity classes and create a domain object:

@Entity
@DynamicInsert
@Table(name = "Order", schema = "public")
class OrderEntity(
    @Id
    @Column(name = "id")
    @GeneratedValue(generator = "ulid_as_uuid")
    var id: UUID? = null,
    @Column(name = "error")
    var error: String? = null,
    @Column(name = "status")
    @Enumerated(EnumType.STRING)
    @JdbcType(PostgreSQLEnumJdbcType::class)
    var status: Status? = null,
    @Column(name = "succeededAt")
    var succeededat: Double? = null,
    @Column(name = "failedAt")
    var failedat: Double? = null,
    @Column(name = "userEmail", nullable = false)
    var useremail: String,
    @Column(name = "orderType")
    @Enumerated(EnumType.STRING)
    @JdbcType(PostgreSQLEnumJdbcType::class)
    var ordertype: Ordertype? = null,
    @Column(name = "createdAt")
    var createdat: Double? = null,
    @Column(name = "createdAtHK")
    var createdathk: String? = null,
) : AbstractAggregateRoot<OrderEntity>() {

    @OneToOne(mappedBy = "orderEntity", fetch = FetchType.LAZY, cascade = [CascadeType.PERSIST])
    @JsonManagedReference
    var orderStripe: OrderStripeEntity? = null

    @OneToOne(mappedBy = "orderEntity", fetch = FetchType.LAZY, cascade = [CascadeType.PERSIST])
    @JsonManagedReference
    var orderMobile: OrderMobileEntity? = null

    fun updateStripeOrder(stripeOrder: OrderStripe) {
        registerEvent(StripeOrderUpdatedEvent(stripeOrder))
    }

    fun updateOrderDetailSubscriptionId(subscriptionId: String) {
        registerEvent(SubscriptionCreatedEvent(orderId = this.id!!, subscriptionId = subscriptionId))
    }

    fun updateOrderSucceededInfo() {
        this.status = Status.SUCCEEDED
        this.succeededat = DateTime().millis.toDouble()
        registerEvent(
            OrderSucceededEvent(orderId = this.id!!,
                                succeededAt = this.succeededat!!)
        )
    }

    fun createCheckoutSession(
        customerStripeId: String,
        productName: String,
        numOfPersons: Int,
        targetPriceId: String,
    ) {
        val orderId = this.id!!
        registerEvent(CreateStripeSessionCommand(orderId,
                                                 customerStripeId,
                                                 productName,
                                                 numOfPersons,
                                                 targetPriceId))
    }

    fun appleOrderSucceeded() {
        val orderId = this.id!!
        registerEvent(AppleOrderSucceededEvent(orderId))
    }
}

3.2. OrderMobileEntity (Anemic Model)

@Entity
@DynamicInsert
@Table(name = "Order_Mobile", schema = "public")
class OrderMobileEntity(
    @Id
    @Column(name = "id")
    var id: UUID? = null,
    @Column(name = "orderId", nullable = false)
    var orderid: UUID,
    @Column(name = "period", nullable = false)
    @Enumerated(EnumType.STRING)
    @JdbcType(PostgreSQLEnumJdbcType::class)
    var period: Period,
    @Column(name = "platform", nullable = false)
    @Enumerated(EnumType.STRING)
    @JdbcType(PostgreSQLEnumJdbcType::class)
    var platform: Platform,
    @Column(name = "originalAppUserId", nullable = false)
    var originalappuserid: String,
    @Column(name = "userEmail", nullable = false)
    var useremail: String,
) {
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "orderId", referencedColumnName = "id", insertable = false, updatable = false)
    @JsonBackReference
    var orderEntity: OrderEntity? = null
}

3.3. OrderStripeEntity (Anemic Model)

@Entity
@DynamicInsert
@Table(name = "Order_Stripe", schema = "public")
class OrderStripeEntity(
    @Id
    @Column(name = "id")
    var id: UUID? = null,
    @Column(name = "stripeSessionId")
    var stripesessionid: String? = null,
    @Column(name = "actionType", nullable = false)
    @Enumerated(EnumType.STRING)
    @JdbcType(PostgreSQLEnumJdbcType::class)
    var actiontype: Actiontype,
    @Column(name = "numOfPersons", nullable = false)
    var numofpersons: Int,
    @Column(name = "subscriptionId")
    var subscriptionid: String? = null,
    @Column(name = "actionTargetSeatId")
    var actiontargetseatid: Int? = null,
    @Column(name = "quota_SeatId")
    var quotaSeatid: Int? = null,
    @Column(name = "orderId", nullable = false)
    var orderid: UUID,
) {
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "orderId", referencedColumnName = "id", insertable = false, updatable = false)
    @JsonBackReference
    private val orderEntity: OrderEntity? = null
}

3.4. Finally, Avoid Back Reference that Causes Infinite Loop in Data Serialization

In short

  • inside of aggregate root we annotate subaggregate/subdomain object by @JsonManagedReference.
  • inside of subdomain object we add @JsonBackReference to the backward reference.
import com.fasterxml.jackson.annotation.JsonManagedReference
import com.fasterxml.jackson.annotation.JsonBackReference

class Parent {
    @OneToMany(mappedBy = "parent")
    @JsonManagedReference
    val children: List<Child> = mutableListOf()
}

class Child {
    @ManyToOne // so is @OneToOne
    @JsonBackReference
    lateinit var parent: Parent
}

4. JpaRepository

4.1. The naming convention of findByXXX

For a complete of convention please visit the

Let's look at our entity class:

@Entity
@DynamicInsert
@Table(name = "Quota_FreeQuotaRecord", schema = "public")
class QuotaFreeEntity(
    @Id
    @Column(name = "id")
    @Generate()
    var id: Int? = null,
    @Column(name = "userEmail", nullable = false)
    var useremail: String,
    @Column(name = "audioUsed", nullable = false)
    var audioused: Double,
    @Column(name = "summaryUsed", nullable = false)
    var summaryused: Int,
) : AbstractAggregateRoot<QuotaFreeEntity>() {
    fun notifyFreeQuotaCreated() {
        val event = FreeQuotaCreatedEvent(this.useremail)
        registerEvent(event)
    }

    fun increaseSummaryCount() {
        this.summaryused = (this.summaryused ?: 0) + 1
        registerEvent(FreeQuotaSummaryCountedEvent(this.id!!))
    }
}

Note that our member name is useremail, we capitalize the first letter to get:

@Repository
interface FreeQuotaJpaRepository : JpaRepository<QuotaFreeEntity, Int> {
    fun findByUseremail(email: String): QuotaFreeEntity?
}

the custom repository method depends on the member name of our entity class but not on the actual column name.

4.2. When Tables and Columns are not Named in Snake Case

Since we use camel case in table and column name instead of lower-letter snake case which jpa recognizes by default, in every query we have to enclose every single occurence of table and column name by two double quote "'s.

We archive this by setting custom naming strategy for jpa:

package com.billie.payment.config.jooq

import org.hibernate.boot.model.naming.Identifier
import org.hibernate.boot.model.naming.PhysicalNamingStrategy
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment

class QuotedIdentifiersNamingStrategy : PhysicalNamingStrategy {

    override fun toPhysicalCatalogName(name: Identifier?, context: JdbcEnvironment?): Identifier? {
        return name?.let { addQuotes(it) }
    }

    override fun toPhysicalSchemaName(name: Identifier?, context: JdbcEnvironment?): Identifier? {
        return name?.let { addQuotes(it) }
    }

    override fun toPhysicalTableName(name: Identifier?, context: JdbcEnvironment?): Identifier? {
        return name?.let { addQuotes(it) }
    }

    override fun toPhysicalSequenceName(name: Identifier?, context: JdbcEnvironment?): Identifier? {
        return name?.let { addQuotes(it) }
    }

    override fun toPhysicalColumnName(name: Identifier?, context: JdbcEnvironment?): Identifier? {
        return name?.let { addQuotes(it) }
    }

    private fun addQuotes(id: Identifier): Identifier {
        return Identifier.quote(id)
    }
}

Next we instruct hibernate to enclose all names by double quote:

spring:
  jpa:
    hibernate:
      naming:
        physical-strategy: com.billie.payment.config.jooq.QuotedIdentifiersNamingStrategy
    properties:
      hibernate:
        globally_quoted_identifiers: true
        type:
          EnumType: STRING

4.3. Test Cases

4.3.1. Set the stage in our test:
1@SpringBootTest
2class RepositoryTest {
3
4    @Autowired
5    private lateinit var orderMobileJpaRepository: OrderMobileJpaRepository
6
7    @Autowired
8    private lateinit var orderJpaRepository: OrderJpaRepository
9
10    init {
11        System.setProperty("spring.profiles.active", "uat,james_db_and_james_stripe")
12    }
4.3.2. Test if we are abole to persist an entity:
14  @Test
15  fun `repository save`() {
16      val orderEntity = OrderEntity(useremail = "james.lee@wonderbricks.com")
17      orderEntity.ordertype = Ordertype.MOBILE
18      orderJpaRepository.save(orderEntity)
19      val orderMobileEntity = OrderMobileEntity(orderid = orderEntity.id!!,
20                                                period = Period.MONTHLY,
21                                                platform = Platform.IOS,
22                                                originalappuserid = "123",
23                                                useremail = orderEntity.useremail)
24      orderMobileJpaRepository.save(orderMobileEntity)
25}
4.3.3. Test if the "back-reference" works, and test if the domain event can be caught by @EventListener.
39    @Test
40    fun `repository get`() {
41        val orderEntity = orderJpaRepository.findByIdOrNull(UUID.fromString("77d8fd43-b780-4116-9b8e-dc4d032a3754"))
42        val orderMobileEntity = orderEntity?.orderMobile
43        val theParentEntity = orderMobileEntity?.orderEntity // this is the same as orderEntity in the first line
44        theParentEntity?.updateOrderSucceededInfo()
45        orderJpaRepository.save(theParentEntity!!) // successfully dispatch an event and we get the event from event handler
46
47        println(orderMobileEntity)
48        println(theParentEntity)
49    }
50}

4.4. Example of Adding Domain Behaviour and How it Actually Works with Our Controller

4.4.1. The Entity Class
data class SeatSummaryCountedEvent(
    val entity: QuotaSeatCounterEntity,
)

@Entity
@DynamicInsert
@Table(name = "Quota_UsageCounter", schema = "public")
class QuotaSeatCounterEntity(
    override var seatid: Int,
    override var audioused: Double,
    override var duedate: Double,
    override var startdate: Double,
) : IDomainModel, QuotaUsagecounterPreEntity(
    seatid = seatid,
    audioused = audioused,
    duedate = duedate,
    startdate = startdate
) {

    @Transient
    override var domainEvents: MutableList<Any>? = null

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "seatId", referencedColumnName = "id", insertable = false, updatable = false)
    @JsonBackReference
    var seat: QuotaSeatEntity? = null

    fun increaseSummaryCount() {
        this.summarygenerated = (this.summarygenerated ?: 0) + 1
        val event = SeatSummaryCountedEvent(this)
        registerEvent(event)
    }
}
4.4.2. The Controller Method

  • The highlighted demonstrates the state change can be managemented by the entity itself.

  • In the past without ORM we have to handle state change in eventListener. With ORM we can now arrange all the in-memory change, and let jpa figure out and persist the changes by repo.save(entity).

  • By repo.save(), jpa will look at the member annotated by @DomainEvents, then dispatch each event synchronously via ApplicationEventPublisher.

    @PostMapping("/increase-summary-count")
    fun increaseSummaryCount(@RequestBody reduceQuotaDto: IncreaseSummaryCountRequest): Response.Success<IncreaseSummaryCountResponse> {
        val user = UserContext.instance.getUser()
        val (counterId) = reduceQuotaDto
        if (counterId == null) {
            val freeQuota = freeQuotaJpaRepository.findByUseremail(user.email) ?: throw Exception("free quota has not created")
            freeQuota.increaseSummaryCount()
            freeQuotaJpaRepository.save(freeQuota)
        }
        val counter = seatCounterJpaRepository.findByIdOrNull(counterId) ?: throw Exception("counter not found")
        counter.increaseSummaryCount()
        seatCounterJpaRepository.save(counter)
        return Response.Success(result = IncreaseSummaryCountResponse(counterId))
    }

Therefore when we execute repo.save() method, we are actually doing:

  • Persist the state change

  • Notify all domains which is interested in the SeatSummaryCountedEvent.

5. @EntityScan

To play safe just list out all the packages where our entity class live in.

@EntityScan(basePackages = [
    "com.billie.db",
    "com.billie.payment"
])
class PaymentApplication {
    ...
}