【JavaWeb】JDBC

wpf1391074638

已于 2022-08-25 09:46:19 修改

阅读量93

点赞数

分类专栏： JavaWeb 文章标签：数据库 java mysql

于 2022-07-06 02:19:53 首次发布

本文链接：https://blog.csdn.net/wpf1391074638/article/details/125547144

版权

JavaWeb 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

JDBC概述

简介

JDBC（Java DataBase Connectivity）就是 Java 数据库连接，用 Java 语言来操作数据库。

原来我们操作数据库是在控制台使用 SQL 语句来操作数据库，JDBC 是用 Java 语言向数据库发送 SQL 语句。

JDBC（Java Data Base Connectivity，Java 数据库连接）是一种用于执行 SQL 语句的Java API，

 可以为多种关系数据库提供统一访问，它由一组用 Java 语言编写的类和接口组成。

JDBC 提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。

 JDBC 屏蔽了底层不同数据库的操作差异，
从而使开发者可以通过统一的 Java API 来进行操作不同的数据库，而不必考虑底层具体数据库实现的差异。


 JDBC 是一套协议，是 Java 开发人员和数据库厂商达成的协议。
 JDBC 只是一组接口，JDBC 的实现是由具体的数据库厂商提供，以驱动程序形式提供，没有驱动无法完成数据库连接！每个数据库厂商都有自己的驱动，用来连接自己公司的数据库。
 当然还有第三方公司专门为某一数据库提供驱动，这样的驱动往往不是开源免费的！

在这里插入图片描述
请添加图片描述

请添加图片描述
MySQL驱动依赖

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.29</version>
</dependency>

编写JDBC测试

package com.lihaozhe.jdbc;

import com.mysql.cj.jdbc.Driver;
import org.junit.jupiter.api.Test;

import java.sql.*;

/**
 * JDBC 操作数据库
 * 1、加载驱动
 * 2、获取连接
 * 3、准备SQL语句
 * 4、发送并执行SQL
 * 5、处理结果集
 * 6、释放资源
 *
 */
