java的jdbc，连接池的工具类，druid连接池的使用

配置文件

jdbc.username=root
jdbc.password=123
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/bigdata

java的jdbc

工具类
从配置文件中获取连接，返回连接对象

package jdbcutiis.jdbcutilsAndTest;

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

/**
 * Created by IBM on 2019/12/30.
 */
public class Utils {
    private static String USERNAME;
    private static String PASSWORD;
    private static String DRIVER;
    private static String URL;
    private static Connection connection;
    public static Connection getConnect(){
//        获取配置文件信息
        final InputStream ras = Utils.class.getClassLoader().getResourceAsStream("application.properties");
//        设计配置对象
        final Properties prop = new Properties();
        try {
//            加载配置
            prop.load(ras);
            USERNAME = prop.getProperty("jdbc.username");
            PASSWORD = prop.getProperty("jdbc.password");
            DRIVER = prop.getProperty("jdbc.driver");
            URL = prop.getProperty("jdbc.url");
//            新建立连接
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

        } catch (Exception e) {
            throw new RuntimeException("读取数据库配置文件异常！");
        }


        return connection;
    }
    public static void closeConnection(){
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

测试代码：
拿到连接，执行查询操作，并输出

package jdbcutiis.jdbcutilsAndTest;

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

/**
 * Created by IBM on 2019/12/30.
 */
public class UseJdbc {
    public static void main(String[] args) throws SQLException {
        final Connection connect = Utils.getConnect();
        final Statement statement = connect.createStatement();
        final ResultSet resultSet = statement.executeQuery("SELECT * FROM wb");
        while (resultSet.next()){
            System.out.println(resultSet.getString(1));
        }
        connect.close();
    }
}

连接池的工具类

写一个连接池

package com.jnshu.datasource;

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

public class MyDataSource {
    //LinkedList集合，存储连接的容器---连接池
    private LinkedList<Connection> dataSources = new LinkedList();
    //初始化连接数量
    public MyDataSource() {
        //一次性创建10个连接
        for(int i = 0; i < 10; i++) {
            try {
                //1、装载驱动对象
                Class.forName("com.mysql.jdbc.Driver");
                //2、通过JDBC建立数据库连接
                Connection con =DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/practice?useSSL=false", "root", "123456");
                //3、将连接加入连接池中
                dataSources.add(con);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public Connection getConnection() throws SQLException {
        //取出连接池中一个连接
        final Connection conn = dataSources.removeFirst(); // 获取第一个连接给Connection，把剩下的连接返回给LinkedList集合
        System.out.println("执行数据库的操作时，连接池内剩余连接个数： "+dataSources.size());//输出集合里面剩下的连接的个数
        return conn;
    }

    public void releaseConnection(Connection conn) {
        //把连接返还给连接池
        dataSources.add(conn);
        System.out.println("数据库操作完成时，连接池剩余连接个数："+dataSources.size());//输出集合里面剩下的连接的个数
    }
}

拿出来使用

package com.jnshu.datasource;

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

public class DataSourceDemo {
        public static void main(String[] args) throws Exception{
            //1、使用连接池建立数据库连接
            MyDataSource myDataSource = new MyDataSource();
            Connection conn =myDataSource.getConnection();
            //2、创建状态
            Statement state =conn.createStatement();
            //3、查询数据库并返回结果
            ResultSet result =state.executeQuery("select * from user where id=5");
            //4、输出查询结果
            while(result.next()){
                System.out.println(result.getString("name"));
            }
            //5、断开数据库连接
            result.close();
            state.close();
//        Connection connection=myDataSource.getConnection();
            //6、归还数据库连接给连接池
            myDataSource.releaseConnection(conn);
        }
    }

druid连接池的使用

配置文件

driverName = com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/bigdata
username = root
password = 123

工具类

package jdbcutiis.usejdbcpool;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
 * Druid连接池工具类
 * */
public class JdbcUtils {
    // 1 定义成员变量   DataSource
    private static DataSource ds;
    static{
        // 2 加载配置文件   获得连接池
        Properties p = new Properties();
        try {
            p.load(JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 3 获得链接
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    // 4 释放资源
    public static void close(Statement stat,Connection conn){
        if(stat!=null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void close(ResultSet rs,Statement stat,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stat!=null){
            try {
                stat.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    // 5 获得连接池
    public static DataSource getDataSource(){
        return ds;
    }
}

测试代码：

package jdbcutiis.usejdbcpool;

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

/**
 * Created by IBM on 2019/12/30.
 */
public class UseDruid {
    public static void main(String[] args) throws SQLException {
         Connection connection = JdbcUtils.getConnection();
         Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery("SELECT * FROM wb");
         while (resultSet.next()){
             System.out.println(resultSet.getString(1));
         }

         JdbcUtils.close(statement,connection);

    }
}