jpa 查詢 列表

我們可以在Internet上找到一些JPA“如何做”，在本博客的此處，教您如何使用JPA執行多項任務。

通常，我看到有人問有關使用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;}@Overridepublic int hashCode() {return getId();}@Overridepublic 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());}@ManyToOneprivate 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;}@Overridepublic int hashCode() {return getId();}@Overridepublic 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;}@Overridepublic int hashCode() {return getId();}@Overridepublic 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 viewsetAddressToOtherPerson(em, person, addressId);int personId = 4;// usually we send an id or a detached object from the viewdeletePerson(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 viewsetAddressToOtherPerson(em, person, addressId);int personId = 4;// usually we send an id or a detached object from the viewdeletePerson(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 mergePerson 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 ”方法中，“ having ”功能與“ count ”功能一起調用。 我們可以使用“ 具有 ”功能來幫助我們過濾查詢數據結果。

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 ='' 。

繼續進行本系列的第二部分 。

參考： uaiHebert博客上來自JCG合作伙伴 Hebert Coelho的JPA查詢和技巧 。

翻譯自: https://www.javacodegeeks.com/2012/07/ultimate-jpa-queries-and-tips-list-part.html

jpa 查詢 列表

總結

以上是生活随笔為你收集整理的jpa 查询 列表_终极JPA查询和技巧列表–第1部分的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。