Help in using JOOQ for sql generation

[Mohan]

import org.jooq.*;
import org.jooq.conf.RenderNameStyle;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;

import static org.jooq.impl.DSL.*;

public class JooqExamples {
    private static Settings settings = new Settings()
            .withRenderNameStyle(RenderNameStyle.AS_IS).withRenderFormatted(true);
    private static DSLContext dsl = DSL.using(SQLDialect.DEFAULT, settings);

    public static void main(String[] args) {
        Long empid = 1234l;
        Field<?> employeeStatus =
               // Excepting here it should generate ?
                when(field("emp.mgr_level").isNotNull(), "MANAGER")
                        .otherwise("employee").as("employee_status");
        SelectConditionStep<? extends Record1<?>> select = dsl
                .select(employeeStatus)
                .from(table("employee").as("emp"))
                // how to bind the variable
                .where(field("emp.id").eq(empid));
        System.out.println(select.getSQL());
    }
}

Above code generates the below sql

select case when emp.mgr_level is not null then ?

            else ?

       end employee_status

from employee emp

where emp.id = ?

I have the following questions

1. In the case statements, I was expecting to get the String values
2. How to bind the empId if I want to us named parameters?

[Lukas Eder]

Hi Mohan,

Yes, Query.getSQL() generates the SQL string as it would have been sent to the JDBC driver. If you want to inline your bind variables, there are a variety of options:

https://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters

If you run the query with jOOQ (e.g. by calling ResultQuery.fetch()), the bind variables will be bound to the prepared statement automatically for you. If you want to run the query with something else than jOOQ (e.g. JDBC, Spring JdbcTemplate, JPA), you can extract the bind variables using Query.getBindValues()

I hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mohan]

Hi Lukas,

Thanks for the reply. 

Inlined-parameters solved the issue of binding values in line. 

But still, I need help in the generating the query with named parameters

Below is my code and I am trying to use param. Please help me what I am missing here.

import org.jooq.*;
import org.jooq.conf.RenderNameStyle;
import org.jooq.conf.Settings;

import org.jooq.conf.StatementType;

import org.jooq.impl.DSL;

import static org.jooq.impl.DSL.*;

/**
 * Created by mohan on 6/6/2017.
 */

public class JooqExamples {
    private static Settings settings = new Settings()
            .withRenderNameStyle(RenderNameStyle.AS_IS).withRenderFormatted(true);

            //.withStatementType(StatementType.STATIC_STATEMENT);

    private static DSLContext dsl = DSL.using(SQLDialect.DEFAULT, settings);

    public static void main(String[] args) {
        Long empid = 1234l;
        Field<?> employeeStatus =

                when(field("emp.mgr_level").isNotNull(), inline("MANAGER"))
                        .otherwise(inline("employee")).as("employee_status");

        SelectConditionStep<? extends Record1<?>> select = dsl
                .select(employeeStatus)

                .from(table(name("employee")).as("emp"))
                .where(field("emp.id").eq(param("empId",empid)));
        System.out.println(select.getSQL());
        System.out.println(select.getBindValues());
    }
}

It generated the sql as below

select case when emp.mgr_level is not null then 'MANAGER'

            else 'employee'

       end employee_status

from employee emp

where emp.id = ?

[1234]

I was excepting as below

select case when emp.mgr_level is not null then 'MANAGER'

            else 'employee'

       end employee_status

from employee emp

where emp.id = :empId

Please provide the help what I am missing here.

Regards,

Mohan

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Use this Query.getSQL() overload:

https://www.jooq.org/javadoc/latest/org/jooq/Query.html#getSQL-org.jooq.conf.ParamType-

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.