至顶网›软件频道 ›我与Db2 9新特性的零距离体验(1)

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

扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条

在这篇文章中，我们将会向大家展示如何操作DB2 9中的新的XML数据类型。我们将通过一个示例来展示如何操作DB2 9中的XML数据类型。

来源：IT专家网 2008年6月4日

本例中更新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.xerces.dom.DOMImplementationImpl;
import org.w3c.dom.*;
import org.apache.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 的地区的客户的行吗？如果还记得的话，很容易知道如何删除与那些客户相关的行。下面看看如何使用 XMLExists 来做这件事：

删除居住在特定地区的客户的数据

delete from clients 
where xmlexists('$c/Client/Address[zip="95116"]' 
passing CUSTOMER.INFO as "c");

VIP专区

VIP用户

普通用户

邮件订阅

如果您非常迫切的想了解IT领域最新产品与技术信息，那么订阅至顶网技术邮件将是您的最佳途径之一。

重磅专题

往期文章

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

业界热点: