kotlinlang #exposed

Hello, everyone! It was a long time since the last release and I want to apologize for the silence on the slack and github. It was very hard two months for me personally (a lot of work on main projects and almost no time and energy to do something with Exposed). Today I want to announce Exposed 0.29.1 release with a lot of bugfixes and few features. I want to thank the community for a lot of attention and PRs. There are several huge PRs those I have to check and as they can break backward compatibility we have to prepare smooth plan of migration. Also, I have unfinished branches with R2DBC and Exposed Intellij IDEA plugin, so stay tuned!

👏 12

🎉 7

🔝 4

Endre Deak

01/26/2021, 1:32 AM

Sorry if this was asked previously, tried to find but no result. I know that I can execute raw SQL with Exposed, and I also know that currently there’s no support for

UNION

. I need

UNION

in my query, so I was wondering if I could wrap an SQL statement (something like

SELECT 1 AS idx UNION ALL SELECT 2 UNION ALL SELECT 3

) and then I could use this expression to join to another table?

Kuba Petržílka

01/28/2021, 7:58 PM

Hi, why would one prefer Exposed over JOOQ? I hear some complains about lot of boilerplate code needed in case of Exposed. Can you sum up what are the key benefits of using Exposed?

Das135

02/05/2021, 8:42 AM

Hello. I have two questions: 1. Does Exposed suport sequence
autoincrement
insert for MSSQL? I have column in Table defined like this:

Copy code

val id: Column<Long> = long("id").autoIncrement("users_seq")

override val primaryKey: PrimaryKey = PrimaryKey(id)

And when inserting object (without defined inc column) I get this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'id', table ''; column does not allow nulls. INSERT fails.

2. How to store NULL value to
varbinary(max)
MSSQL column with Exposed? I have this column type:

Copy code

val image = binary("image", Int.MAX_VALUE).nullable()

When trying to insert null value, I get error:

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

Tarun Chawla

02/13/2021, 12:25 PM

Am getting this continuous warnings related to cleanup:

Copy code

Feb 13, 2021 5:28:37 PM com.impossibl.postgres.jdbc.ThreadedHousekeeper$HousekeeperReference cleanup
WARNING: Cleaning up leaked result-set
Allocation occurred @
  at org.jetbrains.exposed.sql.statements.jdbc.JdbcPreparedStatementImpl.executeQuery(JdbcPreparedStatementImpl.kt:21)
  at org.jetbrains.exposed.sql.Query.executeInternal(Query.kt:79)
  at org.jetbrains.exposed.sql.Query.executeInternal(Query.kt:15)
  at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed_core(Statement.kt:61)
  at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:129)

Due to this elephant sql is complaining about too many multiple connections. Am using exposed version 0.29.1 and latest pgjdbc-ng:0.8.6. Please help me understand the issue and share any code snippet if any. Am opening db connection like below:

Copy code

fun getDBConn(): Database? {
    if (DBConnection == null) {
      synchronized(this) {
        if (DBConnection == null) {
          try {
            DBConnection = DataBaseConnection.connectWithDb(
              Config.DB_URL,
              Config.DB_DRIVER,
              Config.DB_USERNAME,
              Config.DB_PASSWORD
            )
          } catch (e: Exception) {
            println("error while connecting to db $e")
            return null
          }
        }
      }
    }
    return DBConnection
  }

Kuba Petržílka

02/15/2021, 8:37 AM

Hi everyone, I have a question: For a clomun defined as

timestamptz

in postgres I used

timstamp(...)

(

Column<Instant>

from Java Time API extension for Exposed) and when I persist a record it uses my current timezone, but I would like to get it persisted in GMT so that I dont have to do that math when comparing the values while looking into the DB (I want

2021-02-14 15:06:07.66052+00

instead of

2021-02-14 16:06:07.66052+01

) how do I enforce this?

Kuba Petržílka

02/16/2021, 2:07 PM

I m facing the same issue like this one: https://stackoverflow.com/questions/65002107/how-can-i-call-a-sequence-nextval-using-kotlin-exposed Is there a way how to get nextval from sequence without making it a part of some other query?

