Use this Query.getSQL() overload: https://www.jooq.org/javadoc/latest/org/jooq/Query.html#getSQL-org.jooq.conf.ParamType-
2017-06-07 16:20 GMT+02:00 Mohan <[email protected]>: > 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]>: >> >>> 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).withRenderFormat >>> ted(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]. >>> 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. > -- 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.
