SpringBoot基础之Spring Data Jpa

今天讲一下Spring Data Jpa的基础写法,实际上很easy,很多语句都可以用DSL来替换,很有意思。

在Springboot启动类加上@EnableJpaRepositories可以直接允许使用Spring Data Jpa
下面这个是常见的Jpa的Repository的写法

public interface LocationJpaRepository extends JpaRepository {
}

Entity配置,@Id表示主键,@GeneratedValue(strategy = GenerationType.AUTO)表示自增长,@Column表示字段名称
@OneToMany表示一对多的关系

@Entity
@Table(name = "APPLICATION")
public class Application {
    public Application() { }
    public Application(String name, String description, String owner) {
        this.name = name;
        this.description = description;
        this.owner = owner;
    }
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "application_id")
    private Integer id;
    @Column(name = "app_name", nullable = false)
    private String name;
    @Column(length = 2000)
    private String description;
    private String owner;
    @Override
    public String toString() {
        return "Application{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", description='" + description + '\'' +
                ", owner='" + owner + '\'' +
                '}';
    }
}

@OneToMany需要有@ManyToOne对应,其中会有ManyToOne的表Exercise中增加外键索引,application_application_id,另外FetchType.LAZY表示只有在通过getExercises时才去表中查询,如果是FetchType.EAGER表示获取到application时就要同时去表中查询exercises

