Conversion from Clob To String in java and Vice versa


/***reading Clob data from oracle table and setting to Pojo**/


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import oracle.sql.CLOB;

import com.poc.one.pojo.StagedMessage;

public class MessageDao {
private static String url = "jdbc:oracle:thin:@172.22.22.198:1521:ORACLE";
private static String username = "ABC123";
private static String password = "ABC123";


public  List readClobToBO() throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, username, password);
PreparedStatement stmt = conn
.prepareStatement("SELECT ID, STORE_ID, MESSAGE_DATA FROM STAGED_MESSAGE ");
ResultSet resultSet = stmt.executeQuery();
List msgList= new ArrayList();

while (resultSet.next()) {

StagedMessage message = new StagedMessage();

message.setId(resultSet.getInt(1));
message.setStoreId(resultSet.getInt(2));


/**Converting from Clob to String and setting to Pojo **/
CLOB notes = (CLOB) resultSet.getClob("MESSAGE_DATA");
long len = notes.length();
String substring = notes.getSubString(1, (int) len);
System.out.println("reading Clob data before setting to SM Object==> "+substring+"Length is ==>"+len);
message.setMessageData(substring);

//message.setMessageData(resultSet.getClob(3).toString());
msgList.add(message);
}
conn.close();
System.out.println("rows read from stage message table==>"+msgList.size());
return msgList;
}
}



/***reading string and coverting to clob for inserting into table**/



package com.poc.one.dao;

import java.io.File;
import java.io.FileReader;
import java.io.Reader;
import java.io.StringReader;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.sql.CLOB;

import com.poc.one.pojo.StagedMessage;

public class MessageDao {
private static String url = "jdbc:oracle:thin:@172.22.22.198:1521:ORCALE";
private static String username = "ABC123";
private static String password = "ABC123";

public void WriteToDB(StagedMessage stagedMessage) throws Exception {

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);

String sql = "INSERT INTO STAGED_MESSAGE2 (ID, STORE_ID, MESSAGE_DATA) VALUES (?, ?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, stagedMessage.getId());
stmt.setInt(2, stagedMessage.getStoreId());
String msg = stagedMessage.getMessageData().toString();

System.out.println("Data is String format before converting to Clob==>"+msg);
StringReader stringReader = new StringReader(msg);
stmt.setCharacterStream(3, stringReader , msg.length());
/** *alternate Approach***/
// CLOB clob = null;
// clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);
// clob.setString(3, msg);
// stmt.setClob(3, clob);
/** *alternate Approach***/




conn.commit();
conn.close();

}

















Comments

Popular posts from this blog

Portable Java 8/ JDK8 setup without admin rights

Multi Threading , Producer consumer problem in Java