MySQL部分sql记录

      url: jdbc:mysql://xxx.xx.x.xx:3306/xxx_xx?useUnicode=true&characterEncoding=UTF-8&generateSimpleParameterMetadata=true&useSSL=false&serverTimezone=Asia/Shanghai&serverTimezone=GMT%2B8&allowMultiQueries=true

allowMultiQueries=true为mybatis的xml文件可以批量修改

		select
        DATE_FORMAT(a.created_time,'%m') months,
        count(a.id) counts,
		SUM(TIMESTAMPDIFF(SECOND,a.created_time,a.updated_time)) timeCounts
        from hm_service_detail a, hm_online_chat b
        WHERE a.health_service = b.id and b.to_user = #{docId,jdbcType=VARCHAR}
        and a.created_time <![CDATA[ <]]> #{yestoday,jdbcType=TIMESTAMP}
        group by months;

1.SUM(TIMESTAMPDIFF(SECOND,a.created_time,a.updated_time)) timeCounts统计时间差
2.#{yestoday,jdbcType=TIMESTAMP}与jdbcType=“DATE”、jdbcType="TIMESTAMP"的区别就是date直到年月日,timestamp到时分秒,一般带时间查询需要注意

<resultMap id="HmLogMap" type="com.jiuxianginfo.jkgl.web.vo.HmLogVo">
    <id column="id" jdbcType="VARCHAR" property="id" />
    <result column="createId" jdbcType="VARCHAR" property="createId" />
    <result column="createdTime" jdbcType="TIMESTAMP" property="createdTime" />
    <result column="type" jdbcType="VARCHAR" property="type" />
    <result column="moduleName" jdbcType="VARCHAR" property="moduleName" />
    <result column="comment" typeHandler="com.jiuxianginfo.jkgl.web.model.MyBlobTypeHandler" property="comment" />
  </resultMap>
  <select id="logPage" parameterType="com.jiuxianginfo.jkgl.web.model.HmLogDto" resultType="com.jiuxianginfo.jkgl.web.vo.HmLogVo">
    SELECT
    id,
    create_id createId,
    created_time createdTime,
    type,
    module_name moduleName,
    comment
    FROM hm_log
    <where>
      <if test="createdTime!=null and createdTime!=''">
        and  DATE_FORMAT(created_time, '%Y-%m-%d') = DATE_FORMAT(#{createdTime,jdbcType=VARCHAR},'%Y-%m-%d')
      </if>
      <if test="type!=null and type!=''">
        and type = #{type,jdbcType=VARCHAR}
      </if>
    </where>
    order by created_time desc
  </select>

MYSQL字段类型为blob时中文乱码问题，映射实体时typeHandler对应工具类BaseTypeHandler，这样查询后的数据中文就可以正常显示了

public class MyBlobTypeHandler extends BaseTypeHandler<String> {
    //###指定字符集
    private static final String DEFAULT_CHARSET = "utf-8";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i,
                                    String parameter, JdbcType jdbcType) throws SQLException {
        ByteArrayInputStream bis;
        try {
            //###把String转化成byte流
            bis = new ByteArrayInputStream(parameter.getBytes(DEFAULT_CHARSET));
        } 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 {
            //###把byte转化成string
            return new String(returnValue, DEFAULT_CHARSET);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException("Blob Encoding Error!");
        }
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex)
            throws SQLException {
        Blob blob = (Blob) cs.getBlob(columnIndex);
        byte[] returnValue = null;
        if (null != blob) {
            returnValue = blob.getBytes(1, (int) blob.length());
        }
        try {
            return new String(returnValue, DEFAULT_CHARSET);
        } catch (UnsupportedEncodingException e) {
            throw new RuntimeException("Blob Encoding Error!");
        }
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex)
            throws SQLException {
        // TODO Auto-generated method stub
        return null;
    }
}