JDBC 进阶操作

Demo_Null

发布于 2020-09-29 23:31:20

8470

发布于 2020-09-29 23:31:20

文章被收录于专栏：Java 学习Java 学习

本篇为 JDBC 的进阶笔记，对于 JDBC 不太清楚的老铁可以先看看《听说你 JDBC 已经忘了，安排，先来看看基本操作》

1.1 结果集处理

1.1.1 处理 Blob 类型数据

? 概述

??Blob(Binary Long Object)是二进制长对象的意思，Blob 列通常用于存储大文件，典型的 Blob 内容是一张图片或一个声音文件，由于它们的特殊性，必须使用特殊的方式来存储。使用 Blob 列可以把图片、声音等文件的二进制数据保存在数据库里，并可以从数据库里恢复指定文件。

??如果需要将图片插入数据库，显然不能直接通过普通的 SOL 语句来完成，因为有一个关键的问题，Blob 常量无法表示。所以将 Blob 数据插入数据库需要使用 PreparedStatement，该对象有一个方法：setBinaryStream(int parameterlIndex，InputStream x)，该方法可以为指定参数传入二进制输入流，从而可以实现将 Blob 数据保存到数据库的功能。

??当需要从 ResultSet 里取出 Blob 数据时，可以调用 ResultSet 的 getBlob(int columnIndex) 方法，该方法将返回一个 Blob 对象，Blob 对象提供了getBinaryStream() 方法来获取该 Blob 数据的输入流，也可以使用 Blob 对象提供的 getBytes() 方法直接取出该 Blob 对象封装的二进制数据。

? 数据库设计

??MySQL 数据库里的 blob 类型最多只能存储 64KB 内容，这基本不够满足实际用途。所以使用 mediumblob 类型，该类型的数据列可以存储 16MB 内容。 ? 其他 blob 类型

? 示例

/**
 * Created with IntelliJ IDEA.
 *
 * @author Demo_Null
 * @date 2020/7/12
 * @description 处理 Blob 类型数据
 */
public class BlobDemo {
    public static void main(String[] args) {
        String inUrl = "C:\\Users\\softw\\Desktop\\001.jpg";
        String outUrl = "C:\\Users\\softw\\Desktop\\myimg";

        int i = img2blob(inUrl);
        blob2img(i, outUrl);

    }

    /**
     * blob 转为 img 写出到本地
     * @author Demo_Null
     * @date 2020/7/13
     * @param id
     * @param url  
     * @return void
     **/
    public static void blob2img(int id, String url) {
        try {
            // 定义 sql
            String sql = "select * from img where id = ?";

            // 获取数据库连接
            Connection connection = JDBCUtils.getConnection();

            // 获取执行器并设置参数
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();

            // 处理结果集
            if (!resultSet.next()) {
                return;
            }
            Blob img = resultSet.getBlob("img");
            String fileName = resultSet.getString("name");

            // 获取流
            InputStream binaryStream = img.getBinaryStream();
            BufferedInputStream bis = new BufferedInputStream(binaryStream);

            File file = new File(url);
            if (!file.exists()) {
                file.mkdirs();
            }
            FileOutputStream fis = new FileOutputStream(file + File.separator + fileName);
            BufferedOutputStream bos = new BufferedOutputStream(fis);

            // 写出文件
            int len = 0;
            byte[] bytes = new byte[8 * 1024];
            while ((len = bis.read(bytes)) != -1) {
                bos.write(bytes, 0, len);
            }

            // 释放资源
            bos.close();
            bis.close();
            JDBCUtils.close(resultSet, preparedStatement, connection);
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }


    }

