【从零开始学Spring Boot】-10.Spring Boot Jpa操作数据库

Java Spring Boot JDBC Jpa
Spring Boot学习
发布日期:   2020-07-21
文章字数:   2.1k
阅读时长:   11 分
阅读次数:  

1.简介

1.1 概述

The Java Persistence API is a standard technology that lets you “map” objects to relational databases. The spring-boot-starter-data-jpa POM provides a quick way to get started. It provides the following key dependencies:

  • Hibernate: One of the most popular JPA implementations.
  • Spring Data JPA: Makes it easy to implement JPA-based repositories.
  • Spring ORMs: Core ORM support from the Spring Framework.

Java Persistence API 是一种标准技术，可让您将对象“映射”到关系数据库。 spring-boot-starter-data-jpa POM提供了一种快速入门的方法。它提供以下关键依赖性：

  • Hibernate：最流行的JPA实现之一。
  • Spring Data JPA：使基于JPA的存储库的实现变得容易。
  • Spring ORMs：Spring 框架对Core ORM的支持。

1.2 特点

  • 基于Spring和JPA构建存储库的先进支持
  • 支持 Querydsl 谓词，从而支持类型安全的JPA查询
  • 实体类的透明审核
  • 分页支持，动态查询执行，集成自定义数据访问代码的能力
  • 在启动时验证 @Query 带注释的查询
  • 支持基于XML的实体映射
  • 通过引入 @EnableJpaRepositories，支持基于 JavaConfig 的存储库配置

2.演示环境

  1. JDK 1.8.0_201
  2. Spring Boot 2.2.0.RELEASE
  3. 构建工具（apache maven 3.6.3）
  4. 开发工具（IntelliJ IDEA ）

3.演示代码

3.1 代码说明

演示基于 spring-boot-starter-data-jpa 来操作数据库的简单 web mvc 项目。包括以下常用场景：

  • 单表的增、删、改、查
  • 多表关联查询（这里使用2张表）
  • 复杂条件混合查询
  • 分页查询

3.2 代码结构

3.3 maven 依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-lang3</artifactId>
    </dependency>
</dependencies>

3.4 配置文件

application.properties

spring.datasource.url=jdbc:mysql://172.16.11.125:3306/test?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=true
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# 打印sql
spring.jpa.show-sql=true
# 自动建表
spring.jpa.hibernate.ddl-auto=create
# 方言；innodb存储引擎
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
# 格式化sql
spring.jpa.properties.hibernate.format_sql=true
# 打印sql中参数
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=trace

spring.data.web.pageable.default-page-size=3
spring.data.web.pageable.page-parameter=pageNum
spring.data.web.pageable.size-parameter=pageSize
spring.data.web.sort.sort-parameter=orderBy

3.5 java代码

Order.java

@Entity
@Table(name = "t_order")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;
    @Column(nullable = false)
    private Long userId;
    @Column(nullable = false, unique = true)
    private String orderCode;
    @Column(nullable = false)
    private BigDecimal totalMoney;
    @Column(nullable = false)
    private String orderDate;

    public Order() {}

    public Order(Long userId, String orderCode, BigDecimal totalMoney, String orderDate) {
        this.userId = userId;
        this.orderCode = orderCode;
        this.totalMoney = totalMoney;
        this.orderDate = orderDate;
    }

       // get&set&toString
}

User.java

@Entity
@Table(name = "t_user")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(nullable = false, unique = true, length = 32)
    private String name;
    @Column(nullable = false)
    private Integer age;
    private String birthday;
    private String address;
    @Column(nullable = false, length = 16)
    private String phone;

    public User() {}

    public User(String name, Integer age, String birthday, String address, String phone) {
        this.name = name;
        this.age = age;
        this.birthday = birthday;
        this.address = address;
        this.phone = phone;
    }

       // get&set&toString
}

OrderRepository.java

@Repository
public interface OrderRepository extends JpaRepository<Order, Long>, JpaSpecificationExecutor<Order> {

