MySql学习笔记-详细整理-下 - ⎝⎛CodingNote.cc ⎞⎠

MySql学习笔记–详细整理–下

2020 年 11 月 27 日
笔记
MySQL

索引
- 测试索引
- 索引原则
权限管理和备份
- 备份
规范数据库设计
- 三大范式
JDBC
数据库连接池
- DBCP
- C3P0

索引

MySQL官方对索引的定义为：索引(index)是帮助MySQL高效获取数据的的数据结构，提取句子主干，就可以得到作用的本质，索引是数据结构。

索引的分类

主键索引：PRIMARY KEY
- 唯一的标识，主键不可重复，只能由一个列作为索引
唯一索引：UNIQUE KEY
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引：KEY/INDEX
- 默认的，index，key关键字设置
全文索引：FullText
- 在特定的数据库引擎下才有
- 快速定位数据

基础语法

--索引的使用
--在创建表的时候给字段增加索引 或创建完表后增加
USE school
SHOW INDEX FROM student1

--增加索引
ALTER TABLE `student1` ADD FULLTEXT INDEX `studentname`(`studentname`)


--EXPLAIN 分析sql执行的状况
 EXPLAIN SELECT * FROM `student1` --非全文索引 

EXPLAIN SELECT * FROM student1 WHERE MATCH(studentname) AGAINST('韩')

测试索引

