2.2 数据库-JDBC

r囧r小猫 2024-04-01 16:33 16阅读 0赞

**目录**

2.2.1 JDBC

2.2.1.1 JDBC步骤

2.2.1.2 JDBC API

2.2.1.2.1 DriverManager

2.2.1.2.2 Connection

2.2.1.2.3 Statement

2.2.1.2.4 ResultSet

2.2.1.3 PreparedStatement

2.2.1.3.1 PreparedStatement概述

2.2.1.3.2 预编译

2.2.2 数据库连接池

2.2.2.1 常见的数据库连接池

DBCP

C3P0

\*Druid

2.2.3 补充

2.2.3.1 JDBC 处理 Blob 和 Clob

--------------------

## 2.2.1 JDBC ##

Java DataBase Connectivity Java 数据库连接技术 。

sun公司定义的一套操作所有关系型数据库的规则（接口）。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，运行时的代码其实是驱动jar包中的实现类。

### 2.2.1.1 JDBC步骤 ###

**1.**导入驱动jar包 mysql-connector-java-5.1.37-bin.jar

复制mysql-connector-java-5.1.37-bin.jar到项目的lib目录下

右键-->Add As Library

**2.** 注册驱动(安转驱动)

**3.** 获取数据库连接对象 Connection

**4.** 定义sql

**5.** 获取执行sql语句的对象 Statement

**6.** 执行sql，接受返回结果

**7.** 处理结果

**8.** 释放资源

public class JDBC {
        public static void main(String[] args) throws Exception {
            // 1.注册驱动
            DriverManager.registerDriver(new Driver());
            // 2.建立连接
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");
            // 3.编写sql（在java编写sql 结尾；可以省略）
            String sql = "insert into account values(4,'lucy',1000)";
            // 4.获取sql执行对象
            Statement statement = connection.createStatement();
            // 5.执行sql并返回结果
            int i = statement.executeUpdate(sql);
            // 6.处理结果
            System.out.println(i);
            // 7.释放资源
            statement.close();
            connection.close();
        }
    }

### 2.2.1.2 JDBC API ###

#### 2.2.1.2.1 DriverManager ####

DriverManager用于注册和获取数据库连接对象

**注册驱动**

方式一：使用registerDriver 注册驱动

>     static void registerDriver(Driver driver) // 注册与给定的驱动程序 DriverManager

方式二：使用反射注册驱动

>     Class.forName("com.mysql.jdbc.Driver");

**获取数据库连接**

**格式：**

> static Connection getConnection(String URL, String user, String password)

URL：uniform resource locator 统一资源定位符。本处为数据库连接路径

> 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称
> 
> jdbc:mysql://localhost:3306/mydb
> 
> 如果连接的是本机mysql服务器，并且默认端口是3306，可简写为 jdbc:mysql:/// 数据库名称

user：用户名

password：密码

#### 2.2.1.2.2 Connection ####

Connection用于获取sql语名执行对象和事务的管理 。

**获取sql语句执行对象**

<table style="width:628px;"> 
 <tbody> 
  <tr> 
   <th style="width:229px;">方法</th> 
   <th style="width:398px;">说明</th> 
  </tr> 
  <tr> 
   <td style="width:229px;">Statement createStatement()</td> 
   <td style="width:398px;">创建一个 Statement对象，用于将SQL语句发送到数据库。</td> 
  </tr> 
  <tr> 
   <td style="width:229px;">PreparedStatement prepareStatement(String sql)</td> 
   <td style="width:398px;">创建一个 PreparedStatement对象，用于将参数化的SQL语句发送到数据库。</td> 
  </tr> 
 </tbody> 
</table>

**事务管理**

<table style="width:651px;"> 
 <tbody> 
  <tr> 
   <th style="width:316px;">方法</th> 
   <th style="width:331px;">说明</th> 
  </tr> 
  <tr> 
   <td style="width:316px;">void setAutoCommit(boolean autoCommit)</td> 
   <td style="width:331px;">关闭自动提交（开启事务）【默认值】true：自动提交false：手动提交</td> 
  </tr> 
  <tr> 
   <td style="width:316px;">void commit()</td> 
   <td style="width:331px;">提交事务</td> 
  </tr> 
  <tr> 
   <td style="width:316px;">void rollback()</td> 
   <td style="width:331px;">回滚事务</td> 
  </tr> 
 </tbody> 
</table>

#### 2.2.1.2.3 Statement ####

