我与Db2 9新特性的零距离体验

核心提示： 更新操作 DB2 允许用 SQL UPDATE 语句或通过使用系统提供的存储过程（DB2xml(标准化越来越近了)FUNCTIONS.xml(标准化越来越近了)UPDATE）来更新 xml(标准化越来越近了) 列，不管使用哪种方式，我与Db2 9新特性的零距离体验(6)，对 xml(标准化越来越近了) 列的更

　  更新操作
　  DB2 允许用 SQL UPDATE 语句或通过使用系统提供的存储过程（DB2xml(标准化越来越近了)FUNCTIONS.xml(标准化越来越近了)UPDATE）来更新 xml(标准化越来越近了) 列。不管使用哪种方式，对 xml(标准化越来越近了) 列的更新都发生在元素级。然而，使用存储过程更新 xml(标准化越来越近了) 数据的程序员不需要提供整个 xml(标准化越来越近了) 文档给 DB2；他们只需指定要更新的 xml(标准化越来越近了) 元素。发出 UPDATE 语句的程序员则需要指定整个文档（而不仅仅是要更改的元素）。
例如，如果要发出一条 UPDATE 语句来更改某个特定客户的联系方式信息中的 email 地址，就必须在 xml(标准化越来越近了) 列中提供全部联系方式信息，而不仅仅是新的 email 元素值。

update clients set contactinfo=(xml(标准化越来越近了)parse(document '<email>newemail@someplace.com</email>' ) )where id = 3227 

　  本例中更新xml(标准化越来越近了)类型字段的类为updatexml(标准化越来越近了).java，该类通过提供cid来更新CUSTOMER表中的客户信息。

