JDBC编程

1. 编写步骤

六个步骤：

1.注册驱动：Class.forName("com.mysql.cj.jdbc.Driver")

2.通过驱动获取连接：DriverManager.connection

3.通过连接获取sql语句执行对象：connection.preparedStatement(url, root, password)

        url编写：jdbc:mysql://IP地址:端口号/要连接的数据库名

        root：登录数据库账号

        password：登录数据库密码

4.编写sql语句并返回sql语句执行结果

5.查询该结果

6.释放资源

在使用JDBC的相关资源时，比如Connection、PreparedStatement、ResultSet，使用完毕后，要及时关闭这些资源以释放数据库服务器资源和避免内存泄漏是很重要的

 2. 增删改查案例

增

    public void testInsert() throws Exception{
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接对象（通过url root password）
        //url: jdbc:mysql://id:端口号/数据库名
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/study", "root", "123456");

        //3.获取执行sql语句对象(数字代表第几个占位符，有几个占位符必须填入相对应的数据)
        /*PreparedStatement preparedStatement = connection.prepareStatement("insert into emp value (?, ?, ?, ?, ?)");
        preparedStatement.setInt(1, 12);
        preparedStatement.setString(2, "迪丽热巴");
        preparedStatement.setInt(3, 66);
        preparedStatement.setInt(4, 2);
        preparedStatement.setString(5, "齐齐哈尔");*/

        PreparedStatement preparedStatement = connection.prepareStatement("insert into emp (id, name, age, address) value (?, ?, ?, ?)");
        preparedStatement.setInt(1, 14);
        preparedStatement.setString(2, "奶龙");
        preparedStatement.setInt(3, 99);
        preparedStatement.setString(4, "广东");

        //4.查询是否插入成功(返回int值代表受影响的行数)
        int result = preparedStatement.executeUpdate();
        if (result > 0) {
            System.out.println("插入成功");
        } else {
            System.out.println("插入失败");
        }

        //5.释放资源
        preparedStatement.close();
        connection.close();
    }

改

    public void testUpdate() throws Exception{
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接对象(url:"jdbc:mysql://ip地址:端口号/数据库名
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/study", "root", "123456");

        //3.获取执行sql语句对象
        PreparedStatement preparedStatement = connection.prepareStatement("update emp set address = ? where id = ?");
        preparedStatement.setString(1, "桂林");
        preparedStatement.setInt(2, 10);

        //4.查询是否更新成功
        int result = preparedStatement.executeUpdate();
        if (result > 0) {
            System.out.println("更新成功");
        } else {
            System.out.println("更新失败");
        }

        //5.释放资源
        preparedStatement.close();
        connection.close();
    }

查 

    public void testQuery() throws Exception{
        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/study", "root", "123456");

        //3.获取执行sql语句对象
        PreparedStatement preparedStatement = connection.prepareStatement("select * from emp where age < 30");

        //4.返回结果集合
        //preparedStatement.setInt(1, 30);
        ResultSet resultSet = preparedStatement.executeQuery();

        //5.遍历结果集合
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            String address = resultSet.getString("address");
            System.out.println(name+" "+age+" "+address);
        }

        //6.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

 删

    public void testDelete() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/study", "root", "123456");

        //3.获取执行sql语句对象preparedStatement
        PreparedStatement preparedStatement = connection.prepareStatement("delete from emp where id = ?");
        preparedStatement.setInt(1, 11);

        //4.查询是否删除成功
        int result = preparedStatement.executeUpdate();
        if (result > 0) {
            System.out.println("delete");
        } else {
            System.out.println("no");
        }

        //5.释放资源
        preparedStatement.close();
        connection.close();
    }

3. JDBC扩展 

 3.1 实体类和ORM

