oracle_xml.java

使用xml操作oracle数据库的例子

import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.lang.*;
import java.sql.*;
import oracle.xml.sql.dml.OracleXMLSave;

/****************************************
 *1、创建数据表：
	CREATE TABLE FXTRADE( 
		CURRENCY1      VARCHAR2 (3),
		CURRENCY2      VARCHAR2 (3),
		AMOUNT         VARCHAR2(10),
		SETTLEMENT     VARCHAR2(8),
		ACCOUNT        VARCHAR2(10) 
	)
 *2、参数：
 *<?xml version="1.0"?>
 <ROWSET>
     <ROW> 
        <CURRENCY1>GBP</CURRENCY1>
        <CURRENCY2>JPY</CURRENCY2>
        <AMOUNT>11.2</AMOUNT>
        <SETTLEMENT>1111</SETTLEMENT>
        <ACCOUNT>123456</ACCOUNT>
    </ROW>
 </ROWSET>
 
 ***************************************/

public class Oracle_Xml{
	public static void main(String[] args)
   {
		String xml = "<?xml version=\"1.0\" ?>";
		
		xml+="<ROWSET>";
		xml+="<ROW num=\"1\">";
		xml+="<CURRENCY1>GBP</CURRENCY1>";
		xml+="<CURRENCY2>JPY</CURRENCY2>";
		xml+="<AMOUNT>11.2</AMOUNT>";
		xml+="<SETTLEMENT>1111</SETTLEMENT>";
		xml+="<ACCOUNT>123456</ACCOUNT>";
		xml+="</ROW>";
		xml+="</ROWSET>";
		
		System.out.println("--------------------");
		System.out.println(xml);
		System.out.println("--------------------");
		Oracle_Xml o_xml = new Oracle_Xml();
		/////////insert
		o_xml.insertXml(xml);
		/////////select
		String ret=o_xml.getXml();
		System.out.println(ret);
		/////////update
		o_xml.updateXml(xml);
		/////////delete
		o_xml.deleteXml(xml);
   }
   /***********************************
    **3、返回：
	*<?xml version="1.0"?>
	 <ROWSET>
	     <ROW> 
	        <CURRENCY1>GBP</CURRENCY1>
	        <CURRENCY2>JPY</CURRENCY2>
	        <AMOUNT>10000</AMOUNT>
	        <SETTLEMENT>20010325</SETTLEMENT>
	        <ACCOUNT>123456</ACCOUNT>
	    </ROW>
	 </ROWSET>
    *
    ***********************************/
   
	private String getXml(){
		String str="";
		Connection conn=null;
		try {
			// Create the connection
			conn  = getConnection("erp","erp");
			conn.setAutoCommit(false);
			// Create the query class
			OracleXMLQuery qry = new OracleXMLQuery(conn,"SELECT  * FROM FXTRADE");
			//OracleXMLQuery qry = new OracleXMLQuery(conn,"SELECT  * FROM SP_QC_CON_USER where USER_ID='QL01'");
			// Get the XML string
			str = qry.getXMLString();
			// Always close the query to get rid of any resources..
			qry.close();
			conn.commit();
		} catch(Exception e) {
			try{
				conn.rollback();
			}catch(Exception ee){
			}
			e.printStackTrace();
		}finally{
			try{
				conn.close();
			}catch(Exception ee){
			}
		}
		return str;
	}
	private static Connection getConnection(String username,
        String password)
        throws Exception
   {
   		Connection con = null;
		Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
		String url="jdbc:oracle:thin:@127.0.0.1:1521:oracle";
		con= DriverManager.getConnection(url,username,password);
		
		return con;
   }
	private void insertXml(String xml){
		Connection conn=null;
		try {
			conn = getConnection("erp","erp");
			conn.setAutoCommit(false);
			OracleXMLSave sav = new OracleXMLSave(conn, "FXTRADE");
			// Assume that the user passes in this document as xml
			sav.insertXML(xml);
			sav.close();
			conn.commit();
		} catch(Exception e) {
			try{
				conn.rollback();
			}catch(Exception ee){
			}
			e.printStackTrace();
		}finally{
			try{
				conn.close();
			}catch(Exception ee){
			}
		}
   	}
   	private void updateXml(String xml){
		Connection conn=null;
		try {
			conn = getConnection("erp","erp");
			conn.setAutoCommit(false);
			OracleXMLSave sav = new OracleXMLSave(conn, "FXTRADE");
			String[] tempArr = new String[1]; 
			tempArr[0] = "CURRENCY1"; 
			// set the empno as the key for updates.. 
			sav.setKeyColumnList(tempArr); 
			sav.updateXML(xml);
			
			sav.close();
			conn.commit();
		} catch(Exception e) {
			try{
				conn.rollback();
			}catch(Exception ee){
			}
			e.printStackTrace();
		}finally{
			try{
				conn.close();
			}catch(Exception ee){
			}
		}
   	}
   	private void deleteXml(String xml){
		Connection conn=null;
		try {
			conn = getConnection("erp","erp");
			conn.setAutoCommit(false);
			OracleXMLSave sav = new OracleXMLSave(conn, "FXTRADE");
			// Assume that the user passes in this document as xml
			sav.deleteXML(xml);
			sav.close();
			conn.commit();
		} catch(Exception e) {
			try{
				conn.rollback();
			}catch(Exception ee){
			}
			e.printStackTrace();
		}finally{
			try{
				conn.close();
			}catch(Exception ee){
			}
		}
   	}
}