QueryDSL 简单的查询语句应用

1.//queryDSL查询返回一个对象
QCustomer customer = QCustomer.customer;
JPAQuery query = newJPAQuery(entityManager);
Customer bob = query.from(customer).where(customer.firstName.eq("Bob")).uniqueResult(customer);
2.//
QCustomer customer = QCustomer.customer;
QCompany company = QCompany.company;
query.from(customer, company);

query.from(customer).where(customer.firstName.eq("Bob"), customer.lastName.eq("Wilson"));

query.from(customer).where(customer.firstName.eq("Bob").and(customer.lastName.eq("Wilson")));
//The native JPQL version of the query would be
from Customer as customer where customer.firstName = "Bob" and customer.lastName = "Wilson";

query.from(customer).where(customer.firstName.eq("Bob").or(customer.lastName.eq("Wilson")));

3.//Using joins
QCat cat = QCat.cat;
QCat mate = newQCat("mate");
QCate kitten = newQCat("kitten");
query.from(cat).innerJoin(cat.mate, mate).leftJoin(cat.kittens, kitten).list(cat);

from Cat as cat inner join cat.mate as mate left outer join cat.kittens as kitten;

query.from(cat).leftJoin(cat.kittens, kitten).on(kitten.bodyWeight.gt(10.0)).list(cat);

from Cat as cat left join cat.kittens as kitten on kitten.bodyWeight > 10.0

4.//Ordering
QCustomer customer = QCustomer.customer;
query.from(customer).orderBy(customer.lastName.asc(), customer.firstName.desc()).list(customer);

from Customer as customer order by customer.lastName asc, customer.firstName desc

5.//Grouping
query.from(customer).groupBy(customer.lastName).list(customer.lastName);

select customer.lastName from Customer as customer group by customer.lastName

6.//Delete clauses
QCustomer customer = QCustomer.customer;
// delete all customers
new JPADeleteClause(entityManager, customer).execute();
// delete all customers with a level less than 3
new JPAeDeleteClause(entityManager, customer).where(customer.level.lt(3)).execute();

7.//Update clauses
QCustomer customer = QCustomer.customer;
// rename customers named Bob to Bobby
new JPAUpdateClause(session, customer).where(customer.name.eq("Bob")).set(customer.name, "Bobby").execute();

8.//Subqueries
QDepartment department = QDepartment.department;
QDepartment d = new QDepartment("d");
query.from(department).where(department.employees.size().eq(new JPASubQuery().from(d).unique(d.employees.size().max())
)).list(department);

QEmployee employee = QEmployee.employee;
QEmployee e = newQEmployee("e");
query.from(employee).where(employee.weeklyhours.gt(
new JPASubQuery().from(employee.department.employees, e).where(e.manager.eq(employee.manager)).unique(e.weeklyhours.avg())
)).list(employee);