    /**
     * img 转为 blob 存入数据库
     * @author Demo_Null
     * @date 2020/7/13
     * @param url  
     * @return int
     **/
    public static int img2blob(String url) {
        try {
            // 获取文件流
            File file = new File(url);
            FileInputStream fis = new FileInputStream(file);
            String fielNmae = file.getName();

            String sql = "insert into img values (null, ?, ?)";

            // 获取数据库连接
            Connection connection = JDBCUtils.getConnection();

            // 获取执行器并设置参数, Statement.RETURN_GENERATED_KEYS 返回自增 id
            PreparedStatement preparedStatement = 
            							connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, fielNmae);
            preparedStatement.setBinaryStream(2, fis);

            // 执行 SQL
            preparedStatement.executeUpdate();
			
			// 获取自增 id
            ResultSet resultSet = preparedStatement.getGeneratedKeys();
            int id = 0;
            if (resultSet.next()) {
                id = resultSet.getInt(1);
            }


            // 释放资源
            JDBCUtils.close(preparedStatement, connection);

            return id;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
}

1.2 JDBC 事务处理

1.2.1 事务支持

??在我另一篇文章《听说你想了解 MySQL 事务？安排，给你安排得明明白白！》中详细讲解了 MySQL 事务，那么 JDBC 是否支持事务呢？答案是 JDBC 连接也提供了事务支持，JDBC 连接的事务支持由 Connection 提供，Connection 默认打开自动提交，即关闭事务，在这种情况下，每条 SOL 语句一旦执行，便会立即提交到数据库，永久生效，无法对其进行回滚操作。可以调用 Connection 的 setAutoCommit() 方法来关闭自动提交，开启事务。

1.2.2 JDBC 使用事务

? 开启事务

??一旦事务开始之后，程序可以像平常一样创建 Statement 对象，创建了 Statement 对象之后，可以执行任意多条 SQL 语句，这些SQL语句虽然被执行了，但这些SQL语句所做的修改不会生效，因为事务还没有结束。

connection.setAutoCommit(false);

? 提交事务

??当所有的 SQL 都执行完毕后，我们就可以提交事务了。

connection.commit();

? 回滚事务

??当 Connection 遇到一个未处理的 SQLException 异常时，系统将会非正常退出，事务也会自动回滚。但如果程序捕获了该异常，则需要在异常处理块中显式地回滚事务。

connection.rollback();

? 创建保存点

??通常来说，设置保存点时没有必要指定名称，因为 Connection 回滚到指定保存点时，并不是根据名字回滚的，而是根据保存点对象回滚的，Connection 提供了 rollback(Savepoint savepoint) 方法回滚到指定保存点。

// 创建一个保存点
Savepoint savepoint = connection.setSavepoint();

// 以指定名字来创建一个保存点
Savepoint savepoint = connection.setSavepoint(String name);

1.2.3 示例

public class JDBC_Transaction {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接
            connection = JdbcUtils.getConnection();
            // 开启事务
            connection.setAutoCommit(false);

            String sql = "update account set balance = balance + ? where name = ?";
            preparedStatement = connection.prepareStatement(sql);
            
            // 转出操作
            preparedStatement.setInt(1, -500);
            preparedStatement.setString(2, "tom");
            preparedStatement.executeUpdate();
			
			// 插入异常
			int a = 1 / 0;
            
            // 转入操作
            preparedStatement.setInt(1, 500);
            preparedStatement.setString(2, "jack");
            preparedStatement.executeUpdate();
            