    @Query(value = "select "
        + "o.id as orderId, o.orderCode as orderCode, o.orderDate as orderDate, o.userId as userId, "
        + "u.address as address, u.phone as phone, u.age as age from Order o inner join User u on o.userId = u.id where o.orderCode = ?1")
    OrderInfo selectOrderByCode(String orderCode);
}

UserRepository.java

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    @Query("select u from User u where u.name = ?1")
    User findUserByName(String name);

    @Query("select u from User u")
    Page<User> findByPage(Pageable pageable);

    @Query("select u from User u where u.phone = :phone")
    List<User> findUserByPhone(@Param("phone") String phone);

    @Modifying
    @Transactional
    @Query("update User set phone = ?1 where name = ?2")
    int updateByName(String phone, String name);

    @Modifying
    @Transactional
    @Query("delete from User where name = :name")
    int deleteByName(@Param("name") String name);
}

OrderService.java

public interface OrderService {

    /**
     * 查询所有user
     * @return order
     */
    List<Order> selectList();

    /**
     * 根据订单号关联查询
     * @param orderCode 订单号
     * @return OrderInfo
     */
    OrderInfo selectOrderByCode(String orderCode);

    /**
     * 使用example查询
     * @param order 查询参数
     * @return Order
     */
    List<Order> selectByExample(Order order);

    /**
     * 多条件组合查询
     * @param orderParam 查询参数
     * @return Order
     */
    Page<Order> selectByCondition(OrderParam orderParam, Pageable pageable);
}

UserService.java

public interface UserService {

    /**
     * 查询所有数据
     * @return user
     */
    List<User> selectList();

    /**
     * 根据名称查询
     * @param name name
     * @return user
     */
    User findUserByName(String name);

    /**
     * 根据电话查询
     * @param phone 电话
     * @return user
     */
    List<User> findUserByPhone(String phone);

    /**
     * 分页查询
     * @param pageable 分页参数
     * @return user
     */
    Page<User> findByPage(Pageable pageable);

    /**
     * 根据名称更新电话
     * @param phone 电话
     * @param name 名称
     * @return 影响行数
     */
    User updateByName(String phone, String name);

    /**
     * 根据名称删除
     * @param name 名称
     * @return 影响行数
     */
    User deleteByName(String name);

    /**
     * 新增
     * @param user user
     * @return user
     */
    User add(User user);
}

UserServiceImpl.java

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserRepository userRepository;

    @Override
    public List<User> selectList() {
        return userRepository.findAll();
    }

    @Override
    public User findUserByName(String name) {
        return userRepository.findUserByName(name);
    }

    @Override
    public List<User> findUserByPhone(String phone) {
        return userRepository.findUserByPhone(phone);
    }

    @Override
    public Page<User> findByPage(Pageable pageable) {
        return userRepository.findByPage(pageable);
    }

    @Override
    public User updateByName(String phone, String name) {
        userRepository.updateByName(phone, name);
        return findUserByName(name);
    }

    @Override
    public User deleteByName(String name) {
        User user = findUserByName(name);
        userRepository.deleteByName(name);
        return user;
    }

    @Override
    public User add(User user) {
        return userRepository.save(user);
    }
}

OrderServiceImpl.java

@Service
public class OrderServiceImpl implements OrderService {

    @Autowired
    private OrderRepository orderRepository;

    @Override
    public List<Order> selectList() {
        return orderRepository.findAll();
    }

    @Override
    public OrderInfo selectOrderByCode(String orderCode) {
        return orderRepository.selectOrderByCode(orderCode);
    }

    @Override
    public List<Order> selectByExample(Order order) {
        // exact：精确比配 contains: 模糊匹配 startsWith：从头匹配
        // 同 matcher -> matcher.exact();
        ExampleMatcher exampleMatcher = matching().withMatcher("userId", GenericPropertyMatcher::exact)
            .withMatcher("orderCode", GenericPropertyMatcher::contains)
            .withMatcher("orderDate", GenericPropertyMatcher::startsWith);
        Example<Order> example = Example.of(order, exampleMatcher);
        return orderRepository.findAll(example);
    }

