小鹿记录

菜单

Administrator
发布于 2024-12-09 / 5 阅读
0
0

JpaOrm规范

使用规范

【建议】单表的增删改查，推荐使用 JPA Repository 或者 JPA Criteria API；多表连接查询、复杂的查询，推荐使用Query Dsl

【强制】每一张表，都必须对应一个PO类，即使这个表结构，非常简单

【强制】每张表对应的实体类，必须使用工具类生成，属性名称和属性类型，必须和表字段一 一对应，不允许自行修改实体类

【强制】禁止使用@Query注解，禁止手写Sql语句，禁止手写JPQL语句

【强制】禁止使用@OneToOne注解，禁止使用@OneToMany注解

【强制】禁止使用@DynamicUpdate注解，禁止使用@Lombok相关注解

【强制】禁止在开启事务后，切换数据源。所以在切数据源时，要注意看上下游调用链中，有没有开启事务

【强制】推荐使用编程式事务，禁止使用注解式事务

代码结构图

image-20241202192606392

单表的增删改查

实体类生成

每张表，都对应一个PO类，PO类，全部使用工具生成。属性名称和属性类型，必须和表字段一 一对应，不允许自行修改实体类

查询

单表的查询，推荐使用以下几种方式，大家按照具体的业务场景，自行选择：

  • JpaRepository，查询整个实体类，支持分页和排序，只支持单查询条件
  • Jpa Criteria API，查询整个实体类，支持分页和排序，支持动态多查询条件
  • Query Dsl，查询部分字段，支持分页和排序，支持动态多查询条件

JpaRepository

示例2.1

public interface DeviceMonitorRepository extends JpaRepository<DeviceMonitorPO, String> {
    List<DeviceMonitorPO> findByBoxNo(String boxNo);

    Page<DeviceMonitorPO> findByBoxNo(String boxNo, Pageable pageable);
}

    @Test
    void contextLoads() {
        Page<DeviceMonitorPO> monitorPOPage = deviceMonitorRepository.findAll(PageRequest.of(0, 3));
        System.out.println(JSON.toJSONString(monitorPOPage.getContent()));
        System.out.println("-----------------------------------");
        
        
        Page<DeviceMonitorPO> deviceMonitorPOPageByBoxno = deviceMonitorRepository.findByBoxNo("1820641425562718208", PageRequest.of(0, 2));
        System.out.println(JSON.toJSONString(deviceMonitorPOPageByBoxno.getContent()));
        System.out.println("+++++++++++++++++++++++++++++++++++++++++++");
        
        
        Page<DeviceMonitorPO> secondFindResultPage = deviceMonitorRepository.findByBoxNo("1820641425562718208",
                PageRequest.of(1, 2, Sort.by("updateTime").descending()));
        System.out.println(JSON.toJSONString(secondFindResultPage.getContent()));
    }

Jpa Criteria API

示例2.2

