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
On Wednesday, 7 June 2017 01:37:11 UTC-5, Lukas Eder wrote:
>
> 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
>
> 2017-06-07 3:38 GMT+02:00 Mohan <[email protected] <javascript:>>:
>
>> 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?
>>
>>
>> --
>> 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 [email protected] <javascript:>.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