    @Override
    public Page<Order> selectByCondition(OrderParam orderParam, Pageable pageable) {
        return orderRepository.findAll((root, query, cb) -> {
            List<Predicate> predicates = new ArrayList<>();
            // equal userId
            if (Objects.nonNull(orderParam.getUserId())) {
                predicates.add(cb.equal(root.get("userId"), orderParam.getUserId()));
            }
            // like orderCode
            if (StringUtils.isNotBlank(orderParam.getOrderCode())) {
                predicates.add(cb.like(root.get("orderCode"), "%" + orderParam.getOrderCode() + "%"));
            }
            // between
            if (StringUtils.isNotBlank(orderParam.getOrderStartDate()) && StringUtils.isNotBlank(orderParam.getOrderEndDate())) {
                predicates.add(cb.between(root.get("orderDate"), orderParam.getOrderStartDate(), orderParam.getOrderEndDate()));
            }
            // greater than
            if (Objects.nonNull(orderParam.getTotalMoney())) {
                predicates.add(cb.greaterThan(root.get("totalMoney"), orderParam.getTotalMoney()));
            }
            return query.where(predicates.toArray(new Predicate[0])).getRestriction();
        }, pageable);
    }
}

OrderInfo.java

public interface OrderInfo {

    Long getUserId();
    Long getOrderId();
    Integer getAge();
    String getOrderCode();
    String getAddress();
    String getPhone();
    String getOrderDate();
}

OrderParam.java

public class OrderParam {

    private Long id;
    private Long userId;
    private String orderCode;
    private BigDecimal totalMoney;
    private String orderStartDate;
    private String orderEndDate;
    // get&set
}

OrderController.java

@RestController
@RequestMapping(value = "/order")
public class OrderController {

    @Autowired
    private OrderService orderService;

    @GetMapping(value = "/list")
    public List<Order> list() {
        return orderService.selectList();
    }

    @GetMapping(value = "/queryByCode/{orderCode}")
    public OrderInfo queryByCode(@PathVariable String orderCode) {
        return orderService.selectOrderByCode(orderCode);
    }

    @GetMapping(value = "/queryByExample")
    public List<Order> selectByExample(@RequestBody Order order) {
        return orderService.selectByExample(order);
    }

    @GetMapping(value = "/queryByCondition")
    public Page<Order> queryByCondition(@RequestBody OrderParam orderParam, Pageable pageable) {
        return orderService.selectByCondition(orderParam, pageable);
    }
}

UserController.java

@RestController
@RequestMapping(value = "/user")
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping(value = "/list")
    public List<User> list() {
        return userService.selectList();
    }

    @GetMapping(value = "/findByName/{name}")
    public User findByName(@PathVariable String name) {
        return userService.findUserByName(name);
    }

    @GetMapping(value = "/findByPhone/{phone}")
    public List<User> findByPhone(@PathVariable String phone) {
        return userService.findUserByPhone(phone);
    }

    @GetMapping(value = "/page")
    public Page<User> page(Pageable pageable) {
        return userService.findByPage(pageable);
    }

    @PostMapping(value = "/add")
    public User add(User user) {
        return userService.add(user);
    }

    @PutMapping(value = "/updateByName")
    public User updateByName(@RequestBody User user) {
        return userService.updateByName(user.getPhone(), user.getName());
    }

    @DeleteMapping(value = "/deleteByName/{name}")
    public User deleteByName(@PathVariable String name) {
        return userService.deleteByName(name);
    }
}

InitializeDataCommand.java

@Component
public class InitializeDataCommand implements CommandLineRunner {

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private OrderRepository orderRepository;

