Mybatis代码片段
Contents
批处理
<select id="getAdsByGroupIds" resultMap="adInfoWithGroupId">
SELECT
ai.*, gam.group_id
FROM
group_ad_mapping AS gam
LEFT JOIN
ad_info AS ai
ON
gam.creative_id = ai.creative_id
WHERE
gam.group_id in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
ORDER BY
ai.create_at DESC
</select>
一对多
java类
public class SalesLead {
private Long id;
private Long clientId;
private Integer userId;
private Integer salesUserId;
private Integer agentUserId;
private Integer saleStatus = 0;
private Integer isDeal = 0;
private Integer isRead = 0;
private BigDecimal totalMoney = new BigDecimal("0");
private BigDecimal prepaid = new BigDecimal("0");
private String memo = "";
private String fromOpenid = "";
private String toOpenid = "";
private String des = "";
private String title = "";
private Timestamp createTime = new Timestamp(System.currentTimeMillis());
private Timestamp updateTime = new Timestamp(System.currentTimeMillis());
private Timestamp closeTime;
private List<SalesLeadFollow> follows;
//getter and settter
}
<resultMap id="salesLeadMap" type="com.company.sales.pojo.SalesLead">
<id property="id" column="id"/>
<result property="clientId" column="client_id"/>
<result property="userId" column="user_id"/>
<result property="salesUserId" column="sales_user_id"/>
<result property="agentUserId" column="agent_user_id"/>
<result property="saleStatus" column="sale_status"/>
<result property="isDeal" column="is_deal"/>
<result property="isRead" column="is_read"/>
<result property="totalMoney" column="total_money"/>
<result property="prepaid" column="prepaid"/>
<result property="memo" column="memo"/>
<result property="fromOpenid" column="from_openid"/>
<result property="toOpenid" column="to_openid"/>
<result property="des" column="des"/>
<result property="title" column="title"/>
<result property="createTime" column="create_time"/>
<result property="updateTime" column="update_time"/>
<result property="closeTime" column="close_time"/>
<collection property="follows" ofType="com.company.sales.pojo.SalesLeadFollow" column="id" select="selectFollow" />
</resultMap>
<resultMap id="salesLeadFollowMap" type="com.company.sales.pojo.SalesLeadFollow">
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="followUserId" column="follow_user_id"/>
<result property="followType" column="follow_type"/>
<result property="salesLeadId" column="sales_lead_id"/>
<result property="timeline" column="timeline"/>
<result property="createTime" column="create_time"/>
</resultMap>
<select id="selectFollow" resultMap="salesLeadFollowMap">
SELECT * from sales_lead_follow WHERE sales_lead_id = #{id} ORDER BY id DESC
</select>
一对一
public class WbStatus extends BasePojo implements Serializable {
private static final Logger log = LoggerFactory.getLogger(WbStatus.class);
private static final long serialVersionUID = 1L;
private Integer id;
private Long sid = 0L;
private String idstr = "";
private Long mid = 0L;
private Long userId = 0L;
private String userScreenName = "";
private String userProfileImageUrl = "";
private String text = "";
private String source = "";
private String thumbnailPic = "";
private String bmiddlePic = "";
private String originalPic = "";
private String retweetedStatusId = "";
private String geo = "";
private Integer repostsCount = 0;
private Integer commentsCount = 0;
private Integer attitudesCount = 0;
private String visible = "";
private String picUrls = "";
private Timestamp createAt;
private Timestamp updateAt;
private String ad = "";
private Integer isDeleted = 0;
//逻辑字段,不是DB字段.
private WbUser wbUser;
public WbStatus() {
}
Mybatis的代码片段:
<resultMap id="wbStatusResultMap" type="com.weibosdk.pojo.WbStatus">
<id property="id" column="id"/>
<result property="sid" column="sid"/>
<result property="idstr" column="idstr"/>
<result property="mid" column="mid"/>
<result property="userId" column="user_id"/>
<result property="userScreenName" column="user_screen_name"/>
<result property="userProfileImageUrl" column="user_profile_image_url"/>
<result property="text" column="text"/>
<result property="source" column="source"/>
<result property="thumbnailPic" column="thumbnail_pic"/>
<result property="bmiddlePic" column="bmiddle_pic"/>
<result property="originalPic" column="original_pic"/>
<result property="retweetedStatusId" column="retweeted_status_id"/>
<result property="geo" column="geo"/>
<result property="repostsCount" column="reposts_count"/>
<result property="commentsCount" column="comments_count"/>
<result property="attitudesCount" column="attitudes_count"/>
<result property="visible" column="visible"/>
<result property="picUrls" column="pic_urls"/>
<result property="createAt" column="create_at"/>
<result property="updateAt" column="update_at"/>
<result property="ad" column="ad"/>
<result property="isDeleted" column="is_deleted"/>
<association property="wbUser" javaType="com.weibosdk.pojo.WbUser" select="selectWbUser" column="user_id">
</association>
</resultMap>
<select id="selectWbUser" resultMap="com.weibosdk.dao.mapper.WbUserMapper.wbUserResultMap">
SELECT * from wb_user WHERE u_id = #{userId} limit 1
</select>
自定义 TypeHandler
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(Object.class)
public class JsonObjectTypeHandler implements TypeHandler {
@Override
public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
if (parameter == null) {
ps.setString(i, "");
return;
}
ps.setString(i, Constants.GSON.toJson(parameter));
}
@Override
public Object getResult(ResultSet rs, String columnName) throws SQLException {
return rs.getString(columnName);
}
@Override
public Object getResult(ResultSet rs, int columnIndex) throws SQLException {
return rs.getString(columnIndex);
}
@Override
public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {
return cs.getString(columnIndex);
}
}
注册application.properties
mybatis.type-handlers-package=com.your.package.hanlders
使用
<result property="urlObjects" column="url_objects" typeHandler="com.your.package.hanlders.JsonObjectTypeHandler"/>
在 insert 语句中使用
#{s.urlObjects, typeHandler=com.your.package.hanlders.JsonObjectTypeHandler}
常见问题
Cannot define both nestedQueryId and nestedResultMapId
这是因为重复定义了. 典型的错误写法如下
<resultMap>
<association property="user" resultMap="userMap" select="getByUid" column="user_id" />
</resultMap>
<select id="getByUid" resultMap="userMap">
select * from wb_user where u_id = #{uid}
</select>
正确的写法
<association property="user" resultMap="userMap" select="getByUid" column="user_id" />
修改为
<association property="user" select="getByUid" column="user_id" />