通过Spring从PL / SQL读取Blob

I am trying to retrieve a Blob value thorugh PL/SQL, Spring and JDBC.

我试图通过PL / SQL,Spring和JDBC检索Blob值。

Here is my PL/SQL

这是我的PL / SQL

function GETBLOB(pjobid in number)
RETURN bobrecCur
is
vbobrecCur bobrecCur;
begin
   OPEN vbobrecCur FOR
   SELECT jobid, filecontent
   FROM TESTBULKJOBDATAFILE
   WHERE jobid = pjobid;
   RETURN vbobrecCur;
end GETBLOB

And my Java code is

我的Java代码是

this.getDataJdbcCall =
            new SimpleJdbcCall( this.jdbcTemplate )
                    .withFunctionName(  SQL_READ_DATA )
                    .withoutProcedureColumnMetaDataAccess()
                    .declareParameters(
                            new SqlOutParameter( "abc", OracleTypes.CURSOR ),
                            new SqlParameter( "pjobid", OracleTypes.INTEGER )
                    );

Map input = new HashMap();
    input.put( "pjobid", 99999 );

    ResultSet result = this.getDataJdbcCall.executeFunction(ResultSet.class , input );
    DefaultLobHandler lob =  new DefaultLobHandler();
    InputStream is = lob.getBlobAsBinaryStream( result, 1 );

I am getting the following exception.. basically saying that Resultset is null.

我得到以下异常..基本上说Resultset为null。

Exception in thread "main" java.lang.NullPointerException at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:91) at org.springframework.jdbc.core.JdbcTemplate.processResultSet(JdbcTemplate.java:1120) at org.springframework.jdbc.core.JdbcTemplate.extractOutputParameters(JdbcTemplate.java:1089) at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:996) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:935) at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:984) at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:364) at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:349) at org.springframework.jdbc.core.simple.SimpleJdbcCall.executeFunction(SimpleJdbcCall.java:137)

例外在线程org.springframework.jdbc.core.JdbcTemplate.processResultSet(JdbcTemplate.java:1120) “主” 显示java.lang.NullPointerException在org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:91)在org.springframework.jdbc.core.JdbcTemplate.extractOutputParameters(JdbcTemplate.java:1089)在org.springframework.jdbc.core.JdbcTemplate $ 5.doInCallableStatement(JdbcTemplate.java:996)在org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:935)在org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:984)在org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:364)的组织。 springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:349)在org.springframework.jdbc.core.simple.SimpleJdbcCall.executeFunction(SimpleJdbcCall.java:137)

I went through this question which should work for me. But I think the way I am using OracleLobHandler is not right.

我经历了这个应该对我有用的问题。但我认为我使用OracleLobHandler的方式不对。

Can anybody shed any light on where I am going wrong?

任何人都可以解释我出错的地方吗?

2 个解决方案

#1

Never mind, I figured out how to do it with a BLOB return type rather than a Cursor.

没关系,我想出了如何用BLOB返回类型而不是Cursor来做到这一点。

CREATE OR REPLACE function GETDATA(pjobid in number)
RETURN BLOB
is
begin
    SELECT filecontent into pblob
    from TESTDATA
    where jobid = pjobid;
    return pblob;
end;

And in Java I did this

在Java中我做到了这一点

this.getDataJdbcCall =
            new SimpleJdbcCall( this.jdbcTemplate )
                    .withFunctionName( SQL_READ_DATA )
                    .withoutProcedureColumnMetaDataAccess()
                    .declareParameters( new SqlOutParameter( "abc", OracleTypes.BLOB ),
                            new SqlParameter( "pjobid", OracleTypes.INTEGER ) );



    System.out.println( "Reading data" );

    Map input = new HashMap();
    input.put( "pjobid", 99999 );

    Blob result = this.getDataJdbcCall.executeFunction( Blob.class, input );

    InputStream is = result.getBinaryStream();

    byte[] b = new byte[1000];
    while ( true ) {
        if ( is.read( b ) == -1 )
            break;

        System.out.print( new String( b ) );
    }

#2

package test;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class SelectBlobBug {
    public static void main(String[] args) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(new DriverManagerDataSource("jdbc:derby:test-db;create=true"));

        jdbcTemplate.execute("DROP TABLE blob_test");
        jdbcTemplate.execute("CREATE TABLE blob_test (DATA BLOB NOT NULL)");
        byte[] binaryData = new byte[32700];
        for (int i = 0; i < binaryData.length; i++) {
            binaryData[i] = (byte) i;
        }
        jdbcTemplate.update("INSERT INTO blob_test VALUES (?)", binaryData);

        List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM blob_test");

        System.out.println(((byte[]) result.get(0).get("DATA")).length); // should be 32700
    }
}

#1