网络数据库实用技术实验报告

——XML通讯录

学院：交通运输管理学院 专业：信息管理与信息系统 指导老师： 完成时间：2014.06 小组成员：

一、实验名称

XML通讯录

二、实验目的

1、掌握XML文档的基本语法，能够得到格式良好的XML文档；

2、能够了解并掌握XML DTD的定义方法及其用途；

三、实验实现

程序有1.解析；2.从数据库反解析；3.按字符模糊查询；4.使用XQuery语句查询；5.添加；6.删除；7.修改；8.存入数据库 八大功能。

程序结构

AddressList.java实现程序页面和一些功能；

DatabaseToXMLWithDom.java实现数据库到xml文件的转换； XMLToDatabaseWithDom.java实现将xml文件更新到数据库中；

Data.xml外部xml文件，存放通讯信息。 数据库xmlcontact,只有一个表people People表结构：

data.xml文件结构： <people>

<name>小福</name> <sex>男</sex> <age>22</age> <address>邵东</address> <phone>137xxxxxxxx</phone> </people> <people> <name>小郝</name> <sex>男</sex> <age>21</age> <address>衡阳</address> <phone>137xxxxxxxx</phone> </people> <people> <name>小明</name> <sex>女</sex> <age>21</age> <address>浏阳</address> <phone>159xxxxxxxx</phone> </people> <people> <name>老王</name>

<sex>女</sex> <age>21</age> <address>河南</address> <phone>151xxxxxxxx</phone> </people> <people> <name>小罗</name> <sex>男</sex> <age>20</age> <address>衡阳</address> <phone>137xxxxxxxx</phone> </people> <people> <name>金中国</name> <sex>男</sex> <age>60</age>

<address>中国</address> <phone>654767</phone> </people>

</addresslist>

程序界面：

程序功能实现：

1.解析功能

在程序开启时或单击“显示xml文件所有信息”按钮触发解析功能。将data.xml文件中的信息解析到界面中的表格里。

2.从数据库反解析；

单击“从数据库中读取”按钮，通过引用

DatabaseToXMLWithDom.java实现从数据库的反解析；

3.按字符模糊查询；

先在姓名框里输入姓名，通过单击“字符匹配查找”按钮实现字符模糊查询；

如输入“小”，结果如图；

如输入“小罗”，结果如图；

4.使用XQuery语句查询；

使用XQuery语言查询，查询语句为"for $x in doc(\"data.xml\")/addresslist/people where $x/name='"+lookupName1+"' return $x";

输入“小福”，结果如下；

输入“小”，结果如下；

5.添加；

在相应的框中输入信息后，单击“添加”按钮，结果如下；

Data.xml文件中也添加了； <people>

<name>哈哈哈哈哈</name>

<sex>女</sex>

<age>23</age>

<address>庄圩乡</address> <phone>1234555666</phone>

</people></addresslist>

6.删除；

在表格中选中一条记录后，单击“删除”按钮，中相应的记录也被删除了；文件data.xml

7.修改；

在上面信息框中对应的信息修改，然后单击“修改”按钮，则datx.xml文件中相应的信息也发生改变；

将地址“中国”修改成“韩国”，结果如图

8.存入数据库。

单击“存库”，则此时的data.xml文件中的数据则会解析到数据库xmlcontact中的people表中；如图；

四．主要代码：

AddressList.java

package zy;

import javax.xml.xquery.XQConnection;

import javax.xml.xquery.XQDataSource;

import javax.xml.xquery.XQException;

import javax.xml.xquery.XQPreparedExpression;

import javax.xml.xquery.XQResultSequence;

import org.w3c.dom.Element;

import net.sf.saxon.xqj.SaxonXQDataSource;

import java.awt.event.*;

import javax.swing.*;

import java.awt.*;

import javax.xml.parsers.*;

import java.io.*;

import org.w3c.dom.*;

import javax.xml.transform.*;

