How to get generated typed Record from ReactiveRXQueryExecutor(s) ?

[nkss7]

Hi! I confused for this - we have generated typed Record and when we create ReactiveQueryExecutor and do some like execute(), query(), we can get in result, only raw Row. I cant do into(), fetchInto() or any other method that return TypedRecord with reactive executor or maybe i do something wrong. We pass Record type through TableImpl in selectFrom(type) block and in sync behaviour we can just fetch(), fetchOne() - that return our TypedRecord or into() but i cant do this, bacause reactive execitor's methods expects query and no one for mapping logic like fetch() or into() instead this we need to generate dao that return our generated typed pojo(its doesnt have meta database info) and no one returns our typed generated record.

[aleksandar78]

Hi @nikita-mtd,

If I've understood correctly you've tried to call fetch/fetchOne JOOQ methods on a query result insiede vertx-jooq executor?

This is possible with JOOQ and standard JDBC query execution. If you want to use it in that way you should take working/blocking verticles and deal with RDBMS in the old fashion way. BTW this approach is not recommended or better to say it's not how vert.x should be used.

In the vert.x world where we use async/reactive drivers some JOOQ features are not appliable. A closure argument passed to query/execution returns the library-specific values that can be mapped into your entity inside map methods on returned Future or Observable, depends on what was your vertx-jooq driver choice.

[maralerdene]

Hi Aleksandr,

JOOQ is not async/reactive -? I thought JOOQ is a recommended feature for vert.x and async programming.

On Mon, Dec 28, 2020 at 5:15 PM Aleksandar notifications@github.com wrote:

Hi @nikita-mtd https://github.com/nikita-mtd,

If I've understood correctly you've tried to call fetch/fetchOne JOOQ methods on a query result insiede vertx-jooq executor?

This is possible with JOOQ and standard JDBC query execution. If you want to use it in that way you should take working/blocking verticles and deal with RDBMS in the old fashion way. BTW this approach is not recommended or better to say it's not how vert.x should be used.

In the vert.x world where we use async/reactive drivers some JOOQ features are not appliable. A closure argument passed to query/execution returns the library-specific values that can be mapped into your entity inside map methods on returned Future or Observable, depends on what was your vertx-jooq driver choice.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/jklingsporn/vertx-jooq/issues/171#issuecomment-751644072, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB7LT56TYCXVJ7GUQWZJEELSXBEDRANCNFSM4VKZLPRQ .

-- Best Regards Maral-Erdene Tumursukh

[nkss7]

Thx for reply @aleksandar78 !

Yea, you are right I want to use async approach only (you have already say reasons). Buy why vertx async executor cant return typed record like standart jdbc jooq lib? We put record type while pass our TableImpl in query inside select query findOneRow { it.selectFrom(OUR_TABLE) }, but this block can return only raw row which we can put to generated pojo mapper(but not typed record =( )

[aleksandar78]

Hi Aleksandr, JOOQ is not async/reactive -? I thought JOOQ is a recommended feature for vert.x and async programming. … On Mon, Dec 28, 2020 at 5:15 PM Aleksandar @.***> wrote: Hi @nikita-mtd https://github.com/nikita-mtd, If I've understood correctly you've tried to call fetch/fetchOne JOOQ methods on a query result insiede vertx-jooq executor? This is possible with JOOQ and standard JDBC query execution. If you want to use it in that way you should take working/blocking verticles and deal with RDBMS in the old fashion way. BTW this approach is not recommended or better to say it's not how vert.x should be used. In the vert.x world where we use async/reactive drivers some JOOQ features are not appliable. A closure argument passed to query/execution returns the library-specific values that can be mapped into your entity inside map methods on returned Future or Observable, depends on what was your vertx-jooq driver choice. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#171 (comment)>, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB7LT56TYCXVJ7GUQWZJEELSXBEDRANCNFSM4VKZLPRQ . -- Best Regards Maral-Erdene Tumursukh

Hi @maralerdene,

JOOQ is not ORM or some kind of facade in front of RDMBS. It's the best library for Java (and some other JVM languages) code generation from DDL that enforces type-safe SQL queries. Nothing more, nothing less. JOOQ is based on classic Java SQL API and it's used in 99% with standard JDBC Driver. I've used JOOQ with Spring and Java EE for some time.

vertx-jooq library is a very good alternative when you want to use what is best from JOOQ with reactive Drivers and in vertx manner. @nikita-mtd Making a bridge between Vertx/ReactiveDrivers and JOOQ has its own downside/limits and one of them is missing JOOQ fetch methods as some JOOQ basic types are converted into vert.x ones. I know how much JOOQ fetch methods can be useful when mapping to entities is a goal but I think that with some custom mapping code in the map function the same result can be achieved.

[nkss7]

@aleksandar78 can i get your contacts for any questions?)