--测试索引
CREATE TABLE `app_user`(
		`id` INT(4) UNSIGNED NOT NULL AUTO_INCREMENT,
		`name` VARCHAR(20) DEFAULT'' COMMENT'用户姓名',
		`email` VARCHAR(20) NOT NULL COMMENT'用户邮箱',
		`phone` VARCHAR(20) DEFAULT'' COMMENT'用户电话',
		`gender` TINYINT(4) UNSIGNED DEFAULT'0' COMMENT'性别(1是男，2是女)',
		`password` VARCHAR(50) NOT NULL COMMENT '密码',
		`age` INT(3) DEFAULT'0' COMMENT'年龄',
		`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
		`update_name` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY(`id`)
)ENGINE INNODB DEFAULT CHARSET=utf8mb4 COMMENT'app用户表'

--插入100万条数据
--写函数之前必须写 标志
DELIMITER $$ 
CREATE FUNCTION mock_dat() #创建一个函数
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(name,email,phone,gender,password,age)
		VALUES(CONCAT('用户',i),'[email protected]',CONCAT('18',FLOOR(RAND()*((999999999-1000000000)+1000000000))),
		FLOOR((RAND()*2)+1),UUID(),FLOOR(RAND()*100));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;
--
SELECT mock_dat();

SELECT * FROM app_user WHERE name = '用户9999';
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999';
EXPLAIN SELECT * FROM app_user WHERE name = '用户1';

--创建索引 id_表名_字段名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE name = '用户9999';
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999';

索引在小数据的时候用处不大，在大数据的时候，区别十分明显。

索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree：innodb默认的数据结构

权限管理和备份

点击用户进去可以选择新增用户，在服务器权限中可以给用户设置不同的权限。

也可以使用SQL赋予每个用户权限

--创建用户
CREATE USER zhou IDENTIFIED BY '123456'

--修改当前用户密码   
SET PASSWORD = PASSWORD('111111')

--修改指定用户密码
SET PASSWORD FOR zhou = password('111111')

--重命名
RENAME USER zhou TO zzzz

--用户授权  授予全部的权限
--ALL PRIVILEGES除了给别人授权都可以
GRANT ALL PRIVILEGES ON *.* TO zzzz

--查看指定用户权限
SHOW GRANTS FOR zzzz
--查看管理员权限
SHOW GRANTS FOR root@localhost

--root用户权限  GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

--撤销权限
REVOKE ALL PRIVILEGES *.* FROM zzzz

--删除用户
DROP zzzz

备份

为什么备份：

保证重要的数据不丢失
数据转移

MySQL数据库备份的方式

直接拷贝物理文件
在可视化工具中手动导出数据库
- 在想要导出的表或者库中，右键选择转储sql文件

使用命令行导出 mysqldump 命令行使用

--mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student1 > D:/a.sql

--mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student1 subject > D:/b.sql

--mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school > D:/c.sql

--导入表
--登录
mysql -uroot -p123456
use school;
sourse d/a.sql

或
mysql -u用户名 -p密码 库名 表名<文件路径

规范数据库设计

当数据库比较复杂的时候，需要按规范设计。

糟糕的数据库设计：

数据冗余，浪费空间
数据插入和删除都麻烦【阿里巴巴开发手册屏蔽使用外键】
系统的性能差

良好的数据库设计：

节省内存空间
保证数据库的完整性
性能好，便于开发

软件开发中关于数据库的设计

分析需求：分析业务和需要处理的数据库的需求
概要设计：设计关系图E-R图

设计数据库的步骤：（个人博客）

收集信息，分析需求
- 用户表（用户登录注册，个人信息，写博客，创建分类）
- 分类表（文章分类，博客作者）
- 文章表（文章信息）
- 友链表（友链信息）
- 评论表（评论信息）
- 自定义表（可选）（存储其他信息）
标识实体（把需求写到字段）
标识实体之间的关系
- 写博客：user–>blog
- 创建分类：user–>category
- 友链：link
- …

三大范式

第一范式（1NF）：原子性，要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）：满足第一范式，非码属性必须完全依赖于候选码，确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关。即每张表只描述一件事情。

第三范式（3NF）：满足第一第二范式，任何非主属性不依赖于其它非主属性（即消除传递依赖），第三范式需要确保数据表中每一列数据都与主键直接相关，而不能间接相关。

规范性和性能问题：（关联查询的表不得超过三张表阿里规约）

考虑商业化需求和目标（成本，用户体验），数据库的性能更加重要
在规范性能需求的时候，需要适当考虑一下规范性
在有些情况下，故意给表增加一些冗余字段（多表查询变为单表查询），提高效率
故意增加一些计算列（从大数据量降为小数据量的一些查询）

JDBC

数据库驱动：应用程序和数据库之间需要有驱动来进行连接。

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库）规范，俗称JDBC。

这些规范的具体实现由数据库厂商去做。

对于开发人员来说只需掌握JDBC接口的操作即可。

java.sql javac.sql

JDBC程序

创建lib目录，在lib下放入mysql-connector-java-5.1.47.jar，右击lib，add as library。

加载驱动
连接数据库 DriverManager
获得执行sql的对象 statement
获得返回的结果值
释放连接

package com.zr.lesson01;

import java.sql.*;

public class JdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //用户信息和Url
        String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";

        //连接成功 数据库对象 DriverManager驱动管理
        Connection connection = DriverManager.getConnection(url, username, password);
        //执行sql的对象
        Statement statement = connection.createStatement();
        //执行sql
        String sql = "select * from users";
        ResultSet resultSet = statement.executeQuery(sql); //返回的结果集
        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("pwd="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("======================================");
        }
        //释放连接 耗资源 用完就关掉
        resultSet.close();
        statement.close();
        connection.close();
    }
}

//MySQL默认端口号3306

//Oracle ---1521
//jdbc:oracle:thin:@localhost:1521:sid

connection.commit(); //事务提交
connection.rollback(); //事务回滚
connection.setAutoCommit(true); //开启事务提交

statement.executeQuery(); //查询操作返回 resultset
statement.executeUpdate(); //更新，插入，删除都用这个，返回一个受影响的行数
statement.execute(); //执行任何sql

resultset 查询的结果集，封装了所有的查询结果集
    resultSet.getObject();//在不知道列类型的情况下使用
        //知道列的指定类型
        resultSet.getString();
        resultSet.getInt();
        resultSet.getDate();

		resultSet.beforeFirst();//移动到最前面
        resultSet.afterLast();//移动到最后面
        resultSet.next();//移动到下一个
		resultSet.previous();//移动到前一个
        resultSet.absolute(row);//移动到指定行

Statement对象

jdbc中的statement对象用于向数据库发送sql语句，想完成对数据库的增删改查只需发送相应的sql语句即可。

executeUpdate执行完后，返回一个整数（即sql语句导致几行数据发生了变化）。

executeQuery执行完后，返回代表查询结果的resultset对象。

CRUD操作：create，使用Statement(sql)方法完成对数据库的添加操作，示例如下：

Statement st = connection.createStatement;
String sql = "insert into user(...) values(...) "
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功！")
}

CRUD操作：delete，使用Statement(sql)方法完成对数据库的删除操作，示例如下：

Statement st = connection.createStatement;
String sql = "delete from user wherte id=1"
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功！")
}

CRUD操作：update，使用Statement(sql)方法完成对数据库的修改操作，示例如下：

statement st = connection.Statement;
String sql = "update user set name=`` where id=1"
int num = st.executeUpdate;
if(num>0){
    System.out.println("修改成功！");
}

CRUD操作：read，使用Statement(sql)方法完成对数据库的查询操作，示例如下：

Statement st = connection.createStatement;
String sql = "select * from user "
Resultset rs = st.executeQuery(sql);
if(rs.next()){
    //根据获取的数据类型，分别调用rs的相应方法映射到Java对象中
}

工具类实现

先创建一个jdbcStudy数据库，再创建一个users用户表，表中的字段为id,name,password,emain,birthday

配置MySQL数据库文件，创建工具类

配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useunicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

工具类

package com.zr.lesson02.utils;
//工具类
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            //读取资源
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }


    //释放连接
    public static void release(Connection con, Statement st, ResultSet rs) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if (con!=null){
            con.commit();
        }
    }
}

增加数据

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//插入数据
public class TestInsert {
    public static void main(String[] args) throws Exception {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection(); //获取数据库连接
            st = con.createStatement();//创建执行sql的对象
            String sql = "insert into users values(5,'赵云','111111','[email protected]','2020-01-01')";
            int i = st.executeUpdate(sql);//执行sql
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);

        }
    }
}

删除数据

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//删除数据
public class TestDelete {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();//获取数据库连接
            st = con.createStatement();//创建执行sql的对象
            String sql = "delete from users where id=5";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除创建!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

修改数据

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//修改数据
public class TestUpdate {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();//获取数据库连接
            st = con.createStatement();//创建执行sql的对象
            String sql = "update users set name='韩信' where id=4";
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

添加数据

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//查询数据
public class TestSelect {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();//获取数据库连接
            st = con.createStatement();//创建执行sql的对象
            String sql = "select * from users";
            rs = st.executeQuery(sql);
            //if只能查出一条数据  这里查询所有用户信息使用while
            while (rs.next()){
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
                System.out.println("password="+rs.getString("password"));
                System.out.println("email="+rs.getString("email"));
                System.out.println("birthday="+rs.getDate("birthday"));
                System.out.println("==========================");
            }
            System.out.println("查询成功!");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

sql注入

sql存在漏洞，会被攻击导致数据泄露，sql会被拼接。

package com.zr.lesson02.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//sql注入
public class SqlInjection {
    public static void main(String[] args) throws SQLException {
        //正常登录 数据库中有的用户和密码
        //login("韩信","111111");

        //sql注入 会输出所有密码为123456的人的信息
        login("'or'1=1","123456");
        
        //sql注入  会查询所有信息
        //login("'or'1=1","'or='1=1");
    }
    //登录业务
    public static void login(String username,String password) throws SQLException {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();//获取数据库连接
            st = con.createStatement();//创建执行sql的对象
            //select * from users where name=''or'1=1' and password='123456'
            //select * from users where name=''or'1=1' and password=''or'1=1'
            String sql = "select * from users where name='"+username+"' and password='"+password+"'";
            rs = st.executeQuery(sql);
            //if只能查出一条数据  这里查询所有用户信息使用while
            while (rs.next()){
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
                System.out.println("password="+rs.getString("password"));
                System.out.println("email="+rs.getString("email"));
                System.out.println("birthday="+rs.getDate("birthday"));
                System.out.println("==========================");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

PreparedStatement对象

PreparedStatement可以防止sql注入，而且效率更高。实例如下：

增加数据：

package com.zr.lesson03;

import com.zr.lesson02.JdbcUtils;
import java.util.Date;
import java.sql.*;

//PreparedStatement 插入
public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //区别
            String sql = "insert into users values(?,?,?,?,?)";//使用？占位符 代替参数
            ps = con.prepareStatement(sql);//预编译  先写sql 然后不执行

            //手动给参数赋值
            ps.setInt(1,5);
            ps.setString(2,"李白");
            ps.setString(3,"123333");
            ps.setString(4,"[email protected]");
            //sql.Date 数据库    java.sql.date()
            //util.Date Java    new date().gettime()  获得时间戳
            ps.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,ps,rs);
        }
    }
}

删除数据：

package com.zr.lesson03;

import com.zr.lesson02.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

//PreparedStatement 删除
public class TestDelete {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //区别
            String sql = "delete from users where id=?";//使用？占位符 代替参数
            ps = con.prepareStatement(sql);//预编译  先写sql 然后不执行

            //手动给参数赋值
            ps.setInt(1,5);

            //执行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,ps,rs);
        }
    }
}

修改数据：

package com.zr.lesson03;

import com.zr.lesson02.JdbcUtils;

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

//PreparedStatement 修改
public class TestUpdate {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //区别
            String sql = "update users set name = ? where id = ?;";//使用？占位符 代替参数
            ps = con.prepareStatement(sql);//预编译  先写sql 然后不执行

            //手动给参数赋值
            ps.setString(1,"关羽");
            ps.setInt(2,3);

            //执行
            int i = ps.executeUpdate();
            if (i>0){
                System.out.println("修改成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,ps,rs);
        }
    }
}

查询数据：

package com.zr.lesson03;

import com.zr.lesson02.JdbcUtils;

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

//查询
public class TestSelect {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            String sql = "select * from users where id=?";
            ps = con.prepareStatement(sql);
            ps.setInt(1,3);
            rs = ps.executeQuery();
            if (rs.next()){
                System.out.println("name="+rs.getString("name"));
                System.out.println("password="+rs.getString("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(con,ps,rs);
        }
    }
}

事务

要么都成功，要么都失败。

ACID原则：原子性（要么都成功，要么都失败），一致性（保持最终结果的一致性），持久性（一旦提交不可逆，持久到数据库），隔离性（多个进程互不干扰）。

隔离性的问题：

脏读：一个事务读取了另一个没有提交的事务
虚读（幻读）：在一个事务内读取到了别人插入的数据，导致前后读出来的数据不一致
不可重复读：在同一个事务内，重复读取了表中的数据，表数据发生了改变

package com.zr.lesson04;

import com.zr.lesson02.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//模拟转账成功
public class TestTransaction {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //关闭数据库的自动提交功能,自动开启事务
            con.setAutoCommit(false);
            //执行业务
            String sql1 ="update account set money  = money-100  where name = 'libai'";
            st = con.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 ="update account set money  = money+100  where name = 'hanxin'";
            st = con.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕 提交事务
            con.commit();
            System.out.println("成功！");
        } catch (SQLException throwables) {
            try {
                con.rollback(); //失败就回滚事务
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
           JdbcUtils.release(con,st,rs);
        }
    }
}

模拟转账失败

package com.zr.lesson04;

import com.zr.lesson02.JdbcUtils;

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

//模拟转账失败
public class TestTransaction02 {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();
            //关闭数据库的自动提交功能,自动开启事务
            con.setAutoCommit(false);
            //执行业务
            String sql1 ="update account set money  = money-100  where name = 'libai'";
            st = con.prepareStatement(sql1);
            st.executeUpdate();

            int x=1/0;//报错

            String sql2 ="update account set money  = money+100  where name = 'hanxin'";
            st = con.prepareStatement(sql2);
            st.executeUpdate();

            //业务执行完毕 提交事务
            con.commit();
            System.out.println("成功！");
        } catch (Exception e) {
            //默认会自动回滚 也可以显式定义
           /* try {
                con.rollback(); //失败就回滚事务
            } catch (SQLException e2) {
                e2.printStackTrace();
            }*/
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

数据库连接池

数据库连接—执行完毕—释放连接释放是十分浪费系统资源的。

池化技术：准备一些预先的资源，过来连接已经准备好的。

编写连接池，实现一个接口DataSource。

开源数据源实现：

DBCP
C3P0
Durid(阿里巴巴)

DBCP

使用这些数据库连接池之后，我们在项目开发中就不需要编写数据库连接的代码了。

DBCP：lib下导入 commens-dbcp-1.4.jar和commens-pool-1.6.jar

创建dbconfig.properties文件

#连接设置
driverClassname=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useunicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#初始化连接
initialSize=10

#最大连接数量
maxActive=50

#最大空闲连接
maxIdle=20

#超时等待时间 毫秒
maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=utf8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=READ_UNCOMMITTED

创建DBCP工具类

package com.zr.lesson05;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils_DBCP {
    private static DataSource dataSource = null;
    static{
        try{
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            //创建数据源 工厂模式
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //从数据源中获取连接
    }
    public static void release(Connection con, Statement st, ResultSet rs){
        if (con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (rs!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试插入数据

package com.zr.lesson05;

import com.zr.lesson02.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestDBCP {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils_DBCP.getConnection();
            //区别
            String sql = "insert into users values(?,?,?,?,?)";//使用？占位符 代替参数
            ps = con.prepareStatement(sql);//预编译  先写sql 然后不执行

            //手动给参数赋值
            ps.setInt(1, 5);
            ps.setString(2, "李白");
            ps.setString(3, "123333");
            ps.setString(4, "[email protected]");
            //sql.Date 数据库    java.sql.date()
            //util.Date Java    new date().gettime()  获得时间戳
            ps.setDate(5, new java.sql.Date(new Date().getTime()));

            //执行
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(con, ps, rs);
        }
    }
}

C3P0

C3P0：lib下导入c3p0-0.9.5.5.jar和mchange-commens-java-0.2.19.jar

先创建c3p0-config.xml文件，再创建JdbcUtils_C3P0工具类

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!-- 默认配置，如果没有指定则使用这个配置 -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquirIncrement">5</property>>
        <property name="initialPoolSize">10</property>
        <property name="maxPoolSize">20</property>
        <property name="minPoolSize">5</property>
    </default-config>
    <!-- 命名的配置,可以通过方法调用实现 -->
    <named-config name="Mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <!-- 如果池中数据连接不够时一次增长多少个 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">10</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">25</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
    </named-config>
</c3p0-config>

创建C3P0工具类

package com.zr.lesson05;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils_C3P0 {
    private static ComboPooledDataSource dataSource = null;
    static{
        try{
            /*//代码配置
            dataSource = new ComboPooledDataSource();
            dataSource.setDriverClass();
            dataSource.setUser();
            dataSource.setPassword();
            dataSource.setJdbcUrl();

            dataSource.setMaxPoolSize();
            dataSource.setMinPoolSize();*/

            dataSource = new ComboPooledDataSource("Mysql");//配置文件写法


            //创建数据源 工厂模式

        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); //从数据源中获取连接
    }
    public static void release(Connection con, Statement st, ResultSet rs){
        if (con!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (rs!=null){
            try {
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

测试插入代码

package com.zr.lesson05;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestC3P0 {
    public static void main(String[] args) throws SQLException {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils_C3P0.getConnection();
            //区别
            String sql = "insert into users values(?,?,?,?,?)";//使用？占位符 代替参数
            ps = con.prepareStatement(sql);//预编译  先写sql 然后不执行

            //手动给参数赋值
            ps.setInt(1, 6);
            ps.setString(2, "李白");
            ps.setString(3, "123333");
            ps.setString(4, "[email protected]");
            //sql.Date 数据库    java.sql.date()
            //util.Date Java    new date().gettime()  获得时间戳
            ps.setDate(5, new java.sql.Date(new Date().getTime()));

            //执行
            int i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_C3P0.release(con, ps, rs);
        }
    }
}

结论：无论使用什么数据源，本质还是一样的，DataSource不变，方法就不会变。

Tags: MySQL