1：ReadXml
package com.cn.vv.xml;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;
public class ReadXml
{
public Connection conn = null;
public Statement stmt = null;
public Statement stmtsec = null;
public static void main(String[] args)
{
ReadXml rx = new ReadXml();
rx.readXml();
}
public void readXml()
{
String username = "";
String id = "";
String homephone = "";
String officephone = "";
String password = "";
String sex = "";
String homeaddress = "";
String corpaddress = "";
String mobile = "";
String StudentID = "";
String StudentName = "";
String StudentAge = "";
// ***************************建立数据库连接***************************
try
{
/*
* Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver")
* .newInstance(); String url =
* "jdbc:microsoft:sqlserver://localhost:1433;databasename=vv";//
* jdomdb为你的数据库名 conn = DriverManager.getConnection(url, "sa", "");
*/
GetConnection getconn = new GetConnection();
conn = getconn.getConnection();
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stmtsec = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
} catch (Exception sqlexception)
{
System.out.println("数据库连接发生异常！");
}
// **********************读xml文件并写入数据库**************************************
try
{
SAXBuilder sb = new SAXBuilder();
Document doc = sb.build("userinfo.xml");
Element root = doc.getRootElement();
Element elms = null;
Element elms2 = null;
Element elms3 = null;
Element elms4 = null;
List list2 = null;
List list3 = null;
List list1 = root.getChildren("userinfo");
List list4 = root.getChildren("student");
for (int i = 0; i < list4.size(); i++)
{
System.out.println("vvvvvvvvvvvvvvvvvvvv");
elms4 = (Element) list4.get(i);// student接点子元素
StudentID = elms4.getChildText("StudentID");
StudentName = elms4.getChildText("StudentName");
StudentAge = elms4.getChildText("StudentAge");
String sql = "insert into studentnew(StudentID,StudentName,StudentAge)values ('"
+ StudentID
+ "','"
+ StudentName
+ "','"
+ StudentAge
+ "')";
stmtsec.executeUpdate(sql);
}
for (int i = 0; i < list1.size(); i++)
{
elms = (Element) list1.get(i); // userinfo节点子元素
id = elms.getChildText("id");
sex = elms.getChildText("sex");
username = elms.getChildText("username");
password = elms.getChildText("password");
list2 = elms.getChildren("userphone"); // 读出userinfo节点的userphone子节点的元素
for (int j = 0; j < list2.size(); j++)
{
elms2 = (Element) list2.get(j);
homephone = elms2.getChildText("homephone");
officephone = elms2.getChildText("officephone");
System.out.println(officephone);
mobile = elms2.getChildText("mobile");
}
list3 = elms.getChildren("useraddress"); // 读出useraddress节点的元素
for (int j = 0; j < list3.size(); j++)
{
elms3 = (Element) list3.get(j);
homeaddress = elms3.getChildText("homeaddress");
corpaddress = elms3.getChildText("corpaddress");
}
// 插入数据库的表tmpinfo officephone
String sql = "insert into tmpinfo(id,username,password,sex,officephone,mobile,homephone,corpaddress,homeaddress)values ('"
+ id
+ "','"
+ username
+ "','"
+ password
+ "','"
+ sex
+ "','"
+ officephone
+ "','"
+ mobile
+ "','"
+ homephone
+ "','"
+ corpaddress
+ "','"
+ homeaddress
+ "')";
stmt.executeUpdate(sql);
}
stmt.close();
stmtsec.close();
conn.close();
} catch (Exception e)
{
e.printStackTrace();
}
// **********************读xml文件并写入数据库**************************************
}
}
2:ExtraXml
package com.cn.vv.xml;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Random;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;
public class ExtraXml
{
String sql = "";
String colName = "";
String sDBDriver = "";
String sConnStr = "";
String url = "";
private Connection conn;
private Statement stmtone;
private Statement stmtsec;
private ResultSet rs;
private ResultSet rs1;
private ResultSet rss;
private ResultSet rss1;
public static void main(String[] args) throws Exception
{
ExtraXml ex = new ExtraXml();
try
{
ex.BuildXMLDoc();
} catch (Exception exp2)
{
System.out.print("调用异常！");
}
}
public ExtraXml()
{
conn = null;
stmtone = null;
// url = "jdbc:microsoft:sqlserver://localhost:1433;databasename=vv";//
// jdomdb为你的数据库名
sql = "select * from userinfo";
}
public void BuildXMLDoc() throws IOException, SQLException
{
int sum1 = 0;
int sum2 = 0;
int sum = 0;
try
{
GetConnection getconn = new GetConnection();
conn = getconn.getConnection();
stmtone = conn.createStatement(); // /创建语句对象
stmtsec = conn.createStatement();
rs1 = stmtone.executeQuery("select count(*) from userinfo");
while (rs1.next())
{
sum1 = rs1.getInt(1);
}
rss1 = stmtone.executeQuery("select count(*) from students");
while (rss1.next())
{
sum2 = rss1.getInt(1);
}
sum = sum1 + sum2;
System.out.println("zongshu======" + sum);
rs = stmtone.executeQuery("select * from userinfo");
rss = stmtsec.executeQuery("select * from students");
System.out.println("vvvvvvvvvvvvvvvvvvvvv");
// System.out.println("数据库连接成功！");
} catch (Exception sqlexception)
{
System.out.println("数据库连接发生异常！");
}
try
{
Document document = new Document(new Element("alluserinfo")); // 创建文档
ResultSetMetaData rsmd = rs.getMetaData(); // 获取字段名
ResultSetMetaData rsd = rss.getMetaData();
int i = 0;
int numberOfColumns = rsmd.getColumnCount();// 获取字段数
int numberofColumnstwo = rsd.getColumnCount();
// System.out.println(numberOfColumns);
for (int n = 0; n < sum; n++)
{
Random r = new Random();
int random = r.nextInt(2);
System.out.println(random);
if (random == 1)
{
if (rs.next()) // 将查询结果取出
{
// System.out.println("vvvvvvvv");
Element element0 = new Element("userinfo"); // 创建元素
// 生成JDOM树
document.getRootElement().addContent(element0);
Element element2 = new Element("userphone");
element0.addContent(element2);
Element element3 = new Element("useraddress");
element0.addContent(element3);
for (i = 1; i <= numberOfColumns; i++)
{
// colName=new
// String(rs.getString(i).getBytes("ISO-8859-1"),"gb2312");
// //代码转换
colName = rs.getString(i);
if (i > 4 && i < 8)// userinfo表中的第5,6,7个字段归为phone节点
{
Element element = new Element(rsmd
.getColumnName(i)).setText(colName);
element2.addContent(element);
} else if (i > 7 && i <= 9)// 第8，第9个字段归为address节点
{
Element element = new Element(rsmd
.getColumnName(i)).setText(colName);
element3.addContent(element);
} else
{
Element element = new Element(rsmd
.getColumnName(i)).setText(colName);
element0.addContent(element);
}
}
}
} else
{
if (rss.next()) // 将查询结果取出
{
// System.out.println("vvvvvvvv");
Element element0 = new Element("student"); // 创建元素
// 生成JDOM树
document.getRootElement().addContent(element0);
for (i = 1; i <= numberofColumnstwo; i++)
{
colName = rss.getString(i);
Element element = new Element(rsd.getColumnName(i))
.setText(colName);
element0.addContent(element);
}
}
}
if (n == sum - 1)
{
while (rs.next()) // 将查询结果取出
{
// System.out.println("vvvvvvvv");
Element element0 = new Element("userinfo"); // 创建元素
// 生成JDOM树
document.getRootElement().addContent(element0);
Element element2 = new Element("userphone");
element0.addContent(element2);
Element element3 = new Element("useraddress");
element0.addContent(element3);
for (i = 1; i <= numberOfColumns; i++)
{
// colName=new
// String(rs.getString(i).getBytes("ISO-8859-1"),"gb2312");
// //代码转换
colName = rs.getString(i);
if (i > 4 && i < 8)// userinfo表中的第5,6,7个字段归为phone节点
{
Element element = new Element(rsmd
.getColumnName(i)).setText(colName);
element2.addContent(element);
} else if (i > 7 && i <= 9)// 第8，第9个字段归为address节点
{
Element element = new Element(rsmd
.getColumnName(i)).setText(colName);
element3.addContent(element);
} else
{
Element element = new Element(rsmd
.getColumnName(i)).setText(colName);
element0.addContent(element);
}
}
}
while (rss.next()) // 将查询结果取出
{
// System.out.println("vvvvvvvv");
Element element0 = new Element("student"); // 创建元素
// 生成JDOM树
document.getRootElement().addContent(element0);
for (i = 1; i <= numberofColumnstwo; i++)
{
colName = rss.getString(i);
Element element = new Element(rsd.getColumnName(i))
.setText(colName);
element0.addContent(element);
}
}
}
}
XMLOutputter outp = new XMLOutputter(Format.getPrettyFormat());
outp.output(document, new FileOutputStream("e:userinfo.xml")); // 输出XML文档
} catch (Exception exp)
{
exp.printStackTrace();
System.out.print("XML 文档生成失败！");
}
rs.close(); // 关闭结果集
stmtone.close(); // 关闭statement
stmtsec.close();
conn.close();
}
}
3:GetConnection
package com.cn.vv.xml;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class GetConnection
{
private String sDBDriver;
private String url;
private String user;
private String password;
public Connection conn;
public Statement stmt;
public Connection getConnection()
{
sDBDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; // //数据库的驱动程序，连接数据库
url = "jdbc:microsoft:sqlserver://localhost:1433;databasename=vv";
user = "sa";
password = "";
conn = null;
try
{
Class.forName(sDBDriver); // /加载驱动程序
conn = DriverManager.getConnection(url, user, password); // /建立Connection连接
stmt = conn.createStatement(); // /创建语句对象
System.out.println("数据库成功！");
} catch (ClassNotFoundException e)
{
e.printStackTrace();
System.out.println("数据库连接失败！");
}
catch (SQLException e)
{
System.out.println("数据库连接发生异常！");
}
return conn;
}
}
DBsql
CREATE TABLE tmpinfo (
id int NOT NULL ,
username varchar(20) NOT NULL default '',
password varchar(20) NOT NULL default '',
sex varchar(10) NOT NULL default '',
officephone varchar(20) NOT NULL default '',
mobile varchar(20) NOT NULL default '',
homephone varchar(20) NOT NULL default '',
corpaddress varchar(60) NOT NULL default '',
homeaddress varchar(60) NOT NULL default '',
PRIMARY KEY (id)
)
--
-- Dumping data for table `tmpinfo`
--
--
-- Table structure for table `userinfo`
--
DROP TABLE IF EXISTS userinfo;
CREATE TABLE userinfo (
id int NOT NULL ,
username varchar(20) NOT NULL default '',
password varchar(20) NOT NULL default '',
sex varchar(10) NOT NULL default '',
officephone varchar(20) NOT NULL default '',
mobile varchar(20) NOT NULL default '',
homephone varchar(20) NOT NULL default '',
corpaddress varchar(60) NOT NULL default '',
homeaddress varchar(60) NOT NULL default '',
PRIMARY KEY (id)
)
select * from userinfo
insert into userinfo values(1,'王磊',54321,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(2,'黎明',11111,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(3,'VV',22222,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(4,'兆位',33333,'女',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(5,'航程',44444,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(6,'杜格',55555,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(7,'YY',66661,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(8,'霏霏',77777,'女',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
--
-- Dumping data for table `userinfo`
--
create table studentnew(
StudentID varchar(20),
StudentName varchar(20),
StudentAge varchar(20),
)
insert into students values('1','vv','21');
insert into students values('2','mm','20');
insert into students values('3','cc','44');
insert into students values('4','ddd','22')
----------------------------------------------------------------------------------------------------------------------
select * from students
delete from students
select * from userinfo
delete from userinfo
insert into userinfo values(1,'王磊',54321,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(2,'黎明',11111,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(3,'VV',22222,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(4,'兆位',33333,'女',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(5,'航程',44444,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(6,'杜格',55555,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(7,'YY',66661,'男',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into userinfo values(8,'霏霏',77777,'女',01012345678,13357279558,01087654321,'北京文明路55号','北京天行网安');
insert into students values('1','vv','21');
insert into students values('2','mm','20');
insert into students values('3','cc','44');
insert into students values('4','ddd','22')
select * from studentnew
select * from tmpinfo
delete from tmpinfo
delete from studentnew
要么忙着生存，要么赶着去死！人总是要做点什么的！