MySQL 版本：Server version: 5.7.17-log MySQL Community Server (GPL)
用于测试的 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.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.ArrayList;
8 import java.util.List;
9
10 import com.mk.pojo.User;
11 import com.mk.util.DBUtil;
12
13 public class Demo {
14
15 public static void main(String[] args) {
16 List<User> list = selectAll();
17 if (list != null) {
18 for (User user : list) {
19 if (user != null) {
20 System.out.print("id: " + user.getId() + "\t\t");
21 System.out.print("username: " + user.getUsername() + "\t\t");
22 System.out.println("password: " + user.getPassword());
23 }
24 }
25 }
26 }
27
28 private static List<User> selectAll() {
29 List<User> list = new ArrayList<>();
30
31 // 查询语句
32 String sql = "SELECT * FROM user";
33 Connection connection = DBUtil.getConnection();
34 PreparedStatement ps = DBUtil.getPreparedStatement(connection, sql);
35 ResultSet rs = null;
36 try {
37 // 返回查询结果
38 rs = ps.executeQuery();
39 while (rs.next()) {
40 User user = new User();
41 // 使用 User 对象封装查询到的数据
42 user.setId(rs.getInt("id"));
43 user.setUsername(rs.getString("username"));
44 user.setPassword(rs.getString("password"));
45 // 将 User 对象加入到 ArrayList 中
46 list.add(user);
47 }
48 } catch (SQLException e) {
49 e.printStackTrace();
50 return null;
51 } finally {
52 DBUtil.close(connection, ps, rs);
53 }
54 return list;
55 }
56 }