* 在使用JDBC操作数据库时，我们会发现数据都是零散的，明明在数据库中是一行完整的数据，到了Java中变成了一个一个的变量，不利于维护和管理。而我们Java是面向对象的，一个表对应的是一个类，一行数据就对应的是Java中的一个对象，一个列对应的是对象的属性，所以我们要把数据存储在一个载体里，这个载体就是实体类！
* ORM（Object Relational Mapping）思想，**对象到关系数据库的映射**，作用是在编程中，把面向对象的概念跟数据库中表的概念对应起来，以面向对象的角度操作数据库中的数据，即一张表对应一个类，一行数据对应一个对象，一个列对应一个属性！
* 当下JDBC中这种过程我们称其为手动ORM。后续我们也会学习ORM框架，比如MyBatis、JPA等。 

 private int employeeId;
    private String employeeName;
    private int employeeAge;
    private int employeeDept;
    private String employeeAddress;

//利用ORM思想将数据抽取成为实列对象
    @Test
    public void test1() throws Exception{
        //1.注册驱动

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/study", "root", "123456");

        //3.获取执行sql语句对象
        PreparedStatement preparedStatement = connection.prepareStatement("select * from emp where id = ?");

        //4.查询并返回结果集合
        preparedStatement.setInt(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();

        //5.将数据封装成实列化对象
        if (resultSet.next()) {
            Employee employee = new Employee();
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            int age = resultSet.getInt("age");
            int dept = resultSet.getInt("dept_id");
            String address = resultSet.getString("address");
            employee.setEmployeeId(id);
            employee.setEmployeeName(name);
            employee.setEmployeeAge(age);
            employee.setEmployeeDept(dept);
            employee.setEmployeeAddress(address);
            System.out.println(employee);
        }

        //6.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

 3.2 主键回显

在数据中，执行新增操作时，主键列为自动增长，可以在表中直观的看到，但是在Java程序中，我们执行完新增后，只能得到受影响行数，无法得知当前新增数据的主键值。在Java程序中获取数据库中插入新数据后的主键值，并赋值给Java对象，此操作为主键回显。 

//主键回显
    public void test2() throws Exception {
        //1.注册驱动

        //2.获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/study", "root", "123456");

        //3.获取执行sql语句对象
        PreparedStatement preparedStatement = connection//执行sql语句，数据存储进数据库后返回主键值
                .prepareStatement("insert into emp (name, age, address) value (?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

        //4.执行sql语句填充占位符
        preparedStatement.setString(1, "jack");
        preparedStatement.setInt(2, 88);
        preparedStatement.setString(3, "云南");

        //5.查询是否更新成功
        int i = preparedStatement.executeUpdate();
        if (i > 0) {
            System.out.println("更新成功");

            //该结果集会返回更新的主键值回来并存储在集合中
            ResultSet resultSet = preparedStatement.getGeneratedKeys();
            if (resultSet.next()) {
                System.out.println(resultSet.getInt(1));
            }

            resultSet.close();
        }

        //6.释放资源
        preparedStatement.close();
        connection.close();
    }

 3.3 批量操作

* 插入多条数据时，一条一条发送给数据库执行，效率低下！
  
* 通过批量操作，可以提升多次操作效率！ 

@Test
    //批量操作
    public void test3() throws Exception {
        //1.注册驱动

        //2.获取连接
        // 执行批量操作必须在?后添加?rewriteBatchedStatements=true允许批量操作
        // sql语句必须使用values且不能以分号结尾
        // 使用addBatch()方法批量插入数据
        // 使用executeBatch()执行批量添加数据
        Connection connection = DriverManager
                .getConnection("jdbc:mysql://localhost:3306/study?rewriteBatchedStatements=true", "root", "123456");

        //3.获取执行sql语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("insert into emp (name, address) values (?, ?)");

        long start = System.currentTimeMillis();
        //4.插入数据
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setString(1, "hello" + i);
            preparedStatement.setString(2, "earth" + i);
            preparedStatement.addBatch();
        }

        //5.执行批量插入
        preparedStatement.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("批量操作所需时间：" + (end - start));

        //6.释放资源
        preparedStatement.close();
        connection.close();
    }

 4. 连接池

4.1 解决当前问题 

> * 每次操作数据库都要获取新连接，使用完毕后就close释放，频繁的创建和销毁造成资源浪费。
> * 连接的数量无法把控，对服务器来说压力巨大。 

 4.2 概念

连接池就是数据库连接对象的缓冲区，通过配置，由连接池负责创建连接、管理连接、释放连接等操作。

预先创建数据库连接放入连接池，用户在请求时，通过池直接获取连接，使用完毕后，将连接放回池中，避免了频繁的创建和销毁，同时解决了创建的效率。

当池中无连接可用，且未达到上限时，连接池会新建连接。

池中连接达到上限，用户请求会等待，可以设置超时时间。

 4.3 Druid连接池

**Druid 是阿里提供的数据库连接池，是集DBCP 、C3P0 、Proxool 优点于一身的数据库连接池，性能、扩展性、易用性都更好，功能丰富**。 

配置文件信息 

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

 软编码实现

//软编码实现druid
    @Test
    public void TestDruid() throws Exception {
        //1.获取properties对象，存储配置信息
        Properties properties = new Properties();

        //2.通过类加载在器加载配置信息
        InputStream inputStream = poolTest.class.getClassLoader().getResourceAsStream("db.properties");
        properties.load(inputStream);

        //3.创建连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        //4.获取连接对象
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

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

 4.4 HiKari连接池

Hikari（ひかり[shi ga li]） 取自日语，是光的意思，是SpringBoot2.x之后内置的一款连接池，基于 BoneCP （已经放弃维护，推荐该连接池）做了不少的改进和优化，口号是快速、简单、可靠。 

 配置文件

driverClassName=com.mysql.cj.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/study
username=root
password=123456
minimumIdle=10
maximumPoolSize=20

 软编码实现

 @Test
    public void TestHiKari() throws Exception {
        //1.创建properties集合存储配置信息的键和值
        Properties properties = new Properties();

        //2.通过类加载器获取字节输入流对象加载配置文件内容，存储在properties集合中
        InputStream inputStream = poolTest.class.getClassLoader().getResourceAsStream("hikari.properties");
        properties.load(inputStream);

        //3.创建连接池配置信息对象
        HikariConfig hikariConfig = new HikariConfig(properties);

        //4.通过配置信息创建连接池
        HikariDataSource hikariDataSource = new HikariDataSource(hikariConfig);

        //5.获取连接
        Connection connection = hikariDataSource.getConnection();
        System.out.println(connection);

        //6.释放连接
        connection.close();
    }

 5. JDBC工具类封装

用于解决代码冗余问题

* 创建连接池。
* 获取连接。
* 连接的回收。

 ThreadLocal

DK 1.2的版本中就提供java.lang.ThreadLocal，为解决多线程程序的并发问题提供了一种新的思路。使用这个工具类可以很简洁地编写出优美的多线程程序。通常用来在在多线程中管理共享数据库连接、Session等。

ThreadLocal用于保存某个线程共享变量，原因是在Java中，每一个线程对象中都有一个ThreadLocalMap<ThreadLocal， Object>，其key就是一个ThreadLocal，而Object即为该线程的共享变量。

而这个map是通过ThreadLocal的set和get方法操作的。对于同一个static ThreadLocal，不同线程只能从中get，set，remove自己的变量，而不会影响其他线程的变量。

* 在进行对象跨层传递的时候，使用ThreadLocal可以避免多次传递，打破层次间的约束。
* 线程间数据隔离。
* 进行事务操作，用于存储线程事务信息。
* 数据库连接，`Session`会话管理。

1、ThreadLocal对象.get: 获取ThreadLocal中当前线程共享变量的值。

2、ThreadLocal对象.set: 设置ThreadLocal中当前线程共享变量的值。

3、ThreadLocal对象.remove: 移除ThreadLocal中当前线程共享变量的值。

 

 

 封装工具类

package com.itcsdn.sennior.util;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * 将获取回收连接封装给工具类，方便调用，减少代码冗余
 * 在V1.0基础上进行改进，V2.0中一个线程对应一个连接池，保证线程从头到尾都是获取一个连接对象
 * 1.线程维护一个连接池
 * 2.对外提供threadLocal中获取连接的和释放连接的方法
 * */
public class jdbcUntilV2 {
    //创建连接池对象
    private static DataSource dataSource;
    //创建本地连接线程对象，即一个线程维护一个连接
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    static {
        try {
            //1.创建集合存储文件配置信息
            Properties properties = new Properties();
            //2.通过类加载器获取字节输入流加载配置文件内容到集合中
            InputStream inputStream = jdbcUntilV2.class.getClassLoader().getResourceAsStream("db.properties");
            //3.信息存入集合
            properties.load(inputStream);
            //4.获取连接池
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //对外提供获取连接的方法
    public static Connection getConnection() throws SQLException {
        //从本地线程中获取连接对象，如果该线程返回连接为空则还未创建连接对象
        Connection connection = threadLocal.get();
        if (connection == null) {
            //从连接池中获取连接对象
             connection = dataSource.getConnection();
             //连接对象将在进本地线程中，此时每个线程都对应着一个连接池，维护着同一个连接
             threadLocal.set(connection);
        }
        return connection;
    }

    //对外提供释放连接的方法
    public static void releaseConnection() throws SQLException {
        Connection connection = threadLocal.get();
        if (connection != null) {
            threadLocal.remove();
            connection.setAutoCommit(true);
            connection.close();
        }
    }
}

6. DAO封装和baseDAO工具类 

6.1 DAO概念 

> DAO：Data Access Object，数据访问对象。
> 
> Java是面向对象语言，数据在Java中通常以对象的形式存在。一张表对应一个实体类，一张表的操作对应一个DAO对象！
> 
> 在Java操作数据库时，我们会将对同一张表的增删改查操作统一维护起来，维护的这个类就是DAO层。
> 
> DAO层只关注对数据库的操作，供业务层Service调用，将职责划分清楚！ 

 6.2 baseDAO概念

> 基本上每一个数据表都应该有一个对应的DAO接口及其实现类，发现对所有表的操作（增、删、改、查）代码重复度很高，所以可以抽取公共代码，给这些DAO的实现类可以抽取一个公共的父类，复用增删改查的基本操作，我们称为BaseDAO。 

 6.3 BaseDAO搭建

public class basedDao {

    //增、删、改数据
    public int executeUpdate(String sql, Object...objects) throws Exception{
        //通过工具类获取连接
        Connection connection = jdbcUntilV2.getConnection();
        //获取执行sql语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //给占位符填充数据
        if (objects != null && objects.length > 0) {
            for (int i = 0; i < objects.length; i++) {
                preparedStatement.setObject(i + 1, objects[i]);
            }
        }
        int len = preparedStatement.executeUpdate();
        //释放资源
        preparedStatement.close();
        jdbcUntilV2.releaseConnection();
        return len;
    }

    //查询所有数据
    public <T> List<T> executeQuery(Class<T> clazz, String sql, Object...args) throws Exception {
        //1.获取连接
        Connection connection = jdbcUntilV2.getConnection();
        //2.获取执行sql语句对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //3.执行sql语句，为占位符赋值
        if (args != null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
        }
        //4.查询结果并且返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        //获取整张表（该表包含所有元数据，以及列数量）
        ResultSetMetaData metaData = resultSet.getMetaData();
        //获取列数量
        int columnCount = metaData.getColumnCount();

        //用于存储结果集中所有的数据，并返回
        ArrayList<T> list = new ArrayList<>();
        while (resultSet.next()) {
            //将结果集中的每一个结果封装成对象，存入集合中后返回
            T t = clazz.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                //通过resultSet返回当前列的值即返回当前第i列下的值
                Object value = resultSet.getObject(i);

                //获取该列的名字（对应着对象的某个属性）
                String fieldName = metaData.getColumnLabel(i);
                //通过反射获取该列对应的属性
                Field field = clazz.getDeclaredField(fieldName);
                //将该列的私有属性暂时打开
                field.setAccessible(true);
                field.set(t, value);
            }
            list.add(t);
        }
        //释放资源
        resultSet.close();
        preparedStatement.close();
        jdbcUntilV2.releaseConnection();

        return list;
    }

    //查询单条数据
    public <T> T executeQueryBean(Class<T> clazz, String sql, Object...args) throws Exception {
        List<T> list = this.executeQuery(clazz, sql, args);
        if (list == null || list.size() == 0) {
            return null;
        }
        return list.get(0);
    }
}

6.4 BaseDAO应用

 实现员工接口

public interface EmployeeDao {
    /**
     * 将增删改查的操作统一规范
     * */

    //查询所有结果并用集合返回
    List<Employee> selectAll();

    //查询单个结果并返回
    Employee selectById(Integer id);

    //增并返回受影响行数
    int insert(Employee employee);

    //改并返回受影响行数
    int update(Employee employee);

    //删除并返回受影响行数
    int delete(Employee employee);
}

 创建员工dao接口实现类

public class EmployeeDaoImp extends basedDao implements EmployeeDao {
    //1.注册驱动

    //2.获取连接

    //3.通过连接获取执行sql语句的preparedStatement对象

    //4.为占位符赋值

    //5.处理返回结果

    //6.释放资源
    @Override
    public List<Employee> selectAll() {
        String sql = "select id employeeId, name employeeName, age employeeAge, dept_id employeeDept, address employeeAddress from emp";
        try {
            return super.executeQuery(Employee.class, sql, null);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Employee selectById(Integer id) {
        String sql = "select id employeeId, name employeeName, age employeeAge, dept_id employeeDept, address employeeAddress from emp where id = ?";
        try {
            return super.executeQueryBean(Employee.class, sql, id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int insert(Employee employee) {
        int id = employee.getEmployeeId();
        String name = employee.getEmployeeName();
        int age = employee.getEmployeeAge();
        int dept = employee.getEmployeeDept();
        String address = employee.getEmployeeAddress();
        String sql = "insert into emp value (?, ?, ?, ?, ?)";
        try {
            return super.executeUpdate(sql, id, name, age, dept, address);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int update(Employee employee) {
        int id = employee.getEmployeeId();
        String name = employee.getEmployeeName();
        int age = employee.getEmployeeAge();
        int dept = employee.getEmployeeDept();
        String address = employee.getEmployeeAddress();
        String sql = "update emp set id = ?, name = ?, age = ?, dept_id = ?, address = ? where id = ?";
        try {
            return super.executeUpdate(sql, id, name, age, dept, address, id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int delete(Employee employee) {
        int id = employee.getEmployeeId();
        String sql = "delete from emp where id = ?";
        try {
            return super.executeUpdate(sql, id);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}

 7. 事务案列

 @Test
    public void testTransaction(){
        BankDao bankDao = new BankDaoImpl();
        Connection connection=null;
        try {
            //1.获取连接，将连接的事务提交改为手动提交
            connection = JDBCUtilV2.getConnection();
            connection.setAutoCommit(false);//开启事务，当前连接的自动提交关闭。改为手动提交！

            //2.操作减钱
            bankDao.subMoney(1,100);

            int i = 10 / 0;

            //3.操作加钱
            bankDao.addMoney(2,100);

            //4.前置的多次dao操作，没有异常，提交事务！
            connection.commit();
        } catch (Exception e) {
            try {
                connection.rollback();
            } catch (Exception ex) {
                throw new RuntimeException(ex);
            }
        }finally {
            JDBCUtilV2.release();
        }
    }