搞懂JDBC技术看这一篇就够了

康康要加油

已于 2022-02-06 09:15:14 修改

阅读量1.2k

点赞数

文章标签： java jar intellij-idea jdbc

于 2022-02-03 23:31:03 首次发布

本文链接：https://blog.csdn.net/kangkang12221222/article/details/122756349

版权

JDBC技术

一、JDBC简介
- 1.JDBC是什么？
- 2.JDBC编写步骤
二、如何获取数据库连接
- 1.获取数据库连接方法一
- 2.获取数据库连接方法二
- 3.获取数据库连接方法三
- 4.获取数据库连接方法四
- 5.获取数据库连接方法五（推荐）
三、JDBC基本操作
- 1.通过Statement操作和访问数据库
- 2.通过ResultSet执行select
- 3.PreparedStatement添加操作
- 4.将获取连接以及释放连接代码封装到JDBCUtils
- 5.通过PreparedStatement对数据进行修改
- 6.PreparedStatement通用增删改方法
- 7.PreparedStatement通用查询方法
- 8.ORM编程思想
四、JDBC实现用户登录功能
- 1.业务介绍
- 2.登录界面初始化
- 3.登录实现
五、JDBC高级
- 1.什么是SQL注入
- 2.如何解决SQL注入
- 3.批量插入数据
六、Blob数据类型
- 1.MySql Blob类型
- 2.添加数据操作
- 3.读取Blob类型数据
- 4.特殊情况
七、JDBC事务
- 1.JDBC事务概述
- 2.JDBC事务实现
八、数据库连接池
- 1.数据库连接池相关介绍
九、c3p0连接池
- 1.c3p0的基本使用
- 2.通过配置xml的方式来使用c3p0
十、DBCP数据库连接池
- 1.DBCP基本使用
- 2.DBCP配置文件使用方式
十一、druid连接池
- 1.druid基本使用
- 2.通过druid重构JDBCUtils
十二、JDBC_DAO模式
- 1. 应用程序分层
- 2.通用BaseDAO实现
- 3.实现UserDAO
十三、DbUtils工具
- 1.QueryRunner
- 2.通过QueryRunner添加数据
- 3.ResultSetHandler
- 4.通过QueryRunner查询数据
- 5.通过QueryRunner查询特殊数据
- 6.自定义ResultSetHandler
十四、JDBC分页技术
- 1.JDBC分页
- 2.MySql实现分页查询

一、JDBC简介

1.JDBC是什么？

在这里插入图片描述

2.JDBC编写步骤

在这里插入图片描述

二、如何获取数据库连接

前期准备工作：
1.在工程下新建lib文件夹
2.将jar包导入到lib目录下
在这里插入图片描述
3.将导入的jar包通过Add as library添加到项目中
4.创建相关包和测试类

5.在数据库中新建数据库

6.重要知识点：

1.获取数据库连接方法一

//获取JDBC连接方法一
public class jdbc_Test {
    public static void main(String[] args) throws SQLException {
        //加载驱动
        Driver driver = new com.mysql.jdbc.Driver();
        //定义url
        String url = "jdbc:mysql://localhost:3306/baizhan?useSSL=false";
        //通过Properties设置账号密码
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","mysql");
        //通过Connection连接
        Connection conn = driver.connect(url,info);
        System.out.println(conn);
    }
}

2.获取数据库连接方法二

通过反射方法获取driver对象
此方法无第三方API，有助于代码移植以及兼容性

//获取数据库连接方法二
public class jdbc_Test2 {
    public static void main(String[] args) throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        /*
        * 通过反射方法获取driver对象
        * */
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();
        String url = "jdbc:mysql://localhost:3306/baizhan?useSSL=false";
        Properties info = new Properties();
        info.setProperty("user","root");
        info.setProperty("password","mysql");
        Connection conn = driver.connect(url,info);
        System.out.println(conn);
    }
}

3.获取数据库连接方法三

通过驱动管理器 DriverManager 代替 Drive 获取连接

//获取数据库连接方法三
public class jdbc_Test3 {
    //用DriverManager 代替 Driver
    public static void main(String[] args) throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        //用反射获取driver具有良好的代码移植性
        Class clazz = Class.forName("com.mysql.jdbc.Driver");
        Driver driver = (Driver) clazz.newInstance();
        //将driver注册到DriverManager中
        DriverManager.registerDriver(driver);
        String url = "jdbc:mysql://localhost:3306/baizhan?useSSL=false";
        String user = "root";
        String password = "mysql";
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);

    }
}

4.获取数据库连接方法四

//获取数据库连接方法四
public class jdbc_Test4 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //当com.mysql.jdbc.Driver加载到内存虚拟机中时，在Driver类中有static块会自动将Driver注册到DriverManager中
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/baizhan?useSSL=false";
        String user = "root";
        String password = "mysql";
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }
}

5.获取数据库连接方法五（推荐）

将配置文件与程序分离开
优点：
1.实现数据与代码的分离，实现了解耦
2.如果要修改可直接修改配置文件，可以避免程序重打包

//获取数据库连接方法五（推荐）
public class jdbc_Test5 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //将配置文件读入
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        //读入数据
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);
        System.out.println(conn);
    }
}

三、JDBC基本操作

int execute(String sql); //执行insert、update、delete操作
ResultSet executeQuery(String sql); //执行查询操作

1.通过Statement操作和访问数据库

在这里插入图片描述
Statement的使用

//通过Statement操作和访问数据库
public class Statement_Test {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //获取数据库连接
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);
        //获取statement对象
        Statement statement = conn.createStatement();
        //拼接sql
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入账户:");
        String username = sc.next();
        System.out.println("请输入密码:");
        String userpassword = sc.next();
        String sql = "insert into user(username,userpassword) values('" + username + "','" + userpassword + "')";
        //执行sql
        statement.execute(sql);
        //关闭资源
        conn.close();
        statement.close();
    }
}

2.通过ResultSet执行select

在这里插入图片描述

//通过ResultSet执行select
public class Statement_Test2 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //获取数据库连接
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);

        //获取statement对象
        Statement statement = conn.createStatement();
        String sql = "select * from user";
        ResultSet rs = statement.executeQuery(sql);
        while(rs.next()){
            System.out.println(rs.getString("username")+"  "+rs.getString("userpassword"));
        }
        conn.close();
        statement.close();
        rs.close();
    }
}

在这里插入图片描述

3.PreparedStatement添加操作

在这里插入图片描述

//PreparedStatement添加操作
public class Statement_Test3 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        //获取数据库连接
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);

        //创建PreparedStatement对象
        String sql = "insert into user(username,userpassword) values(?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        //填充占位符
        ps.setString(1,"admin6");
        ps.setString(2,"123456");
        //执行添加
        ps.executeUpdate();
        //关闭资源
        ps.close();
        conn.close();
    }
}

4.将获取连接以及释放连接代码封装到JDBCUtils

为了统一对Connection资源的管理及使用，创建JDBCUtils工具类，实现对数据库的连接与释放进行统
一管理。

//将获取连接以及释放连接代码封装到JDBCUtils
public class JDBCUtils {
    //获取连接
    public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
        InputStream is = jdbc_Test5.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        properties.load(is);
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String url = properties.getProperty("url");
        String driverClass = properties.getProperty("driverClass");
        Class.forName(driverClass);
        Connection conn = DriverManager.getConnection(url,user,password);
        return conn;
    }
    //关闭资源操作
    public static void close(Connection conn,Statement statement) throws SQLException {
        if (conn != null){
            conn.close();
        }
        if (statement != null){
            statement.close();
        }
    }
}

5.通过PreparedStatement对数据进行修改

//通过PreparedStatement对数据进行修改
public class PreparedStatement_Test2 {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        //获取连接并创建PreparedStatement对象
        Connection conn = JDBCUtils.getConnection();
        String sql = "update user set username = ? where id = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        //填充占位符
        ps.setObject(1,"admintest");
        ps.setInt(2,1);
        //执行修改
        ps.executeUpdate();
        //关闭资源
        JDBCUtils.close(conn,ps);
    }
}

6.PreparedStatement通用增删改方法

//PreparedStatement通用增删改方法
public class Statement_Test4 {
    public static void main(String[] args) throws Exception {
        String sql = "delete from user where id = ? or id = ?";
        update(sql,1,2);
    }

    public static void update(String sql,Object...args) throws Exception {
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i<args.length; i++){
            ps.setObject(i+1,args[i]);
        }
        ps.executeUpdate();
        JDBCUtils.close(conn,ps);
    }
}

7.PreparedStatement通用查询方法

//PreparedStatement通用查询方法
public class PreparedStatement_Test3 {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        String sql = "select * from user where id < ?";
        query(sql,6);
    }

    public static void query(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        ResultSet rs = ps.executeQuery();
        //获取ResultSet元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取表格列数
        int columnCount = rsmd.getColumnCount();
        while (rs.next()){
            for (int i =0;i<columnCount;i++){
                System.out.print(rs.getObject(i+1)+" ");
            }
            System.out.println();
        }
        rs.close();
        JDBCUtils.close(conn,ps);
    }
}

8.ORM编程思想

在这里插入图片描述

//ORM编程思想
public class PreparedStatement_Test4 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException, NoSuchFieldException, IllegalAccessException, IOException {
        String sql = "select * from user where id < ?";
        List<User> users = queryUser(sql,6);
        System.out.println();
    }

    public static List<User> queryUser(String sql,Object...args) throws SQLException, IOException, ClassNotFoundException, NoSuchFieldException, IllegalAccessException {
        //获取Connection
        Connection conn = JDBCUtils.getConnection();
        //预处理
        PreparedStatement ps = conn.prepareStatement(sql);
        //设置不定参参数
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args);
        }
        List<User> users = new ArrayList<>();
        //查询操作
        ResultSet rs = ps.executeQuery();
        //获取元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取列数
        int columnCount = rsmd.getColumnCount();
        while (rs.next()){
            User u = new User();
            for (int i=0;i<columnCount;i++){
                //获取列值
                Object columnValue = rs.getObject(i+1);
                //获取列名
                String columnName = rsmd.getColumnName(i+1);
                //通过反射获取属性
                Field field = u.getClass().getDeclaredField(columnName);
                //提升权限
                field.setAccessible(true);
                //赋值
                field.set(u,columnValue);
            }
            //将赋值后的user对象，添加进list
            users.add(u);
        }
        rs.close();
        JDBCUtils.close(conn,ps);
        return users;
    }
}

