SpringBoot2 - SpringDataJPA

构建项目

1. 访问地址：http://start.spring.io
2. 添加Web、MySQL、JPA依赖

使用Druid数据源

1. pom.xml添加Druid依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.9</version>
</dependency>

2. 添加application.yml配置文件

spring:
 datasource:
  type: com.alibaba.druid.pool.DruidDataSource
  driver-class-name: com.mysql.jdbc.Driver
  url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8
  username: root
  password: 123456
  #配置监控统计拦截的filters
  filters: stat,wall,log4j
  #最大活跃数
  maxActive: 20
  #初始化数量
  initialSize: 1
  #最大连接等待超时时间
  maxWait: 60000
  #打开PSCache，并指定每个连接PSCache的大小
  poolPreparedStatements: true
  maxPoolPreparedStatementPerConnectionSize: 20
  #通过connectionProperties属性打开mergeSql功能
  connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
  minldle: 1
  timeBetweenEvictionRunsMillis: 60000
  minEvictableldleTimeMillis: 300000
  validationQuery: select 1 from dual
  testWhiledle: true
  testOnBorrow: false
  testOnReturn: false
 jpa:
  properties:
   hibernate:
    show_sql: true
    format_sql: true

建表

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) DEFAULT NULL COMMENT '名字',
  `password` varchar(50) DEFAULT NULL COMMENT '密码',
  `age` int(11) DEFAULT '0' COMMENT '年龄',
  `address` varchar(200) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建实体类

package com.gala.springdataJPA.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "t_user")
public class User {

    private Long id;

    private String name;

    private String password;

    private Integer age;

    private String address;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    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 String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getAge() {
        return age;
    }

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

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

创建数据接口

@Query是配置自定义SQL的注解，参数nativeQuery = true表明使用原生的sql，如果不配置，默认是false，则使用HQL查询方式。

@Query注解配合@Modifying注解同时使用，可以完成数据的删除、添加、更新操作。

package com.gala.springdataJPA.jpa;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.gala.springdataJPA.entity.User;

@Transactional
public interface UserDao extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

    @Query(value = "select * from t_user where age > ?1", nativeQuery = true)
    public List<User> findUserByAge(Integer age);

    @Modifying
    @Query(value = "delete from t_user where name = ?1", nativeQuery = true)
    public void deleteUserByName(String name);
}

创建分页对象

package com.gala.springdataJPA.base;

import java.io.Serializable;

public class PageInfo implements Serializable {

    private static final long serialVersionUID = -1210826608201795162L;

    // 分页页码，默认第一页
    protected Integer pageNo = 1;
    // 分页每页条数，默认20条
    protected Integer pageSize = 20;
    // 总记录数
    protected Integer total = 0;

    public PageInfo() {
        super();
    }

    public PageInfo(Integer pageNo, Integer pageSize) {
        super();
        this.pageNo = pageNo;
        this.pageSize = pageSize;
    }

    public PageInfo(Integer pageNo, Integer pageSize, Integer total) {
        super();
        this.pageNo = pageNo;
        this.pageSize = pageSize;
        this.total = total;
    }

    /**
     * 设置当前页号
     */
    public void setPageNo(Integer pageNo) {
        this.pageNo = pageNo;
    }

    /**
     * 设置每页包含的纪录数
     */
    public void setPageSize(Integer size) {
        this.pageSize = size;
    }

    /**
     * 获取当前页号
     */
    public Integer getPageNo() {
        return this.pageNo;
    }

    /**
     * 获取每页包含的纪录数
     */
    public Integer getPageSize() {
        return this.pageSize;
    }

    /**
     * 获取总记录数
     */
    public Integer getTotal() {
        return this.total;
    }

    /**
     * 获取总页数
     */
    public int getTotalPage() {
        int tpage = total % pageSize;
        int totalPage = total / pageSize;
        if (tpage != 0) {
            totalPage += 1;
        }
        return totalPage;
    }
}

创建控制器

package com.gala.springdataJPA.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.gala.springdataJPA.base.PageInfo;
import com.gala.springdataJPA.entity.User;
import com.gala.springdataJPA.jpa.UserDao;

@RestController
public class UserController {

    @Autowired
    private UserDao userDao;

    /**
     * 获取用户
     */
    @RequestMapping(value = "/list")
    public List<User> list() {
        return userDao.findAll();
    }

    /**
     * 添加用户
     */
    @RequestMapping(value = "/add")
    public String add() {
        User user = new User();
        user.setName("用户1");
        user.setPassword("123456");
        user.setAge(33);
        user.setAddress("北京");

        userDao.save(user);
        return "添加成功";
    }

    /**
     * 删除用户
     */
    @RequestMapping(value = "/delete")
    public String delete(Long id) {
        userDao.deleteById(id);
        return "删除成功";
    }

    /**
     * 根据年龄过滤用户
     */
    @RequestMapping(value = "/findUser")
    public List<User> findUser() {
        return userDao.findUserByAge(5);
    }

    /**
     * 根据名称删除用户
     */
    @RequestMapping(value = "/deleteUser")
    public String deleteUser() {
        userDao.deleteUserByName("用户1");
        return "删除成功";
    }

    /**
     * 分页查询
     */
    @RequestMapping(value = "/pageUser")
    public List<User> pageUser() {
        PageInfo page = new PageInfo();
        page.setPageNo(1);
        page.setPageSize(2);

        PageRequest pageRequest = buildPageRequest(page.getPageNo(), page.getPageSize(), "auto");

        // 执行分页查询
        Page<User> users = userDao.findAll(pageRequest);
        return users.getContent();
    }

    /**
     * 创建分页排序请求
     */
    private PageRequest buildPageRequest(Integer pageNo, Integer pagzSize, String sortType) {
        Sort sort = null;
        if ("auto".equals(sortType)) {
            sort = new Sort(Direction.DESC, "id");
        }
        return PageRequest.of(pageNo - 1, pagzSize, sort);
    }

}

启动项目，测试

1. 查询用户表所有数据 http://127.0.0.1:8080/list

2. 添加数据 http://127.0.0.1:8080/add

3. 根据ID删除数据 http://127.0.0.1:8080/delete?id=1

4. 根据年龄过滤用户 http://127.0.0.1:8080/findUser

5. 根据姓名删除用户 http://127.0.0.1:8080/deleteUser

6. 分页排序用户数据 http://127.0.0.1:8080/pageUser