spring boot整合jpa实现CRUD、分页、排序等功能

一、使用IDE新建一个spring boot项目：

File --> Project
选择JDK版本
自己写好包名，填写Group和Artifact
版本默认，选择web复选框
填好项目名和路径
这几个可以的删掉

这个是我pom里的maven依赖：

<?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.hyw</groupId>
    <artifactId>lhrz</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <!--默认为jar方式-->
    <!--<packaging>jar</packaging>-->
    <!--改为war方式,部署-->
    <packaging>war</packaging>

    <name>lhrz</name>
    <description>Demo project for Spring Boot</description>

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

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- mysql依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>
        <!-- durid连接池 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.28</version>
        </dependency>
        <!--JPA-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.1.0.RELEASE</version>
        </dependency>
        <!-- alibaba fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.31</version>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.20</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
            <!--打包的时候可以不用包进去，别的设施会提供。事实上该依赖理论上可以参与编译，测试，运行等周期。
        相当于compile，但是打包阶段做了exclude操作-->
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
    <!-- 使用alibaba远程仓库 -->
    <repositories>
        <repository>
            <id>public</id>
            <name>aliyun nexus</name>
            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
            <releases>
                <enabled>true</enabled>
            </releases>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>public</id>
            <name>aliyun nexus</name>
            <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
            <releases>
                <enabled>true</enabled>
            </releases>
            <snapshots>
                <enabled>false</enabled>
            </snapshots>
        </pluginRepository>
    </pluginRepositories>
</project>

我们新建的项目默认配置文件是叫 application.properties，我们这里给他改名为 application.yml; 因本人习惯，喜欢分开配置，所以建了两个yml文件

这是我的application.yml文件

server:
  #端口号
  port: 80 
 #项目路径，可以不填
  servlet:
    context-path: /jpaTest
spring:
  profiles:
    #在这里配置需要加载哪个配置文件
    active: dev

 #jpa
  jpa:
    database: MySQL
    #每个SQL查询显示或不记录
    show-sql: false
    #格式化sql
    properties:
      hibernate:
        format_sql: true
        #use_sql_comments: true
        #hibernate:
        #自动建表
        #ddl-auto: update
        #hibernate:
        #naming:
        #implicit-strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy

这是我的application-dev.yml文件

#mysql和druid配置:
spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: com.mysql.jdbc.Driver
    username: test
    password: 123456
    url: jdbc:mysql://127.0.0.1:3306/jpatest?useUnicode=true&characterEncoding=utf8
    # 下面为连接池的补充设置，应用到上面所有数据源中
    # 初始化大小，最小，最大
    initialSize: 10
    minIdle: 10
    maxActive: 200
    # 配置获取连接等待超时的时间
    maxWait: 60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    timeBetweenEvictionRunsMillis: 60000
    # 配置一个连接在池中最小生存的时间，单位是毫秒
    minEvictableIdleTimeMillis: 30000
    validationQuery: select 'x'
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    # 打开PSCache，并且指定每个连接上PSCache的大小
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,slf4j
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
    # 合并多个DruidDataSource的监控数据
    useGlobalDataSourceStat: true

二、entity实体类（可用jpa工具生成）

package com.hyw.jpatest.model;

import lombok.Data;

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

/**
 * @author huyw
 * @date 2018/11/12
 */
@Data
@Entity(name = "user")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String mobile;

    private String password;

    private String headPic;

    private Integer type;

    private String region;

    private Integer isDel;
}

三、dao层

package com.hyw.jpatest.mapper;

import com.hyw.jpatest.model.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author
 * @date 2018/11/09 18:09
 */
public interface UserMapper extends CrudRepository<User, Long> {

    /**
     * 根据id获取用户信息
     *
     * @param id
     * @return User
     */
    User getUserById(Long id);

    /**
     * 分页查询
     *
     * @param pageable
     * @return Page
     */
    Page<User> findAll(Pageable pageable);

    /**
     * 根据id删除
     *
     * @param id
     */
    void deleteById(Long id);

    /**
     * 插入用户
     *
     * @param user
     * @return User
     */
    User save(User user);

    /**
     * 修改用户
     *
     * @param id
     * @param mobile
     * @return int
     */
    @Transactional(rollbackFor = Exception.class)
    @Modifying
    @Query(value = "update user set mobile = :mobile where id = :id", nativeQuery = true)
    int updateMobileById(@Param("id") Long id, @Param("mobile") String mobile);
}

