MyBatis不用mapper.xml实现CRUD、查询数据库中已有的记录作为参数插入或者更新数据库纪录示例@Select、@ResultType 注解版增删改查示例

 MyBatis注解@Insert插入一条数据和批量插入，查询数据库中已有的记录作为参数插入或者更新数据库纪录示例

插入一条记录并返回主键

查询数据库中已有的记录并进行逻辑处理后作为参数插入或者更新纪录，对应SQL 不需要values 下面的SQL请仔细查看，统一productId的identifier插入数据库时需要增加ref字段的值，不想先查询在代码逻辑中进行处理，那么可以转换成如下的sql处理，对应的Mapper在本文也给出了。

一条SQL语句实现查询DB中已有的数据作为下一条纪录的字段

     INSERT INTO t_product_inner_identifier ( product_id, identifier, ref ) 
     SELECT
     1,
     '22',
     ref + 1 
     FROM
     t_product_inner_identifier 
     WHERE
     product_id = 1 
     ORDER BY
     ref DESC 
     LIMIT 1

复杂SQL，统计关联表数据作为展示数据

<select id="getObjectServicesByObjectId" resultMap="ObjectServicesVO">
       select
        distinct
        os.id,
        os.object_id,
        os.identifier,
        os.name,
        os.description,
        os.system_call,
        os.required,
        os.method,
        os.create_time,
        os.update_time,
        (select count(*) FROM t_object_services_params WHERE ref_id = os.id and type = 0) as inputData,
        (select count(*) FROM t_object_services_params WHERE ref_id = os.id and type = 1) as outputData
        from t_object_services os
        left join t_object_services_params osp on os.id = osp.ref_id
        where object_id = #{objectId}
        and os.identifier not exist ('get','set')
    </select>

 返回非自增主键

    /**
     * 
     * 
     * @Title: insertSysRole3
     * 
     * @Description: insertSysRole3 返回非自增主键的情况
     * 
     * @param sysRole
     * @return
     * 
     * @return: int
     */
    @Insert({ "insert into sys_role(role_name, enabled, create_by, create_time) values(#{roleName}, #{enabled}, #{createBy}, #{createTime, jdbcType=TIMESTAMP})" })
    @SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "id", resultType = Long.class, before = false)
    int insertSysRole3(SysRole sysRole);

对应mapper，返回主键id

/**
     * INSERT INTO t_product_inner_identifier ( product_id, identifier, ref ) SELECT
     * 1,
     * '22',
     * ref + 1 
     * FROM
     * 	t_product_inner_identifier 
     * WHERE
     * 	product_id = 1 
     * ORDER BY
     * 	ref DESC 
     * 	LIMIT 1
     * @param entity
     * @return
     */
    @Insert("insert into t_product_inner_identifier(product_id, identifier, ref) " +
            "select #{productId}, #{identifier},ref + 1 from t_product_inner_identifier where product_id = #{productId} order by ref desc limit 1")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertEntity(ProductInnerIdentifierEntity entity);

批量插入多条记录（不加script标签将不能解析SQL语句，以xml的方式解析）

1、List集合，默认使用list代表入参，数组，默认使用array作为入参。

2、如果传入的参数是一个对象，对象User有个属性List ids，那么就使用ids作为入参。

3、如果接口声明的地方通过注解@Param("xx")指定了入参，那么list,array这种默认的参数就失效，这时候需要指定xx为入参。

批量插入mapper

@Insert("<script>" +
            "insert into t_product_inner_identifier(product_id, identifier, ref) values " +
                "<foreach collection='list' item='item' separator=',' > " +
                    "(#{item.productId}, #{item.identifier}, #{item.ref})" +
                "</foreach>" +
         "</script>")
int insertBatch(@Param("list") List<ProductInnerIdentifierEntity> list);

单元测试

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
class ProductInnerIdentifierDaoTest {
 
    @Autowired
    private ProductInnerIdentifierDao dao;
 
    @Test
    @Transactional
    void insertEntity() {
        ProductInnerIdentifierEntity identifierEntity = new ProductInnerIdentifierEntity();
        identifierEntity.setIdentifier("33");
        identifierEntity.setProductId(1L);
        int id = dao.insertEntity(identifierEntity);
        Assert.assertNotNull(id);
    }


    @Test
    @Transactional
    void insertBatch() {
        ProductInnerIdentifierEntity identifierEntity1 = new ProductInnerIdentifierEntity();
        identifierEntity1.setIdentifier("33");
        identifierEntity1.setProductId(1L);
        ProductInnerIdentifierEntity identifierEntity2 = new ProductInnerIdentifierEntity();
        identifierEntity2.setIdentifier("44");
        identifierEntity2.setProductId(1L);
        List<ProductInnerIdentifierEntity> list = new ArrayList<>(Arrays.asList(identifierEntity1, identifierEntity2));
        int ret = dao.insertBatch(list);
        Assert.assertNotNull(ret);
    }


}

@Select("SELECT * FROM city WHERE city_name=#{cityName}")
// 返回 Map 结果集 column一定要和数据库表中的列保持一致
@Results({
@Result(property = "id", column = "id"),
@Result(property = "provinceId", column = "province_id"),
@Result(property = "cityName", column = "city_name"),
@Result(property = "description", column = "description"),
})
City findByName(@Param("cityName") String cityName);
}

注解版示例(使用@Insert、@Update、@Delete、@Select注解)

//指定这是一个操作数据库的mapper
@Mapper
public interface DepartmentMapper {

    @Select("select * from department where id=#{id}")
    public Department getDeptById(Integer id);

    @Delete("delete from department where id=#{id}")
    public int deleteDeptById(Integer id);

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department set departmentName=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}

批量操作

@Delete("<script>" +
            "   delete from t_product_events_params " +
            "   where " +
            "       <foreach collection="list" item="item" open="(" separator="," close=")">" +
            "           identifier = #{item.identifier} and ref_id = #{item.refId}" +
            "       </foreach>" +
            "</script>")
int deleteAllRefByRefIdAndIdentifier(List<DeleteProductFunctionParam4ObjectRefDTO> eventParamsDel);

批量删除

@Delete("<script>" 
    + " delete from t_product_events_params " 
    + " where " 
    + " <foreach collection='list' item='item' open="(" separator="," close=")">"
    + " identifier = #{item.identifier} and ref_id = #{item.refId}" 
    + " </foreach>" 
    + "</script>") 
int deleteAllRefByRefIdAndIdentifier(List<DeleteProductFunctionParam4ObjectRefDTO> eventParamsDel);

@Delete("<script>" +
            "   delete from t_product_events_params " +
            "   where  ref_id = #{refId} and identifier in (" +
            "       <foreach collection='list' item='item' separator=",">" +
            "           #{item.identifier}" +
            "       </foreach>)" +
            "</script>")
    int deleteAllRefByRefIdAndIdentifiersIn(Long refId, List<String> eventParamsDel);