jooq多表查询_动态创建JOOQ查询

I need to create a JOOQ SELECT query dynamically based on the set of parameters. I dont know how to append it dynamically.

Please help

Thanks in advance.

解决方案

jOOQ has two types of APIs to construct queries.

The DSL API that allows for creating inline SQL statements in your Java code, e.g.

create.select(T.A, T.B).from(T).where(T.X.eq(3).and(T.Y.eq(5)));

The "model" API that allows for incremental SQL building. At any time, you can access the "model" API through the getQuery() method on a DSL query object

An example of what you want to do is given in the manual here:

For instance, optionally adding a join:

DSLContext create = DSL.using(configuration);

SelectQuery query = create.selectQuery();

query.addFrom(AUTHOR);

// Join books only under certain circumstances

if (join)

query.addJoin(BOOK, BOOK.AUTHOR_ID.equal(AUTHOR.ID));

Result> result = query.fetch();

Or, optinally adding conditions / predicates:

query.addConditions(BOOK.TITLE.like("%Java%"));

query.addConditions(BOOK.LANGUAGE_CD.eq("en"));

UPDATE: Given your comments, that's what you're looking for:

// Retrieve search strings from your user input (just an example)

String titleSearchString = userInput.get("TITLE");

String languageSearchString = userInput.get("LANGUAGE");

boolean lookingForTitles = titleSearchString != null;

boolean lookingForLanguages = languageSearchString != null;

// Add only those conditions that the user actually provided:

if (lookingForTitles)

query.addConditions(BOOK.TITLE.like("%" + titleSearchString + "%"));

else if (lookingForLanguages)

query.addConditions(BOOK.LANGUAGE_CD.eq(languageSearchString));

Note, you can also use the Field.compare(Comparator, Object) methods:

// Initialise your dynamic arguments

Field field = BOOK.TITLE;

Comparator comparator = Comparator.LIKE;

String value = "%" + titleSearchString + "%";

// Pass them to the field.compare() method

query.addConditions(field.compare(comparator, value));

For more info, consider the org.jooq.SelectQuery Javadoc