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 :
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!
