WHERE clause

The WHERE clause can be used in SELECT, UPDATE and DELETE statements. Using the WHERE operator you specify the condition to identify the exact records to be selected / updated / deleted. 

You specify a condition using the comparison operators: 

Comparison operation

SQL operator

QueryDSL operator

Equal

=

eq()

Not equal

NOT

ne()

Greater than

>

gt()

Greater or equal

>=

goe()

Less than

<

lt()

Less or equal

<=

loe()

Using WHERE clause in SELECT query

In the folllowing example, we fetch the name, country and profit fields from the table factories where profit is greater than 1000:

SQL

SELECT name, country, profit FROM factories f WHERE f.profit > 1000;

QueryDSL

selectFrom(factories)
    .where(factories.profit.goe(1000))
    .list(factories.number, factories.country, factories.profit);

Combining clauses

You can combine several number of conditions using AND or OR operators. 

In the next example, we added one more clause. We fetch data for the factories with profit greater than 1000 located in France only:

SQL

SELECT name, country, profit FROM factories f WHERE f.profit > 1000 AND country = 'France';

QueryDSL

selectFrom(factories)
    .where(factories.profit.goe(1000).and(factories.country.eq("France")))
    .list(factories.number, factories.country, factories.profit);


You can form complex conditions by combining AND and OR operators. To combine several clauses, use parenthesis. The next example demonstrates both AND or OR operators. Here we fetch all profitable factories from Netherlands, Belgium, and Luxembourg. 

Let's select data from all table columns. To do this, we write SELECT * in SQL, and list() in QueryDSL.

SQL

SELECT * FROM factories f WHERE f.profit > 1000 AND (country = 'Netherlands' OR country = 'Belgium' OR country = 'Luxembourg');

QueryDSL

selectFrom(factories)
    .where(
factories.profit.goe(1000).and(factories.country.eq("Netherlands").or(factories.country.eq("Belgium")).or(factories.country.eq("Luxembourg")))
   .list();


Similarly, you use WHERE clause in UPDATE and DELETE statements.