SpringBoot+Jpa+Hibernate（基础使用、原生SQL、分页查询、动态SQL）

一、pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.sid</groupId>
    <artifactId>springboot-jpa-hibernate</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.4.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.27</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

二、application.yml

2.1 hikari

数据源，类似于commons-dbcp,tomcat,c3po,druid。

spring boot2.0已经将HikariCP做为了默认的数据源链接池。

2.2 ddl-auto常用有几种

create：每次加载hibernate时都会删除上一次的生成的表，然后根据model类生成表。

即使这张表没有任何改变也会删了重建，这样原来的表里面的数据就没了。

create-drop：每次加载hibernate时根据model类生成表，但是sessionFactory一关闭,表就自动删除。

update：通常使用这个，第一次加载hibernate时根据model类会自动创建表结构，之后每次加载hibernate时根据model类自动更新表结构。只是更新表结构，不会删除原来的表里面的数据。

validate：每次加载hibernate时，验证创建数据库表结构，不会创建新表。

none:什么都不做 

2.3 show-sql

是否在控制台打印执行的SQL语句 

spring:
  datasource: #通用数据源配置
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/sid?charset=utf8mb4&useSSL=false
    username: root
    password: Liyijie331
    hikari: # Hikari 数据源专用配置
      maximum-pool-size: 20
      minimum-idle: 5
  jpa: # JPA 相关配置
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect #在建表的时候，将默认的存储引擎切换为 InnoDB 用的。
    show-sql: true
    hibernate:
      ddl-auto: update

三、model

package com.sid.model;

import javax.persistence.*;

@Entity
@Table(name = "student")
public class Student {

    @Id
    @GeneratedValue
    private Long id;

    @Column(nullable = false,length = 20)
    private String name;

    @Column(nullable = false,length = 8)
    private Integer age;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
}

四、dao

4.1继承了JpaRepository后会有很多自带的save、delete、find方法

4.2 Spring-data-jpa有一个特点，可以通过解析方法名字来自动生成SQL查询语句，不适合delete insert update，不适合动态查询

基础语法

表达式	例子	hql查询语句
And	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstname,findByFirstnameIs,findByFirstnameEqual	… 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)

4.3 也可以自己在@Query标签中写查询语句，注意，nativeQuery 是询问是否使用原生sql语句，默认nativeQuery为false，这里写的Student是model类名

4.4 在@Query标签中使用原生SQL查询，nativeQuery设置为true，那么这里写的student是表名

package com.sid.dao;

import com.sid.model.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentDao extends JpaRepository<Student, Long> {

     //执行自己的sql语句，nativeQuery=true不映射
    @Query(value = "select * from student where name=:name",nativeQuery = true)
    List<Student> findNativeQueryTrue(@Param("name") String name);

    @Query(value = "select s from Student s where name= ?1")
    List<Student> findQuery(String name);

    //JPA自动根据方法名字来生成SQL语句
    List<Student> findByAge(Integer age);

    // 分页查询
    @Query(value = "select s from Student s where s.age=:age")
    Page<Student> findByAgePageable(@Param("age") Integer age, Pageable pageable);

    //分页查询 + 动态条件
    @Query(value = "select s from Student s where (:age=null or s.age=:age)")
    Page<Student> findPageIfAgeNotNull(@Param("age") Integer age, Pageable pageable);

    //分页查询 + 动态条件 + nativeQuery = true
    @Query(value = "select * from student s where (?1=null or s.age=?1) order by ?#{#pageable}",nativeQuery = true)
    Page<Student> findPageIfAgeNotNullNativeQuery(Integer age, Pageable pageable);
}

service

分页查询，动态SQL

EntityManager、Example、Specification

package com.sid.service.impl;

import com.sid.dao.StudentDao;
import com.sid.model.Student;
import com.sid.service.StudentService;
import org.springframework.data.domain.*;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

@Service
public class StudentServiceImpl implements StudentService {

    @Resource
    StudentDao studentDao;

    // 分页查询
    public Page<Student> findByAgePageable(Integer age, Pageable pageable) {
        return studentDao.findByAgePageable(age, pageable);
    }
    //分页查询 + 动态条件
    public Page<Student> findPageIfAgeNotNull(Integer age, Pageable pageable) {
        return studentDao.findPageIfAgeNotNull(age, pageable);
    }
    //分页查询 + 动态条件 + nativeQuery = true
    public Page<Student> findPageIfAgeNotNullNativeQuery(Integer age, Pageable pageable) {
        return studentDao.findPageIfAgeNotNullNativeQuery(age,pageable);
    }

    //    @Autowired
    @PersistenceContext
    EntityManager em;
    /**JPA的Criteria API*/
    public List<Student> findEntityManagerPage(String name) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Student> query = cb.createQuery(Student.class);
        Root<Student> root = query.from(Student.class); //from Student s
        query.select(root); // select s
        //where
        Predicate p1 = cb.equal(root.get("name"),name);
        query.where(p1);
        List<Student> userInfos = em.createQuery(query).getResultList();
        return userInfos;
    }

    /**动态条件查询+分页 实现方式：Example*/
    public Page<Student> findExamplePage(String name) {
        Pageable pageable =  PageRequest.of(1, 2, Sort.Direction.ASC, "id");
        Student s = new Student();
        s.setName(name);
        ExampleMatcher matcher = ExampleMatcher.matching()
                .withMatcher("name" ,ExampleMatcher.GenericPropertyMatchers.contains());//全部模糊查询，即%{address}%
                //.withIgnorePaths("password");//忽略字段，即不管password是什么值都不加入查询条件
        Example<Student> example = Example.of(s ,matcher);
        Page<Student> result = studentDao.findAll(example,pageable);
        return result;
    }

    /**动态条件查询+分页 实现方式：DAO层接口实现JpaSpecificationExecutor<T>接口*/
    public Page<Student> findSpecificationPage(String name) {
        Pageable pageable =  PageRequest.of(1, 2, Sort.Direction.ASC, "id");

        Specification<Student> specification = new Specification<Student>() {
            @Override
            public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
                List<Predicate> predicatesList  = new ArrayList<Predicate>();

                if (!StringUtils.isEmpty(name)) {
                    predicatesList .add(cb.like(root.get("name").as(String.class), "%" + name + "%"));
                }
                //criteriaQuery.orderBy(cb.asc(root.get("id")));
                Predicate[] predicates = new Predicate[predicatesList.size()];
                return cb.and(predicatesList .toArray(predicates));
            }
        };

        //DAO层接口实现JpaSpecificationExecutor<T>接口
        Page<Student> students = studentDao.findAll(specification, pageable);

        return students;

    }

    @Override
    public Long add() {
        Student s = new Student();
        s.setName("Sid");
        s.setAge(18);
        Student save = studentDao.save(s);
        return save.getId();
    }

    @Override
    public void update(Long id) {
        Optional<Student> s = studentDao.findById(id);
        Student old = s.get();
        old.setName("sid2");
        studentDao.save(old);
    }

    @Override
    public void delete(Long id) {
        studentDao.deleteById(id);
    }

    @Override
    public List<Student> findByAge(Integer age) {
        List<Student> byAge = studentDao.findByAge(age);
        return byAge;
    }

    public List<Student> findNativeQueryTrue(String name){
        List<Student> nativeQueryTrue = studentDao.findNativeQueryTrue(name);
        return nativeQueryTrue;
    }

    public List<Student> findQuery(String name){
        List<Student> nativeQueryTrue = studentDao.findQuery(name);
        return nativeQueryTrue;
    }


}