java和SpringData

这次学习SpringData 可参考 https://www.baeldung.com/spring-data-jpa-query

导入maven

 <dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.20</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

application.properties 进行数据库配置

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=root

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.jpa.show-sql=true

查询规则

简单条件查询: 查询某一个实体类或者集合

按照Spring Data 的规范,查询方法以 find| read| get 开头,

设计条件查询时,条件的属性用条件关键字连接,要注意的是:条件属性以首字母大写。

关键字: And, Or, Between, LessThan, GreaterThan, After, Before, IsNull, IsNotNull,NotNull, StringWith

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)
List<User> getByNameStartingWithAndIdLessThan(String name, Integer id);

SpringData使用@Query两种方式

1、查询基本方式一:
// By default the query definition uses JPQL.
//Let's look at a simple repository method that returns active User entities from the database:

@Query("SELECT u FROM User u WHERE u.status = 1")
Collection<User> findAllActiveUsers();

2、查询基本方式二:
//We can use also native SQL to define our query. All we have to do is to set the value of the nativeQuery attribute to true and define the native SQL query in the value attribute of the annotation:

@Query(
  value = "SELECT * FROM USERS u WHERE u.status = 1", 
  nativeQuery = true)
Collection<User> findAllActiveUsersNative();

3、查询,带参数的方法一:
@Query("SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2")
User findUserByStatusAndName(Integer status, String name);

4、查询,带参数的方法二:
@Query("SELECT u FROM User u WHERE u.status = :status and u.name = :name")
User findUserByStatusAndNameNamedParams(
  @Param("status") Integer status, 
  @Param("name") String name);

5、修改
@Modifying
@Query("update User u set u.status = :status where u.name = :name")
int updateUserSetStatusForName(@Param("status") Integer status, 
  @Param("name") String name);

`需要在调用的Service 上面加事务`
@Transactional(rollbackFor = Exception.class)
public void updateUserName(String name, Integer id) {
    userRepository.updateUserName(name, id);
}

6、插入
@Modifying
@Query(
  value = 
    "insert into Users (name, age, email, status) values (:name, :age, :email, :status)",
  nativeQuery = true)
void insertUser(@Param("name") String name, @Param("age") Integer age, 
  @Param("status") Integer status, @Param("email") String email);

分页

PageRequest pageAble = PageRequest.of(1, 2, Sort.Direction.DESC, "name");
Page<User> page = userRepository.findAll(pageAble);
List<User> content = page.getContent();

for (User user : content) {
    System.out.println(user.getName());
}

Sort.Order name = new Sort.Order(Sort.Direction.DESC, "name");
Sort.Order id = new Sort.Order(Sort.Direction.ASC, "id");
Sort sort = Sort.by(Arrays.asList(name, id));

PageRequest pageAble = PageRequest.of(1, 4, sort);
Page<User> page = userRepository.findAll(pageAble);
List<User> content = page.getContent();
for (User user : content) {
    System.out.println(user.getName());
}

JpaSpecificationExecutor 带查询分页

@Test
public void testFindAllParam() {
    PageRequest pageRequest = PageRequest.of(1, 4);
    Specification<User> specification= new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root,
                                     CriteriaQuery<?> criteriaQuery,
                                     CriteriaBuilder cb) {

            Predicate predicate = cb.gt(root.get("id"), 5);
            return predicate;
        }
    };

    Page<User> page = userRepository.findAll(specification, pageRequest);
    List<User> content = page.getContent();
    for (User user : content) {
        System.out.println(user.getName());
    }
}

如果是多条件则可按如下方法:
Predicate p1=cb.like(root.get(“name”).as(String.class), “%”+uqm.getName()+“%”);

Predicate p2=cb.equal(root.get("uuid").as(Integer.class), uqm.getUuid());

Predicate p3=cb.gt(root.get("age").as(Integer.class), uqm.getAge());

构建组合的Predicate示例:

Predicate p = cb.and(p3,cb.or(p1,p2));