     		// 提交事务
            connection.commit();
        } catch (Exception e) {
            if (connection != null) {
                try {
                	// 回滚
                    connection.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
        } finally {
        	// 释放资源
            JdbcUtils.close(preparedStatement,connection);
        }
    }
}

1.3 数据库连接池

1.3.1 什么是数据库连接池

??与线程池类似，数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。

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

? 最小连接数：是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。

? 最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过此数，后面的数据库连接请求将被加入到等待队列中，这会影响之后的数据库操作。

? 最小连接数与最大连接数差距：最小连接数与最大连接数相差太大，那么最先的连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，它将被放到连接池中等待重复使用或是空闲超时后被释放。

1.3.2 C3P0

? 概述

??C3P0 是一个开源的 JDBC 连接池，它实现了数据源和 JNDI 绑定，支持 JDBC3 规范和 JDBC2 的标准扩展，有自动回收空闲连接功能。目前使用它的开源项目有 Hibernate、Spring 等。

? 导包

??想要使用 C3P0 需要导入 c3p0.jar、 mchange-commons-java.jar 两个 jar 包，其中 mchange-commons-java.jar 是 C3P0 的辅助包，没有这个包系统启动的时候会报 classnotfoundexception，这是 c3p0-0.9.2版本后分离出来的包，0.9.1 的时候还是一个包就搞定的。注意：数据库驱动包也需要导入。

<!-- maven 坐标 -->
<dependencies>
	<dependency>
		<groupId>com.mchange</groupId>
		<artifactId>c3p0</artifactId>
		<version>0.9.5.4</version>
	</dependency>
	
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>8.0.18</version>
	</dependency>
</dependencies>

? 配置文件

??C3P0 的配置文件有两种形式：c3p0.properties 或者 c3p0-config.xml，一般使用 c3p0-config.xml，将其放在 src 目录下，会自动加载配置文件。如果使用 c3p0.properties 则需要自己读取配置并使用 datasource.setXxx() 设置参数。

<c3p0-config>
    <!-- 使用默认的配置读取连接池对象 -->
    <default-config>
        <!--  连接参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://cd-cdb-m4d564v8.sql.tencentcdb.com:62421/mydatabase</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <!-- 连接池参数 -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">10</property>
        <property name="checkoutTimeout">3000</property>
    </default-config>

    <!-- 使用指定的配置读取连接池对象 -->
    <named-config name="otherc3p0">
        <!--  连接参数 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://cd-cdb-m4d564v8.sql.tencentcdb.com:62421/test</property>
        <property name="user">work</property>
        <property name="password">work</property>

        <!-- 连接池参数 -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">8</property>
        <property name="checkoutTimeout">1000</property>
    </named-config>
</c3p0-config>

? c3p0 的使用

/**
 * Created with IntelliJ IDEA.
 *
 * @author Demo_Null
 * @date 2020/7/13
 * @description C3P0 演示代码
 */
public class C3P0Demo {
    public static void main(String[] args) throws SQLException {
        // 通过默认配置获取 datasource
        // DataSource dataSource = new ComboPooledDataSource();

		// 通过指定配置获取 datasource
        DataSource dataSource = new ComboPooledDataSource("otherc3p0");

        // 获取数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        
        // 归还连接到数据库连接池
        connection.close();
    }
}

1.3.3 Druid

? 概述

??Druid 连接池是阿里巴巴开源的数据库连接池项目。Druid 连接池为监控而生，内置强大的监控功能，监控特性不影响性能。功能强大，能防 SQL 注入，内置 Loging 能诊断 Hack 应用行为。阿里巴巴官方文档说：Druid 是 Java 语言中最好的数据库连接池。

? 导包

??使用 druid 数据库连接池需要导入 druid.jar，注意：数据库驱动 jar 也需要导入

<dependencies>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.37</version>
	</dependency>
	
	<dependency>
		<groupId>com.alibaba</groupId>
		<artifactId>druid</artifactId>
		<version>1.1.12</version>
	</dependency>
</dependencies>

? 配置文件

# 此项可以不配，通过 url 可以知道
#driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://cd-cdb-m4d564v8.sql.tencentcdb.com:62421/mydatabase
username=root
password=root
#initialSize=5
#maxActive=10
#maxWait=3000

? druid 的使用

/**
 * Created with IntelliJ IDEA.
 *
 * @author Demo_Null
 * @date 2020/7/13
 * @description druid 演示代码
 */
public class DruidDemo {
    public static void main(String[] args) throws Exception {
        // 读取配置文件
        InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");

        // 加载配置文件
        Properties properties = new Properties();
        properties.load(is);

        // 通过工厂创建 datasource
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

        // 获取数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        // 归还连接
        connection.close();
    }
}

1.3.4 DBCP

? 概述

??DBCP(DataBase connection pool)数据库连接池是 apache 上的一个 Java 连接池项目。DBCP 通过连接池预先同数据库建立一些连接放在内存中(即连接池中)，应用程序需要建立数据库连接时直接到从接池中申请一个连接使用，用完后由连接池回收该连接，从而达到连接复用，减少资源消耗的目的。

? 导包

??如果需要使用 DBCP 连接池，则应在系统中增加 commons-dbcp.jar：连接池的实现；commons-pool.jar：连接池实现的依赖库，这两个 jar 包。可能还需要 commons-logging.jar。

? 配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://cd-cdb-m4d564v8.sql.tencentcdb.com:62421/mydatabase
username=root
password=root

# 初始连接数量
initialSize=5
# 最大连接数量
maxActive=30
# 最长等待时间
maxWaitMillis=1000

? DBCP 的使用

/**
 * Created with IntelliJ IDEA.
 *
 * @author Demo_Null
 * @date 2020/7/13
 * @description DBCP 演示代码
 */
public class DBCPDemo {
    public static void main(String[] args) throws Exception {
        // 读取配置文件
        InputStream is = DBCPDemo.class.getClassLoader().getResourceAsStream("dbcp.properties");

        // 加载配置文件
        Properties properties = new Properties();
        properties.load(is);

        // 通过工厂创建 datasource
        DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);

