DBUtils的封装
1.1 创建配置文件
在src目录下面创建一个文件为：dbinfo.properties
相关内容为：
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql:///demo
username=root
password=123456
1.2 DBUtils的封装
主要的工作是：
1. 加载配置文件，初始化些参数，注册数据库驱动
2. 封装一个得到连接的方法
3. 封装一个安全关闭资源的方法
示例如下：
public class DBUtils {
private static String driverClass;
private static String url;
private static String username;
private static String password;
static {
//此对象是用于加载properties文件数据的
ResourceBundle rb = ResourceBundle.getBundle("dbinfo");
driverClass = rb.getString("driverClass");
url = rb.getString("url");
username = rb.getString("username");
password = rb.getString("password");
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//得到连接的方法
public static Connection getConnection() throws Exception {
return DriverManager.getConnection(url, username, password);
}
//关闭资源的方法
public static void closeAll(ResultSet rs, Statement stmt, Connection conn) {
//关闭资源
if (rs != null) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
conn = null;
}
}
}
使用DBUtils实现增删改查
2.1 为了方便，我们需要封装一个Bean类：
public class User {
private int id;
private String name;
private String email;
private String password;
public User(int id, String name, String email, String password) {
this.id = id;
this.name = name;
this.email = email;
this.password = password;
}
public User() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", password='" + password + '\'' +
'}';
}
}
2.2 这里用JUnit单元测试框架进行测试，相关的使用可以自行百度，4个方法分别代表增删改查的示例：
@Test
public void insert() throws Exception {
Connection conn = DBUtils.getConnection();
PreparedStatement stmt = conn.prepareStatement("INSERT INTO users(id,name,email,password) VALUE(?,?,?,?)");
User user = new User(2, "wuhuannan", "123@email.com", "321");
stmt.setInt(1, user.getId());
stmt.setString(2, user.getName());
stmt.setString(3, user.getEmail());
stmt.setString(4, user.getPassword());
int num = stmt.executeUpdate();
if (num > 0) {
System.out.println("success");
}
DBUtils.closeAll(null, stmt, conn);
}
@Test
public void delete() throws Exception {
Connection conn = DBUtils.getConnection();
PreparedStatement stmt = conn.prepareStatement("DELETE FROM users WHERE id = ?");
stmt.setInt(1, 1);
int num = stmt.executeUpdate();
if (num > 0) {
System.out.println("success");
}
DBUtils.closeAll(null, stmt, conn);
}
@Test
public void update() throws Exception {
Connection conn = DBUtils.getConnection();
PreparedStatement stmt = conn.prepareStatement("UPDATE users SET name = ? WHERE id = ?");
stmt.setString(1, "nan");
stmt.setInt(2, 1);
int num = stmt.executeUpdate();
if (num > 0) {
System.out.println("success");
}
DBUtils.closeAll(null, stmt, conn);
}
@Test
public void query() throws Exception {
Connection conn = DBUtils.getConnection();
Statement stmt = conn.createStatement();
ResultSet res = stmt.executeQuery("SELECT * FROM users");
List<User> users = new ArrayList<>();
while (res.next()) {
User user = new User();
user.setId(res.getInt("id"));
user.setEmail(res.getString("email"));
user.setName(res.getString("name"));
user.setPassword(res.getString("password"));
users.add(user);
}
System.out.println(Arrays.asList(users));
DBUtils.closeAll(res, stmt, conn);
}
实现一个简单的用户登录功能
核心逻辑：通过传入的用户名和密码，利用DBUtils查找User，如果查找有值则登录成功，否则登录失败。
相关的类图如下：
image.png
首先，我们先封装一个DoLogin类专门用于处理用户登录的业务逻辑：
public class DoLogin {
public static User findUser(String name, String password) {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet res = null;
User user = null;
try {
conn = DBUtils.getConnection();
stmt = conn.prepareStatement("SELECT * FROM users WHERE name = ? AND password = ?");
stmt.setString(1, name);
stmt.setString(2, password);
res = stmt.executeQuery();
if (res.next()) {
user = new User();
user.setId(res.getInt("id"));
user.setEmail(res.getString("email"));
user.setName(res.getString("name"));
user.setPassword(res.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBUtils.closeAll(res, stmt, conn);
}
return user;
}
}
然后，进行测试：
public class Client {
public static void main(String[] args) {
String name = "wuhuannan";
String password = "321";
User user = DoLogin.findUser(name, password);
if (user != null) {
System.out.println("登陆成功！");
} else {
System.out.println("登陆失败！");
}
}
}
SQL注入问题
当SQL语句需要传入参数的时候，使用PreparedStatement代替Statement，就可以防止SQL注入问题（参数被传入SQL语句）：
stmt = conn.prepareStatement("SELECT * FROM users WHERE name = ? AND password = ?");
stmt.setString(1, name);
stmt.setString(2, password);
res = stmt.executeQuery();
PreparedStatement：预编译对象， 是Statement对象的子类
PreparedStatement特点：
- 性能要比Statement高
- 会把sql语句先编译
- sql语句中的参数会发生变化，过滤掉用户输入的关键字