Vertx pg client has own custom reactive implementation (not RDBMS) isnt it?

[aleksandar78]

Hi @nikita-mtd.

I think that you are not the only one that needs correct explanations. As all this software is made from the contribution I think that also questions/discussions could/should be public and useful for new people coming into vertx-jooq world.

It was hard for me and my colleagues when we switch from old blocking frameworks to vertx. There are not enough resources that help people with real-life examples when they are in the beginning.

My advice for everyone should be to create an issue in the term of the proposal explaining what is hard to understand and what examples should be good for the starters to have. I think that lot of people will be ready to contribute and make this library even more popular. @jklingsporn do you have any thoughts on how could we create good documentation with showcases or still better a project with examples as help for beginners?

To answer your question @nikita-mtd: vertx-pg-client is only reactive and it was recently imported by vertx project. RDBMS is the relational database acronym and drivers could be blocking such as standard JDBC or not blocking such as vertx-pg-client.

I hope that now you have enough information to go on with your project.

[jklingsporn]

@aleksandar78 thanks for jumping in and explaining stuff about vertx-jooq. I agree that there is a lack of documentation of how to achieve certain things. We should probably add a list of How-to's in the Wiki-section of this repo which also explains what this library does and how it differs from plain jOOQ. I do also agree that any kind of question of "how can I achieve X using vertx-jooq" is valuable and should be made public for documentation for future users. @nikita-mtd as already pointed out you cannot use any of the jOOQ-methods that return a Result (like the fetch-methods do) or anything comparable as it directly would be executed via JDBC. You wrote that you already have a typed record, so maybe it helps when you create a Dao of that type and call dao.queryExecutor() for the given type instead of creating a QueryExecutor by hand?

[jymboche]

Heres how im doing it for simple cases...in this example from a chat application. A Conversation has many Participants. I want to get all CONVERSATIONS for a given user, by looking up their participant records (code in kotlin):

package com.thfy.site.messaging.repo

import com.devsouth.db.DBStore
import com.thfy.db.Tables
import com.thfy.db.tables.daos.ConversationDao
import com.thfy.db.tables.pojos.Conversation
import io.github.jklingsporn.vertx.jooq.rx.reactivepg.ReactiveRXGenericQueryExecutor
import io.reactivex.Single
import io.vertx.sqlclient.Row

class ConversationRepo(dbStore: DBStore) {

    private val qe = ReactiveRXGenericQueryExecutor(dbStore.getConfig(), dbStore.getClient())

    private val dao = ConversationDao(dbStore.getConfig(), dbStore.getClient())

    private val PARTICPANT = Tables.PARTICIPANT

    private val CONVERSATION = Tables.CONVERSATION

    /**
     * Using QueryExecutor obtain list of row
     */
    fun getUserConversations(userId: Int): Single<MutableList<Row>> {
        return qe
            .findManyRow {dsl ->
                dsl
                    .select(CONVERSATION.asterisk())
                    .from(PARTICPANT)
                    .join(CONVERSATION)
                    .on(CONVERSATION.ID.eq(PARTICPANT.CONVERSATION_ID))
                    .where(PARTICPANT.UID.eq(userId))
            }
    }

    /**
     * Using Dao
     */
    fun getUserConversationsWithDao(userId: Int): Single<MutableList<Conversation>> {
        return dao
            .queryExecutor()
            .findMany {dsl ->
                dsl
                    .select(CONVERSATION.asterisk())
                    .from(PARTICPANT)
                    .join(CONVERSATION)
                    .on(CONVERSATION.ID.eq(PARTICPANT.CONVERSATION_ID))
                    .where(PARTICPANT.UID.eq(userId))
                    .coerce(CONVERSATION)
            }
    }

}

Using the second function with dao.queryExecutor() i can coerce as Conversation. Is this how this should be done or is there a better way? A lot of times I have complex selects and use custom row mappings, but sometimes it would also be helpful to select TWO tables (conversations, participants) and convert each row into a pair/collection of Conversation, Participant...

[jklingsporn]

Option two looks good for me. When it comes to relations, this is one of the downsides of jOOQ in general. There is no easy way to achieve what you are looking for. As described in this stack overflow answer, there are some native fetch-methods that one can use with plain jOOQ. The other way is to use third party-mapping like ModelMapper or SimpleFlatMapper. Your best bet in that regard is going with option one and map from a Row to your POJO / pair of POJOs.