在这篇文章里介绍用JdbcTemplate进行数据库插入操作,包括对blob或clob字段的插入
还有对blob字段的取出操作。
1.使用JdbcTemplate往数据库里插入数据,其中包含blob字段。
public boolean doSubmitWeekly(final WeeklyVO weeklyVO)
throws DataAccessException {
StringBuffer sql = new StringBuffer();
sql.append("INSERT INTO WEEKLY_INFO_T T (T.F_START_TIME, ");
sql.append(" T.F_END_TIME, ");
sql.append(" T.F_DATE, ");
sql.append(" T.F_OWNER, ");
sql.append(" T.F_ANNEX_NAME, ");
sql.append(" T.F_ANNEX) ");
sql.append(" VALUES (TO_DATE (?, 'yyyy-mm-dd'), ");
sql.append(" TO_DATE (?, 'yyyy-mm-dd'), ");
sql.append(" TO_DATE (to_char(sysdate,'yyyy-mm-dd'), 'yyyy-mm-dd'), ");
sql.append(" ?, ");
sql.append(" ?, ");
sql.append(" ?) ");//blob字段
Boolean flag = new Boolean(false);
try {
flag = (Boolean) this.getJdbcTemplate().execute(sql.toString(),
new MyPreparedStatementCallback(weeklyVO));
} catch (Exception e) {
e.printStackTrace();
}
return flag.booleanValue();
}
MyPreparedStatementCallback类的实现
/**
* 上传附件回调操作类
*/
private class MyPreparedStatementCallback implements
PreparedStatementCallback {
private WeeklyVO weeklyVO;
public MyPreparedStatementCallback(WeeklyVO weeklyVO) {
this.weeklyVO = weeklyVO;
}
public Object doInPreparedStatement(PreparedStatement pstm)
throws SQLException,
org.springframework.dao.DataAccessException {
pstm.setObject(1, this.weeklyVO.getStartTime());
pstm.setObject(2, this.weeklyVO.getEndTime());
pstm.setObject(3, this.weeklyVO.getOwner());
pstm.setObject(4, this.weeklyVO.getAnnexName());
try {
// 操作Blob ---这里WeeklyVO类的annex属性是File类型
pstm.setBinaryStream(5, new FileInputStream(this.weeklyVO
.getAnnex()), (int) (this.weeklyVO.getAnnex()).length());
// 操作Clob
/**
pstm.setCharacterStream(5, new FileReader(this.weeklyVO
.getAnnex()), (int) (this.weeklyVO.getAnnex()).length());
*/
} catch (FileNotFoundException e) {
e.printStackTrace();
return new Boolean(false);
}
try {
pstm.execute();
return new Boolean(true);
} catch (Exception e) {
e.printStackTrace();
return new Boolean(false);
}
}
}
2.使用JdbcTemplate读取数据库中的blob字段信息(把blob内容写到临时目录)
public Map doSelectWeekly(String weeklyId) throws DataAccessException {
String sql = "select t.f_annex_name,t.f_annex from weekly_info_t t"
+ " where t.f_weekly_id = " + weeklyId;
Map map = new HashMap();
map = (Map) this.getJdbcTemplate().execute(sql,
new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement stmt)
throws SQLException,
org.springframework.dao.DataAccessException {
ResultSet rs = stmt.executeQuery();
Map map = new HashMap();
InputStream inputStream = null;
String name = "";
String path = System.getProperty("java.io.tmpdir")
+ "/";
File temp = new File(path);
if (!temp.exists()) {
temp.mkdir();
}
temp = null;
while (rs.next()) {
inputStream = rs.getBinaryStream("f_annex");// 读取blob
//Reader fileReader = rs.getCharacterStream("f_annex");// 读取clob
name = rs.getString("f_annex_name");
path += name;
File fileOutput = new File(path);
FileOutputStream fo;
try {
fo = new FileOutputStream(fileOutput);
int readed;
// 将附件写到临时目录里
while ((readed = inputStream.read()) != -1) {
fo.write(readed);
}
fo.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
map.put("annexName", name);
map.put("filePath", path);
return map;//返回文件名称和文件所在路径,供页面下载用。
}
});
return map;
}
附:下载blob内容代码片段(先把blob内容写到临时目录在从临时目录下载)
Map map = weeklyServise.doSelectWeekly("52");//参数为附件ID
String annexName = (String) map.get("annexName");
String path = (String) map.get("filePath");
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
OutputStream fos = null;
InputStream fis = null;
String filepath = path;
System.out.println("文件路径" + filepath);
java.io.File uploadFile = new java.io.File(filepath);
//从低级流构造成高级流
fis = new FileInputStream(uploadFile);
bis = new BufferedInputStream(fis);
fos = response.getOutputStream();
bos = new BufferedOutputStream(fos);
//设置下载文件名
response.setHeader("Content-disposition", "attachment;filename="
+ URLEncoder.encode(annexName, "utf-8"));
int bytesRead = 0;
byte[] buffer = new byte[4096];
while ((bytesRead = bis.read(buffer, 0, 4096)) != -1) {
bos.write(buffer, 0, bytesRead);//开始下载数据
}
bos.flush();
fis.close();
bis.close();
fos.close();
bos.close();
java.io.File temp = new java.io.File(System.getProperty("java.io.tmpdir")+"/");
if(temp.isDirectory()){
FileUtils.deleteDirectory(temp);//删除临时文件夹
}
return null;