9.//Using Native SQL in JPA queries
/*Maven配置
<plugin>
<groupId>com.mysema.querydsl</groupId>
<artifactId>querydsl-maven-plugin</artifactId>
<version>${project.version}</version>
<executions>
<execution>
<goals>
<goal>export</goal>
</goals>
</execution>
</executions>
<configuration>
<jdbcDriver>org.apache.derby.jdbc.EmbeddedDriver</jdbcDriver>
<jdbcUrl>jdbc:derby:target/demoDB;create=true</jdbcUrl>
<packageName>com.mycompany.mydomain</packageName>
<targetFolder>${project.basedir}/target/generated-sources/java</targetFolder>
</configuration>
<dependencies>
<dependency>
<groupId>org.apache.derby</groupId>
<artifactId>derby</artifactId>
<version>${derby.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 = QCat.cat;
JPASQLQuery query = newJPASQLQuery(entityManager, templates);
List<String> names = query.from(cat).list(cat.name);

//Query multiple columns
query = newJPASQLQuery(entityManager, templates);
List<Object[]> rows = query.from(cat).list(cat.id, cat.name);

//Query all columns
List<Object[]> rows = query.from(cat).list(cat.all());

//Query in SQL, but project as entity
query = newJPASQLQuery(entityManager, templates);
List<Cat> cats = query.from(cat).orderBy(cat.name.asc()).list(catEntity);

//Query with joins
query = newJPASQLQuery(entityManager, templates);
cats = query.from(cat).innerJoin(mate).on(cat.mateId.eq(mate.id)).where(cat.dtype.eq("Cat"), mate.dtype.eq("Cat")).list(catEntity);

//Query and project into DTO
query = newJPASQLQuery(entityManager, templates);
List<CatDTO> catDTOs = query.from(cat).orderBy(cat.name.asc()).list(ConstructorExpression.create(CatDTO.class, cat.id, cat.name));

10.//Ordering
QCustomer customer = QCustomer.customer;
query.from(customer).orderBy(customer.lastName.asc(), customer.firstName.desc()).list(customer);

11.//Grouping
query.from(customer).groupBy(customer.lastName).list(customer.lastName);

12.//Delete clauses
QCustomer customer = QCustomer.customer;
// delete all customers
newJDODeleteClause(pm, customer).execute();
// delete all customers with a level less than 3
newJDODeleteClause(pm, customer).where(customer.level.lt(3)).execute();

13.//Subqueries
//JDOSubQueryinstance
QDepartment department = QDepartment.department;
QDepartment d = newQDepartment("d");
query.from(department)
.where(department.employees.size().eq(newJDOSubQuery().from(d).unique(AggregationFunctions.max(d.employees.size()))
)).list(department);

//native JDO query
SELECT this FROM com.mysema.query.jdoql.models.company.Department
WHERE this.employees.size() == SELECT max(d.employees.size()) FROM com.mysema.query.jdoql.models.company.Department d)

QEmployee employee = QEmployee.employee;
QEmployee e = newQEmployee("e");
query.from(employee).where(employee.weeklyhours.gt(newJDOSubQuery().from(employee.department.employees, e)
.where(e.manager.eq(employee.manager)).unique(AggregationFunctions.avg(e.weeklyhours)))).list(employee);

//native JDO query
SELECT this FROM com.mysema.query.jdoql.models.company.Employee
WHERE this.weeklyhours > (SELECT avg(e.weeklyhours) FROM this.department.employees e WHERE e.manager == this.manager)

14.//Window functions
query.from(employee).list(SQLExpressions.rowNumber().over().partitionBy(employee.name).orderBy(employee.id));

15.//Insert
QSurvey survey = QSurvey.survey;
newSQLInsertClause(conn, dialect, survey).columns(survey.id, survey.name).values(3, "Hello").execute()

newSQLInsertClause(conn, dialect, survey).values(4, "Hello").execute();
//subquery
new SQLInsertClause(conn, dialect, survey).columns(survey.id, survey.name).select(newSQLSubQuery().from(survey2).list(survey2.id.add(1), survey2.name))
.execute();

new SQLInsertClause(conn, dialect, survey).select(newSQLSubQuery().from(survey2).list(survey2.id.add(10), survey2.name))
.execute();
16.//Update
QSurvey survey = QSurvey.survey;
new SQLUpdateClause(conn, dialect, survey).where(survey.name.eq("XXX")).set(survey.name, "S").execute();

new SQLUpdateClause(conn, dialect, survey).set(survey.name, "S").execute()

17.//Delete
QSurvey survey = QSurvey.survey;
new SQLDelecteClause(conn, dialect, survey).where(survey.name.eq("XXX")).execute();

new SQLDelecteClause(conn, dialect, survey).execute()

18.//Batch support in DML clauses
//Update:
QSurvey survey = QSurvey.survey;
insert(survey).values(2, "A").execute();
insert(survey).values(3, "B").execute();
SQLUpdateClause update = update(survey);
update.set(survey.name, "AA").where(survey.name.eq("A")).addBatch();
update.set(survey.name, "BB").where(survey.name.eq("B")).addBatch();
//Delete:
insert(survey).values(2, "A").execute();
insert(survey).values(3, "B").execute();
SQLDeleteClause delete = delete(survey);
delete.where(survey.name.eq("A")).addBatch();
delete.where(survey.name.eq("B")).addBatch();
assertEquals(2, delete.execute());
//Insert
SQLInsertClause insert = insert(survey);
insert.set(survey.id, 5).set(survey.name, "5").addBatch();
insert.set(survey.id, 6).set(survey.name, "6").addBatch();
assertEquals(2, insert.execute());

19.
//limit
query.where(doc.title.like("*")).limit(10).list();
//offset
query.where(doc.title.like("*")).offset(3).list();

20.//Fuzzy searches
query.where(LuceneExpressions.fuzzyLike(doc.title, "Hello")).list();
21.//Applying Lucene filters to queries
query.where(doc.title.like("*")).filter(filter).list();

query.where(doc.title.like("*")).distinct(doc.title).list()
22.//Querying Hibernate Search
QUser user = QUser.user;
SearchQuery<User> query = newSearchQuery<User>(session, user);
List<User> list = query.where(user.firstName.eq("Bob")).list();

23.// Querying Mongodb
/*
<dependency>
<groupId>com.mysema.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.mysema.querydsl</groupId>
<artifactId>querydsl-mongodb</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.6.1</version>
</dependency>
*/
/*
<project>
<build>
<plugins>
...
<plugin>
<groupId>com.mysema.maven</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>com.mysema.query.apt.morphia.MorphiaAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
...
</plugins>
</build>
</project>
*/

24.//Result handling

List<Tuple> result = query.from(employee).list(employee.firstName, employee.lastName);
for(Tuple row : result) {
System.out.println("firstName "+ row.get(employee.firstName));
System.out.println("lastName "+ row.get(employee.lastName));
}}

List<UserDTO> dtos = query.list(Projections.bean(UserDTO.class, user.firstName, user.lastName));

class CustomerDTO {
@QueryProjection
public  CustomerDTO(longid, String name){
...
}
}
QCustomer customer = QCustomer.customer;
JPQLQuery query = newHibernateQuery(session);
List<CustomerDTO> dtos = query.from(customer).list(new QCustomerDTO(customer.id, customer.name));

QCustomer customer = QCustomer.customer;
JPQLQuery query = new HibernateQuery(session);
List<Customer> dtos = query.from(customer).list(QCustomer.create(customer.id, customer.name));

List<Customer> dtos = query.from(customer).list(ConstructorExpression.create(Customer.class, customer.id, customer.name));

25.//Result aggregation

import staticcom.mysema.query.group.GroupBy.*;
Map<Integer, List<Comment>> results = query.from(post, comment).where(comment.post.id.eq(post.id)).transform(groupBy(post.id).as(list(comment)))

Map<Integer, Group> results = query.from(post, comment).where(comment.post.id.eq(post.id)).transform(groupBy(post.id).as(post.name, set(comment.id)));