With the increasing use of JPA (Java Persistence API), traditional Java Persistence Query Language (JPQL) queries have exposed many shortcomings. The most obvious is that the Java compiler cannot find the syntax error of the JPQL string at compile time and can only wait for the JPQL statement to execute at runtime before throwing a runtime exception.
To make up for the shortcomings of JPQL, a new generation of query APIs is introduced: Criteria API. The Criteria API supports building queries dynamically at runtime, as well as building type-safe queries that can be checked by the compiler. The compiler cannot check the correctness of JPQL queries, it must be checked at runtime of the test. See IBM.
For more information, see logicbig.
Das JpaRepository<T, ID>Interface of JPA can conveniently define filtering conditions of entity-object fields. For example, the Person object has fields such as name, age, and phone. It's easy to create queries on the fields of these objects yourself, but there's no way to query nested fields. In addition, some conditions may or may not be present for screening queries, which is also a blind area that interface method definitions cannot touch.
In order to solve the above two problems, it is necessary to introduce theJpaSpecificationExecutor<T>Interface and UsageSpecification<T>to define query conditions.
public interface Specification<T> erweitert Serializable {@NullablePredicate toPredicate(Root<T> Root, CriteriaQuery<?> Query, CriteriaBuilder CriteriaBuilder);}The description of each parameter is as follows:
Root<T>: Entity node that can represent the attributes and nested attributes of the entity and is used to specify the fields to query.CriteriaBuilder: Used to create query conditions, e.g. B. cb.isNull(root.get(“deleteTime”))criteria query: The last query object, all query conditions are packed in this object and executed by JPA.
For non-collective fields, there is nothing wrong with the appeal method. Suppose the nested field is a collection as shown in the following person:
@Entitypublic class Person{@OneToMany(mappedBy="Person")Set<Address> The address;}Since the @OneToMany annotation is lazy-loaded by default, when querying Person, addresses are lazy-loaded by default so that there is no data. If you need to conditionally filter the nested collection attribute (addresses here), it is best to use Address as the body text (i.e.Root<Address>) to run a query, and then assign it to the Addresses attribute.
List<T> findAll(@Nullable Specification<T> spec, Sort sort);
Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);
Use theJpaSpecificationExecutor<T>.count(Specification<T> Specification)counting method.
You can also use EntityManager query, seeCriteriaBuilder.count()Method.
Use EntityManager to create queries.
CriteriaBuilder cb = EntityManager. getCriteriaBuilder();CriteriaQuery<Tuple> criteriaQuery = cb.createQuery(Tuple.class);Root<QuiltRule> root = kriterienQuery.from(QuiltRule.class);KriterienAbfrage. where(cb.GreaterThan(root.get("id"), 0)).multiselect(cb.sum(root.get("price"))); //This keyTuple tuple = entityManager.createQuery(criteriaQuery).setMaxResults(1).getSingleResult();object integer = tuple. get(0); The grouping query should be used in conjunction with the aggregate function to define the statistical scope of the aggregate function. The condition of groupBy must be an item in the multiple selection.
CriteriaBuilder cb = EntityManager. getCriteriaBuilder();CriteriaQuery<Tuple> query = cb.createQuery(Tuple.class);Root<QuiltAppointment> root = query.from(QuiltAppointment.class);List<tuple> result = entityManager. create query (query.multiselect(root.get("Order").get("Device").get("mbId").alias("mbId"), cb.sum(root.get("Order").get(" payMoney")).alias("Money")).where(cb. equal(root.get("state"), 3)).groupBy(root.get("Order").get("Device").get("mbId"))).getResultList(); You can use SQL's built-in functions for statistics.
CriteriaBuilder cb = EntityManager. getCriteriaBuilder();CriteriaQuery<Tuple> query = cb.createTupleQuery();Root<QuiltOrder> orderRoot = query.from(QuiltOrder.class);Expression<String> payTime = cb.function("DATE_FORMAT", String.class, orderRoot.get("payTime"), cb.parameter(String.class, "format")); //define function [function name, return type, parameters...]List<tuple> tupleList = entityManager. create query (query.multiselect(payTime.alias("date"), //Use functioncb.sum(orderRoot.get("payMoney")).alias("sum")).wo(cb.isNull(orderRoot.get("deleteTime")),cb.isNotNull(orderRoot.get("payTime")),cb. sizerThanOrEqualTo(orderRoot. get("payTime"), s),cb.lessThanOrEqualTo(orderRoot.get("payTime"), e),cb.isNull(orderRoot.get("refundTime"))).groupBy(payTime)).setParameter("format", "%Y-%m-%d") //Set function parameters.getResultList(); Similar to the case of SQL...when...else, it can find multiple results in different scopes at once in one query.
CriteriaBuilder cb = EntityManager. getCriteriaBuilder();Abfrage = cb.createTupleQuery();Root<QuiltUser> userRoot = query.from(QuiltUser.class);Tupel userResult = entityManager.createQuery(query.multiselect(cb.sum(cb.selectCase().when(cb.greaterThanOrEqualTo(userRoot.get("createTime")), DateUtils.truncate(new Date(), Calendar.DATE)), 1).else(0).as ( Integer.class)).alias("today"), //Statistics of today's userscb.sum(cb.selectCase().when(cb.greaterThanOrEqualTo(userRoot.get("createTime")), DateUtils.truncate(new Date(), Calendar.MONTH)), 1).else(0).as ( Integer.class)).alias("month"), //Statistics of users this monthcb.count(userRoot.get("id")).alias("total") //count all users).wo(cb.isNull(userRoot.get("deleteTime")))).setMaxResults(1).getSingleResult(); If the field to be queried is in another table or if a join type needs to be specified, a join query is required.
CriteriaBuilder cb = EntityManager. getCriteriaBuilder();CriteriaQuery<Tupel> tupleQuery = cb. createTupleQuery();Root<QuiltArea> areaRoot = tupleQuery.from(QuiltArea.class);Path<Object> SchoolPath = areaRoot. join("Schools", JoinType.LEFT). get("id");Path<Object> devicePath = areaRoot.join("schools", JoinType.LEFT).join("floors", JoinType.LEFT).join("devices", JoinType.LEFT).get("id");List<tuple> resultList = entityManager. create query (tupleQuery.multiselect(areaRoot.get("name").alias("name"),cb.countDistinct(schoolPath).alias("schools"),cb.countDistinct(DevicePath).alias("Devices")).wo(cb.isNull(areaRoot.get("deleteTime"))).groupBy(areaRoot.get("name"))).getResultList();The code above queries the count of all schools and all devices in each area, grouped by area name.
A subquery is actually a nested query, similar to a method call that encapsulates a complex query.
CriteriaBuilder cb = EntityManager. getCriteriaBuilder();//query areaCriteriaQuery<Tupel> tupleQuery = cb. createTupleQuery();Root<QuiltArea> areaRoot = tupleQuery.from(QuiltArea.class);//Subquery: Query the number of schools in the area given the area IDSubquery<Long> schoolSQ = tupleQuery.subquery(Long.class);Root<QuiltSchool> fromSchool = schoolSQ. from(QuiltSchool. class);SchuleSQ. select(cb. count(fromSchool.get("id"))).where(cb.and(fromSchool.get("deleteTime").isNull(), cb.equal(fromSchool.get("area").get("id"), areaRoot.get("id"))) );//Subquery: Query the number of devices in the region according to the region IDSubquery<Long> deviceSQ = tupleQuery.subquery(Long.class);Root<QuiltDevice> fromDevice = deviceSQ.from(QuiltDevice.class);deviceSQ.select(cb.count(fromDevice.get("id"))).where(cb.and(fromDevice.get("deleteTime").isNull(), cb.equal(fromDevice.get("floor").get("school").get("area").get(" id"), areaRoot. get("id"))));// parameter conditionList<Predicate> PredicateList = new ArrayList<>();predicateList.add(areaRoot.get("deleteTime").isNull());if (StringUtils. hasLength(q. getCityOrArea())) {String ca = "%" + q.getCityOrArea() + "%";predicateList.add(cb.or(cb.like(areaRoot.get("name"), ca),cb. like(areaRoot. get("city"). get("name"), ca)));}// last queryList<tuple> areaList = entityManager. create query (tupleQuery.multiselect(areaRoot. received ("name"). alias ("territory"),areaRoot.get("id").alias("areaId"),areaRoot. join("City", JoinType.LEFT). received ("name"). pseudonym ("city"),areaRoot. join("Employees", JoinType.LEFT). get("nickname"). alias ("nickname"),areaRoot. join("Employees", JoinType.LEFT). received ("Account"). alias ("account"),schoolSQ.alias("schoolNum"), //Subquery as an expressiondeviceSQ.alias("deviceNum") //Subquery as an expression).where(predicateList.toArray(new Predicate[0]))).setFirstResult(Start).setMaxResults(Size).getResultList(); UseEntityManagerto update some fields of the record:
CriteriaDelete<PO> poCriteriaUpdate = CriteriaBuilder.createCriteriaUpdate(PO.class);Root<PO> mppRoot = poCriteriaUpdate.from(PO.class);int mpp = entityManager.createQuery(poCriteriaUpdate.set(mppRoot.get("name"),"name").where(mppRoot. get("po"). get("id"). in(ids))).executeUpdate(); UseEntityManagerDelete records:
CriteriaDelete<PO> poCriteriaDelete = CriteriaBuilder.createCriteriaDelete(PO.class);Root<PO> mppRoot = poCriteriaDelete.from(PO.class);int mpp = entityManager.createQuery(poCriteriaDelete.where(mppRoot. get("po"). get("id"). in(ids))).executeUpdate(); @Inquiry
The @Query annotation is used for SQL queries andnativeQueryis set totrue(the class name cannot be used for true, but the table name of the database).
@Modifying und @Transactional
In addition to adding, deleting and changing the@InquiryAnnotation,@Changeshould also be added to indicate that it is a modification method. In addition, every change must be made in a transaction, so add@Transaction.
Binding of SQL parameters and method parameters in @Query
@Param("param") und :param
Parameter in sql: param , parameters marked with@Param("Param")about the binding method.
?i represents the ith parameter
In SQL use?Ito bindwithParameters of the method.
Others
The in clause can only bind list type parameters.
The projection is actually a field mapping function. JPA's Criteria API encapsulates the query results asjavax.persistence.Tupeltype by default and then retrieves each query result. The projection discussed here refers to the mapping of the return result onto the interface. See JPA projection for details.
If the return result of the interface can only consist of all entity fields, there is unnecessary network and query overhead. According to a certain scenario, you may only need some fields of the entity, or some statistical fields, or connect some fields of other tables. In this case, it is not appropriate to return an entity.
Fields returned from an interface are defined with the interface.Although the official documentation says it can be defined with a class, testing has not been successful.
projected as an interface
//First define an interface that accepts the returned result and follows the POJO naming convention.
Interface NamesOnly {String getFirstname();//The field in SQL should be firstNameString getLastname();//The field in SQL should be lastName}Interface PersonRepository extends Repository<Person, UUID> {// You can't just query for the method name here; You can also use the @Query annotationCollection<Person> findByLastname(String lastname);}If the field returned does not follow the naming of POJO, you must useifdefine an alias in sql.
Interface NamesOnly {String getVorname();String getNachname();}//Use as name@Query(value="select first_name as firstName, last_name as lastName from user where id=?1", nativeQuery=true)Fields can also be specifiedorg.springframework.beans.factory.annotation.@ValueAnnotation.
Interface NamesOnly {@Value("#{tagert.first_name}") //This can also be a more complex SpEL expressionString getVorname();@Value("#{tagert. Last Name}")String getNachname();}//Do not use as name@Query(value="select first_name,last_name from user where id=?1", nativeQuery=true)project as a tuple
The result returned is also receivedjavax.persistence.Tupel.
Projected as a map
The returned result is received byjava.util.Map.
projects as Object[]
The return result is received as an array of objects.
JPA does a good job of encapsulating jdbc in the form of an interface, such asJpaRepository<T, ID>, which can run non-nested conditional queries; thenJpaSpecificationExecutor<T> executesnested conditional queries and maybe queries on JPA (may or may not be conditions) are added; Finally, for nested collection attributes, it's best to change the subject and do a conditional query before assigning a value.
FAQs
How to use criteria API in JPA? ›
Create an instance of CriteriaBuilder by calling the getCriteriaBuilder() method. Create an instance of CriteriaQuery by calling the CriteriaBuilder createQuery() method. Create an instance of Query by calling the Session createQuery() method. Call the getResultList() method of the query object, which gives us the ...
Is Criteria API deprecated? ›It has been deprecated in Hibernate 5, and you should avoid it when implementing new use cases. Sooner or later, you will also need to replace Hibernate' Criteria API in your existing use cases. Let's talk about the differences compared to JPA's Criteria API and the required migration steps.
What is the difference between JPQL and criteria API? ›JPQL queries are defined as strings, similarly to SQL. JPA criteria queries, on the other hand, are defined by the instantiation of Java objects that represent query elements. A major advantage of using the criteria API is that errors can be detected earlier, during compilation rather than at runtime.
How get JPQL SQL string from Criteria query in JPA? ›- EclipseLink. EclipseLink's Query representation is the org. eclipse. persistence. jpa. ...
- Hibernate. Hibernate's Query representation is org. hibernate. Query. ...
- OpenJPA. OpenJPA's Query representation is org. apache. openjpa.
The Criteria API is used to define queries for entities and their persistent state by creating query-defining objects. Criteria queries are written using Java programming language APIs, are typesafe, and are portable. Such queries work regardless of the underlying data store.
Which API is deprecated now? ›acl API has been deprecated.
Do deprecated methods still work? ›A deprecated class or method is like that. It is no longer important. It is so unimportant, in fact, that you should no longer use it since it has been superseded and may cease to exist in the future.
Does deprecated still work? ›While deprecated software is no longer supported, it may continue to work. In some cases, however, deprecated software may stop working entirely if, for example, it is incompatible with an updated operating system.
What are the disadvantages of JPQL? ›The disadvantage of JPQL is that dynamic queries require performing string concatenations to build queries dynamically from web forms or dynamic content. JPQL is also not checked until runtime, making typographical errors more common.
Is JPQL better than native query? ›Most of the time, a good JPQL Query can fulfill our needs and most importantly, maintain a level of abstraction from the actual database implementation. Using NativeQuery doesn't necessarily mean locking ourselves to one specific database vendor.
Is native query faster than JPQL? ›
It shows that JPA queries are 15% faster than their equivalent JDBC queries. Also, JPA criteria queries are as fast as JPQL queries, and JPA native queries have the same efficiency as JPQL queries.
What can we do by using @query JPA annotation? ›- 8.1. JPQL. ...
- 8.2. Native. ...
- 8.3. Inserts.
The Criteria API is a predefined API used to define queries for entities. It is the alternative way of defining a JPQL query. These queries are type-safe, and portable and easy to modify by changing the syntax. Similar to JPQL it follows abstract schema (easy to edit schema) and embedded objects.
What is the difference between criteria query and TypedQuery? ›These two types are not the alternatives of each other, they rather serve the different purposes: CriteriaQuery<T> is used to programmatically define query, instead of writing it manually, and TypedQuery<T> is used to avoid casting which you have to do when using Query .
How do you use multiple criteria in a query? ›- Open the table that you want to use as your query source and on the Create tab click Query Design.
- In the Query Designer, select the table, and double-click the fields that you want displayed in the query results.
Four kinds of action queries are: Append Query – takes the set results of a query and "appends" (or adds) them to an existing table. Delete Query – deletes all records in an underlying table from the set results of a query. Make Table Query – as the name suggests, it creates a table based on the set results of a query.
How many types of criteria are there? ›There are two types of criteria you can use; specific and generic. Specific criteria can be answered with a simple yes, no or maybe.
How to write subquery in JPA criteria? ›Following is Subquery interface snippet: package javax. persistence. criteria; .... public interface Subquery<T> extends AbstractQuery<T>, Expression<T> { Subquery<T> select(Expression<T> expression); Subquery<T> where(Expression<Boolean> restriction); Subquery<T> where(Predicate...
What is the advantage of criteria query? ›5. Advantages of Criteria Queries Over HQL and JPQL Queries. The main advantage of Criteria Queries over HQL is the nice, clean, object-oriented API. As a result, we can detect errors in Criteria API during the compile time.
What is the difference between Java Persistence Query Language and Criteria API? ›The Java Persistence Query Language (JPQL) is a SQL-like language that expresses queries in Strings. The Criteria API uses Java code with a builder syntax. Generally, I write JPQL, but sometimes the parameters, logic, and String concatenation makes less readable code. In that case, I opt for the Criteria API.
What will replace APIs? ›
Since being introduced by Facebook, GraphQL has taken the API world by storm as an alternative to REST APIs. GraphQL fixes many problems that API developers and users have found with RESTful architecture. However, it also introduces a new set of challenges which need to be evaluated.
What are the three types of APIs available? ›Today, there are three categories of API protocols or architectures: REST, RPC and SOAP.
Do you really need API versioning? ›Updating an API is necessary, but also risky. Without proper versioning, things break. And when things break, consumers lose trust and look for a more stable alternative. With each change, aim to take as much burden off the customer as you can — that's the ultimate goal of proper API versioning.
What is the difference between deprecate and delete? ›A removed feature is no longer available in the product. A deprecated feature is not in active development and may be removed in a future update.
Should you delete deprecated code? ›Often, removing dependencies on deprecated code is not a priority for development teams that prefer to focus on new development. However, unaddressed deprecated code can lead to future code breaks and inefficient databases.
Why is @deprecated used? ›Using the @Deprecated Annotation
To use it, you simply precede the class, method, or member declaration with "@Deprecated." Using the @Deprecated annotation to deprecate a class, method, or field ensures that all compilers will issue warnings when code uses that program element.
A deprecated API is one that you are no longer recommended to use, due to changes in the API. While deprecated classes, methods, and fields are still implemented, they may be removed in future implementations, so you should not use them in new code, and if possible rewrite old code not to use them.
Does deprecated mean delete? ›Deprecation and removal are two different things. Deprecation, on the other hand, means that the manufacturer discourages a feature's use but leaves it available. Typically, deprecation occurs when a feature is unsafe or gets replaced with a superior alternative.
Can deprecated API still be used True False? ›It can still be used, but eventually it will not work with other things because it is no longer being supported.
What is the advantage of JPQL over SQL? ›Jpql takes advantage of mapping (lazy / eager loading, relatinships) - it uses what you have already defined. In SQL you would have to map the result somehow. Queries can be stored in cache to provide better performance. Persistence context doesn't need to be flushed before query.
What is better than JPA? ›
Also, JPA is thought to be better suited for more sophisticated applications by many developers. But, JDBC is considered the preferable alternative if an application will use a simple database and we don't plan to migrate it to a different database vendor.
Why JPQL is better than SQL in Web application? ›SQL works directly against relational database tables, records and fields, whereas JPQL works with Java classes and instances. For example, a JPQL query can retrieve an entity object rather than field result set from database, as with SQL.
What is the difference between @NamedQuery and @query? ›@Query annotation is a similar annotation, which declares finder queries directly on repository methods. While @NamedQuery is used on domain classes, Spring Data JPA @Query annotation is used on Repository interface. This frees the domain classes from persistence specific information, which is a good thing.
What is the difference between @query and NamedQuery? ›...
The Differences between Query and Named Query Commands.
| Mary by Query | Mark by Named Query |
|---|---|
| Cannot combine queries. | Can combine queries into a new query. |
This is a common misconception.
JPA and other ORMs relieves us from creating hand-coded SQL statements, but they won't prevent us from writing vulnerable code.
- 1.1 1. Choose a projection that fits your use case.
- 1.2 2. Avoid eager fetching in your mapping definition.
- 1.3 3. Initialize all required associations in your query.
- 1.4 4. Use pagination when you select a list of entities.
- 1.5 5. Log SQL statements.
The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can minimize the calculation burden on the database i.e., instead of multiple queries using one join query.
Which is faster JPA or JDBC? ›JPA is 4x slower than JDBC when it comes to large-batch processing. Mapping between database tables can be a bit challenging.
Why do we use @entity annotation? ›The JPA specification requires the @Entity annotation. It identifies a class as an entity class. You can use the name attribute of the @Entity annotation to define the name of the entity. It has to be unique for the persistence unit, and you use it to reference the entity in your JPQL queries.
What is @query annotation used for? ›The @Query annotation can only be used to annotate repository interface methods. The call of the annotated methods will trigger the execution of the statement found in it, and their usage is pretty straightforward. The @Query annotation supports both native SQL and JPQL.
What is the use of @springboot annotation? ›
Spring Boot @SpringBootApplication annotation is used to mark a configuration class that declares one or more @Bean methods and also triggers auto-configuration and component scanning. It's same as declaring a class with @Configuration, @EnableAutoConfiguration and @ComponentScan annotations.
How to use Criteria API in JPA? ›Create an instance of CriteriaBuilder by calling the getCriteriaBuilder() method. Create an instance of CriteriaQuery by calling the CriteriaBuilder createQuery() method. Create an instance of Query by calling the Session createQuery() method. Call the getResultList() method of the query object, which gives us the ...
What is the difference between JPQL and Criteria API? ›JPQL queries are defined as strings, similarly to SQL. JPA criteria queries, on the other hand, are defined by the instantiation of Java objects that represent query elements. A major advantage of using the criteria API is that errors can be detected earlier, during compilation rather than at runtime.
Which is better HQL or criteria? ›HQL is suitable for executing Static Queries, where as Criteria is suitable for executing Dynamic Queries. HQL is to perform both select and non-select operations on the data, Criteria is only for selecting the data, we cannot perform non-select operations using criteria.
What is the difference between native query and named query in JPA? ›Native query refers to actual sql queries (referring to actual database objects). These queries are the sql statements which can be directly executed in database using a database client. Similar to how the constant is defined. NamedQuery is the way you define your query by giving it a name.
How does @query work? ›Queries help you find and work with your data
A query can either be a request for data results from your database or for action on the data, or for both. A query can give you an answer to a simple question, perform calculations, combine data from different tables, add, change, or delete data from a database.
Criteria in Hibernate can be used for join queries by joining multiple tables, useful methods for Hibernate criteria join are createAlias(), setFetchMode() and setProjection() Criteria in Hibernate API can be used for fetching results with conditions, useful methods are add() where we can add Restrictions.
Can we write SQL query in JPA repository? ›In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.xml file.
What is Criteria Builder API? ›public interface CriteriaBuilder. Used to construct criteria queries, compound selections, expressions, predicates, orderings. Note that Predicate is used instead of Expression<Boolean> in this API in order to work around the fact that Java generics are not compatible with varags. Since: Java Persistence 2.0.
What are three methods to execute queries in JPA? ›- Query, written in Java Persistence Query Language (JPQL) syntax.
- NativeQuery, written in plain SQL syntax.
- Criteria API Query, constructed programmatically via different methods.
What is the difference between criteria and DetachedCriteria? ›
Using a DetachedCriteria is exactly the same as a Criteria except you can do the initial creation and setup of your query without having access to the session. When it comes time to run your query, you must convert it to an executable query with getExecutableCriteria(session) .
Which API is used to fetch the records based on the specific criteria? ›The Hibernate Criteria Query Language (HCQL) is used to fetch the records based on the specific criteria. The Criteria interface provides methods to apply criteria such as retreiving all the records of table whose salary is greater than 50000 etc.
How do I pass a SQL query in REST API? ›To run a SQL statement through REST, you POST a JSON document that includes the SQL statement to execute against the /api/servers/{alias}/databases/{dbname}/sql URI endpoint.
Can we use subquery in JPA query? ›JPA allows usage of subqueries in WHERE or HAVING clauses. Subqueries must be surrounded by parentheses.
What are the 4 types of an action query? ›There are four types of action queries: append queries, delete queries, update queries, and make-table queries. Except for make-table queries (which create new tables), action queries make changes to the data in tables they are based on.
What are the 4 types of queries? ›They are: Select queries • Action queries • Parameter queries • Crosstab queries • SQL queries.