        // 获取数据库连接
        Connection connection = dataSource.getConnection();
        System.out.println(connection);

        // 归还连接
        connection.close();
    }
}

1.4 JdbcTemplate 简单使用

1.4.1 概述

??JDBC 已经能够满足大部分用户最基本的需求，但是在使用 JDBC 时，必须自己来管理数据库资源如：获取 PreparedStatement，设置 SQL 语句参数，关闭连接等步骤。? JdbcTemplate 是 Spring 对 JDBC 的封装，目的是使 JDBC 更加易于使用。JdbcTemplate 是 Spring 的一部分。JdbcTemplate 处理了资源的建立和释放。他帮助我们避免一些常见的错误，比如忘了总要关闭连接。他运行核心的 JDBC 工作流，如 Statement 的建立和执行，而我们只需要提供 SQL 语句和提取结果。

1.4.2 使用步骤

? 导包

??想要使用 Spring 封装好的 JDBC 需要导入 spring-beans.jar、spring-core.jar、spring-jdbc.jar、spring-tx.jar、spring-jcl.jar 五个 jar 包，使用 maven 可以只写 spring-jdbc 的坐标，其他相关依赖会自动导入。

<dependencies>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>5.1.40</version>
	</dependency>
	
	<dependency>
		<groupId>com.mchange</groupId>
		<artifactId>c3p0</artifactId>
		<version>0.9.5.2</version>
	</dependency>
	
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>5.2.6.RELEASE</version>
	</dependency>
</dependencies>

? CRUD

update()：执行 DML 语句。增、删、改语句

queryForMap()：查询结果将结果集封装为 map 集合，将列名作为 key，将值作为 value 将这条记录封装为一个 map 集合。注意：这个方法查询的结果集长度只能是 1

queryForList()：查询结果将结果集封装为 list 集合。注意：将每一条记录封装为一个 Map 集合，再将 Map 集合装载到 List 集合中

query()：查询结果，将结果封装为 JavaBean 对象。参数：RowMapper，一般我们使用 BeanPropertyRowMapper 实现类。可以完成数据到 JavaBean 的自动封装 new BeanPropertyRowMapper<T>(T.class)

queryForObject：查询结果，将结果封装为对象。一般用于聚合函数的查询。

queryForObject(sql,BeanPropertyRowMapper<T>(T.class))：将一条记录封装为javabean

1.4.3 示例

/**
 * Created with IntelliJ IDEA.
 *
 * @author Demo_Null
 * @date 2020/7/13
 * @description JdbcTemplate 演示
 */
public class JdbcTemplateDemo {
    public static void main(String[] args) {
    	// 此处的 datasource 就是前文数据库连接池中获取的 datasources
        JdbcTemplate jdbcTemplate = new JdbcTemplate(JdbcUtils.dataSource);

        String sql = "select * from img where id = ?";

        List<Img> imgList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<Img>(Img.class), 7);
        System.out.println(imgList);
    }
}