public class Application {
    @OneToMany(mappedBy = "goal", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List exercises = new ArrayList();
}
public class Exercise {
    @ManyToOne
    private Application application;
}

增删改查操作CRUD

location loc;
Long id;
//save
locationJpaRepository.saveAndFlush(loc);//或者save
//update
locationJpaRepository.saveAndFlush(loc);//或者save
//delete
locationJpaRepository.delete(loc);
//find
locationJpaRepository.findOne(id);

DSL,其中State必须与Location中的字段完全一致即可直接使用模糊查询方法

List findByStateLike(String stateName);
//执行时需要增加like需要的%
locationJpaRepository.findByStateLike("dfdf%");

DSL中有很多关键字

1. findBy表示通过By后面的字段来查询
2. And与SQL中的and意义相同,findByStateAndCity(String value1, String value2),等同SQL语句为:where a.state = ? and a.city = ?
3. Or与SQL中的or意义相同,findByStateOrCity(String value1, String value2),等同SQL语句为:where a.state = ? or a.city = ?
4. Equals和不带任何参数的意义相同,findByStateEquals(String value),等同SQL语句为:where a.state = ?
5. Is和equals意义相同,findByStateIs(String value),等同SQL语句为:where a.state = ?
6. Not与SQL中的<>意义相同,findByStateIs(String value),等同SQL语句为:where a.state <> ?
7. Like与SQL中的like意义相同,findByStateLike("New%"),等同SQL语句为:where a.state like ?
8. NotLike与SQL中的not like意义相同,findByStateNotLike("New%"),等同SQL语句为:where a.state not like ?
9. StartingWith与SQL中的like意义相同,findByStateStartingWith("New"),等同SQL语句为:where a.state not like '%?'
10. EndingWith与SQL中的like意义相同,findByStateEndingWith("New"),等同SQL语句为:where a.state not like '?%'
11. Containing与SQL中的like意义相同,findByStateContaining("New"),等同SQL语句为:where a.state not like '%?%'
12. LessThan与SQL中的<意义相同,findByPriceLessThan(BigDemical big),等同SQL语句为:where a.price < ?
13. LessThanEqual与SQL中的<=意义相同,findByPriceLessThanEqual(BigDemical big),等同SQL语句为:where a.price <= ?
14. GreaterThan与SQL中的>意义相同,findByPriceGreaterThan(BigDemical big),等同SQL语句为:where a.price > ?
15. GreaterThanEqual与SQL中的>=意义相同,findByPriceGreaterThanEqual(BigDemical big),等同SQL语句为:where a.price >= ?
16. Before与SQL中比较日期的<意义相同,findByFoundedDateBefore(Date date),等同SQL语句为:where a.date < ?
17. After与SQL中比较日期的>意义相同,findByFoundedDateAfter(Date date),等同SQL语句为:where a.date > ?
18. Between与SQL中比较日期的between and意义相同,findByFoundedDateBetween(Date date1,Date date2),
等同SQL语句为:where a.date between ? and ?
19. True与SQL中比较Boolean的=true意义相同,findByActiveTrue(),等同SQL语句为:where a.active = true
20. False与SQL中比较Boolean的=false意义相同,findByActiveFalse(),等同SQL语句为:where a.active = false
21. IsNull与SQL中比较的is null意义相同,findByActiveIsNull(),等同SQL语句为:where a.active is null
22. IsNotNull与SQL中比较的is not null意义相同,findByActiveIsNotNull(),等同SQL语句为:where a.active is not null
23. NotNull与SQL中比较的is not null意义相同,findByActiveNotNull(),等同SQL语句为:where a.active is not null
24. In与SQL中比较的in意义相同,findByStateIn(Collection states),等同SQL语句为:where a.state in ?
25. NotIn与SQL中比较的not in意义相同,findByStateNotIn(Collection states),等同SQL语句为:where a.state not in ?
其中有一个特殊的情况,Model类其中组合了ModelType类,因此可以通过ModelType来获取Model,List models = findByModelTypeNameIn(List modelTypeName)
26. IgnoreCase,findByStateIgnoreCase(String state),等同SQL语句为:where UPPER(a.state) = UPPER(?)
27. StartingWithIgnoreCase,findByStateStartingWithIgnoreCase(String state),等同SQL语句为:where UPPER(a.state) like UPPER('?%')
28. OrderByCountryAsc,findByStateOrderByCountryAsc(String state) ,等同于SQl语句为:whrere a.state = ? order by a.country asc
29. OrderByCountryDesc,findByStateOrderByCountryDesc(String state),等同于SQl语句为:whrere a.state = ? order by a.country desc
30. First,findFirstByState(String state),等同于SQl语句为:whrere a.state = ? limit 1
31. Top5,findTop5ByState(String state),等同于SQl语句为:whrere a.state = ? limit 5
32. Distinct,findDistinctManufacturerByState(String state),等同于SQl语句为:select discint manufacturer *** whrere a.state = ?

不实用DQL可以通过@Query注解自定义JPQL语句,@Param指定参数对应JPQL中的:后边的参数

@Query("select m from Model m where m.price >= :lowest and m.price <= :highest and m.woodType like :wood ")
List queryByPriceRangeAndWoodType(@Param("lowest")BigDecimal lowest,
                                         @Param("highest")BigDecimal high,
                                         @Param("wood")String wood);

通过Entity的NamedQuery方法定义的JPQL语句可以在ModelJpaRepository中直接实现,需要传入制定的参数

@Entity
@NamedQuery(name="Model.findAllModelsByType", query="select m from Model m where m.modelType.name = :name")
public class Model {
}
@Repository
public interface ModelJpaRepository extends JpaRepository {
    public List findAllModelsByType(@Param("name") String name);
}

通过Entity的NamedNativeQuery方法定义的原生SQL语句可以在ModelJpaRepository中直接实现,需要传入制定的参数

@Entity
@NamedNativeQuery(name = "Manufacturer.getAllThatSellAcoustics", 
      query = "SELECT m.id, m.name, m.foundedDate, m.averageYearlySales, m.location_id as headquarters_id "
       + "FROM Manufacturer m "
      + "LEFT JOIN Model mod ON (m.id = mod.manufacturer_id) "
      + "LEFT JOIN ModelType mt ON (mt.id = mod.modeltype_id) "
       + "WHERE (mt.name = ?)", resultClass = Manufacturer.class)
public class Manufacturer {
}
@Repository
public interface ManufacturerJpaRepository extends JpaRepository {
    List getAllThatSellAcoustics(String name);
}

所有带@Query的都是需要原生SQL处理的,而默认不带@Query的都是DSL标准的方法,如果使用原生SQL,需要增加nativeQuery = true
分页方法,需要在Repository的方法中增加Pageable方法

@Repository
public interface ModelJpaRepository extends JpaRepository {
    @Query("select m from Model m where m.price >= :lowest and m.price <= :highest and m.woodType like :wood ")
    Page queryByPriceRangeAndWoodType(@Param("lowest")BigDecimal lowest,
                                             @Param("highest")BigDecimal high,
                                             @Param("wood")String wood,
                                             Pageable page);
}
//service方法,Sort为排序方式,创建PageRequest,第一个为页数,第二个每页展示个数,第三个是排序方式
public Page queryByPriceRangeAndWoodType(BigDecimal lowest,BigDecimal highest, String wood){
   Sort sort = new Sort(Sort.Direction.ASC, "name");
   Pageable page = new PageRequest(0,2, sort);
   return locationJpaRepository.queryByPriceRangeAndWoodType(lowest, highest, wood,page);
}

锁,有几种情况的锁,乐观悲观等

@Repository
public interface ModelJpaRepository extends JpaRepository {
    @Lock(LockModeType.PESSIMISTIC_WRITE)
    public List findAllModelsByType(@Param("name") String name);
}

你可能感兴趣的