四、JDBC实现用户登录功能

1.业务介绍

在这里插入图片描述

2.登录界面初始化

//用户登录功能_登录界面初始化
public class Login {
    public static void main(String[] args) {
        initUI();
    }

    public static Map<String,String> initUI(){
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入账号:");
        String account = sc.next();
        System.out.println("请输入密码:");
        String password = sc.next();
        Map<String,String> userLoginInfo = new HashMap<>();
        userLoginInfo.put("account",account);
        userLoginInfo.put("password",password);
        return userLoginInfo;
    }
}

3.登录实现

Account实体类

//Account实体类
public class Account {
    private Integer userid;
    private String useraccount;
    private String username;
    private String userpassword;

    public Integer getUserid() {
        return userid;
    }

    public void setUserid(Integer userid) {
        this.userid = userid;
    }

    public String getUseraccount() {
        return useraccount;
    }

    public void setUseraccount(String useraccount) {
        this.useraccount = useraccount;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getUserpassword() {
        return userpassword;
    }

    public void setUserpassword(String userpassword) {
        this.userpassword = userpassword;
    }
}

针对account表实现通用查询

//针对account表实现通用查询
    public static List<Account> queryAccount(String sql,Object...args) throws Exception{
        List<Account> accounts = new ArrayList<>();
        //获取数据库的连接
        Connection conn = JDBCUtils.getConnection();
        //预编译sql语句，返回PrepareStatement的实例
        PreparedStatement ps = conn.prepareStatement(sql);
        //填充占位符
        for (int i=0;i<args.length;i++){
            ps.setObject(i+1,args[i]);
        }
        //执行
        ResultSet rs = ps.executeQuery();
        //获取结果集元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        //通过ResultSetMetaData获取结果集中的列数
        int columncount = rsmd.getColumnCount();
        while (rs.next()){
            Account account = new Account();
            for (int i =0;i<columncount;i++){
                //获取该列的值
                Object columnValue = rs.getObject(i+1);
                //获取该列的名称
                String columnName = rsmd.getColumnName(i+1);
                //获取该字段相关属性
                Field field = account.getClass().getDeclaredField(columnName);
                //提升权限
                field.setAccessible(true);
                //赋值
                field.set(account,columnValue);
            }
            accounts.add(account);
        }
        //资源的关闭
        rs.close();
        JDBCUtils.close(conn,ps);
        return accounts;
    }

登录实现

//登录实现
    public static boolean login(Map<String,String> userLoginInfo) throws Exception{
        //定义sql
        String sql = "select * from account where useraccount = ? and userpassword = ?";
        //获取所有匹配账号密码的account对象
        List<Account> accounts = queryAccount(sql,userLoginInfo.get("account"),userLoginInfo.get("password"));
        //如果该集合为0,代表账号或密码没有匹配，登陆失败
        if (accounts.size() == 0){
            return  false;
        }
        return true;
    }

测试登录功能

public static void main(String[] args) throws Exception {
       Map<String,String> userLoginInfo = initUI();
       System.out.println(login(userLoginInfo)?"登录成功":"登录失败");
    }

五、JDBC高级

1.什么是SQL注入

SQL注入是指利用某些系统没有对用户输入的数据进行充分的检查，而在用户输入数据中注入非法的
SQL语句段或者命令，从而利用系统的SQL引擎完成恶意行为的做法。
例如当使用Statement实现时：

Statement statement = conn.createStatement();

SQL语句改为:

String sql = "select * from account where useraccount = '" +userLoginInfo.get("account")+ "' and userpassword = '" + userLoginInfo.get("password") +"'";

当你测试时:
在这里插入图片描述
以上输入的账号和密码，通过SQL拼接，在执行过程中的SQL实际上是：

select * from account where useraccount = 'zhangsan' and userpassword ='baizhan'or'1=1'

由于1=1永远成立，所以不论账号密码是否正确，都会返回正确。

导致SQL注入的根本原因：
用户输入的信息中包含SQL语句的关键字，并且这些关键字参与SQL语句的编译过程，导致SQL语句的原意被扭曲，进而达到SQL注入的目的。

2.如何解决SQL注入

只要用户提供的信息不参与SQL语句的编译过程，即使用户提供的信息中包含SQL语句的关键字，但是
没有参与编译，仍然不起作用。

PreparedStatement可以将信息参数化，仍然用PreparedStatement实现登录功能：

        PreparedStatement ps = conn.prepareStatement(sql);

在这里插入图片描述
以上输入的账号和密码，通过PreparedStatement预编译，将baizhan’or’1=1作为一个整体的字符串参
数设置到SQL当中，在执行过程中的SQL实际上是：

select * from account where useraccount = 'zhangsan' and userpassword ="baizhan'or'1=1"

3.批量插入数据

利用三种不同的方法插入数据，并测试其用时。
首先通过Navicat创建一张物品空表：

CREATE TABLE goods(id int PRIMARY key auto_increment,goodsname VARCHAR(25))

依次用三种方法向表中插入2000条数据：
方法一、通过Statement + for循环方式批量插入数据，计算执行时间：

//1. 通过Statement + for循环方式批量插入数据，计算执行时间
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        Statement statement = conn.createStatement();
        //获取起始时间
        Long start = System.currentTimeMillis();
        for (int i=0;i<2000;i++){
            String sql = "insert into goods(goodsname)values('name_"+i+"')";
            statement.execute(sql);
        }
        //获取结束时间
        Long end = System.currentTimeMillis();
        JDBCUtils.close(conn,statement);
        System.out.println("插入总用时为:"+(end-start));

由于此方法使用的是statement，所以每次需要重新生成sql字符串。结果如下:
在这里插入图片描述
方法二、通过PreparedStatement + for循环方式批量插入数据，计算执行时间:

    //2. 通过PreparedStatement + for循环方式批量插入数据，计算执行时间
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into goods(goodsname)value(?)";
        PreparedStatement psmt = conn.prepareStatement(sql);
        Long start = System.currentTimeMillis();
        for (int i=0;i<2000;i++){
            psmt.setObject(1,"name_"+ i);
            psmt.executeUpdate();
        }
        Long end = System.currentTimeMillis();
        JDBCUtils.close(conn,psmt);
        System.out.println("插入总用时为:"+(end-start));
    }

方法二使用的是PreparedStatement，PreparedStatement是预编译模式，DBServer的编译器编
译后的执行代码被缓存下来，那么下次调用时只要是相同的预编译语句就不需要编译，只要将参数
直接传入就可以执行。结果如下：
在这里插入图片描述
方法三、通过PreparedStatement的addBatch()和executeBatch()进行批量插入数据

1. addBatch()把若干SQL语句装载到一起，然后一次性传送到数据库执行，即是批量处理sql数
据的。
2. executeBatch()会将装载到一起的SQL语句执行。
3. clearBatch()清除缓存

注：MySql默认情况下是不支持批处理的但从5.1.13开始添加了一个rewriteBatchStatement的参数，让MySql支持批处理。在加载url时设置该参数：rewriteBatchedStatements=true

url=jdbc:mysql://localhost:3306/baizhan?useSSL=false&rewriteBatchedStatements=true

    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into goods(goodsname)value(?)";
        PreparedStatement psmt = conn.prepareStatement(sql);
        Long start = System.currentTimeMillis();
        for (int i=0;i<=2000;i++){
            psmt.setObject(1,"name_"+i);
            //缓存sql
            psmt.addBatch();
            //每500条缓存执行一次
            if (i%500==0){
                //批量执行sql
                psmt.executeBatch();
                //清除缓存
                psmt.clearBatch();
            }
        }
        Long end = System.currentTimeMillis();
        JDBCUtils.close(conn,psmt);
        System.out.println("插入总用时为: "+(end - start));
    }

结果:
在这里插入图片描述

六、Blob数据类型

1.MySql Blob类型

在这里插入图片描述

2.添加数据操作

向演员名单表格中添加图片

public class Blob_Test {
    //添加操作
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into movieactor(actorname,photo)values(?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,"朱茵");
        InputStream is = new FileInputStream(new File("C:/actorimg/zhuyin.jpg"));
        ps.setBlob(2,is);
        ps.executeUpdate();
        JDBCUtils.close(conn,ps);
    }
}

3.读取Blob类型数据

//读取数据
public class Blob_Test2 {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "select * from movieactor where photo != ''";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while(rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("actorname");
            Blob blob = rs.getBlob("photo");
            InputStream is = blob.getBinaryStream();
            FileOutputStream fos = new FileOutputStream(id+"_"+name+".jpg");
            byte[] Buffer = new byte[1024];
            int len;
            while((len = is.read(Buffer))!=-1){
                fos.write(Buffer,0,len);
            }
            is.close();
            fos.close();
        }
        JDBCUtils.close(conn,ps);
    }
}

4.特殊情况

当读入一张7M左右的照片时，会出现报错

public class Blob_Test3 {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        Connection conn = JDBCUtils.getConnection();
        String sql = "insert into movieactor(actorname,photo)value(?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1,"照片");
        InputStream is = new FileInputStream(new File("C:/actorimg/photo.jpg"));
        ps.setBlob(2,is);
        ps.executeUpdate();
        JDBCUtils.close(conn,ps);
    }
}

报错：

Exception in thread “main” com.mysql.jdbc.PacketTooBigException: Packet for query is too large (8056021 > 4194304). You can change this value on the server by setting the max_allowed_packet’ variable.

原因：虽然MediumBlob允许保存最大值为16M，但MySql默认允许值为4194304即4M。
解决：在my.ini中添加max_allowed_packet=16M，并重启MySql服务。
文件路径：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
在这里插入图片描述
并重启Mysql服务。

七、JDBC事务

1.JDBC事务概述

在这里插入图片描述

2.JDBC事务实现

银行转账业务:
非事务实现:

加载相关数据，导入bank.sql
由zhangsan向lisi转账500

    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        PreparedStatement psmt = null;
        try{
            conn = JDBCUtils.getConnection();
            String sql = "update bank set balance = balance + ? where accountname = ?";
            psmt = conn.prepareStatement(sql);
            //lisi账户增加500
            psmt.setObject(1,500);
            psmt.setObject(2,"lisi");
            psmt.executeUpdate();
            //zhangsan账户减少500
            psmt.setObject(1,-500);
            psmt.setObject(2,"zhangsan");
            psmt.executeUpdate();
        }catch (Exception ex){
            System.out.println(ex.getMessage());
        }finally {
            JDBCUtils.close(conn,psmt);
        }
    }

