JDBC

一、数据库驱动

这里的驱动的概念和平时听到的那种驱动的概念是一样的，比如平时购买的声卡，网卡直接插到计算机上面是不能用的，必须要安装相应的驱动程序之后才能够使用声卡和网卡，同样道理，我们安装好数据库之后，我们的应用程序也是不能直接使用数据库的，必须要通过相应的数据库驱动程序，通过驱动程序去和数据库打交道，如下所示：

二、JDBC介绍

SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范（接口），称之为JDBC。这套接口由数据库厂商去实现，这样，开发人员只需要学习jdbc接口，并通过jdbc加载具体的驱动，就可以操作数据库。

JDBC全称为：Java Data Base Connectivity（java数据库连接），它主要由接口组成。
组成JDBC的２个包：java.sql、javax.sql
开发JDBC应用需要以上2个包的支持外，还需要导入相应JDBC的数据库实现(即数据库驱动包—mysql-connector-java-5.1.47.jar)。

三、第一个JDBC程序

搭建实验环境

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');

新建一个Java工程，并导入数据驱动。

jar包下载地址：maven仓库。

编写程序从user表中读取数据，并打印在命令行窗口中。

//第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws Exception{
        // 1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); // mysql8.0固定写法，加载驱动

        // 2.用户信息和url
        // uesUnicode=true 支持中文编码
        // characterEncoding=utf8 设定字符集
        // useSSL=true 使用安全的连接
        String url = "jdbc:mysql://localhost:3306/jdbcStudy?uesUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";

        // 3.连接成功，数据库对象 connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        // 4.执行SQL的对象  statement代表执行sql语句的对象
        Statement statement = connection.createStatement();

        // 5.执行SQL对象，执行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("both=" + resultSet.getObject("birthday"));
        }

        // 6.释放连接，后用先释放
        resultSet.close();
        statement.close();
        connection.close();

    }
}

步骤总结：

加载驱动；
连接数据库 DriverManager；
获得执行SQL的对象 Statement；
获得返回的结果集；
释放连接。

四、对象说明

1、DriverManager

JDBC程序中的DriverManager用于加载驱动，并创建与数据库的链接，这个API的常用方法：

// DriverManager.registerDriver(new Driver())
// DriverManager.getConnection(url, user, password)

Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，加载驱动 
Connection connection = DriverManager.getConnection(url, username, password);

// connection 代表数据库
connection.createStatement();// 创建向数据库发送sql的statement对象
connection.prepareStatement(sql);// 创建向数据库发送预编译sql的PrepareSatement对象
connection.rollback();  // 事务回滚
connection.commit(); // 事务提交
connection.setAutoCommit(); // 设置数据库自动提交

注意：在实际开发中并不推荐采用registerDriver方法注册驱动。原因如下：

查看Driver的源代码可以看到，如果采用此种方式，会导致驱动程序注册两次，也就是在内存中会有两个Driver对象。
程序依赖mysql的api，脱离mysql的jar包，程序将无法编译，将来程序切换底层数据库将会非常麻烦。

推荐方式：Class.forName("com.mysql.jdbc.Driver");

采用此种方式不会导致驱动对象在内存中重复出现，并且采用此种方式，程序仅仅只需要一个字符串，不需要依赖具体的驱动，使程序的灵活性更高。

2、URL

URL用于标识数据库的位置，通过URL地址告诉JDBC程序连接哪个数据库，URL地址的写法为：

String url = "jdbc:mysql://localhost:3306/jdbcStudy?uesUnicode=true&characterEncoding=utf8&useSSL=true";

//公式： 协议://主机地址:端口号/数据库名?参数1&参数2&参数3

// mysql 默认端口号 3306
// jdbc:mysql://localhost:3306/数据库名?参数1&参数2&参数3

// oracle 默认端口号 1521
// jdbc:oracle:thin:@localhost:1521:sid