import javax.xml.transform.dom.*;

import javax.xml.transform.stream.*;

import java.sql.SQLException;

import java.util.*;

public class AddressList extends JFrame implements ActionListener {

JButton addRecord = new JButton("添加");

JButton lookup = new JButton("字符匹配查找");

JButton delete = new JButton("刪除");

JButton showAllResult = new JButton("显示xml文件所有信息"); JButton change = new JButton("修改");

JButton CDB = new JButton("存库");

JButton DDB = new JButton("从数据库中读取");

JButton XQuery = new JButton("使用XQuery查询");

JTextField qq = new JTextField(8);

JTextField name = new JTextField(8);

Object[] sex = {"男", "女"};

JComboBox gender = new JComboBox(sex);

JTextField age = new JTextField(8);

JTextField address = new JTextField(15);

JTextField phone = new JTextField(10);

Object[] columnNames = {"姓名", "性别","QQ", "年龄", "地址", "联系电话"};

Object[][] studentInfo = new Object[40][6];

JTable table = new JTable(studentInfo, columnNames);

//内部类

//table.addMouseListener();

Document doc;

public AddressList()

{

setTitle("通讯录");

setSize(810, 600);

setVisible(true);

setResizable(false);

setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

qq.setBorder(BorderFactory.createTitledBorder("QQ")); name.setBorder(BorderFactory.createTitledBorder("姓名")); gender.setBorder(BorderFactory.createTitledBorder("性别")); age.setBorder(BorderFactory.createTitledBorder("年龄"));

address.setBorder(BorderFactory.createTitledBorder("地址")); phone.setBorder(BorderFactory.createTitledBorder("电话号码"));

Container con1 = getContentPane();

con1.setLayout(new FlowLayout());

con1.add(name);

con1.add(age);

con1.add(qq);

con1.add(address);

con1.add(phone);

con1.add(gender);

Container con2 = getContentPane();

con2.setLayout(new FlowLayout());

con2.add(XQuery);

con2.add(lookup);

con2.add(delete);

con2.add(showAllResult);

con2.add(addRecord);

con2.add(change);

con2.add(CDB);

con2.add(DDB);

Container con3 = getContentPane();

con3.add("Center", new JScrollPane(table));

validate();

addRecord.addActionListener(this);

lookup.addActionListener(this);

delete.addActionListener(this);

showAllResult.addActionListener(this);

change.addActionListener(this);

CDB.addActionListener(this);

DDB.addActionListener(this);

XQuery.addActionListener(this);

//为表格添加单击事件 匿名类实现

table.addMouseListener(new MouseAdapter(){public void

mouseClicked(MouseEvent e){//单击表格，文本框内对应显示表格内的信息；

//System.out.println(table.getSelectedRow());

name.setText((String)studentInfo[table.getSelectedRow()][0]);

if(((String)studentInfo[table.getSelectedRow()][1]).equals("男"))//判断是男还是女

gender.setSelectedIndex(0);

else

gender.setSelectedIndex(1);

qq.setText((String)studentInfo[table.getSelectedRow()][2]);

age.setText((String)studentInfo[table.getSelectedRow()][3]);

address.setText((String)studentInfo[table.getSelectedRow()][4]);

phone.setText((String)studentInfo[table.getSelectedRow()][5]); }});

init();//初始化表格

}

public static void main(String[] args)//主函数；

{

new AddressList();

}

File xmlFile = null;//连接data.xml文件；

public Document getDocument()throws Exception

{

xmlFile = new File("data.xml");

DocumentBuilderFactory factory =

DocumentBuilderFactory.newInstance();

DocumentBuilder builder = factory.newDocumentBuilder(); Document doc = builder.parse(xmlFile);

return doc;

}

public void init()//初始化表格

{

for(int i = 0; i < table.getRowCount(); i++)

{

studentInfo[i][0] = "";

studentInfo[i][1] = "";

studentInfo[i][2] = "";

studentInfo[i][3] = "";

studentInfo[i][4] = "";

studentInfo[i][5] = "";

}

try

{//在表格内显示通讯录信息；

doc = getDocument();

Element root = doc.getDocumentElement();

NodeList names = root.getElementsByTagName("name"); NodeList sexes = root.getElementsByTagName("sex"); NodeList qqs = root.getElementsByTagName("qq");

NodeList ages = root.getElementsByTagName("age");

NodeList addresses = root.getElementsByTagName("address"); NodeList phones = root.getElementsByTagName("phone");

for(int i = 0; i < names.getLength(); i++)

{

studentInfo[i][0] = names.item(i).getTextContent(); studentInfo[i][1] = sexes.item(i).getTextContent(); studentInfo[i][2] = qqs.item(i).getTextContent(); studentInfo[i][3] = ages.item(i).getTextContent();

studentInfo[i][4] = addresses.item(i).getTextContent(); studentInfo[i][5] = phones.item(i).getTextContent(); }

repaint();

}

catch(Exception e)

{

System.out.println("异常信息1:" + e);

}

}

public void actionPerformed(ActionEvent e)

{

if(addRecord == e.getSource()) //插入people信息进入xml文件中；

{

String n="",s="",q="",a="",ad="",p="";

try{n=name.getText();}catch(Exception e2){n="";}

try{s=(String)(gender.getSelectedItem());}catch(Exception e3){s="";}

try{q=qq.getText();}catch(Exception e4){q="";}

try{a=age.getText();}catch(Exception e5){a="";}

try{ad=address.getText();}catch(Exception e6){ad="";} try{p=phone.getText();}catch(Exception e7){p="";} try

{

Node root = doc.getFirstChild();

root.appendChild(makeupPeopleNode(doc,n,s,q,a,ad,p)); TransformerFactory transformerFactory =

TransformerFactory.newInstance();

Transformer transformer =

transformerFactory.newTransformer();

DOMSource newDomSource = new DOMSource(root);

transformer.transform(newDomSource,new

StreamResult(new FileOutputStream(xmlFile)));

init();

}

catch(Exception e8)

{

System.out.println("异常信息3:" + e8);

}

}

if(delete == e.getSource()) //从xml文件中删除选中信息； {

try

{

System.out.println(table.getSelectedRow());

Element root = doc.getDocumentElement();

root.removeChild(root.getElementsByTagName("sex").item(table.getSelectedRow()).getParentNode());

TransformerFactory transformerFactory =

TransformerFactory.newInstance();

Transformer transformer =

transformerFactory.newTransformer();

DOMSource newDomSource = new DOMSource(root);

transformer.transform(newDomSource,new StreamResult(new FileOutputStream(xmlFile)));

init();

}

catch(Exception e9)

{

System.out.println("异常信息4:" + e9);

}

}

if(CDB==e.getSource()){ //存库；

XMLToDatabaseWithDom m=new XMLToDatabaseWithDom(); try {

m.toDatabase();

JOptionPane.showMessageDialog(this, "data.xml中的信息已解析存入数据库中！", null, JOptionPane.WARNING_MESSAGE);

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

JOptionPane.showConfirmDialog(this,e);

}

}

if(DDB==e.getSource()){//解析数据；

DatabaseToXMLWithDom m=new DatabaseToXMLWithDom(); try {

m.toXML();

JOptionPane.showMessageDialog(this, "数据库中的信息已存入data.xml中！",null, JOptionPane.WARNING_MESSAGE);

} catch (SQLException e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

JOptionPane.showConfirmDialog(this,e);

}

}

if(XQuery == e.getSource()) //利用XQuery查询；

{

String lookupName1 = name.getText();

//System.out.println(lookupName);

try

{

for(int i = 0; i < table.getRowCount(); i++) {

studentInfo[i][0] = "";

studentInfo[i][1] = "";

studentInfo[i][2] = "";

studentInfo[i][3] = "";

studentInfo[i][4] = "";

studentInfo[i][5] = "";

}

XQDataSource ds = new SaxonXQDataSource();

XQConnection conn = ds.getConnection();

XQPreparedExpression exp = conn.prepareExpression("for $x in doc(\"data.xml\")/addresslist/people where

$x/name='"+lookupName1+"' return $x");

XQResultSequence result=exp.executeQuery();

NodeList names=null;

NodeList sexes=null ;

NodeList qqs=null;

NodeList ages=null ;

NodeList addresses=null;

NodeList phones=null;

while(result.next()){

Element el=(Element) result.getNode();

names = el.getElementsByTagName("name"); sexes = el.getElementsByTagName("sex"); qqs = el.getElementsByTagName("qq");

ages = el.getElementsByTagName("age");

addresses = el.getElementsByTagName("address"); phones = el.getElementsByTagName("phone"); System.out.println(el.getTextContent()); }

int index=0;

for(int i = 0; i < names.getLength(); i++)

{

studentInfo[index][0] =

names.item(i).getTextContent();

studentInfo[index][1] =

sexes.item(i).getTextContent();

studentInfo[index][2] =

qqs.item(i).getTextContent();

studentInfo[index][3] =

ages.item(i).getTextContent();

studentInfo[index][4] =

addresses.item(i).getTextContent();

studentInfo[index][5] =

phones.item(i).getTextContent();

index++;

}

repaint();

}

catch(Exception e8)

{

JOptionPane.showMessageDialog(this, "没有匹配信息！", null, JOptionPane.WARNING_MESSAGE);

}

}

if(lookup == e.getSource()) //通过名字查找；

{

String lookupName = name.getText();

try

{

for(int i = 0; i < table.getRowCount(); i++) {

studentInfo[i][0] = "";

studentInfo[i][1] = "";

studentInfo[i][2] = "";

studentInfo[i][3] = "";

studentInfo[i][4] = "";

studentInfo[i][5] = "";

}

doc = getDocument();

Element root = doc.getDocumentElement();

NodeList names = root.getElementsByTagName("name"); NodeList sexes = root.getElementsByTagName("sex"); NodeList qqs = root.getElementsByTagName("qq"); NodeList ages = root.getElementsByTagName("age"); NodeList addresses =

root.getElementsByTagName("address");

NodeList phones = root.getElementsByTagName("phone");

int index=0;

for(int i = 0; i < names.getLength(); i++)

{

if(names.item(i).getTextContent().indexOf(lookupName) != -1) {

studentInfo[index][0] =

names.item(i).getTextContent();

studentInfo[index][1] =

sexes.item(i).getTextContent();

studentInfo[index][2] =

qqs.item(i).getTextContent();

studentInfo[index][3] =

ages.item(i).getTextContent();

studentInfo[index][4] =

addresses.item(i).getTextContent();

studentInfo[index][5] =

phones.item(i).getTextContent();

index++;

}

}

repaint();

}

catch(Exception e8)

{

System.out.println("异常信息6:" + e8);

JOptionPane.showMessageDialog(this, "没有匹配信息！", null, JOptionPane.WARNING_MESSAGE);

}

}

if(showAllResult == e.getSource())//显示所有信息；

{

init();

}

if(change == e.getSource())//修改信息；

{

String n="",s="",q="",a="",ad="",p="";

try{n=name.getText();}catch(Exception e2){n="";}

try{s=(String)(gender.getSelectedItem());}catch(Exception e3){s="";}

try{q=qq.getText();}catch(Exception e4){q="";}

try{a=age.getText();}catch(Exception e5){a="";}

try{ad=address.getText();}catch(Exception e6){ad="";} try{p=phone.getText();}catch(Exception e7){p="";} try

{

Element root = doc.getDocumentElement();

root.replaceChild(makeupPeopleNode(doc,n,s,q,a,ad,p),root.getElementsByTagName("sex").item(table.getSelectedRow()).getParentNode()); TransformerFactory transformerFactory =

TransformerFactory.newInstance();

Transformer transformer =

transformerFactory.newTransformer();

DOMSource newDomSource = new DOMSource(root); transformer.transform(newDomSource,new

StreamResult(new FileOutputStream(xmlFile)));

init();

}

catch(Exception e8)

{

System.out.println("异常信息5:" + e8);

}

}

}

public Node makeupPeopleNode(Document doc,String n,String s,String q,String a,String ad,String p)//生成people元素,一个xml文件节点； {

Element people2 = doc.createElement("people");

try

{

Element name2 = doc.createElement("name");

Element sex2 = doc.createElement("sex");

Element qq2 = doc.createElement("qq");

Element age2 = doc.createElement("age");

Element address2 = doc.createElement("address");

Element phone2 = doc.createElement("phone");

name2.setTextContent(n);

sex2.setTextContent(s);

qq2.setTextContent(q);

age2.setTextContent(a);

address2.setTextContent(ad);

phone2.setTextContent(p);

people2.appendChild(name2);

people2.appendChild(sex2);

people2.appendChild(qq2);

people2.appendChild(age2);

people2.appendChild(address2);

people2.appendChild(phone2);

}

catch(Exception e)

{

System.out.println("异常信息2:" + e);

}

return people2;

}

}

