I was working on storing xml file into Oracle clob. But if I need particular element from that xml, so first I need to retrieve xml, and than need to parse it and that need to look up to get element. But what if we query to Oracle to retrieve the element?
yes we can do it, using Oracle’s XMLType, but we can store upto only 4000 character long file. is it?
yes, but I have done some research, so we can store more than 4000 character long file. How?.
In order to do that, First define variable in .hbm file if you are using Hibernate.
This is what you can write in .hbm
<property name=”xmlclm” type=”java.sql.Clob” >
<column name=”XMLCLM” />
</property>
public class TestXmlServiceImpl extends HibernateDaoSupport{
public void insertDatainto(){
System.out.println(”Hi how are you”);
try {
TestXml test = new TestXml();
/*DocumentBuilderFactory documentBuilderFactory =
DocumentBuilderFactory.newInstance();
DocumentBuilder documentBuilder =
documentBuilderFactory.newDocumentBuilder();
Document document = documentBuilder.newDocument();
// document.setTextContent(”<abc>adfa</abc”);
Element rootElement = document.createElement(”EmbeddedFile”);
document.appendChild(rootElement);
System.out.println(”String :->”+document.toString());*/
//test.setCLOBFactActPdf(domToString(document));
String path = new ContextLoaderListener().getContextLoader()
.getCurrentWebApplicationContext().getServletContext()
.getRealPath(”/xmls/credit.xml”);
System.out.println(”path:->”+path);
org.hibernate.engine.SessionFactoryImplementor impl = (org.hibernate.engine.SessionFactoryImplementor )this.getSessionFactory();
Connection conn = (Connection)WSJdbcUtil.getNativeConnection((WSJdbcConnection)impl.getConnectionProvider().getConnection()); ;
System.out.println(”Connection:->”+conn);
insertXML(getContents(new File(path)),conn);
//test.setCLOBFactActPdf(getContents(new File(path)));
// this.getHibernateTemplate().saveOrUpdate(test);
} catch (DataAccessException dae) {
dae.printStackTrace();
} catch (Throwable thrown) {
thrown.printStackTrace();
}
}
public void getData(){
System.out.println(”Get Data:->”);
/*TestXml testxml = (TestXml)
this.getHibernateTemplate().findByCriteria(
DetachedCriteria.forClass(TestXml.class).add(Restrictions.eq(”testXmlId”,new Long(2261)))).iterator().next();*/
List<TestXml> xmlList=null;
xmlList = (List<TestXml>) this.getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) {
String sql =”select x.FACTACTPDF.GETCLOBVAL() as FACTACTPDF, x.TESTXMLID TESTXMLID from T_TestXML x”;
return session.createSQLQuery(sql).addEntity(TestXml.class).list();
}
});
if(xmlList.size()>0){
for(TestXml testXml : xmlList){
try {
System.out.println(”–>”+testXml.getCLOBFactActPdf());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public static String domToString(Document _document)
throws TransformerException
{
System.out.println(”—>_Document”);
TransformerFactory tFactory = TransformerFactory.newInstance();
Transformer transformer = tFactory.newTransformer();
transformer.setOutputProperty(OutputKeys.INDENT, “yes”);
DOMSource source = new DOMSource(_document);
StringWriter sw=new StringWriter();
StreamResult result = new StreamResult(sw);
transformer.transform(source, result);
System.out.println(”String xml–>”+sw.toString());
return sw.toString();
}
static public String getContents(File aFile) {
//…checks on aFile are elided
StringBuilder contents = new StringBuilder();
try {
//use buffering, reading one line at a time
//FileReader always assumes default encoding is OK!
BufferedReader input = new BufferedReader(new FileReader(aFile));
try {
String line = null; //not declared within while loop
/*
* readLine is a bit quirky :
* it returns the content of a line MINUS the newline.
* it returns null only for the END of the stream.
* it returns an empty String if two newlines appear in a row.
*/
while (( line = input.readLine()) != null){
contents.append(line);
contents.append(System.getProperty(”line.separator”));
}
}
finally {
input.close();
}
}
catch (IOException ex){
ex.printStackTrace();
}
System.out.println(”Lenght:->”+contents.toString().length());
return contents.toString();
}
private void insertXML(String xmlData, Connection conn) {
CLOB clob = null;
String query;
// Initialize statement Object
PreparedStatement pstmt = null;
try{
query = “INSERT INTO T_TestXML (TESTXMLID,FACTACTPDF) VALUES (1,XMLType(?)) “;
// Get the statement Object
pstmt = conn.prepareStatement(query);
// xmlData is the string that contains the XML Data.
// Get the CLOB object using the getCLOB method.
clob = getCLOB(xmlData, conn);
// Bind this CLOB with the prepared Statement
pstmt.setObject(1, clob);
// Execute the Prepared Statement
if (pstmt.executeUpdate () == 1) {
System.out.println (”Successfully inserted a Xmltype !”);
}
} catch(SQLException sqlexp){
sqlexp.printStackTrace();
} catch(Exception exp){
exp.printStackTrace();
}
}
private CLOB getCLOB(String xmlData, Connection conn) throws SQLException{
CLOB tempClob = null;
try{
// If the temporary CLOB has not yet been created, create new
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
// Open the temporary CLOB in readwrite mode to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Write the data into the temporary CLOB
tempClobWriter.write(xmlData);
// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();
// Close the temporary CLOB
tempClob.close();
} catch(SQLException sqlexp){
tempClob.freeTemporary();
sqlexp.printStackTrace();
} catch(Exception exp){
exp.printStackTrace();
tempClob.freeTemporary();
exp.printStackTrace();
}
return tempClob;
}
}
If you enjoyed this post, feel free to subscribes to our rss feeds
oping interactive Web 2.0 web applications that can do things that static HTML simply cannot.
