MySQL 版本：Server version: 5.7.17-log MySQL Community Server (GPL)
相关内容：JDBC Java 程序从 MySQL 数据库中读取数据，并封装到 Javabean 对象中
用于测试的 MySQL 数据库：game
查看表的定义
mysql> DESC user;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| username | varchar(16) | NO | | NULL | |
| password | varchar(16) | NO | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
查看表中的数据
mysql> SELECT * FROM user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | mk | 123 |
| 2 | seven | 456 |
| 3 | tutu | 789 |
| 4 | lan | 666 |
+----+----------+----------+
4 rows in set (0.00 sec)
Java 工程结构：
db.properties 文件中的内容：
mysqlDriver=com.mysql.jdbc.Driver
mysqlUrl=jdbc\:mysql\://localhost\:3306/game
mysqlUser=root
mysqlPassword=123456
DBUtil.java 文件中的内容：
1 package com.mk.util;
2
3 import java.io.IOException;
4 import java.sql.Connection;
5 import java.sql.DriverManager;
6 import java.sql.PreparedStatement;
7 import java.sql.ResultSet;
8 import java.sql.SQLException;
9 import java.sql.Statement;
10 import java.util.Properties;
11
12 public class DBUtil {
13 static Properties properties = null; // 用于读取和处理资源文件中的信息
14 static { // 类加载的时候被执行一次
15 properties = new Properties();
16 try {
17 properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
18 } catch (IOException e) {
19 e.printStackTrace();
20 }
21 }
22
23 public static Connection getConnection() {
24 try {
25 // 加载 MySQL JDBC 驱动类
26 Class.forName(properties.getProperty("mysqlDriver"));
27 // 建立连接（连接对象内部其实包含了Socket对象，是一个远程的连接，比较耗时！这是Connection对象管理的一个要点！）
28 // 真正开发中，为了提高效率，都会使用连接池来管理连接对象！
29 String mysqlUrl = properties.getProperty("mysqlUrl");
30 String mysqlUser = properties.getProperty("mysqlUser");
31 String mysqlPassword = properties.getProperty("mysqlPassword");
32 return DriverManager.getConnection(mysqlUrl, mysqlUser, mysqlPassword);
33 } catch (ClassNotFoundException e) {
34 e.printStackTrace();
35 return null;
36 } catch (SQLException e) {
37 e.printStackTrace();
38 return null;
39 }
40 }
41
42 public static PreparedStatement getPreparedStatement(Connection connection, String sql) {
43 try {
44 // 使用 PreparedStatement，防止 SQL 注入
45 return connection.prepareStatement(sql);
46 } catch (SQLException e) {
47 e.printStackTrace();
48 return null;
49 }
50 }
51
52 public static void close(Connection connection, Statement statement, ResultSet resultSet) {
53 if (resultSet != null) {
54 try {
55 resultSet.close();
56 } catch (SQLException e) {
57 e.printStackTrace();
58 }
59 }
60 if (statement != null) {
61 try {
62 statement.close();
63 } catch (SQLException e) {
64 e.printStackTrace();
65 }
66 }
67 if (connection != null) {
68 try {
69 connection.close();
70 } catch (SQLException e) {
71 e.printStackTrace();
72 }
73 }
74 }
75
76 public static void close(Connection connection) {
77 if (connection != null) {
78 try {
79 connection.close();
80 } catch (SQLException e) {
81 e.printStackTrace();
82 }
83 }
84 }
85
86 public static void close(Statement statement) {
87 if (statement != null) {
88 try {
89 statement.close();
90 } catch (SQLException e) {
91 e.printStackTrace();
92 }
93 }
94 }
95
96 public static void close(ResultSet resultSet) {
97 if (resultSet != null) {
98 try {
99 resultSet.close();
100 } catch (SQLException e) {
101 e.printStackTrace();
102 }
103 }
104 }
105 }
User.java 文件中的内容：
1 package com.mk.pojo;
2
3 import java.io.Serializable;
4
5 public class User implements Serializable {
6 private int id;
7 private String username;
8 private String password;
9
10 public User() {
11
12 }
13
14 public User(int id, String username, String password) {
15 super();
16 this.id = id;
17 this.username = username;
18 this.password = password;
19 }
20
21 @Override
22 public String toString() {
23 return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
24 }
25
26 @Override
27 public int hashCode() {
28 final int prime = 31;
29 int result = 1;
30 result = prime * result + id;
31 result = prime * result + ((password == null) ? 0 : password.hashCode());
32 result = prime * result + ((username == null) ? 0 : username.hashCode());
33 return result;
34 }
35
36 @Override
37 public boolean equals(Object obj) {
38 if (this == obj)
39 return true;
40 if (obj == null)
41 return false;
42 if (getClass() != obj.getClass())
43 return false;
44 User other = (User) obj;
45 if (id != other.id)
46 return false;
47 if (password == null) {
48 if (other.password != null)
49 return false;
50 } else if (!password.equals(other.password))
51 return false;
52 if (username == null) {
53 if (other.username != null)
54 return false;
55 } else if (!username.equals(other.username))
56 return false;
57 return true;
58 }
59
60 public int getId() {
61 return id;
62 }
63
64 public void setId(int id) {
65 this.id = id;
66 }
67
68 public String getUsername() {
69 return username;
70 }
71
72 public void setUsername(String username) {
73 this.username = username;
74 }
75
76 public String getPassword() {
77 return password;
78 }
79
80 public void setPassword(String password) {
81 this.password = password;
82 }
83 }
Demo.java 文件中的内容：
1 package com.mk;
2
3 import java.io.File;
4 import java.io.FileWriter;
5 import java.io.IOException;
6 import java.io.Writer;
7 import java.sql.Connection;
8 import java.sql.PreparedStatement;
9 import java.sql.ResultSet;
10 import java.sql.SQLException;
11 import java.text.SimpleDateFormat;
12 import java.util.ArrayList;
13 import java.util.Date;
14 import java.util.List;
15
16 import org.dom4j.Document;
17 import org.dom4j.DocumentHelper;
18 import org.dom4j.Element;
19 import org.dom4j.io.OutputFormat;
20 import org.dom4j.io.XMLWriter;
21
22 import com.mk.pojo.User;
23 import com.mk.util.DBUtil;
24
25 public class Demo {
26
27 public static void main(String[] args) {
28 List<User> list = selectAll();
29 if (list != null) {
30 writeUsers2xml(list);
31 }
32 }
33
34 private static void writeUsers2xml(List<User> list) {
35 // 创建 xml 文档对象
36 Document document = DocumentHelper.createDocument();
37 // 创建根元素
38 Element users = document.addElement("users");
39 // 为根元素添加子元素
40 for (User u : list) {
41 Element user = users.addElement("user").addAttribute("id", (u.getId() + ""));
42 user.addElement("username").addText(u.getUsername());
43 user.addElement("password").addText(u.getPassword());
44 }
45 // 将 xml 文档对象写到具体的 xml 文件中，文件名格式：xmlyyyyMMdd.xml，yyyyMMdd 是指年月日
46 // .1、指定输出格式
47 OutputFormat format = OutputFormat.createPrettyPrint();
48 // .2、输出 xml 文件
49 Writer writer = null;
50 XMLWriter xmlWriter = null;
51 try {
52 writer = new FileWriter(new File("Backup" + new SimpleDateFormat("yyyyMMdd").format(new Date()) + ".xml"));
53 xmlWriter = new XMLWriter(writer, format);
54 xmlWriter.write(document);
55 } catch (IOException e) {
56 e.printStackTrace();
57 } finally {
58 if (xmlWriter != null) {
59 try {
60 xmlWriter.close();
61 } catch (IOException e) {
62 e.printStackTrace();
63 }
64 }
65 if (writer != null) {
66 try {
67 writer.close();
68 } catch (IOException e) {
69 e.printStackTrace();
70 }
71 }
72 }
73
74 }
75
76 private static List<User> selectAll() {
77 List<User> list = new ArrayList<>();
78
79 // 查询语句
80 String sql = "SELECT * FROM user ORDER BY id";
81 Connection connection = DBUtil.getConnection();
82 PreparedStatement ps = DBUtil.getPreparedStatement(connection, sql);
83 ResultSet rs = null;
84 try {
85 // 返回查询结果
86 rs = ps.executeQuery();
87 while (rs.next()) {
88 User user = new User();
89 // 使用 User 对象封装查询到的数据
90 user.setId(rs.getInt("id"));
91 user.setUsername(rs.getString("username"));
92 user.setPassword(rs.getString("password"));
93 // 将 User 对象加入到 ArrayList 中
94 list.add(user);
95 }
96 } catch (SQLException e) {
97 e.printStackTrace();
98 return null;
99 } finally {
100 DBUtil.close(connection, ps, rs);
101 }
102 return list;
103 }
104 }
运行程序后，刷新（快捷键 F5） Java 工程目录，就可以看到备份的 xml 文件：
xml 文件中的内容：
1 <?xml version="1.0" encoding="UTF-8"?>
2
3 <users>
4 <user id="1">
5 <username>mk</username>
6 <password>123</password>
7 </user>
8 <user id="2">
9 <username>seven</username>
10 <password>456</password>
11 </user>
12 <user id="3">
13 <username>tutu</username>
14 <password>789</password>
15 </user>
16 <user id="4">
17 <username>lan</username>
18 <password>666</password>
19 </user>
20 </users>