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 10.2.0.4
Reqd. libs : commons-fileupload-*.jar , commons-io*.jar , ojdbc14.jar and obviously the reqd. spring and mybatis jars.

Database columns in EMPLOYEE table:

FILENAME VARCHAR2 (100 Byte),
FILECONTENTTYPE VARCHAR2 (100 Byte),
FILEDATA BLOB.

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"
    class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
	<property name="maxUploadSize">
		<value>10000000</value>
	</property>
</bean>

The fileupload size is in Bytes.

Model object / Form data

@Alias(value="emp")
public class Employee {
       private CommonsMultipartFile fileData;
       private byte[] fileDataBytes;
       private String fileName;
       private String fileContentType;
       //getters/setters
}

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
	<set>
<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},
</if>
	</set>
	where
	empId = #{empId}
</update>

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"
		resultType="emp">
		select empId,fileName,fileContentType,fileData as
		fileDataBytes
		from
		employee where
		empId=#{empId}

</select>

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.

@Controller
public class EmployeeController {
	@Autowired
	private EmployeeBaseService employeeService;
	@Autowired
	private UploadedObjectView uploadedObjectView;
	public EmployeeBaseService getEmployeeService() {
		return employeeService;
	}
     //getters,setters
         @RequestMapping(value="/employeeDownloadFile")
	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");
	      response.setContentType(contentType);
	      response.setContentLength(bytes.length);
	      ServletOutputStream out = response.getOutputStream();
	      out.write(bytes);
	      out.flush();
	}
	//For a download option
	@Override
	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"));
		response.setContentType(contentType);
		response.setContentLength(bytes.length);
		ServletOutputStream out = response.getOutputStream();
		out.write(bytes);
		out.flush();
	}
}

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!

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s