Spring Data JPA Tutorial Series (iv)--------advanced query _spring

This chapter describes the spring Data JPA advanced Query, the last chapter of the JPQL,JPQL is very convenient to use, but if the SQL has a word accidentally write wrong, only when the program is run to find out where the error, this is a disadvantage, if you want to find errors in the compiler how to do it? The answer is to use the spring Data JPA advanced query.

First, the criteria query

@PersistenceContext

private Entitymanager Entitymanager;

First inject Entitymanager

Criteriabuilder cb = Entitymanager.getcriteriabuilder ();  Criteriaquery Factory
    Criteriaquery CQ = Cb.createquery ();//query Statement builder
    root<user> Root = Cq.from (User.class);  //Get the query root object
    cq.select (root);   Select u from User u
    predicate pre = Cb.greaterthan (Root.get (' age '). as (integer.class);//Age greater than
    Cq.wher E (PRE); Select u from user u where u.age >
    ///above this string is intended to construct a    select u from user u where u.age >  JPQ L Statement
    Query query = em.createquery (CQ);
    list<user> users = Query.getresultlist ();

This is an example of a simple criteria query, and here's how to use dynamic parameters, at the top of parameter 20, where you can change:

  predicate pre = Cb.greaterthan (Root.get ("age"). As (Integer.class), Cb.parameter (Integer.class, "age"));
    Then in the back:
    query.setparameter ("Age",  20);

Query only some fields:

Change Cq.select (Root) to Cq.select (Root.get ("Age"));

There are multiple fields on Cq.multiselect (Root.get ("Age"), Root.get ("name") ...) To

At this point, there are two methods of result set mapping

①list<user> to List<object[]> Manually set the mapping of values to entity attributes;

②cq.select (Cb.construct (User.class, Root.get ("Age"),....... Other fields ...)); Automatically convert to an entity using the constructor method

Fetch query:

Before the Cq.select, add:

  Fetch Rootfetch = Root.fetch ("interests");   Associating queries out of all the interest of the user

To manually control the fetch in the program, you need to @manytomany the fetch in other annotations, and then control it in the program.

To set a join:

Before the Cq.select, add:

Root.join ("Interests", jointype.left);  Select u from User u left join u.interests where u.age > 20

Result set Ordering:

Behind the Cq.orderby:

Cq.orderby (Cb.desc (Root.get ("Age")), Cb.asc (Root.get ("name"));

Multiple query criteria:

  predicate pre = Cb.greaterthan (Root.get ("age"). As (Integer.class), 20); Age is greater than

    predicate pre2 = Cb.lessthan (Root.get ("age"). As (Integer.class), m);//age is less than

    cq.where (pre) Changed into Cq.where (Pre,pre2);

CB can also be used to set the and,or relationship, such as;

    Cq.where (Cb.and (pre, pre2));

Second, the use of Jpaspecificationexcutor query

Public interface Userrepository extends Jparepository<user, integer>, japspecificationexecutor<user>{

 }

After repository inherits Japspecificationexecutor, the userrepository is invoked in Userserviceimpl:

  Public list<user> Getusers () {return
            Userrepository.findall (new specification<user> () {
                  @ Override Public
                    predicate topredicate (ROOT<USER>, criteriaquery<?> CQ, Criteriabuilder cb) {
                        List <Predicate> prelist = new ArrayList ();
                        It is the same
                        as the conditional construction of the criteria above. // ..........
                        After the condition is constructed, the Prelist return
                        cq.where (Prelist.toarray (New Predicate[prelist.size ())). Getrestriction ();            
            }) ;       

        }

Third, @Query and @namedquery inquiries

This annotation allows us to use custom query statements (JPQL or SQL) at the pretext of the method.

Public interface Userrepository extends Jparepository<user, integer>, japspecificationexecutor<user>{

    @Query (value= "Select  u from User u where name like 1")//Here you can use positional parameters public

    list<user> finduserbyname (String name);

    @Query (value= "Select  u from User u where name like:name")  you can also use named parameters

    //Public list<user> Finduser ByName (@Param ("name") String name);

If you want to use native SQL, there is one attribute in the @Query annotation that can be configured:

@Query (value= "Select  u from user u where name like 1", Nativequery = True)//set to True means using native SQL queries

@NamedQuery: Named query, is called the entity Manager to execute the named query, there are some frequently used queries, we write it first, then call directly according to the name.

@NamedQuery custom query is written to the entity,

@NamedQuery (name = "Finduserbyname", query= "Select U from User s where u.name like? 1")
    @Entity
    @Table ("User") 
  public class User {}

Called using the Entity Manager:

  Use named query

   entitymanager.createnamedquery ("Finduserbyname");

    Query.setparameter (1, "% king%");

    list<user> users = Query.getresultlist ();

When there are multiple named queries, you can wrap them @namedqueries

@NamedQueries ({
         @NamedQuery (name = "Finduserbyname", query= "Select U from User s where u.name like? 1"),
         @NamedQu ery (name = "FindUserByName2", query= "Select U from User s where u.name like 1"),
         @NamedQuery (name = "FindUserByName2" , query= "Select U from User s where u.name like 1")
    })
    @Entity
    @Table ("User") public
    class User {}

OK, this lesson is here, after class ~~~~~~~~~~ ............