批量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();