Endre Deak

02/17/2021, 5:06 PM

Is there a nice and easy way to go through a query and make sure there are no duplicated table names in there?

Mark

02/17/2021, 7:53 PM

Hey, using Dao is it possible to have a

Entity#new

that does

INSERT IGNORE INTO

instead of just

INSERT INTO

shengyou

02/19/2021, 8:15 AM

Hello, does anyone tried to connect to SQLite in-memory storage using Exposed? I tried this but doesn’t works.

Copy code

Database.connect(
    url = "jdbc:sqlite::memory:",
    driver = "org.sqlite.JDBC"
)

TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE

Follow the wiki and use

Database.connect(“jdbc:sqlite:file:test?mode=memory&cache=shared”, “org.sqlite.JDBC”)

and doesn’t work either. Not sure what I missing? Any suggestions are appreciated. Thanks!

Adam

02/19/2021, 1:04 PM

I’m confused at how exposed handles

LocalDate

(via the

JavaLocalDateColumnType

class).

LocalDate

does not contain a timezone, but

JavaLocalDateColumnType

converts the

LocalDate

to a

java.sql.Date

object using the local timezone. This means that, if my timezone is UTC+n, saving a

LocalDate

of, for example

1991-01-01

will put

1990-12-31

in the DB. Have I misunderstood or overlooked something? This behaviour doesn’t seem to be correct. SQL `DATE`s are timezone free, so are

LocalDate

s so there shouldn’t be any timezone conversion happening.

Daniel Skogquist Åborg

02/19/2021, 1:35 PM

Does anyone know how I specify a tinyint(1) column to come out as an integer value using MySQL? Casting to an IntegerColumnType generates a SQL error.

Adam Crane

02/23/2021, 7:40 PM

Hi all, does anyone know how I could use Exposed to model a routine/function in MS SQL that takes in a varchar and returns a table? Ideally, I'd love to use the routine in a join statement. Worst case I'll use custom SQL, but was hoping there was a better way.

Luca Sas

03/01/2021, 3:49 PM

hi, i was wondering if there is a way to automatically generate the entity classes. Seems a bit redundant to me to have to type the same copy-pasta twice even if its not a big deal.

pjagielski

03/03/2021, 8:57 AM

hi, I'm not following the channel, any plans for moving to maven central regarding bintray shutdown? there is an issue https://github.com/JetBrains/Exposed/issues/1160 but rather quiet there

Endre Deak

03/03/2021, 10:29 PM

Is there any way to get a list of the tables along with their aliases participating in a

Join

? I saw

Join.joinParts

but that’s not accessible from outside - looks like it’s not 100% prepared for a use-case where the same table can be joined with a different alias.

Robert

03/05/2021, 4:59 PM

