1. 循环插入
1
2
3
4
5
6
7
8
9
10
11
| <insert id="importFromSource" parameterType="list">
<if test="list!=null and list.size()>0">
INSERT INTO `table` (....) VALUES
<foreach collection="list" index="index" item="item" separator=",">
(#{item.id}, #{item.name}, #{item.date}, ....)
</foreach>
ON DUPLICATE KEY UPDATE
`name`=VALUES(`name`),
`date`=VALUES(`date`),
</if>
</insert>
|
2. if elseif else
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
| <choose>
<when test="item.updateTime==null">
now()
</when>
<otherwise>
#{item.updateTime}
</otherwise>
</choose>
<choose>
<when test="item.parentId==null">
0
</when>
<when test="item.parentId==item.userId">
0
</when>
<otherwise>
#{item.parentId}
</otherwise>
</choose>
|
3. 存入表用逗号分割，实体为数组，读写自动转换
- 创建 IntegerListTypeHandler
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
| @MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes({List.class})
public class IntegerListTypeHandler extends BaseTypeHandler<List<Integer>> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, List<Integer> parameter, JdbcType jdbcType) throws SQLException {
if (parameter==null || parameter.size()==0) {
ps.setString(i, "");
}
else {
ps.setString(i, parameter.stream().map(String::valueOf).collect(Collectors.joining(",")));
}
}
@Override
public List<Integer> getNullableResult(ResultSet rs, String columnName) throws SQLException {
if (rs.wasNull()) return Collections.emptyList();
String str = rs.getString(columnName);
if (str==null || str.equals("")) return Collections.emptyList();
return Arrays.stream(str.split(",")).map(Integer::valueOf).collect(Collectors.toList());
}
@Override
public List<Integer> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
if (rs.wasNull()) return Collections.emptyList();
String str = rs.getString(columnIndex);
if (str==null || str.equals("")) return Collections.emptyList();
return Arrays.stream(str.split(",")).map(Integer::valueOf).collect(Collectors.toList());
}
@Override
public List<Integer> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
if (cs.wasNull()) return Collections.emptyList();
String str = cs.getString(columnIndex);
if (str==null || str.equals("")) return Collections.emptyList();
return Arrays.stream(str.split(",")).map(Integer::valueOf).collect(Collectors.toList());
}
}
|
1
2
3
4
5
6
7
8
| <resultMap id="user" type="xyz.5163.project.User">
<id column="id" property="id" jdbcType="BIGINT" />
<result column="rule_ids" property="ruleIds" typeHandler="xyz.5163.project.handler.IntegerListTypeHandler"/>
</resultMap>
<select id="getUsers" resultMap="user">
SELECT * FROM `user`
</select>
|
如果您喜欢此博客或发现它对您有用，则欢迎对此发表评论。 也欢迎您共享此博客，以便更多人可以参与。 如果博客中使用的图像侵犯了您的版权，请与作者联系以将其删除。 谢谢 ！