JDBC——代码完善

1. 问题一

• 看如下代码我们发现，倘若在<执行sql语句并返回结果>这一步报出了异常，那程序就会停止，这样就导致后面关闭资源相关的代码无法执行，但是在报异常之前，conn和stmt已经创建好了，为了解决这个问题，可以将关闭资源代码的部分放到finally语句块中。分析发现Connection、Statement、ResultSet都继承了AutoCloseable接口，因此可以使用try-with-resources的方式关闭这些资源。此时优先选用AutoCloseable方法解决问题
• 初始代码

//        注册驱动
        Class.forName("com.mysql.jdbc.Driver");
//        获取连接Connection
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/monkey1024", "root", "monkey1024");
//        得到执行sql语句的对象Statement
        Statement stmt = conn.createStatement();
//        执行sql语句，并返回结果
        ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user");
//        处理结果
        while(rs.next()){
            System.out.println(rs.getObject("id"));
            System.out.println(rs.getObject("name"));
            System.out.println(rs.getObject("password"));
            System.out.println(rs.getObject("email"));
            System.out.println(rs.getObject("birthday"));
            System.out.println("------------");
        }
//        关闭Connection
        rs.close();
        stmt.close();
        conn.close();

• 解决办法

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * jdk7和 JDBC4.1之后的正确关闭资源
 *
 */
public class JDBC_Test03 {

    public static void main(String[] args) {
        // 注册驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        // 获取连接Connection
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/monkey1024", "root",
                "monkey1024");
                // 得到执行sql语句的对象Statement
                Statement stmt = conn.createStatement();
                // 执行sql语句，并返回结果
                ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user")) {
            // 处理结果
            while (rs.next()) {
                System.out.println(rs.getObject("id"));
                System.out.println(rs.getObject("name"));
                System.out.println(rs.getObject("password"));
                System.out.println(rs.getObject("email"));
                System.out.println(rs.getObject("birthday"));
                System.out.println("------------");
            }
        } catch (SQLException e) {
            e.printStackTrace();

        } 
    }

}

2. 问题二

• 分析初始JDBC代码，就会发现里面的增删改查操作中很多代码是重复的，比如注册驱动、创建连接，倘若将来更换数据库或者用户名密码的话需要修改很多内容，这样可维护性不高，为了提高可维护性，可以将这些经常变换内容写到一个配置文件中，这里创建一个名为db.properties的文件：
• db.properties代码

driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/monkey1024
username=root
password=monkey1024

• 创建一个DBUtil的工具类，在这个类中注册驱动和获取连接

package com.monkey1024.jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;

public class DBUtil {

    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;

    static{
        ResourceBundle rb = ResourceBundle.getBundle("db");
        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 SQLException{
        return DriverManager.getConnection(url, username, password);
    }
}

• 问题2解决办法（在有注册驱动和获取连接的操作时，直接调用DBUtil工具类）

@Test
public void testSelect() {
    // 获取连接Connection
    try (Connection conn = DBUtil.getConnection();
            // 得到执行sql语句的对象Statement
            Statement stmt = conn.createStatement();
            // 执行sql语句，并返回结果
            ResultSet rs = stmt.executeQuery("select id,name,password,email,birthday from t_user")) {

        // 处理结果
        List<User> userList = new ArrayList<>();
        while (rs.next()) {
            User u = new User();
            u.setId(rs.getInt("id"));
            u.setName(rs.getString("name"));
            u.setPassword(rs.getString("password"));
            u.setEmail(rs.getString("email"));
            u.setBirthday(rs.getDate("birthday"));
            userList.add(u);
        }
        System.out.println(userList);
    } catch (SQLException e) {
        e.printStackTrace();
    } 

}

• 用PreparedStatement代替Statement

package com.beacon1024.loginService;

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 com.beacon1024.Util.DBUtil;
import com.beacon1024.bean.User;

public class LoginService {

    public  User findUserByNameAndPassword(String name,String password) {
        String sql ="select * from t_user where name=? and password=?";
        System.out.println(sql);
        User u = null;
        try (
            Connection conn = DBUtil.getConnection();
            PreparedStatement stmt =conn.prepareStatement(sql)
            ){
            
            stmt.setString(1, name);
            stmt.setString(2, password);
            try(ResultSet rs = stmt.executeQuery()){
                while(rs.next()) {
                    u = new User();
                    u.setId(rs.getInt("id"));
                    u.setName(rs.getNString("name"));
                    u.setPassword(rs.getString("password"));
                    u.setEmail(rs.getString("email"));
                    u.setBirthdy(rs.getDate("birthday"));
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }       
        
        return u;           
    }

}