postgresql的批量更新的原生sql和在mybatis的xml中的sql写法

postgresql的批量更新的原生sql和在mybatis的xml中的sql写法

postgresql批量更新的原生sql

        update person
        set id=tmp.id
        from (values
        (1,'张飞',37,'书法','成都','巴蜀中学','15276152716','2021-03-26 17:32:28'),
        (2,'关羽',43,'围棋','成都','巴蜀中学','15276152716','2021-03-26 17:32:28'),
        (3,'赵云',33,'剑术','成都','巴蜀中学','15276152716','2021-03-26 17:32:28'),
        (4,'马超',31,'射箭','成都','巴蜀中学','15276152716','2021-03-26 17:32:28'),
        (5,'黄忠',49,'饮酒','成都','巴蜀中学','15276152716','2021-03-26 17:32:28')
        )
        as
        tmp(id,name,age,hobby,city,school,mobile,update_time)
        where
        person.id=tmp.id

在mybatis的xml中的sql写法

    <update id="batchUpdateName">
        update person
        set name = tem.name
        from
        (
        values
        <foreach collection="personList" item="item" index="index"
                 open="" separator="," close="">
            (
            #{item.id},
            #{item.name},
            #{item.age},
            #{item.hobby},
            #{item.city},
            #{item.school},
            #{item.mobile},
            cast(#{item.updateTime} as timestamp)
            )
    </foreach>
        ) as
        tem(id,name,age,hobby,city,school,mobile,update_time)
        where
        person.id = tem.id

    </update>

其中cast(#{item.updateTime} as timestamp)是对传递的数据的格式转换成timestamp