mybatis--注解-分页-批量-batch

原创已于 2022-08-31 00:41:20 修改 · 765 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #batch #java

于 2021-05-15 15:41:53 首次发布

java 同时被 3 个专栏收录

31 篇文章

订阅专栏

springboot

24 篇文章

订阅专栏

mysql

6 篇文章

订阅专栏

批量update

msg		link
批量更新		link
批量更新		link
jdbc:mysql://mysql004.beta.sdbattery.net:3306/sd_retail_goods?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false		link
		link
		link
		link
		link
		link
		link
		link
		link
		link

@Mapper

添加了@Mapper注解之后这个接口在编译时会生成相应的实现类

@Param

于多个参数来说，每个参数之前都要加上@Param注解

//UserDAO
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import entity.User;

/**
 * 添加了@Mapper注解之后这个接口在编译时会生成相应的实现类
 * 
 * 需要注意的是：这个接口中不可以定义同名的方法，因为会生成相同的id
 * 也就是说这个接口是不支持重载的
 */
@Mapper
public interface UserDAO {

    @Select("select * from user where name = #{name}")
    public User find(String name);

    @Select("select * from user where name = #{name} and pwd = #{pwd}")
    /**
      * 对于多个参数来说，每个参数之前都要加上@Param注解，
      * 要不然会找不到对应的参数进而报错
      */
    public User login(@Param("name")String name, @Param("pwd")String pwd);
}

@Insert

eg1----default

@Insert("insert into tbemployee values(default,#{empName},#{empSex},#{empBirth},#{empSalary},2)")
public void insertEmp(TbEmployee  emp);

eg2---- 使用 insert可以使用Map作为参数

@Insert("insert into h5_source.cheyipaiBack ( orderId,appKey,callNextUrl)values ( #{orderId},#{appKey},#{callNextUrl})")
Integer addChaCallBack(Map map);

eg3----如何使用注解进行批量插入？

 @Insert("<script>"  +
		 "INSERT INTO user(id,username,sex,frequency,money,chepai,phone,beizhu) "
	 		+ "VALUES <foreach collection=\"list\" item=\"usermap\" index=\"index\" separator=\",\">" + 
	 		"(#{usermap.id},#{usermap.username},#{usermap.sex},#{usermap.frequency},#{usermap.money},#{usermap.chepai},#{usermap.phone},#{usermap.beizhu})" + 
	 		" </foreach>"
	 		+ "</script>")
	 int add(@Param("list") List<Map<String, Object>> list);

使用id来update，没有id，那就插入；
新建表的时候，必须带上：

ALTER  TABLE tb_test add  lastModityTime TIMESTAMP  
null DEFAULT CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP comment'最后修改时间';
timeCreate  TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

@Select

eg1----用as

    @Select({
            "SELECT **DISTINCT** c.app_key as appKey,c.create_time as createTime from manager.company_apps as c  RIGHT JOIN (SELECT distinct b.app_id  FROM manager.product as a RIGHT  JOIN manager.app_product_contract as b on b.product_id=a.id WHERE a.api_id=**#{apiId}**) as f on c.id=f.app_id where c.status_flag=1"
            })
    List<App> queryPowerAppKey(**@Param("apiId") Integer apiId**);
优点：
.app_key as **appKey** ，直接用as 查询出来 ，映射到实体的appKey,不用xml，即便需要的字段有点多，也可以这样做；如果可以使用Map接受，那就更加牛逼了

eg2----多行用 +

  @Select({
      "select * from ( SELECT a.id,a.vehicleId,a.lng,a.lat,a.angle,b.vehicle_number as vehicleNumber,a.speed,b.company_id as companyId,b.dev_no as devNo,a.address,FROM_UNIXTIME(a.gpsTime,'%Y-%m-%d %H:%i:%S') as gpsTime FROM basealarm.tb_gps_record a " +
              " LEFT JOIN basealarm.tb_vehicle_snap b ON a.snap_id = b.id " +
              " where  a.gpsTime  BETWEEN #{minTime} and #{maxTime} limit #{account} ) as k where 1=1 order by null limit #{pageStart},#{pageEnd}"
    })
    List<VehicleGpsResDTO> queryGpsRecordPageByHandle(@Param("minTime") Long minTime , @Param("maxTime") Long maxTime ,
                                         @Param("pageStart") Integer pageStart , @Param("pageEnd") Integer pageEnd ,@Param("account") int account);

eg3----map接收

其实使用id查询出单条消息的时候，完全可以使用map接收
eg：

  @Select("select * from h5_source.cheyipaiBack where orderId=#{orderId}")
Map queryBack(@Param("orderId") String orderId);

eg4----如果查询是多条，可用List 接收

@Select("select * from h5_source.cheyipaiBack where orderId=#{orderId}")
List<Map> queryBackx(@Param("orderId") String orderId);

使用@Select动态模糊查询

@Select({"<script>",
            "select * from h5_source.cheyipaiBack where 1=1",
            "<if test='appKey!=null'>",
            "and appKey like '${appKey}%'",
            "</if>",
            "<if test='originalOrderId!=null'>",
            "and originalOrderId like ‘${originalOrderId}%’ ",
            "</if>",
            "</script>"
})
必须加 <script>    .....      </script>
内部用逗号作为拼接符号；like使用$，不能使用#
List<Map> queryBackx(Map map);// 参数和最后接受都用Map

xml配置

<select id="selectConfuse" resultMap="BaseResultMap" parameterType="java.util.Map" >
    select *
    from gateway_system.api_info_public_module as a
    where a.isDelete=0
    <if test="name != null and name !=’’ " >
      and a.name like #{name,jdbcType=VARCHAR}
    </if>
    <if test="type != null" >
      and a.type like #{type,jdbcType=VARCHAR}
    </if>
    order by a.createTime DESC
  </select>

使用update也可以使用Map作为参数

@Update("update h5_source.cheyipaiBack 
set originalOrderId=#{originalOrderId},brandName=#{brandName},result=#{result} 
where orderId=#{orderId}")
Integer update(Map map);

需要分页查询，用pagehelper

one 使用micro的democontroller
//获取参数

        String name = StringUtils.trimToEmpty(query.getName());
        String type = StringUtils.trimToEmpty(query.getType());
        //使用xml格式进行数据的查询
        Map param = new HashMap();
        if (org.apache.commons.lang3.StringUtils.isNotBlank(name))
            param.put("name", org.apache.commons.lang3.StringUtils.wrap(name, "%"));
        if (org.apache.commons.lang3.StringUtils.isNotBlank(type))
            param.put("type", org.apache.commons.lang3.StringUtils.wrap(type, "%"));
        Page memoMessage = PageHelper.startPage(query.getPageNum(), query.getPageSize());
        //doing
        apiInfoPublicModuleMapper.selectConfuse(param);
        return memoMessage.toPageInfo();