<table style="width:645px;"> 
 <tbody> 
  <tr> 
   <th style="width:246px;">方法</th> 
   <th style="width:397px;">说明</th> 
  </tr> 
  <tr> 
   <td style="width:246px;">boolean execute(String sql)</td> 
   <td style="width:397px;">可以执行任意的sql（返回值boolean false代表执行的是ddl dml语句 可以理解为该语句返回值为判断执行的是否是查询语句）</td> 
  </tr> 
  <tr> 
   <td style="width:246px;">int executeUpdate(String sql)</td> 
   <td style="width:397px;">执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句</td> 
  </tr> 
  <tr> 
   <td style="width:246px;">ResultSet executeQuery(String sql)</td> 
   <td style="width:397px;">执行DQL（select)语句</td> 
  </tr> 
 </tbody> 
</table>

#### 2.2.1.2.4 ResultSet ####

数据库结果集的数据表，通常通过执行查询数据库的语句生成

<table style="width:586px;"> 
 <tbody> 
  <tr> 
   <th style="width:205px;">方法</th> 
   <th style="width:378px;">说明</th> 
  </tr> 
  <tr> 
   <td style="width:205px;">boolean next()</td> 
   <td style="width:378px;">将光标从当前位置向前移动一行true：表示此行有数据false：表示此行没有数据</td> 
  </tr> 
  <tr> 
   <td style="width:205px;">T getXxx(int 列编号)</td> 
   <td style="width:378px;">根据列的编号获取列的值</td> 
  </tr> 
  <tr> 
   <td style="width:205px;">T getXxx(String 列名)</td> 
   <td style="width:378px;">根据列的name获取列的值</td> 
  </tr> 
  <tr> 
   <td style="width:205px;">Object getObject(String 列名)</td> 
   <td style="width:378px;">根据列的name获取所有列的类型的值</td> 
  </tr> 
 </tbody> 
</table>

### 2.2.1.3 PreparedStatement ###

#### 2.2.1.3.1 PreparedStatement概述 ####

`sql注入问题`

> 正常登录
> 
> login("张三","1234567"); 可以登录进去
> 
> sql注入
> 
> login("' or '1=1","' or '1=1"); 也可以登录进去 显然这个是不合法的

而PreparedStatement 就是用来解决使用Statement拼接sql注入的问题！

PreparedStatements是**预编译的SQL** ,要求SQL语句中所有的参数都要使用 ? 作为占位符

> select \* from user where username = ? and password = ?;  
> insert into user(username,password) values(?,?);  
> update user set username = ? ,password = ? where id = ?;  
> delete from user where id = ?

使用步骤

> 1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar  
> 2. 注册驱动  
> 3. 获取数据库连接对象 Connection  
> 4. 定义sql  
> \* 注意：sql的参数使用？作为占位符。  
> 5. 获取执行sql语句的对象 PreparedStatement Connection.prepareStatement(String sql)  
> 6. 给？赋值：  
> 7. 执行sql，接受返回结果，不需要传递sql语句  
> 8. 处理结果  
> 9. 释放资源

//注册驱动 
            Class.forName("com.mysql.jdbc.Driver");
             //获取数据库连接对象 Connection
    		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "123456");
    		// 定义sql  使用？占位符表示对应数据
    		String sql = "insert into account (username,password,sex,age) values(?,?,?,?)";
    		// 获取预编译声明对象
    		// 与statement对象不同的是 预编译对象需要在创建时就将sql传入
    		PreparedStatement ps = con.prepareStatement(sql);
            //按位置传入值
            ps.setObject(1, "张三");
    		ps.setObject(2, "123456");
    		ps.setObject(3, "男");
    		ps.setObject(4, 18);
    		// 注意：不要调用statement提供的方法 传入sql执行
    		// 那样预编译就会失效 导致占位符没有替换
    		int update = ps.executeUpdate();
    		ps.close();
    		con.close();

#### 2.2.1.3.2 预编译 ####

SQL预编译指的是数据库驱动在发送SQL语句和参数给DBMS之前，对SQL语句进行编译，DBMS就不需要再重新编译。

为什么需要预编译？

第一个因素就是为了防止SQL注入问题。

另外，JDBC使用PreparedStatement 来预编译sql语句，预编译阶段可以**优化SQL的执行**。越复杂的SQL，编译的复杂度越大，预编译阶段可以合并多次操相同的操作，可以重复利用预编译语句对象（把sql预编译后产生的PreparesStatement对象缓存，下次对于同一个sql,可以直接使用）。