如果在两个executeUpdate()之间发生异常，则部分转账正常，部分转账异常
模拟异常：

 //lisi账户增加500
            psmt.setObject(1,500);
            psmt.setObject(2,"lisi");
            psmt.executeUpdate();
            //zhangsan账户减少500
            //模仿异常
            int i = 1 / 0;
            psmt.setObject(1,-500);
            psmt.setObject(2,"zhangsan");
            psmt.executeUpdate();

运行结果：
在这里插入图片描述
由于两次SQL操作之间出现异常，所以只有部分转账成功。
解决：事务实现：
在获取数据库连接之后，通过Connection对象开启事务

    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        PreparedStatement psmt = null;
        try{
            conn = JDBCUtils.getConnection();
            //开启事务
            conn.setAutoCommit(false);
            String sql = "update bank set balance = balance + ? where accountname = ?";
            psmt = conn.prepareStatement(sql);
            //lisi账户增加500
            psmt.setObject(1,500);
            psmt.setObject(2,"lisi");
            psmt.executeUpdate();
            //zhangsan账户减少500
            psmt.setObject(1,-500);
            psmt.setObject(2,"zhangsan");
            psmt.executeUpdate();
            //如果没出错，提交事务
            conn.commit();
        }catch (Exception ex){
            //如果出错，回滚事务
            conn.rollback();
            System.out.println(ex.getMessage());
        }finally {
            JDBCUtils.close(conn,psmt);
        }
    }

八、数据库连接池

1.数据库连接池相关介绍

JDBC数据库连接池的必要性
数据库连接池
数据库连接池的优点
常用的数据库连接池

九、c3p0连接池

1.c3p0的基本使用

在这里插入图片描述

public class C3P0_Test {
    public static void main(String[] args) throws Exception {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        //配置JDBC相关数据
        cpds.setDriverClass("com.mysql.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/baizhan");
        cpds.setUser("root");
        cpds.setPassword("mysql");
        //设置初始化时，数据库连接池的连接数量
        cpds.setInitialPoolSize(10);
        //获取连接
        Connection conn = cpds.getConnection();
        System.out.println(conn);
    }
}

在这里插入图片描述

2.通过配置xml的方式来使用c3p0

在src目录下添加名为c3p0-config.xml文件
在c3p0-config.xml配置内容如下：

<c3p0-config>
<!-- 默认的数据库连接池-->
<default-config>
<!-- 数据库基本配置-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property
name="jdbcUrl">jdbc:mysql://localhost:3306/baizhan</property>
<property name="user">root</property>
<property name="password">mysql</property>
<!-- 数据库连接池配置-->
<!-- 当数据库连接池中的连接不够时，c3p0一次性向数据库申请的连接数-->
<property name="acquireIncrement">5</property>
<!-- 数据库连接池中初始化的连接数量-->
<property name="initialPoolSize">10</property>
<!-- 数据库连接池中最少的连接数量-->
<property name="minPoolSize">10</property>
<!-- 数据库连接池中最多的连接数量-->
<property name="maxPoolSize">100</property>
<!-- 数据库连接池中最多存在的Statement数量-->
<property name="maxStatements">200</property>
<!-- 每个连接最多可以使用的Statement数量-->
<property name="maxStatementsPerConnection">2</property>
</default-config>
</c3p0-config>

从c3p0连接池中获取Connection连接

//使用默认的数据库连接池
ComboPooledDataSource cpds = new ComboPooledDataSource();
Connection conn = cpds.getConnection();
System.out.println(conn);

也可以在c3p0-config.xml设置多个数据库连接池，根据连接池名字不同，来初始化不同的连接池。
如下的配置中，有两个数据库连接池配置

<c3p0-config>
    <!-- 设置一个名为localc3p0的数据库连接池-->
    <named-config name="localc3p0">
        <!-- 数据库基本配置-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/baizhan</property>
        <property name="user">root</property>
        <property name="password">mysql</property>
        <!-- 数据库连接池配置-->
        <!-- 当数据库连接池中的连接不够时，c3p0一次性向数据库申请的连接数-->
        <property name="acquireIncrement">5</property>
        <!-- 数据库连接池中初始化的连接数量-->
        <property name="initialPoolSize">10</property>
        <!-- 数据库连接池中最少的连接数量-->
        <property name="minPoolSize">10</property>
        <!-- 数据库连接池中最多的连接数量-->
        <property name="maxPoolSize">100</property>
        <!-- 数据库连接池中最多存在的Statement数量-->
        <property name="maxStatements">200</property>
        <!-- 每个连接最多可以使用的Statement数量-->
        <property name="maxStatementsPerConnection">2</property>
    </named-config>
    <!-- 设置一个名为VMc3p0的数据库连接池-->
    <named-config name="VMc3p0">
        <!-- 数据库基本配置-->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://xxx.xxx.xxx.xxx:3306/bjsxt</property>
        <property name="user">root</property>
        <property name="password">root</property>
        <!-- 数据库连接池配置省略-->
    </named-config>
</c3p0-config>

通过named-config中的name属性的值，来初始化不同的数据库连接池

//获取名为localc3p0连接池
ComboPooledDataSource cpds = new ComboPooledDataSource("localc3p0");
Connection conn = cpds.getConnection();
System.out.println(conn);

十、DBCP数据库连接池

