使用mybatis从Oracle DB中选择BLOB列

3 回答

• 0

  你可以直接使用BLOB，做 import oracle.sql.BLOB;

  例子：

  BLOB blob = (BLOB)map.get("binfile");
  
  //one way: as array
  byte[] bytes = blob.getBytes(1L, (int)blob.length());
  System.out.println(new String(bytes)); //use for text data
  System.out.println(Arrays.toString(bytes));
  
  //another way: as stream
  BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream("data.bin"));
  InputStream is = blob.binaryStreamValue();
  int b = -1;
  while ((b = is.read()) != -1) {
      bos.write(b);
  }
  bos.close();
  

  回复于 2024-04-25T19:06:00+08:00
• 2

  您是否尝试将字段映射到jdbcType = LONGVARBINARY？

  回复于 2024-04-25T19:06:00+08:00
• 0

  在我的情况下，我必须实现custom BaseTypeHandler to support Oracle BLOB conversion to byte[] for Mybatis .

  • 将Oracle JDBC驱动程序添加到项目中，您也需要 mybatis 依赖项 . 如果您使用Maven：

  <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc14</artifactId>
      <version>10.2.0.3.0</version>
  </dependency>
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.2.1</version>
  </dependency>
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.3</version>
  </dependency>
  

  • 添加custom BaseTypeHandler for reading byte[] from Oracle BLOB类：

  @MappedTypes(byte[].class)
  public class OracleBlobTypeHandler extends BaseTypeHandler<byte[]> {
      @Override
      public void setNonNullParameter(PreparedStatement preparedStatement, int i, byte[] bytes, JdbcType jdbcType) throws SQLException {
          // see setBlobAsBytes method from https://jira.spring.io/secure/attachment/11851/OracleLobHandler.java
          try {
              if (bytes != null) {
                  //prepareLob
                  BLOB blob = BLOB.createTemporary(preparedStatement.getConnection(), true, BLOB.DURATION_SESSION);
  
                  //callback.populateLob
                  OutputStream os = blob.getBinaryOutputStream();
                  try {
                      os.write(bytes);
                  } catch (Exception e) {
                      throw new SQLException(e);
                  } finally {
                      try {
                          os.close();
                      } catch (Exception e) {
                          e.printStackTrace();//ignore
                      }
                  }
                  preparedStatement.setBlob(i, blob);
              } else {
                  preparedStatement.setBlob(i, (Blob) null);
              }
          } catch (Exception e) {
              throw new SQLException(e);
          }
      }
  
      /** see getBlobAsBytes method from https://jira.spring.io/secure/attachment/11851/OracleLobHandler.java */
      private byte[] getBlobAsBytes(BLOB blob) throws SQLException {
  
          //initializeResourcesBeforeRead
          if(!blob.isTemporary()) {
              blob.open(BLOB.MODE_READONLY);
          }
  
          //read
          byte[] bytes = blob.getBytes(1L, (int)blob.length());
  
          //releaseResourcesAfterRead
          if(blob.isTemporary()) {
              blob.freeTemporary();
          } else if(blob.isOpen()) {
              blob.close();
          }
  
          return bytes;
      }
  
      @Override
      public byte[] getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
          try {
              //use a custom oracle.sql.BLOB
              BLOB blob = (BLOB) resultSet.getBlob(columnName);
              return getBlobAsBytes(blob);
          } catch (Exception e) {
              throw new SQLException(e);
          }
      }
  
      @Override
      public byte[] getNullableResult(ResultSet resultSet, int i) throws SQLException {
          try {
              //use a custom oracle.sql.BLOB
              BLOB blob = (BLOB) resultSet.getBlob(i);
              return getBlobAsBytes(blob);
          } catch (Exception e) {
              throw new SQLException(e);
          }
      }
  
      @Override
      public byte[] getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
          try {
              //use a custom oracle.sql.BLOB
              BLOB blob = (BLOB) callableStatement.getBlob(i);
              return getBlobAsBytes(blob);
          } catch (Exception e) {
              throw new SQLException(e);
          }
      }
  }
  

  • 将类型处理程序包添加到mybatis配置 . 如你所见，我正在使用spring-mybatis：

  <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
      <property name="dataSource" ref="dataSource" />
      <property name="typeHandlersPackage" value="package.where.customhandler.is" />
  </bean>
  

  • 然后，您可以从Mybatis的Oracle BLOB中读取byte []：

  public class Bean {
      private byte[] file;
  }
  
  interface class Dao {
      @Select("select file from some_table where id=#{id}")
      Bean getBean(@Param("id") String id);
  }
  

  我希望这将有所帮助 . 这是对这个优秀答案的改编：https://stackoverflow.com/a/27522590/2692914 .

  回复于 2024-04-25T19:06:00+08:00