Spring Data JPA 的查询方式

Spring Data JPA的查询方式有很多,下面用示例来挨个介绍一下。

Entity

package com.school1024.spring.boot.data.jpa.model;
 
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
 
import com.school1024.spring.boot.data.jpa.model.type.GenderType;
 
@Entity
@Table(name = "book")
public class BookModel {
 
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
 
    @Column(length = 256, nullable = false)
    private String name;
 
    @Column(length = 256, nullable = false)
    private String author;
 
    @Enumerated(EnumType.STRING)
    @Column(name = "gender", length = 100, nullable = false)
    private GenderType gender;
 
    public Integer getId() {
        return id;
    }
 
    public void setId(Integer id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getAuthor() {
        return author;
    }
 
    public void setAuthor(String author) {
        this.author = author;
    }
 
    public GenderType getGender() {
        return gender;
    }
 
    public void setGender(GenderType gender) {
        this.gender = gender;
    }
 
    @Override
    public String toString() {
        return "BookModel [id=" + id + ", name=" + name + ", author=" + author + ", gender=" + gender + "]";
    }
 
}

DAO

package com.school1024.spring.boot.data.jpa.dao;
 
import java.util.List;
import java.util.Map;
 
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
 
import com.school1024.spring.boot.data.jpa.model.BookModel;
import com.school1024.spring.boot.data.jpa.model.type.GenderType;
 
public interface BookDao extends JpaRepository<BookModel, Integer>, JpaSpecificationExecutor<BookModel> {
 
    List<BookModel> findByGender(GenderType gender);
 
    @Query("FROM BookModel WHERE gender=:gender")
    List<BookModel> find2(@Param("gender") GenderType gender);
 
    @Query("SELECT new Map(id as id, name as name) FROM BookModel WHERE gender=:gender")
    List<Map<String, Object>> find3(@Param("gender") GenderType gender);
 
    @Query(nativeQuery = true, value = "SELECT * FROM book WHERE author=:author")
    List<BookModel> find4(@Param("author") String author);
 
    @Query(nativeQuery = true, value = "SELECT id, concat('《',name,'》') as name, author, gender FROM book WHERE author=:author")
    List<Object[]> find5(@Param("author") String author);
}

通过自带的方法查询

package com.school1024.spring.boot.data.jpa.feature;
 
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
 
import org.junit.Assert;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.jpa.domain.Specification;
 
import com.school1024.spring.boot.data.jpa.SpringBootDataJpaTest;
import com.school1024.spring.boot.data.jpa.dao.BookDao;
import com.school1024.spring.boot.data.jpa.model.BookModel;
import com.school1024.spring.boot.data.jpa.model.type.GenderType;
 
public class QueryTest extends SpringBootDataJpaTest {
 
    @Autowired
    private BookDao dao;
 
    @Test
    public void test() {
        // 通过自带的方法查询
 
        long count = dao.count();
        System.out.println(count);
 
        List<BookModel> result = dao.findAll();
        for (BookModel bookModel : result) {
            System.out.println(bookModel);
        }
    }
 
}

这些方法是Spring Data自带的,一些常见的简单操作都可以完成。

2
BookModel [id=1, name=张三疯自传, author=张三, gender=MALE]
BookModel [id=2, name=武大烧饼烹饪技巧, author=武大, gender=MALE]

这是上面的输出内容,接下来的示例就用这2条数据

通过方法命名查询

DAO接口中定义一个方法

List<BookModel> findByGender(GenderType gender);

测试用例

@Test
public void test1() {
    List<BookModel> result = dao.findByGender(GenderType.MALE);
    Assert.assertTrue(result.size() > 0);
}

通过对方法的命名来完成查询,方法名里指定属性和判断方法,默认是等于,命名规则可以查看官方文档,里面很详细,一般的查询都可以实现。这种方式有几个弊端,如果条件过多,方法名会很长,有些查询条件不是必填的,不好处理。下面的方式可以解决第一个问题,条件不是必填的可以用Specification处理,后面有介绍。

通过@Query注解查询

注解里要写JPQL,或者原生的SQL。

@Query("FROM BookModel WHERE gender=:gender")
List<BookModel> find2(@Param("gender") GenderType gender);

测试用例

@Test
public void test2() {
    List<BookModel> rs = dao.find2(GenderType.MALE);
    Assert.assertTrue(rs.size() > 0);
}

有时候你想写个复杂的查询,返回结果并不是Entity,这时候你还可以定义个Map

@Query("SELECT new Map(id as id, name as name) FROM BookModel WHERE gender=:gender")
List<Map<String, Object>> find3(@Param("gender") GenderType gender);

示例

@Test
public void test3() {
    List<Map<String, Object>> rs = dao.find3(GenderType.MALE);
    for (Map<String, Object> map : rs) {
        System.out.println(map);
    }
}

如果JPQL解决不了,你一定要写原生SQL也可以,但这种方式不推荐。需要设置nativeQuery=true。

@Query(nativeQuery = true, value = "SELECT * FROM book WHERE author=:author")
List<BookModel> find4(@Param("author") String author);

示例

@Test
public void test4() {
    List<BookModel> rs = dao.find4("张三");
    for (BookModel bookModel : rs) {
        System.out.println(bookModel);
    }
}

上面是比较好的情况,返回值可以映射到一个Entity上,如果返回值是一个自定义的列,根本匹配不了,可以通过下面的方式

@Query(nativeQuery = true, value = "SELECT id, concat('《',name,'》') as name, author, gender FROM book WHERE author=:author")
List<Object[]> find5(@Param("author") String author);

示例

@Test
public void test6() {
    List<Object[]> rs = dao.find5("张三");
    for (Object[] objects : rs) {
        for (Object object : objects) {
            System.out.println(object);
        }
    }
}

如果你的查询条件比较多,而且又都是选填的,那可以用Specification查询

通过Specification查询

示例

@Test
public void test7() {
    List<BookModel> rs = dao.findAll(new Specification<BookModel>() {
        @Override
        public Predicate toPredicate(Root<BookModel> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            List<Predicate> list = new ArrayList<Predicate>();
 
            list.add(cb.like(root.get("name").as(String.class), "张" + "%"));
 
            list.add(cb.equal(root.get("gender").as(GenderType.class), GenderType.MALE));
 
            Predicate[] p = new Predicate[list.size()];
            return cb.and(list.toArray(p));
        }
    });
 
    for (BookModel bookModel : rs) {
        System.out.println(bookModel);
    }
}

Specification是最后的杀手锏,这里能完成所有的查询操作。

很久以前用Hibernate的时候,用过Example查询,这对于一些简单的根据某些属性查询来说,真的特别方便,Spring Data同样支持。

通过Example查询

@Test
public void test8() {
    // 通过Example查询
    BookModel model = new BookModel();
    model.setAuthor("张三");
 
    List<BookModel> rs = dao.findAll(Example.of(model));
    for (BookModel bookModel : rs) {
        System.out.println(bookModel);
    }
}

你可以设置实体的多个属性,这些都会被添加到where里。

除了以上,还可以通过QueryDSL查询,这个后面单独介绍。

以上所有代码都在GitHub中:https://github.com/ckwen/school1024-java/tree/master/spring-boot/spring-boot-data-jpa

原文 

http://www.school1024.com/spring/spring-data-jpa-query-example.html

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » Spring Data JPA 的查询方式

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址