在这里插入图片描述

1.DBCP基本使用

导入commons-dbcp2-2.5.0.jar包及其依赖包commons-pool2-2.6.0.jar、commons-logging1.2.jar到lib目录下，并引入项目中
从dbcp连接池中获取Connection连接

public class DBCP_Test {
    public static void main(String[] args) throws SQLException {
        BasicDataSource source = new BasicDataSource();
        source.setDriverClassName("com.mysql.jdbc.Driver");
        source.setUrl("jdbc:mysql://localhost:3306/baizhan");
        source.setUsername("root");
        source.setPassword("mysql");
        source.setInitialSize(10);
        Connection conn = source.getConnection();
        System.out.println(conn);
    }
}

2.DBCP配置文件使用方式

在src下创建一个dbcp.properties类型的文件，并写入

url=jdbc:mysql://localhost:3306/baizhan
driverClassName=com.mysql.jdbc.Driver
username=root
password=mysql
initialSize=10
maxActive=20

加载配置文件,获取连接池对象，通过该连接池对象获取连接

    //DBCP配置文件使用方式
    public static void main(String[] args) throws Exception {
        //将配置文件以流的方式加载
        InputStream is = DBCP_Test2.class.getClassLoader().getResourceAsStream("dbcp.properties");
        //创建配置文件
        Properties properties = new Properties();
        //加载流
        properties.load(is);
        //创建连接池对象
        DataSource ds = BasicDataSourceFactory.createDataSource(properties);
        //获取连接
        Connection conn = ds.getConnection();
        System.out.println(conn);
    }

十一、druid连接池

druid是阿里提供的数据库连接池，据说是集DBCP、c3p0、proxool优点于一身的数据库连接池，目前
经常使用。

1.druid基本使用

导入druid-1.0.19.jar包到lib目录下，并引入到项目中
在src下创建一个druid.properties类型的文件，并写入

url=jdbc:mysql://localhost:3306/baizhan
driverClassName=com.mysql.jdbc.Driver
username=root
password=mysql
initialSize=10
maxActive=20

druid配置信息：
在这里插入图片描述

加载配置文件，获取连接池对象，通过该连接池对象获取连接

    public static void main(String[] args) throws Exception {
        //将配置文件以流的方式加载
        InputStream is = Druid_Test.class.getClassLoader().getResourceAsStream("druid.properties");
        //创建配置文件
        Properties properties = new Properties();
        //加载流
        properties.load(is);
        //创建连接池对象
        DataSource ds = DruidDataSourceFactory.createDataSource(properties);
        //获取连接
        Connection conn = ds.getConnection();
        System.out.println(conn);
    }

2.通过druid重构JDBCUtils

//通过druid重构JDBCUtils
public class JDBCUtilsDruid {
    private static DataSource ds = null;
    static {
        //将配置文件以流的方式加载
        InputStream is = Druid_Test.class.getClassLoader().getResourceAsStream("druid.properties");
        //创建配置文件
        Properties properties = new Properties();
        try {
            //加载流
            properties.load(is);
            //创建连接池对象
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        Connection conn = ds.getConnection();
        return conn;
    }

    // 释放数据库连接方法
    public static void close(Connection conn, Statement statement) throws SQLException {
        if (conn != null){
            conn.close();
        }
        if (statement != null){
            statement.close();
        }
    }
}

十二、JDBC_DAO模式

1. 应用程序分层

应用程序通过创建不同的包来实现项目的分层，将项目中的代码根据功能做具体划分，并存放在不同的
包下。
在这里插入图片描述

2.通用BaseDAO实现

DAO：Data Access Object访问数据库信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete）、而不包含任何业务相关的信息。

DAO一般是提供从数据库增加、删除、修改记录、查询所有记录、查询符合某个条件记录、取得某条记录等方法的底层数据操作自定义类。由于可能操作多个数据库表，这样就需要为每个表提供一个操作他的类 xxDAO，这些DAO继承BaseDAO 就可以省略很多重复代码（从数据库增加、删除、修改记录、查询所有记录、查询符合某个条件记录、取得某条记录等方法的代码）。

实现一个通用的BaseDao抽象类

public abstract class BaseDao<T> {
    //定义一个变量来接受泛型的类型
    private Class<T> type;
    //获取T的Class对象，获取泛型类型，泛型是在被子类继承时才确定
    public BaseDao(){
        //获取子类类型
        Class clazz = this.getClass();
        //获取父类类型
        //getGenericSuperclass()用来获取当前类的父类的类型
        //ParameterizedType表示的是带泛型的类型
        ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
        //获取具体的泛型类型
        //getActualTypeArguments()获取具体的泛型类型
        //这个方法返回一个Type类型数组
        Type[] types = parameterizedType.getActualTypeArguments();
        //获取具体的泛型类型
        this.type =(Class<T>) types[0];
    }
}

2. 通用的增删改操作

