mybatis简单使用记录
@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
private Date updateTime;
1. 插入或者更新
<insert id="insertOrUpdate" parameterType="cn.lhcz.lhczyq.project.entity.YqAccount">
insert into yq_account
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="userId != null">
user_id,
</if>
<if test="appName != null">
app_name,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=VARCHAR},
</if>
<if test="userId != null">
#{userId,jdbcType=VARCHAR},
</if>
<if test="appName != null">
#{appName,jdbcType=VARCHAR},
</if>
</trim>
ON DUPLICATE KEY UPDATE
<trim suffixOverrides=",">
<if test="id != null">
id = #{id,jdbcType=VARCHAR},
</if>
<if test="userId != null">
user_id = #{userId,jdbcType=VARCHAR},
</if>
<if test="appName != null">
app_name = #{appName,jdbcType=VARCHAR},
</if>
</trim>
</insert>
ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!
MySQL唯一约束
建立如下表:
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`idcard` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `idcard`(`idcard`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of test_user
-- ----------------------------
INSERT INTO `test_user` VALUES (1, '123456', 'gxz', NULL);
INSERT INTO `test_user` VALUES (2, '123', 'bb', 23);
执行如下就会更新:
insert into test_user (idcard, name) VALUES('123', 'dd')
ON DUPLICATE KEY UPDATE name='zz';
2. 批量插入更新
<insert id="batchInsert" parameterType="cn.lhcz.lhczyq.project.entity.YqAccount">
insert into yq_account (id, user_id, app_name, nickname, gender, head_url, douyinhao, signature, flag)
values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.id,jdbcType=VARCHAR},
#{item.userId,jdbcType=VARCHAR},
#{item.appName,jdbcType=VARCHAR},
#{item.nickname,jdbcType=VARCHAR},
#{item.gender,jdbcType=VARCHAR},
#{item.headUrl,jdbcType=VARCHAR},
#{item.signature,jdbcType=VARCHAR},
#{item.flag,jdbcType=VARCHAR}
)
</foreach>
ON DUPLICATE KEY UPDATE
nickname = values (nickname),
head_url = values (head_url),
signature = values (signature)
</insert>
3. 添加可获取id
<insert id="add" parameterType="cn.lhcz.mars.project.entity.ZaFace" keyProperty="id" useGeneratedKeys="true">
</insert>
代码示例:
ZaFace face = new ZaFace();
zaFaceMapper.add(face);
Long faceId = face.getId();
4. 处理blob字段
4.1 blob字段对应byte[],可直接使用
mapper文件中:
<resultMap id="BaseResultMap" type="cn.lhcz.mars.project.entity.ZaFace">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="group_id" jdbcType="INTEGER" property="groupId" />
<result column="features" jdbcType="BLOB" property="features" />
<result column="del_flag" jdbcType="CHAR" property="delFlag" />
<result column="insert_time" jdbcType="TIMESTAMP" property="insertTime" />
</resultMap>
entity文件中:
private byte[] features;
4.2 blob字段对应String类型
mapper文件中:
<resultMap id="BaseResultMap" type="cn.lhcz.mars.project.entity.ZaPersonFace">
<id column="face_id" jdbcType="BIGINT" property="faceId" />
<result column="person_id" jdbcType="BIGINT" property="personId" />
<result column="face_image" property="faceImage" typeHandler="cn.lhcz.mars.commons.config.BlobTypeHandler"/>
<result column="img_path" jdbcType="VARCHAR" property="imgPath" />
</resultMap>
entity文件中:
private String faceImage;
BlobTypeHandler类文件内容:
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.io.ByteArrayInputStream;
import java.io.UnsupportedEncodingException;
import java.sql.*;
public class BlobTypeHandler extends BaseTypeHandler<String> {
public void setNonNullParameter(PreparedStatement ps, int i,
String parameter, JdbcType jdbcType) throws SQLException {
//声明一个输入流对象
ByteArrayInputStream bis;
try {
//把字符串转为字节流
bis = new ByteArrayInputStream(parameter.getBytes("UTF-8"));
} catch (UnsupportedEncodingException e) {
throw new RuntimeException("Blob Encoding Error!");
}
ps.setBinaryStream(i, bis, parameter.length());
}
@Override
public String getNullableResult(ResultSet rs, String columnName)
throws SQLException {
Blob blob = (Blob) rs.getBlob(columnName);
byte[] returnValue = null;
if (null != blob) {
returnValue = blob.getBytes(1, (int) blob.length());
}
try {
//将取出的流对象转为utf-8的字符串对象
return new String(returnValue, "UTF-8");
} catch (UnsupportedEncodingException e) {
throw new RuntimeException("Blob Encoding Error!");
}
}
@Override
public String getNullableResult(ResultSet rs, int i) throws SQLException {
Blob blob = (Blob) rs.getBlob(i);
byte[] returnValue = null;
if (null != blob) {
returnValue = blob.getBytes(1, (int) blob.length());
}
try {
//将取出的流对象转为utf-8的字符串对象
return new String(returnValue, "UTF-8");
} catch (UnsupportedEncodingException e) {
throw new RuntimeException("Blob Encoding Error!");
}
}
@Override
public String getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
Blob blob = (Blob) callableStatement.getBlob(i);
byte[] returnValue = null;
if (null != blob) {
returnValue = blob.getBytes(1, (int) blob.length());
}
try {
//将取出的流对象转为utf-8的字符串对象
return new String(returnValue, "UTF-8");
} catch (UnsupportedEncodingException e) {
throw new RuntimeException("Blob Encoding Error!");
}
}
}
这样使用即可。
5. 实现in查询
/** 根据videoIds查询*/
List<Map<String, Object>> getByVideoIds(String[] videoIds);
<select id="getByVideoIds" resultType="java.util.Map">
SELECT
*
from yq_videos
WHERE id IN
<foreach collection="videoIds" item="id" index="index" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
6. 一个null指针异常
字段content(varchar类型,实体类中为String类型)在表中记录为null时,查询出来也是null,执行content.trim()就会报null指针异常。
当放到一个Set<String>
中时,这个Set中就会有一个null元素,可能会引起其他错误:set.remove(null);