JDBC理解

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

JDBC

执行过程

• 导入MySQL驱动包，加载数据库驱动

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

• 获取数据库连接Connection;

Connection conn = DriverManager .getConnection (url, username, password);

• 定义SQL语句

String sql = “update…”

• 获取执行SQL对象：创建Statement/PreparedStatement对象;

Statement stmt = conn .createStatement ();

• 执行SQL语句;

stmt.executeUpdate(sql);

• 获得结果集ResultSet，数据封装处理等；

• 回收数据库资源,包括关闭ResultSet, PreparedStatement Connection

Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try{
    // 注册驱动
    Class.forName("com.mysql.jdbc.Driver");
    //获取与数据库连接对象 Connection
    String url = "jdbc:mysql://127.0.0.1:3306/db1";
    String username = "root";
    String password = "1234";
    connection = DriverManager.getConnection(url, username, password);
    //获取执行sql语句的statement对象
    statement=connection.createStatement();
    //执行sql语句，获取结果集
    resultSet = statement.executeQuery("select * from users");
    //便利结果集，获取数据
    while(resultSet.next()){
        System.out.println(resultSet.getString(1));
        System.out.println(resultSet.getString(2));
    }
} catch (SQLException e){
    e.printStackTrace();
} catch (ClassNotFoundException e){
    e.printStackTrace();
} finally {
    //关闭资源，后调用的先关闭
    //关闭之前，要判断对象是否存在
    if(resultSet != null){
        try{
            resultSet.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    if(statement != null){
        try{
            statement.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
    
    if(connection != null){
        try{
            connection.close();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }
}

JDBC API

DriverManager

• JDBC的DriverManager是一个工厂类，通过它创建数据库连接

• 当JDBC的Driver类被加载进来时，它会⾃⼰注册到DriverManager类⾥⾯

• 然后我们会把数据库配置信息传成DriverManager.getConnection()⽅法，DriverManager会使用注册到它里面的驱动来获取数据库连接，并返回给调⽤的程序

Connection 对象

客户端与数据库所有的交互都是通过Connection来完成

• 获取执行 SQL 的对象

• 管理事务

获取执行SQL对象

    //创建向数据库发送sql的statement对象
    Statement createStatement()
    //创建向数据库发送预编译的sql的prepareStatement：防止sql注入
    PreparedStatement prepareStatement(sql) 
    //创建执行存储过程的CallableStatement
    CallableStatement prepareCall(sql)

事务管理

    // 开启事务 ： begin; 或者 START TRANSACTION;
    conn.setAutoCommit(false)
    // 提交事务 ： commit();
    conn.commit();
    // 回滚事务 ： rollback();
    conn.rollback();
    // MySQL 默认是自动提交事务

Statement

用于向数据库发送sql语句，对数据库的增删改查都可以通过此对象发送sql语句完成

Statement stmt = conn.createStatement();

    //查询
    executeQuery(String sql)
    // 增删改
    executeUpdate（String sql)
    //任意sql语句都可使用
    execute(String sql)
    // 把多条sql语句放进同一批处理中
    addBatch(String sql)
    //向数据库发送一批sql语句执行
    executeBatch()

PreparedStatement

• 预编译sql，性能高
• 防止sql注入

缺点

不能直接执行in条件语句

sql 注入

sql注入就是用户输入的数据与sql语句进行了拼接。

SQL 注入是通过操作输入来修改事先定义好的 SQL 语句，用以达 到执行代码对服务器进行攻击的方法。

// 获取 PreparedStatement 对象
// SQL 语句中的参数值，使用？占位符替代
String sql = "select * from user where username = ? and password = ?" ;
// 通过 Connection 对象获取，并传入对应的 sql 语句
PreparedStatement pstmt = conn . prepareStatement ( sql );
//设置参数值
//PreparedStatement 对象： setXxx( 参数 1 ，参数 2) ：给 ? 赋值
 pstmt.setString(1,name);
 pstmt.setString(2,pwd);
//执行 SQL 语句
executeUpdate(); // 执行 DDL 语句和 DML 语句
executeQuery(); // 执行 DQL 语句

获取自增主键

resultSet = prepareStatement.getGeneratedKeys();

@Test
public void test() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    try {
        connection = JdbcUtils.getConnection();
        String sql = "INSERT INTO test(name) VALUES(?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, "ouzicheng");
        if (preparedStatement.executeUpdate() > 0) {
            //获取到⾃动主键列的值
            resultSet = preparedStatement.getGeneratedKeys();
            if (resultSet.next()) {
                int id = resultSet.getInt(1);
                System.out.println(id);
            }
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JdbcUtils.release(connection, preparedStatement, null);
    }

ResultSet

ResultSet（结果集对象）代表了sql执行的结果

当Statement对象执行executeQuery()时，会返回一个ResultSet对象

ResultSet对象维护了一个数据行的游标(集合，简单理解为指针)，调用ResultSet.next()方法，可以让游标指向具体的数据行，进行获取该行的数据

//循环判断游标是否时最后一行
while(rs.next()){
    //获取数据
    rs.getXXX(参数)
}

批处理

批处理的两个方式

• Statement
  • 优点：可以向数据库发送不同的sql语句
  • 缺点：sql没有预编译；仅参数不同的sql，需要重复写多条sql
• PreparedStatement
  • 优点：sql语句预编译‘对同一种类型的sql语句，不需要编写多条语句
  • 缺点：不能发送不同类型的sql语句

执行

• 添加到批处理中：addBatch()
• 执行批处理：exexuteBatch , 返回一个int[]数组，该数组代表各个sql的返回值
• 清空 clearBatch()

JDBC工具类

public class JDBCUtils {

    // 为什么是静态的呢，因为只有静态的变量才能被静态代码块所访问，被静态方法所访问
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    /**
     * 文件的读取，只需要读取一次即可拿到这些值。 使用静态代码块
	 */
    static {

        // 读取资源文件，获取值。
        try {
            // 1. 创建Properties集合类
            Properties properties = new Properties();

            //获取src路径下的文件的方式--->ClassLoader 类加载器 加载字节码文件进内存
            //获取配置⽂件的读⼊流
            //JDBCUtils.class是获得当前对象所属的class对象
            //getClassLoader()是取得该Class对象的类装载器
            //getResourceAsStream(“jdbc.properties”)通过类加载器的方法加载资源,返回的是字节流
            InputStream inputStream =
                JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties")

            // 2. 加载文件
            properties.load(inputStream);

            // 3. 获取数据，赋值
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
            // 4. 注册驱动
            Class.forName(driver);

        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }

    }


    /**
 	 * 获取连接
 	 * @return  连接对象
 	 */
    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(url,user,password);
    }

    /**
	 * 释放资源  两个参数
	 * @param statement
	 * @param connection
	 */
    public static void close(Statement statement, Connection connection){
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

    /**
	 * 释放资源		三个参数
	 * @param resultSet
	 * @param statement
	 * @param connection
	 */
    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

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

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

JDBC配置文件

# 这里的IP地址和端口号都省略了，因为默认就是 localhost:8080
# 用户名和密码修改成自己数据库的用户名和密码
url=jdbc:mysql:///jdbc?characterEncoding=utf-8
user=root
password=123
driver=com.mysql.jdbc.Driver

Statement ,PreparedStatement，CallableStatement的区别

Statement是父接口，PreparedStatement，CallableStatement是子接口。

• PreparedStatement是预编译的SQL语句，效率高于Statement。

• PreparedStatement支持?占位符操作符，相对于Statement更加灵活。

• PreparedStatement可以防止SQL注入，安全性高于Statement。

• CallableStatement适用于执行存储过程。

事务

事务是业务上的一个逻辑单元，它能够保证其中对数据所有的操作，要么成功，要么不执行。

一个逻辑工作单元必须有四个属性，称为原子性、一致性、隔离性和持久性 (ACID) 属性。 Connection类中提供了4个事务处理方法:

setAutoCommit(Boolean autoCommit):设置是否自动提交事务,默认为自动提交,即为true,通过设置false禁止自动提交事务;

commit():提交事务;

rollback():回滚事务.

savepoint:保存点,如果在某地方出错了，设置中间点，回滚到出错之前的即可

	注意：savepoint不会结束当前事务，普通提交和回滚都会结束当前事务的

元数据

元数据就是数据库，表，列的定义信息

数据连接池

数据连接池就是听过链接

理由：数据库的连接的建立和关闭非常消耗资源，频繁打开关闭连接造成系统性能地下

原理

• 服务器启动时会建立一定数量的连接池，并一直维持不少于此数目的连接池。
• 客户端程序需要连接时，池驱动程序会返回一个未使用的池连接并将其记为忙。
• 如果当前没有空闲连接，池驱动程序就新建一定数量的连接，新建连接的数量由配置参数决定。
• 当使用池连接调用完成后，池驱动程序将此连接表记为空闲，其他调用可以使用这个连接

Druid数据库连接池

druid.properties配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/jdbc?characterEncoding=utf-8
username=root
password=itcast
# 初始化的连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

• 从数据库连接池工厂中获取连接池对象:
  • 数据库连接池工厂 -> DruidDataSourceFactory。
  • 获取连接池对象 -> createDataSource(Properties 对象)。

JDBC中大数据量的分页

mysql语法

-- start 偏移量，不设置就是从0开始 = (currentPage-1)*linSize
-- length 长度，取多少数据
SELECT *

FROM 表名

LIMIT [START], length;

oracle语法

分页原理：

• 子查询查出前n行数据，ROWNUM产生前n行的行号

• 使用子查询产生ROWNUM的行号，通过外部的筛选出想要的数据

例如：子查询查出前10条数据：ROWNUM<=10；外部筛选出后面5条数据：ROWNUM>5，这样就获取到后面5条数据

  -- linSize 每页显示数据的行数
  -- currentPage 当前所在页
  SELECT *FROM (
	  -- ROWNUM 产生行号
      SELECT 列名,列名,ROWNUM rn

      FROM 表名

      WHERE ROWNUM<=(currentPage*lineSize)) temp  

  WHERE temp.rn>(currentPage-1)*lineSize;

MySQL和Oracle区别

Oracle支持大并发，大访问量，是OLTP(On-Line Transaction Processing联机事务处理系统)最好的工具

应用场景

对于Mysql数据库：
①Mysql属于轻量级数据库，小巧，免费（开源的），使用方便。
②Mysql数据库的应用场景：大都集中于互联网方向，因为免费，安装使用简便快捷，深受广大互联网公司的喜爱。

对于Oracle数据库：
①Oracle：大型数据库软件，收费，支撑体系完善，强大，安全性高（适用于服务器比较强大的单节点或者集群环境）
②Oracle数据库的应用场景：大都集中于一些大型企业，一些传统行业的数据化业务中，比如：银行、金融这一类，对于可用性，安全性，健壮性，实时性要求极高的业务。

相同点

• 都是关系型数据库管理系统
• 都是目前比较流行的数据库

不同点

规模

• Oracle是大型数据库而Mysql是中小型数据库
  Oracle的价格非常高而Mysql是开源免费的；
  Oracle的市场占有率达到40%，Mysql只有20%左右；

数据量

• Oracle单表数据量，可以千万，甚至上亿的规模；
• Mysql单表数据量，最好控制在百万级别;

并发性

• Oracle使用行级别的锁，不依赖索引来锁定某一行，锁资源的粒度小，所以并发性高
• Mysql只有InnoDb支持行锁，而且必须依赖索引才能用到行锁，否则会使用表锁，所以并发性较低

持久性

• Oracle有在线联机日志，可以恢复因为意外宕机导致的数据丢失
• Mysql如果意外宕机，假设数据还没来得及从缓存写入到磁盘，则会出现数据丢失

主从复制

• Oracle支持多机容灾，在主库出现问题的时候，可以自动切换为主库
• Mysql的主从复制比较简单，主库出现问题的时候，需要重新去手动配置哪个是主库

事务隔离级别

• Oracle默认的是 read commited（读已提交）的隔离级别，会有不可重复读+幻读的问题
• Mysql默认是 repeatable read（可重复度）的隔离级别，只会有幻读的问题

语法不同

• 单引号的处理
  Mysql里可以用双引号包起字符串

  Oracle里只可以用单引号包起字符串。

• 自动增长的数据类型处理
  Mysql是一个自动增长的数据类型，插入数据的时候，不需要管理，它自己会自动增长

  Oracle不支持自动增长的数据类型，通过建立一个自动增长的序列号来完成自动增长。

• 事物提交方式
  Oracle默认不自动提交，需要用户手动提交。
  Mysql默认是自动提交。不支持事物。
  Mysql默认自动提交，也就是你提交一个query，他就直接执行，我们可以通过
  set autocommit=0 禁止自动提交
  set autocommit=1 开启自动提交

• 字符串的“模糊比较”
  Mysql里用 字段名 like ‘%字符串%’ 进行字符串的“模糊比较”
  Oracle里也可用 字段名 like ‘%字符串%’ 进行字符串的“模糊比较”，但这种方法不能使用索引，速度不快。

• 空字符的处理
  在Oracle数据库中非空字段不允许有空的内容。
  在Mysql数据库中非空字段也有空的内容。

Redis：（非关系型数据库）

• 是一个基于内存高性能的key-value数据库
• 数据类型：string字符串，hash哈希，list列表。zset有序集合，set集合
• 优点：
  • 速度快，因为数据存在内存中
  • 支持丰富的数据类型
  • 支持事务，操作都是原子性
  • 丰富的特性：可用于缓存，消息，
  • 持久化：会把存在内存里的数据刷到硬盘上

写出⼀段JDBC连接本机MySQL数据库的代码

Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost/JDBC";
Stirng user='root';
String password='root';
Connection conn = DriverManager.getConnection(url,user,password);

常⻅的JDBC异常

• java.sql.SQLException——这是JDBC异常的基类。
• java.sql.BatchUpdateException——当批处理操作执⾏失败的时候可能会抛出这个异常。这取决 于具体的JDBC驱动的实现，它也可能直接抛出基类异常java.sql.SQLException。
• java.sql.SQLWarning——SQL操作出现的警告信息。
• java.sql.DataTruncation——字段值由于某些⾮正常原因被截断了（不是因为超过对应字段类型的 ⻓度限制）。

JDBC中存在哪些不同类型的锁?

从广义上讲，有两种锁机制来防止多个⽤户同时操作引起的数据损坏。

• 乐观锁——只有当更新数据的时候才会锁定记录。
• 悲观锁——从查询到更新和提交整个过程都会对数据记录进行加锁