XMLToDatabaseWithDom.java

package zy;

import java.io.File;

import java.sql.*;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

import org.w3c.dom.NodeList;

public class XMLToDatabaseWithDom {

public void toDatabase() throws SQLException{

Connection connection=null;

PreparedStatement statement=null;

ResultSet rs=null;

try{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

}

catch(ClassNotFoundException e){System.out.print(e);}

connection=DriverManager.getConnection("jdbc:odbc:xmlcontact","",""); String sq="DELETE FROM people";

Statement s=null;

try {

s=connection.createStatement();

s.execute(sq);

} catch (SQLException e) {

// TODO Auto-generated catch block

System.out.print(e);

}

String sql="INSERT INTO people VALUES (?,?,?,?,?,?)"; try {

statement=connection.prepareStatement(sql);

DocumentBuilderFactory

factory=DocumentBuilderFactory.newInstance();

factory.setIgnoringElementContentWhitespace(true); //忽略空白缩进

DocumentBuilder domParser=factory.newDocumentBuilder(); Document document=domParser.parse(new File("data.xml")); //通过已经存在的文件创建Document对象;

Element root=document.getDocumentElement();

NodeList list1=root.getElementsByTagName("name"); NodeList list2=root.getElementsByTagName("sex"); NodeList list3=root.getElementsByTagName("qq"); NodeList list4=root.getElementsByTagName("age"); NodeList list5=root.getElementsByTagName("address"); NodeList list6=root.getElementsByTagName("phone"); int size=list1.getLength(); //获取长度

String[] name=new String[size];

String[] sex=new String[size];

String[] qq=new String[size];

String[] age=new String[size];

String[] address=new String[size];

String[] phone=new String[size];

for(int k=0;k<size;k++){

Node numberNode=list1.item(k);

Node nameNode=list2.item(k);

Node dateNode=list3.item(k);

Node heightNode=list4.item(k);

Node addressNode=list5.item(k);

Node phoneNode=list6.item(k);

name[k]=numberNode.getTextContent().trim(); sex[k]=nameNode.getTextContent().trim();

qq[k]=dateNode.getTextContent().trim();

age[k]=heightNode.getTextContent().trim();

address[k]=addressNode.getTextContent().trim(); phone[k]=phoneNode.getTextContent().trim();

statement.setString(1, name[k]);

statement.setString(2, sex[k]);

statement.setString(3, qq[k]);

statement.setString(4, age[k]);

statement.setString(5, address[k]);

statement.setString(6, phone[k]);

statement.executeUpdate();

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public void getItemFromDatabase() throws SQLException{ Connection connection=null;

PreparedStatement statement=null;

ResultSet rs=null;

try{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

}

catch(ClassNotFoundException e){System.out.print(e);}

connection=DriverManager.getConnection("jdbc:odbc:xmlcontact","", "");

String sql="SELECT * FROM people";

try {

statement=connection.prepareStatement(sql); rs=statement.executeQuery();

while(rs.next()){

System.out.println(rs.getString(1)+rs.getString(2)+rs.getString

(3)+rs.getDouble(4)+rs.getString(5)+rs.getString(6));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

DatabaseToXMLWithDom.java

public class DatabaseToXMLWithDom {

public void toXML() throws SQLException{

String[] name={""};

String[] sex={""};

String[] qq={""};

String[] age={""};

String[] address={""};

String[] phone={""};

Connection connection=null;

PreparedStatement statement=null;

ResultSet rs=null;

ResultSet rs1=null;

try{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

}

catch(ClassNotFoundException e){System.out.print(e);}

connection=DriverManager.getConnection("jdbc:odbc:xmlcontact","","");

String sql="SELECT * FROM people";

try {

statement=connection.prepareStatement(sql); rs=statement.executeQuery();

int recordAmount=0;

while(rs.next()){

recordAmount++;

}

// rs.last(); //讲游标移到结果集的最后一行

获取记录数据 name=new String[recordAmount];

sex=new String[recordAmount];

qq=new String[recordAmount];

age=new String[recordAmount];

address=new String[recordAmount];

phone=new String[recordAmount];

int k=0;

// rs.beforeFirst(); //讲游标移到第一条记录前 rs1=statement.executeQuery();

while(rs1.next()){

name[k]=rs1.getString(1).trim();

sex[k]=rs1.getString(2).trim();

qq[k]=rs1.getString(3).trim();

age[k]=rs1.getString(4).trim();

address[k]=rs1.getString(5).trim();

phone[k]=rs1.getString(6).trim();

k++;

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

DocumentBuilderFactory

factory=DocumentBuilderFactory.newInstance();

factory.setIgnoringElementContentWhitespace(true); //忽略空白缩进

DocumentBuilder domParser;

try {

domParser = factory.newDocumentBuilder();

Document document=domParser.newDocument(); //通过调用newDocument() 方法获取实例

document.setXmlVersion("1.0"); //设置版本号

Element root=document.createElement("addresslist"); document.appendChild(root);

for(int k=0;k<name.length;k++){

Node people=document.createElement("people"); root.appendChild(people);

Node minzi=document.createElement("name");

minzi.appendChild(document.createTextNode(name[k]));

Node xingbie=document.createElement("sex");

xingbie.appendChild(document.createTextNode(sex[k]));

Node QQ=document.createElement("qq");

QQ.appendChild(document.createTextNode(qq[k]));

Node

nianling=document.createElement("age");

nianling.appendChild(document.createTextNode(age[k]));

Node

dizi=document.createElement("address");

dizi.appendChild(document.createTextNode(address[k]));

Node

dianhua=document.createElement("phone");

dianhua.appendChild(document.createTextNode(phone[k]));

people.appendChild(minzi);

people.appendChild(xingbie);

people.appendChild(QQ);

people.appendChild(nianling);

people.appendChild(dizi);

people.appendChild(dianhua);

TransformerFactory

transFactory=TransformerFactory.newInstance(); //工厂对象获取transFactory实例

Transformer

transformer=transFactory.newTransformer(); //获取Transformer实例 DOMSource domSource=new DOMSource(document);

File file=new File("data.xml");

FileOutputStream out=new FileOutputStream(file);

//OutputStreamWriter OutputStreamWrite(out);

StreamResult xmlResult=new StreamResult(out);

transformer.transform(domSource,

xmlResult);

out.close();

}

} catch (Exception e) {

// TODO Auto-generated catch block e.printStackTrace();

}