#### 2.2.1.3.3 Statement 和 PreparedStatement的区别 ####

① PreparedStatement 接口代表预编译的语句，它主要的优势在于可以减少 SQL 的编译错误并增加 SQL 的安全性（减少 SQL 注射攻击的可能性）

② PreparedStatement 中的 SQL 语句是可以带参数的，避免了用字符串连接拼接 SQL 语句的麻烦和不安全

③ 当批量处理 SQL 或频繁执行相同的查询时， PreparedStatement 有明显的性能上的优势，由于数据库可以将编译优化后的 SQL 语句缓存起来，下次执行相同结构的语句时就会很快（不用再次编译和生成执行计划）

#### 2.2.1.3.4 JDBC操作数据库，提升读取、更新数据的性能 ####

**问题**

在使用MySQL的JDBC时，如果查询结果集过大，使用一次查询，可能会出现Java.lang.OutOfMemoryError: Java heap space问题，因为DB服务器端一次将查询到的结果集全部发送到Java端保存在内存中而造成OOM。

**解决办法**

要提升读取数据的性能，可以指定通过结果集（ResultSet）对象的 setFetchSize() 方法指定每次抓取的记录数（典型的空间换时间策略）；

要提升更新数据的性能 可以使用 PreparedStatement 语句构建批处理，将若干 SQL 语句置于一个批处理中执行

## 2.2.2 数据库连接池 ##

在开发中“获得连接”或“释放资源”是非常消耗系统资源的两个过程，为了解决此类性能问题，通常情况我们采用连接池技术，来共享连接Connection。这样我们就不需要每次都创建连接、释放连接了，这些操作都交给了连接池 。

### 2.2.2.1 常见的数据库连接池 ###

#### DBCP ####

DBCP是一个开源的连接池，是Apache Common成员之一，在早期企业开发中比较常见，现在已经很少使用！

配置文件必须放在src目录下

**c3p0-config.xml ：**

<?xml version="1.0" encoding="UTF-8"?>
    <c3p0-config>
    	
    	<default-config>
    		
    		<property name="jdbcUrl">jdbc:mysql://localhost:3306/mydb?characterEncoding=UTF-8</property>
    		<property name="driverClass">com.mysql.jdbc.Driver</property>
    		<property name="user">root</property>
    		<property name="password">123456</property>
    		
    		
            <property name="acquireIncrement">5</property>
            
            <property name="initialPoolSize">20</property>
            
            <property name="maxPoolSize">25</property>
            
            <property name="minPoolSize">5</property>
    	</default-config>
    </c3p0-config>

public class ComboPooledDataSource{
        public static void main(String[] args){
            //1.创建数据库连接池对象
            DataSource ds  = new ComboPooledDataSource();
            //2. 获取连接对象
            Connection conn = ds.getConnection();
            System.out.println(conn);
        }
    }

#### C3P0 ####

C3P0是一个开放源代码的JDBC连接池,c3p0拥有比DBCP更丰富的配置属性

**c3p0-config.xml:**

<?xml version="1.0" encoding="UTF-8"?>
    <c3p0-config>
      <default-config>
        
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/day06</property>
        <property name="user">root</property>
        <property name="password">root</property>
        
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">10</property>
        <property name="acquireIncrement">5</property>
        <property name="maxPoolSize">100</property>
        <property name="maxIdleTime">30</property>
      </default-config>
    </c3p0-config>

public class C3P0Util {
        //使用ComboPooledDataSource来生成DataSource的实例
        private static DataSource dataSource = new ComboPooledDataSource();
     
        //从连接池中获取连接
        public static Connection getConnection() {
            try {
                return dataSource.getConnection();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                throw new RuntimeException();
            }
        }
     
        //释放连接回连接池
        public static void release(Connection conn, Statement stmt, ResultSet rs) {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
                conn = null;
            }
        }
    }

#### **\***Druid ####

数据库连接池实现技术，由阿里巴巴提供。是淘宝与支付宝专用的数据库连接池，支持所有JDBC兼容的数据库。也是现在主流的数据库连接池，重点掌握。

**druid.properties**