常用数据库URL地址的写法：
- Oracle写法：jdbc:oracle:thin:@localhost:1521:sid
- SqlServer写法：jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=sid
- MySql写法：jdbc:mysql://localhost:3306/sid
如果连接的是本地的Mysql数据库，并且连接使用的端口是3306，那么的url地址可以简写为
- jdbc:mysql:///数据库

3、Statement

Statement——执行SQL的对象

String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);  // 返回结果集,结果集中封装了我们全部查询出来的对象

// Statement对象常用方法
statement.executeQuery(sql); // 查询操作，返回结果集
statement.execute(sql); // 执行任何SQL
statement.executeUpdate(sql); // 更新、插入、删除，均为这个。返回一个受影响的行数
statement.addBatch(sql); // 把多条sql语句放到一个批处理中
statement.executeBatch(sql); // 向数据库发送一批sql语句执行

4、ResultSet

ResultSet 查询的结果集：封装了所有的查询结果。

获取指定的数据类型

resultSet.getObject(); // 不知道列数据类型时使用
// 如果知道列的类型就用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDouble();
resultSet.getDate();
resultSet.getObject();
....

ResultSet还提供了对结果集进行滚动的方法：

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

5、释放资源

// 6.释放连接
resultSet.close();
statement.close();
connection.close(); // 用完即关掉

五、statement对象

JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的 ResultSet对象。

1、插入数据

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

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

2、删除数据

使用executeUpdate(String sql)方法完成数据删除操作，示例操作：

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

3、更新数据

使用executeUpdate(String sql)方法完成数据修改操作，示例操作：

Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
	System.out.println(“修改成功！！！");
}

4、查询数据

使用executeQuery(String sql)方法完成数据查询操作，示例操作：

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeUpdate(sql);
while(rs.next()){
	//根据获取列的数据类型，分别调用rs的相应方法映射到java对象中
}

5、案例：使用jdbc对数据库增删改查

新建一个 lesson02 的包；
在src目录下创建一个db.properties文件，写入如下内容：

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

在lesson02 下新建一个 utils 包，新建一个类 Utils：

