mysql迁移到达梦数据库，sql语法支持问题

Question

原来的mysql方法

public static String getSaveAndUpdateDossierOutWithSQLDM1(List<DossierOut> dossierOutList, String suffix) {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("INSERT INTO " + TableConstant.TABLE_C_DOSSIER_OUT + "_" + suffix + " (id, ai, child_id, parent_id, task_id, ajbh, type, ");
        stringBuilder.append("title, sort, node_id, recognize, org_id, cata,release_sort, published) VALUES ");

        for (DossierOut dossierOut : dossierOutList) {
            stringBuilder.append("('" + dossierOut.getId() + "', " + (dossierOut.getAi()? 1 : 0) + ", '" +
                    dossierOut.getChildId() + "', '" + dossierOut.getParentId() + "', '" +
                    dossierOut.getTaskId() + "', '" + dossierOut.getAjbh() + "', '" +
                    dossierOut.getType().getType() + "' , '" + dossierOut.getTitle() + "', " +
                    dossierOut.getSort() + ", '" + dossierOut.getNodeId() + "', " +
                    (dossierOut.getRecognize() ? 1 : 0) + ", '" + dossierOut.getOrgId() + "', " +
                    (dossierOut.getCata() ? 1 : 0) + "," + (dossierOut.getReleaseSort() == null ? 0 : dossierOut.getReleaseSort()) + ", '" + (StringUtils.isEmpty(dossierOut.getPublished()) ? "" : dossierOut.getPublished()) + "') ,");
        }
        String sql = stringBuilder.substring(0, stringBuilder.length() - 1); // 去掉最后一个逗号
        String updateSql = " ON DUPLICATE KEY UPDATE ai = VALUES(ai) ,child_id = VALUES(child_id),parent_id = VALUES(parent_id), " +
                "task_id = VALUES(task_id),title = VALUES(title ),sort = VALUES(sort)," +
                " node_id = VALUES(node_id ),recognize = VALUES(recognize), cata = VALUES(cata), release_sort = VALUES(release_sort) ,published = VALUES(published)";
        sql += updateSql;
        return sql;
    }

用的是jpa+hibernate+达梦

现在需要怎么修改。
尝试过用jpa的saveAll方法，但是报错：Batch update returned unexpected row count from update [0]; actual row count: 51; expected: 1

Answer 1

让阿豪来帮你解答，本回答参考chatgpt3.5编写提供，如果还有疑问可以评论或留言

根据错误提示，可能是因为使用了批量保存（saveAll）的方法，但是写入数据库的行数与预期不符，因此需要进行修改。 一种可能的解决方案是，遍历列表中的每个实体，分别进行保存，然后在保存后进行手动事务提交。具体实现代码如下：

@Autowired
private EntityManager entityManager;
@Transactional
public void saveAndUpdateDossierOut(List<DossierOut> dossierOutList, String suffix) {
    String tableName = TableConstant.TABLE_C_DOSSIER_OUT + "_" + suffix;
    for (DossierOut dossierOut : dossierOutList) {
        // 先判断该实体是否已经存在
        Optional<DossierOut> optional = dossierOutRepository.findById(dossierOut.getId());
        if (optional.isPresent()) {
            // update
            DossierOut oldDossierOut = optional.get();
            oldDossierOut.setAi(dossierOut.getAi());
            oldDossierOut.setChildId(dossierOut.getChildId());
            oldDossierOut.setParentId(dossierOut.getParentId());
            oldDossierOut.setTaskId(dossierOut.getTaskId());
            oldDossierOut.setTitle(dossierOut.getTitle());
            oldDossierOut.setSort(dossierOut.getSort());
            oldDossierOut.setNodeId(dossierOut.getNodeId());
            oldDossierOut.setRecognize(dossierOut.getRecognize());
            oldDossierOut.setOrgId(dossierOut.getOrgId());
            oldDossierOut.setCata(dossierOut.getCata());
            oldDossierOut.setReleaseSort(dossierOut.getReleaseSort());
            oldDossierOut.setPublished(dossierOut.getPublished());
            dossierOutRepository.save(oldDossierOut);
        } else {
            // insert
            String sql = "INSERT INTO " + tableName + "(id, ai, child_id, parent_id, task_id, ajbh, type, title, sort, node_id, recognize, org_id, cata, release_sort, published) " +
                    "VALUES (:id, :ai, :childId, :parentId, :taskId, :ajbh, :type, :title, :sort, :nodeId, :recognize, :orgId, :cata, :releaseSort, :published)";
            Query query = entityManager.createNativeQuery(sql).setParameter("id", dossierOut.getId())
                    .setParameter("ai", dossierOut.getAi())
                    .setParameter("childId", dossierOut.getChildId())
                    .setParameter("parentId", dossierOut.getParentId())
                    .setParameter("taskId", dossierOut.getTaskId())
                    .setParameter("ajbh", dossierOut.getAjbh())
                    .setParameter("type", dossierOut.getType().getType())
                    .setParameter("title", dossierOut.getTitle())
                    .setParameter("sort", dossierOut.getSort())
                    .setParameter("nodeId", dossierOut.getNodeId())
                    .setParameter("recognize", dossierOut.getRecognize())
                    .setParameter("orgId", dossierOut.getOrgId())
                    .setParameter("cata", dossierOut.getCata())
                    .setParameter("releaseSort", dossierOut.getReleaseSort())
                    .setParameter("published", dossierOut.getPublished());
            query.executeUpdate();
        }
    }
    entityManager.flush();
    entityManager.clear();
}

这种做法需要在方法头上加上 @Transactional 注解，开启事务，保证每个实体都在一个事务内进行操作。同时，需要注入 EntityManager 对象，因为需要手动执行原先在 saveAndUpdateDossierOutWithSQLDM1 方法中使用的 SQL 语句。在实现方法中，首先遍历列表中的每个实体，对于已经存在的实体，直接使用 save 方法进行更新；对于不存在的实体，手动执行插入语句。最后，需要手动执行事务提交的操作，确保所有的实体操作能够成功写入数据库。 参考链接： 1. Spring Boot JPA——批量插入实体数据 2. 利用 EntityManager 执行 Native SQL