Springboot集成mysql+jpa实现增删改查示例

 引用的gradle依赖

implementation("org.springframework.boot:spring-boot-starter-data-jpa")
implementation("mysql:mysql-connector-java")

 application.yml配置文件

spring:
  datasource:
    hikari:
      # 最大连接数,默认值10.
      maximum-pool-size: 100
      # 最小空闲连接，默认值10.
      minimum-idle: 10
      # 连接超时时间(毫秒),默认值30秒.
      connection-timeout: 60000
      #空闲连接超时时间，默认值600000(10分钟),只有空闲连接数大于最大连接数且空闲时间超过该值，才会被释放
      #如果大于等于 max-lifetime 且 max-lifetime>0,则会被重置为0.
      idle-timeout: 600000
      auto-commit: true
      # 连接超时时间(毫秒),默认值30秒.
      max-lifetime: 3000000
      #连接测试查询
      connection-test-query: select 1
    tomcat:
      initial-size: 10
      max-active: 100
      max-age: 300000
      max-idle: 100
      min-idle: 10
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/base?serverTimezone=Asia/Shanghai&characterEncoding=utf-8&useSSL=false&parseTime=true
    username: root
    password: root
    
  jpa:
    database: MYSQL
    properties:
      hibernate:
        jdbc:
          batch_size: 500
          batch_versioned_data: true
        order_inserts: true
        order_updates: true
      format_sql: true
      dialect: org.hibernate.dialect.MySQL55Dialect
    show-sql: false
    hibernate:
      ddl-auto: update
      naming:
        implicit-strategy: org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
    generate-ddl: true
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect

下面以用户管理为例，实现用户信息的增删改查，JacksonUtil是序列化和反序列化工具，可根据自己的修改，未用到的可删除。

@Entity定义对象将会成为被JPA管理的实体，将映射到指定的数据库表。

@Table指定数据库的表名。

@Id定义属性为数据库的主键，一个实体里面必须有一个。

@GeneratedValue(strategy = GenerationType.IDENTITY )：数据库自增长。

@Column定义该属性对应数据库中的列名。

@Enumerated很好用，直接映射enum枚举类型的字段。

@Convert属性转换器用于数据库属性类型与java存储的类型做转换，其方便之处在于存储与读取的时候能自动转换，可以自定义转换规则。

package com.hmbase.user.pojo

import com.hmbase.jackson.JacksonUtil
import org.hibernate.annotations.Where
import java.math.BigDecimal
import javax.persistence.*

@Entity
@Table(name = "us_user", uniqueConstraints = [UniqueConstraint(name = "unique_account", columnNames = ["account"])])
@Where(clause = "deleted=0")
class UserEntity {
    /**
     * 登录账号
     */
    var account: String = ""

    /**
     * 姓名
     */
    var userName: String = ""

    /**
     * 密码
     */
    var password: String = ""

    /**
     * 收入
     */
    @Column(columnDefinition = "decimal(11,2)")
    var income: BigDecimal? = null

    /**
     * 职业
     */
    @Enumerated(EnumType.STRING)
    var workType: WorkType? = WorkType.OTHER

    /**
     * 学校
     */
    @Column(columnDefinition = "json")
    @Convert(converter = SchoolConverter::class)
    var school: School? = null

    fun toUserDto(): UserDto {
        return UserDto().also {
            it.id = this.id
            it.account = this.account
            it.userName = this.userName
            it.password = this.password
            it.income = this.income
            it.workType = this.workType
            it.school = this.school
        }
    }
}

class School {
    var schoolName: String = ""
    var schoolAddress: String = ""
}

/**
 * 用于数据库属性类型与java存储的类型做转换，例如枚举类型，在存储到数据库时或者在数据库取出来时，不用手动转换。必须实现接口AttributeConverter<X,Y>
 * convertToDatabaseColumn（X attribute）用于把输入的类型转成数据库存储的类型
 * convertToEntityAttribute (Y dbData) 用于把数据库搜出来的类型转成实体中想要的类型
 */
@Converter
class SchoolConverter : AttributeConverter<School, String> {
    override fun convertToDatabaseColumn(attribute: School?): String? {
        if (attribute == null) return null
        return JacksonUtil.toJson(attribute)
    }

    override fun convertToEntityAttribute(dbData: String?): School? {
        if (dbData == null) {
            return null
        }
        return JacksonUtil.toBean(dbData, School::class.java)
    }
}

enum class WorkType(val desc: String) {
    TEACHER("老师"), STUDENT("学生"), OTHER("其他")
}

package com.hmbase.tools.pojo

import com.fasterxml.jackson.annotation.JsonIgnore
import com.fasterxml.jackson.annotation.JsonIgnoreProperties
import org.hibernate.annotations.Where
import java.io.Serializable
import java.util.*
import javax.persistence.*

@MappedSuperclass
@JsonIgnoreProperties("createDate", "updateDate", "deleted")
@Where(clause = "DELETED <> 1")
abstract class BaseUIDEntity : Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY )
    var id: Long? = null

    @Temporal(value = TemporalType.TIMESTAMP)
    var createDate: Date? = Calendar.getInstance().time

    @Temporal(value = TemporalType.TIMESTAMP)
    @JsonIgnore
    var updateDate: Date? = null

    @Column(name = "deleted")
    @JsonIgnore
    var deleted: Boolean? = false

    @PreUpdate
    fun preUpdate() {
        updateDate = Date()
    }

    @PrePersist
    fun prePersist() {
        createDate = Date()
        updateDate = Date()
    }
}

package com.hmbase.user.pojo

import java.math.BigDecimal
import javax.validation.constraints.Size

class UserDto {
    /**
     * 用户唯一标识
     */
    var id: Long? = null

    /**
     * 登录账号
     */
    @Size(min = 10, max = 14)
    var account: String = ""

    /**
     * 姓名
     */
    var userName: String = ""

    /**
     * 密码
     */
    var password: String = ""

    /**
     * token
     */
    var token: String? = null

    /**
     * 收入
     */
    var income: BigDecimal? = null

    /**
     * 职业
     */
    var workType: WorkType? = WorkType.OTHER

    var school: School? = null

    fun toUserEntity(): UserEntity {
        return UserEntity().also {
            it.account = this.account
            it.userName = this.userName
            it.password = this.password
            it.income = this.income
            it.workType = this.workType
            it.school = this.school
        }
    }
}

UserRepository：可以根据JPA语法规则自定义的查询方法，也可以自定义sql查询； 
@Query注解来将自定义sql语句绑定到自定义方法上，其中nativeQuery = true代表使用原始sql语句； 
针对修改、删除操作，务必添加@Transactional和@Modifying注解。

package com.hmbase.user.repository

import com.hmbase.user.pojo.UserEntity
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 org.springframework.stereotype.Repository
import javax.transaction.Transactional

@Repository
interface UserRepository : JpaRepository<UserEntity, Long>, JpaSpecificationExecutor<UserEntity> {

    // 按照JPA语法规则自定义的查询方法
    fun findAllByAccountIn(list: List<String>): List<UserEntity>

    // 按照JPA语法规则自定义的查询方法
    fun findAllByAccount(account: String): List<UserEntity>

    // 自定义的查询方法
    @Query(
        "SELECT * FROM us_user WHERE account=?1 ", nativeQuery = true
    )
    @Transactional
    @Modifying
    fun deleteAllByAccount(account: String)
}

UserController：使用了swagger2接口文档的注解，可自行添加或删除。

package com.hmbase.user

import com.hmbase.common.page.PageDto
import com.hmbase.common.page.PagedData
import com.hmbase.tools.formatToDate
import com.hmbase.tools.pojo.Response
import com.hmbase.user.pojo.UserDto
import io.swagger.annotations.Api
import io.swagger.annotations.ApiOperation
import io.swagger.annotations.ApiParam
import org.springframework.web.bind.annotation.*
import java.util.*
import javax.annotation.Resource

@RestController
@RequestMapping("/user")
@Api(description = "用户管理")
class UserController {
    @Resource
    private lateinit var userService: UserService

    @PostMapping("/add")
    @ApiOperation("用户新增")
    fun add(
        @ApiParam("用户信息") @RequestBody userDto: UserDto
    ): Response<Boolean> {
        return Response(userService.add(userDto))
    }

    @DeleteMapping("/delete/{account}")
    @ApiOperation("根据用户账号删除用户")
    fun delete(
        @ApiParam("用户登录账号", example = "zhangsan") @PathVariable account: String
    ): Response<Boolean> {
        return Response(userService.delete(account))
    }

    @PostMapping("/modify/{account}")
    @ApiOperation("根据用户账号修改用户信息")
    fun modify(
        @ApiParam("用户信息") @RequestBody userDto: UserDto
    ): Response<Boolean> {
        return Response(userService.modify(userDto))
    }

    @GetMapping("/find")
    @ApiOperation("用户条件查询")
    fun find(
        @ApiParam("用户登录账号", example = "zhangsan") @RequestParam(required = false) account: String?,
        @ApiParam("用户名称", example = "张三") @RequestParam(required = false) userName: String?,
        @ApiParam("职业，多个用,分割", example = "TEACHER,STUDENT") @RequestParam(required = false) workType: String?,
        @ApiParam("开始时间", example = "2022-11-02 08:30:00") @RequestParam(required = false) startDate: String?,
        @ApiParam("结束时间", example = "2022-11-03 08:30:00") @RequestParam(required = false) endDate: String?,
    ): Response<List<UserDto>> {
        val startTime: Date? = if (!startDate.isNullOrEmpty()) startDate.formatToDate() else null
        val endTime: Date? = if (!endDate.isNullOrEmpty()) endDate.formatToDate() else null
        return Response(userService.find(account, userName, workType, startTime, endTime))
    }

