oracle 如何使用 iBatis 从数据库中选择 BLOB 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12059872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Select a BLOB column from database using iBatis
提问by yathirigan
One of a table's column is of BLOB datatype (Oracle 10g). We have a simple select query executed via iBatis to select the BLOB column and display it using Struts2 & JSP.
表的列之一是 BLOB 数据类型 (Oracle 10g)。我们有一个通过 iBatis 执行的简单选择查询来选择 BLOB 列并使用 Struts2 和 JSP 显示它。
The result tag in the iBatis xml file had the jdbctype as java.sql.Blob
iBatis xml 文件中的结果标签的 jdbctype 为 java.sql.Blob
<result property="uploadContent" column="uploadcontent" jdbctype="Blob"/>
Should we be mentioning any typeHandler class for Blob column ? Currently we are getting an error stating column type mismatch.
我们应该提到 Blob 列的任何 typeHandler 类吗？目前我们收到一个错误，指出列类型不匹配。
Note: This column is selected and mapped into a java bean who has an attribute of type java.sql.Blob
注意：此列被选中并映射到具有 java.sql.Blob 类型属性的 java bean
采纳答案by AnBisw
I think you cannot use native jdbctypefor LOBtypes in Oracle with iBatis. The solution is to create custom typeHandlerto handle LOBand then map it like -
我认为您不能将本机jdbctype用于LOBOracle 中的类型和iBatis. 解决方案是创建自定义typeHandler来处理LOB，然后将其映射为 -
<result property="aClassStringProperty" column="aClobColumn" typeHandler="com.path.to.my.ClobTypeHandler"/>
More information on typeHandlerCallbackhere.
更多信息请typeHandlerCallback点击此处。
回答by David García González
It is not neccesary to create a typeHandler. For Oracle, the jdbctype is BLOB
创建 typeHandler 不是必需的。对于Oracle， jdbctype 是BLOB
<result property="bytes" column="COLUMNBLOB" jdbcType="BLOB" />
Assumming "bytes" as byte [].
假设“字节”为字节 []。
The important thing: in the select sql, you must set the jdbcType in this way:
重要的是：在select sql中，必须这样设置jdbcType：
INSERT INTO X (COLUMNBLOB) VALUES #bytes:BLOB#
I noticed that this jdbctype for Postgresqlis different. You must set:
我注意到Postgresql 的这个 jdbctype是不同的。您必须设置：
<result property="bytes" column="COLUMNBLOB" jdbcType="BINARY" />
回答by lmiguelmh
I dindn't have problems using INSERTs, my problems where when I did SELECT of the blob type. I am using Oracle 9i and this is how I've done:
我在使用 INSERT 时没有问题，我在执行 blob 类型的 SELECT 时遇到了问题。我正在使用 Oracle 9i，这就是我所做的：
Add the Oracle JDBC driver to your project, you will need
mybatisdependencies too. If you are using 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>Add the custom BaseTypeHandler for reading byte[] from Oracle BLOBclass:
@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); } } }Add the type handlers package to mybatis configuration. As you can see, I am using spring-mybatis:
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="typeHandlersPackage" value="package.where.customhandler.is" /> </bean>And then, you can read byte[] from Oracle BLOBs from Mybatis:
public class Bean { private byte[] file; } interface class Dao { @Select("select file from some_table where id=#{id}") Bean getBean(@Param("id") String id); }
将 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>添加自定义 BaseTypeHandler 以从 Oracle BLOB类中读取 byte[]：
@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); } } }将 type handlers 包添加到 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); }
I hope this will help. This is an adaptation of this excellent answer: This is an adaptation of this excellent answer: https://stackoverflow.com/a/27522590/2692914.
我希望这将有所帮助。这是对这个优秀答案的改编：这是对这个优秀答案的改编：https: //stackoverflow.com/a/27522590/2692914。
