通常,我看到有人问有关使用JPA进行查询的问题。 通常,为了回答此类问题,提供了几个链接,以尝试找到该问题的解决方案。
直到今天,我仍无法找到一篇博客文章,其中汇集了有关使用JPA进行查询的良好主题,有关性能/利用率的技巧,要下载的源代码……
今天我们将看到:
- 模型类和将生成数据库数据的类
- 查找方法; 使用getReference方法可获得更好的性能,并使用log4j在控制台中显示查询参数
- JPQL:具有简单参数或对象的查询,联接,排序依据,在关系中导航
- JPQL:功能:AVG,COUNT,MAX,MIN,TRIM,SUM,UPPER,LOWER,MOD,LENGHT,SQRT; 使用HAVING,GROUP BY
- JPQL:筛选条件:LIKE,IN,DISTINCT,EMPTY,BETWEEN,NULL,MEMBER OF,EXISTS(子查询),ANY,ALL,SOME,CONCAT,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,LOCATE,SIZE,SUBSTRING
- JPA:NamedQuery,使用日期查询,有关getSingleResult方法的警告
- JPA:NativeQuery,名为NativeQuery
- JPA:复杂的本地查询
- JPA:使用EJB优化查询
- JPA:分页
- JPA:数据库提示
- JPA:通过查询创建对象
- JPQL:批量更新和删除
- JPA:条件
您将看到在每个主类中,我们将调用方法“ CodeGenerator.generateData() ”。 此类方法仅在数据库中创建数据; 有了这些数据,我们的查询将找到正确的结果。
在这篇文章的最后一页中,您会找到下载该文章源代码的链接。
在本文中,我们将使用带有Hibernate作为提供程序的JPA 2.0。 该数据库将是HSQLDB,并将附加到该项目。 您可以下载源代码并运行项目,而无需任何其他配置。 我们不会谈论如何设置HSQLDB,因为本文的重点是如何查询数据库的数据。
这篇文章在某些方面不会使用开发的最佳实践。 这篇文章的重点是展示JPA查询的工作方式。
模型类和将生成数据库数据的类
package com.model;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
@Entity
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;
private String name;
private int age;
public Person() {
}
public Person(String name, int age) {
this.name = name;
this.age = age;
}
@OneToMany(mappedBy = 'person', cascade = CascadeType.ALL)
private List<Dog> dogs;
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name='address_id')
private Address address;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public List<Dog> getDogs() {
if (dogs == null) {
dogs = new ArrayList<Dog>();
}
return dogs;
}
public void setDogs(List<Dog> dogs) {
this.dogs = dogs;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
@Override
public int hashCode() {
return getId();
}
@Override
public boolean equals(Object obj) {
if (obj instanceof Person) {
Person person = (Person) obj;
return person.getId() == getId();
}
return false;
}
}
package com.model;
import java.util.Date;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
@Entity
public class Dog {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;
private String name;
private double weight;
@Temporal(TemporalType.TIMESTAMP)
private Date dateOfBirth;
public Dog() {
}
public Dog(String name, double weight, Date dateOfBirth) {
this.name = name;
this.weight = weight;
this.dateOfBirth = dateOfBirth;
}
public static void main(String[] args) {
System.out.println(new Date());
}
@ManyToOne
private Person person;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getWeight() {
return weight;
}
public void setWeight(double weight) {
this.weight = weight;
}
public Date getDateOfBirth() {
return dateOfBirth;
}
public void setDateOfBirth(Date dateOfBirth) {
this.dateOfBirth = dateOfBirth;
}
public Person getPerson() {
return person;
}
public void setPerson(Person person) {
this.person = person;
}
@Override
public int hashCode() {
return getId();
}
@Override
public boolean equals(Object obj) {
if (obj instanceof Dog) {
Dog dog = (Dog) obj;
return dog.getId() == getId();
}
return false;
}
}
package com.model;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
@Entity
public class Address {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;
private String streetName;
private int houseNumber;
public Address() {
}
public Address(String streetName, int houseNumber) {
this.streetName = streetName;
this.houseNumber = houseNumber;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getStreetName() {
return streetName;
}
public void setStreetName(String streetName) {
this.streetName = streetName;
}
public int getHouseNumber() {
return houseNumber;
}
public void setHouseNumber(int houseNumber) {
this.houseNumber = houseNumber;
}
@Override
public int hashCode() {
return getId();
}
@Override
public boolean equals(Object obj) {
if (obj instanceof Address) {
Address address = (Address) obj;
return address.getId() == getId();
}
return false;
}
}
我们获得了一些具有单向和双向关系的基本类。 这些关系将帮助我们处理将要执行的所有类型的查询。
为了生成数据库数据,我们具有以下类:
package com.main;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import com.model.Address;
import com.model.Dog;
import com.model.Person;
public class CodeGenerator {
private static EntityManagerFactory emf;
private static EntityManager em;
public static final String PERSON01_NAME = 'John';
public static final String PERSON02_NAME = 'Mary';
public static final String PERSON03_NAME = 'Anna';
public static final String PERSON04_NAME = 'Joseph';
public static final String PERSON05_NAME = 'Mark';
public static final String PERSON06_NAME = 'I will not have any relationship';
public static void startConnection() {
emf = Persistence.createEntityManagerFactory('JpaQuery');
em = emf.createEntityManager();
em.getTransaction().begin();
}
public static void closeConnection() {
em.getTransaction().commit();
emf.close();
}
public static void generateData() {
int year = 1995;
int month = 1;
int day = 10;
Dog dog01 = new Dog('Yellow', 3.5d, createNewDate(day, month, year));
Dog dog02 = new Dog('Brown', 8.5d, createNewDate(++day, ++month, ++year));
Dog dog03 = new Dog('Dark', 15.5d, createNewDate(++day, ++month, ++year));
Dog dog04 = new Dog('Kaka', 4.3d, createNewDate(++day, ++month, ++year));
Dog dog05 = new Dog('Pepe', 8.2d, createNewDate(++day, ++month, ++year));
Dog dog06 = new Dog('Casillas', 6.1d, createNewDate(++day, ++month, ++year));
Dog dog07 = new Dog('Fish', 6.7d, createNewDate(++day, ++month, ++year));
Dog dog08 = new Dog('Lion', 3.1d, createNewDate(++day, ++month, ++year));
Dog dog09 = new Dog('Cat', 5.5d, createNewDate(++day, ++month, ++year));
Dog dog10 = new Dog('Java', 21.7d, createNewDate(++day, ++month, ++year));
Dog dog11 = new Dog('JSF', 23.65d, createNewDate(++day, ++month, ++year));
Dog dog12 = new Dog('VRaptor', 24.0d, createNewDate(++day, ++month, ++year));
Dog dog13 = new Dog('Ferrari', 3.7d, createNewDate(++day, ++month, ++year));
Dog dog14 = new Dog('Porshe', 1.33d, createNewDate(++day, ++month, ++year));
Dog dog15 = new Dog('Bike', 4.44d, createNewDate(++day, ++month, ++year));
Dog dog16 = new Dog('Rambo', 5.44d, createNewDate(++day, ++month, 2015));
Dog dog17 = new Dog('Terminator', 3.88d, createNewDate(++day, ++month, 2016));
Dog dog18 = new Dog('John McClan', 3.88d, createNewDate(++day, ++month, 2016));
Person person01 = new Person(PERSON01_NAME, 33);
person01.getDogs().add(dog01);
person01.getDogs().add(dog02);
person01.getDogs().add(dog03);
person01.setAddress(new Address('Street A', 30));
dog01.setPerson(person01);
dog02.setPerson(person01);
dog03.setPerson(person01);
Person person02 = new Person(PERSON02_NAME, 27);
person02.getDogs().add(dog04);
person02.getDogs().add(dog05);
person02.getDogs().add(dog06);
person02.setAddress(new Address('Street B', 60));
dog04.setPerson(person02);
dog05.setPerson(person02);
dog06.setPerson(person02);
Person person03 = new Person(PERSON03_NAME, 7);
person03.getDogs().add(dog07);
person03.getDogs().add(dog08);
person03.getDogs().add(dog09);
person03.setAddress(new Address('Street B', 90));
dog07.setPerson(person03);
dog08.setPerson(person03);
dog09.setPerson(person03);
Person person04 = new Person(PERSON04_NAME, 43);
person04.getDogs().add(dog10);
person04.getDogs().add(dog11);
person04.getDogs().add(dog12);
person04.setAddress(new Address('Street C', 120));
dog10.setPerson(person04);
dog11.setPerson(person04);
dog12.setPerson(person04);
Person person05 = new Person(PERSON05_NAME, 70);
person05.getDogs().add(dog13);
person05.getDogs().add(dog14);
person05.getDogs().add(dog15);
person05.getDogs().add(dog16);
person05.setAddress(new Address('Street D', 150));
dog13.setPerson(person05);
dog14.setPerson(person05);
dog15.setPerson(person05);
dog16.setPerson(person05);
Person person06 = new Person(PERSON06_NAME, 45);
em.persist(person01);
em.persist(person02);
em.persist(person03);
em.persist(person04);
em.persist(person05);
em.persist(person06);
em.persist(dog17);
em.persist(dog18);
em.flush();
}
private static Date createNewDate(int day, int month, int year) {
SimpleDateFormat formatter = new SimpleDateFormat('dd/MM/yyyy');
try {
return formatter.parse('' + day + '/' + month + '/' + year);
} catch (ParseException e) {
e.printStackTrace();
return null;
}
}
public static EntityManager getEntityManager() {
return em;
}
}
查找方法; 使用getReference方法可获得更好的性能,并使用log4j在控制台中显示查询参数
通常在我们对数据库执行某些更改(例如更新某些对象属性,关系或删除它)之前调用find方法。
在下面,您将找到使用find方法的代码:
package com.main;
import javax.persistence.EntityManager;
import com.model.Address;
import com.model.Person;
public class Page03 {
public static void main(String[] args) {
CodeGenerator.startConnection();
CodeGenerator.generateData();
EntityManager em = CodeGenerator.getEntityManager();
Person person = em.find(Person.class, 1);
int addressId = 2;
// usually we send an id or a detached object from the view
setAddressToOtherPerson(em, person, addressId);
int personId = 4;
// usually we send an id or a detached object from the view
deletePerson(em, personId);
CodeGenerator.closeConnection();
}
private static void setAddressToOtherPerson(EntityManager em, Person person, int addressId) {
Address address = em.find(Address.class, addressId);
person.setAddress(address);
em.merge(person);
em.flush();
}
private static void deletePerson(EntityManager em, int personId) {
Person savedPerson = em.find(Person.class, personId);
em.remove(savedPerson);
em.flush();
}
}
注意,方法“ setAddressToOtherPerson ”和“ deletePerson ”仅使用find方法来更新引用或删除对象。
find()方法具有优化的查询功能,该功能将在持久性上下文中搜索对象,如果找不到该对象,它将查询数据库以带来数据。 如果您获得了以EAGER注释的关系(例如:“ @OneToMany(fetch = FetchType.EAGER) ”),则find方法将从数据库中带走这些对象。 注意,对于诸如删除参考更新之类的简单任务,无需从数据库中获取所有这些数据。
EntityManager具有帮助这些简单任务的特定方法。 EntityManager将执行一个简单的查询,例如“ 从Person p中选择id,其中p.id =:personId ”。 我们将有一个更快,更小的查询。
在下面,您可以看到我们将如何使用getReference:
package com.main;
import javax.persistence.EntityManager;
import com.model.Address;
import com.model.Person;
public class Page03 {
public static void main(String[] args) {
CodeGenerator.startConnection();
CodeGenerator.generateData();
EntityManager em = CodeGenerator.getEntityManager();
Person person = em.find(Person.class, 1);
int addressId = 2;
// usually we send an id or a detached object from the view
setAddressToOtherPerson(em, person, addressId);
int personId = 4;
// usually we send an id or a detached object from the view
deletePerson(em, personId);
CodeGenerator.closeConnection();
}
private static void setAddressToOtherPerson(EntityManager em, Person person, int addressId) {
Address address = em.getReference(Address.class, addressId);
person.setAddress(address);
em.merge(person);
em.flush();
System.out.println('Merged');
}
private static void deletePerson(EntityManager em, int personId) {
// usually is find or merge
Person savedPerson = em.getReference(Person.class, personId);
em.remove(savedPerson);
em.flush();
System.out.println('Deleted');
}
}
使用“ getReference ”方法,您将仅查询对象ID,将节省一些数据库流量。
在下面,您将找到在控制台中显示JPA查询参数所需的lo4j.properties配置。 通常,当我们使用Hibernate调用查询时,Hibernate将使用“?”格式化查询。 而不是使用实际价值。 使用下面的代码,您将能够看到查询参数:
# Direct log messages to stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
# Root logger option
log4j.rootLogger=ERROR, stdout
# Hibernate logging options (INFO only shows startup messages)
log4j.logger.org.hibernate=ERROR
# Log JDBC bind parameter runtime arguments
log4j.logger.org.hibernate.type=TRACE
如果要停用日志,则只需在lo4j.properties的最后一行用#符号注释,并将“ persistence.xml”中的show_log配置设置为false。
JPQL:具有简单参数或对象的查询,联接,排序依据,在关系中导航
要进行基本查询,您只需要运行以下命令:“从Dog d中选择d”。 您始终需要牢记的一件事是:为了执行这种查询,我们使用JPQL而不是常规SQL 。
使用JPQL的优点是,它与SQL非常相似且可移植。 您可以在每个数据库中使用相同的查询而不会出现问题。
切勿将查询与字符串连接。 如果您这样查询:“从Person p的p中选择p,其中p.name” + person.getName(),则可以确保黑客会喜欢它。 他们使用这种代码进行名为“ SQL注入”(或JPQL注入)的攻击。 避免这种攻击的方法是在您的查询中添加参数,就像我们将在下面看到的那样。
您将看到以下几种执行查询的方法:
package com.main;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import com.model.Dog;
import com.model.Person;
public class Page04 {
public static void main(String[] args) {
CodeGenerator.startConnection();
CodeGenerator.generateData();
EntityManager em = CodeGenerator.getEntityManager();
List<Dog> dogs = listAllDogs(em);
for (Dog dog : dogs) {
System.out.println(dog.getName());
}
Person person03 = findPersonByName(em, CodeGenerator.PERSON03_NAME);
System.out.println(person03.getName());
Person person01 = new Person();
person01.setId(1);
Person savedPerson = findPersonByPersonObject(em, person01);
System.out.println(savedPerson.getName());
List<Dog> dogsByWeight = listAllDogsOrderingByWeight(em);
for (Dog dog : dogsByWeight) {
System.out.println(dog.getWeight());
}
String addressName = findAddressNameOfPerson(em, CodeGenerator.PERSON04_NAME);
System.out.println('Person 04 address is: ' + addressName);
Person person02 = findPersonByNameWithAllDogs(em, CodeGenerator.PERSON02_NAME);
for (Dog dog : person02.getDogs()) {
System.out.println('Person 02 Dog: ' + dog.getName());
}
Person person05 = findPersonByNameThatMayNotHaveDogs(em, CodeGenerator.PERSON06_NAME);
System.out.println('Is the list of the Dogs from the Person 05 empty? ' + person05.getDogs().size());
CodeGenerator.closeConnection();
}
/**
* Easiest way to do a query
*/
@SuppressWarnings('unchecked')
private static List<Dog> listAllDogs(EntityManager em) {
Query query = em.createQuery('select d from Dog d', Dog.class);
return query.getResultList();
}
/**
* Easiest way to do a query with parameters
*/
private static Person findPersonByName(EntityManager em, String name) {
Query query = em.createQuery('select p from Person p where name = :name', Person.class);
query.setParameter('name', name);
return (Person) query.getSingleResult();
}
/**
* Executes a query that has as parameter an object
*/
private static Person findPersonByPersonObject(EntityManager em, Person person) {
Query query = em.createQuery('select p from Person p where p = :person');
query.setParameter('person', person);
return (Person) query.getSingleResult();
}
/**
* Query that will list all dogs with an order
*/
@SuppressWarnings('unchecked')
private static List<Dog> listAllDogsOrderingByWeight(EntityManager em) {
Query query = em.createQuery('select d from Dog d order by d.weight desc', Dog.class);
return query.getResultList();
}
/**
* Query that get only a field instead a complete class object
*/
private static String findAddressNameOfPerson(EntityManager em, String name) {
Query query = em.createQuery('select p.address.streetName from Person p where p.name = :name');
query.setParameter('name', name);
return (String) query.getSingleResult();
}
/**
* Query that will fetch a lazy relationship Be carefull, with this kind of
* query only those who have the relationship will come in the result
*/
private static Person findPersonByNameWithAllDogs(EntityManager em, String name) {
Query query = em.createQuery('select p from Person p join fetch p.dogs where p.name = :name', Person.class);
query.setParameter('name', name);
return (Person) query.getSingleResult();
}
/**
* With this query will will bring results that may not have arelationship
*/
private static Person findPersonByNameThatMayNotHaveDogs(EntityManager em, String name) {
Query query = em.createQuery('select p from Person p left join fetch p.dogs where p.name = :name', Person.class);
query.setParameter('name', name);
return (Person) query.getSingleResult();
}
}
关于上面的代码:
- 每个查询都使用特定的查询文本和返回类像“ em.createQuery(“ HHH”,HHH.class) ”那样调用。 您可以定义一个返回类,例如Person.class。 Person.class参数将向JPA指示返回对象。
- 我们可以使用基本属性作为查询参数,例如“ p.name =:name ”或对象“ p =:person ”。 如果使用对象,则JPA将通过其@ID进行比较。
- 如果要订购查询,则只需要执行以下操作:“ 按d.weight desc订购 ”。 默认的订单值为asc,您无需编写。
- 关于联接,您必须注意我们使用的两种联接。 在“ findPersonByNameWithAllDogs ”方法中,我们仅使用“… Person p join fetch p.dogs …”来显示狗列表。 我们需要使用join fetch,因为狗列表使用“ lazy”属性进行了注释。 如果我们不包括join fetch并执行了诸如“ person.getDogs() ”之类的命令,则需要其他“ trip ”到数据库。 如果使用此查询查找没有狗的人,则无论数据库是否有没有狗的人,JPA都不会在数据库中找到任何数据。 如果您要执行一个查询,其中包含获取狗的集合,而有或没有狗的人则需要使用“ …人p左连接获取p.dogs… ”,就像我们在方法中所做的那样:“ findPersonByNameThatMayNotHaveDogs ”。 “ 左连接获取 ”将带来一个空狗列表的人。
JPQL:功能:AVG,COUNT,MAX,MIN,TRIM,SUM,UPPER,LOWER,MOD,LENGHT,SQRT; 使用HAVING,GROUP BY
JPQL还有很多功能可以帮助我们进行查询。 在下面您可以看到他们的描述:
- AVG –数字平均值
- COUNT –计算查询找到的记录数量
- MAX –获取列的较高值
- MIN-获取列的下限值
- TRIM –删除文本开头/结尾处的空白
- SUM –对列的所有值求和
- 大写–将所有列文本修改为大写
- LOWER-将所有列文本修改为小写
- MOD –返回列的模数
- LENGTH –返回字符串的大小
- SQRT –返回数字的平方根
在下面,您将看到如何使用这些功能:
package com.main;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import com.model.Person;
public class Page05 {
public static void main(String[] args) {
CodeGenerator.startConnection();
CodeGenerator.generateData();
EntityManager em = CodeGenerator.getEntityManager();
Number average = getPersonsAgeAverage(em);
System.out.println(average);
List<Object[]> personsFilteredByDogsWeight = getPersonsWithDogsWeightHigherThan(em, 4d);
for (Object[] objects : personsFilteredByDogsWeight) {
Person person = (Person) objects[0];
Long count = (Long) objects[1];
System.out.println('The person : ' + person.getName() + ' has ' + count + ' dogs with the weight > 4');
}
List<Object[]> dogsMinAndMaxWeightList = getDogMinAndMaxWeight(em);
Object[] dogMinAndMaxWeightResult = dogsMinAndMaxWeightList.get(0);
System.out.println('Min: ' + dogMinAndMaxWeightResult[0] + ' Max: ' + dogMinAndMaxWeightResult[1]);
Number sumOfAllAges = getTheSumOfAllAges(em);
System.out.println('All summed ages are: ' + sumOfAllAges);
String loweredCaseName = getLoweredCaseNameFromUpperCase(em, CodeGenerator.PERSON03_NAME);
System.out.println(loweredCaseName);
Number personAgeMod = getPersonAgeMode(em, CodeGenerator.PERSON05_NAME, 6);
System.out.println('Person modulus age: ' + personAgeMod);
Number personAgeSqrt = getPersonAgeSqrtUsingTrim(em, ' ' + CodeGenerator.PERSON04_NAME + ' ');
System.out.println('Person modulus age: ' + personAgeSqrt);
List<Object[]> personsByDogsAmount = getPersonByHavingDogAmountHigherThan(em, 3);
for (Object[] objects : personsByDogsAmount) {
Person person = (Person) objects[0];
Long count = (Long) objects[1];
System.out.println(person.getName() + ' has ' + count + ' dogs');
}
CodeGenerator.closeConnection();
}
/**
* Uses the AVG sql database function
*/
private static Number getPersonsAgeAverage(EntityManager em) {
Query query = em.createQuery('select avg(p.age) from Person p');
return (Number) query.getSingleResult();
}
/**
* This query will use the count database function
*
* @return List<Object[]> where object[0] is a person, object [2] is a Long
*/
@SuppressWarnings('unchecked')
private static List<Object[]> getPersonsWithDogsWeightHigherThan(EntityManager em, double weight) {
Query query = em.createQuery('select p, count(p) from Person p join p.dogs d where d.weight > :weight group by p');
query.setParameter('weight', weight);
return query.getResultList();
}
/**
* This query will use the min and max sql database function
*
* @return List<Object[]> where object[0] is the min, object [2] is the max
*/
@SuppressWarnings('unchecked')
private static List<Object[]> getDogMinAndMaxWeight(EntityManager em) {
Query query = em.createQuery('select min(weight), max(weight) from Dog');
return query.getResultList();
}
/**
* This query will use the sum sql database function
*/
private static Number getTheSumOfAllAges(EntityManager em) {
Query query = em.createQuery('select sum(p.age) from Person p');
return (Number) query.getSingleResult();
}
/**
* Method that uses the UPPER and LOWER database functions
*/
private static String getLoweredCaseNameFromUpperCase(EntityManager em, String name) {
Query query = em.createQuery('select lower(p.name) from Person p where UPPER(p.name) = :name');
query.setParameter('name', name.toUpperCase());
return (String) query.getSingleResult();
}
/**
* Method that uses the mod database function
*/
private static Number getPersonAgeMode(EntityManager em, String personName, int modBy) {
Query query = em.createQuery('select mod(p.age, :modBy) from Person p where p.name = :name');
query.setParameter('modBy', modBy);
query.setParameter('name', personName);
return (Number) query.getSingleResult();
}
/**
* Method that uses the square root of a person age using the trim function in the name
*/
private static Number getPersonAgeSqrtUsingTrim(EntityManager em, String name) {
Query query = em.createQuery('select sqrt(p.age) from Person p where p.name = trim(:name)');
query.setParameter('name', name);
return (Number) query.getSingleResult();
}
/**
* Method that uses the having comparator with count
*/
@SuppressWarnings('unchecked')
private static List<Object[]> getPersonByHavingDogAmountHigherThan(EntityManager em, long dogAmount) {
Query query = em.createQuery('select p, count(p) from Person p join p.dogs group by p.id having count(p) > :dogAmount');
query.setParameter('dogAmount', dogAmount);
return query.getResultList();
}
}
关于上面的代码:
- 在方法“ getPersonsAgeAverage ”中,我们使用“ avg”函数对年龄列值进行平均计算。
- 在“ getPersonsWithDogsWeightHigherThan ”方法中,我们使用count函数将携带人对象的狗的数量带入。 注意,我们有两个不同的结果,一个数字和一个人物对象。 这些值将位于Object []数组中。
- LOWER和UPPER函数将更改您的字符串大小写,您可以使用它来更改查询结果(在选择之后)或在where条件下。 “ getLoweredCaseNameFromUpperCase ”方法以两种方式使用LOWER和UPPER函数。
- “ getPersonAgeMode ”在单词select之后使用一个参数。 使用JPA,我们可以在查询的任何位置使用参数,您只需要在变量中添加“:”即可。 您可以多次使用相同的参数,并使用query.setParameter方法传递值。
- 在方法“ getPersonByHavingDogAmountHigherThan ”中,“ 具有 ”功能与“ 计数 ”功能一起调用。 我们可以使用“ 具有 ”功能来帮助我们过滤查询数据结果。
JPQL:筛选条件:LIKE,IN,DISTINCT,EMPTY,BETWEEN,NULL,MEMBER OF,EXISTS(子查询),ANY,ALL,SOME,CONCAT,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,LOCATE,SIZE,SUBSTRING
其中一些功能具有相同目的,但处理方式不同。
在下面您可以看到如何使用这些功能:
package com.main;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import com.model.Dog;
import com.model.Person;
public class Page06 {
public static void main(String[] args) {
CodeGenerator.startConnection();
CodeGenerator.generateData();
EntityManager em = CodeGenerator.getEntityManager();
List<Person> personByLike = getPersonByNameUsingLike(em, 'oh');
for (Person person : personByLike) {
System.out.println(person.getName());
}
List<Person> personsByAdressNumber = getPersonsByAddressNumberHigherThan(em, 90);
for (Person person : personsByAdressNumber) {
System.out.println(person.getName());
}
List<Person> personsWithoutDogs = getPersonsWithoutDogs(em);
System.out.println('Total of persons without dogs: ' + personsWithoutDogs.size());
List<Person> personsWithoutAddress = getPersonsWithoutAddress(em);
System.out.println('Total of persons without address: ' + personsWithoutAddress.size());
try {
SimpleDateFormat formatter = new SimpleDateFormat('dd/MM/yyyy');
Date startDate = formatter.parse('01/01/1996');
Date endDate = formatter.parse('01/01/1999');
List<Dog> dogsByBirth = getDogByBirthDate(em, startDate, endDate);
for (Dog dog : dogsByBirth) {
System.out.println(dog.getName() + ': ' + formatter.format(dog.getDateOfBirth()));
}
} catch (Exception e) {
e.printStackTrace();
}
Dog dog = (Dog) em.createQuery('select d from Dog d where d.id = 1', Dog.class).getSingleResult();
boolean belongsTo = isThisDogBelongingToAperson(em, dog, CodeGenerator.PERSON01_NAME);
System.out.println('Is this Dog member of Perons01? ' + belongsTo);
Person personByConcatedName = getPersonConcatingName(em, 'Ma', 'ry');
System.out.println('Found the person? ' + personByConcatedName.getName());
List<Person> personByLocate = getPersonByLocatingStringInTheName(em, 'Mary');
System.out.println('Amount of persons found by locate: ' + personByLocate.size());
String personNameBySubstring = getPersonNameBySubstring(em, CodeGenerator.PERSON06_NAME, 12, 18);
System.out.println('Name substring is: ' + personNameBySubstring);
List<Person> personsDogWeight = getPersonByDogWeightOnlyHigherThan(em, 20);
for (Person person : personsDogWeight) {
System.out.println(person.getName());
}
List<Person> distinctPersons = getDistinctPersonsByDogsWeight(em, 2d);
System.out.println('With the distinct, the result size is: ' + distinctPersons.size());
List<Person> personsWithDogsAmount = getPersonsWithDougsAmountOf(em, 4);
System.out.println('Number of persons with 4 dogs: ' + personsWithDogsAmount.size());
Number numberOfDogsByPerson = getDogAmountByPerson(em, CodeGenerator.PERSON04_NAME);
System.out.println('The dog amount is to ' + CodeGenerator.PERSON04_NAME + ': ' + numberOfDogsByPerson);
List<Dog> dogsBornedAfterToday = getDogsBornAfterToday(em);
System.out.println('The amount of dogs borned after today is: ' + dogsBornedAfterToday.size());
CodeGenerator.closeConnection();
}
/**
* This methods compares a value with LIKE
*/
@SuppressWarnings('unchecked')
private static List<Person> getPersonByNameUsingLike(EntityManager em, String name) {
Query query = em.createQuery('select p from Person p where p.name like :name');
query.setParameter('name', '%' + name + '%');
return query.getResultList();
}
/**
* This methods show several ways to do a query that checks if a part of a collection is inside another
*/
@SuppressWarnings('unchecked')
private static List<Person> getPersonsByAddressNumberHigherThan(EntityManager em, int houseNumber) {
Query query = em.createQuery('select p from Person p where p.address in (select a from Address a where a.houseNumber > :houseNumber)');
// Query query = em.createQuery('select p from Person p where (select a from Address a where a.houseNumber > :houseNumber and p.address = a) > 0');
// Query query = em.createQuery('select p from Person p where p.address = any (select a from Address a where a.houseNumber > :houseNumber)');
// Query query = em.createQuery('select p from Person p where p.address = some (select a from Address a where a.houseNumber > :houseNumber)');
// Query query = em.createQuery('select p from Person p where exists (select a from p.address a where a.houseNumber > :houseNumber)');
query.setParameter('houseNumber', houseNumber);
return query.getResultList();
}
/**
* This methods show how to check if a collection is empty
*/
@SuppressWarnings('unchecked')
private static List<Person> getPersonsWithoutDogs(EntityManager em) {
Query query = em.createQuery('select p from Person p where p.dogs is empty');
return query.getResultList();
}
/**
* This method shows two ways to check if a relationship @OneToOne is empty
*/
@SuppressWarnings('unchecked')
private static List<Person> getPersonsWithoutAddress(EntityManager em) {
Query query = em.createQuery('select p from Person p where p.address is null');
// Query query = em.createQuery('select p from Person p where p.address is empty');
return query.getResultList();
}
/**
* Method that uses the between comparation
*/
@SuppressWarnings('unchecked')
private static List<Dog> getDogByBirthDate(EntityManager em, Date startDate, Date endDate) {
Query query = em.createQuery('select d from Dog d where d.dateOfBirth between :startDate and :endDate');
query.setParameter('startDate', startDate);
query.setParameter('endDate', endDate);
return query.getResultList();
}
/**
* Method that uses the member of comparation to check if an object belogs to a collection
*/
private static boolean isThisDogBelongingToAperson(EntityManager em, Dog dog, String name) {
Query query = em.createQuery('select p from Person p where :dog member of p.dogs and p.name = :name');
query.setParameter('dog', dog);
query.setParameter('name', name);
try {
return query.getSingleResult() != null;
} catch (Exception e) {
return false;
}
}
/**
* Methods that concats Strings
*/
private static Person getPersonConcatingName(EntityManager em, String firstWord, String secondWord) {
Query query = em.createQuery('select p from Person p where p.name = concat(:firstWord, :secondWord)', Person.class);
query.setParameter('firstWord', firstWord);
query.setParameter('secondWord', secondWord);
return (Person) query.getSingleResult();
}
/**
* Method that locates a string inside another
*/
@SuppressWarnings('unchecked')
private static List<Person> getPersonByLocatingStringInTheName(EntityManager em, String valueToBeLocated) {
Query query = em.createQuery('select p from Person p where locate(p.name, :value) > 0', Person.class);
query.setParameter('value', valueToBeLocated);
return query.getResultList();
}
/**
* Methods that uses the ALL comparator
*/
@SuppressWarnings('unchecked')
private static List<Person> getPersonByDogWeightOnlyHigherThan(EntityManager em, double weight) {
Query query = em.createQuery('select p from Person p where p.dogs is not empty and :weight < all (select d.weight from p.dogs d)');
query.setParameter('weight', weight);
return query.getResultList();
}
/**
* Method that uses the distinct to remove any repetetition
*/
@SuppressWarnings('unchecked')
private static List<Person> getDistinctPersonsByDogsWeight(EntityManager em, double weight) {
Query query = em.createQuery('select distinct p from Person p join p.dogs d where d.weight > :weight');
query.setParameter('weight', weight);
return query.getResultList();
}
/**
* Method that uses the substring to get just a position of chars inside the string
*/
private static String getPersonNameBySubstring(EntityManager em, String personName, int startPosition, int endPosition) {
Query query = em.createQuery('select substring(p.name, :startPosition, :endPosition) from Person p where p.name = :personName');
query.setParameter('personName', personName);
query.setParameter('startPosition', startPosition);
query.setParameter('endPosition', endPosition);
return (String) query.getSingleResult();
}
/**
* Method that checks the size of a collection
*/
@SuppressWarnings('unchecked')
private static List<Person> getPersonsWithDougsAmountOf(EntityManager em, int dogAmount) {
Query query = em.createQuery('select p from Person p where size(p.dogs) = :dogAmount');
query.setParameter('dogAmount', dogAmount);
return query.getResultList();
}
/**
* Method that gets the size of a collection
*/
private static Number getDogAmountByPerson(EntityManager em, String personName) {
Query query = em.createQuery('select size(p.dogs) from Person p where p.name = :personName');
query.setParameter('personName', personName);
return (Number) query.getSingleResult();
}
/**
* Methods that uses the current database server date/time
*/
@SuppressWarnings('unchecked')
private static List<Dog> getDogsBornAfterToday(EntityManager em) {
Query query = em.createQuery('select d from Dog d where d.dateOfBirth > CURRENT_DATE');
return query.getResultList();
}
}
关于上面的代码:
- 您可以在查询中添加“ NOT”字样。 如果使用“ IS EMPTY ”,则将搜索没有值的集合; 如果您使用“ IS NOT EMPTY ”,则将搜索已填充的集合。
- “ getPersonsByAddressNumberHigherThan ”显示了如何使用不同的功能执行相同的查询。 所有带注释的命令行将带来相同的结果。 In / Any / Some / Exists具有紧密的语法。 根据Pro EJB3的书,“ Some ”是“ Any”的别名。
- 比较器“ IS EMPTY ”可用于检查集合(例如@OneToMany)或关系类(例如@OneToOne)。 “ IS NULL ”比较器无法检查集合,但是您可以使用它来检查非集合属性(例如@OneToOne)。
- “ MEMBER OF ”比较器将检查给定参数是否属于集合。
- “ CONCAT”功能可以用作条件比较器或查询结果。 在上面的代码中,它只是用作比较器,但您可以像这样使用它:“从Person p中选择concat(firstName,lastName)”
- 在“ getPersonByDogWeightOnlyHigherThan ”方法中,我们使用ALL运算符。 仅当条件的所有项目(“ :weight> ALL” )均返回true时,此运算符才返回true。 在这种方法中,只有当所有狗的体重都大于“:weight”时,它才会返回true;如果只有一只狗的体重值较小,则比较器将返回false。 您必须知道, 如果列表为空,则比较器将返回true 。 为了避免这种行为,您需要像方法中那样检查列表是否为空:“ p.dogs不为空 ”。
- “ distinct ”功能将删除重复的对象。 在方法“ getDistinctPersonsByDogsWeight ”中,“ distinct ”功能将删除重复的人。
- “ SUBSTRING ”函数从给定的字符串中提取一个值。 您将设置将从原始值中提取的值的开始和结束。 您也可以将此功能用作比较器。
- “ SIZE ”函数将返回集合中的元素数量。 您可以用作比较器或获取值。
- 在上面的代码中,我们使用“ CURRENTE_DATE ”函数比较日期,您也可以使用“ CURRENT_TIME,CURRENT_TIMESTAMP ”。 JPA规范指出,当前日期函数只能用作比较器。 JPA目前尚不支持任何功能来检索数据库的当前日期,因为这种功能不是数据库可移植的( 4.6.16函数表达式– JSR-000220 Enterprise JavaBeans 3.0 Final Release(持久性) )。 如果要查询数据库日期,则可以使用NativeQuery来获取此值。
- 我必须始终记住,您不能在集合内部导航。 您不能执行以下命令:“ person.dogs.name”。 您可以使用以下命令访问狗的名字: 从Person中选择p,然后取入p.dogs d,其中d.name ='' 。
继续进行本系列的第二部分 。
翻译自: https://www.javacodegeeks.com/2012/07/ultimate-jpa-queries-and-tips-list-part.html