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

Source: Internet
Author: User

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 ~~~~~~~~~~ ............


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.