Creating dynamic Query using SqlBuilder in java

Question

can you help me for creating sql query in where condition

SELECT * FROM classicmodels.offices t1 INNER JOIN classicmodels.employees t0 
  ON (t1.officeCode = t0.officeCode) INNER JOIN classicmodels.customers t2 ON 
  ( (t0.employeeNumber = t2.salesRepEmployeeNumber) OR (t0.firstName = 
  t2.contactFirstName) ) WHERE (t2.creditLimit > 70000) AND (t2.creditLimit < 
  100000) OR (t0.officeCode = 6)
ORDER BY
t0.firstName ASC

I am not able to create please tell me how to create AND or OR both condition in single query

WHERE (t2.creditLimit > 70000) AND (t2.creditLimit < 100000) OR (t0.officeCode = 6)

How can create dynamic query may more then one time AND condition or may be more then one time OR condition. it's condition comes random. then i want to create query. can you help me....

Answer 1

I'm not sure if "SqlBuilder" is a specific library, because there are several libraries that have a SqlBuilder class (e.g. MyBatis), and there are other libraries that are SQL builders (e.g. jOOQ). So, I'm just going to answer this for jOOQ (I work for the vendor).

Using jOOQ

Your query could be written as such, assuming you're using the code generator:

// Aliasing is not necessary in your case, but here it is, still:
Offices t1 = OFFICES.as("t1");
Employees t0 = EMPLOYEES.as("t0");
Customers t2 = CUSTOMERS.as("t2");

Result<?> result =
ctx.select()
   .from(t1)
   .join(t0).on(t1.OFFICECODE.eq(t0.OFFICECODE))
   .join(t2).on(t0.EMPLOYEENUMBER.eq(t2.SALESREPEMPLOYEENUMBER)
            .or(t0.FIRSTNAME.eq(t2.CONTACTFIRSTNAME)))
   .where(t2.CREDITLIMIT.gt(70000))
   .and(t2.CREDITLIMIT.lt(100000))
   .or(t0.OFFICECODE.eq(6))
   .orderBy(t0.FIRSTNAME)
   .fetch();

Using some other, specific SqlBuilder

If your specific SqlBuilder cannot handle the type of predicate you've written, perhaps you can rewrite it as such:

WHERE (t2.creditLimit BETWEEN 70001 AND 99999) OR (t0.officeCode = 6)