jdbc连接回顾

不使用任何工具类手动连接

package com.oracle.jdbc;

import java.sql.*;

/**
 *jdbc查询 jdbc数据库下，user表中所有数据并打印在控制台
 * jdbc操作数据库步骤
 *  1注册驱动
 *  2创建数据库连接对象
 *  3获取传输器对象
 *  4执行sql
 *  5处理结果集
 *  6释放资源
 */

public class JDBCDemo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String root = "root";
        String password = "root";
       // String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8";
        String url = "jdbc:mysql://localhost:3306/jdbc";
        Connection connection = DriverManager.getConnection(url, root, password);
        System.out.println(connection);


        Statement statement = connection.createStatement();

        String sql = "select * from user";

        ResultSet resultSet = statement.executeQuery(sql);

        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String gender = resultSet.getString("gender");
            int age = resultSet.getInt("age");
            String address = resultSet.getString("address");

            System.out.println(id +"," +name +","+gender+","+age+","+address );
        }

        resultSet.close();
        statement.close();
        connection.close();
    }
}

初步封装一下，变成工具类

package com.march.mysql0314;

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

public class DBUtil {
    private static final String URL = "jdbc:mysql://localhost:3306/empdb?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
    private static final String U = "com.mysql.cj.jdbc.Driver";
    private static final String USERNAME = "root";
    private static final String PASSWORLD = "root";
    private static Connection connection = null;
    private DBUtil(){};

    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        if (connection == null || connection.isClosed()){
            Class.forName(U);
             connection = DriverManager.getConnection(URL, USERNAME, PASSWORLD);
        }
        return connection;
    }

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


}

使用属性集文件连接，方便改写用户登录的数据

package com.march.mysql0314;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtilPro {
    private static Connection connection = null;
    private DBUtilPro(){};
    private static Properties properties = new Properties();
    static{
        try(InputStream inputStream = DBUtilPro.class.getResourceAsStream("/mysql.properties");)
        {
            properties.load(inputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        if (connection == null || connection.isClosed()){
            Class.forName(properties.getProperty("Driver"));
             connection = DriverManager.getConnection(properties.getProperty("URL"),
                    properties.getProperty("USERNAME"),
                    properties.getProperty("PASSWORLD"));
        }
        return connection;
    }

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

使用线程集合ThreadLocal实现同一事务的同一连接

package com.march.mysql0314;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtilProMax {
    private DBUtilProMax(){};
    private static Properties properties = new Properties();
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();
    static{
        try(InputStream inputStream = DBUtil.class.getResourceAsStream("/mysql.properties");)
        {
            properties.load(inputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        Connection connection = threadLocal.get();
        if (connection == null || connection.isClosed()){
            Class.forName(properties.getProperty("Driver"));
            connection = DriverManager.getConnection(properties.getProperty("URL"),
                    properties.getProperty("USERNAME"),
                    properties.getProperty("PASSWORLD"));
            threadLocal.set(connection);
        }
        return connection;
    }

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

使用德鲁伊连接池

package com.march.mysql0314;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DButilFinally {
        // 私有的构造方法
        private DButilFinally(){}
        //---1定义静态变量 : Properties
        private  static Properties pro =new Properties();
        //========================> 1 创建DataSource对象
        private  static DataSource ds = null;
        //---2使用静态代码块,读取配置文件
        static{
            try(
                    //==>2 获取流, 文件存储在src目录下
                    InputStream in = DBUtil.class.getResourceAsStream("/druid.properties");
            ){
                //把IO中的属性集加载到properties对象中
                pro.load(in);
                //==================>获取数据源对象
                ds = DruidDataSourceFactory.createDataSource(pro);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        // ---- 使用ThreadLocal存储连接对象
        //1 定义ThreadLocal
        private  static ThreadLocal<Connection> local = new ThreadLocal<>();
        /**
         * 获取连接的方法
         * @return
         * @throws SQLException
         * @throws ClassNotFoundException
         */
        public  static Connection getConn() throws SQLException, ClassNotFoundException {
            // --2 从ThreadLocal中获取连接对象
            Connection conn = local.get();
            if(conn ==null  || conn.isClosed()){
                //=============================>通过DataSource获取连接对象
                conn = ds.getConnection();
                // ---3 向ThreadLocal中存储连接对象
                local.set(conn);;
            }
            return  conn;
        }

        /**
         * 关闭连接的方法
         */
        public static   void closeConn(){
            //  // --2 从ThreadLocal中获取连接对象
            Connection conn = local.get();
            try {
                if(conn!=null  && !conn.isClosed()){  //conn不是null ,同时也没有关闭
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                // -- 删除ThreadLocal中存储的数据
                local.remove();
            }
        }

}