Spring JdbcTemplate 应用(三)

转自：http://rockjava.iteye.com/blog/299964

 

在这篇文章里介绍用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;