Spring Boot-------JPA Basics and query rules

JPA Basics and Query rules

Jpa

JPA is the abbreviation for Java Persistence API, the Chinese name Java Persistence Layer API, is a JDK 5.0 annotation or XML Description object-relational table mapping relationship, and the runtime of the entity object persisted into the database.

The JPA framework supports container-level transactions such as big data sets, transactions, concurrency, and so on, which makes JPA beyond the limits of a simple persistence framework and plays a greater role in enterprise applications.

Spring Boot uses JPA

The first time you create a project in idea, you need to select JPA, which automatically introduces the JPA jar package in the Pom.xml file.

Create a Basedao to inherit the jparepository, this is fundamental is also the foundation, here Basedao and hibernate very similar, can refer to, DAO is the root of a project, write well he other also almost

Basic Query

The JPA basic query is also divided into two, one is spring data by default has been implemented, one is based on the query method to automatically parse into SQL.

The default is directly called directly in your controller:

Here's what Daniel wrote, it's great, I'm just a porter.

Address http://www.cnblogs.com/ityouknow/p/5891443.html

Basic Query

The basic query is also divided into two, one is spring data by default has been implemented, one is based on the query method to automatically parse into SQL.

Pre-build method

Spring Data JPA defaults to pre-generating some basic curd methods, such as: Add, delete, change, etc.

1 Inheritance Jparepository

public interface UserRepository extends JpaRepository<User, Long> {}

2 Using the default method

@Test  Public void throws Exception {    user user=new  User ();    Userrepository.findall ();    Userrepository.findone (1l);    Userrepository.save (user);    Userrepository.delete (user);    Userrepository.count ();    Userrepository.exists (1l);     //  ...}

It doesn't explain what you mean by the method name.

customizing simple Queries

A custom simple query is the automatic generation of SQL based on the method name, the main syntax being,,, followed by the findXXBy readAXXBy queryXXBy countXXBy getXXBy property name:

User findbyusername (String userName);

Also use some keywords plus some And ,Or

User Findbyusernameoremail (string username, string email);

Modifications, deletions, and statistics are similar syntax

Long Deletebyid (long ID); Long countbyusername (String userName)

Basically the keywords in the SQL system can be used, for example: LIKE ,, IgnoreCase OrderBy .

List<user> findbyemaillike (String email); User findbyusernameignorecase (String userName);    List<User> findbyusernameorderbyemaildesc (String email);

Specific keywords, using methods and production into SQL are shown in the following table

Keyword	Sample	JPQL Snippet
and	Findbylastnameandfirstname	... where x.lastname =? 1 and x.firstname =? 2
Or	Findbylastnameorfirstname	... where x.lastname =? 1 or x.firstname =? 2
Is,equals	Findbyfirstnameis,findbyfirstnameequals	... where x.firstname =? 1
Between	Findbystartdatebetween	... where x.startdate between? 1 and? 2
LessThan	Findbyagelessthan	.. where X.age <? 1
Lessthanequal	Findbyagelessthanequal	... where x.age?? 1
GreaterThan	Findbyagegreaterthan	.. where X.age >? 1
Greaterthanequal	Findbyagegreaterthanequal	... where x.age >=? 1
After	Findbystartdateafter	.. where X.startdate >? 1
Before	Findbystartdatebefore	.. where X.startdate <? 1
IsNull	Findbyageisnull	... where x.age is null
Isnotnull,notnull	Findbyage (IS) notnull	... where x.age not null
Like	Findbyfirstnamelike	... where x.firstname like? 1
Notlike	Findbyfirstnamenotlike	... where x.firstname not? 1
Startingwith	Findbyfirstnamestartingwith	... where x.firstname like? 1 (parameter bound with appended%)
Endingwith	Findbyfirstnameendingwith	... where x.firstname like? 1 (parameter bound with prepended%)
Containing	Findbyfirstnamecontaining	... where x.firstname like? 1 (parameter bound wrapped in%)
By	Findbyageorderbylastnamedesc	... where x.age =? 1 ORDER BY X.lastname DESC
Not	Findbylastnamenot	.. where X.lastname <>? 1
Inch	Findbyagein (Collection Ages)	... where x.age in? 1
Notin	Findbyagenotin (Collectionage)	... where x.age not in? 1
TRUE	Findbyactivetrue ()	... where x.active = True
FALSE	Findbyactivefalse ()	... where x.active = False
IgnoreCase	Findbyfirstnameignorecase	... where UPPER (x.firstame) = UPPER (? 1)

Complex queries

In the actual development we need to use the paging, delete, and even the table query when you need a special method or custom SQL

Paging Query

Paged query is very common in the actual use, spring data JPA has helped us to achieve the function of paging, in the query method, we need to pass parametersPageable
, it is Pageable recommended to pass in the last parameter when there are multiple parameters in the query.

Page<user> FindALL (pageable pageable);    Page<User> findbyusername (String username,pageable pageable);

Pageableis a spring-encapsulated paging implementation class that requires an incoming number of pages, a per-page count, and a collation

@Test  Public void throws Exception {    int page=1,size=10;     New Sort (DIRECTION.DESC, "id");     New pagerequest (page, size, sort);    Userrepository.findall (pageable);    Userrepository.findbyusername ("testname", pageable);}

restricting queries

Sometimes we just need to query the top n elements, or draw the previous entity.

ser findfirstbyorderbylastnameasc (); User Findtopbyorderbyagedesc (); Page<User> queryfirst10bylastname (String LastName, pageable pageable); List<User> findfirst10bylastname (String LastName, sort sort); List<User> Findtop10bylastname (String LastName, pageable pageable);

Custom SQL queries

In fact, most of the SQL of spring data can be implemented according to the method name definition, but for some reason we want to use custom SQL to query, spring data is also the perfect support; use annotations on SQL query methods. @Query such as is related to deletion and modification in need @Modifying of adding. You can also add @Transactional support for things as needed, query timeout settings, etc.

@Modifying @query ("Update User u set u.username =? 1 where c.id =? 2") int Modifybyidanduserid (String  userName, Long ID);    @Transactional @modifying@query ("Delete from User where id =? 1")void  Deletebyuserid (Long ID );   = Ten) @Query ("Select u from user u where u.emailaddress =? 1")    User findbyemailaddress (String Emailad Dress);

