数据库连接池初使用

一、数据库连接池简介

1.数据库连接池是一个容器，用来分配和管理数据库的连接，它允许多个应用程序重复使用同一批现有的数据库连接，而不是重新创建新的连接。

2.若一个数据库连接的空闲时间超出最大空闲时间，则强制释放此连接。

连接池就是一开始创建一些连接对象并存储起来，当用户需要连接数据库时，用户只需从连接池中获取一个连接进行使用，而不必自己创建连接，使用完毕后将连接对象归还给连接池即可。

优点：资源重用；节省创建和销毁连接所花费的时间，提升系统响应速度；避免数据库连接遗漏。

二、数据库连接池的实现（Druid）

1.导入Druid的jar包

下载jar包：

链接：https://pan.baidu.com/s/1fRELYGU0zb1H87r7Xw9sig?pwd=kapt 
提取码：kapt

将jar包导入项目的lib文件夹下并添加为库文件

2.定义配置文件

将druid.properties文件粘贴到src目录下，并根据自己的实际情况修改配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false
username=root
password=123456
#初始化连接数量
initialSize=5
#最大连接数
maxActive=10
#最大等待时间
maxWait=3000

3.Java代码实现

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.util.Properties;

/*
    Druid的使用
 */
public class Test5 {
    public static void main(String[] args) throws Exception{
        //加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
    }
}

运行结果如下

 三、数据库连接池的使用案例

1.创建使用的学生表students

 2.创建学生类Student

import java.sql.Date;

public class Student {
    private int sid;
    private String sname;
    private String smajor;
    private int sclass;
    private String sgender;
    private Date sbirthday;
    private String school;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public String getSmajor() {
        return smajor;
    }

    public void setSmajor(String smajor) {
        this.smajor = smajor;
    }

    public int getSclass() {
        return sclass;
    }

    public void setSclass(int sclass) {
        this.sclass = sclass;
    }

    public String getSgender() {
        return sgender;
    }

    public void setSgender(String sgender) {
        this.sgender = sgender;
    }

    public Date getSbirthday() {
        return sbirthday;
    }

    public void setSbirthday(Date sbirthday) {
        this.sbirthday = sbirthday;
    }

    public String getSchool() {
        return school;
    }

    public void setSchool(String school) {
        this.school = school;
    }

    @Override
    public String toString() {
        return "Student{" +
                "sid=" + sid +
                ", sname='" + sname + '\'' +
                ", smajor='" + smajor + '\'' +
                ", sclass=" + sclass +
                ", sgender='" + sgender + '\'' +
                ", sbirthday=" + sbirthday +
                ", school='" + school + '\'' +
                '}';
    }
}

 3.实现数据库的增删改查

3.1 查询所有

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class SelectALL {
    public static void main(String[] args) throws Exception {
        //1.获取Connection对象
        //加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection = dataSource.getConnection();
        //2.定义sql
        String sql = "select * from students where school = ?;";
        //3.获取PreparedStatement对象
        PreparedStatement pstmt = connection.prepareStatement(sql);
        //4.设置参数
        String sschool = "材料学院";
        pstmt.setString(1, sschool);
        //5.执行sql
        ResultSet resultSet = pstmt.executeQuery();
        //6.处理结果
        List<Student> list = new ArrayList<Student>();
        while (resultSet.next()) {
            Student student = new Student();
            //获取数据
            int sid = resultSet.getInt("sid");
            String sname = resultSet.getString("sname");
            String smajor = resultSet.getString("smajor");
            int sclass = resultSet.getInt("sclass");
            String sgender = resultSet.getString("sgender");
            Date sbirthday = resultSet.getDate("sbirthday");
            String school = resultSet.getString("school");
            //给student赋值
            student.setSid(sid);
            student.setSname(sname);
            student.setSmajor(smajor);
            student.setSclass(sclass);
            student.setSgender(sgender);
            student.setSbirthday(sbirthday);
            student.setSchool(school);
            //存入集合
            list.add(student);
        }
        System.out.println(list);
        //7.释放资源
        resultSet.close();
        pstmt.close();
        connection.close();
    }
}

查询结果如下

 3.2 修改数据

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;

public class Update {
    public static void main(String[] args) throws Exception{
        //修改的内容
        String gender="女";
        //1.获取Connection对象
        //加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection = dataSource.getConnection();
        //2.定义sql
        String sql="update students set sgender = ? where sid = ?";
        //3.获取PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4.设置参数
        preparedStatement.setString(1,gender);
        preparedStatement.setInt(2,102299201);
        //5.执行sql
        int i = preparedStatement.executeUpdate();
        //6.返回处理结果
        System.out.println(i);
        //7.释放资源
        preparedStatement.close();
        connection.close();
    }
}

执行结果：

修改后的students表如下，可以看到灰原哀的性别已被修改为女

 3.3 删除数据

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;

public class Delete {
    public static void main(String[] args) throws Exception{
        //删除参数
        int id = 102299205;
        //1.获取Connection对象
        //加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection = dataSource.getConnection();
        //2.定义sql
        String sql="delete from scores where sid = ?";
        //3.获取PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4.设置参数
        preparedStatement.setInt(1,id);
        //5.执行sql
        int i = preparedStatement.executeUpdate();
        //6.处理结果
        System.out.println(i);
        //7.释放资源
        preparedStatement.close();
        connection.close();
    }
}

执行结果如下图

执行前的scores表如图 

执行后的scores表如图，可以看到学号为205的学生信息已被删除

 

3.4 添加数据

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Properties;

public class Add {
    public static void main(String[] args) throws Exception{
        //待添加参数
        int id=102299201;
        int id2=102;
        int sscore=100;
        //1.获取Connection对象
        //加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //获取数据库连接
        Connection connection = dataSource.getConnection();
        //2.定义sql
        String sql="insert into scores(sid,cid,score) values(?,?,?)";
        //3.获取PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //4.设置参数
        preparedStatement.setInt(1,id);
        preparedStatement.setInt(2,id2);
        preparedStatement.setInt(3,sscore);
        //5.执行sql
        int i = preparedStatement.executeUpdate();
        //6.处理结果
        System.out.println(i);
        //7.释放资源
        preparedStatement.close();
        connection.close();
    }
}

 执行结果如图

 执行后的scores表如图，sid为201，cid为102的成绩已被成功加入