public interface DeviceMonitorRepository extends JpaRepository<DeviceMonitorPO, String>, JpaSpecificationExecutor<DeviceMonitorPO> {

@Test
void contextLoads() {
    Integer pendingQueryDataType = 0;
    String pendingQueryBoxNo = "1820641425562718208";

    Specification<DeviceMonitorPO> specification = new Specification<>() {
        @Override
        public Predicate toPredicate(Root<DeviceMonitorPO> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
            List<Predicate> predicateList = new LinkedList<>();
            if (Objects.nonNull(pendingQueryDataType)) {
                predicateList.add(criteriaBuilder.equal(root.get("dataType"), pendingQueryDataType));
            }
            if (Objects.nonNull(pendingQueryBoxNo)) {
                predicateList.add(criteriaBuilder.equal(root.get("boxNo"), pendingQueryBoxNo));
            }
            Predicate compositedPredicate = criteriaBuilder.and(predicateList.toArray(Predicate[]::new));
            return compositedPredicate;
        }
    };
    List<DeviceMonitorPO> all = deviceMonitorRepository.findAll(specification);
    System.out.println(JSON.toJSONString(all));

}

示例2.2.2

推荐使用X_辅助类，如下：

@Test
    void contextLoads2() {

        Integer pendingQueryDataType = 0;
        String pendingQueryBoxNo = "1820641425562718208";

        Specification<DeviceMonitorPO> specification = new Specification<>() {
            @Override
            public Predicate toPredicate(Root<DeviceMonitorPO> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicateList = new LinkedList<>();
                if (Objects.nonNull(pendingQueryDataType)) {
                    predicateList.add(criteriaBuilder.equal(root.get(DeviceMonitorPO_.dataType), pendingQueryDataType));
                }
                if (Objects.nonNull(pendingQueryBoxNo)) {
                    predicateList.add(criteriaBuilder.equal(root.get(DeviceMonitorPO_.boxNo), pendingQueryBoxNo));
                }
                Predicate compositedPredicate = criteriaBuilder.and(predicateList.toArray(Predicate[]::new));
                return compositedPredicate;
            }
        };
        Page<DeviceMonitorPO> all = deviceMonitorRepository.findAll(specification, PageRequest.of(0, 3));
        System.out.println(JSON.toJSONString(all.getContent()));
    }

上面的示例，使用了DeviceMonitorPO_辅助类

示例2.3

这个示例，展示了使用Jpa Criteria API的分页功能

 @Test
    void contextLoads2() {

        Integer pendingQueryDataType = 0;
        String pendingQueryBoxNo = "1820641425562718208";

        Specification<DeviceMonitorPO> specification = new Specification<>() {
            @Override
            public Predicate toPredicate(Root<DeviceMonitorPO> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicateList = new LinkedList<>();
                if (Objects.nonNull(pendingQueryDataType)) {
                    predicateList.add(criteriaBuilder.equal(root.get("dataType"), pendingQueryDataType));
                }
                if (Objects.nonNull(pendingQueryBoxNo)) {
                    predicateList.add(criteriaBuilder.equal(root.get("boxNo"), pendingQueryBoxNo));
                }
                Predicate compositedPredicate = criteriaBuilder.and(predicateList.toArray(Predicate[]::new));
                return compositedPredicate;
            }
        };
        Page<DeviceMonitorPO> all = deviceMonitorRepository.findAll(specification, PageRequest.of(0, 3));
        System.out.println(JSON.toJSONString(all.getContent()));
    }

Query Dsl

实际项目，添加以下依赖：

<dependency>
    <groupId>com.querydsl</groupId>
    <artifactId>querydsl-jpa</artifactId>
</dependency>

示例2.4

查询单列---select...from...

@Test
void contextLoads() {
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
    List<String> graphNames = jpaQueryFactory.select(qScadaGraphPO.name).from(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .fetch();
    System.out.println(JSON.toJSONString(graphNames));
}

示例2.5

查询多列---select... Projections.bean

@Test
void contextLoads() {
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
    // 查询多列
    List<ScadaGraphPO> scadaGraphPOList = jpaQueryFactory.select(
        Projections.bean(ScadaGraphPO.class, qScadaGraphPO.name, qScadaGraphPO.branchId, qScadaGraphPO.scadaLayerPath))
        .from(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .fetch();
    System.out.println(JSON.toJSONString(scadaGraphPOList));
}

示例2.6

这个示例，展示了Query Dsl的分页查询

count查询

@Test
void contextLoads() {
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
    Long totalCount = jpaQueryFactory.select(Wildcard.count).from(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .fetchOne();
    System.out.println(totalCount);
}

分页查询

@Test
void contextLoads() {
    int pageNum = 1;  // 页码，从1开始
    int pageSize = 3;
    QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;

    List<ScadaGraphPO> scadaGraphPOList = jpaQueryFactory.selectFrom(qScadaGraphPO)
        .where(qScadaGraphPO.bizScene.eq(1).and(qScadaGraphPO.branchId.eq("a76ab35f-fa42-467d-a439-4278562931ef")))
        .orderBy(qScadaGraphPO.updateTime.desc())
        .offset((pageNum-1) * pageSize)
        .limit(pageSize)
        .fetch();
    System.out.println(JSON.toJSONString(scadaGraphPOList));

}

新增

推荐使用Jpa Repository的saveAndFlush，不推荐使用save方法

更新

示例2.7

使用Jpa Repository的saveAndFlush方法，如下：

    @Test
    void contextLoads() {
        // 前端提供的实体类
        DeviceMonitorPO frontProvideMonitor = new DeviceMonitorPO();
        frontProvideMonitor.setName("启动/停止");
		// 从数据库中，查询到的实体类
        DeviceMonitorPO dbExistMonitorPO = deviceMonitorRepository.getReferenceById("1745343730292273152");

		// 忽略空值字段
        CopyOptions ignoreNullValueOption = CopyOptions.create().setIgnoreNullValue(true);
        // cn.hutool.core.bean.BeanUtil
        BeanUtil.copyProperties(frontProvideMonitor, dbExistMonitorPO, ignoreNullValueOption);
        // 更新数据库
        deviceMonitorRepository.saveAndFlush(dbExistMonitorPO);
        
    }

示例2.8

更新表中的部分字段，使用query dsl

public Integer update2(DeviceMonitorBO pendingUpdateMon) throws Exception {
        String id = pendingUpdateMon.getId();
        QDeviceMonitorPO qDeviceMonitorPO = QDeviceMonitorPO.deviceMonitorPO;
        Long affectedRows = 0L;

    DefaultTransactionDefinition defaultTransactionDefinition = new DefaultTransactionDefinition();
    defaultTransactionDefinition.setName("update2");
    defaultTransactionDefinition.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    TransactionStatus txStatus = platformTransactionManager.getTransaction(defaultTransactionDefinition);
    try {
        affectedRows = jpaQueryFactory.update(qDeviceMonitorPO)
            .set(qDeviceMonitorPO.name, "启动/停止").set(qDeviceMonitorPO.updateBy, "哈哈哈卫鹏")
            .where(qDeviceMonitorPO.id.eq(pendingUpdateMon.getId())).execute();
        platformTransactionManager.commit(txStatus);
    } catch (TransactionException e) {
        platformTransactionManager.rollback(txStatus);
    }
       
        return affectedRows.intValue();
    }

注意：query dsl的update操作 和 delete操作，默认必须要添加事务

删除

示例2.9

repository：

deviceMonitorRepository.deleteById("1745343730292273152");

示例2.10

query dsl方式如下：

QScadaGraphPO qScadaGraphPO = QScadaGraphPO.scadaGraphPO;
long affectedRows = jpaQueryFactory.delete(qScadaGraphPO).where(qScadaGraphPO.id.eq("1705025930973929472")).execute();

注意：query dsl的update操作 和 delete操作，默认必须要添加事务

多表的增删改查

OneToOne

示例3.1

采用的模式是select(Projections.bean，另外，如果属性名称不一致的话，那么采用as方法

package org.wp.querydsldemo.po;

import java.time.LocalDateTime;

public class DeviceAlarmConfigPOExtend {

    private String id;

    private String name;
    
    ...此处省略
		
    // 这个是额外的嵌套属性
    private DeviceAlarmRulePO alarmRule;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

  

    public DeviceAlarmRulePO getAlarmRule() {
        return alarmRule;
    }

    public void setAlarmRule(DeviceAlarmRulePO alarmRule) {
        this.alarmRule = alarmRule;
    }
}


@Test
void contextLoads() {
    QDeviceAlarmConfigPO qDeviceAlarmConfigPO = QDeviceAlarmConfigPO.deviceAlarmConfigPO;
    QDeviceAlarmRulePO qDeviceAlarmRulePO = QDeviceAlarmRulePO.deviceAlarmRulePO;

    Expression<?>[] alarmRuleExpressions = {qDeviceAlarmRulePO.id,qDeviceAlarmRulePO.monId,qDeviceAlarmRulePO.monName,
                                            qDeviceAlarmRulePO.comparisonMode,qDeviceAlarmRulePO.comparisonValue};

    Expression<?>[] alarmConfigExpressions = {qDeviceAlarmConfigPO.id,qDeviceAlarmConfigPO.alarmType,qDeviceAlarmConfigPO.name,
                                              Projections.bean(
                                                  DeviceAlarmRulePO.class,
                                                  alarmRuleExpressions
                                              ).as("alarmRule")};

    List<DeviceAlarmConfigPOExtend> deviceAlarmConfigPOExtends = jpaQueryFactory.select(Projections.bean(
        DeviceAlarmConfigPOExtend.class,
        alarmConfigExpressions
    ))
        .from(qDeviceAlarmConfigPO)
        .leftJoin(qDeviceAlarmRulePO).on(qDeviceAlarmConfigPO.id.eq(qDeviceAlarmRulePO.alarmConfigId))
        .where(qDeviceAlarmConfigPO.id.eq("1797472963327889408"))
        .fetch();
    System.out.println(JSON.toJSONString(deviceAlarmConfigPOExtends));
}

OneToMany

示例3.2

public class BoxPOExtend {

    private String boxNo;

    private String alias;

    private Integer boxChannelType;

    private String groupName;

    ...此处省略
    
	// 这个是额外的嵌套属性
    private List<DeviceMonitorPO> deviceMonitorPOList;
    
    
}

@Test
    void contextLoads() {
        Integer pendingQueryDataType = 16;
        String firstBoxNo = "1820641425562718208";
        String secondBoxNo = "1745347080979132417";

        QBoxPO qBoxPO = QBoxPO.boxPO;
        QDeviceMonitorPO qDeviceMonitorPO = QDeviceMonitorPO.deviceMonitorPO;

        List<BoxPOExtend> boxPOExtends = jpaQueryFactory.select(qBoxPO.boxNo, qBoxPO.alias, qBoxPO.boxChannelType, qBoxPO.groupName,
                        qDeviceMonitorPO.name, qDeviceMonitorPO.dataType, qDeviceMonitorPO.branchId)
                .from(qBoxPO).leftJoin(qDeviceMonitorPO).on(qBoxPO.boxNo.eq(qDeviceMonitorPO.boxNo))
                .where(qBoxPO.boxNo.in(firstBoxNo, secondBoxNo).and(qDeviceMonitorPO.dataType.eq(pendingQueryDataType)))
                .transform(
                        GroupBy.groupBy(qBoxPO.boxNo)
                                .list(Projections.bean(BoxPOExtend.class, qBoxPO.alias, qBoxPO.boxNo, qBoxPO.boxChannelType, qBoxPO.groupName,
                                        list(Projections.bean(DeviceMonitorPO.class, qDeviceMonitorPO.name, qDeviceMonitorPO.dataType, qDeviceMonitorPO.branchId))
                                                .as("deviceMonitorPOList")))
                );
        System.out.println(JSON.toJSONString(boxPOExtends));

    }

工具项目

http://218.94.128.34:1010/iot-team/ReverseJpaEntity

该工具项目，支持以下功能：

  • 生成PO实体类
  • 生成QXX辅助类

生成PO实体类

image-20241128085922944

image-20241128090001611

image-20241128090039008

image-20241128090243547

生成X_辅助类

mvn clean install

在target/generated-sources/annotations路径下，就能看到生成的X_辅助类

生成QXX辅助类

mvn clean install

在target/generated-sources/java路径下，找到自动生成的类

Git Gui---Fork
JpaPractise

评论