JDBC实现与解析

名词解释

1. JDBC（JAVA Data Base Connectivity）
2. ODBC（Open Data Base Connectivity）

模式

1. 下载jar包，加载JDBC驱动：常用的方式Class.forName(“xxx”)
2. 获取数据库连接：DriverManager.getConnection(String url, String useName,String password);
3. 获取Statement实例对象
   
   • Statement：执行静态SQL语句
   • PreparedStatement：执行动态SQL语句
   • CallableStatement：执行存储过程
4. 根据不同需求执行不同Statement方法
   
   • executeQuery(String sqlStr)：执行一次查询过程，返回查询的结果集——ResultSet
   • executeUpdate(String sqlStr)：执行INSERT , UPDATE , DELETE操作以及SQL DLL语句
   • execute(String sqlStr)：用于执行返回多个结果集、多个更新计数或二者组合的语句
5. 处理结果集
6. 释放资源：注意关闭的顺序。先关闭记录集，然后Statement声明，最后连接对象

---

实现DEMO

//1.User Entity:对应数据表
public class User {
    private String username;
    private String password;
    private int age;

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

    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;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
}

//2.mysql.properties:配置文件
password=root
url=jdbc\:mysql\://localhost\:3306/test_db
driver=com.mysql.jdbc.Driver
username=root