package com.jhoves.lesson02.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class Utils {

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

    static{
        try{
            // 读取db.properties文件中的数据库连接信息
            InputStream in = Utils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            // 获取数据库连接驱动
            driver = properties.getProperty("driver");
            // 获取数据库连接URL地址
            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 conn, Statement st, ResultSet rs){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

执行增删改查数据

// ==================插入数据======================
package com.jhoves.lesson02;
import com.jhoves.lesson02.utils.Utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

//插入数据
public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            // 获取一个数据库连接
            conn = Utils.getConnection();

            // 通过conn对象获取负责执行SQL命令的Statement对象
            st = conn.createStatement();

            // 要执行的SQL
            String sql = "";

            // 执行操作
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("插入数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            Utils.release(conn,st,rs);
        }
    }
}

// ==================删除数据======================
package com.jhoves.lesson02;
import com.jhoves.lesson02.utils.Utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

//删除数据
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            String sql = "delete from users where id=4";
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("删除数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

// ==================修改数据======================
package com.jhoves.lesson02;
import com.jhoves.lesson02.utils.Utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

//修改数据
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            String sql = "update users set name='王伟',email='wangwei@163.com' where id=3";
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("更新数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

// ==================查询数据======================
package com.jhoves.lesson02;
import com.jhoves.lesson02.utils.Utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

//查询数据
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            //String sql = "select * from users";
            String sql = "select * from users where id=2";
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println("查询数据成功！！！");
                //System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

6、SQL注入

SQL存在漏洞，会被攻击
通过巧妙的技巧来拼接字符串，造成SQL短路，从而获取数据库数据

package com.jhoves.lesson03;
import com.jhoves.lesson02.utils.Utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class SQL注入 {
    public static void main(String[] args) {
        login("lisi","123456");    // 正常登录 ==结果==>结果输出
//        login("'or '1=1","1234 56");  // SQL注入 ==结果==>输出数据库内容
    }

    // 登陆业务
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            st = conn.createStatement();
            // select * from users where name=''or '1-1' and password='123456'
            String sql = "select * from users where name='"+username
                    +"' and password='"+password+"'";
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println("查询数据成功！！！");
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("==========");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

六、PreparedStatement对象

PreperedStatement是Statement的子类，它的实例对象可以通过调用 Connection.preparedStatement()方法获得，相对于Statement对象而言：PreperedStatement可以避免SQL注入的问题。

Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。

PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于 sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。

使用PreparedStatement对象完成对数据库的CRUD操作

1、插入数据

package com.jhoves.lesson03;
import com.jhoves.lesson02.utils.Utils;

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

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils.getConnection();
            //使用?占位符，代替参数
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);  //预编译，先写sql，但是不执行
            // 为参数赋值，索引是从1开始的
            st.setInt(1,5); // id是int类型
            st.setString(2,"jhoves"); // name是字符串类型
            st.setString(3,"123456"); // password是字符串类型
            st.setString(4,"558282@qq.com"); // email是字符串类型
            //注意点： sql.Date 数据库的时间
            //		  util.Date  java的时间
            st.setDate(5,new java.sql.Date(System.currentTimeMillis())); // birthday是date类型

            /*
            * 这里有个小问题:
            * 在使用  new Date().getTime()时，会报错：请使用System.currentTimeMillis()代替new Date().getTime()
            */

            // 执行插入数据操作
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("插入数据成功！！！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL释放资源
            Utils.release(conn,st,rs);
        }

    }
}

2、删除数据

package com.jhoves.lesson03;
import com.jhoves.lesson02.utils.Utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 删除数据
 */
public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "delete * from users where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1,4);
            int num = st.executeUpdate(sql);
            if(num>0){
                System.out.println("删除数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

3、更新数据

package com.jhoves.lesson03;

import com.jhoves.lesson02.utils.Utils;

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

/**
 * 修改数据
 */
public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "update users set name=?,email=? where id=?";
            st = conn.prepareStatement(sql);
            st.setString(1,"叶凡");
            st.setString(2,"632579682@163.com");
            st.setInt(3,1);
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("更新数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

4、查询数据

package com.jhoves.lesson03;

import com.jhoves.lesson02.utils.Utils;

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

/**
 * 查询数据
 */
public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "select * from users where id=?";  //编写Sql
            st = conn.prepareStatement(sql);  //预编译
            st.setInt(1,1);  //传递参数
            rs = st.executeQuery();  //执行
            if(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println("查询数据成功！！！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);  //释放
        }
    }
}

5、避免SQL注入

package com.jhoves.lesson03;

import com.jhoves.lesson02.utils.Utils;

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

public class SQL注入 {
    public static void main(String[] args) {
//        login("lisi","123456");    // 正常登录  ==结果==>结果输出
        login("'or '1=1","123456");  // SQL注入  ==结果==>无结果输出
    }

    // 登陆业务
    public static void login(String username,String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try{
            conn = Utils.getConnection();
            String sql = "select * from users where name=? and password=?";
            st = conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,password);
            rs = st.executeQuery();
            while(rs.next()){
                System.out.println("查询数据成功！！！");
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
                System.out.println("==========");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

原理：执行的时候参数会用引号包起来，并把参数中的引号作为转义字符，从而避免了参数也作为条件的一部分。

七、使用IDEA连接数据库

打开IDEA的如下图：

打开如下界面，开始进行相关设置：

连接成功后，进入下图操作：

然后打开如下图：

编写数据库，编写后点击下图左上角绿色执行，具体如下图：

-- 案例
-- 创建账户表
CREATE TABLE account(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(40),
    money FLOAT
);
-- 插入测试数据
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);

八、JDBC操作事务

事务：逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。
ACID原则：
- 原子性(Atomic)：要么全部完成，要么都不完成。
- 一致性(Consist)：总数不变。
- 隔离性(Isolated)：多个进程互不干扰。
- 持久性(Durable)：一旦提交不可逆，持久化到数据库了。
隔离导致的一些问题：
- 脏读：一个事务读取了另外一个事务未提交的数据。
- 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）。
- 虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
当Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交在它上面发送的SQL语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行，可使用下列的 JDBC控制事务语句。

Connection.setAutoCommit(false);//开启事务(start transaction)
Connection.rollback();//回滚事务(rollback)
Connection.commit();//提交事务(commit)

模拟转账成功时的业务

package com.jhoves.lesson04;

import com.jhoves.lesson02.utils.Utils;

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) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            // 关闭数据库的自动提交，自动开启事务
            conn = Utils.getConnection();
            // 开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

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

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

模拟转账过程中出现异常，导致部分SQL执行失败后，让数据库自动回滚事务

package com.jhoves.lesson04;

import com.jhoves.lesson02.utils.Utils;

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) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            // 关闭数据库的自动提交，自动开启事务
            conn = Utils.getConnection();
            // 开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            int x=1/0;  // 报错

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

            // 业务完毕，提交事务
            conn.commit();
            System.out.println("成功！");
        } catch (Exception e) {
            try {
                conn.rollback(); // 如果失败则会回滚事务
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }
    }
}

模拟转账过程中出现异常，导致部分SQL执行失败时，手动通知数据库回滚事务

package com.jhoves.lesson04;

import com.jhoves.lesson02.utils.Utils;

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) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            // 关闭数据库的自动提交，自动开启事务
            conn = Utils.getConnection();
            // 开启事务
            conn.setAutoCommit(false);

            String sql1 = "update account set money=money-100 where name='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            int x=1/0;  // 报错

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

            // 业务完毕，提交事务
            conn.commit();
            System.out.println("转账成功！！！");
        } catch (Exception e) {
            try {
                conn.rollback(); // 失败，则事务回滚
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            Utils.release(conn,st,rs);
        }

    }
}

