mybatis中插入和读取mysql的blob/text类型数据

mysql中的blob，mediumblob  ，longblob 可以映射到mybatis中 的byte[] 类型 ，需要mybatis的org.apache.ibatis.type.BlobTypeHandler 类型转换处理器的支持。

clob则需要org.apache.ibatis.type.ClobTypeHandler处理器支持。

而mysql中text类型则可以直接使用String 接收和设置即可。。。。

 

1   前端html测试页面

<p>图书上架--带图片</p>
   书名：<input type="text" id="name" name="name" /><br />
   价格：<input type="text" id="price" name="price" /><br />
   作者：<input type="text" id="author" name="author" /><br />
   出版社：<input type="text"  id="publisher" name="publisher" /><br />
   出版时间:<input type="text" id="publishtime" name="publishtime" /> <br />
   描述:<input type="text" id="des" name="des" /> <br />
   图片:<input type="file"  id="file" name="file" /><br />
   html大文本串:<input type="text" name="htmlText" id="htmlText" /><br />
   <button type="button" οnclick="doRequest()">提交</button>
 <br /><br />

  function doRequest(){
	var request = new XMLHttpRequest();
	var url="http://localhost:8100/mvnweb2/book_AddWithImg.do";
	request.open("POST",url);
         var formdata=new FormData();
         formdata.append("name",document.getElementById("name").value);
         formdata.append("price",document.getElementById("price").value);
         formdata.append("author",document.getElementById("author").value);
         formdata.append("publisher",document.getElementById("publisher").value);
         formdata.append("publishtime",document.getElementById("publishtime").value);
         formdata.append("des",document.getElementById("des").value);
         
          var fileObj = document.getElementById("file").files[0]; // 获取文件对象
	 formdata.append("file",fileObj);
	formdata.append("htmlText",document.getElementById("htmlText").value);//htmlText为大文本数据
	request.onreadystatechange=function(){
	if(request.readyState==4 &&request.status==200){
		alert(request.responseText);
				
		}
			
	}
	request.send(formdata);
		
}

2 后台controller 方法

   

@RequestMapping("/book_AddWithImg")
	public String book_AddWithImg(HttpServletRequest request,HttpServletResponse response,
			@RequestParam(value = "file", required = false) MultipartFile file,Book book)throws Exception{
          byte[] imgbytes=   file.getBytes();
		  book.setImgBytes(imgbytes);
		  book.setId(UUID.randomUUID().toString());
		  book.setCreatetime(LTDateFormatUtil.format(new Date()));
		Map<String,Object> map=  bookService.doAddBook(book);
		response.getWriter().write(JSON.toJSONString(map,SerializerFeature.WriteMapNullValue));
		return null;
	}

3  后台mapper.xml 配置
   

 <insert id="save" parameterType="Book">
		 INSERT INTO t_book(id,`name`,author,price,des,publisher,publishtime,createtime,imgBytes,htmlText) 
		 VALUES(#{id},#{name},#{author},#{price},
		 #{des},#{publisher},#{publishtime},#{createtime}
		 ,#{imgBytes,typeHandler=org.apache.ibatis.type.BlobTypeHandler},#{htmlText})
     </insert>

 如果插入失败 sql报错 data too long ...   说明上传的文件太大超过了mysql的blob存储范围(65K)，可以改用mediumBlob 16M 或longBlob (4G) 接收 图片。

4 插入图书图片成功，前台页面读取图片

   

<img  src="http://localhost:8100/mvnweb2/book_getBookImg.do?id=1a261fb4-17a4-4d8a-89eb-638d46691c79"  />

 

5 读取图片后台方法

@RequestMapping("/book_getBookImg")
	public String book_getBookImg(HttpServletRequest request,HttpServletResponse response,String id) throws Exception{
		byte[] imgbytes= bookService.doFindBookImg(id);
	    OutputStream  out= response.getOutputStream();
	    out.write(imgbytes);
		return null;
	}

  @Transactional(propagation=Propagation.NOT_SUPPORTED)
	public byte[] doFindBookImg(String id) {
	 try {
		Map<String,Object> map= bookMapper.findBookImg(id);
		 return  (byte[]) map.get("imgBytes");
	} catch (Exception e) {
		throw new MyCustomException(e);
	} 
	}

<resultMap type="java.util.Map" id="imgResultMap" >
            <result  property="imgBytes" column="imgBytes" jdbcType="BLOB"  typeHandler="org.apache.ibatis.type.BlobTypeHandler"/>
      </resultMap>
      
       <select id="findBookImg" parameterType="string" resultMap="imgResultMap" >
		SELECT   a.`imgBytes` FROM `t_book` a WHERE a.`id`=#{_parameter}
      </select>

。。。