Announcement Announcement Module
Collapse
No announcement yet.
Inserting BLOB using BeanPropertySqlParameterSource ? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Inserting BLOB using BeanPropertySqlParameterSource ?

    Hi!

    I have defined a bean (class Foo) having one Long and one byte[] property.

    When reading with SimpleJdbcCall ....returningResultSet("return_parameter",
    BeanPropertyRowMapper.newInstance(Foo.class)). execute(some_parameters). get("return_parameter"). get(0) it all works fine.

    The stored procedure is defined as:
    Code:
    procedure getFoo(a_id IN INTEGER,  return_parameter OUT sys_refcursor);
    The return is a select from a table with two columns: NUMBER and BLOB.

    The DB is Oracle.

    The problem is writing.

    Code:
    procedure createFoo(fooId IN INTEGER, data IN BLOB)
    Java code:
    Code:
    new SimpleJdbcCall(m_dataSource).withProcedureName("createFoo").
      execute(new BeanPropertySqlParameterSource(newFoo))
    newFoo is an initialized object of class Foo.

    The problem is, that the data proprty is set to SQL type OTHER.

    If I set the type manually to BLOB like this:
    Code:
    .execute(new BeanPropertySqlParameterSource(newFoo).registerSqlType("data", Types.BLOB))
    Then I get a java.lang.ClassCastException: [B cannot be cast to oracle.sql.BLOB


    So the conversion works when reading (BLOB to byte array), but not when writing (byte array to BLOB).
    Am I missing something obvious here?

    What is the proper solution?

    Maybe I could use an instance of java.sql.Blob, but I don't want to put SQL specific code in the upper layers of my application (Foo is a DTO).


    Regards,
    David
    Last edited by xerces8; Feb 11th, 2011, 04:12 AM. Reason: Mark as resolved

  • #2
    Aha, according to org.springframework.jdbc.core.StatementCreatorUtil s.javaTypeToSqlTypeMap , only Blob.class is mapped to BLOB, so I have to cook up something by hand.

    Comment


    • #3
      It is harder than I thought....

      Another try:
      Code:
      MapSqlParameterSource inParams = new MapSqlParameterSource();
      inParams.addValue("a_id", foo.getA_Id());
      inParams.addValue("data", new SerialBlob(foo.getData()));
      This gives ORA-17004 "Invalid column type".

      If I add:
      Code:
      inParams.registerSqlType("data", Types.BLOB);
      I get:
      ClassCastException: javax.sql.rowset.serial.SerialBlob cannot be cast to oracle.sql.BLOB



      Next try:

      Code:
      ...
      Blob blob;
      blob = m_dataSource.getConnection().createBlob();
      blob.setBytes(1, foo.getData());
      inParams.addValue("data", blob);
      
      inParams.registerSqlType("data", Types.BLOB);
      ...
      Result:
      ORA-22922: nonexistent LOB value

      Without the registerSqlType() I get the 17004 error again.

      Comment


      • #4
        Fixed!

        Instead of SimpleJdbcCall I used AbstractLobCreatingPreparedStatementCallback, as described on its JavaDoc: http://static.springsource.org/sprin...tCallback.html

        Here is the new code:
        Code:
        m_jdbcTemplate.execute("{ call createFoo(?, ?) }",
        	new AbstractLobCreatingPreparedStatementCallback(m_lobHandler) {
        		protected void setValues(PreparedStatement ps,
        				LobCreator lobCreator) throws SQLException {
        			ps.setLong(1, foo.getAmEnquiryId());
        			lobCreator.setBlobAsBytes(ps, 2, foo.getData());
        		}
        	});

        Comment


        • #5
          Better solution

          I ran into this same error "java.lang.ClassCastException: [B cannot be cast to oracle.sql.BLOB" and was frustrated by it for several hours. In the end I started trying to register my byte[] field to different data types.

          The simple solution that still allowed me to use BeanPropertySqlParameterSource was to register my byte[] field (named "data") as "BINARY" instead of "BLOB"

          Code:
          BeanPropertySqlParameterSource paramSource = new BeanPropertySqlParameterSource(entity);
          		
          paramSource.registerSqlType("data", Types.BINARY);
          Scott

          Comment

          Working...
          X