url=jdbc:mysql://localhost:3306/users?useUnicode=true&characterEncoding=UTF-8
    #这个可以缺省的，会根据url自动识别
    driverClassName=com.mysql.jdbc.Driver
    username=root
    password=123456
    
    ##初始连接数，默认0
    initialSize=10
    #最大连接数，默认8
    maxActive=30
    #最小闲置数
    minIdle=10
    #获取连接的最大等待时间，单位毫秒
    maxWait=2000
    #缓存PreparedStatement，默认false
    poolPreparedStatements=true
    #缓存PreparedStatement的最大数量，默认-1（不缓存）。大于0时会自动开启缓存PreparedStatement，所以可以省略上一句设置
    maxOpenPreparedStatements=20

public static void main(String[] args) throws Exception {
    		// druid连接池不会默认加载 需要自己加载配置文件
    		Properties p = new Properties();
    		InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
    		p.load(is);
    		// 创建Druid连接池的方式
    //方式一
    		//通过druid连接工厂加载配置文件对象创建
    		DataSource createDataSource = DruidDataSourceFactory.createDataSource(p);
    		Connection con1 = createDataSource.getConnection();
    		System.out.println(con1);
    //方式二
    		//new druid连接池对象 通过set赋值创建
    		DruidDataSource dds=new DruidDataSource();
    		dds.setUrl(p.getProperty("url"));
    		dds.setDriverClassName(p.getProperty("driverClassName"));
    		dds.setUsername(p.getProperty("username"));
    		dds.setPassword(p.getProperty("password"));
    		DruidPooledConnection con2 = dds.getConnection();
    	}

## 2.2.3 补充 ##

### 2.2.3.1 JDBC 处理 Blob 和 Clob ###

**BLOB定义**

BLOB (binary large object)，[二进制][Link 1]大对象，是一个可以存储二进制文件的容器。在计算机中，BLOB常常是数据库中用来存储二进制文件的字段类型。BLOB是一个大文件，典型的BLOB是一张图片或一个声音文件，由于它们的尺寸，必须使用特殊的方式来处理（例如：上传、下载或者存放到一个数据库）。

**CLOB 定义**

Character Large Object，SQL 类型 CLOB 在 JavaTM 编程语言中的映射关系。SQL CLOB 是内置类型，它将字符大对象 (Character Large Object) 存储为数据库表某一行中的一个列值。默认情况下，驱动程序使用 SQL locator(CLOB) 实现 Clob 对象，这意味着 CLOB 对象包含一个指向 SQL CLOB 数据的逻辑指针而不是数据本身。Clob 对象在它被创建的事务处理期间有效。

在一些数据库系统里，也使用Text 作为CLOB的别名，比如SQL Server。

>     通常像图片、文件、音乐等信息就用BLOB字段来存储，先将文件转为二进制再存储进去。
>     而像文章或者是较长的文字，就用CLOB存储，这样对以后的查询更新

下面以 MySQL 数据库为例，创建一个张有三个字段的用户表，包括编号（id）、 姓名（name）和照片（photo），建表语句如下：

create table tb_user
    (
    id int primary key auto_increment,
    name varchar(20) unique not null,
    photo longblob
    );

import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    class JdbcLobTest {
        public static void main(String[] args) {
            Connection con = null;
                try {
    // 1. 加载驱动（Java6 以上版本可以省略）
                Class.forName("com.mysql.jdbc.Driver");
    // 2. 建立连接
                con =DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root",
    "123456");
    // 3. 创建语句对象
                PreparedStatement ps = con.prepareStatement("insert intotb_user values (default, ?, ?)");
                ps.setString(1, "骆昊"); 
    // 将 SQL 语句中第一个占位符换成字符串    
                try (InputStream in = new FileInputStream("test.jpg"))
                    { // Java 7 的 TWR
                    ps.setBinaryStream(2, in); 
    // 将 SQL 语句中第二个占位符换成二进制流
    // 4. 发出 SQL 语句获得受影响行数
                    System.out.println(ps.executeUpdate() == 1 ? "插入成功" : "插入失败");
                    } catch(IOException e) {
            System.out.println("读取照片失败!");
            }
        } catch (ClassNotFoundException | SQLException e) {
         // Java7 的多异常捕获
        e.printStackTrace();
        } finally { 
            // 释放外部资源的代码都应当放在 finally 中保证其能够得到执行
        try {
            if(con != null && !con.isClosed()) {
                con.close(); // 5. 释放数据库连接
                con = null; // 指示垃圾回收器可以回收该对象
            }
        } catch (SQLException e) {
        e.printStackTrace();
        }
        }
    }
    }

[Link 1]: https://so.csdn.net/so/search?q=%E4%BA%8C%E8%BF%9B%E5%88%B6&spm=1001.2101.3001.7020