Chinaunix首页 | 论坛 | 博客
  • 博客访问: 103136221
  • 博文数量: 19283
  • 博客积分: 9968
  • 博客等级: 上将
  • 技术积分: 196062
  • 用 户 组: 普通用户
  • 注册时间: 2007-02-07 14:28
文章分类

全部博文(19283)

文章存档

2011年(1)

2009年(125)

2008年(19094)

2007年(63)

分类:

2008-04-24 09:52:43

作者: 宗一 出处:  

其他查询操作
SQL 程序员常常编写根据某种条件限制从 DBMS 返回的行的查询。例如,SQL 查询限制从 "CUSTOMER" 表中检索的行,使之只包括那些具有某种状态的客户。在这个例子中,客户的状态可在 SQL VARCHAR 列中捕捉。但是,如果您想根据某种应用于 XML 列中数据的条件对搜索进行限制,那么应该怎么做呢?SQL/XML 的 XMLExists 函数为完成该任务提供了一种手段。 

通过 XMLExists 可以在 XML 文档中找到一个元素,并测试它是否满足某个特定的条件。如果用在 WHERE 子句中,则 XMLExists 可以限制返回的结果,使之只包括那些包含具有特定 XML 元素值的 XML 文档的行(换句话说,指定的值等于 "true")。 

例如您想找到居住在具有特定邮政编码的地区的所有客户的姓名。您也许还记得," CUSTOMER " 表的一个 XML 列中存储了客户的地址(包括邮政编码)。通过使用 XMLExists,可以从 XML 列中搜索目标邮政编码,并相应地限制返回的结果集。下面的 SQL/XML 查询返回居住在邮政编码为 95116 的地区的客户的姓名: 

根据 XML 元素值限制结果

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


如果我们想将 XML 值投影到返回的结果集。换句话说,我们要从 XML 文档中检索一个或多个元素值。有很多方法可以做这件事。首先我们使用 XMLQuery 函数来检索一个元素的值,然后使用 XMLTable 函数来检索多个元素的值,然后将这些映射到一个 SQL 结果集的列。 

比如如何创建一个列出 具有某种状态的客户的 email 地址的报告。我们可以通过XMLQuery 函数来完成这项任务:
检索符合条件的客户的 email 信息

select xmlquery('$c/Client/email'
passing info as "c")
from CUSTOMER
where status = 'good'


更新操作

DB2 允许用 SQL UPDATE 语句或通过使用系统提供的存储过程(DB2XMLFUNCTIONS.XMLUPDATE)来更新 XML 列。不管使用哪种方式,对 XML 列的更新都发生在元素级。然而,使用存储过程更新 XML 数据的程序员不需要提供整个 XML 文档给 DB2;他们只需指定要更新的 XML 元素。发出 UPDATE 语句的程序员则需要指定整个文档(而不仅仅是要更改的元素)。

例如,如果要发出一条 UPDATE 语句来更改某个特定客户的联系方式信息中的 email 地址,就必须在 XML 列中提供全部联系方式信息,而不仅仅是新的 email 元素值。

update clients set contactinfo=(
xmlparse(document 'newemail@someplace.com' ) )
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.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("");
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");

阅读(483) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~