I need to read image from blob column and put it into a JSP to display in a web page.
I am using JPA and Spring MVC.
This is my sample FILESTORE table. The data type for column FILE_OBJ_L is BLOB.
1. The JPA Entity is as below.
@Entity
@Table(name="FILE_STORE")
@NamedQuery(name="FileStore.findAll", query="SELECT f FROM FileStore f")
public class FileStore implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name="FILE_ID")
private Long fileId;
@Column(name="FILE_NAME")
private String fileName;
@Lob
@Column(name="FILE_OBJ_L",length = 31457280)
@Basic(fetch = FetchType.LAZY)
private byte[] fileObjL;
private String mimetype;
public FileStore() {
}
public Long getFileId() {
return this.fileId;
}
public void setFileId(Long fileId) {
this.fileId = fileId;
}
public String getFileName() {
return this.fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public byte[] getFileObjL() {
return this.fileObjL;
}
public void setFileObjL( byte[] fileObjL) {
this.fileObjL = fileObjL;
}
:
:
2. DAO access layer (repository).
@Override
public FileStore findByFileId(Long fileId) {
EntityManager emgr = emf.createEntityManager();
try {
Query query = emgr
.createQuery("SELECT f FROM FileStore f WHERE f.fileId =:fileId");
query.setParameter("fileId", fileId);
FileStore filestore = (FileStore) query.getSingleResult(); // test and return
return filestore;
} catch (Exception ex) {
log.warn(
"Error during jpa query for filestore by file id: {} {}",
fileId, ex.getMessage() + " | " + ex.getCause());
} finally {
if (emgr != null) {
emgr.close();
}
}
return null;
}
3. I also have a service layer to call dao (repository).
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import model.FileStore;
import repository.FileStoreRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
@Service
public class FileStoreServiceImpl implements FileStoreService {
@Autowired
private FileStoreRepository filestoreRepository;
@Autowired
public FileStoreServiceImpl(FileStoreRepository filestoreRepository) {
this.filestoreRepository = filestoreRepository;
}
// FileStore requests by fileid
@Override
@Transactional(readOnly = true)
public FileStore findByFileId(Long fileid) throws DataAccessException {
return filestoreRepository.findByFileId(fileid);
}
}
4. Spring MVC controller class.
import javax.servlet.http.HttpServletResponse;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import model.FileStore;
import service.FileStoreService;
@Controller
public class FileController {
@Autowired
private FileStoreService fileStoreService;
@RequestMapping("/download/{fileId}")
public String download(@PathVariable("fileId") Long fileId,
HttpServletResponse response) {
try {
if (fileId == null) {
response.sendError(HttpServletResponse.SC_NOT_FOUND); // 404.
} else {
// Lookup file by fileId in database.
FileStore file = fileStoreService.findByFileId(fileId);
// Check if file is actually retrieved from database.
if (file != null) {
if (file.getFileObjL() != null) {
response.setContentType(file.getMimetype());
response.setHeader("Content-Length",
String.valueOf(file.getFileObjL().length));
// Write file content to response.
response.getOutputStream().write(file.getFileObjL());
}
}
}
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
5. JSP page.
<img src="${pageContext.request.contextPath}/download/7">
Here, fieId=7
Or just simply test it through URL:http: //localhost:8080/supsearch/download/7
6. Note:
- Remember, HTTP does not allow to get image blob directly from the object. Each image needs to be a separate request to a separate URL. So you will need a controller to write the blob to the response stream.
- When I worked on this, I also got an error when it called the JPA DAO. The error is
SQL Error: -134, SQLState: 42907 on blob column.
This means the string is too long.
At that time I used the query as below.
Query query = emgr.createQuery("SELECT distinct f FROM FileStore f WHERE f.fileId =:fileId");
Then I changed the query, and removed "distinct" since the fileId is unique one. Then everything works fine.
Query query = emgr.createQuery("SELECT f FROM FileStore f WHERE f.fileId =:fileId");
IBM wrote:
SQL0134N Improper use of a string column, host variable, constant, or function name.
Explanation: The use of the string name is not permitted.
An expression resulting in a string data type with a maximum length greater than 255 bytes is not permitted in:
* A SELECT DISTINCT statement
* A GROUP BY clause
* An ORDER BY clause
* A column function with DISTINCT
* A SELECT or VALUES statement of a set operator other than UNION ALL.
An expression resulting in a LONG VARCHAR or LONG VARGRAPHIC data type is not permitted in:
* A predicate other than EXISTS or NULL
* A column function
* The SELECT clause of a subquery of a predicate other than EXISTS or NULL
* The SELECT clause of a subselect in an INSERT statement
* The value expression of a SET clause in an UPDATE statement unless the expression is a LONG VARCHAR or LONG VARGRAPHIC host variable
* A SELECT statement of a set operator (except UNION ALL)
* VARGRAPHIC scalar function.
Federated system users: in a pass-through session, a data source-specific restriction can cause this error. See the SQL Reference documentation for the failing data sources.
The statement cannot be processed.
User Response: The requested operation on the string is not supported.
Note:
If it is unclear as to how the 255 byte limit is being exceeded, consider that codepage conversion operations may be required to evaluate the string expression. Depending on the source and target codepages, the target may have a greater length attribute than the source. For more information, refer to the SQL Reference for discussions on string restrictions and string conversions.
sqlcode:-134
sqlstate: 42907
