Java: Reading Blob from Oracle

Question

I have this code that converts a Treemap into bytes and store them in database (Oracle 11g). Now that storage seems to be working fine. I want to retrieve the map now, but it is in bytes in blob field. How can I retrieve and re-construct the map?

The code for storing the map is:

 public void StoreMapDB(TreeMap<DateTime, Integer> map) throws
        IOException, FileNotFoundException{

       try {
          Connection con = null;

          Class.forName("oracle.jdbc.driver.OracleDriver");
          con=DriverManager.getConnection(
            "jdbc:oracle:thin:@dsds",
            "dsdsd",
            "XXdsdsX");
          con.setAutoCommit(false);
          ByteArrayOutputStream bos = new ByteArrayOutputStream() ;
          ObjectOutputStream out = new ObjectOutputStream(bos);
          out = new ObjectOutputStream(bos) ;
          out.writeObject(map);
          out.close();

          byte[] buf = bos.toByteArray();
          PreparedStatement prepareStatement = con.prepareStatement("INSERT INTO 
           SMD_DATESTREEMAP VALUES(?,?)");
          prepareStatement.setLong(1, 2);
          prepareStatement.setBinaryStream(2, new ByteArrayInputStream(buf),
           buf.length);
          prepareStatement.executeUpdate();


         // insertMap.executeUpdate();
          con.commit();
    } catch(Exception e){
        System.err.print(e);
    }
}

P.S. I edited this code but don't think it works because it displays size of retrieved map as 0 where it should be 366.

  public TreeMap<DateTime, Integer> retrieveMapDB()throws IOException,
        SQLException{

    try {
        Connection con = null;

        Class.forName("oracle.jdbc.driver.OracleDriver");
        con=DriverManager.getConnection(
            "jdbc:oracle:thin:@oradbfdfdt05:f:fdfd",
            "cxcx",
            "hpdbcxcxsmb");
        con.setAutoCommit(false);
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        String query = "SELECT TREEMAP FROM SMD_DATESTREEMAP WHERE id = ?";
        try {
            pstmt = con.prepareStatement(query);
            int id = 1;
            pstmt.setInt(1, id);

            rs = pstmt.executeQuery();
            while(rs.next()){
                ByteArrayInputStream bos = new 
           ByteArrayInputStream(rs.getBytes("TREEMAP")) ;
                ObjectInputStream out = new ObjectInputStream(bos);
                retrievedmap=(TreeMap<DateTime, Integer>)out.readObject();
            }


        }catch(IOException ioe){
            System.err.print(ioe);
        }
    }catch(ClassNotFoundException cnfe){
        System.err.print(cnfe);
    }
  return retrievedmap;
}

svarog · Accepted Answer · 2020-11-22 07:52:50Z

2

You may obtain InputStream object via ResultSet.getBinaryStream() method.

PreparedStatement prepareStatement = con.prepareStatement("select * from SMD_DATESTREEMAP");
ResultSet rs=prepareStatement.executeQuery();
      
while(rs.next())
{
   oracle.jdbc.driver.OracleBlobInputStream bos = (oracle.jdbc.driver.OracleBlobInputStream) rs.getBinaryStream(2) ;
       
   ObjectInputStream out = new ObjectInputStream(bos);
      
   map = (TreeMap<DateTime, Integer>) out.readObject();
   ...
}

You may write byte array instead of binary stream.

ByteArrayOutputStream bos = new ByteArrayOutputStream() ;
ObjectOutputStream out = new ObjectOutputStream(bos);
out.writeObject(map);
out.flush();
out.close();
byte[] buf = bos.toByteArray();
      
PreparedStatement prepareStatement = con.prepareStatement("INSERT INTO SMD_DATESTREEMAP VALUES(?,?)");
prepareStatement.setInt(1, 1);
prepareStatement.setBytes(2, buf);
prepareStatement.executeUpdate();
prepareStatement.close();

and read byte array:

while(rs.next())
{ 
    byte []buf=rs.getBytes(2);
    ByteArrayInputStream bos=new ByteArrayInputStream(buf);
    ObjectInputStream out = new ObjectInputStream(bos);
    map=(TreeMap<DateTime, Integer>)out.readObject();
    ..
}

edited Nov 22, 2020 at 7:52

svarog

9,8755 gold badges66 silver badges79 bronze badges

answered Dec 9, 2011 at 13:45

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sys_debug Over a year ago

i tried the oracle.jdbc.drive... but didnt work. it says it can't be accessed out of package because its not public?

sys_debug Over a year ago

Ok so before I read your post, i tried the above(question modified) but it didn't work. Any ideas as to why?

sys_debug Over a year ago

ur code to read byte array if placed in the method gives java.sql.SQLException: ResultSet.next was not called

KV Prajapati Over a year ago

@sys_debug - next() method must be called before you start read. Try to flush the ObjectOutputStream before it closes.

sys_debug Over a year ago

I did that now, but I believe that the map retrieved is empty. I tested that with if(map.isEmpty()) System.out.println("Empty");

Magnus Reftel · Accepted Answer · 2017-12-06 12:11:05Z

1

thanks to AVD for supporting greatly and also this worked most with my code/question. So the answer is here...BLOB from Oracle

And the snippet that worked in complement with AVD's answer, is:

     Blob blob = rs.getBlob("col_blob");

    // Get the number bytes in the BLOB
    long blobLength = blob.length();

    // Get bytes from the BLOB in a byte array
    int pos = 1;   // position is 1-based
    int len = 10;
    byte[] bytes = blob.getBytes(pos, len);

Hope this would be helpful and if anyone require clarification, i will be more than Happy :D

edited Dec 6, 2017 at 12:11

Magnus Reftel

9997 silver badges21 bronze badges

answered Dec 10, 2011 at 5:24

sys_debug

4,01318 gold badges71 silver badges99 bronze badges

Collectives™ on Stack Overflow

Java: Reading Blob from Oracle

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related