前文中已经介绍过了mybatis注解开发(详情请看spring boot 之mybatis注解开发),本文将讲述一下高级使用,比如in的使用,批量插入等功能实现
一、@Select @Update @Insert @Delete 注解中批量操作
本文以@Select 和批量@Insert举例
其实很简单,只需要使用<script></script>标签将sql包裹起来,其他书写跟xml一样,可以使用xml中的各种标签
具体实现如下
@Select({
"<script>"
+ "SELECT "
+ "orders.orderId, product_sku.productId, product_sku.skuName, "
+ "orders.number, orders.orderPrice,product_sku.skuPrice, "
+ "orders.orderCreate, customer.mobile, shop_keeper.mobile1 as shopKeeperMobile, "
+ "shop.name, orders.shopId, shop.address, shop.cityCode "
+ "FROM orders, product_sku, customer, shop, shop_keeper "
+ "WHERE orders.skuId=product_sku.skuId "
+ "AND orders.customerId = customer.customerId "
+ "<if test='orderStatus != null'>"
+ "AND orders.orderStatus IN "
+ "<foreach item='status' index='index' collection='orderStatus' open='(' separator=',' close=')'>"
+ "#{status} "
+ "</foreach>"
+ "</if>"
+ "AND orders.shopId = shop.shopId "
+ "AND orders.shopId = shop_keeper.shopId "
+ "ORDER BY customer.mobile DESC, orders.shopId DESC ,orders.orderCreate DESC"
+ "</script>"
})
List<Map<String, Object>> selectOrders(@Param(value="orderStatus")List<Short> orderStatus);
@Insert("<script>" +
"insert into bsqs_order_product(order_id,product_id,buy_num,create_time," +
"create_by,update_time,update_by) values " + "" +
"<foreach item='orderProduct' index='index' collection='orderProducts' open='(' separator=',' close=')'>" +
"#{orderId},#{productId},#{buyNum},#{buyPrice},#{createTime},#{createBy},#{updateTime},#{updateBy}" +
"</foreach>" +
"</script>"
)
public void batchInsert1(List<BsqsOrderProduct> orderProducts);
二、使用@SelectProvider @InsertProvider 等注解开发
具体实现如下:
@InsertProvider
@InsertProvider(type = BsqsOrderProductProvider.class,method = "batchInsertSql")
public void batchInsert( List<BsqsOrderProduct> orderProducts);
provider实现:
public String batchInsertSql(Map<String,List<BsqsOrderProduct>> map){
List<BsqsOrderProduct> list = map.get("list");
StringBuffer pattern = new StringBuffer();
pattern.append("(#'{'list[{0}].orderId},");
pattern.append("#'{'list[{0}].productId},");
pattern.append("#'{'list[{0}].buyNum},");
pattern.append("#'{'list[{0}].createTime},");
pattern.append("#'{'list[{0}].createBy},");
pattern.append("#'{'list[{0}].updateTime},");
pattern.append("#'{'list[{0}].updateBy})");
MessageFormat messageFormat = new MessageFormat(pattern.toString());
StringBuffer sql = new StringBuffer();
sql.append("insert into bsqs_order_product(order_id,product_id,buy_num,");
sql.append("create_time,create_by,update_time,update_by) values ");
int size = list.size();
for (int i = 0; i < size; i++)
{
sql.append(messageFormat.format(new Object[]{i}));
if (i < size - 1) {
sql.append(",");
}
}
return sql.toString();
}
@SelectProvider
@SelectProvider(type = BsqsCartProvider.class,method = "findCartsSql")
public List<BsqsCart> findCarts(Map<String,Object> map);
provider实现
public String findCartsSql(Map<String,Object> map) {
List<String> list = (List<String>)map.get("list");
StringBuffer sql = new StringBuffer();
sql.append("select id,cart_id as cartId,product_id as productId,");
sql.append("buy_num as buyNum,uid,add_time as addTime,del_flag as delFlag ");
sql.append("from bsqs_cart where uid=#{uid} and cart_id in (");
int size = list.size();
for (int i = 0; i < size; i++)
{
sql.append(String.format("#{list[%s]}",i));
if (i < size - 1) {
sql.append(",");
}
}
sql.append(")");
return sql.toString();
}