import java.io.ByteArrayInputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* @author http://java-sample-program.blogspot.in/
* Write CLOB to database
*/
public class ClobTest {
public static void main(String[] args) throws Exception{
Connection con = null;
CallableStatement stmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@IPAddress:Port:Database",
"username","password");
stmt = con.prepareCall("{call CLOBTEST.saveClob(?)}");
String xmlString = "1234567890";
stmt.setAsciiStream(1,new ByteArrayInputStream(xmlString.getBytes()),
xmlString.getBytes().length);
stmt.execute();
System.out.println("executed successfully");
}catch(Exception e){
e.printStackTrace();
} finally {
try{
if(stmt != null){
stmt.close();
}
if(con!= null){
con.close();
}
}catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Thursday 21 March 2013
How to write CLOB fields
Below code shows the way to write CLOB fields to the database:
CLOB to String Conversion
Below code shows the two ways to convert database CLOB to String code.
1) Convert CLOB to String using BufferedReader:
Use the above method if a very large Clob can be returned by the database.
2) Convert CLOB to String using getSubString() method:
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();
}
}
}
}
Subscribe to:
Posts (Atom)