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
要么忙著生存，要么趕著去死！人總是要做點什么的！