基于SpringBoot使用MyBaties以及MySQL插入集合类型的数据进数据库
DAO的定义:
package com.easyjoy.marketing.request.newpeople;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
@Data
public class MarketingEntity {
@ApiModelProperty("活动id")
@JsonProperty("marketing_id")
private long marketingId;
@ApiModelProperty("门店id")
@JsonProperty("store_ids")
private String storeIds;
@ApiModelProperty("活动名称")
@JsonProperty("marketing_name")
private String marketingName;
@ApiModelProperty("活动图片")
@JsonProperty("marketing_activity_pic")
private String marketingActivityPic;
@ApiModelProperty("活动创建时间")
@JsonProperty("marketing_create_time")
private long marketingCreateTime;
@ApiModelProperty("活动开始时间")
@JsonProperty("marketing_start_time")
private long marketingStartTime;
@ApiModelProperty("活动结束时间")
@JsonProperty("marketing_end_time")
private long marketingEndTime;
@ApiModelProperty("活动类型 1自定义 2券包")
@JsonProperty("marketing_type")
private int marketingType;
@ApiModelProperty("活动是否被删除 0否 1是")
@JsonProperty("is_deleted")
private int isDeleted;
@ApiModelProperty("活动是否上下架 0否 1是 ")
@JsonProperty("marketing_on_shelves")
private int marketingOnShelves;
@ApiModelProperty("活动状态 1关闭 2进行中 3结束")
@JsonProperty("marketing_state")
private int marketingState;
@ApiModelProperty("是否分享 1是 2否")
@JsonProperty("marketing_share")
private int marketingShare;
@ApiModelProperty("分享标题")
@JsonProperty("marketing_share_title")
private String marketingShareTitle;
@ApiModelProperty("分享图")
@JsonProperty("marketing_share_pic")
private String marketingSharePic;
@ApiModelProperty("券包")
@JsonProperty("marketing_coupon")
private String marketingCoupon;
@ApiModelProperty("服务商品类目集合")
@JsonProperty("marketing_service_category_id")
private String marketingServiceCategoryId;
@ApiModelProperty("商品类目集合")
@JsonProperty("marketing_product_category_id")
private String marketingProductCategoryId;
@ApiModelProperty("套餐类目集合")
@JsonProperty("marketing_combination_category_id")
private String marketingCombinationCategoryId;
@ApiModelProperty("活动规则")
@JsonProperty("marketing_activity_role")
private List<String> marketingActivityRole;
@ApiModelProperty("页面背景色")
@JsonProperty("marketing_background_color")
private String marketingBackgroundColor;
}
RO的定义:
package com.easyjoy.marketing.result.newpeople;
import com.easyjoy.marketing.result.CouponTemplateRO;
import com.easyjoy.marketing.result.product.ProductRo;
import com.easyjoy.marketing.result.store.StoreMerchantRo;
import com.fasterxml.jackson.annotation.JsonProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.util.List;
@Data
public class MarketingRO {
@ApiModelProperty("活动id")
@JsonProperty("marketing_id")
private long marketingId;
@ApiModelProperty("门店id")
@JsonProperty("store_ids")
private String storeIds;
@ApiModelProperty("活动名称")
@JsonProperty("marketing_name")
private String marketingName;
@ApiModelProperty("活动图片")
@JsonProperty("marketing_activity_pic")
private String marketingActivityPic;
@ApiModelProperty("活动创建时间")
@JsonProperty("marketing_create_time")
private long marketingCreateTime;
@ApiModelProperty("活动开始时间")
@JsonProperty("marketing_start_time")
private long marketingStartTime;
@ApiModelProperty("活动结束时间")
@JsonProperty("marketing_end_time")
private long marketingEndTime;
@ApiModelProperty("活动类型 1自定义 2 券包")
@JsonProperty("marketing_type")
private int marketingType;
@ApiModelProperty("活动是否被删除 0否 1是")
@JsonProperty("is_deleted")
private int isDeleted;
@ApiModelProperty("活动是否上下架 0否 1是 ")
@JsonProperty("marketing_on_shelves")
private int marketingOnShelves;
@ApiModelProperty("活动状态 1关闭 2进行中 3结束")
@JsonProperty("marketing_state")
private int marketingState;
@ApiModelProperty("是否分享 1是 2否")
@JsonProperty("marketing_share")
private int marketingShare;
@ApiModelProperty("分享标题")
@JsonProperty("marketing_share_title")
private String marketingShareTitle;
@ApiModelProperty("分享图")
@JsonProperty("marketing_share_pic")
private String marketingSharePic;
@ApiModelProperty("券包")
@JsonProperty("marketing_coupon")
private String marketingCoupon;
@ApiModelProperty("券包集合")
@JsonProperty("coupon_list")
private List<CouponTemplateRO> couponList;
@ApiModelProperty("门店信息集合")
@JsonProperty("store_infos")
private List<StoreMerchantRo> storeInfos;
@ApiModelProperty("服务商品类目集合")
@JsonProperty("marketing_service_category_id")
private String marketingServiceCategoryId;
@ApiModelProperty("商品类目集合")
@JsonProperty("marketing_product_category_id")
private String marketingProductCategoryId;
@ApiModelProperty("套餐类目集合")
@JsonProperty("marketing_combination_category_id")
private String marketingCombinationCategoryId;
@ApiModelProperty("服务产品集合")
@JsonProperty("service_product_list")
private List<ProductRo> serviceProductList;
@ApiModelProperty("普通产品集合")
@JsonProperty("product_list")
private List<ProductRo> productList;
@ApiModelProperty("套餐产品集合")
@JsonProperty("combination_list")
private List<ProductRo> combinationList;
@ApiModelProperty("活动规则")
@JsonProperty("marketing_activity_role")
private List<String> marketingActivityRole;
@ApiModelProperty("页面背景色")
@JsonProperty("marketing_background_color")
private String marketingBackgroundColor;
}
Mapper文件的语句
void addNewMarketing(MarketingEntity marketingEntity);
工具类
package com.easyjoy.marketing.common;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.apache.ibatis.type.TypeHandler;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
/**
* 自定义类型转换器
* 作用:
* 1、数据存储时,自动将List集合,转为字符串(格式自定义)
* 2、数据查询时,将查到的字符串再转为List集合
*
* @author 5
*/
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(List.class)
public class List2VarcharHandler implements TypeHandler<List<String>> {
@Override
public void setParameter(PreparedStatement ps, int i, List<String> strings, JdbcType jdbcType) throws SQLException {
StringBuffer sb = new StringBuffer();
for (String s : strings) {
sb.append(s).append(",");
}
ps.setString(i, sb.toString());
}
@Override
public List<String> getResult(ResultSet rs, String s) throws SQLException {
String favs = rs.getString(s);
if (favs != null) {
return Arrays.asList(favs.split(","));
}
return null;
}
@Override
public List<String> getResult(ResultSet rs, int i) throws SQLException {
String favs = rs.getString(i);
if (favs != null) {
return Arrays.asList(favs.split(","));
}
return null;
}
@Override
public List<String> getResult(CallableStatement cs, int i) throws SQLException {
String favs = cs.getString(i);
if (favs != null) {
return Arrays.asList(favs.split(","));
}
return null;
}
}
SQL 语句
配置的结果集
<resultMap id="MarketingResultMap" type="com.easyjoy.marketing.dal.dao.NewPeopleDao">
<id column="marketing_id" property="marketingId"></id>
<result column="store_ids" property="storeIds"></result>
<result column="marketing_name" property="marketingName"/>
<result column="marketing_activity_pic" property="marketingActivityPic"/>
<result column="marketing_create_time" property="marketingCreateTime"/>
<result column="marketing_start_time" property="marketingStartTime"/>
<result column="marketing_end_time" property="marketingEndTime"/>
<result column="marketing_type" property="marketingType"/>
<result column="is_deleted" property="isDeleted"/>
<result column="marketing_on_shelves" property="marketingOnShelves"/>
<result column="marketing_state" property="marketingState"/>
<result column="marketing_share" property="marketingShare"/>
<result column="marketing_share_title" property="marketingShareTitle"/>
<result column="marketing_share_pic" property="marketingSharePic"/>
<result column="marketing_coupon" property="marketingCoupon"/>
<result column="marketing_product_category_id" property="marketingProductCategoryId"/>
<result column="marketing_service_category_id" property="marketingServiceCategoryId"/>
<result column="marketing_combination_category_id" property="marketingCombinationCategoryId"/>
<result column="marketing_activity_role" property="marketingActivityRole"
typeHandler="com.easyjoy.marketing.common.List2VarcharHandler"></result>
<result column="marketing_background_color" property="marketingBackgroundColor"/>
</resultMap>
插入语句
<insert id="addNewMarketing">
INSERT IGNORE INTO easyjoy_markting_activite_new_people (`marketing_id`, `store_ids`, `marketing_name`, `marketing_activity_pic`, `marketing_create_time`,
`marketing_start_time`, `marketing_end_time`,
`marketing_type`,`is_deleted`, `marketing_on_shelves`, `marketing_state`, `marketing_share`, `marketing_share_title`, `marketing_share_pic`,
`marketing_coupon`,
`marketing_product_category_id`, `marketing_service_category_id`, `marketing_combination_category_id`, `marketing_activity_role`, `marketing_background_color`)
VALUES (#{marketingId}, #{storeIds}, #{marketingName}, #{marketingActivityPic}, #{marketingCreateTime}, #{marketingStartTime}, #{marketingEndTime}, #{marketingType}, #{isDeleted}, #{marketingOnShelves},
#{marketingState}, #{marketingShare}, #{marketingShareTitle}, #{marketingSharePic},
#{marketingCoupon},#{marketingProductCategoryId}, #{marketingServiceCategoryId}, #{marketingCombinationCategoryId},
#{marketingActivityRole,typeHandler=com.easyjoy.marketing.common.List2VarcharHandler}, #{marketingBackgroundColor})
</insert>
总结就是定义一个转换的类,使用这个转换的类去完成这个工作