Spring Data JPA Tutorial Series (iii)--------jpql_spring

Gorky once said--"to use a good spring Data Jpa, you must learn to JPQL."

Hey, heh. Students who have studied hibernate may know that there is a hql in Hibernate, which is independent of the database and can be automatically translated into the SQL of the corresponding database.

JPQL, too, is an object-oriented language that can be ported. Let's go straight ahead, I can't wait.

To emit JPQL queries from within Java code, you need to take advantage of the appropriate methods of the Entitymanager API and the Query API to perform the following general steps:

1. Use injection or obtain a Entitymanager instance by entitymanagerfactory instance.

2. Create a Query instance by calling the appropriate Entitymanager method, such as CreateQuery.

3. If there are query parameters, use the Setparameter method of the corresponding query to set up.

4. If necessary, use setmaxresults and/or Setfirstresult Query to set the maximum number of instances to retrieve and/or specify the starting instance location for the retrieval.

5. If necessary, set vendor-specific prompts using the Sethint Query method.

6. If necessary, use the Setflushmode query method to set the refresh mode of the query execution, overwriting the refresh mode of the entity manager.

7. Execute the query Getsingleresult or getresultlist using the corresponding query method. If you do an update or delete operation, you must use the Executeupdate method, which returns the number of entity instances that have been updated or deleted.

User-------------->interests many-to-many Relationship,

JPQL:

Select u from  user u

select u.name u.income from user u  

Looks like it doesn't make any difference to SQL, right, so here's a simple example, and we're going to talk about the concept:

Arithmetic operators:

+   -   *   /

Select u from User u where (u.income + 2000) = 5000

Relational operators:

= > < >= <=

Logical operators:

Between,like,in,is Null,is Empty,member of, Not,and, or

Is null used to judge common properties.

is empty used to judge collection properties.

Member of the words, emmm ... Give me a chestnut:

Find out all user:select that like sports u from User u where:interest member of U.interests

: Interests is an entity of interest in sports, yes

JPQL function

String functions:

Concat,substring,trim,upper,lower,length,locate

concat concatenation String,

Trim (Leading, ' small ', from u.name) means to remove the ' small ' string on the left

Trailing to the right, both means both sides, and remove the space can be directly trim (u.name)

Locate is the location function, locate (' small ', u.name,1) indicates the ' small ' position, and the minimum starting position is 1.

Time function:

Current_date date, Current_time time, Current_timestamp date time

Arithmetic functions:

ABS Absolute Value

sqrt square root

MoD Take Mold

Number of size collections (in where only)

For example: select U.name from User u where size (u.interests) =2 detect 2 people who are interested.

JPQL Paging:

Similar to hibernate, set the starting position and number of Setfirstresult and Setmaxresult in query

JPQL Group query:

Group BY ... having

Aggregate functions: AVG (DISTINCT), sum (distinct), count (distinct), min (distinct), MAX (DISTINCT)

JPQL Connection query:

Left JOIN connection:

Selecy A.id,a.name,b.id,b.name from A-a left join A.B b   

Association query:

Select u from User u (left) join fetch u.interests I

JPQL Dynamic Query:

Based on positional parameters: Use a number to indicate the position of the parameter, such as. 1,. 2, set the parameters on Query.setparameter (1,name);

Based on name binding: with: ID colon plus a parameter name, Query.setparameter ("id", name);

Using constructors in queries (constructor)

You can use the constructor in the SELECT clause to return one or more Java instances. As shown below:

Query query = em.createquery ("Select New Com.demo.bean.Person (P.id, p.name) from person p order by p.id Desc");

JPQL localized queries, that is, using SQL queries directly, porting to other databases requires modifying code, and requires manual results mapping:

Query query = entitymanager.createnativequery ("");

Named query

You can @namedquery or @NamedQueries predefined one or more query statements on an entity bean, reducing the number of bugs that are caused by writing errors each time. Frequently used query statements are usually defined as named queries.

To define a single named query:

@NamedQuery (name= "Getperson", query= "select p from person  p where p.id=?1")

@Entity the public
class person Implements serializable{

If you want to define multiple named queries, you should define @namedquery in @javax.persistence.namedqueries:

@NamedQueries ({

@NamedQuery (name= "Getperson", query= "select p from person  p where p.id=?1"),

@NamedQuery (name= "Getpersonlist", query= "select p from person as p where p.age>?1")

})

@Entity public

class person implements serializable{

Once the named query has been defined, we can execute its query by name. The code is as follows:

Query query = em.createnamedquery ("Getperson");

Query.setparameter (1, 1);

Here it is, the next one to talk about advanced inquiries, blog writing is not good, there is any problem directly home QQ ask me, or leave a message.