代码实现

开启事务 conn.setAutoCommit(False);
一组业务执行完毕，提交事务
可以在catch语句中定义回滚语句，默认失败就会回滚

九、数据库连接池

用户每次请求都需要向数据库获得连接，而数据库创建连接通常需要消耗相对较大的资源，创建时间也较长。假设网站一天10万访问量，数据库服务器就需要创建10万次连接，极大的浪费数据库的资源，并且极易造成数据库服务器内存溢出、拓机。

数据库连接池的基本概念

数据库连接是一种关键的有限的昂贵的资源，这一点在多用户的网页应用程序中体现的尤为突出。对数据库连接的管理能显著影响到整个应用程序的伸缩性和健壮性，影响到程序的性能指标。数据库连接池正是针对这个问题提出来的。数据库连接池负责分配,管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。

数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。

数据库连接池的最小连接数和最大连接数的设置要考虑到以下几个因素:

最小连接数：是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。
最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过次数，后面的数据库连接请求将被加入到等待队列中，这会影响以后的数据库操作。
如果最小连接数与最大连接数相差很大：那么最先连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，他将被放到连接池中等待重复使用或是空间超时后被释放。

编写连接池，需实现java.sql.DataSource接口。

开源数据源实现

现在很多WEB服务器(Weblogic, WebSphere, Tomcat)都提供了DataSoruce的实现，即连接池的实现。 通常我们把DataSource的实现，按其英文含义称之为数据源，数据源中都包含了数据库连接池的实现。

也有一些开源组织提供了数据源的独立实现：

DBCP 数据库连接池
C3P0 数据库连接池
Druid 数据库连接池 —— 阿里巴巴

在使用了数据库连接池之后，在项目的实际开发中就不需要编写连接数据库的代码了，直接从数据源获得数据库的连接。

DBCP

要使用DBCP数据源，需要导入如下两个 jar 文件：
- Commons-dbcp-1.4.jar
- Commons-pool-1.6.jar

在src目录下加入dbcp的配置文件：dbcpconfig.properties

#连接设置 这里面的名字，是DBCP数据源中定义好的
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=root

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000

#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;]
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED,REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

编写工具类 Utils_DBCP

