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

阳光穿透心脏的1/2处 2023-10-12 11:02 31阅读 0赞

#### 依赖 ####

<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
    }