Hi. I understand it isn’t a very specific question, and maybe not the right place, but gonna try it anyway: should I prefer Jetbrains Exposed (https://github.com/JetBrains/Exposed) or SQL delight (https://github.com/cashapp/sqldelight)? And especially, why.

solidogen

03/05/2021, 9:29 PM

hi, can I store kotlinx.datetime Instant as a table?

solidogen

03/06/2021, 2:17 PM

So I need something like this to let exposed validate it's own scheme against itself spring.jpa.hibernate.ddl-auto=validate

Ray

03/07/2021, 7:44 PM

Hello i do hope i can ask questions here. I'm currently facing an issue using exposed batchInsert function i keep getting the error

java.util.NoSuchElementException: List is empty.

and i'm pretty certain the list is not empty. Also please note l'm relatively new to working with ktor/exposed. I'll really appreciate any help cos it's really difficult to find helpful post or video tutorials on ktor/exposed.

Copy code

var statement: InsertStatement<Number>? = null
        var addOnStatement: List<ResultRow>?
        var foodSizeStatement: List<ResultRow>?
        var foodAddOns = emptyList<com.df.backend.model.FoodAddOn>()
        var foodSizes = emptyList<com.df.backend.model.FoodSize>()

        dbQuery {
            statement = Foods.insert { food ->
                food[name] = foodRequest.name
                food[description] = foodRequest.description
                food[category] = foodRequest.category
                food[foodImage] = foodRequest.foodImage
                food[createdAt] = foodRequest.createdAt
                food[updatedAt] = foodRequest.updatedAt
            }

            val resultRow = statement?.resultedValues?.get(0)
            println("Food count ${statement?.resultedValues?.size}")
            println("Addon List ${foodRequest.foodAddOns.size}")

            addOnStatement = FoodAddOn.batchInsert(foodRequest.foodAddOns) { addOn ->
                this[FoodAddOn.name] = addOn.name
                this[FoodAddOn.addOnPrice] = addOn.addOnPrice
                this[FoodAddOn.foodAddOnImage] = addOn.foodAddOnImage
                this[FoodAddOn.foodId] = resultRow?.get(Foods.foodId)!!
            }

            foodAddOns = rowToAddOns(addOnStatement!!)

            foodSizeStatement = FoodSize.batchInsert(foodRequest.foodSizes) { foodSize ->
                this[FoodSize.size] = foodSize.size
                this[FoodSize.sizePrice] = foodSize.sizePrice.toInt()
                this[FoodSize.foodId] = resultRow?.get(Foods.foodId)!!
            }

            foodSizes = rowToSizes(foodSizeStatement!!)
        }
        return rowToFood(statement?.resultedValues?.get(0),foodAddOns, foodSizes )

Rodrigo Felizola Medeiros

03/24/2021, 9:28 PM

exist a way to save the data class without need to set one by one?

John Pena

03/26/2021, 2:44 PM

when i call this code, i get an

IllegalStateException: No transaction in context

. anyone know what i’m doing wrong here?

Copy code

val breakdownDimensions = transaction {
                Dimension.find {
                    Dimensions.id inList breakdownIds
                }
            }

            for (dimension in breakdownDimensions) {
                println(dimension.id)
            }

phobe

03/28/2021, 1:20 AM

Hi, i’m trying to get the value of a key (when using the DAO API) without having an extra select to fetch the referenced object. There should be no need for that but can’t seem to find an answer

Marcel Overdijk

03/31/2021, 12:24 PM

Hi, I'm wondering if #exposed could also be used as a SQL generator (without interacting with the database itself). In my case I have data in various CSV files for which I need to generate a *.sql script with DDL and insert statements. The generated *.sql script can then be used by others to import it in their database. Now

SchemaUtils

has a

createStatements

fun that could do the DDL part, but I cannot find anything to simply generate the SQL for inserts.

👀 1

Wishnuprathikantam

04/01/2021, 11:30 AM

Exposed supports Jsonb types with postgres database?

APXEOLOG

04/02/2021, 10:55 AM

Hello, I'm using SQL DSL and I want to perform optional WHERE condition building. Is there any example for it?

tapac

04/02/2021, 11:29 AM

Hello everyone, Exposed 0.30.1 was released and now it's available on Maven Central insted of jcenter (read blog post for details). Also, there are UNION support and few bug fixes and performance improvements in the release. Please check the change log for details.

K 2

🦜 8

WildOne (Yuri)

04/02/2021, 12:19 PM

Hi there. Can anyone help me integrate Exposed and TornadoFX properly? I'm still pretty new to Kotlin/Java in general I have this code:

Copy code

val agents: ObservableList<AgentModel> by lazy {
        transaction(db) {
            addLogger(StdOutSqlLogger)
            AgentEntity.all().map {
                AgentModel().apply {
                    item = it
                }
            }.toObservable()
        }
    }

yet it seems like I can't get the UI to update when the DB changes. Do I need to update the agents list manually?

WildOne (Yuri)

04/03/2021, 6:13 AM

https://gist.github.com/wildy/16415fa756d2073a2d3f8e8170646888 Seems like I got hit by a bug where Exposed can't find the table if using a newer H2 version that defaults to being case-sensitive. But applying the suggested workaround doesn't fix it. Where did I screw up this time? 🙂 (and excuse me, I've been sitting here for um... almost 16 hours straight, redid almost everything but now can't get it to work because of the db)