public class JdbcTest01 {
    @Test
    public void test01() {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            // 1、使用驱动管理类 加载驱动 com.mysql.cj.jdbc.Driver;
            DriverManager.registerDriver(new Driver());
            // 2、获取连接
            // 数据库地址
            String url = "jdbc:mysql://47.94.130.233:3306/day04?useUnicode=true&characterEncoding=UTF8&useSSL=false&useServerPrepStmts=false&rewriteBatchedStatements=true&cachePrepStmts=true&allowMultiQueries=true&serverTimeZone=Aisa/Shanghai";
            // 数据库账号
            String user = "root";
            // 数据库密码
            String password = "Lihaozhe!!@@6633";
            conn = DriverManager.getConnection(url, user, password);
            // 3、准备SQL语句
             String sql = "SELECT `id`,`realname`,`idcard` FROM `student`";
            // 预编译SQL
            pst = conn.prepareStatement(sql);
            // 4、发送并执行SQL
            rs = pst.executeQuery();
            // 5、处理结果集
          while (rs.next()) {
                // columnIndex SQL中 字段位置
                // long id =  rs.getLong(1);
                // columnLabel SQL中 字段名字
                long id = rs.getLong("id");
                String realname = rs.getString("realname");
                String idcard = rs.getString("idcard");
                System.out.println("id = " + id + "\trealname = " + realname + "\tidcard = " + idcard);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6、释放资源
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

编写持久层接口与实现类获取新闻列表并测试

 @Test
    public void test02() {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            // 1、使用驱动管理类 加载驱动 com.mysql.cj.jdbc.Driver;
            DriverManager.registerDriver(new Driver());
            // 2、获取连接
            // 数据库地址
            String url = "jdbc:mysql:///news?useUnicode=true&characterEncoding=UTF8&useSSL=false&useServerPrepStmts=false&rewriteBatchedStatements=true&cachePrepStmts=true&allowMultiQueries=true&serverTimeZone=Aisa/Shanghai";
            // 数据库账号
            String user = "root";
            // 数据库密码
            String password = "Lihaozhe!!@@6633";
            conn = DriverManager.getConnection(url, user, password);
            // 3、准备SQL语句  参数使用 问号 占位
            String sql = "SELECT `nickname` FROM `user` WHERE `account` = ? AND `password` = ?";
            // 预编译SQL
            pst = conn.prepareStatement(sql);
            // 4、发送并执行SQL
            // parameterIndex 参数位置 从 1  开始， x为参数
            pst.setString(1,"admin");
            pst.setString(2,"123456");
            rs = pst.executeQuery();
            // 5、处理结果集
            if (rs.next()){
                System.out.println("恭喜 " + rs.getString("nickname") + " 登录成功");
            }else {
                System.out.println("账号或者密码错误");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 6、释放资源
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

public interface NewsDao {
    /**
     * 查询新闻列表
     * @return
     */
    List<News> selectAll() throws SQLException, IOException;
    List<NewsVo> selectNewsAndUserAll() throws SQLException, IOException;
}

public class NewsDaoTest {
    @Test
    public void selectAll() throws SQLException, IOException {
        NewsDao newsDao = new NewsDaoImpl();
        List<News> newsList = newsDao.selectAll();
        if (newsList != null && newsList.size() > 0) {
            newsList.forEach(System.out::println);
        } else {
            System.out.println("我页是有底线的");
        }
    }
}

一、jdbc的增删改查

删

package com.atguigu.dml;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/*
jdbc 完成 增 删 改
 */
public class IUDTest {
## 增---

    @Test
    public void test01() throws Exception {
       //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.建立连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.准备sql
        String sql = "insert into account values(null,'王一',1000)";

        //4.创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);

        //5.执行sql 获取结果
        int i = pst.executeUpdate();

        //6.输出结果
        System.out.println("i = " + i);
        //7.关闭资源
        pst.close();
        connection.close();

    }
}

## 删---

    @Test
    public void test03() throws Exception {
        //1.注册驱动 SPI

        //2.获取连接
        // jdbc:mysql://localhost:3306/atguigu 连接本地的mysql 端口号是 3306 可以省略 localhost:3306
        String url = "jdbc:mysql:///atguigu";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        //3.准备sql
        String sql = "delete from account where aid =3 or aid =4";
        //4.创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);
        //5.执行sql 获取结果
        int i = pst.executeUpdate();
        //6.输出结果
        System.out.println("i = " + i);
        //7.关闭资源

        pst.close();
        connection.close();



    }

## 改---

    @Test
    public void test02() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");
        //3.准备sql
        String sql ="update account set balance = 1000 where aid =2";
        //4.创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);
        //5.执行sql 获取结果
        int i = pst.executeUpdate();
        //6.输出结果
        System.out.println("i = " + i);
        //7.关闭资源
        pst.close();
        connection.close();
    }

查询

package com.atguigu.dml;

import org.junit.Test;

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

/*
查询

 */
public class QueryTest {

    @Test
    public void test01() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
        //3.准备sql
        String sql = "select * from account";

        //4.创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);
        //5.执行sql 获取结果集
        ResultSet rs = pst.executeQuery();

        //6.处理结果集 并展示内容
        while (rs.next()){//是不是还有一行数据/记录 如果有返回true
            //获取了一条记录
            int aid = rs.getInt("aid");
            //字段下标 从 1 开始
            String aname = rs.getString(2);
            // 忽略数据类型区别 Object类是所有类型的父类
            Object balance = rs.getObject(3);

            System.out.println(aid+" , "+aname+" , "+balance);
        }
        //7.关闭资源
        rs.close();
        pst.close();
        connection.close();


    }
}

二、sql拼接问题

    @Test
    public void test01() throws Exception {
        //1.创建键盘输入对象
        Scanner in = new Scanner(System.in);
        //2.准备数据
        System.out.println("请您输入id");
        int id = in.nextInt();
        System.out.println("请您输入名字");
        String name = in.next();
        System.out.println("请您输入余额");
        int balance = in.nextInt();
        //3.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //4.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
        //5.准备sql
        String sql = "insert into account values ("+id+",'"+name+"',"+balance+")";
        //5.1创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);
        //6.执行sql 获取结果
        int i = pst.executeUpdate();
        //7.输出结果
        System.out.println("i = " + i);
        //8.关闭资源
        pst.close();
        connection.close();


    }

}

解决拼接问题

package com.atguigu.exeption;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

/*
拼接问题：
   插入的数据是动态数据
 */
public class ExeptionTest {

    @Test
    public void test02() throws Exception {
        //1.创建键盘输入对象
        Scanner in = new Scanner(System.in);
        //2.准备数据
        System.out.println("请您输入id");
        int id = in.nextInt();
        System.out.println("请您输入名字");
        String name = in.next();
        System.out.println("请您输入余额");
        int balance = in.nextInt();
        //3.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //4.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
        //5.准备sql 避免拼接的麻烦 需要插入数据的位置 使用？代替
        String sql = "insert into account values (?,?,?)";
        //5.1创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);
        //5.2填充数据
        //设置int类型数据
        pst.setInt(1, id); // 将 id 插入到 第一个? 的位置
        // setObject 设置任意类型的数据
        pst.setObject(2, name);// 将 name 插入到 第二个? 的位置
        pst.setObject(3, balance);



        //6.执行sql 获取结果
        int i = pst.executeUpdate();
        //7.输出结果
        System.out.println("i = " + i);
        //8.关闭资源
        pst.close();
        connection.close();

    }

三、sql注入问题

    @Test
    public void test01() throws Exception {
        //sql注入问题
        Scanner in =new Scanner(System.in);
        System.out.println("请您输入id");
        String id = in.nextLine();
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
        //3.准备sql
        String sql = "select * from account where aid = "+id;

        //4.创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);

        //5.执行sql 获取结果集
        ResultSet rs = pst.executeQuery();
        //6.处理结果集并进行数据展示
        while (rs.next()){
            Object aid = rs.getObject(1);
            Object aname = rs.getObject(2);
            Object balance = rs.getObject(3);
            System.out.println(aid+" , "+aname+" , "+ balance);
        }

        //7.关闭资源
        rs.close();
        pst.close();
        connection.close();
    }

}

sql注入问题解决

/*
sql注入问题：
    sql是有问题的但是却可以找到数据

    采用数据填充的方式来解决问题
 */
public class ExeptionTest2 {

    @Test
    public void test02() throws Exception {
        Scanner in =new Scanner(System.in);
        System.out.println("请您输入id");
        String id = in.nextLine();
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
        //3.准备sql
        String sql = "select * from account where aid = ?";

        //4.创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);
        //4.1填充数据
        pst.setObject(1, id);



        //5.执行sql 获取结果集
        ResultSet rs = pst.executeQuery();
        //6.处理结果集并进行数据展示
        while (rs.next()){
            Object aid = rs.getObject(1);
            Object aname = rs.getObject(2);
            Object balance = rs.getObject(3);
            System.out.println(aid+" , "+aname+" , "+ balance);
        }

        //7.关闭资源
        rs.close();
        pst.close();
        connection.close();
        }

四、获取自增主键

/*
获取自增增长的主键
 */
public class GetKey {
    @Test
    public void test(){


    }


    @Test
    public void test01() throws Exception {
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
        //3.准备sql
        String sql = "insert into account values(null,?,?)";
        //4.创建命令发送器 不同 *****
        PreparedStatement pst = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        pst.setObject(1, "米莱迪");
        pst.setObject(2, 9000);
        //5.执行sql 获取结果
        int i = pst.executeUpdate();
        //6.输出结果
        System.out.println("i = " + i);
        //7.获取自增的主键
        ResultSet rs = pst.getGeneratedKeys();
        //因为主键只有一个值 可以使用if 不用循环
        if(rs.next()){
            //8.展示主键
            Object key = rs.getObject(1);
            System.out.println("key = " + key);
        }
        //9.关闭资源
        rs.close();
        pst.close();
        connection.close();
}

五、通过 jdbc 向maysql插入一张图片

/*
jdbc 向mysql中 插入一张图片 了解
 在 mysql的数据目录中固定单次上传最大容量
   max_allowed_packet=4M

 */
public class BlobTest {

    @Test
    public void test01() throws ClassNotFoundException, SQLException, IOException {

        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
        //3.准备sql
        String sql = "insert into photo values(null,?,?)";

        //4.创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);
        //5.填充数据
        pst.setObject(1, "苍老师3");

       // FileInputStream fis = new FileInputStream("C:\\图片\\g4.jpg");
        FileInputStream fis = new FileInputStream("D:\\mysql.rar");
       // pst.setBlob(2,fis );
        pst.setObject(2,fis );
        //6.执行sql
        int i = pst.executeUpdate();
        //7.输出结果
        System.out.println("i = " + i);
        //8.关闭资源
        fis.close();
        pst.close();
        connection.close();
}

六、批处理

/*
批处理：
批量插入数据
*/

    @Test
    public void test01() throws Exception {
        long start = System.currentTimeMillis();
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
        //3.准备sql
        String sql = "insert into account values(null,?,?)";
        //4.创建命令发送器
        PreparedStatement pst = connection.prepareStatement(sql);
        //5.填充数据
        for (int i = 1; i <=10000 ; i++) {
            pst.setObject(1, "张三");
            pst.setObject(2, i);
            //6.执行sql
            pst.executeUpdate();
        }
        //7.关闭资源
        pst.close();
        connection.close();
        long end = System.currentTimeMillis();
        System.out.println(end-start);//39122  39s


    }
public class BatchTest {
@Test
public void test02() throws Exception {
    long start = System.currentTimeMillis();
    //1.注册驱动
    Class.forName("com.mysql.cj.jdbc.Driver");
    //2.获取连接
    // 这一步很重要 ?rewriteBatchedStatements=true
    Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu?rewriteBatchedStatements=true", "root", "123456");
    //3.准备sql
    String sql = "insert into account values(null,?,?)";
    //4.创建命令发送器
    PreparedStatement pst = connection.prepareStatement(sql);
    //5.填充数据
    for (int i = 1; i <=10000 ; i++) {
        pst.setObject(1, "张三");
        pst.setObject(2, i);
        //6.将任务攒到一起
        pst.addBatch();
    }
    //执行批量添加
    pst.executeBatch();
    //7.关闭资源
    pst.close();
    connection.close();
    long end = System.currentTimeMillis();
    System.out.println(end-start);//39122  39s  37903  2963


}

七、事务操作

/*
jdbc操作事务：
    1.如何开启事务
         connection.setAutoCommit(false);
    2.如何回滚
         connection.rollback();
    3.如何提交
         connection.commit();




 */

public class TransactionTest {
Thread l;
    @Test
    public void test01() throws SQLException {
        Connection connection = null;
        PreparedStatement pst = null;
        try {
            System.out.println("开始转账");
            //1.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取连接
            connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "123456");
            //开启事务
            connection.setAutoCommit(false);

            //3.准备sql
            String sql = "update account set balance = balance + ? where aid = ?";
            //3.1创建命令发送器

            pst = connection.prepareStatement(sql);
            //4.填充数据
            //陈一 - 3000
            pst.setObject(1, -3000);
            pst.setObject(2, 1);
            //5.执行sql
            pst.executeUpdate();
            //报错
          //  int i = 10 / 0;

            //李四 + 3000
            pst.setObject(1, 3000);
            pst.setObject(2, 2);
            //5.执行sql
            pst.executeUpdate();
            System.out.println("转账成功");
            //没有问题进行数据提交
            connection.commit();

        } catch (Exception e) {
            //一旦有问题 将数据回滚
            connection.rollback();
            System.out.println("有内鬼 终止交易");
            e.printStackTrace();

        } finally {
            //6.关闭资源
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }

            }
            if (pst != null) {
                try {
                    pst.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

数据库连接池的使用

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=123456
initialSize=5
maxActive=10
maxWait=1000

public class JDBCUtils {
  static   DataSource dataSource;
/*
 Thread th;
 ThreadLocal.ThreadLocalMap threadLocals = null;
 */
  static ThreadLocal<Connection> local = new ThreadLocal<>();
    //静态代码块
    static {
        //属性集对象
       Properties pro = new Properties();
        try {
            //将配置文件信息 读取到属性集内
            // JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties") 将 配置文件信息转为 字节流
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //创建了连接池对象
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //关闭连接
    public static void closeConnection() throws Exception {

        Connection connection = JDBCUtils.getConnection();
        //事务关闭 防止后面复用的时候出现问题
        connection.setAutoCommit(true);
        //删除 线程内存储的连接
        local.remove();
        connection.close();
    }


    //通过连接池获取连接
    public static Connection getConnection() throws Exception {

        Connection connection = local.get();
        if(connection==null){
            connection= dataSource.getConnection();
            local.set(connection);
        }
        return connection;
    }
    public static void main(String[] args) throws Exception {
        for (int i = 1; i <=11; i++) {
            //从连接池获取连接
            Connection connection = getConnection();
            System.out.println(i+ "  connection = " + connection);
            //将连接还到池子到
            connection.close();
        }
    }

apache 的dbutils

dbutils 完成增删改

package com.atguigu.dbutils;

import com.atguigu.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import java.sql.Connection;
import java.sql.SQLException;

/*
dbutils 完成 增 删  改

0.导入 jar
1.建立依赖
2.创建 QueryRunner对象




 */
public class IUDTest {

    @Test
    public void test03() throws Exception {
        //0.获取连接

        //1.创建 QueryRunner对象
     //   QueryRunner queryRunner = new QueryRunner();
        //2.准备sql
        new QueryRunner().update(JDBCUtils.getConnection(), "update account set aname=? where aid = ?", "米莱迪",3);
        //3.关闭资源
    }

    @Test
    public void test02() throws Exception {
        //0.获取连接

        Connection connection = JDBCUtils.getConnection();
        //1.创建 QueryRunner对象
        QueryRunner runner = new QueryRunner();
        //2.准备sql
        String sql = "delete from account where aid = ?";

        //3.执行更新
        runner.update(connection, sql, 2);

        //4.关闭资源
        connection.close();
    }

    @Test
    public void test01() throws Exception {
        //0.获取连接
        Connection connection = JDBCUtils.getConnection();

        //1.创建 QueryRunner对象
        QueryRunner queryRunner = new QueryRunner();

        //2.准备sql
        String sql = "insert into account values(null,?,?)";

        //3.执行更新
        queryRunner.update(connection, sql, "安琪拉", 5000);


        //4.关闭资源
        connection.close();


    }

dbutils 完成查找

public class Account {
    private Integer aid;
    private String aname;
    private Integer balance;

    public Account() {
    }

    public Account(Integer aid, String aname, Integer balance) {
        this.aid = aid;
        this.aname = aname;
        this.balance = balance;
    }

    public Account(String aname, Integer balance) {
        this.aname = aname;
        this.balance = balance;
    }

    public Integer getAid() {
        return aid;
    }

    public void setAid(Integer aid) {
        this.aid = aid;
    }

    public String getAname() {
        return aname;
    }

    public void setAname(String aname) {
        this.aname = aname;
    }

    public Integer getBalance() {
        return balance;
    }

    public void setBalance(Integer balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "aid=" + aid +
                ", aname='" + aname + '\'' +
                ", balance=" + balance +
                '}';
    }
}

/*
DBuils查询时 结果返回什么数据重点是结果集处理器

    BeanListHandler 返回一个集合
    BeanHandler 封装到一个对象内 返回一个对象
    ScalarHandler 返回单个数据


 */
public class QueryTest {


    @Test
    public void test02() throws Exception{
        //0.获取连接
        Connection connection = JDBCUtils.getConnection();
        //1.创建 QueryRunner对象
        QueryRunner runner = new QueryRunner();
        //2.准备sql
        String sql = "select count(*) from account";

        //3.查询数据并将数据封装到集合内
        Object o = runner.query(connection, sql, new ScalarHandler<>());
        System.out.println("o = " + o);
        //4.关闭资源
        connection.close();
    }
    @Test
    public void test01() throws Exception{
        //0.获取连接

        Connection connection = JDBCUtils.getConnection();

        //1.创建 QueryRunner对象
        QueryRunner runner = new QueryRunner();

        //2.准备sql
        String sql = "select * from account";

        //3.查询数据并将数据封装到集合内

        List<Account> list = runner.query(connection, sql, new BeanListHandler<>(Account.class));

        list.forEach(System.out::println);

        //4.关闭资源

        connection.close();

    }

数据访问层完成

package com.atguigu.javabean;

public class Account {
    private Integer aid;
    private String aname;
    private Integer balance;

    public Account() {
    }

    public Account(Integer aid, String aname, Integer balance) {
        this.aid = aid;
        this.aname = aname;
        this.balance = balance;
    }

    public Account(String aname, Integer balance) {
        this.aname = aname;
        this.balance = balance;
    }

    public Integer getAid() {
        return aid;
    }

    public void setAid(Integer aid) {
        this.aid = aid;
    }

    public String getAname() {
        return aname;
    }

    public void setAname(String aname) {
        this.aname = aname;
    }

    public Integer getBalance() {
        return balance;
    }

    public void setBalance(Integer balance) {
        this.balance = balance;
    }

    @Override
    public String toString() {
        return "Account{" +
                "aid=" + aid +
                ", aname='" + aname + '\'' +
                ", balance=" + balance +
                '}';
    }

package com.atguigu.dao;

import com.atguigu.javabean.Account;

import java.util.List;
/*
领导 规定了 对Account表的操作
 */
public interface AccountDao {

    List<Account> selectAll();

    Account getById(int id);
    //更新
    void updateAccount(Account account);

    void deleteById(int id);

    void insertAccount(Account account);

}

package com.atguigu.dao.impl;

import com.atguigu.dao.AccountDao;
import com.atguigu.javabean.Account;
import com.atguigu.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/*
具体的实施者 小兵
 */
public class AccountDaoImpl implements AccountDao {


    @Override
    public List<Account> selectAll() {
        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();
            //3.准备sql
            String sql = "select * from account ";

            //4.执行操作
            List<Account> list = queryRunner.query(connection, sql, new BeanListHandler<>(Account.class));
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }


        return null;
    }

    @Override
    public Account getById(int id) {
        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();
            //3.准备sql
            String sql = "select * from account where aid = ?";

            //4.执行操作
            Account account = queryRunner.query(connection, sql, new BeanHandler<>(Account.class));
            return account;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void updateAccount(Account account) {
        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();
            //3.准备sql
            String sql = "update account  set aname = ?,balance = ? where aid = ?";
            //4.执行操作
            queryRunner.update(connection, sql, account.getAname(), account.getBalance(), account.getAid());

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


    }

    @Override
    public void deleteById(int id) {
        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();

            //3.准备sql
            String sql = "delete from account where aid = ?";
            //4.执行操作
            queryRunner.update(connection, sql, id);

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


    }
    @Override
    public void insertAccount(Account account) {
        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();
            //3.准备sql
            String sql = "insert into account values (null,?,?)";
            //4.执行操作
            queryRunner.update(connection, sql, account.getAname(), account.getBalance());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

测试

public class AccountTest {
    AccountDaoImpl accountDao = new AccountDaoImpl();

    @Test
    public void testinsert() {

        Account account = new Account("李白", 5000);

        accountDao.insertAccount(account);
    }
    @Test
    public void test02() {

        List<Account> accounts = accountDao.selectAll();

        accounts.forEach(System.out::println);


    }
}

通用的方法定义

package com.atguigu.dao;

import com.atguigu.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.util.List;

/*
通用的BaseDao
通用的增 删 改 查方法
 */
public class BaseDaoImpl {
    /**
     * 通用的增删改
     * @param sql
     * @param params
     */
    public void update(String sql,Object...params){
        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();
            //3.执行操作
            queryRunner.update(connection, sql,params);
        } catch (Exception e) {
            //一旦发生问题 抛出异常
           throw new RuntimeException(e.getMessage());
        }
    }

    /**
     * 通用的查询多条数据
     * @param clazz 集合的泛型类型
     * @param sql
     * @param params 参数
     * @param <T>
     * @return
     */
    public <T> List<T> findAll(Class<T> clazz,String sql, Object...params){

        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();
            List<T> list = queryRunner.query(connection, sql, new BeanListHandler<>(clazz), params);
            return list;
        }catch (Exception e){
            //一旦发生问题 抛出异常
            throw new RuntimeException(e.getMessage());

        }

    }

    /**
     * 通用的返回单个对象
     * @param clazz 对象的类型
     * @param sql
     * @param params 参数
     * @param <T>
     * @return
     */
    public <T> T findOneBean(Class<T> clazz,String sql, Object...params){

        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();
            T bean = queryRunner.query(connection, sql, new BeanHandler<>(clazz), params);
            return bean;
        }catch (Exception e){
            //一旦发生问题 抛出异常
            throw new RuntimeException(e.getMessage());

        }

    }

    /**
     * 通用的查询单个值
     * @param sql
     * @param params
     * @return
     */
    public Object getSingleValue(String sql,Object...params){

        //1.获取连接
        try {
            Connection connection = JDBCUtils.getConnection();
            //2.创建QueryRunner
            QueryRunner queryRunner = new QueryRunner();

            Object query = queryRunner.query(connection, sql, new ScalarHandler<>(), params);
            return query;
        }catch (Exception e){
            //一旦发生问题 抛出异常
            throw new RuntimeException(e.getMessage());
        }
    }



}

通用的BaseDaomlmp

package com.atguigu.javabean;

public class Job {

    private Integer jid;
    private String jname;
    private String description;

    public Job(Integer jid, String jname, String description) {
        this.jid = jid;
        this.jname = jname;
        this.description = description;
    }

    public Job() {
    }

    public Integer getJid() {
        return jid;
    }

    public void setJid(Integer jid) {
        this.jid = jid;
    }

    public String getJname() {
        return jname;
    }

    public void setJname(String jname) {
        this.jname = jname;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "Job{" +
                "jid=" + jid +
                ", jname='" + jname + '\'' +
                ", description='" + description + '\'' +
                '}';
    }
}

package com.atguigu.dao;

import com.atguigu.javabean.Job;

import java.util.List;

public interface JobDao {

    List<Job> getAll();
    Job getByJid(int jid);
    void updateJob(Job job);
    void insertJob(Job job);

}

package com.atguigu.dao.impl;

import com.atguigu.dao.BaseDaoImpl;
import com.atguigu.dao.JobDao;
import com.atguigu.javabean.Job;

import java.util.List;

public class JobDaoImpl extends BaseDaoImpl implements JobDao {
    @Override
    public List<Job> getAll() {
        String sql = "select * from job";
        List<Job> all = findAll(Job.class, sql);
        return all;
    }

    @Override
    public Job getByJid(int jid) {

        String sql = "select * from job where jid = ?";

        return findOneBean(Job.class, sql, jid);
    }

    @Override
    public void updateJob(Job job) {

        String sql = "update job set jname=? ,description=? where jid = ?";

        update(sql, job.getJname(),job.getDescription(),job.getJid());
    }

    @Override
    public void insertJob(Job job) {

    }
}

测试

package com.atguigu.daotest;

import com.atguigu.dao.impl.JobDaoImpl;
import com.atguigu.javabean.Job;
import org.junit.Test;
import org.omg.PortableInterceptor.SYSTEM_EXCEPTION;

import java.util.List;

public class JobTest {

    JobDaoImpl jobDao = new JobDaoImpl();
    @Test
    public void test02(){
        //1.获取 jid=5的记录
        Job job = jobDao.getByJid(5);

        job.setJname("人事总监6666");

        jobDao.updateJob(job);




    }

    @Test
    public void test01(){



      /*  List<Job> all = jobDao.getAll();
        all.forEach(System.out::println);*/

        Job byJid = jobDao.getByJid(3);

        System.out.println("byJid = " + byJid);
    }
}

创建Basedao 的意义

把重复的东西，封装到一起

保证同一个线程同一个连接

ThreadLocal

package com.atguigu.daotest;

import com.atguigu.utils.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
/*
当线程相同时 连接是相同的

ThreadLocal



 */
public class AccountDaoTest2 {

    @Test
    public void test01() throws Exception {
        //连接池获取 1
        Connection connection = JDBCUtils.getConnection();

        System.out.println("test01 connection = " + connection);
        System.out.println("test01  "+Thread.currentThread().getName());//main

        show();

    }

    private void show() throws Exception {
        //从连接池获取 2
        Connection connection = JDBCUtils.getConnection();
        System.out.println("show connection = " + connection);
        System.out.println("show  "+Thread.currentThread().getName());// main

    }
}

package com.atguigu.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import jdk.nashorn.internal.scripts.JD;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class JDBCUtils {
  static   DataSource dataSource;
/*
 Thread th;
 ThreadLocal.ThreadLocalMap threadLocals = null;
 */
  static ThreadLocal<Connection> local = new ThreadLocal<>();
    //静态代码块
    static {
        //属性集对象
       Properties pro = new Properties();
        try {
            //将配置文件信息 读取到属性集内
            // JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties") 将 配置文件信息转为 字节流
            pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            //创建了连接池对象
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //关闭连接
    public static void closeConnection() throws Exception {

        Connection connection = JDBCUtils.getConnection();
        //事务关闭 防止后面复用的时候出现问题
        connection.setAutoCommit(true);
        //删除 线程内存储的连接
        local.remove();
        connection.close();
    }


    //通过连接池获取连接
    public static Connection getConnection() throws Exception {

        Connection connection = local.get();
        if(connection==null){
            connection= dataSource.getConnection();
            local.set(connection);
        }
        return connection;
    }
    public static void main(String[] args) throws Exception {
        for (int i = 1; i <=11; i++) {
            //从连接池获取连接
            Connection connection = getConnection();
            System.out.println(i+ "  connection = " + connection);
            //将连接还到池子到
            connection.close();
        }
    }
}