JPA查询

2.8.1 开始新增JPA Query Method查询，只需要在接口中定义方法就能进行查询，无须编写SQL语句。

原理：根据方法名称中的关键字自动推导出SQL语

目前实现了大部分功能，参考：JPA Query Method (opens new window) 除了Distinct不支持，其它已全部支持

在Mapper中定义一个方法，以findBy开头

/**
 * … where x.lastname = ?1 and x.firstname = ?2
 * @param lastname
 * @param firstname
 * @return
 */
List<Student> findByLastnameAndFirstname(String lastname, String firstname);

在Mapper中定义这个方法，就可以直接使用了，无须编写XML。

使用：

    @Test
    public void findByLastnameAndFirstname() {
        List<Student> users = mapper.findByLastnameAndFirstname("张", "三");
        Assert.assertEquals(1, users.size());
        users.forEach(System.out::println);
    }

更多使用示例：

And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is, Equals	findByFirstname,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, Null	findByAge(Is)Null	… 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 like '%?1%'
StartingWith	findByFirstnameStartingWith	… where x.firstname like '?1%'
EndingWith	findByFirstnameEndingWith	… where x.firstname like '%?1'
Containing	findByFirstnameContaining	… where x.firstname like '%?1%'
OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	… where x.lastname <> ?1
In	findByAgeIn(Collection<Age> ages)	… where x.age in ?1
NotIn	findByAgeNotIn(Collection<Age> ages)	… where x.age not in ?1
True	findByActiveTrue()	… where x.active = true
False	findByActiveFalse()	… where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstname) = UPPER(?1)

注意事项

• 方法名称必须findBy开头
• 数据库字段名必须遵从下划线模式，如：name, add_time，驼峰命名方式不支持JPA查询
• 如果：IDEA下运行 JPA查询 mybatis报错 Parameter 'arg0' not found，参考这篇文章：https://www.cnblogs.com/JangoJing/p/10791831.html

maven maven-compiler-plugin插件加上<parameters>true</parameters>配置，如下所示：

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <configuration>
        <parameters>true</parameters>
        <encoding>UTF-8</encoding>
    </configuration>
</plugin>

更多例子

/**
 * 演示JPA findBy查询，根据方法名称自动生成查询语句，无需编写SQL
 * @author tanghc
 */
public interface StudentMapper extends CrudMapper<Student, Integer> {

    /**
     * … where x.lastname = ?1 and x.firstname = ?2
     * @param lastname
     * @param firstname
     * @return
     */
    List<Student> findByLastnameAndFirstname(String lastname, String firstname);

    /**
     *
     * … where x.lastname = ?1 or x.firstname = ?2
     * @param lastname
     * @param firstname
     * @return
     */
    List<Student> findByLastnameOrFirstname(String lastname, String firstname);

    /**
     * where x.firstname = ?
     * @param firstname
     * @return
     */
    List<Student> findByFirstname(String firstname);
    List<Student> findByFirstnameIs(String firstname);
    List<Student> findByFirstnameEquals(String firstname);

    /**
     * … where x.startDate between ?1 and ?2
     * @param begin
     * @param end
     * @return
     */
    List<Student> findByStartDateBetween(Date begin, Date end);

    /**
     * … where x.age < ?1
     * @param age
     * @return
     */
    List<Student> findByAgeLessThan(int age);

    /**
     * … where x.age <= ?1
     * @param age
     * @return
     */
    List<Student> findByAgeLessThanEqual(int age);

    /**
     * … where x.age > ?1
     * @param age
     * @return
     */
    List<Student> findByAgeGreaterThan(int age);

    /**
     * … where x.age >= ?1
      * @param age
     * @return
     */
    List<Student> findByAgeGreaterThanEqual(int age);

    /**
     * … where x.startDate > ?1
     * @param date
     * @return
     */
    List<Student> findByStartDateAfter(Date date);

    /**
     * … where x.startDate < ?1
     * @param date
     * @return
     */
    List<Student> findByStartDateBefore(Date date);


    /**
     * … where x.age is null
     * @return
     */
    List<Student> findByAgeNull();
    List<Student> findByAgeIsNull();


    /**
     * … where x.firstname like ?1
     * @param firstname
     * @return
     * @see #findByFirstnameContaining(String)
     */
    List<Student> findByFirstnameLike(String firstname);

    /**
     * … where x.firstname not like ?1
     * @param firstname
     * @return
     */
    List<Student> findByFirstnameNotLike(String firstname);

    /**
     * … where x.firstname like 'xx%'
     * @param firstname
     * @return
     */
    List<Student> findByFirstnameStartingWith(String firstname);

    /**
     * … where x.firstname like '%xx'
     * @param firstname
     * @return
     */
    List<Student> findByFirstnameEndingWith(String firstname);

    /**
     * 等同于like
     * … where x.firstname like '%xx%'
     * @param firstname
     * @return
     */
    List<Student> findByFirstnameContaining(String firstname);

    /**
     * … where x.age = ?1 order by x.lastname desc
     * @param age
     * @return
     */
    List<Student> findByAgeOrderByLastnameDesc(int age);


    /**
     * … where x.lastname <> ?1
     * @param lastname
     * @return
     */
    List<Student> findByLastnameNot(String lastname);

    /**
     * … where x.age in ?1
     *
     * @param ages
     * @return
     */
    List<Student> findByAgeIn(Collection<Integer> ages);

    /**
     * … where x.age not in ?1
     *
     * @param ages
     * @return
     */
    List<Student> findByAgeNotIn(Collection<Integer> ages);


    List<Student> findByAgeNotInAndIdIn(Collection<Integer> ages, List<Integer> ids);

    /**
     * … where x.active = 1
     * @return
     */
    List<Student> findByActiveTrue();

    /**
     * … where x.active = 0
     * @return
     */
    List<Student> findByActiveFalse();

    /**
     * … where UPPER(x.firstname) = UPPER(?1)
     * @param firstname
     * @return
     */
    List<Student> findByFirstnameIgnoreCase(String firstname);


    // 复杂的例子
    List<Student> findByLastnameOrFirstnameAndIdBetweenOrderByAgeDescIdAsc(String lastname, String firstname, int id1, int id2);

}