JPA框架基本用法-SpringBoot框架（二）

依赖

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

1. DAO层

import org.springframework.stereotype.Repository;
import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.StoredProcedureQuery;
import java.util.List;

@Repository
public class UserDao {

    @PersistenceContext
    private EntityManager entityManager;

    /**
     * 新增用户
     * @param user 用户对象
     */
    public void addUser(User user) {
        entityManager.persist(user);
    }

    /**
     * 删除用户
     * @param id 用户ID
     */
    public void deleteUser(Long id) {
        User user = entityManager.find(User.class, id);
        entityManager.remove(user);
    }

    /**
     * 更新用户
     * @param user 用户对象
     */
    public void updateUser(User user) {
        entityManager.merge(user);
    }

    /**
     * 根据ID查询用户
     * @param id 用户ID
     * @return 用户对象
     */
    public User getUserById(Long id) {
        return entityManager.find(User.class, id);
    }

    /**
     * 多表关联查询
     * @return 用户和订单信息列表
     */
    public List<Object[]> getUserOrderInfo() {
        String sql = "SELECT u.*, o.* " +
                "FROM user u " +
                "INNER JOIN order o ON u.id = o.user_id";
        Query query = entityManager.createNativeQuery(sql);
        return query.getResultList();
    }

    /**
     * 分组查询
     * @return 每个用户的订单数量和总金额
     */
    public List<Object[]> getUserOrderSummary() {
        String sql = "SELECT u.name, COUNT(o.id), SUM(o.amount) " +
                "FROM user u " +
                "LEFT JOIN order o ON u.id = o.user_id " +
                "GROUP BY u.id";
        Query query = entityManager.createNativeQuery(sql);
        return query.getResultList();
    }

    /**
     * 使用存储过程查询用户订单信息
     * @param userId 用户ID
     * @return 用户和订单信息列表
     */
    public List<Object[]> getUserOrderInfoByProcedure(Long userId) {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("get_user_order_info");
        query.registerStoredProcedureParameter("userId", Long.class, ParameterMode.IN);
        query.setParameter("userId", userId);
        return query.getResultList();
    }

    /**
     * 视图查询
     * @return 用户和订单信息列表
     */
    public List<Object[]> getUserOrderInfoByView() {
        String sql = "SELECT * FROM user_order_info";
        Query query = entityManager.createNativeQuery(sql);
        return query.getResultList();
    }
}

2. 服务层

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;

@Service
public class UserService {

    @Autowired
    private UserDao userDao;

    /**
     * 新增用户
     * @param user 用户对象
     */
    public void addUser(User user) {
        userDao.addUser(user);
    }

    /**
     * 删除用户
     * @param id 用户ID
     */
    public void deleteUser(Long id) {
        userDao.deleteUser(id);
    }

    /**
     * 更新用户
     * @param user 用户对象
     */
    public void updateUser(User user) {        userDao.updateUser(user);
    }

    /**
     * 根据ID查询用户
     * @param id 用户ID
     * @return 用户对象
     */
    public User getUserById(Long id) {
        return userDao.getUserById(id);
    }

    /**
     * 多表关联查询
     * @return 用户和订单信息列表
     */
    public List<Object[]> getUserOrderInfo() {
        return userDao.getUserOrderInfo();
    }

    /**
     * 分组查询
     * @return 每个用户的订单数量和总金额
     */
    public List<Object[]> getUserOrderSummary() {
        return userDao.getUserOrderSummary();
    }

    /**
     * 使用存储过程查询用户订单信息
     * @param userId 用户ID
     * @return 用户和订单信息列表
     */
    public List<Object[]> getUserOrderInfoByProcedure(Long userId) {
        return userDao.getUserOrderInfoByProcedure(userId);
    }

    /**
     * 视图查询
     * @return 用户和订单信息列表
     */
    public List<Object[]> getUserOrderInfoByView() {
        return userDao.getUserOrderInfoByView();
    }
}

3. SQL脚本

-- 建表
CREATE TABLE user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  age INT NOT NULL,
  gender VARCHAR(10) NOT NULL
);

CREATE TABLE order (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES user (id)
);

-- 建视图
CREATE VIEW user_order_info AS
SELECT u.*, o.* 
FROM user u
INNER JOIN order o ON u.id = o.user_id;

-- 建存储过程
CREATE PROCEDURE get_user_order_info (IN userId BIGINT)
BEGIN
  SELECT u.*, o.* 
  FROM user u
  INNER JOIN order o ON u.id = o.user_id
  WHERE u.id = userId;
END;

-- 插入数据
INSERT INTO user (name, age, gender) VALUES ('张三', 20, '男');
INSERT INTO user (name, age, gender) VALUES ('李四', 25, '女');
INSERT INTO user (name, age, gender) VALUES ('王五', 30, '男');

INSERT INTO order (user_id, amount) VALUES (1, 100.00);
INSERT INTO order (user_id, amount) VALUES (2, 200.00);
INSERT INTO order (user_id, amount) VALUES (3, 300.00);

4. YML文件

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

  jpa:
    database: mysql
    show-sql: true
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        dialect: org.hibernate.dialect.MySQL5Dialect

5. 实体类

import javax.persistence.*;
import java.math.BigDecimal;
import java.sql.Timestamp;

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

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

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

    @Column(name = "age", nullable = false)
    private Integer age;

    @Column(name = "gender", nullable = false)
    private String gender;

    // getter and setter
}

@Entity
@Table(name = "order")
public class Order {

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

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", nullable = false)
    private User user;

    @Column(name = "amount", nullable = false)
    private BigDecimal amount;

    @Column(name = "create_time", nullable = false)
    private Timestamp createTime;

    // getter and setter
}