 //通用增删改操作
    public int excuteUpdate(String sql,Object...param) throws Exception{
        //获取连接
        Connection conn = JDBCUtilsDruid.getConnection();
        PreparedStatement psmt = conn.prepareStatement(sql);
        //绑定参数
        for (int i=0;i<param.length;i++){
            psmt.setObject(i+1,param[i]);
        }
        int rows = psmt.executeUpdate();
        JDBCUtilsDruid.close(conn,psmt);
        //返回受影响的行数
        return rows;
    }

3. 用的查询操作（需要用到commons-beanutils-1.9.3.jar包及其依赖包commons-logging-1.2.jar，将它们导入并引入工程）

//通用查询方法，返回零条或多条查询记录
    public List<T> getBean(String sql,Object...param) throws Exception{
        List<T> list = new ArrayList<>();
        //获取连接
        Connection conn = JDBCUtilsDruid.getConnection();
        PreparedStatement psmt = conn.prepareStatement(sql);
        //绑定参数
        for (int i=0;i<param.length;i++){
            psmt.setObject(i+1,param[i]);
        }
        ResultSet rs = psmt.executeQuery();
        //获取结果集元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        while(rs.next()){
            T bean = type.newInstance();
            for (int i=0;i<rsmd.getColumnCount();i++){
                //获得列名
                String comlumnName = rsmd.getColumnName(i+1);
                //获得列值
                Object value = rs.getObject(comlumnName);
                //通过BeanUtil工具将类注入到对象中
                BeanUtils.setProperty(bean,comlumnName,value);
            }
            list.add(bean);
        }
        return list;
    }

4. 获取单一值，如select(*)

    //获取单一值，如select(*),select max(date)等等
    public Object getValue(String sql,Object...param) throws Exception{
        Object res = null;
        Connection conn = JDBCUtilsDruid.getConnection();
        PreparedStatement psmt = conn.prepareStatement(sql);
        //绑定参数
        for (int i=0;i<param.length;i++){
            psmt.setObject(i+1,param[i]);
        }
        ResultSet rs = psmt.executeQuery();
        if (rs.next()){
            //获取第一列的值
            res = rs.getObject(1);
        }
        return res;
    }

3.实现UserDAO

创建UserDAO接口

public interface UserDAO {
    //根据username获取一条记录
    User getUser(String username) throws Exception;
    //插入一条User
    void insertUser(User user) throws Exception;
    //根据id删除一条数据
    void deleteUserById(Integer id) throws Exception;
    //获取一共有多少用户
    Integer getUserCount() throws Exception;
}

2 . 创建UserDAO的实现类UserDAOImpl，UserDAOImpl继承BaseDAO
（由于每个方法都要抛出异常，所以在接口UserDAO的方法后面也会throw异常）

public class UserDAOImpl extends BaseDao<User> implements UserDAO {
    //根据username获取一条记录
    @Override
    public User getUser(String username) throws Exception {
        User u = null;
        String sql = "select * from user where username = ?";
        List<User> list = this.getBean(sql,username);
        if (list.size() != 0 ){
            u = list.get(0);
        }
        return u;
    }

    //插入一条User
    @Override
    public void insertUser(User user) throws Exception {
        String sql = "insert into user(username,userpassword)value(?,?)";
        this.excuteUpdate(sql,user.getUsername(),user.getUserpassword());
    }

    //根据id删除一条数据
    @Override
    public void deleteUserById(Integer id) throws Exception {
        String sql = "delete from user where id = ?";
        this.excuteUpdate(sql,id);
    }

    //获取一共有多少用户
    @Override
    public Integer getUserCount() throws Exception {
        String sql = "select count(*) from user";
        Integer count = Integer.valueOf(this.getValue(sql).toString());
        return count;
    }
}

测试各个方法

//测试UserDAO
public class Main {
    public static void main(String[] args) throws Exception {
        UserDAO userDAO = new UserDAOImpl();
        //测试insertuser
        /*User u = new User();
        u.setUsername("test");
        u.setUserpassword("password");
        userDAO.insertUser(u);*/

        //测试get
     /*   User u = userDAO.getUser("test");
        System.out.println(u.getUsername()+"--"+u.getUserpassword());*/

        //测试delete
//        userDAO.deleteUserById(7);

        //获取用户总数
        System.out.println(userDAO.getUserCount());
    }
}

十三、DbUtils工具

1.QueryRunner

在这里插入图片描述

2.通过QueryRunner添加数据

//通过QueryRunner添加数据
public class DBUtils_Test {
    public static void main(String[] args) throws Exception {
        InputStream is = DBUtils_Test.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties properties = new Properties();
        properties.load(is);
        //通过数据库连接池初始化QueryRunner对象
        QueryRunner queryRunner = new QueryRunner(DruidDataSourceFactory.createDataSource(properties));
        String sql = "insert into user(username,userpassword)values(?,?)";
        //count代表影响了几行记录
        int count = queryRunner.update(sql,"QueryRunnerTest","QueryRunnner");
        System.out.println(count);
    }
}

3.ResultSetHandler

在这里插入图片描述

4.通过QueryRunner查询数据

返回单条记录并封装成实体类对象

//返回单条记录并封装成实体类对象
public class DBUtils_Test2 {
    public static void main(String[] args) throws Exception {
        InputStream is = DBUtils_Test2.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties properties = new Properties();
        properties.load(is);
        QueryRunner queryRunner = new QueryRunner(DruidDataSourceFactory.createDataSource(properties));
        String sql = "select * from user where id = ?";
        //一条记录使用BeanHandler
        //给BeanHandler提供相关实体类信息
        BeanHandler<User> bh = new BeanHandler<>(User.class);
        //使用实体类对象直接接收数据
        //query参数，分别为sql语句，ResultSetHandler对象，及sql语句中的占位符参数
        User user = queryRunner.query(sql,bh,8);
        System.out.println(user.getUsername());
    }
}

