File upload / Insert Retrieve Blob using Spring Mybatis

Posted: May 15, 2012 in Techilla
Tags: , , , ,

Uploading a file or an image and retrieving it is an extremely frequent activity and doing so via Mybatis/Spring must be a breeze as there are so many users who must be doing it!

So I thought, 6 hours before I started to code it. Unfortunately , Mybatis user manual has zero references to blob/clob insert /delete and searching on Google didn’t seem to go very far. There were pointers but no complete code examples. In the end, it proved to be exceedingly simple and I went to sleep a happy man. Here’s the full example to save half a day for somebody else.

Mybatis version : mybatis-3.1.0
Spring version : 3.1.1
Mybatis-Spring bundle : 1.1.1
Database : Oracle
Reqd. libs : commons-fileupload-*.jar , commons-io*.jar , ojdbc14.jar and obviously the reqd. spring and mybatis jars.

Database columns in EMPLOYEE table:


The actual file data is stored in a BLOB field.

Note: not a CLOB field, as we are mainly trying to host images here.(Even if some other file type is uploaded, it being stored as a BLOB will help to retrieve it exactly as it was stored, without any character encoding being applied, as in case of CLOB)

We are having a Spring MVC application, so we will augment our web-config.xml or whatever config file the DispatcherServlet listens to with the below code:

<bean id="multipartResolver"
	<property name="maxUploadSize">

The fileupload size is in Bytes.

Model object / Form data

public class Employee {
       private CommonsMultipartFile fileData;
       private byte[] fileDataBytes;
       private String fileName;
       private String fileContentType;

Our jsp will host the below :

spring form file upload

If we don’t put the enctype=”multipart/form-data, we will not be able to typecast the uploaded file into the CommonsMultiPartFile and hence we will not be able to retrieve the contentType and the filename. If we don’t put the encType, we can always retrieve the uploaded file as byte[] but in order to retrieve the file, we will have to store the contenttype as well, so an easier option is to use CommonsMultiPartFile.

Store or Upload the file or image in Mybatis

<update id="updateEmployee" parameterType="com.spring.model.Employee">
	update employee
<if test="fileData.originalFilename != null">filename = #{fileData.originalFilename,jdbcType=VARCHAR},</if>
		<if test="fileData.ContentType != null">fileContentType =					#{fileData.contentType,jdbcType=VARCHAR},</if>
		<if test="fileData.bytes != null">fileData = 							#{fileData.bytes},
	empId = #{empId}

Note, that we don’t set the jdbcType of contentType, nor do we use any typehandlers as many suggested.

Also, note that we use the original CommonsMultipart object to retrieve the fileContentType,filename and fileData.

That’s it for storing the file.

Retrieve the uploaded file/image using Mybatis

Now, for retrieving it back, we will use the below query:

<select id="getUploadedFileForEmployee" parameterType="Long"
		select empId,fileName,fileContentType,fileData as
		employee where


That’s it. We have successfully retrieved the file along with its name and contenttype. Now, we shall see how we can display it / download the file.

public class EmployeeController {
	private EmployeeBaseService employeeService;
	private UploadedObjectView uploadedObjectView;
	public EmployeeBaseService getEmployeeService() {
		return employeeService;
	public ModelAndView downloadFile(@RequestParam("empId") long empId){
		Map model = new HashMap();
		Employee empMap = employeeService.getUploadedFileForEmployee(empId);
		model.put("data", empMap.getFileDataBytes());
		model.put("contentType", empMap.getFileContentType());
		model.put("filename", empMap.getFileName());
		return new ModelAndView(uploadedObjectView, model);

For the save of the employee along with its data and a detailed explanation of the annotations, please refer here.

Ok, so we are done with our controller, but since the file to be downloaded can be of different types, so we construct a generic UploadedObjectView and populate it with the file data.

public class UploadedObjectView extends AbstractView {
	//To rediect to another page, with inline text.
	protected void renderMergedOutputModel1(Map model,
			HttpServletRequest request, HttpServletResponse response) throws Exception {
		  byte[] bytes = (byte[]) model.get("data");
	      String contentType = (String) model.get("contentType");
	      ServletOutputStream out = response.getOutputStream();
	//For a download option
	protected void renderMergedOutputModel(Map model,
			HttpServletRequest request, HttpServletResponse response) throws Exception {
		byte[] bytes = (byte[]) model.get("data");
		String contentType = (String) model.get("contentType");
		response.addHeader("Content-disposition","attachment; filename="+model.get("filename"));
		ServletOutputStream out = response.getOutputStream();

We have 2 methods above:

If we override with the 1st method, the output will be redirected to another page with an inline text / image.

For a totally generic implementation, the overridden method 2 is safer as it provides with a download option.

That’s it folks :)! I hope this could be a help to somebody.

One common error which I have encountered in Mybatis while uploading large files was :

SQL state [72000]; error code [1013]; ORA-03111: break received on communication channel

This error is due to the query timeout being exceeded.

From the mybatis-config.xml(more info here), changed the value from 10 to 100 to resolve this issue.

<setting name="defaultStatementTimeout" value="100" />

Great post here by Juergen Hoeller if you need further inputs or just anything about Spring in particular. Thanks Juergen!

  1. angel says:

    Thank for your help , it should amazing im going to try it

  2. Lakshmanan M says:

    Excellent tutorial. Regarding the upload file limit error: Does this happen even if we just provided the limit maximum upload file limit in the multipartResolver bean?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s