//3.PropertiesHelper:用于操作Properties文件
[PropertiesHelper](http://blog.csdn.net/zwwtj2014/article/details/49431049)

//4.Operation:核心操作类
public class Operation {
    private static String PATH = "config/mysql.properties";

    private static Connection getConn() {
        Map<String, String> map = PropertiesHelper.getProps(PATH);
        String driver = map.get("driver");
        String url = map.get("url");
        String username = map.get("username");
        String password = map.get("password");
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = (Connection) DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return conn;
    }

    public static int insert(User user,String sql) {
//      String sql = "insert into t_user (username,password,age) values (?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;
        try {
            conn = getConn();
            ps = (PreparedStatement) conn.prepareStatement(sql);
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setInt(3, user.getAge());
            count = ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }

        return count;
    }

    public static int delete(User user,String sql) {

//      String sql = "delete from t_user where username = '"
//              + user.getUsername() + "'";
        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;

        try {
            conn = getConn();
            ps = (PreparedStatement) conn.prepareStatement(sql);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return count;
    }

    public static void selectAll() {
        String sql = "select * from t_user";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = getConn();
            ps = (PreparedStatement) conn.prepareStatement(sql);
            rs = ps.executeQuery();
            ResultSetHelper.printRS(rs);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    public static int update(User user,String sql) {
//      String sql = "update t_user set age = '" + user.getAge()
//              + "' where username = '" + user.getUsername() + "'";

        Connection conn = null;
        PreparedStatement ps = null;
        int count = 0;

        try {
            conn = getConn();
            ps = (PreparedStatement) conn.prepareStatement(sql);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally{
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
        return count;
    }
}

原理探究

我们知道在操作数据库的时候肯定需要获取，所以我们从DriverManager.getConnection(url, username, password);这句话入手。在DriverManager中，getConnection方法有四个重载：

 1. public static Connection getConnection(String url)
 2. public static Connection getConnection(String url,String user, String password)
 3. public static Connection getConnection(String url,java.util.Properties info)
 4. private static Connection getConnection(String url, java.util.Properties info, ClassLoader callerCL) 

4 是私有的，当跨ClassLoader的时候可能使用到，所以我们主要看前3个代码。通过源码可以看到前面三个都调用了最后一个方法来实现的，所以这地方我们直接来看4的源码，前3个的源码可以追进去看看：

//  Worker method called by the public getConnection() methods.
    private static Connection getConnection(
        String url, java.util.Properties info, ClassLoader callerCL) throws SQLException {
        synchronized(DriverManager.class) {
          // synchronize loading of the correct classloader.
          if(callerCL == null) {
          //如果没有传加载器进来，用当前线程的
              callerCL = Thread.currentThread().getContextClassLoader();
           }
        }
        //url不能为空
        if(url == null) {
            throw new SQLException("The url cannot be null", "08001");
        }
        //向JDBC日志流写日志
        println("DriverManager.getConnection(\"" + url + "\")");

        // Walk through the loaded registeredDrivers attempting to make a connection.
        // Remember the first exception that gets raised so we can reraise it.
        SQLException reason = null;
        //遍历创建连接
        for(DriverInfo aDriver : registeredDrivers) {
            // If the caller does not have permission to load the driver then
            // skip it.
            if(isDriverAllowed(aDriver.driver, callerCL)) {
                try {
                    println("    trying " + aDriver.driver.getClass().getName());
                    //核心创建代码
                    Connection con = aDriver.driver.connect(url, info);
                    if (con != null) {
                        // Success!
                        println("getConnection returning " + aDriver.driver.getClass().getName());
                        return (con);
                    }
                } catch (SQLException ex) {
                    if (reason == null) {
                        reason = ex;
                    }
                }

            } else {
                println("    skipping: " + aDriver.getClass().getName());
            }

        }

        // if we got here nobody could connect.
        if (reason != null)    {
            println("getConnection failed: " + reason);
            throw reason;
        }

        println("getConnection: no suitable driver found for "+ url);
        throw new SQLException("No suitable driver found for "+ url, "08001");
    }
}

从上面的代码可以看出来，后台会遍历已经注册的驱动，然后进行创建连接，我们可以将这个过程打印到控制台上。

DriverManager.setLogWriter(new PrintWriter(System.out));

输出结果如下:
DriverManager.getConnection("jdbc:mysql://localhost:3306/test_db")
    trying sun.jdbc.odbc.JdbcOdbcDriver
*Driver.connect (jdbc:mysql://localhost:3306/test_db)
    trying com.mysql.jdbc.Driver
getConnection returning com.mysql.jdbc.Driver
==============================
2   djj 456 18  
3   zww1    123 22  
4   djj1    456 18  
==============================
done

接下来，我们来看看核心的创建代码：
Connection con = aDriver.driver.connect(url, info);

public java.sql.Connection connect(String url, Properties info) throws SQLException {
//url不能为空
        if (url == null) {
            throw SQLError.createSQLException(Messages.getString("NonRegisteringDriver.1"), SQLError.SQL_STATE_UNABLE_TO_CONNECT_TO_DATASOURCE, null);
        }
//集群下使用，这里不用管
        if (StringUtils.startsWithIgnoreCase(url, LOADBALANCE_URL_PREFIX)) {
            return connectLoadBalanced(url, info);
        } else if (StringUtils.startsWithIgnoreCase(url, REPLICATION_URL_PREFIX)) {
            return connectReplicationConnection(url, info);
        }
        Properties props = null;
        //parseURL用于解析url参数
        if ((props = parseURL(url, info)) == null) {
            return null;
        }

        if (!"1".equals(props.getProperty(NUM_HOSTS_PROPERTY_KEY))) {
            return connectFailover(url, info);
        }

        try {
        //根据解析的参数实例化connection对象
            Connection newConn = com.mysql.jdbc.ConnectionImpl.getInstance(host(props), port(props), props, database(props), url);

            return newConn;
        } catch (SQLException sqlEx) {
            // Don't wrap SQLExceptions, throw
            // them un-changed.
            throw sqlEx;
        } catch (Exception ex) {
            SQLException sqlEx = SQLError.createSQLException(
                    Messages.getString("NonRegisteringDriver.17") + ex.toString() + Messages.getString("NonRegisteringDriver.18"),
                    SQLError.SQL_STATE_UNABLE_TO_CONNECT_TO_DATASOURCE, null);

            sqlEx.initCause(ex);

            throw sqlEx;
        }
    }

因为parseURL代码太长了，这里就贴一些重要的，朋友们如果有兴趣，可以自己单步走一下：

 public Properties parseURL(String url, Properties defaults) throws java.sql.SQLException {
 Properties urlProps = (defaults != null) ? new Properties(defaults) : new Properties();
 //略...
 int beginningOfSlashes = url.indexOf("//");
 //解析url，将?后面的参数去掉
 int index = url.indexOf("?");
 if (index != -1) {//如果带有参数，则解析
            String paramString = url.substring(index + 1, url.length());
            url = url.substring(0, index);
            //类似split方法
            StringTokenizer queryParams = new StringTokenizer(paramString, "&");
            //因为实现了Enumeration接口，所以可以这样遍历
            while (queryParams.hasMoreTokens()) {
                String parameterValuePair = queryParams.nextToken();

                int indexOfEquals = StringUtils.indexOfIgnoreCase(0, parameterValuePair, "=");

                String parameter = null;
                String value = null;
                //解析key = val模式
                if (indexOfEquals != -1) {
                    parameter = parameterValuePair.substring(0, indexOfEquals);

                    if (indexOfEquals + 1 < parameterValuePair.length()) {
                        value = parameterValuePair.substring(indexOfEquals + 1);
                    }
                }

                if ((value != null && value.length() > 0) && (parameter != null && parameter.length() > 0)) {
                    try {
                       //将参数放在属性map中
                       urlProps.setProperty(parameter, URLDecoder.decode(value, "UTF-8"));
                    } catch (UnsupportedEncodingException badEncoding) {
                        // punt
                        urlProps.setProperty(parameter, URLDecoder.decode(value));
                    } catch (NoSuchMethodError nsme) {
                        // punt again
                        urlProps.setProperty(parameter, URLDecoder.decode(value));
                    }
                }
            }
        }
 }

经过parseURL后全局properties是

{HOST=localhost, user=root, HOST.1=localhost, password=root, DBNAME=test_db, PORT=3306, NUM_HOSTS=1, PORT.1=3306}

从这里可以看出来，后面只要根据key获取相应的value创建连接就行

上述总结

我们理一下：
我们通过上面的跟踪知道创建连接是通过connect方法，而且每次请求getConnection的时候都会调用connect方法创建出来一个连接对象，这也就是为什么要注意连接池的使用。

至于注册Class.forName。我们可以看下下面代码：

    static {
        try {
            java.sql.DriverManager.registerDriver(new Driver());
        } catch (SQLException E) {
            throw new RuntimeException("Can't register driver!");
        }
    }

可以看到这是一个Driver类中的静态块保证该驱动只被注册一次，而forName也只是为了触发这个静态块。再跟踪registerDriver

 public static synchronized void registerDriver(java.sql.Driver driver)
        throws SQLException {

        /* Register the driver if it has not already been added to our list */
        if(driver != null) {
            registeredDrivers.addIfAbsent(new DriverInfo(driver));
        } else {
            // This is for compatibility with the original DriverManager
            throw new NullPointerException();
        }

        println("registerDriver: " + driver);

    }

这里是一个CopyOnWriteArrayList数组，保证线程安全，以后会讲。

完结