SpringBoot PostgreSQL upsert-Toy模板网

这篇具有很好参考价值的文章主要介绍了SpringBoot PostgreSQL upsert。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

PostgreSQL 的 upsert 功能：当记录不存在时，执行插入；否则，进行更新。但是版本需要在9.5以上文章来源地址https://www.toymoban.com/news/detail-596166.html

upsert SQL示例

INSERT INTO table_name(column_list) 
VALUES(value_list)
ON CONFLICT target action;

SQL字符串定义


    @Autowired
    private EntityManager entityManager;
	
	//具体业务SQL
    private static final String INSERT_QUERY_BASE_OFFLINE_AUTH = 
    "insert INTO etk_lg_life (fbh, offline_auth_id, offline_auth_time) VALUES  ";
    private static final String INSERT_PARAM_FOOTER_OFFLINE_AUTH = 
    "  on conflict (fbh)  do update set offline_auth_id = EXCLUDED.offline_auth_id , offline_auth_time=EXCLUDED.offline_auth_time ";
	
    private static final int INSERT_BATCH_SIZE = 1000;
    private static final String DELIMITER = ",";

函数定义&使用

 private void insertAll1(List<String> lgs, Date dataDate, int linkId) {
 		//日期转时间字符串的函数而已
        String dataDateStr = DateUtil.format4Sql(dataDate);

        String values = lgs.stream().map(lg -> this.renderSqlForLgLife(lg, dataDateStr, linkId))
                .collect(Collectors.joining(DELIMITER));

        String insertSQL = INSERT_QUERY_BASE_OFFLINE_AUTH + values +INSERT_PARAM_FOOTER_OFFLINE_AUTH ;

        entityManager.createNativeQuery(insertSQL).executeUpdate();
        entityManager.flush();
        entityManager.clear();

    }

   下方函数为拼接values-SQL///
    private String renderSqlForLgLife(String lg, String dataDateStr, int linkId) {
        StringBuilder sBuilder = new StringBuilder();

        sBuilder.append("('").append(lg).append("',").append(linkId).append(",'").append(dataDateStr).append("')");

        return sBuilder.toString();
    }

ListUtils.partition(lgLifeModel.getLgs(), INSERT_BATCH_SIZE).forEach(lgs -> {
                insertAll1(lgs, lgLifeModel.getDataDates()[0], lgLifeModel.getLinkId());

                log.info("插入{}life数据成功", lgs.size());
            });