XML 和数据库工具
What This Exercise is About
关于这个练习
XML is found in more and more applications today. With its non-proprietary format, XML can
be used to represent and exchange information in an application. The information for most
applications is stored in some fashion. A relational database is the most common location for
persisting information. With databases storing information and XML used for representing
information, a relationship exists between the two technologies. WebSphere Studio Application
Developer has tooling that supports this relationship as well as individual relational database
design tools and XML authoring tools. There are tools for working with existing databases
schemas or creating new database schemas. There are also editors for specific types of XML
documents. In this exercise you will use some of the XML tools and some of the features in the
Data Perspective in WebSphere Studio Application Developer.
现在，XML出现在越来越多的应用当中。因为有公共的格式，XML可以在应用程序中描述和传输数据。在大多数应用中数据存储成某种格式，关系数据库是最常见的一个。数据库存储，XML描述，两者之间形成了联系。WebSphere Studio Application Developer有支持这个联系的工具比如数据库设计工具，XML编辑工具。也有创建和编辑数据库结构的工具，还有编辑特定XML的工具。在这个练习中，您会使用一些XML工具和WebSphere Studio Application Developer中的数据视图。
User Requirement
用户必要条件
User must have DB2 (version 7.2) and WebSphere Studio Application Developer installed. The
Invest database must be setup (c:\yourbank\lab5\dbsetup.bat) and the source files used in the
lab must be available at c:\yourbank\lab5.
DB2 7.2,WSAD, Invest 数据库，c:\yourbank\lab5有要用到的代码文件。
What You Should Be Able to Do
您即将能做的
After completing this lab you should understand how to connect to an active database and
import the schema into WebSphere Studio Application Developer. You should be able to add
foreign keys to a table schema. By the end of this lab you will also be able to create SQL
statements from multiple tables using the SQL statment wizard. Finally, you should be able to
use the XML to SQL wizard to query the active database and return the information in an XML
document. With the XML results, you will use the XML to XML mapping wizard to translate the
results to another XML format.
课程之后，您能够连接到一个数据库，导入结构到WSAD，加入主键到表。使用SQL指南为多个表创建SQL命令。使用XML to SQL指南查询数据库，返回XML文件格式的数据。使用XMLtoXML映射指南转到另外的格式。
Introduction
介绍
The Invest database contains three tables for storing information on stocks owned by【？？？】 a
customer and customer contact information. To extract this information from the database and
possibly integrate it into your application, you will need to connect to the database and import
the schema into WebSphere Studio Application Developer. With the schema in the
Workbench, you will link the ADDRESS table to the PERSONALINFORMATION table. Next
you will create a SQL SELECT statement that will select all of the customer address
information. Then in the Data Perspective, you will execute the SQL statement against the
database and obtain customer information in XML format for two customers. Finally you will
use the XML to XML mapping wizard to map customer information to a statement DTD.
Invest数据库有三个表，存储着一个客户的联系人信息。为了在您的应用程序中使用这些数据，请链接数据库，导入表结构到WSAD。使用工作台里的结构，您可以链接ADDRESS表到PERSONALINFORMATION表。然后创建SQL SELECT命令，查询出所有的客户地址。在数据视图，运行SQL命令得到XML格式的两个客户的信息。最后，使用XMLtoXML映射指南，把客户信息映射到DTD（Document Type Definition，文档类型定义）。
** NOTE ** Solution instructions are at the end of Part Five
** 注 意 **解决方案说明书在第五部分最后
Exercise Instructions
实验说明
Part One: Setup Data Perspective
的一部分：配置数据视图
__1. Set up the Invest database, if it has not been done already.
__ Run C:\YourBank\Lab5\DBSetup.bat from a DB2 Command window
Start->Programs->IBM DB2->Command Window
Type C:\YourBank\Lab5\DBSetup.bat.
Enter the password PASSWORD when prompted.
__1. 如果没有Invest,则配置Invest 数据库.
__ 在DB2命令行窗口运行C:\YourBank\Lab5\DBSetup.bat。
Start->Programs->IBM DB2->Command Window
键入 C:\YourBank\Lab5\DBSetup.bat.
输入密码。（PASSWORD）
__2. In Application Developer, Open the Data Perspective.
__ Click Perspective --> Open --> Other... and click Data.
__ Click OK.
__2. 在WSAD中，打开数据视图。
__ 单击 Perspective --> Open --> Other... and click Data.
__ 单击OK.
__3. Connect to the Invest database.
__ In the DB Explorer view, right click and click New Connection....
__ In the Connection field, change the value to Invest DB Connection.
__ In the Database field, change the value to Invest.
__ In the User ID and Password fields, enter USERID and PASSWORD.
__3. 连接到 Invest 数据库。
__ 在数据库Explorer View，单击 New Connection....
__ Connection Name是Invest DB Connection.
__ Database是Invest.
__ User ID 和 Password 分别是USERID 和 PASSWORD.
__ Leave the other default values unchanged and click Finish.
__ 其他值保持默认，不作改变，单击 Finish.
__4. Import data into a Project
__ Click File --> New --> Project....
__ Click Simple and Project before clicking Next.
__4. 向项目导入数据。
__ 单击 File --> New --> Project....
__ 单击 Simple 和 Project 然后是 Next。
__ For the Project name enter InvestDatabase.
__ Project Name 是InvestDatabase。
__ Click Finish. The new project will be created in a Resource perspective.
__ Close the Resource perspective by right clicking on the Resource perspective icon
and clicking Close.
__ 单击Finish。在资源视图，项目已经创建。
__关闭资源视图。
__ If the Data perspective is not displayed, switch to it and insure the DB Explorer view is displayed.
__ Expand the Invest DB Connection and right click on Invest(jdbc:db2:Invest) and click Import to Folder...
__切换到数据视图。
__展开Invest DB Connection，右击Invest(jdbc:db2:Invest) 然后单击 Import to Folder...。
__Click Browse. Select the InvestDatabase project and click OK. Then click Finish.
The schema information for the invest database will be imported into the InvestDatabase project.
__单击Browse（浏览），选择InvestDatabase,点OK。然后Finish。
Invest结构信息导入到了InvestDatabase项目。
Part Two: Create Foreign Key
第二部分：创建外键。
__1. In the Data perspective, switch to the Navigator view.
__1.在数据视图，切换到导航器视图（Navigator view）。
__2. Expand InvestDatabase and double click Invest DB Connection_Invest_USERID_ADDRESS.tblxmi.
__2. 展开 InvestDatabase ，双击 Invest DB Connection_Invest_USERID_ADDRESS.tblxmi 。
__3. Change to the Foreign Keys view by clicking on the tab.
__3.切换到Foreign Keys view（外键）
__4. Click Add another.
__4. 点 Add another.（添加另一个）
__5. For the Target Table field, click Browse... to select the table holding the key.
__ Expand InvestDatabase\Invest (DB2 UDB, V7.2)\USERID\Tables
__ Click USERID.PERSONALINFORMATION.
__ Click OK.
__5.在目标表栏位，点Browse（浏览）。选择外键表，
__ 展开InvestDatabase\Invest (DB2 UDB, V7.2)\USERID\Tables
__ 点USERID.PERSONALINFORMATION.
__ 点OK。
__6. Move the MEMBERID column over to the right pane with the >> button. This creates a
relationship between the MEMBERID column in the local table is linked to the Primary
Key field in the PersonalInformation table.
__6.单击>>,把MEMBERID列移动到右边。这样，建立了一个在MEMBERID和PersonalInformation的主键之间的联系。
__7. Save and Close the file.
__7.保存，然后关闭。
Part Three: Generating a SQL Script
第三部分：生成SQL脚本。
__1. Click File --> New --> Other... and select Data and SQL Statement.
__1.单击File --> New --> Other... 选择 Data 和 SQL Statement. （数据和SQL语句）。
__2. Click Next.
__2.单击 NEXT。
__3. You will be guided through the creation of a SELECT SQL statement. The SQL
statement must use an existing database schema for reference. You can specify a
schema already imported into the Workbench or import one at this time. You will use
the Legacy database schema information imported during Part One.
__ To the right of the Database Model field, click Browse....
__ Expand InvestDatabase and select Invest (DB2 UDB, V7.2).
__ Click OK.
__3.您会一步步创建一个SELECT语句。SQL语句必须使用一个已经存在的表结构。您可以指定一个已存在的库结构，或导入一个。在这里，您将使用第一部分导入的数据库。
__在数据库模型（Database Model）右边，单击Browse...。
__展开InvestDatabase，选择Invest (DB2 UDB, V7.2).
__点OK。
__4. In the SQL Statement Name field, enter customerinfo.
__4. 语句名（SQL Statement Name）：customerinfo。
__5. Click Next.
__5.点Next。
__6. The Construct a SQL Statement window will be displayed with the Tables tab in view.
You will need to select which tables will the select statement reference.
__ Expand the DB2ADMIN schema and the Tables folder.
__ Select USERID.ADDRESS and move it over to the right list by clicking the > button.
__ Select USERID.PERSONALINFORMATION and move it over to the right list by
clicking the > button. The PersonalInformation table and the Address table will be used
to create the SQL statement.
__6. 构造SQL语句窗口出来了。选择SELECT语句用到的表。
__展开DB2ADMIN结构和表文件夹。
__选择USERID.ADDRESS点>加入右边列表。
__ 选择USERID.PERSONALINFORMATION点>加入右边列表。PersonalInformation 和Address表，会用来生成SQL语句。
__7. Click the Columns tab to specify which columns in the table should be returned when selected.
__ Expand USERID.PERSONALINFORMATION and move over the columns FULLNAME, SSN, and MEMBERID with the > button.
__ Expand USERID.ADDRESS and move to the Selected Columns the columns
ADDRESS, CITY, STATE, and POSTALCODE, listed under the table using the > button. Do not move over the column MEMBERID.
__7.选择”列”（ Columns）页面，指定表中要被选择的列。
__展开USERID.PERSONALINFORMATION，选FULLNAME, SSN, 和 MEMBERID 。
__展开USERID. ADDRESS，选ADDRESS, CITY, STATE, 和POSTALCODE 。不要选MEMBERID
__8. Click the Joins tab to specify how the relationship between the tables for the SELECT
statement.
__ Click and drag the mouse from MEMBERID in the Address table to MEMBERID in
the PersonalInformation table. Release the button when you have reached MEBERID in
the PersonalInformation table. Notice how the join between the two tables is created as
you drag the mouse.
__8.选”连接”（Joins）页面。指定SELECT中的连接关系。
__从Address的MEMBERID拖动鼠标，到PersonalInformation的MEMBERID放开。注意连接关系的建立是看您怎样拖动鼠标。
__ Click Next.
__单击Next。
__9. The Work with SQL statement screen displays the source SQL statement. You could
make any additional necessary changes to the SQL statement at this point.
__9.现在画面上显示了SQL语句。您可以在这里加上必要的修改。
__10. Click Finish.
__10.点Finish。
The file invest_customerinfo.sqx will be created in the InvestDatabase project. You
can open this file up and change the statement using the interface or by directly editting
the SQL statement.
invest_customerinfo.sqx文件建立并显示在InvestDatabase项目内。您可以打开并且直接修改SQL语句。
Part Four: Executing SQL Script
第四部分：执行SQL脚本
__1. With the script just created, you can run the SQL and have the results displayed in XML
format. Open the file invest_custormerinfo.sqx.
__1.使用上面创建的SQL，你可以运行并且以XML格式显示结果。打开文件invest_custormerinfo.sqx。
__2. In the Outline view, right click on customerinfo and select Generate new XML....
__2.在“轮廓”（OutLine）内，右击customerinfo，选Generate new XML...。
__3. Select the DTD radio button, leave the rest of the fields as the defaults and select
Finish. The files customerinfo.dtd, customerinfo.html, customerinfo.xml,
customerinfo.xsl, and customerinfo.xst are created in the InvestDatabase project.
__3.选中 DTD选项，其他默认即可。然后单击Finish。可以在InvestDatabase项目中看到创建了文件customerinfo.dtd, customerinfo.html, customerinfo.xml,customerinfo.xsl, 和customerinfo.xst。
Open up customerinfo.html and notice the information displayed in the table. This
information is from the database based on your query, displayed in HTML format. Close
the file when finished viewing.
打开customerinfo.html，注意表中的信息。这些信息来自您的Query，是HTML格式。看完了请关闭它。
The customer.xml file contains the information from the database based on your query,
except the information is displayed in XML format. The definition files for the XML are
also available. With this combination, you can begin using information from your
database in your application as it can be easily used in the XML format.
customer.xml文件包含了数据库中您的Query信息，但是显示成XML格式，还有XML的定义文件。这两者相结合，您就可以在应用中使用数据库来的数据，因为很容易变成XML格式。
Part Five: XML to XML Mapping
第五部分：XML to XML映射
Investment statements are sent out every month. The customer information and investment
summary are printed on reports mailed to the customer. The layout for the statement requires
the customer information to be in a specific XML format. You will map the customer information
optained by your customerinfo SQL SELECT statement from Part Two to the statement XML
format using the XML to XML Mapping wizard.
投资声明每个月都会发出。客户信息和投资总结打印成报表发到客户。声明需要特定XML格式。您要把SQL SELECT产生的XML中含有的客户信息从第二部分的XML映射过来。【？？？】
__1. Before creating the mapping, create a project to hold the resulting XML files
__ Click File --> New --> Project. Click Simple and select Project. Click Next.
__ In the Project name field, enter, StatementXML
__ Click Finish.
__1.在建立映射之前，建个项目存放XML文件。
__ 点 File --> New --> Project. 选 Simple 选 Project. 然后 Next.
__ Project name 是StatementXML。
__2. Import in the statement.dtd file which specifies the definition for the customer
information
__ Click File --> Import and select File System and click Next.
__ Click Browse... and navigate to c:\yourbank\lab5\statement.dtd.
Double click on the box infront of folder lab5 to select it. Uncheck the DBSetup.bat
Click OK.
__2.导入statement.dtd文件，它定义了用户信息。
__点文件à导入，选文件系统，然后Next。
__ In the Folder field, click Browse... and select StatementXML.
__ Click Finish.
__ 选中StatementXML
__ 点完成。
__3. With the Statement definition file in place, you are ready to begin mapping the customer
information obtained with the customerinfo SQL SELECT statment to the Statement.
Start the wizard by clicking File --> New --> Other... and click XML and then click XML
to XML Mapping. Click Next.
__3.有了定义文件，您就可以映射客户数据数据了。
文件à新建à其他 …选XML，XML至XML映射。然后NEXT。
__4. In the New XML to XML Mapping Session window, click StatementXML and click Next.
__4.在新建XML至XML映射窗口，选StatementXML然后点Next。
__5. In the Source XML or DTD Files window, expand InvestDatabase and click
customerinfo.xml and move it to the Select Files view by clicking the > button. You
could have selected other documents allowing you to map multiple XML documents to
one definition. Click Next.
__5.在源XML，DTD或XSD文件窗口，展开InvestDatabase，把customerinfo.xml加入到右边。您也可以选择多个文档来形成多XML文档对一个源。点Next。
__6. In the Target XML or DTD File window, expand StatementXML and click statement.dtd.
You could have also select customerinfo.dtd. Click Next.
__6.在目标XML，DTD或XSD文件窗口，展开StatementXML选statement.dtd，您也可以选customerinfo.dtd，点Next。
__7. Click Finish.
__7.点Finish.
__8. The mapping file, xmlmap.xmx, will be displayed in the Editor view. Expand
customerinfo\SQLResult\ADDRESS_PERSONALINFORMATION to view the
elements in the source.
__8.映射文件xmlmap.xmx在编辑器里显示出来了。展开customerinfo\SQLResult\ADDRESS_PERSONALINFORMATION，查看源中的元素。
__9. In the Target, expand statement\Statement\Address. Also expand AccountInfo and Contact.
__9.在目标中，展开statement\Statement\Address，还有AccountInfo 和 Contact。
__10. Map the following elements from Source to the Target by dragging each element from
the Source to the Target. Notice the Outline view is updated with each mapping.
__10.从源拖动元素到目标来完成映射。注意视图会随之变化。
__ Select the ADDRESS element under Source and map it to AddressLine under
Target.
__ Select the CITY element under Source and map it to City under Target.
__ Select the STATE element under Source and map it to State under Target.
__ Select the POSTALCODE element under Source and map it to Zip under Target.
__ Select the FULLNAME element under Source and map it to LastName under Target.
__ Select the FULLNAME element again under Source and map it to FirstName under
Target.
__ Select the SSN element again under Source and map it to TaxID under Target.
__ Select the MEMBERID element again under Source and map it to AccountNumber
under Target.
The Output view should look like the diagram below.
现在视图看起来应该是这样。
__11. The LastName element is currently mapped to an attribute in the form of lastname,
firstname (e.g. Doe, John). To extract just the lastname from this attribute we need to
define a special mapping.
__11.LastName和FistName映射了同一个元素，为了得到LastName，要作特别的映射。
__ Under Target, right-click on LastName and select Define XSLT Function...
__ In the Define a Function for this Mapping window, leave String selected and Click
Next.
__ In the Function name field select substring-after.
__在目标内，右击LastName，选择“定义XLT函数”（Define XSLT Function...）。
__在定义函数窗口，选中String，点Next。
__函数名选substring-after。
__ Select the Add button to bring up the Value Dialog window. In the Enter a value for
the parameter field, type ‘ ‘ (1 apostrophe, 1 space, 1 apostrophe). Click OK.
__点Add按钮，弹出值对话框，输入’ ’（单引号，中间空格），点OK。
__ Select Finish.
__点Finish.
__12. Just as with the LastName element, the FirstName element is currently mapped to an
attribute in the form of lastname, firstname. To extract just the firstname from this
attribute we need to define a special mapping.
__ Under Target, right-click on FirstName and select Define XSL Function...
__ In the Define a Function for this Mapping window, leave String selected and select
Next.
__ In the Function name field select substring-before.
__ Click the Add button to bring up the Value Dialog window. In the Enter a value for
the parameter field, type ‘ ‘ (1 apostrophe, 1 space, 1 apostrophe). Click OK.
__ Click Finish.
__ Save the mapping file.
__12.FirstName与此类似，只是substring-after改成substring-before.
__13. Select the Generate XSLT script for mapping button from the Tool Bar (last button on
the right).
__13.选择（为映射生成XSLT脚本）Generate XSLT script for mapping按钮。
__14. Select StatementXML and leave the File name as statement.xsl.
Click Finish. The statement.xsl file should be displayed and added to the
XMLStatement project.
__14.选择StatementXML，FileName不变，就是statement.xsl。
__15. In the Navigator view, select customerinfo.xml in the InvestDatabase project and in the
XMLStatement project, using the Control key, select statement.xsl. Right-click on the
files and select Apply XSL -> As XML
__15.在导航器里，用Control键选择InvestDatabase项目的customerinfo.xml和 XMLStatement项目内的statement.xsl。右击，选 应用XSLà作为XML。
__16. The XML document customerinfo.xml has been converted to the Result XML output
by the XSL file statement.xsl. Notice how the values in the customerinfo.xml file have
been transferred to the elements in the Result XML. The value in the FULLNAME
element in the customerinfo.xml has been separated into the LastName element and
the FirstName element in Result XML.
__16.通过使用XSL 文件statement.xsl，XML文件customerinfo转化成了Result XML 输出。请注意customerinfo.xml里的值是怎样到Result XML里的。FULLNAME的值已经分解成LastName和FirstName。
__17. At this point you can also trace through the conversion done by the XSL file. Make sure
the XSL Trace Editor view is selected and the XSL Trace menu is visible.
__17.在这里，也可以通XSL文件跟踪这个转化。确认选择了XSL Trace Editor（XSL跟踪编辑器），XSL Trace菜单也可用。
__18. From the XSL Trace menu, select Trace forward to begin tracing through the
conversion by statement.xsl. You can also select the Trace forward button on the tool
bar to progress through the trace (third button from the right end of the tool bar).
__18.在XSL Trace菜单，选择“向前跟踪”开始跟踪转化。也可在工具条上选择跟踪。
__19. Select the Trace forward button a number of times and then the Trace backward button
from the tool bar (second button from the right end of the tool bar). Watch as the lines
corresponding to the mapping in the XML files are hi-lighted as well as the line in the
XSL file performing the translation.
__19.单击“向前跟踪”数次，然后单击是“向后跟踪”。查看高亮的行。
__20. Select customerinfo.xml and statement.xsl again, right click, and click Apply XSL -->
As HTML. The XSL Trace Editor will open again and display the Result as HTML.
__20.再次选择customerinfo.xml 和 statement.xsl，然后是应用XSLà作为HTML。XSL Trace Editor（XSL跟踪编辑器）会显示HTML格式的结果。
What you did in this exercise
In this exercise, you used the Data Perspective to establish a connection to an active database.
You then imported in the schema from the database into WebSphere Studio Application
Developer. Next you created a foreign key in one of the tables. Then you created a SQL
SELECT statement which returned information in XML from two tables in the active database.
Finally you used the XML to XML Mapping wizard to map the XML returned by the SQL
statement to the another XML definition.
Solution Files:
To run this lab without having run the previous labs:
1) Run the file C:\yourbank\solutions\lab1\CfgLab1.bat
(type in into a DOS window or double-click on it.)
This will open a DB2CMD window and execute cfgdb.bat
2) Import the Server Configurations
a) File->New->Project... Server, Server Project, click Next
b) Enter the project name TestServer, click Finish.
c) File->Import. Select Zip file, click Next
d) Browse to C:\YourBank\Solutions\Lab1\TestServers.zip
e) Enter "TestServer" as your Folder name
f) Click "Finish"
*NOTE* The Test Servers imported assume default directory structure. If you have used
different directory structure than the defaults, you will have to configure the servers
manually as found in Section 3 of Lab 1.
3) Import the EAR in Application Developer:
a) File->Import
b) Select EAR file, click NEXT
c) Browse to c:\YourBank\Solutions\Lab2\YourBankAfterLab2.ear
d) Enter the Enterprise Application Project name as YourBankEAR
e) click "Finish"
4) Right-click on YourBankWebModule in the Navigator view. Select Properties,
Server Preferences, YourBankServer, click Apply, then OK.
5) Right-click on YourBankEJBModule in the Navigator view. Select Properties,
Server Preferences, YourBankServer, click Apply, then OK.
6) Run the file c:\YourBank\Lab5\DBSetup.bat from a DB2 Command Window.
© Copyright IBM Corporation 2001. All rights reserved.
2001/12/20 16:46:02 IBM WebSphere Studio Application Developer Workshop - Lab Exercise APPDEVL5.lwp
Page 15 of 15
| |