    @GetMapping("page/users")
    @ApiOperation("分页查询用户数据")
    fun findByPage(
        pageDto: PageDto,
    ): Response<PagedData<List<UserDto>>> {
        val pagedData = userService.findByPage(pageDto)
        return Response(pagedData)
    }
}

UserService

package com.hmbase.user

import com.hmbase.common.page.PageDto
import com.hmbase.common.page.PagedData
import com.hmbase.tools.exception.BadRequestException
import com.hmbase.user.pojo.UserDto
import com.hmbase.user.pojo.UserEntity
import com.hmbase.user.pojo.WorkType
import com.hmbase.user.repository.UserRepository
import org.springframework.data.jpa.domain.Specification
import org.springframework.stereotype.Service
import java.util.*
import javax.annotation.Resource
import javax.persistence.criteria.CriteriaBuilder
import javax.persistence.criteria.CriteriaQuery
import javax.persistence.criteria.Root

@Service
class UserService {
    @Resource
    private lateinit var userRepository: UserRepository


    fun add(userDto: UserDto): Boolean {
        if (userDto.account.isEmpty()) {
            throw BadRequestException("登录账号不能正确，请核实！")
        }
        if (userDto.password.isEmpty()) {
            throw BadRequestException("密码不能正确，请核实！")
        }
        val entity = userRepository.findAllByAccount(userDto.account)
        if (entity.isNotEmpty()) {
            throw BadRequestException("登录账户已存在，请核实！")
        }

        userRepository.save(userDto.toUserEntity())
        return true
    }

    fun delete(account: String): Boolean {
        val entity = userRepository.findAllByAccount(account)
        if (entity.isEmpty()) {
            throw BadRequestException("登录账户不存在，请核实！")
        }

        userRepository.deleteAllByAccount(account)
        return true
    }

    fun modify(userDto: UserDto): Boolean {
        if (userDto.account.isEmpty()) {
            throw BadRequestException("登录账号不能正确，请核实！")
        }
        val list = userRepository.findAllByAccount(userDto.account)
        if (list.isEmpty()) {
            throw BadRequestException("登录户不存在，请核实！")
        }
        val entity = list.first()
        entity.userName = userDto.userName
        entity.password = userDto.password

        userRepository.save(entity)
        return true
    }

    fun find(account: String?, userName: String?, workType: String?, startTime: Date?, endTime: Date?): List<UserDto> {
        val specification =
            Specification { root: Root<UserEntity>, _: CriteriaQuery<*>?, criteriaBuilder: CriteriaBuilder ->
                //增加筛选的条件
                val predicate = criteriaBuilder.conjunction()
                /**模糊查询**/
                if (!userName.isNullOrEmpty()) {
                    predicate.expressions.add(criteriaBuilder.like(root.get("userName"), "%$userName%"))
                }
                /**精确查询**/
                if (!account.isNullOrEmpty()) {
                    predicate.expressions.add(criteriaBuilder.equal(root.get<String>("account"), account))
                }
                /**范围查询**/
                if (!workType.isNullOrEmpty()) {
                    val statusIn = criteriaBuilder.`in`(root.get<WorkType>("workType"))
                    workType.split(",".toRegex()).forEach {
                        statusIn.value(WorkType.valueOf(it))
                    }
                    predicate.expressions.add(statusIn)
                    //第二种方式
                    //predicate.expressions.add(root.get<WorkType>("workType").`in`(listOf(WorkType.STUDENT,WorkType.TEACHER)))
                }
                /**时间段查询**/
                if (startTime != null) {
                    //大于等于开始时间
                    predicate.expressions.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createDate"), startTime))
                }
                if (endTime != null) {
                    //小于等于结束时间
                    predicate.expressions.add(criteriaBuilder.lessThanOrEqualTo(root.get("createDate"), endTime))
                }
                predicate
            }

        return userRepository.findAll(specification).map { it.toUserDto() }
    }

    fun findByPage(pageDto: PageDto): PagedData<List<UserDto>> {
        val pages = userRepository.findAll(pageDto.getPageRequest())

        return PagedData<List<UserDto>>().also { pageDate ->
            pageDate.data = pages.content.map {
                it.toUserDto()
            }
            pageDate.total = pages.totalElements
        }
    }
}

PagedData分页实体

package com.hmbase.common.page

class PagedData<T> {
    /**
     * 数据内容
     */
    var data: T? = null

    /**
     * 数据总数目
     */
    var total: Long = 0
}

转载请注明出处！