网络营销渠道策略,青岛seo外包服务,怎么做别人网站销售的东西公证,合肥网络公司行情我们可以在Internet上找到一些JPA“如何做”#xff0c;在本博客的此处#xff0c;教您如何使用JPA执行多项任务。 通常#xff0c;我看到有人问有关使用JPA进行查询的问题。 通常#xff0c;为了回答此类问题#xff0c;提供了几个链接#xff0c;以尝试找到该问题的解决… 我们可以在Internet上找到一些JPA“如何做”在本博客的此处教您如何使用JPA执行多项任务。 通常我看到有人问有关使用JPA进行查询的问题。 通常为了回答此类问题提供了几个链接以尝试找到该问题的解决方案。 直到今天我仍无法找到一篇博客文章其中汇集了有关使用JPA进行查询的良好主题有关性能/利用率的技巧要下载的源代码…… 今天我们将看到 模型类和将生成数据库数据的类 查找方法 使用getReference方法可获得更好的性能并使用log4j在控制台中显示查询参数 JPQL具有简单参数或对象的查询联接排序依据在关系中导航 JPQL功能AVGCOUNTMAXMINTRIMSUMUPPERLOWERMODLENGHTSQRT 使用HAVINGGROUP BY JPQL筛选条件LIKEINDISTINCTEMPTYBETWEENNULLMEMBER OFEXISTS子查询ANYALLSOMECONCATCURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPLOCATESIZESUBSTRING JPANamedQuery使用日期查询有关getSingleResult方法的警告 JPANativeQuery名为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 {IdGeneratedValue(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 ListDog dogs;OneToOne(cascade CascadeType.ALL)JoinColumn(nameaddress_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 ListDog getDogs() {if (dogs null) {dogs new ArrayListDog();}return dogs;}public void setDogs(ListDog 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 {IdGeneratedValue(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 {IdGeneratedValue(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注释的关系例如“ OneToManyfetch 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.stdoutorg.apache.log4j.ConsoleAppender
log4j.appender.stdout.TargetSystem.out
log4j.appender.stdout.layoutorg.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern%d{ABSOLUTE} %5p %c{1}:%L - %m%n# Root logger option
log4j.rootLoggerERROR, stdout# Hibernate logging options (INFO only shows startup messages)
log4j.logger.org.hibernateERROR# Log JDBC bind parameter runtime arguments
log4j.logger.org.hibernate.typeTRACE 如果要停用日志则只需在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();ListDog 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());ListDog 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 ListDog 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 ListDog 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功能AVGCOUNTMAXMINTRIMSUMUPPERLOWERMODLENGHTSQRT 使用HAVINGGROUP 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);ListObject[] 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);}ListObject[] 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);ListObject[] 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 ListObject[] where object[0] is a person, object [2] is a Long*/SuppressWarnings(unchecked)private static ListObject[] 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 ListObject[] where object[0] is the min, object [2] is the max*/SuppressWarnings(unchecked)private static ListObject[] 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 ListObject[] 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筛选条件LIKEINDISTINCTEMPTYBETWEENNULLMEMBER OFEXISTS子查询ANYALLSOMECONCATCURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPLOCATESIZESUBSTRING 其中一些功能具有相同目的但处理方式不同。 在下面您可以看到如何使用这些功能 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();ListPerson personByLike getPersonByNameUsingLike(em, oh);for (Person person : personByLike) {System.out.println(person.getName());}ListPerson personsByAdressNumber getPersonsByAddressNumberHigherThan(em, 90);for (Person person : personsByAdressNumber) {System.out.println(person.getName());}ListPerson personsWithoutDogs getPersonsWithoutDogs(em);System.out.println(Total of persons without dogs: personsWithoutDogs.size());ListPerson 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);ListDog 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());ListPerson 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);ListPerson personsDogWeight getPersonByDogWeightOnlyHigherThan(em, 20);for (Person person : personsDogWeight) {System.out.println(person.getName());}ListPerson distinctPersons getDistinctPersonsByDogsWeight(em, 2d);System.out.println(With the distinct, the result size is: distinctPersons.size());ListPerson 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);ListDog 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 ListPerson 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 ListPerson 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 ListPerson 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 ListPerson 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 ListDog 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 ListPerson 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 ListPerson 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 ListPerson 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 ListPerson 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 ListDog 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中选择concatfirstNamelastName” 在“ getPersonByDogWeightOnlyHigherThan ”方法中我们使用ALL运算符。 仅当条件的所有项目“ weight ALL” 均返回true时此运算符才返回true。 在这种方法中只有当所有狗的体重都大于“weight”时它才会返回true如果只有一只狗的体重值较小则比较器将返回false。 您必须知道 如果列表为空则比较器将返回true 。 为了避免这种行为您需要像方法中那样检查列表是否为空“ p.dogs不为空 ”。 “ distinct ”功能将删除重复的对象。 在方法“ getDistinctPersonsByDogsWeight ”中“ distinct ”功能将删除重复的人。 “ SUBSTRING ”函数从给定的字符串中提取一个值。 您将设置将从原始值中提取的值的开始和结束。 您也可以将此功能用作比较器。 “ SIZE ”函数将返回集合中的元素数量。 您可以用作比较器或获取值。 在上面的代码中我们使用“ CURRENTE_DATE ”函数比较日期您也可以使用“ CURRENT_TIMECURRENT_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
