SpringBoot基础之Spring|SpringBoot基础之Spring Data Jpa SpringBoot基础之SpringDataJpa

今天讲一下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%");

【SpringBoot基础之Spring|SpringBoot基础之Spring Data Jpa】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); }