Javaweb第三章课后习题
需要的jar包(自行下载):Connector-J-8.0,dom4j-2.1.1
首先,建立一张数据表emp,我建立的数据库和数据表名都为emp
然后创建一个XML文件,命名为employee.xml,保存在D盘,用来解析
<?xml version="1.0" encoding="GBK"?>
<emps>
<emp>
<empno>1000</empno>
<ename>张三</ename>
<job>经理</job>
<hiredate>1998-08-16</hiredate>
<sal>3000</sal>
<comm>500</comm>
</emp>
</emps>
接下来就是利用DOM4J解析XML文件,向数据库表中插入数据
InsertData.java
package test03;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
public class InsertData {
public static final String DBDRIVER = "com.mysql.cj.jdbc.Driver";
//定义MySQL的数据库驱动程序,本程序用的是mysql-connector-java-8.0.12.jar
public static final String DBURL = "jdbc:mysql://localhost:3306/emp?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
//定义MySQL数据库的连接地址,格式:jdbc:mysql://IP地址:端口号/数据库名?字符集为utf-8&时区为GMT&SSL连接关闭
public static final String DBUSER = "root"; //MySQL数据库的连接用户名
public static final String DBPASS = "123456"; //MySQL数据库的连接密码
public static void main(String[] args) {
File file = new File("D:" + File.separator + "employee.xml"); //读取文件
SAXReader reader = new SAXReader(); //建立SAX解析读取
Document doc = null;
try {
doc = reader.read(file); //读取文档
} catch(DocumentException e) {
e.printStackTrace();
}
String emno = "";
String emname = "";
String emjob = "";
String emhiredate = "";
String emsal = "";
String emcomm = "";
Element root = doc.getRootElement(); //取得根元素
Iterator<Element> iter = root.elementIterator(); //取得全部的子节点
while(iter.hasNext()) {
Element linkman = iter.next(); //取得每一个linkman
emno = linkman.elementText("empno");
emname = linkman.elementText("ename");
emjob = linkman.elementText("job");
emhiredate = linkman.elementText("hiredate");
emsal = linkman.elementText("sal");
emcomm = linkman.elementText("comm");
}
Connection conn = null; //数据库连接
Statement stmt = null; //数据库操作
String sql = "INSERT INTO emp(empno,ename,job,hiredate,sal,comm)" + "VALUES('"+ emno +"','"+ emname +"','"+ emjob + "','"+ emhiredate +"','"+ emsal +"','"+ emcomm +"')"; //插入数据操作,拼凑出一个完整的SQL语句
try {
Class.forName(DBDRIVER); //加载驱动程序
System.out.println("加载驱动程序成功!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(DBURL,DBUSER,DBPASS);
System.out.println("连接数据库!");
if(!conn.isClosed()) {
System.out.println("Succeeded connecting to the Database!");
}
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println(conn);
try {
stmt = conn.createStatement(); //创建statement类对象,用来执行SQL语句
} catch (SQLException e) {
e.printStackTrace();
}
try {
stmt.executeUpdate(sql);
} catch (SQLException e1) {
e1.printStackTrace();
} //执行数据库更新操作
try {
stmt.close();
} catch (SQLException e1) {
e1.printStackTrace();
} //操作关闭
try {
conn.close(); //数据库关闭
} catch (SQLException e) {
e.printStackTrace();
}
}
}
插入数据前后: