https://kotlinlang.org logo
#exposed
Title
# exposed
l

Lbenyehu

12/21/2020, 9:19 AM
Hi guys, How can I dynamically determine (on run time) which column in the table insert a value? In my issue, every transaction, I insert/update different columns…
j

Joel

12/21/2020, 2:33 PM
Map different columns and select them logically in code
l

Lbenyehu

12/21/2020, 2:36 PM
Can you write short example? lets say my table is:
Copy code
object record : Table() {    
    val id = integer("ewid") // Column<Int>
    // Buckets Columns:
    val bckt_0_2_ms = integer("bckt_0_2_ms").nullable() // Column<Int>
    val bckt_3_4_ms = integer("bckt_3_4_ms").nullable() // Column<Int>
    val bckt_5_7_ms = integer("bckt_5_7_ms").nullable() // Column<Int>
}
And every transaction, I insert/update different buckets… How should I write a generic code for it?
j

Joel

12/21/2020, 2:38 PM
Copy code
record.insert { stmt ->
  val columnToUpdate = when (someValue) {
    conditionOne -> bckt_0_2_ms
    conditionTwo -> bckt_3_4_ms
    else -> bckt_5_7_ms
  }
  
  stmt[columnToUpdate] = someValue
}
l

Lbenyehu

12/21/2020, 2:47 PM
Exposed doesn’t like it… 😔
I solve it by building row SQL query for it… but I dont like my solution… Really hope to find a solution with the “insert” statement of the framwork…
and I have about 300 buckets (columns) so, I have to find a solution to build the “insert” dynamically
j

Joel

12/21/2020, 2:56 PM
Copy code
object Record : Table() {
    val id = integer("ewid") // Column<Int>
    // Buckets Columns:
    val bckt_0_2_ms = integer("bckt_0_2_ms").nullable() // Column<Int>
    val bckt_3_4_ms = integer("bckt_3_4_ms").nullable() // Column<Int>
    val bckt_5_7_ms = integer("bckt_5_7_ms").nullable() // Column<Int>
}

fun foo() {
    val someValue = Random.nextInt(0, 10)

    Record.insert { stmt ->
        val columnToUpdate = when (someValue) {
            in (0 until 3) -> bckt_0_2_ms
            in (3 until 6) -> bckt_3_4_ms
            else -> bckt_5_7_ms
        }

        stmt[columnToUpdate] = someValue
    }
}
This compiles just fine for me
Copy code
object Record : Table() {
    val id = integer("ewid") // Column<Int>

    val bucketColumns = mapOf(
        (0 until 3) to "bckt_0_3_ms",
        (3 until 6) to "bckt_3_6_ms",
        (6 until 9) to "bckt_6_9_ms",
    ).mapValues { (_, colName) ->
        integer(colName).nullable()
    }
}

fun foo() {
    val someValue = Random.nextInt(0, 10)

    Record.insert { stmt ->
        val bucketColumn = bucketColumns.entries.first { someValue in it.key }.value
        stmt[bucketColumn] = someValue
    }
}
l

Lbenyehu

12/21/2020, 3:01 PM
WOW!
tnx! about to try this!
j

Joel

12/21/2020, 3:01 PM
Copy code
object Record : Table() {
    val id = integer("ewid") // Column<Int>

    val bucketColumns = mapOf(
        (0 until 3) to integer("x").nullable(),
        (3 until 6) to integer("y").nullable(),
        (6 until 9) to integer("z").nullable(),
    )
}
If you need to have differences in each column
I actually don't have any code like this so I'm interested if it works 🤣
l

Lbenyehu

12/21/2020, 3:04 PM
I’ll update… Should I need to wrap with ”
Copy code
transaction {}
the insert statment?
j

Joel

12/21/2020, 3:05 PM
Yes always
You have to be within a transaction context anytime you touch the db
l

Lbenyehu

12/21/2020, 3:06 PM
OK that’s what I assumed
j

Joel

12/21/2020, 3:07 PM
The key here is that rather than making the column name dynamic, you tell Exposed about all the potential columns, and then treat them as Just Another Kotlin Variable.
l

Lbenyehu

12/21/2020, 3:08 PM
That’s what I looked for…
I’m new in all “Kotlin s world” ☺️
Still, compilation issue… 🤷‍♂️
j

Joel

12/21/2020, 3:22 PM
It's working on my end 🙂
l

Lbenyehu

12/21/2020, 3:23 PM
maybe exposed version??
I’m working with:
Copy code
<dependency>
            <groupId>org.jetbrains.exposed</groupId>
            <artifactId>exposed-core</artifactId>
            <version>0.28.1</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.exposed</groupId>
            <artifactId>exposed-dao</artifactId>
            <version>0.28.1</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.exposed</groupId>
            <artifactId>exposed-jdbc</artifactId>
            <version>0.28.1</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.exposed</groupId>
            <artifactId>exposed-jodatime</artifactId>
            <version>0.28.1</version>
        </dependency>
        <dependency>
            <groupId>org.jetbrains.exposed</groupId>
            <artifactId>exposed-java-time</artifactId>
            <version>0.28.1</version>
        </dependency>
j

Joel

12/21/2020, 3:38 PM
Random.nextInt
l

Lbenyehu

12/21/2020, 3:39 PM
ok I think it’s work, or anyway without compilation issues…
I have to check this…
tnx again
🙂
It works! Thanks again! Now I’ve to find nice solution for POSTGRESQL upsert method
🙂
7 Views