Skip to Main Content

New to Java

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to insert XML node value into Oracle database with Java

925267Mar 21 2012 — edited Mar 21 2012
I'm very new to Java so please bear with me here...

What I want to do is get the value of an XML node and update a table in an Oracle database with the XML node value. Also the column in the Oracle table that I want to update contains the NCLOB datatype.

I already have a Java class that can read an XML file and get the XML node value; and I also have a separate Java class that can connect to an Oracle db, load an entire XML file, and then update a column (with NCLOB datatype) in the Oracle table with the data from the XML file.

What I now want to do is this:

- connect to Oracle db
- read XML file and get node value
- update column (with NCLOB datatype) with XML node value

Below is some code that I've glued together from what I already have, but obviously it's not doing what it should. So this is where I need some help. What do I need to do to insert the XML node value into the Oracle table? I'm guessing that I don't need to use the BufferReader or PrintWriter classes here (used to load an entire XML file insert into the db), or do I? Any assistance would be greatly appreciated!

Code:
import java.io.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;

public class ImportDesc {
    public static void main(String[] args) throws Exception 
    {
        Class.forName ("oracle.jdbc.OracleDriver");

        //initialize connection variable to connect to database...
        Connection con = DriverManager.getConnection
         ("jdbc:oracle:thin:@//host:port/SID", "username", "password");
        System.out.println("Connected.\n");

        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
        String query="SELECT COLUMN1 FROM TABLE WHERE COLUMN2='Something' FOR UPDATE";
        con.setAutoCommit(false);
        ResultSet resultset=stmt.executeQuery(query);

        File file = new File("C:/Java/XML/Test.xml");
        DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
        DocumentBuilder db = dbf.newDocumentBuilder();
        Document doc = db.parse(file);
        new ImportDesc().GetText(doc);

        if(resultset.next()){
        oracle.sql.CLOB clobnew = ((OracleResultSet) resultset).getCLOB("COLUMN1");
        PrintWriter pw = new PrintWriter(clobnew.getCharacterOutputStream() );
        BufferedReader br = new BufferedReader( new FileReader( file ) );
        String  lineIn = null;
        while( ( lineIn = br.readLine() ) != null )
              pw.println( lineIn );
              pw.close();
              br.close();
        }

        con.setAutoCommit(true);
        con.commit();
    }
    public void GetText(Document doc) 
    {
       Element e = doc.getDocumentElement();
        NodeList nodeList = doc.getElementsByTagName("Interface");
        for (int i = 0; i < nodeList.getLength(); i++) 
        {
            Node node = nodeList.item(i);
            if (node.getNodeType() == Node.ELEMENT_NODE) 
            {
                Element element = (Element) node;
                NodeList nodelist = element.getElementsByTagName("Description");
                Element element1 = (Element) nodelist.item(0);
                NodeList ndLst = element1.getChildNodes();
                System.out.print((ndLst.item(0)).getNodeValue());
            }
        }
    }
}
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 18 2012
Added on Mar 21 2012
15 comments
1,028 views