How to write / read BLOB to / from Oracle database?

WRITE:

To transform String value into blob just execute your_string.getBytes();

String blobValue = "long blob";
bytes[] bytes = blobValue.getBytes();
int rows = getJdbcTemplate().update("update MY_TABLE set BLOB_COLUMN = ? where ROWID = '1'", bytes);

READ:

we can either read blob from database in Blob class or transform into String. To transform it into String we will need additional custom method:

public static String sqlBlobToString(Blob blob) {

StringBuilder stringBuilder = new StringBuilder();

try(BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(blob.getBinaryStream())) {
       while ((string = bufferedReader.readLine()) != null) {
              stringBuilder.append(string);
       }
 } catch (IOException e) {
       throw new RuntimeException(e);
}
return stringBuilder;
}


Querying from database:

String s = sqlBlobToString(getJdbcTemplate().queryForObject("select BLOB_COLUMN from MY_TABLE where ROWID = '1'", Blob.class));

Note: I am using java.sql.Blob

How to write / read CLOB to / from Oracle database?

WRITE:

there is no problems with clob saving into db - just keep it in String value and regularly save it:

String clobValue = "long clob";
int rows = getJdbcTemplate().update("update MY_TABLE set CLOB_COLUMN = ? where ROWID = '1'", clobValue);

READ:

we can either read clob from database in Clob class or transform into String. To transform it into String we will need additional custom method:

public static String sqlClobToString(Clob clob) {

StringBuilder stringBuilder = new StringBuilder();

try(BufferedReader bufferedReader = new BufferedReader(clob.getCharacterStream()) {
       while ((string = bufferedReader.readLine()) != null) {
              stringBuilder.append(string);
       }
 } catch (IOException e) {
       throw new RuntimeException(e);
}
return stringBuilder;
}

Querying from database:

String s = sqlClobToString(getJdbcTemplate().queryForObject("select CLOB_COLUMN from MY_TABLE where ROWID = '1'", Clob.class));

Note: I am using java.sql.Clob

How To Acсess Target Object Behind a Spring Proxy?

If you have @Autowired spring bean but for some reason (preferably for unit testing purposes in order to stub something) you want to get target object behind Spring proxy:

((Advised)yourAutowiredBean).getTargetSource().getTarget();

AopUtils.isJdkDynamicProxy(yourAutowiredBean) - can confirm you got proxied object if you have any doubts.

Good DataSource

DriverManagerDataSource by Spring can act weird sometimes, so the best choice to use Apache data source: BasicDataSource.

Configuration:

@Bean

public DataSource dataSource() {

BasicDataSource dataSource = new BasicDataSource();

dataSource.setUrl(JDBC_URL);

dataSource.setUsername(USERNAME);

dataSource.setPassword(PASSWORD);

dataSource.setDriverClassName(DRIVER_CLASS_NAME);

}

Maven:

<dependency>

<groupId>org.apache.commons</groupId>

<artifactId>commons-dbcp2</artifactId>

<version>2.1</version>

</dependency>