1. How to call the interface

1. Basic introduction

By calling the method query in the interface, we need our custom interface to inherit the Spring Data Jpa specified interface

public interface UserDao extends JpaRepository<User.Integer>, JpaSpecificationExecutor<User>
Copy the code

The prerequisite for using these methods is that the entity class you define must be annotated accordingly

@Entity // This is an entity class
@Table(name = "tbl_user") // Create a mapping between entity classes and tables
public class User {

    @Id // Declare this property as the primary key
    @GeneratedValue(strategy = GenerationType.IDENTITY) // Primary key generation policy, increment
    @Column(name = "user_id")// Specify the column name of the database table corresponding to the attribute
    private Integer userId;

    @Column(name = "user_name")
    private String userName;

    @Column(name = "user_address")
    private String userAddress;

    @Column(name = "user_salary")
    private Double userSalary;
    
    / /... Getter setter toString method
}
Copy the code
  1. JpaRepository<T,ID>

The first interface defines some simple CRUD methods. The generic T is the type of your entity class, and the generic ID is the type of the primary key of your entity class

Generic T is the type of the entity class that you define

2. Usage

Just write an interface of your own that inherits the two interfaces and fills in the generics

// Test the class, calling the interface's findAll method
@Test
public void testFindAll(a){
    List<User> users = userDao.findAll();
    for(User user : users) { System.out.println(user); }}Copy the code

3. Precautions

The JpaRepository interface has findOne() and getOne() methods, both of which are queries in the literal sense of the word, but they are intrinsically different

  • findOne()

The bottom layer calls the find() method, which gives us the results when we call it

  • getOne()

The getReference() method is called in lazy loading mode. A dynamic proxy object is created, and an SQL statement is sent when a query result is called

Second, JPQL query

1. Basic introduction

JPQL is Jpa Query Language

JPQL operates on entity classes, while SQL operates directly on database tables. Therefore, JPQL only replaces SQL database table names, column names and other information with entity class attributes

For example,

SQL statement query: select * from tbl_user where user_name =?

Select * from User where userName =?

2. Usage

We use the @query annotation, value is a JPQL statement, and you may have noticed that each question mark has a number behind it, which actually represents the position of the parameter in the method for that attribute, so we can assign values out of the order of the attribute.

/** * Query * by user ID and name@returnUser object */
@Query(value = "from User where userId = ? 2 and userName = ? 1")
User findUserByIdAndName(String name, int id);
Copy the code

The test code

@Test
public void testJpql1(a){
    User user = userDao.findUserByIdAndName("Zhang".1);
    System.out.println(user);
}
Copy the code

3. Precautions

To use JPQL, you must have configured the entity classes and parameters using annotations

/ * * *@Entity* Function: Specifies that the current class is an entity class. *@Table* Function: Specifies the mapping between entity classes and tables. * Attribute: * name: Specifies the name of the database table *@Id* Specifies that the current field is the primary key. *@GeneratedValue* Function: specifies how the primary key is generated. * Properties: * strategy: Specifies the primary key generation strategy. * generationType. IDENTITY: increment, underlying database must support increment (mysql) * generationType. SEQUENCE: underlying database must support SEQUENCE (Oracle) * generationType. TABLE: Jpa provides a policy for primary key auto-increment by generating a table that stores the value of the next primary key to be added. * GenerationType.AUTO: the program automatically selects a policy * *@ColumnAttribute: * name: specifies the column name of the database table. * unique * nullable * inserttable * updateable * columnDefinition: Define DDL * secondaryTable: the secondaryTable name. If the column is not built on the main table (the default is), this attribute defines the name of the table from which the column is built
Copy the code

Third, SQL query

1. Basic introduction

Query using SQL statements

2. Usage

A custom method that, unlike JPQL, requires nativeQuery=true to declare that this is a local query (SQL query)

/** * use SQL to query query conditions */
@Query(value = "select * from tbl_user where user_name like ?",nativeQuery = true)
List<User> sqlFindByName(String name);
Copy the code

The test method

@Test
public void testSql2(a){
    List<User> users = userDao.sqlFindByName("%张%");
    for(User user : users) { System.out.println(user); }}Copy the code

4. Method naming rule query

1. Basic introduction

As the name implies, this method is to use the method name specified by Spring Data JPA. This method does not require us to write JPQL or SQL. Spring Data JPA will parse the method name to help us automatically create the query

2. Usage

Custom methods

/** * Fuzzy query by user name and id matching query *@param name
 * @param id
 * @return* /
List<User> findUserByUserNameLikeAndUserId(String name, int id);
Copy the code

test

@Test
public void TestName1(a){
    List<User> users = userDao.findUserByUserNameLikeAndUserAddress("%张%"."Beijing");
    for(User user : users) { System.out.println(user); }}Copy the code

3. Naming rules

According to the rules defined by Spring Data JPA, the query method starts with findBy and the delete method starts with deleteBy…… When condition query is involved, condition attributes are connected using condition keywords. Note that the condition attribute must start with a capital letter. When the framework performs method name resolution, it first intercepts the prefixes of the method name, and then parses the rest.

If you’re using the idea compiler, IDEA will give you hints as you write.