5.Spring Boot Data JPA

[作者信息] github: https://github.com/MiniPa email: eric.fourpackcn@outlook.com

[项目代码：https://github.com/MiniPa/spring_boot_demos]

===============================================================================

   

[spring data jpa]

JPA(Java Persistence API) 一套规范 简化现有的持久化开发工作和整合ORM技术 spring data jpa Spring 基于 ORM 框架、JPA 规范的基础上封装的一套JPA应用框架 供了包括增删改查等在内的常用功能，且易于扩展

[基本查询]

1.预先生成了一些基本的CURD的方法 增、删、改 1.1 继承 public interface UserRepository extends JpaRepository<User, Long> { } 1.2使用 @Test public void testBaseQuery() throws Exception { User user=new User(); userRepository.findAll(); userRepository.findOne(1l); userRepository.save(user); userRepository.delete(user); userRepository.count(); userRepository.exists(1l); // ... }     2.自定义简单查询 根据方法名来自动生成SQL 2.1基本语法 findXXBy,readAXXBy,queryXXBy,countXXBy, getXXBy User findByUserName(String userName); User findByUserNameOrEmail(String username, String email); 2.2 SQL关键词 文章底部有关键词(太罗嗦放最下面) 例如：LIKE、 IgnoreCase、 OrderBy List<User> findByEmailLike(String email); User findByUserNameIgnoreCase(String userName); List<User> findByUserNameOrderByEmailDesc(String email);

[复杂查询 --- 分页、删选、连表等查询]

1.分页 Page<User> findALL(Pageable pageable); Page<User> findByUserName(String userName,Pageable pageable); // Pageable建议做为最后一个参数传入 @Test public void testPageQuery() throws Exception { int page=1,size=10; Sort sort = new Sort(Direction.DESC, "id"); Pageable pageable = new PageRequest(page, size, sort); userRepository.findALL(pageable); userRepository.findByUserName("testName", pageable); }     2.限制查询 -- 查询前N个元素，或者支取前一个实体 ser findFirstByOrderByLastnameAsc(); User findTopByOrderByAgeDesc(); Page<User> queryFirst10ByLastname(String lastname, Pageable pageable); List<User> findFirst10ByLastname(String lastname, Sort sort); List<User> findTop10ByLastname(String lastname, Pageable pageable);     3.自定义SQL查询 3.1注解 @Query @Modifying @Transactional @Modifying @Query("update User u set u.userName = ?1 where u.id = ?2") int modifyByIdAndUserId(String userName, Long id);     @Transactional @Modifying @Query("delete from User where id = ?1") void deleteByUserId(Long id);   @Transactional(timeout = 10) @Query("select u from User u where u.emailAddress = ?1") User findByEmailAddress(String emailAddress);     4.多表查询 4.1第一种是利用hibernate的级联查询来实现 4.2第二种是创建一个结果集的接口来接收连表查询后的结果 4.2.1 定义一个结果集的接口类 public interface HotelSummary { City getCity(); String getName(); Double getAverageRating(); default Integer getAverageRatingRounded() { return getAverageRating() == null ? null : (int) Math.round(getAverageRating()); } } 4.2.2 查询的方法返回类型设置为新创建的接口 @Query("select h.city as city, h.name as name, avg(r.rating) as averageRating " - "from Hotel h left outer join h.reviews r where h.city = ?1 group by h") Page<HotelSummary> findByCity(City city, Pageable pageable);     @Query("select h.name as name, avg(r.rating) as averageRating " - "from Hotel h left outer join h.reviews r group by h") Page<HotelSummary> findByCity(Pageable pageable); 4.2.3 使用 Page<HotelSummary> hotels = this.hotelRepository.findByCity(new PageRequest(0, 10, Direction.ASC, "name")); for(HotelSummary summay:hotels){ System.out.println("Name" +summay.getName()); }

[多数据源支持]

一、同源数据库的多源支持 1 配置多数据源 2 不同源的实体类放入不同包路径 3 声明不同的包路径下使用不同的数据源、事务支持 blog: spring boot 多数据源支持   二、异构数据库多源支持 1.不同的数据源使用不同的实体 @Entity 关系型数据库支持类型 @Document 为mongodb支持类型 interface PersonRepository extends Repository<Person, Long> { …} @Entity public class Person { …}     interface UserRepository extends Repository<User, Long> { …} @Document public class User { …}     混合使用 interface JpaPersonRepository extends Repository<Person, Long> { …} interface MongoDBPersonRepository extends Repository<Person, Long> { …}     @Entity @Document public class Person { …} 2.对不同的包路径进行声明 @EnableJpaRepositories(basePackages = "com.neo.repositories.jpa") @EnableMongoRepositories(basePackages = "com.neo.repositories.mongo") interface Configuration { }

[其他]

1.使用枚举 希望数据库中存储的是枚举对应的String类型，而不是枚举的索引值 在属性上面添加        @Enumerated(EnumType.STRING) 注解 @Enumerated(EnumType.STRING) @Column(nullable = true) private UserType type; 2.不需要和数据库映射的属性@Transient @Transient private String userName;

   

[SQL Key]

Keyword Sample JPQL snippet And findByLastnameAndFirstname … where x.lastname = ?1 and x.firstname = ?2 Or findByLastnameOrFirstname … where x.lastname = ?1 or x.firstname = ?2 Is,Equals 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 findByAgeIsNull … 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 (parameter bound with appended %) EndingWith findByFirstnameEndingWith … where x.firstname like ?1 (parameter bound with prepended %) Containing findByFirstnameContaining … where x.firstname like ?1 (parameter bound wrapped in %) OrderBy findByAgeOrderByLastnameDesc … where x.age = ?1 order by x.lastname desc Not findByLastnameNot … where x.lastname <> ?1 In findByAgeIn(Collection ages) … where x.age in ?1 NotIn findByAgeNotIn(Collection age) … where x.age not in ?1 TRUE findByActiveTrue() … where x.active = true FALSE findByActiveFalse() … where x.active = false IgnoreCase findByFirstnameIgnoreCase … where UPPER(x.firstame) = UPPER(?1)