How to read XMLType column from database using JDBC?

Last updated  

0 Shares

After reading my post on “How to insert XMLType Column value ?”, many of my blog readers asked me how to read the XML type column value using JDBC. Below java code show you how to read XMLType column value from database using JDBC driver.


/**********************************************************************************
 * Created on Nov, 2010 Copyright(c) https://kodehelp.com All Rights Reserved. 
 **********************************************************************************
 */
package com.kodehelp.java.sample;

import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * @author https://kodehelp.com
 *
 */
public class ReadXMLType {

	/**
	 * This class contains the logic for reading the XML from XMLType column from 
	 * database using JDBC.
	 * @param args
	 */
	public static void main(String[] args) {
		String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
		String JDBC_STRING = "jdbc:odbc:thin:@DBNAME:PORTNUMBER:SID";
		String USER_NAME = "USER_NAME";
		String PASSWD = "PASSWORD";
		Connection conn = null;
		ResultSet rs = null;
		Statement stmt = null;
		Clob xmlClob = null;
		try{
		Class.forName(JDBC_DRIVER);
        conn = DriverManager.getConnection(JDBC_STRING, USER_NAME, PASSWD);
        stmt = conn.createStatement();
        
        
        String insertQuery = "SELECT TBL.XMLTYPECOLUMN.GETCLOBVAL() FROM TABLE TBL";
        rs = stmt.executeQuery(insertQuery);
        xmlClob = (Clob) rs.getClob(1);
        
        Reader xmlReader = xmlClob.getCharacterStream();
        char[] buffer = new char[(int)xmlClob.length()];
        StringBuffer sb = new StringBuffer();
        if(xmlClob.length()>0){
        	FileWriter fw = new FileWriter("/tmp/tempdata.xml");
        	int ch;
			while (( ch = xmlReader.read(buffer)) != -1){
        		sb.append(buffer);
        	}
			fw.append(sb.toString());
			fw.close();
        }
		}catch(SQLException sqlEx){
			sqlEx.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
				try {
					if(rs!=null) rs.close();
					if(stmt !=null) stmt.close();
					if(conn!=null) conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
	}

}