CODEWITHSUNDEEP
oracle-databasejooq
vvra
vvra

Reputation: 2912

JOOQ - query without quotes

I use JOOQ-3.1.0 to generate and execute dynamic queries for Oracle and Postgresql with Spring-4. In a scenario I have a partitioned table, which I need to query using JOOQ. I use DSL.tableByName(vblTablename); where vblTablename is the string received as a string in the query generation method, ex, vbl_default partition(p_04-Dec-14). (The vblTablename pattern differs for different databases, and is configured in the external property file). The JOOQ generates the sql, but with the double-quote around the tablename. The query and error shown below

Query

SELECT COUNT(ID) COUNT FROM "vbl_default partition(p_04-Dec-14)" 
    where (rts between timestamp '2014-12-04 00:00:00.0' and timestamp '2014-12-05 00:00:00.0' and userid in (2))

Error

ORA-00972: identifier is too long
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.
Error at Line: 4 Column: 29

Though I have set the below settings on the DefaultDSLContext

Settings settings = new Settings();
        settings.setRenderNameStyle(RenderNameStyle.AS_IS);

How do I remove the quote around the table? Any other settings have I missed?

Upvotes: 1

Views: 1373

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

The idea behind DSL.tableByName(String...) is that you provide a table ... by name :-)

What you're looking for is a plain SQL table, via DSL.table(String).

You can write:

// Assuming this import
import static org.jooq.impl.DSL.*;

DSL.using(configuration)
   .select(count(VBL_DEFAULT.ID))
   .from(table("vbl_default partition(p_04-Dec-14)"))
   .where(...);

Or by using the convenient overload SelectFromStep.from(String)

DSL.using(configuration)
   .select(count(VBL_DEFAULT.ID))
   .from("vbl_default partition(p_04-Dec-14)")
   .where(...);

More information about plain SQL in jOOQ can be obtained from this manual page:

http://www.jooq.org/doc/latest/manual/sql-building/plain-sql/

Partition support

Note that support for Oracle partitions is on the roadmap: #2775. If in the mean time you wish to use partitioned tables more often, you could also write your own function for that:

// Beware of the risk of SQL injection, though!
public <R extends Record> Table<R> partition(Table<R> table, String partition) {
    return DSL.table("{0} partition(" + partition + ")", table);
}

... and then:

DSL.using(configuration)
   .select(count(VBL_DEFAULT.ID))
   .from(partition(VBL_DEFAULT, "p_04-Dec-14"))
   .where(...);

Upvotes: 2

Related Questions