import java.sql.*;import java.io.*;import java.util.*;import org.xml(标准化越来越近了).sax.*;import javax.xml(标准化越来越近了).parsers.*;import org.apache(Unix平台最流行的WEB服务器平台).xerces.dom.DOMImplementationImpl;import org.w3c.dom.*;import org.apache(Unix平台最流行的WEB服务器平台).xml(标准化越来越近了).serialize.*;public class updatexml(标准化越来越近了)...{/**//** private members **/private static Connection conn;private static PreparedStatement sStmt;private static PreparedStatement uStmt;private static ResultSet rs;/**//*needed for properties file*/static Properties db2ConnProps = new Properties();static Properties fileinputProps = new Properties();/**//* Default Constructor */public updatexml(标准化越来越近了)() ...{}/**//* Main Driver*/public static void main(String[] args)...{String db,pID, userName, passwd, host, port, pInfo, input, newDoc, xpath, fileName;String inputfile = " ";boolean inputfilevalidate=false;String rollbackCommit="rollback";String interactive = "";boolean exit, update;boolean validate=false;exit=update=false;int option;BufferedReader br = new BufferedReader(new InputStreamReader(System.in));String [] productInfo = ...{"name", "details", "price", "weight" };String [] productValues = new String[4];newDoc=pInfo=host=port=db=pID=userName=passwd=input=null;/**//** print user options **/try...{/**//* load the contents of properties file in case of missing arguments*/db2ConnProps.load(new FileInputStream ("F:\\eclipse\\WorkPlace\\DB2xml(标准化越来越近了)\\src\\db2Conn.properties"));db=db2ConnProps.getProperty("databaseName");userName=db2ConnProps.getProperty("userName");passwd=db2ConnProps.getProperty("password");host=db2ConnProps.getProperty("hostName");port=db2ConnProps.getProperty("portNumber");rollbackCommit=db2ConnProps.getProperty("rollbackCommit");interactive=db2ConnProps.getProperty("interactive");/**//** establish DB connection **/conn=db2Conn.get(db,userName,passwd,host,port);try...{conn.setAutoCommit(false);}catch (java.sql.SQLException e)...{e.printStackTrace();System.exit(-1);}/**//* prints current connection status*/System.out.println();System.out.println(" This sample updates xml(标准化越来越近了) information from a table.");System.out.println();System.out.println(" Connect to '"+db+"' database using JDBC Universal type 4 driver");System.out.println(" Connection: com.ibm.db2.jcc");try...{ db2ConnProps.load(new FileInputStream ("F:\\eclipse\\WorkPlace\\DB2xml(标准化越来越近了)\\src\\db2Conn.properties"));}catch(IOException io) ...{System.out.println(io.getMessage());}catch(NullPointerException ne) ...{System.out.println("NullPointerException");}/**//** get product id if not interactive, else request user input**/if (interactive.equals("no"))...{pID = db2ConnProps.getProperty("pID");System.out.println();System.out.println(" Using default product ID of "+pID);}else...{pID=db2ConnProps.getProperty("pID");System.out.println();System.out.print(" Enter Product ID #[default is '1000']:");pID = br.readLine();}/**//*If user just presses enter, then use default value*/if(pID.length()==0)...{System.out.println(" Nothing entered, thus, using default value.");pID=db2ConnProps.getProperty("pID");}else;/**//** prompt for valid product ID **/while((pInfo=getProduct(pID))==null)...{System.out.println();System.out.println(" No Such Product ID can be found. Do you want to use default");System.out.print(" value of "+db2ConnProps.getProperty("pID")+" [y/n] or q to quit: ");input=br.readLine();/**//** if update, parse and update new product **/if(input.equals("y") || input.equals("Y"))...{pID=db2ConnProps.getProperty("pID");System.out.println();System.out.println(" Now using pid="+pID);}else if(input.equals("q") || input.equals("Q"))...{System.out.println(" Quitting program! ");System.exit(-1);}else...{System.out.println();System.out.print(" Please re-enter Product #: ");pID = br.readLine();}}//end while /**//** insert product info into a DOM and use DOM API to extract element values **/domUtility.initializeFromString(pInfo);/**//** Print the product description currently **/System.out.println();System.out.println(" Current contents of DESCRIPTION in the PRODUCTS table");System.out.println(" for pid='"+pID+"'");System.out.println();System.out.println(domUtility.toString("/product"));/**//** print product update options **/printProduct(productInfo);if (interactive.equals("no"))...{ System.out.println(" Just changing #[4] weight");input = "4";}else...{ input = ""; }while(!(input.equals("0") | input.equals("1") | input.equals("2") | input.equals("3") | input.equals("4")))...{System.out.println();System.out.print(" Enter # of the Item to change (or zero to quit): ");input =br.readLine();}option= Integer.parseInt(input);/**//** update product value or exit **/while(!(option == 0))...{System.out.println();switch (option)...{case 0: exit=true;break ;case 1: System.out.print(" Enter new Value for \"name\": ");input=br.readLine();domUtility.setValue("/product/description/name/text()",input);break;case 2: System.out.print(" Enter new Value for \"details\": ");input=br.readLine();domUtility.setValue("/product/description/details/text()",input);break;case 3: System.out.print(" Enter new Value for \"price\": ");input=br.readLine();domUtility.setValue("/product/description/price/text()",input);break;case 4:if (interactive.equals("no"))...{input="5";System.out.println();System.out.println(" Using default Product Weight of 5.");System.out.println();option = 0;}else...{ System.out.print(" Enter new Value for \"weight\": ");input=br.readLine();}domUtility.setValue("/product/description/weight/text()",input);break;default: System.out.print(" Invalid Entry, please try again");break;}//end switch /**//** select next item to update **/if (interactive.equals("no"))...{ input = "0"; }else...{ input = "";printProduct(productInfo);}while(!(input.equals("0") | input.equals("1") | input.equals("2") | input.equals("3") | input.equals("4")))...{System.out.print(" Enter # of the Item to change (or zero to quit): ");input =br.readLine();}option= Integer.parseInt(input);}//end while(!(option == 0)) System.out.println();System.out.println(" Updating the record in the Products table using the above data");System.out.println(" updateProd(domUtility.toString(\"/product\"),pID)");System.out.println();updateProd(domUtility.toString("/product"),pID);/**//** Print the product description currently--before rollback **/pInfo=getProduct(pID);domUtility.initializeFromString(pInfo);System.out.println(" After update, contents of DESCRIPTION in the PRODUCTS table");System.out.println(" for pid='"+pID+"'");System.out.println();System.out.println(domUtility.toString("/product"));//rollback and then print the contents if (rollbackCommit.equals("rollback"))...{System.out.println(" Rollback the transaction.");try...{ conn.rollback(); }catch (Exception e)...{ JdbcException jdbcExc = new JdbcException(e, conn);jdbcExc.handle(); }}else ;/**//** Print the product description currently--After rollback **/pInfo=getProduct(pID);domUtility.initializeFromString(pInfo);System.out.println();System.out.println(" After rollback, contents of DESCRIPTION in the PRODUCTS table");System.out.println(" for pid='"+pID+"'");System.out.println();System.out.println(domUtility.toString("/product"));/**//** close connections **/try ...{conn.commit();}catch(SQLException ex)...{System.err.println("SQLException information");while(ex!=null)...{System.err.println ("Error msg: " + ex.getMessage());System.err.println ("SQLSTATE: " + ex.getSQLState());System.err.println ("Error code: " + ex.getErrorCode());ex.printStackTrace();ex = ex.getNextException(); // For drivers that support chained exceptions }}System.out.println(" Disconnect from the '"+db+"' database");closeConn();System.out.println(" Disconnected.");}catch(NullPointerException ne)...{System.out.println("NullPointerException main");}catch(IOException io)...{io.printStackTrace();}}//main public static void setConn(Connection c)...{conn=c;}/**//* Get product from database */public static String getProduct(String pID)...{String pInfo=null;try...{/**//** Prepare Statement **/sStmt= conn.prepareStatement("Select INFO from CUSTOMER where cid=?");sStmt.setString(1,pID);rs=sStmt.executeQuery();if (rs.next())...{pInfo=rs.getString(1);}else;}catch(SQLException sqle)...{System.out.println("Error Msg: "+ sqle.getMessage());System.out.println("SQLState: "+sqle.getSQLState());System.out.println("SQLError: "+sqle.getErrorCode());System.out.println("Rollback the transaction and quit the program");System.out.println();try ...{ conn.rollback(); }catch (Exception e)...{JdbcException jdbcExc = new JdbcException(e, conn);jdbcExc.handle();}System.out.println();System.exit(1);}return pInfo;}//end getProduct /**//* Update Product */public static void updateProd(String newDoc, String pID)...{int recordUpdated=0;try...{/**//** Prepare Statement **/uStmt= conn.prepareStatement("Update CUSTOMER set INFO = ? where PID=?");uStmt.setString(1,newDoc);uStmt.setString(2,pID);if(uStmt.executeUpdate()!=1)System.out.println("Product could not be updated");else ;}catch(SQLException sqle)...{System.out.println("Error Msg: "+ sqle.getMessage());System.out.println("SQLState: "+sqle.getSQLState());System.out.println("SQLError: "+sqle.getErrorCode());System.out.println("Rollback the transaction and quit the program");System.out.println();try ...{ conn.rollback(); }catch (Exception e)...{JdbcException jdbcExc = new JdbcException(e, conn);jdbcExc.handle();}System.exit(1);}}//end updateProd /**//* Print Product Update Values */public static void printProduct(String [] pInfo)...{String pValue, xpath;pValue = xpath = null;System.out.println();System.out.println(" Product ID="+domUtility.getValue("/product/@pid")+"\n");for(int i=0; i < pInfo.length; i++)...{xpath="/Client/description/"+pInfo[i]+"/text()";pValue = domUtility.getValue(xpath);if(pValue != null)System.out.println(" ["+(i+1)+"] "+pInfo[i].toString()+" : "+ pValue);}}//end printProduct /**//* Close conections */public static void closeConn()...{try...{conn.close();sStmt.close();uStmt.close();}catch(SQLException sqle)...{System.out.println(sqle.getMessage());System.out.println(sqle.getSQLState());System.out.println(sqle.getErrorCode());}}//end closeConn }

　  删除 xml(标准化越来越近了) 数据
　  删除包含 xml(标准化越来越近了) 列的行很简单。SQL DELETE 语句允许通过 WHERE 子句识别（或限制）要删除的行。该子句可以包括简单的谓词来标识非 xml(标准化越来越近了) 列值或包括 SQL/xml(标准化越来越近了) 函数来标识包含在 xml(标准化越来越近了) 列中的 xml(标准化越来越近了) 元素值。

　  例如，下面展示了如何删除客户 CID 为 3227 的客户的所有信息：
delete from CUSTOMER
where cid = 1000

　  还记得怎样限制 SQL SELECT 语句，使之仅返回居住在邮政编码为 95116 的地区的客户的行吗？如果还记得的话，很容易知道如何删除与那些客户相关的行。下面看看如何使用 xml(标准化越来越近了)Exists 来做这件事：

　  删除居住在特定地区的客户的数据
delete from clients
where xml(标准化越来越近了)exists('$c/Client/Address[zip="95116"]'
passing CUSTOMER.INFO as "c");baidu: http://www.cncms.com.cn/db2/y372916.html