    @Override
    public void run(String... args) throws Exception {

        User user1 = new User("zhangsan", 20, "2000-01-01", "shenzhen", "13888888888");
        User user2 = new User("lisi", 21, "1999-01-01", "shanghai", "13777777777");
        User user3 = new User("wangwu", 22, "1998-01-01", "beijing", "13666666666");
        User user4 = new User("zhaoliu", 23, "1997-01-01", "guangzhou", "13555555555");
        User user5 = new User("sunqi", 24, "1996-01-01", "wuhan", "13444444444");

        SecureRandom random = SecureRandom.getInstance("SHA1PRNG", "SUN");
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        LocalDateTime now = LocalDateTime.now();
        List<User> users = userRepository.saveAll(Arrays.asList(user1, user2, user3, user4, user5));

        List<Order> orders = users.stream().map(user -> {
            Order order = new Order();
            order.setUserId(user.getId());
            order.setOrderCode("OC202005231205000" + (users.indexOf(user) + 1));
            order.setOrderDate(dateTimeFormatter.format(now.minusDays(random.nextInt(100))));
            order.setTotalMoney(BigDecimal.valueOf(random.nextDouble() * random.nextInt(10000)));
            return order;
        }).collect(Collectors.toList());

        orderRepository.saveAll(orders);
    }
}

3.6 git 地址

spring-boot/spring-boot-06-jdbc/spring-boot-data-jpa

4.效果展示

启动 SpringBootJpaDemoApplication.main 方法，在 spring-boot-data-jpa.http 访问下列地址，观察输出信息是否符合预期。

4.1 t_user

查询用户列表（所有）

### GET /user/list
GET http://localhost:8080/user/list
Accept: application/json

根据用户名查询

### GET /user/findByName/{name}
GET http://localhost:8080/user/findByName/lisi
Accept: application/json

根据手机号查询

### GET /user/findByPhone/{phone}
GET http://localhost:8080/user/findByPhone/13666666666
Accept: application/json

查询用户列表（分页）

### GET /user/page
GET http://localhost:8080/user/page
Accept: application/json
Content-Type: application/json

{
  "pageable":{
     "pageNumber":1,
     "pageSize":3,
     "orderBy":"age desc"
  }
}

更新用户信息

### PUT /user/updateByName
PUT http://localhost:8080/user/updateByName
Content-Type: application/json

{
  "name": "zhangsan",
  "phone": "13456789012"
}

删除用户

### DELETE /user/deleteByName/{name}
DELETE http://localhost:8080/user/deleteByName/zhangsan
Content-Type: application/json

4.2 t_order

查询订单列表（所有）

### GET  /order/list
GET http://localhost:8080/order/list
Accept: application/json

根据订单编号关联查询

### GET /order/queryByCode/{orderCode}
GET http://localhost:8080/order/queryByCode/OC2020052312050002
Accept: application/json

多条件查询订单

### GET /order/queryByExample
GET http://localhost:8080/order/queryByExample
Accept: application/json
Content-Type: application/json

{
"userId":2,
"orderCode":"OC202005231",
"orderDate": "2020-05-17"
}

多条件混合查询

### GET /order/queryByCondition
GET http://localhost:8080/order/queryByCondition
Accept: application/json
Content-Type: application/json

{
  "userId": 2,
  "orderCode": "OC20200523",
  "totalMoney": 20,
  "orderStartDate": "2020-02-10 16:17:12",
  "orderEndDate": "2020-05-30 16:17:12"
}

5.参考

  1. 官方 spring-data-jpa
  2. 官方文档-Spring Boot Features/JPA
文章作者: Soulballad
文章链接: https://soulballad.github.io/posts/170665fe
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Soulballad !
Java Spring Boot JDBC Jpa
赏
评论
 上一篇
【源码分析-Spring Boot】-10.Spring Boot Jpa 查询流程及实现原理 【源码分析-Spring Boot】-10.Spring Boot Jpa 查询流程及实现原理
Spring Boot Jpa：【从零开始学Spring Boot】-10.Spring Boot Jpa操作数据库 1.@Repository 如何加载的？SpringBooApplication 应用启动时，会调用 createAp
2020-07-21 源码分析
Java Spring Boot 源码分析
下一篇 
【源码分析-Spring Boot】-9.Spring Boot Admin 请求和通信原理 【源码分析-Spring Boot】-9.Spring Boot Admin 请求和通信原理
Spring Boot Admin：【从零开始学Spring Boot】-9.Spring Boot Admin 1.admin server 的请求原理Spring Boot Admin 组件中，通过 @EnableAdminServ
2020-07-20 源码分析
Java Spring Boot 源码分析
  目录