1.//queryDSL查询返回一个对象
QCustomer customer = ;
JPAQuery query = newJPAQuery(entityManager);
Customer bob = (customer).where(("Bob")).uniqueResult(customer);
2.//
QCustomer customer = ;
QCompany company = ;
(customer, company);
(customer).where(("Bob"), ("Wilson"));
(customer).where(("Bob").and(("Wilson")));
//The native JPQL version of the query would be
from Customer as customer where = "Bob" and = "Wilson";
(customer).where(("Bob").or(("Wilson")));
3.//Using joins
QCat cat = ;
QCat mate = newQCat("mate");
QCate kitten = newQCat("kitten");
(cat).innerJoin(, mate).leftJoin(, kitten).list(cat);
from Cat as cat inner join as mate left outer join as kitten;
(cat).leftJoin(, kitten).on((10.0)).list(cat);
from Cat as cat left join as kitten on > 10.0
4.//Ordering
QCustomer customer = ;
(customer).orderBy((), ()).list(customer);
from Customer as customer order by asc, desc
5.//Grouping
(customer).groupBy().list();
select from Customer as customer group by
6.//Delete clauses
QCustomer customer = ;
// delete all customers
new JPADeleteClause(entityManager, customer).execute();
// delete all customers with a level less than 3
new JPAeDeleteClause(entityManager, customer).where((3)).execute();
7.//Update clauses
QCustomer customer = ;
// rename customers named Bob to Bobby
new JPAUpdateClause(session, customer).where(("Bob")).set(, "Bobby").execute();
8.//Subqueries
QDepartment department = ;
QDepartment d = new QDepartment("d");
(department).where(().eq(new JPASubQuery().from(d).unique(().max())
)).list(department);
QEmployee employee = ;
QEmployee e = newQEmployee("e");
(employee).where((
new JPASubQuery().from(, e).where(()).unique(())
)).list(employee);
9.//Using Native SQL in JPA queries
/*Maven配置
<plugin>
<groupId></groupId>
<artifactId>querydsl-maven-plugin</artifactId>
<version>${}</version>
<executions>
<execution>
<goals>
<goal>export</goal>
</goals>
</execution>
</executions>
<configuration>
<jdbcDriver></jdbcDriver>
<jdbcUrl>jdbc:derby:target/demoDB;create=true</jdbcUrl>
<packageName></packageName>
<targetFolder>${}/target/generated-sources/java</targetFolder>
</configuration>
<dependencies>
<dependency>
<groupId></groupId>
<artifactId>derby</artifactId>
<version>${}</version>
</dependency>
</dependencies>
</plugin>
*/
//Single column query
// serialization templates
SQLTemplates templates = newDerbyTemplates();
// query types (S* for SQL, Q* for domain types)
SAnimal cat = newSAnimal("cat");
SAnimal mate = newSAnimal("mate");
QCat catEntity = ;
JPASQLQuery query = newJPASQLQuery(entityManager, templates);
List<String> names = (cat).list();
//Query multiple columns
query = newJPASQLQuery(entityManager, templates);
List<Object[]> rows = (cat).list(, );
//Query all columns
List<Object[]> rows = (cat).list(());
//Query in SQL, but project as entity
query = newJPASQLQuery(entityManager, templates);
List<Cat> cats = (cat).orderBy(()).list(catEntity);
//Query with joins
query = newJPASQLQuery(entityManager, templates);
cats = (cat).innerJoin(mate).on(()).where(("Cat"), ("Cat")).list(catEntity);
//Query and project into DTO
query = newJPASQLQuery(entityManager, templates);
List<CatDTO> catDTOs = (cat).orderBy(()).list((, , ));
10.//Ordering
QCustomer customer = ;
(customer).orderBy((), ()).list(customer);
11.//Grouping
(customer).groupBy().list();
12.//Delete clauses
QCustomer customer = ;
// delete all customers
newJDODeleteClause(pm, customer).execute();
// delete all customers with a level less than 3
newJDODeleteClause(pm, customer).where((3)).execute();
13.//Subqueries
//JDOSubQueryinstance
QDepartment department = ;
QDepartment d = newQDepartment("d");
(department)
.where(().eq(newJDOSubQuery().from(d).unique((()))
)).list(department);
//native JDO query
SELECT this FROM
WHERE () == SELECT max(()) FROM d)
QEmployee employee = ;
QEmployee e = newQEmployee("e");
(employee).where((newJDOSubQuery().from(, e)
.where(()).unique(()))).list(employee);
//native JDO query
SELECT this FROM
WHERE > (SELECT avg() FROM e WHERE == )
14.//Window functions
(employee).list(().over().partitionBy().orderBy());
15.//Insert
QSurvey survey = ;
newSQLInsertClause(conn, dialect, survey).columns(, ).values(3, "Hello").execute()
newSQLInsertClause(conn, dialect, survey).values(4, "Hello").execute();
//subquery
new SQLInsertClause(conn, dialect, survey).columns(, ).select(newSQLSubQuery().from(survey2).list((1), ))
.execute();
new SQLInsertClause(conn, dialect, survey).select(newSQLSubQuery().from(survey2).list((10), ))
.execute();
16.//Update
QSurvey survey = ;
new SQLUpdateClause(conn, dialect, survey).where(("XXX")).set(, "S").execute();
new SQLUpdateClause(conn, dialect, survey).set(, "S").execute()
17.//Delete
QSurvey survey = ;
new SQLDelecteClause(conn, dialect, survey).where(("XXX")).execute();
new SQLDelecteClause(conn, dialect, survey).execute()
18.//Batch support in DML clauses
//Update:
QSurvey survey = ;
insert(survey).values(2, "A").execute();
insert(survey).values(3, "B").execute();
SQLUpdateClause update = update(survey);
(, "AA").where(("A")).addBatch();
(, "BB").where(("B")).addBatch();
//Delete:
insert(survey).values(2, "A").execute();
insert(survey).values(3, "B").execute();
SQLDeleteClause delete = delete(survey);
(("A")).addBatch();
(("B")).addBatch();
assertEquals(2, ());
//Insert
SQLInsertClause insert = insert(survey);
(, 5).set(, "5").addBatch();
(, 6).set(, "6").addBatch();
assertEquals(2, ());
19.
//limit
(("*")).limit(10).list();
//offset
(("*")).offset(3).list();
20.//Fuzzy searches
((, "Hello")).list();
21.//Applying Lucene filters to queries
(("*")).filter(filter).list();
(("*")).distinct().list()
22.//Querying Hibernate Search
QUser user = ;
SearchQuery<User> query = newSearchQuery<User>(session, user);
List<User> list = (("Bob")).list();
23.// Querying Mongodb
/*
<dependency>
<groupId></groupId>
<artifactId>querydsl-apt</artifactId>
<version>${}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId></groupId>
<artifactId>querydsl-mongodb</artifactId>
<version>${}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.6.1</version>
</dependency>
*/
/*
<project>
<build>
<plugins>
...
<plugin>
<groupId></groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.0.9</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor></processor>
</configuration>
</execution>
</executions>
</plugin>
...
</plugins>
</build>
</project>
*/
24.//Result handling
List<Tuple> result = (employee).list(, );
for(Tuple row : result) {
("firstName "+ ());
("lastName "+ ());
}}
List<UserDTO> dtos = ((, , ));
class CustomerDTO {
@QueryProjection
public CustomerDTO(longid, String name){
...
}
}
QCustomer customer = ;
JPQLQuery query = newHibernateQuery(session);
List<CustomerDTO> dtos = (customer).list(new QCustomerDTO(, ));
QCustomer customer = ;
JPQLQuery query = new HibernateQuery(session);
List<Customer> dtos = (customer).list((, ));
List<Customer> dtos = (customer).list((, , ));
25.//Result aggregation
import .*;
Map<Integer, List<Comment>> results = (post, comment).where(()).transform(groupBy().as(list(comment)))
Map<Integer, Group> results = (post, comment).where(()).transform(groupBy().as(, set()));