JDBC Java 程序从 MySQL 数据库中读取数据，并封装到 Javabean 对象中

MySQL 版本：Server version: 5.7.17-log MySQL Community Server (GPL)

相关内容：JDBC Java 连接 MySQL 数据库

---

用于测试的 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 文件中的内容：

package com.mk.util;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DBUtil {
  static Properties properties = null; // 用于读取和处理资源文件中的信息
  static { // 类加载的时候被执行一次
    properties = new Properties();
    try {
      properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
    } catch (IOException e) {
      e.printStackTrace();
    }
  }

  public static Connection getConnection() {
    try {
      // 加载 MySQL JDBC 驱动类
      Class.forName(properties.getProperty("mysqlDriver"));
      // 建立连接（连接对象内部其实包含了Socket对象，是一个远程的连接，比较耗时！这是Connection对象管理的一个要点！）
      // 真正开发中，为了提高效率，都会使用连接池来管理连接对象！
      String mysqlUrl = properties.getProperty("mysqlUrl");
      String mysqlUser = properties.getProperty("mysqlUser");
      String mysqlPassword = properties.getProperty("mysqlPassword");
      return DriverManager.getConnection(mysqlUrl, mysqlUser, mysqlPassword);
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
      return null;
    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    }
  }

  public static PreparedStatement getPreparedStatement(Connection connection, String sql) {
    try {
      // 使用 PreparedStatement，防止 SQL 注入
      return connection.prepareStatement(sql);
    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    }
  }

  public static void close(Connection connection, Statement statement, ResultSet resultSet) {
    if (resultSet != null) {
      try {
        resultSet.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (statement != null) {
      try {
        statement.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close(Connection connection) {
    if (connection != null) {
      try {
        connection.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close(Statement statement) {
    if (statement != null) {
      try {
        statement.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static void close(ResultSet resultSet) {
    if (resultSet != null) {
      try {
        resultSet.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

User.java 文件中的内容：

package com.mk.pojo;

import java.io.Serializable;

public class User implements Serializable {
  private int id;
  private String username;
  private String password;

  public User() {

  }

  public User(int id, String username, String password) {
    super();
    this.id = id;
    this.username = username;
    this.password = password;
  }

  @Override
  public String toString() {
    return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
  }

  @Override
  public int hashCode() {
    final int prime = 31;
    int result = 1;
    result = prime * result + id;
    result = prime * result + ((password == null) ? 0 : password.hashCode());
    result = prime * result + ((username == null) ? 0 : username.hashCode());
    return result;
  }

  @Override
  public boolean equals(Object obj) {
    if (this == obj)
      return true;
    if (obj == null)
      return false;
    if (getClass() != obj.getClass())
      return false;
    User other = (User) obj;
    if (id != other.id)
      return false;
    if (password == null) {
      if (other.password != null)
        return false;
    } else if (!password.equals(other.password))
      return false;
    if (username == null) {
      if (other.username != null)
        return false;
    } else if (!username.equals(other.username))
      return false;
    return true;
  }

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getUsername() {
    return username;
  }

  public void setUsername(String username) {
    this.username = username;
  }

  public String getPassword() {
    return password;
  }

  public void setPassword(String password) {
    this.password = password;
  }
}

Demo.java 文件中的内容：

package com.mk;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mk.pojo.User;
import com.mk.util.DBUtil;

public class Demo {
  
  public static void main(String[] args) {
    List<User> list = selectAll();
    if (list != null) {
      for (User user : list) {
        if (user != null) {
          System.out.print("id: " + user.getId() + "		");
          System.out.print("username: " + user.getUsername() + "		");
          System.out.println("password: " + user.getPassword());
        }
      }
    }
  }

  private static List<User> selectAll() {
    List<User> list = new ArrayList<>();

    // 查询语句
    String sql = "SELECT * FROM user";
    Connection connection = DBUtil.getConnection();
    PreparedStatement ps = DBUtil.getPreparedStatement(connection, sql);
    ResultSet rs = null;
    try {
      // 返回查询结果
      rs = ps.executeQuery();
      while (rs.next()) {
        User user = new User();
        // 使用 User 对象封装查询到的数据  
        user.setId(rs.getInt("id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        // 将 User 对象加入到 ArrayList 中
        list.add(user);
      }
    } catch (SQLException e) {
      e.printStackTrace();
      return null;
    } finally {
      DBUtil.close(connection, ps, rs);
    }
    return list;
  }
}