1) Convert CLOB to String using BufferedReader:
import java.io.BufferedReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author http://java-sample-program.blogspot.in/
* Convert CLOB to String using BufferedReader
*/
public class ClobTest {
public static void main(String[] args) throws Exception{
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:Port:Database",
"username","password");
pstmt = con.prepareStatement("SELECT clobCol FROM TableName");
rs = pstmt.executeQuery();
while(rs.next()){
StringBuffer strOut = new StringBuffer();
String aux;
try {
BufferedReader br = new BufferedReader(rs.getClob("clobCol").
getCharacterStream());
while ((aux=br.readLine())!=null) {
strOut.append(aux);
strOut.append(System.getProperty("line.separator"));
}
}catch (Exception e) {
e.printStackTrace();
}
String clobStr = strOut.toString();
System.out.println(clobStr);
}
}catch(Exception e){
e.printStackTrace();
} finally {
try{
if(pstmt != null){
pstmt.close();
}
if(rs!= null){
rs.close();
}
if(con!= null){
con.close();
}
}catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Use the above method if a very large Clob can be returned by the database.
2) Convert CLOB to String using getSubString() method:
import java.io.BufferedReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @author http://java-sample-program.blogspot.in/
* Convert CLOB to String using getSubString() method
*/
public class ClobTest {
public static void main(String[] args) throws Exception{
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:Port:Database",
"username","password");
pstmt = con.prepareStatement("SELECT clobCol FROM TableName");
rs = pstmt.executeQuery();
while(rs.next()){
Clob clob = rs.getClob("clobCol");
if (clob != null) {
String clobStr = clob.getSubString(1, (int) clob.length());
System.out.println(clobStr);
}
}
}catch(Exception e){
e.printStackTrace();
} finally {
try{
if(pstmt != null){
pstmt.close();
}
if(rs!= null){
rs.close();
}
if(con!= null){
con.close();
}
}catch (SQLException e) {
e.printStackTrace();
}
}
}
}