package com.jhoves.lesson05.utils;

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 Utils_DBCP {
    private static DataSource datasource = null;

    static{
        try {
            InputStream in = Utils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties prpo = new Properties();
            prpo.load(in);
            // 创建数据源（工厂模式--创建对象）
            datasource = BasicDataSourceFactory.createDataSource(prpo);
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * 获取数据库连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return datasource.getConnection();
    }

    /**
     * 释放资源
     * @param conn
     * @param st
     * @param rs
     */
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

测试

package com.jhoves.lesson05;

import com.jhoves.lesson05.utils.Utils_DBCP;
import java.sql.*;

/**
 * 数据库连接工具类测试
 */
public class TestDbcp {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils_DBCP.getConnection();
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            st.setInt(1,6);
            st.setString(2,"jhoves");
            st.setString(3,"152546");
            st.setString(4,"152546@qq.com");
            st.setDate(5,new java.sql.Date(System.currentTimeMillis()));
            // 执行插入数据操作
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("插入数据成功！！！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL释放资源
            Utils_DBCP.release(conn,st,rs);
        }
    }
}

C3P0

C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate，Spring等。
c3p0与dbcp区别：
- dbcp没有自动回收空闲连接的功能；
- c3p0有自动回收空闲连接功能。
要使用C3P0数据源，需要导入如下两个 jar 文件：
- c3p0-0.9.5.5.jar
- mchange-commons-java-0.2.19.jar
在src目录下加入C3P0的配置文件：c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    C3P0的缺省(默认)配置，
    如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource();”这样写
    就表示使用的是C3P0的缺省(默认)配置信息来创建数据源
    -->
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy? useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>
    <!--
    C3P0的命名配置，
    如果在代码中“ComboPooledDataSource ds = new
    ComboPooledDataSource("MySQL");”这样写就表示使用的是name是MySQL的配置
    信息来创建数据源
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
</c3p0-config>

编写工具类 Utils_C3P0.java

package com.jhoves.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

/**
 * C3P0工具类
 */
public class Utils_C3P0 {
    private static ComboPooledDataSource ds = null;

    static{
        try {
            // 使用C3P0的默认配置来创建数据源
            ds = new ComboPooledDataSource();
            // 使用C3P0的命名配置来创建数据源
//            ds = new ComboPooledDataSource("MySQL");
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * 获取数据库连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 释放资源
     * @param conn
     * @param st
     * @param rs
     */
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if(rs != null){
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(st!=null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if(conn!=null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

测试类

package com.github.lesson05;

import com.github.lesson05.utils.Utils_C3P0;

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

/**
 * C3P0测试类
 */
public class TestC3P0 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = Utils_C3P0.getConnection();
            String sql = "insert into users(id,name,password,email,birthday) values (?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            st.setInt(1,7);
            st.setString(2,"pink");
            st.setString(3,"263223");
            st.setString(4,"3276128203@qq.com");
            st.setDate(5,new java.sql.Date(System.currentTimeMillis()));
            // 执行插入数据操作
            int num = st.executeUpdate();
            if(num>0){
                System.out.println("插入数据成功！！！");
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // SQL释放资源
            Utils_C3P0.release(conn,st,rs);
        }
    }
}

结论：无论使用什么数据源，本质都是一样的

posted @ 2022-03-20 20:30 JHoves 阅读(147) 评论(0) 收藏举报

刷新页面返回顶部

JHoves的博客

JDBC

JDBC

一、数据库驱动

二、JDBC介绍

三、第一个JDBC程序

四、对象说明

1、DriverManager

2、URL

3、Statement

4、ResultSet

5、释放资源

五、statement对象

1、插入数据

2、删除数据

3、更新数据

4、查询数据

5、案例：使用jdbc对数据库增删改查

6、SQL注入

六、PreparedStatement对象

1、插入数据

2、删除数据

3、更新数据

4、查询数据

5、避免SQL注入

七、使用IDEA连接数据库

八、JDBC操作事务

九、数据库连接池

公告