Multi-Table Query

There are two implementations of multi-table queries in spring data JPA, the first of which is implemented using Hibernate cascade queries, and the second is to create a result set interface to receive the results of a query with a table, the main second way.

You first need to define an interface class for a result set.

 Public Interface hotelsummary {city    getcity ();    String getName ();    Double getaveragerating ();     default Integer getaverageratingrounded () {        returnnullnull : (int ) Math.Round (getaveragerating ());}    }

The method return type of the query is set to the newly created interface

@Query ("Select H.city as City, H.name as name, AVG (r.rating) as averagerating"        -"from Hotel H left outer join H.revi EWS r where h.city =? 1 Group by H ") page<HotelSummary> findbycity (pageable pageable), @Query (  "Select H.name as Name, AVG (r.rating) as averagerating"        -"from the Hotel h left outer joins h.reviews R  Group BY H ") page<HotelSummary> findbycity (pageable pageable);

Use

this. hotelrepository.findbycity (new pagerequest (0, ten, DIRECTION.ASC, "name"));  for (Hotelsummary summay:hotels) {        System.out.println ("Name" +Summay.getname ());    }

In the run spring will automatically produce a proxy class for the interface (Hotelsummary) to receive the returned result, which is used in getXX the form of code summarization to get

Multi-source support for multiple data sources supporting homogeneous databases

Because of the distributed development pattern used in daily projects, different services have different data sources, often need to use multiple data sources in one project, so it is necessary to configure the use of sping data JPA for multiple data sources, which is generally divided into three steps:

• 1 Configuring multiple data sources
• 2 entity classes of different sources into different package paths
• 3 declaring different package paths using different data sources, transaction support

Here is an article that is very clear: Spring boot multi-data source configuration and use

Heterogeneous database multi-source support

For example, in our project, we need support for MySQL, as well as a query for MongoDB.

Entity classes declare @Entity relational database support types, declared @Document as MongoDB support types, and different data sources use different entities to

Interface extends Repository<person, long> {...} @Entitypublicclass person {  ...} Interface extends Repository<user, long> {...} @Documentpublicclass  User {  ...}

However, if user users use both MySQL and MongoDB, they can also do mixed use

Interface extends Repository<person, long> {...} Interface extends Repository<person, long> {...} @Entity @documentpublicclass person {  ...}

You can also declare different package paths, such as using MongoDB under the Mysql,b package path under a packet path

@EnableJpaRepositories (basepackages = "COM.NEO.REPOSITORIES.JPA"= "Com.neo.repositories.mongo")  interface Configuration {}

Other

Using enumerations

When using enumerations, we want the database to store an enumeration of the corresponding string type, rather than the index value of the enumeration, to add annotations to the attribute @Enumerated(EnumType.STRING)

true )private usertype type;

Properties that do not need to be mapped with the database

Normally when we add annotations to an entity class @Entity , we make the entity class and the table related if one of the properties we do not need to associate with the database is just a calculation at the time of the presentation, just add the @Transient attribute.

@Transientprivate String  userName;

Spring Boot-------JPA Basics and query rules