四、service层

package com.hyw.jpatest.service;

import com.hyw.jpatest.model.User;
import com.hyw.jpatest.result.PageResultEntity;
import com.hyw.jpatest.result.ResultEntity;

/**
 * @author
 * @date 2018/11/09 18:09
 */
public interface UserService {

    /**
     * 根据id获取用户信息
     *
     * @param id 主键
     * @return ResultEntity
     */
    ResultEntity getUserById(Long id);

    /**
     * 分页查询
     *
     * @param pageNo   页码
     * @param pageSize 页数
     * @return PageResultEntity
     */
    PageResultEntity findAll(Integer pageNo, Integer pageSize);

    /**
     * 根据id删除
     *
     * @param id
     * @return ResultEntity
     */
    ResultEntity deleteUserById(Long id);

    /**
     * 插入用户
     * @param user
     * @return ResultEntity
     */
    ResultEntity saveUser(User user);

    /**
     * 修改用户
     * @param id
     * @param name
     * @return ResultEntity
     */
    ResultEntity updateMobileById(Long id, String name);
}

五、Impl

package com.hyw.jpatest.service.impl;

import com.hyw.jpatest.mapper.UserMapper;
import com.hyw.jpatest.model.User;
import com.hyw.jpatest.result.PageResultEntity;
import com.hyw.jpatest.result.ResultEntity;
import com.hyw.jpatest.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author huyw
 * @create 2018/11/9
 * @since 1.0.0
 */
@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public ResultEntity getUserById(Long id) {

        User user = userMapper.getUserById(id);
        if (user == null) {
            return ResultEntity.error("用户不存在");
        } else {
            return ResultEntity.success(user);
        }
    }

    @Override
    public PageResultEntity findAll(Integer pageNo, Integer pageSize) {

        Sort sort = new Sort(Sort.Direction.ASC, "id");
        Pageable pageable = PageRequest.of(pageNo, pageSize, sort);
        Page<User> datas = userMapper.findAll(pageable);
        //总条数
        int total = (int) datas.getTotalElements();
        //总页数
        int totalPages = datas.getTotalPages();
        //数据列表
        List<User> rows = datas.getContent();
        return PageResultEntity.success(total, rows);
    }

    @Override
    public ResultEntity deleteUserById(Long id) {
        userMapper.deleteById(id);
        return ResultEntity.success();
    }

    @Override
    public ResultEntity saveUser(User user) {
        User saveUser = userMapper.save(user);
        if(saveUser!=null){
            return  ResultEntity.success(saveUser);
        }else {
            return  ResultEntity.error("插入失败");
        }
    }

    @Override
    public ResultEntity updateMobileById(Long id, String mobile) {
        int count = userMapper.updateMobileById(id,mobile);
        if(count==0){
            return  ResultEntity.error("修改失败");
        }else{
            return ResultEntity.success();
        }
    }
}

六、controller层：

package com.hyw.jpatest.controller;

import com.hyw.jpatest.model.User;
import com.hyw.jpatest.result.PageResultEntity;
import com.hyw.jpatest.result.ResultEntity;
import com.hyw.jpatest.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

/**
 * @author huyw
 * @create 2018/11/9
 * @since 1.0.0
 */
@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/user/get/{id}")
    public ResultEntity get(@PathVariable("id") Long id) {
        return userService.getUserById(id);
    }

    @GetMapping("/user/list")
    public PageResultEntity list(Integer pageNo, Integer pageSize) {

        return userService.findAll(pageNo, pageSize);
    }

    @PostMapping("/user/add")
    public ResultEntity add(String mobile, String password, String headPic, Integer type, String region) {
        User user = new User();
        user.setMobile(mobile);
        user.setPassword(password);
        user.setHeadPic(headPic);
        user.setType(type);
        user.setRegion(region);
        return userService.saveUser(user);
    }

    @PutMapping("/user/update")
    public ResultEntity update(@RequestParam Long id, String mobile) {

        return userService.updateMobileById(id, mobile);
    }

    @DeleteMapping("/user/delete/{id}")
    public ResultEntity delete(@PathVariable("id") Long id) {

        return userService.deleteUserById(id);
    }
}