返回多条记录并封装成实体类对象列表

//返回多条记录并封装成实体类对象列表
public class DBUtils_Test3 {
    public static void main(String[] args) throws Exception {
        InputStream is = DBUtils_Test3.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties properties = new Properties();
        properties.load(is);
        QueryRunner queryRunner = new QueryRunner(DruidDataSourceFactory.createDataSource(properties));
        String sql = "select * from user";
        //多条记录使用BeanListHandler
        //给BeanListHandler提供相关实体类信息
        BeanListHandler<User> bh = new BeanListHandler<>(User.class);
        //使用实体类集合直接接收所有数据
        //query参数，分别为sql语句，ResultSetHandler对象
        List<User> users = queryRunner.query(sql,bh);
        for (User u : users){
            System.out.println(u.getUsername());
        }
    }
}

5.通过QueryRunner查询特殊数据

查询User表中一共有多少条数据

//查询User表中一共有多少条数据
public class DBUtils_Test4 {
    public static void main(String[] args) throws Exception {
        InputStream is = DBUtils_Test4.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties properties = new Properties();
        properties.load(is);
        QueryRunner queryRunner = new QueryRunner(DruidDataSourceFactory.createDataSource(properties));
        String sql = "select count(*) from user";
        //通过ScalarHandler保存单一值
        ScalarHandler sh = new ScalarHandler();
        Long count = (Long)queryRunner.query(sql,sh);
        System.out.println(count);
    }
}

6.自定义ResultSetHandler

//自定义ResultSetHandler
public class MyResultSetHandler implements ResultSetHandler {
    @Override
    public Object handle(ResultSet resultSet) throws SQLException {
        Map<String,String> map = null;
        if (resultSet.next()){
            map = new HashMap<>();
            map.put("id",resultSet.getString("id"));
            map.put("username",resultSet.getString("username"));
            map.put("userpassword",resultSet.getString("userpassword"));
        }
        return map;
    }

    public static void main(String[] args) throws Exception {
        InputStream is = DBUtils_Test.class.getClassLoader().getResourceAsStream("druid.properties");
        Properties properties = new Properties();
        properties.load(is);
        QueryRunner queryRunner = new QueryRunner(DruidDataSourceFactory.createDataSource(properties));
        String sql = "select * from user where id = ?";
        MyResultSetHandler msh = new MyResultSetHandler();
        Map<String,String> map = (Map<String, String>) queryRunner.query(sql,msh,8);
        System.out.println(map);
    }
}

十四、JDBC分页技术

在这里插入图片描述

1.JDBC分页

jdbc通过分页关键字实现分页效果，将分页结果存在分页对象中
分页类：

public class Page {
    //当前页数
    private Integer currPage;
    //每页显示的记录数
    private Integer pageSize;
    //总记录数
    private Integer totalCount;
    //总页数
    private Integer totalPage;
    //每页显示的数据
    private List<Map<String,Object>> list = new ArrayList<>();

    public Integer getCurrPage() {
        return currPage;
    }

    public void setCurrPage(Integer currPage) {
        this.currPage = currPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(Integer totalCount) {
        this.totalCount = totalCount;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(Integer totalPage) {
        this.totalPage = totalPage;
    }

    public List<Map<String, Object>> getList() {
        return list;
    }

    public void setList(List<Map<String, Object>> list) {
        this.list = list;
    }
}

2.MySql实现分页查询

//JDBC分页_Mysql实现
public class MysqlPage {
    public static void main(String[] args) throws Exception{
        Page page = new Page();
        //设置当前查询页数
        page.setCurrPage(2);
        //每页大小
        page.setPageSize(2);
        Page respage = selectPage(page);
        //输出总页数
        System.out.println(respage.getTotalPage());
        //输出总记录数
        System.out.println(respage.getTotalCount());
        //输出结果集
        System.out.println(respage.getList());
    }

    public static Page selectPage(Page page) throws Exception {
        Connection conn = JDBCUtilsDruid.getConnection();
        String sql = "select * from user limit ?,?";
        PreparedStatement ps = conn.prepareStatement(sql);
        //设置查询页数   （当前页-1）*每页数量
        ps.setInt(1,(page.getCurrPage()-1)*page.getPageSize());
        //设置每页数量
        ps.setInt(2,page.getPageSize());
        //执行sql
        ResultSet rs = ps.executeQuery();
        //获取元数据
        ResultSetMetaData rsmd = rs.getMetaData();
        while (rs.next()){
            Map<String,Object> map = new HashMap<>();
            //根据元数据填充map
            for (int i=0;i<rsmd.getColumnCount();i++){
                String columnName = rsmd.getColumnName(i+1);
                String columnValue = rs.getString(i+1);
                map.put(columnName,columnValue);
            }
            page.getList().add(map);
        }

        //查询总记录数
        sql = "select count(*) from user ";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()){
            //获取总记录数
            int count = rs.getInt(1);
            //设置总记录数
            page.setTotalCount(count);
            //总页数 = 总数/每页数量   向上取整
            Double totalpage = Math.ceil((double)count / (double)page.getPageSize());
            page.setTotalPage(totalpage.intValue());
        }
        return page;
    }
}