《2019/11/12》mybatis简单使用记录

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);