Archive for March, 2011

Using JPA to Insert and Retrieve BLOBs and CLOBs

Posted by stevedaskam in Hibernate, JPA on March 28, 2011

I spent some time recently exploring how to insert and retrieve BLOBs and CLOBs in Oracle using JPA/Hibernate. Here’s an example of how to do it:

The table:

CREATE table large_table_demo (
table_id Integer,

clob_column CLOB,

blob_column BLOB

);

The JPA annotated class:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.Table;

@Entity
@Table(name="LARGE_TABLE_DEMO")
public class LargeTable {
	private Long tableId;
	private String clob;
	private MyObject blob;
	
	@Id
	@Column(name="TABLE_ID")
	public Long getTableId() {
		return tableId;
	}
	
	public void setTableId(Long tableId) {
		this.tableId = tableId;
	}
	
	@Lob
	@Column(name="CLOB_COLUMN")
	public String getClob() {
		return clob;
	}
	
	public void setClob(String clob) {
		this.clob = clob;
	}

	@Lob
	@Column(name="BLOB_COLUMN")
	public MyObject getBlob() {
		return blob;
	}

	public void setBlob(MyObject blob) {
		this.blob = blob;
	}
	
}

The Test Class:

public class BlobTest extends BaseDaoTests {

    @Autowired
    protected BlobDao blobDao;
	
    public void testJpaBlobSave() throws Exception {
        // Read in serialized object
        FileInputStream f_in = new FileInputStream("c:\\myobject.data");
        ObjectInputStream obj_in = new ObjectInputStream (f_in);
        Object obj = obj_in.readObject();
        MyObject myObject = (MyObject)obj;
	    	
        LargeTable lt = new LargeTable();
        lt.setTableId(1L);
        lt.setBlob(myObject);
	    	
        // Save file to DB
        blobDao.saveObject(lt);    	
    }
    
    public void testJpaBlobRetrieve() throws Exception {
    	Object obj = blobDao.find(LargeTable.class, 1L);
    	LargeTable lt = (LargeTable)obj;
        assertEquals(Long.valueOf(1L), lt.getTableId());
    }

    public void testClobSave() throws Exception {
       // Read in text file
       String s = readFileAsString("c:\\clobtest.txt");
       LargeTable lt = new LargeTable();
       lt.setTableId(2L);
       lt.setClob(s);
    		
       blobDao.saveObject(lt);
    }

    public void testClobRetrieve() throws Exception {
        Object obj = blobDao.find(LargeTable.class, 2L);
        LargeTable lt = (LargeTable)obj;
        assertEquals(Long.valueOf(2L), lt.getTableId());
    }
	
    private String readFileAsString(String filePath) throws java.io.IOException {
        StringBuffer fileData = new StringBuffer();
        BufferedReader reader = new BufferedReader(
                new FileReader(filePath));
        char[] buf = new char[1024];
        int numRead=0;
        while((numRead=reader.read(buf)) != -1){
            String readData = String.valueOf(buf, 0, numRead);
            fileData.append(readData);
            buf = new char[1024];
        }
        reader.close();
        return fileData.toString();
    }
}

Leave a Comment

Copy from Excel into HTML Table

Posted by stevedaskam in Web on March 27, 2011

Using the code below, you can paste a column of cells from an Excel spreadsheet into a column of an HTML table. Just open up a spreadsheet, select multiple cells in a single column, and hit CTRL-C. Then open up this code in your web browser, and click the Paste button.

NOTE that this code only works with the IE browser.

<html>
<head><title>Test Copy/Paste</title>
<script>
function pasteFromClipboard() {
   var data = window.clipboardData.getData('Text');
   if (data != null) {
      var cells = data.split('\n');
      var rowCnt = 5;

      for (i = 0; i < cells.length; i++) {
         if (i == rowCnt) return;
         var tbId = 'r' + i + 'c1';
         document.getElementById(tbId).value = cells[i];
      }
   }
}
</script>
</head>

<body>
   <form action="#" method="get">
      <table>
         <tr>
            <td><input type="text" id="r0c1"/></td>
            <td><input type="text" id="r0c2"/></td>
            <td><input type="text" id="r0c3"/></td>
            <td><input type="text" id="r0c4"/></td>
            <td><input type="text" id="r0c5"/></td>
         </tr>
         <tr>
            <td><input type="text" id="r1c1"/></td>
            <td><input type="text" id="r1c2"/></td>
            <td><input type="text" id="r1c3"/></td>
            <td><input type="text" id="r1c4"/></td>
            <td><input type="text" id="r1c5"/></td>
        </tr>
        <tr>
            <td><input type="text" id="r2c1"/></td>
            <td><input type="text" id="r2c2"/></td>
            <td><input type="text" id="r2c3"/></td>
            <td><input type="text" id="r2c4"/></td>
            <td><input type="text" id="r2c5"/></td>
        </tr>
        <tr>
            <td><input type="text" id="r3c1"/></td>
            <td><input type="text" id="r3c2"/></td>
            <td><input type="text" id="r3c3"/></td>
            <td><input type="text" id="r3c4"/></td>
            <td><input type="text" id="r3c5"/></td>
        </tr>
        <tr>
            <td><input type="text" id="r4c1"/></td>
            <td><input type="text" id="r4c2"/></td>
            <td><input type="text" id="r4c3"/></td>
            <td><input type="text" id="r4c4"/></td>
            <td><input type="text" id="r4c5"/></td>
        </tr>
      </table>

      <button onclick="pasteFromClipboard();">Paste</button>
      <input type="reset" value="Clear">
   </form>

</body>
</html>

2 Comments

Follow

Get every new post delivered to your Inbox.