疯狂Java讲义 第十三章 MySQL数据库与JDBC编程

jefxff 153,590 2020-04-15

1. JDBC 基础

1.1 JDBC 简介

  1. JDBC 就是 Java Database Connectivity 即 Java 数据库连接, 是用来执行 SQL 语句的 Java API
  2. JDBC 的优势就是跨平台, 跨应用; 实现同一种 API 可以访问不同平台不同的数据库系统
  3. JDBC 的功能就是,连接数据库, 执行SQL语句, 返回SQL语句执行结果

1.2 JDBC 驱动程序

  1. 数据库驱动程序就是JDBC和数据库之间的转换层, 负责将JDBC调用映射成数据库调用

  2. JDBC 4种驱动:

    1. JDBC-ODBC驱动: Java8已删除, 原因是不适合并发使用, 性能和扩展能力不强
    2. 直接将JDBC API 映射成数据库特定的客户端API; 包含特定数据库的本地代码, 用户访问特定数据库客户端;(这种驱动性能好, 但是代价就是增加了代码量被维护的难度)
    3. 支持三层结构的 JDBC 访问方式, 主要用于 Applet 阶段, 通过 Applet 访问数据库
    4. 纯Java的, 直接与数据库实例交互, 这种驱动知道数据库底层协议(避开了本地代码, 减少开发复杂性, 以及出错的可能)

2. SQL 语法

2.1 Windows 下安装MySQL数据库需要注意点:

  1. 需要先安装 vcredist_x86.exe 这个程序, 似乎默认安装在了C盘下
  2. 在 MySQL 安装的目录下 新建 my.ini
  3. 创建 MySQL 环境变量以及添加MySQL_path变量
  4. 以管理员运行 CMD, 执行 mysqld -install
  5. 执行初始化获取登陆密码: mysqld --initialize 密码在 目录下data文件夹以*.err结尾的文件中
  6. 以获取的密码登录MySQL, 重置密码 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
  7. 重置MySQL服务

2.2 关系数据库基本概念及MySQL基本命令

  1. 关系型数据库

    1. 了解DBMS: DBMS 是 Database Management System 数据库管理系统的简称; 它负责管理数据的存储, 安全, 一致性并发, 恢复和访问等操作

  2. 几种类型的数据库系统

    1. 网状型数据库
    2. 层次性数据库
    3. 关系型数据库
    4. 面向对象数据库

  3. Mysql 数据库两种存储机制

    1. MyISAM: 这是MySQL早期默认的存储机制, 对事务支持不够好
    2. InnoDB: 提供事务安全的存储机制, 通过建立行级锁来保证事务完整性, 以共享锁来处理Senlect语句
    3. 建表时指定存储机制:
      ENGINE=MyISAM 强制使用 MyISAM 存储机制
      ENGINE=InnoDB 强制使用 InnoDB 存储机制

2.3 SQL 语句基础

  1. SQL语句的功能:

    1. 在数据库中检索信息
    2. 对数据库的信息进行更新
    3. 改变数据库的结构
    4. 更改系统的安全设置
    5. 增加或回收用户对数据库, 表的许可权限

  2. 标准SQL语句的类型

    1. 查询语句：主要由 select 关键字完成
    2. DML (Data Manipulation Language, 数据操作语言) 语句: 主要由 insert, update, delete 关键字完成
    3. DDL (Data Definition Lanaguage, 数据定义语言) 语句: 主要由 create, alter, drop, truncate(删除表内容, 留下表结构) 关键字完成
    4. DCL (Data Control Lanaguage, 数据控制语言) 语句: 主要由 grant(授予) 和 revoke(撤销) 关键字完成; 通常用于为数据库用户授权, 或者收回指定用户的权限
    5. 事务控制语句: 主要由 commit, rollback, savepoint 关键字完成

  3. SQL 命令标识符命名规则:

    1. 标识符通常必须以字母开头
    2. 标识符包括字母, 数字和三个特殊字符(#_$)
    3. 不要使用关键字, 保留字, 多个单词连缀组成, 单词之间以_隔开

2.4 DDL 语句

常见的数据库对象

对象名称关键字描述
表table表是数据存储的逻辑单元, 以行和列的形式存在; 列就是字段, 行就是记录
数据字典 就是系统表,存放数据库相关信息的表;系统表里的数据通常由数据库系统维护,不可轻易修改
约束constraint执行数据校验的规则, 用于保证数据完整性的规则
视图view一个或多个数据表里数据的逻辑显式, 视图并不存储数据
索引index用于提高查询性能, 相当于书的目录
函数function用于完成一次特定的计算, 具有一个返回值
存储过程procedure用于完成一次完整的业务处理, 没有返回值, 但可通过传出参数将多个值传给调用环境
触发器tigger相当于一个事件监听器, 当数据库发生特定事件后, 触发器被触发, 完成相应处理

Mysql 支持的列类型

列类型说明
tinyint/smallint/mediumint/int(integer)/bigint1字节/2字节/3字节/4字节/8字节整数, 又可分为有符号和无符号两种, 这些整数类型的区别仅是表数范围不同
float/double单精度, 双精度浮点类型
decima(dec)精确小数类型, 相当于 float 和 double 不会产生精度丢失的问题
date日期类型, 不能保存时间, 把java.util.Date 对象保存进date列时, 时间部分会丢失
time时间类型, 不能保存日期, 把java.util.Date 对象保存进time列时, 日期部分会丢失
datetime日期, 时间类型
timestamp时间戳类型
year年类型, 仅仅保存时间的年份
char定长字符串类型
varchar可变长度的字符串类型
binary定长二进制字符串类型,它以二进制形式保存字符串
varbinary可变长度的二进制字符串类型, 它以二进制的形式保存字符串
tinblob/blob/mediumblob/longblob1字节/2字节/3字节/4字节的二进制大对象, 可用于存储图片, 音乐等二进制数据; 分别可存储255B/64KB/16MB/4GB大小
tinytext/text/mediumtext/longtext1字节/2字节/3字节/4字节的文本对象, 可用于存储超长长度的字符串, 分别存储:255B/64KB/16MB/4GB大小的文本
enum('value1','value2',...)枚举类型, 该列的值只能是enum后括号里多个值的其中之一
set('value1','value2',...)集合类型, 该列的值只能是set后括号里多个值的其中几个

创建表语法

-- 语法
    create table ['模式名'.]'表名' (
       -- 可以有多个列定义
        columnName1 datatype [default expr],
        ...,
        ...
    );    
 
-- 例子: 创建一张test 表
    create table test (
        -- 整数通常用 int
        test_id int,
        -- 小数点位
        test_price decimal,
        -- 普通长度文本, 使用default指定默认值
        test_name varchar(255) default 'XXX',
        -- 大文本类型
        test_desc text,
        -- 图片
        test_img blob,
        test_date datetime
    );

子查询建表

-- 语法
    create table ['模式名'.]'表名' [column[, column...]]
    as subquery;
--
-- 例子
    -- 创建hehe数据表, 该数据表和test完全相同, 数据也相同
    create table hehe
    as 
    select * from test;

修改表结构语法

-- 语法
    -- 增加列定义的语法: (新增的列名必须是原表中不存在的)
    alter table '表名'
    add(
        -- 可以有多个列定义
        column_name1 datatype [default expr],
        ...
    );
-- 
-- 例子
    --为hehe数据表增加一个hehe_id字段, 该字段的类型为int
    alter table hehe
    add hehe_id int;
    -- 为hehe数据表增加aaa,bbb字段, 两个字段的类型都为varchar(255)
    alter table hehe
    add(
        aaa varchar(255) defaule 'xxx',
        bbb varchar(255)
    );

修改列定义的语法: (修改的列名必须是原表中存在的)

-- 语法
    alter table '表名'
    modify column_name datatype [default expr] [first | after column_name];
-- 
-- 例子
    -- 将hehe表的hehe_id列修改为varchar(255)类型
    alter table hehe 
    modify hehe_id varchar(255);
    -- 将hehe表的bbb列修改成 int 类型
    alter table hehe
    modify bbb int;

从数据表删除列语法

-- 语法
    alter table '表名'
    drop column_name;
-- 
-- 例子
    -- 删除hehe的aaa字段
    alter table hehe
    drop aaa;

重命名数据表语法

-- 语法
    alter table '表名'
    rename to '新表名';
--
--例子
    -- 重命名表
    alter table hehe
    rename to wawa;

改变列名语法

-- 语法
    alter table '表名'
    change old_column_name new_column_name type [default expr] [first | after column_name];
--
--例子
    -- 改变列名语法
    alter table wawa
    change bbb ddd int;

删除表的语法

--语法:
    drop table '表名';
--
-- 例子:
    drop table wawaha;

删除表的效果

  1. 表结构被删除, 表对象不再存在
  2. 表里的所有数据也被删除
  3. 该表所有相关的索引, 约束也被删除

truncate 表

  1. truncate 表的作用是一次性删除整个表里的全部数据, 但保留表结构

  2. 语法:
    truncate 表名;

2.5 数据库约束

  • 通过约束可以更好地保证数据表里数据的完整性, 约束是再表上强制执行数据校验规则, 约束主要用于保证数据库里数据表的完整性, 另外, 当表中数据存在相互依赖性时, 可以保护相关的数据不被删除

数据库5种完整性约束

  1. NOT NULL 非空约束, 指定某列不能为空
  2. UNIQUE 唯一约束, 指定某列或者几列组合不能重复
  3. PRIMARY KEY 主键, 指定该列的值可以唯一第标识该条记录
  4. FOREIGN KEY 外键, 指定该行记录从属于主表中的一条记录, 主要用于保证参照完整性
  5. ※ CHECK 检查, 指定一个布尔表达式, 用于指定对应列的值必须满足该表达式 MySQL 不支持这一条

约束分类

  1. 单列约束: 每个约束只约束一列
  2. 多列约束: 每个约束可以约束多个数据列

为数据表指定约束的两个时机

  1. 建表的同时为相应的数据列指定约束
  2. 建表之后, 以修改的方式来增加约束

NOT NULL 约束:

  1. 非空约束用于确保列不允许为空, 只能作为列级约束, 只能使用列级约束语法, SQL中null不区分大小写
  2. 所有的数据类型的时都可以是null, 包括 int, float, boolean 等数据类型
  3. 空字符串不等于 null, 0 也不等于 null

示例代码

--  建表时指定列非空
create table hehe1 (
    --  建立非空约束, 这意味着hehe_id 不可以为 null 
    hehe_id int not null,
    --  MySQL 的非空约束不能指定名字
    hehe_name varchar(255) default 'xyz' not null,
    --  下面的列可以为空, 默认就可以为空
    hehe_gender varchar(2) null
);
-- 
--  修改表示增加或删除非空约束
-- 增加约束
alter table hehe1
modify hehe_gender varchar(2) not null;
-- 取消约束
alter table hehe1
modify hehe_name varchar(2) nll;
-- 取消非空约束, 指定默认值
alter table hehe1
modify hehe_name varchar(255) default 'abc' null;

UNIQUE 约束:

  1. 唯一约束用于保证指定列或指定列组合不允许出现重复值(但可以出现多个null值),
  2. 同一个表可建立多个唯一约束, 唯一约束也可以由多列组合而成, 唯一约束既可以使用列级约束, 也可以使用表级约束语法建立
  3. 表级约束语法: [constraint 约束名] 约束定义
  4. 删除约束: drop index 约束名;

示例代码

--  列级约束语法
    --  建表时创建唯一约束, 使用列级约束语法建立约束
    create table unique_test (
        --  建立了非空约束,这意味着test_id不可以为null
        test_id int not null,
        --  unique就是唯一约束, 使用列级约束语法建立唯一约束
        test_name varchar(255) UNIQUE
    );
-- 表级约束语法
    --  建表时创建唯一约束, 使用表级约束语法建立约束
    --  test_name 和 test_pass 都是唯一约束, 他们都不可以重复
    create table unique_test2 (
        --  建立了非空约束,这意味着test_id不可以为null
        test_id int not null,
        test_name varchar(255),
        test_pass varchar(255),
        --  使用表记约束语法建立唯一约束
        unique (test_name),
        --  使用表级约束语法建立唯一约束, 而且指定约束名
        constraint test2_uk unique (test_pass)
    );
-- 表级组合约束语法
    --  建表时创建唯一约束, 使用表级约束语法建立约束
    --  test_name 和 test_pass 的组合是唯一约束, 他们的组合不可以重复
    create table unique_test3 (
        --  建立了非空约束,这意味着test_id不可以为null
        test_id int not null,
        test_name varchar(255),
        test_pass varchar(255),
        --  使用表级约束语法建立唯一约束, 指定两列组合不允许重复
        constraint test3_uk unique (test_name, test_pass)
    );
-- 已建好的表, 修改约束或增加约束语法
    --  也可以使用add或者modify时增加约束
    --  增加唯一约束
    alter table unique_test3
    add unique(test_name, test_pass);
    --  修改唯一约束
    alter table unique_test3
    modify test_name varchar(255) unique;
    --  删除约束
    alter table unique_test3
    drop index test3_uk;

PRIMARY KEY 约束:

  1. 主键约束相当于非空约束和唯一约束, 即主键约束的列既不允许出现重复值, 也不允许出现null
  2. 每个表最多允许有一个主键, 但是这个主键约束可以由一列或者多列组合而成
  3. 也有列级约束语法和表级约束语法(多字段建立组合主键)
  4. 删除主键约束: drop primary key
  5. 对主键添加自增的特性, MySQL使用: auto_increment 来设置自增长

示例代码

-- 列级约束语法
    -- 列级约束语法(单独指定某一列为主键)
    create table primary_test (
        -- 建立主键约束
        test_id int PRIMARY KEY,
        test_name varchar(255)
    );
--
-- 表级约束语法
    create table primary_test (
        test_id int not null,
        test_name varchar(255),
        test_pass varchar(255),
        --指定主键约束名为 test2_pk, 对大部分数据库有效, 但是对MySQL无效
        -- MySQL数据库中该主键约束名依然是 PRIMARY 
        constraint test2_pk primary key(test_id)
    );
--
-- 以多列建立组合主键, 只能使用表级约束语法:
    create table primary_test3 (
        test_name varchar(255),
        test_pass varchar(255),
        -- 建立多列组合的主键约束
        primary key(test_name, test_pass)
    );
--
-- 删除主键
    alter table primary_test3
    drop primary key;

--
-- 已建立的表通过(add)增加主键约束
    alter table primary_test3 
    add primary key(test_name, test_pass);
--
-- 已建立的表通过 (modify) 增加主键约束
    alter table primary_test3 
    modify test_name varchar(255) primary key;

FOREIGN KEY 约束

  1. 外键约束主要用于保证一个或两个数据表之间的参照完整性, 外键是构建于一个表的两个字段或两个表的两个字段之间的参照关系
  2. 参照关系: 子(从)表外键列的值必须在主表被参照列的值范围之内, 或者为空(通过 not null 设置外键不为空)
  3. 当主表的记录被从表记录参照时, 主表记录不允许被删除, 必须先把从表里参照该记录的所有记录全部删除后, 才可以删除主表的记录; 或者删除主表记录时级联删除从表中所有参照该记录的从表记录
  4. 从表外键参照的只能是主表主键列或者唯一列, 同一表内可以有多个外键
  5. 外键约束用于定义两个时间之间一对多, 一对一的关联关系,
  6. 外键约束也有表级和列级约束之分;列级约束直接使用关键字 references 关键字, references 指定该列参照那个主表, 以及参照主表的那一列
  7. 如果创建外键约束时没有指定约束名, 则MySQL会为该外键约束命名为 table_name_ibfk_n, 其中 table_name 是从表的表名, 而n是从1开始的整数
  8. 删除外键: drop foreign key 外键约束名
  9. 外键约束参照自身的情况叫做自关联, (一个部门经理和员工之间是自关联的一对多)

示例代码

-- 例子1: 实例, 建立blog数据库
create database blog charset=utf8;
-- 建立 User 表
-- 如果删除主表(user)记录时, 想从表(blog)记录也会随之删除, 则需要在建立外键约束后添加 on delete cascade
-- 或者添加 on delete set null (第一种:主表删除后,从表记录也全部级联删除; 第二种: 主表删除后, 参照该主表
-- 记录的外键设为null)
create table user (
    user_id int not null primary key auto_increment,
    user_name varchar(255) not null unique,
    create_date datetime not null,
    is_admin boolean defaule false
);
--
-- 建立 category 表
create table category (
    category_id int not null auto_increment primary key,
    category_name varchar(255) not null unique
);
--
-- 建立 blog 主表 使用表级约束语法
create table blog (
    title varchar(255) not null,
    blog_user varchar(255),
    blog_category varchar(255),
    blog_body text,
    -- 表级约束
    foreign key (blog_user) references user(user_name) on delete cascade,
    foreign key (blog_category) references category(category_name) on delete set null
);

-- 例子2:  表级约束语法
--  建立多列组合的外键约束, 则必须使用表级约束语法
--  为了保证从表参照的主表存在, 通常应该先建主表
create table teacher_table3 (
    teacher_name varchar(255),
    teacher_pass varchar(255),
    --  以两列建立组合主键
    primary key(teacher_name, teacher_pass)
);
-- 
create table students_table3 (
    --  为本表建立主键约束
    students_id int auto_increment primary key,
    students_name varchar(255),
    java_teacher_name varchar(255),
    java_teacher_pass varchar(255),
    --  使用表级约束语法建立外键约束, 指定两列的联合外键
    foreign key(java_teacher_name, java_teacher_pass)
        references teacher_table3(teacher_name, teacher_pass)
);

-- 例子3: 使用列级约束语法
create table teacher_table (
    teacher_id int auto_increment,
    teacher_name varchar(255),
    primary key (teacher_id)
);

-- 为本表建立外键约束
create table students_table (
    students_id int auto_increment primary key,
    students_name varchar(255),
    -- 指定 java_teacher 参照到 teacher_table 的 teacher_id 
    java_teacher int references teacher-table(teacher_id)
);

-- 例子4: 删除外键
alter table students_table3
drop foreign key student_table_ibfk_1;

-- 例子5: 已创建的表增加外键
alter table students_table3
add foreign key (java_teacher_name, java_teacher_pass)
    references teacher_table3(teacher_name, teacher_pass);

-- 例子6: 自关联
create table foreign_test (
    foreign_id int auto_increment primary key,
    foreign_name varchar(255),
    -- 使用该表的refer_id参照到本表的foreign_id列
    refer_id int,
    foreign key(refer_id) references foerign_test(foreign_id)
);

2.6 索引

  1. 索引的作用就是加速对表的查询, 索引存放在模式(schema)中的一个数据库对象, 索引总是从属于某个表, 不可以单独存在, 可以在一列或者多列上创建索引;
  2. 索引的坏处是当表中记录增删改时, 数据库系统需要维护索引, 增加系统开销, 而且索引也占用系统磁盘空间

创建索引的两种方式:

  • 自动: 当在表上定义主键约束, 唯一约束和外键约束时, 系统会为该数据列自动创建对应的索引
  • 手动: 用户可以通过 create index... 语句来创建索引

删除索引的两种方式:

  • 自动: 数据表被删除时, 该表上的索引自动被删除
  • 手动: 用户可以通过 drop index... 语句来删除指定数据表上的指定索引

创建索引语法格式:

create index index_name 
on table_name (column[, column]...);

删除指定索引语法:

drop index '索引名' on '表名';

示例代码

--例子:
-- 创建单独列的索引
create index emp_last_name_idx 
on employees(last_name);
--
-- 同时对多列创建索引
create index emp_last_name_idx2 
on employees(first_name, last_name);
--
-- 删除索引
drop index emp_last_name_idx2
on employees;

2.7 视图

  • 视图不存储数据, 也不是数据表,视图只是一个或多个数据表中数据的逻辑显式

视图的好处:

  1. 可以限制对数据的访问
  2. 可以使复杂的查询变得简单
  3. 提供了数据的独立性
  4. 提供了对相同数据的不同显示

创建/修改视图的语法:

-- 因为视图只是数据表中数据的逻辑显示--也就是一个查询结果, 
--      所以创建视图就是建立视图名和查询语句的关联
-- 其实就是将一条复杂的查询语句创建一个快捷查询方式
create or replace view '视图名'
as 
subquery;

删除视图:

drop view '视图名';

示例代码

-- 创建一个视图
create view blog_all
as 
select * from blog;
--
-- 删除上面创建的视图名
drop view blog_all;

2.8 DML 语句语法

  1. DML 用于操作数据表里的数据, 通常完成下面三项工作:
    1. 插入新数据 (insert into)
    2. 修改已有数据 (update)
    3. 删除不需要的数据 (delete from)

insert into 插入数据

示例代码

-- 语法:
    insert into table_name [(column [, column...])]
    values (value [, value...]);
--
--例子:
    -- 可以全字段插入, 即 每一个字段都插入
    insert into blog values (
        'java-02-java基础', 'jeff', 'java', '正在学习Java基础, 学习完基础之后学习IO,JDBC...' 
    );
    --
    -- 如果不想在表后用括号列出所欲列, 则需要为所有列指定值; 如果某列的值不能确定, 则为该列分配一个null值
    insert into teacher_table2
    -- 使用null代替主键列的值
    values(null, 'abc');
--
--例子:
    -- 使用带子查询的插入语句, 带子查询的插入语句可以一次插入多条记录
    insert into student_table2(student_name)
    select teacher_name from teacher_table2;
--
--例子:
    -- 同时插入多条语句
    insert into category values
    (0, 'python'),
    (0, 'Html'),
    (0, 'CSS');

2. update 语句

  • update 语句用于修改表数据, 可一次修改一条或多条或多列记录, 通过 WHERE 子句来限定修改那些记录

示例代码

-- 语法:
    update table_name 
    set column1 = value1[, column2 = value2]...
    [WHERE condition];
--
-- 例子:
    update teacher_table2 
    set teacher_name = '苍老师'
    where teacher_id > 1;

3. delete from 语句

  • delete from 语句用于删除指定数据表的记录, delete from 总是整行删除记录, 也可以通过 WHERE 来限定删除条件

示例代码

-- 语法:
    delete from table_name
    [WHERE condition];
--
-- 例子:
    -- 删除student_table2表中的全部记录
    delete from student_table2;

    -- 通过 where 来限定删除条件
    delete from student_table2 
    where teacher_id > 2;

2.9 单表查询

  1. select 语句不仅可以执行单表查询, 而且可以执行多表连接查询, 还可以进行子查询, 或者通过where进行条件查询
  2. MySQL 使用 concat 函数来进行字符串连接运算(不可以连接 null)
  3. 可以通过 as 对列起一个别名
  4. select 会选出全部符合条件的记录, 包括重复项, 要去除重复项, 使用 distinct, (select distinct ...)
  5. like 用于模糊查询, 可以使用两个通配符: 下划线(_)代表一个字符, 百分号(%)代表任意多个字符
  6. where 后面可以跟多个子句, 子句可使用逻辑判断 and or not 来组合
  7. order by 默认按照升序来排序, 如果强制按照降序, 使用 order by desc

select 语句查询时算术表达式规则:

  1. 对数值型数据列, 变量, 常量可以使用算术运算符(+, -, *, /) 创建表达式
  2. 对日期型数据列, 变量, 常量可以使用部分算术运算符(+, -) 创建表达式, 两个日期之间可以进行减法运算, 日期和数值之间可以进行加, 减运算
  3. 运算符不仅可以在列和常量, 变量之间进行运算, 也可以在两列之间进行运算

select 单表查询语法:

    select column1, column2 ...
    from '数据源'
    [where condition];

特殊的比较运算符:

运算符含义
expr1 between expr2 and expr3要求 expr1 >= expr2 并且 expr2 <= expr3
expr1 in(expr2, expr3, expr4,...)要求 expr1 等于后面括号里任意一个表达式的值
like字符串匹配, like后的字符串支持通配符
is null要求指定值等于null

order by 语法:

order by column_name1 [desc], column_name2 ...

示例代码

-- 例子
    -- 数据列实际可当成一个变量进行算术运算
    select teacher_id + 5
    from teacher_table;
    --
    -- 查询出 teacher_table 表中 teacher_id*3 大于4的记录
    select * from teacher_table
    where teacher_id * 3 > 4;
    --
    -- select 之后可以是表达式, 变量, 常量
    select 3*5, 20
    from teacher_table;
    --
    -- 选择出 teacher_name 和 'xx' 字符串连接后的结果
    select concat('teacher_name', 'xx')
    from teacher_table;
    --
    -- 通过 as 为列起别名
    select tracher_id  + 5 as MY_ID 
    from teahcer_table;
    --
    -- 选出所有记录包括重复项 
    select student_name, java_teacher 
    from student_table;
    -- 选出所有记录, 去除重复项
    select distinct student_name, java_teacher
    from student_table;
    --
    -- 选出student_id 大于等于2, 且小于等于4的所有记录
    select * from student_table 
    where student_id between 2 and 4;
    --
    -- between 前后的两个值不仅可以是常量, 也可是变量, 或者是列
    select * from student_table 
    where 2 between java_teacher and student_id;
    --
    -- 使用in比较运算符时, 必须在in后的括号里面列出一个或多个值
    select * from student_table 
    where student_id in(2, 4);
    --
    -- in后也可以是常量, 变量或列名
    select * from student_table 
    where 2 in(student_id, java_teacher);
    --
    -- 查找user中以j开头的用户
    select * from student_table 
    where student_name like 'j%';
    --
    -- 查找user中两个字符的用户
    select * from user 
    where user_name like '__';
    --
    -- is null 用于判断某些值是否为空
    select * from student_table 
    where student_name is null;
    --
    -- order by 默认按照升序
    select * from category 
    order by category_id;
    --
    -- order by 按照降序排列
    select * from category
    order by category_id desc;

2.10 数据库函数

  1. 数据库函数是用于进行数据处理或复杂计算, 他们通过一组数据进行计算, 得到最终需要的输出结果
  2. 按对数据的处理方式分为:单行函数或多行函数(聚集函数, 分组函数)

MySQL中单行函数特征:

  1. 单行函数的参数可以是变量, 常量或数据列; 单行函数可以接受多个参数, 但只返回一个值
  2. 单行函数对每行单独起作用, 每行(可能包含多个参数)返回一个结果
  3. 使用单行函数可以改变参数的数据类型

MySQL单行函数分类:

  • 日期时间函数, 数值函数, 字符函数, 转换函数, 其他函数(位函数, 流程控制, 加密解密, 信息函数)

示例代码

-- MySQL单行函数:
    -- char_length(column_name) 查找列的字符长度
        select char_length(blog_body) from blog;
    --
    -- sin(char_length(column_name)) 计算字符长度的sin值
        select sin(char_length(blog_body)) from blog;
    --
    -- adddate('date', 数值) 这种简单一点, 为指定日期添加一定时间(按天加)
        select adddate('2019-11-01', 2);
    --
    -- select curtime() 获取当前时间
    --
    -- select curdate(); 获取当前日期
    --
    -- select MD5('字符串'); 使用 MD5 加密
        select MD5('lijianfeng');

MySQL处理null的函数:

  1. ifnull(expr1, expr2): 如果 expr1 为 null, 则返回 expr2, 否则返回 expr1
  2. nullif(expr1, expr2): 如果 expr1 和 expr2 相等, 则返回null, 否则返回 expr1
  3. if(expr1, expr2,expr3): 类似与三元运算, 如果 expr1 为true 不等于0, 且不等于 null, 则返回expr2, 否则返回 expr3
  4. isnull(expr1): 判断expr1是否为null, 如果为null则返回true, 否则返回false

case 函数第一种语法:

-- 语法
    case value 
    when compare_value1 then result1
    when compare_value2 then result2
    ...
    else result 
    end 
--
-- 例子:
    select student_name, case java_teacher
    when 1 then 'java 老师',
    when 2 then 'Ruby 老师',
    else '其他老师',
    end 
    from student_table;

case 函数第二种语法:

-- 语法
    case 
    when compare_value1 then result1
    when compare_value2 then result2
    ...
    else result 
    end 
--
-- 例子:
    select student_name, case 
    when student_id <= 3 then '初级班',
    when student_id <= 6 then '中级班',
    else '高级班'
    end
    from student_table;

2.11 分组和组函数

grop by 语句:

  1. grop by 子句后通常跟一个或多个列名, 表示查询结果根据一列或多列进行分组
  2. 可以使用 having子句 对分组进行过滤, having子句是一个表达式, 只有满足条件的分组才会被选出来

常用的组函数:

  1. avg([distinct | all]expr)
    计算多行expr的平均值, distinct 表示不计算重复值, all表示计算重复值
  2. count({* | [distinct | all]expr})
    计算多行expr的总条数, *表示统计该表内的记录行数, distinct 表示不计算重复项, all表示计算重复项
  3. max(expr)
    计算多行expr的最大值, expr可以是变量, 常量或数据列数据类型任意
  4. min(expr)
    计算多行expr的最小值, expr可以是变量, 常量或数据列数据类型任意
  5. sum([distinct | all]expr)
    计算多行expr的总和, expr可以是变量, 常量或数据列, 数据类型必须是整数

示例代码

    select count(*) 
    from student_table
    -- 当 java_teacher, student_name 两列的值完全相同时才会被当成一组
    grop by java_teacher, student_name;

where 和 having 的区别:

  1. 不能在 where 子句种过滤组, where 子句仅用于过滤行, 过滤组必须使用having子句
  2. 不能在 where 子句种使用组函数, having 子句才可以使用组函数

2.12 多表连接查询

SQL92 的连接查询:(了解)

  • 等值连接, 非等值连接, 外连接, 广义笛卡尔积

示例代码

-- 查询语法:
    select column1, column2 ...
    from table1, table2 ...
    [where join_condition]
--    
-- 例子:
    select s.* teacher_name 
    from student_table s, teacher_table t 
    where s.java_teacher = t.teacher_id;

自连接查询语法:

  • 自连接插叙就是把一张表当作两张表来查询

SQL99 的连接查询

  • 交叉连接, 自然连接, 使用using子句的连接, 全外连接或者左,右外连接

交叉连接: (cross join)

  • 效果就是SQL92种的广义笛卡尔积

示例代码

-- 例子:
    select s.*, teacher_name 
    -- SQL 99 多表连接查询的from后只有一个表名
    from student_table s 
    -- cross join 交叉连接, 相当于广义的笛卡尔积
    cross join teacher_table t;

自然连接: (natural join)

  • 自然连接会以两个表种的同名列作为连接条件, 如果两个表种没有同名列, 则自然连接与交叉连接效果完全一样, 因为没有连接条件

示例代码

-- 例子:
    select s.*, teacher_name 
    -- SQL 99 多表连接查询的from后只有一个表名
    from student_table s 
    -- natural join 自然连接使用两个表中的同名列作为连接条件
    natural join teacher_table t;
    --
    select distinct b.*, blog_user 
    from blog b 
    natural join user s;

using 子句连接: using('column')

  • using 子句可以指定一列或多列, 用于显式指定两个表中的同名列作为连接条件; 如果两个表中有超过一列的同名列, 如果使用 natural join, 则会把所有的同名列当成连接条件; 如果使用using子句就可显式指定使用哪些同名列作为连接条件

示例代码

-- 例子:
    select s.*, teacher_name 
    from student_table s 
    join teacher_table t 
    using(teacher_id);
    -- 这条SQL语句会报错, 因为使用 using 时两个表中必须有同名列

on 子句连接: (on)

  • SQL99语法的连接条件放在on子句中指定, 而且每个on子句值指定一个连接条件, 即:如果需要进行N表的连接则需要有N-1个join...on对

示例代码

-- 例子:
    select s.*, teacher_name 
    from student_table s 
    join teacher_table t 
    on s.java_teacher = t.teacher_id;
    --
    select s.*, teacher_name 
    from student_table s 
    join teacher_table t 
    on s.java_teacher > t.teacher_id;

左, 右, 全外连接:

  • 这三种连接分别使用 left[outer]join, right[outer]join和full[outer]join, 这三种外连接的连接条件一样通过 on 子句来指定, 既可以是等值连接条件, 也可以是非等值连接条件

示例代码

-- 右外连接, 非等值连接:
    select s.*, teacher_name 
    from student_table s 
    right join teacher_table t 
    on s.java_teacher < t.teacher_id;
--
-- 左外连接, 非等值连接:
    select s.*, teacher_name 
    from student_table s 
    left join teacher_table t 
    on s.java_teacher > t.teacher_id;
--
-- 全外连接, 等值连接: -- 会报错
    select s.*, teacher_name 
    from student_table s 
    full join teacher_table t 
    on s.java_teacher = t.teacher_id;

2.13 子查询

  • 子查询就是指在查询语句中嵌套另一个查询, 子查询可以支持多层嵌套,

子查询出现的位置:

  1. 出现在 from 语句后当成数据表, 这种用法也被称为行内视图, 因为该子查询的实质就是一个临时视图
  2. 出现在 where 条件之后作为过滤条件的值

子查询要注意的点:

  1. 子查询要用括号括起来
  2. 把子查询当成数据表(出现在 from 之后), 可以为该子查询起别名, 尤其是作为前缀来限定数据列时, 必须给子查询起别名
  3. 把子查询当成过滤条件时, 将子查询放在比较运算符的右边, 这样可以增强查询的可读性
  4. 把子查询当成过滤条件时, 单行子查询使用单行运算符, 多行子查询使用多行运算符

示例代码

    -- 把子查询当成数据表的用法
    select * 
    from (select * from student_table) t 
    where t.java_teacher > 1;
    --
    -- 把子查询当成 where 条件中的值
    select * 
    from student_table where java_teacher > 
    (select teacher_id from teacher_table where teacher_name = 'YeeKu');
    --
    -- 如果子查询返回多个值, 则需要使用 in, any, all等关键字; in 可以单独使用, any, all可以与
    -- >, <, >=, <=, <>, = 等运算符结合使用
    sleect * 
    from student_table
    where student_id in 
    (select teacher_id from teacher_table);
    --
    sleect * 
    from student_table
    where student_id = 
    any(select teacher_id from teacher_table);
    --
    sleect * 
    from student_table
    where student_id >
    all(select teacher_id from teacher_table);
    --
    -- 子查询返回多行多列
    select * 
    from student_table 
    where (student_id, student_name)
    =any(select teacher_id, teacher_name from teacher_table);

2.14 集合运算

对两个结果集进行集合运算, 这两个结果集必须满足的条件:

  1. 两个结果集所包含的数据列的数量必须相等
  2. 两个结果集锁包含的数据列的数据类型必须一一对应

union 运算(并)

    -- 语法格式:
        select '语句' union select '语句'

minus运算(差)MySQL不支持

    -- 语法格式:
        select '语句' minus select '语句'

intersect 运算(交) MySQL不支持

    -- 语法格式:
        select '语句' intersect select '语句'

3. JDBC 的典型用法

3.1 JDBC 4.2 常用接口和类简介

DriverManager 类:

  • 主要用于管理JDBC驱动服务类, 主要功能就是获取 Connection 对象

该类的方法:

    // 该方法用于获取url对应数据库的连接
    public static synchronized Connection getConnection(String url, String user, String pass) throws SQLException()

Connection 接口:

  • 该类代表的是数据库连接对象, 每个Connection 代表一个物理连接会话, 想要访问数据库, 必须先获得数据库连接

该接口的主要方法(执行SQL语句):

    //该方法返回一个 Statement 对象, 只有获得了 Statement 对象之后才能执行 SQL 语句 
    Statement createStatement() throws SQLException:
    //    
    // 该方法返回预编译的 Statement 对象, 即将SQL语句提交到数据库进行预编译, 
    //   其中 PreparedStatement 是 Statement 的子类 
    PreparedStatement preparedStatement(String sql) throws SQLException:
    //    
    // 该方法返回 CallableStatement 对象, 该对象用于调用存储过程, 
    //   其中 CallableStatement 是 Statement 的子类 
    CallableStatement prepareCall(String sql) throws SQLExxception:

该接口用于控制事务的主要方法:

    // 创建一个保存点
    Savepoint setSavepoint():  
    //    
    // 以指定名字来创建一个保存点
    Savepoint setSavepoint(String name):
    //    
    // 设置事务的隔离级别
    void setTransactionIsolation(int level):
    //   
    // 回滚事务
    void rollback():
    //         
    // 将事务回滚到指定的保存点
    void rollback(Savepoint savepoint)
    //         
    // 关闭自动提交, 打开事务
    void setAutoCommit(boolean autoCommit)
    //     
    // 提交事务
    void commit()
    //         
    // 控制 Connection 访问数据库的 schema
    setSchema(String schema)
    //         
    // 控制 Connection 访问数据库的 schema
    getSchema()
    //         
    // 控制数据库连接超时的行为
    setNetworkTimeout(Executor executor, int milliseconds)
    //         
    // 控制数据库连接超时的行为
    getNetworkTimeout()

Statement 接口:

  • 用于执行SQL语句的工具接口, 该对象可执行 DDL, DCL, DML 以及 SQL查询语句, 执行SQL查询语句的时候, 返回查询到的结果集

常用方法:

    // 该方法用于执行查询语句, 并返回查询结果对应的 ResultSet 对象, 该方法只能用于执行查询语句
    ResultSet executeQuery(String sql) throws SQLException:
    //        
    // 该方法用于执行DML语句, 并返回受影响的行数; 该方法也可用于执行DDL语句, 执行DDL语句将返回0
    int executeUpdate(String sql) throws SQLException:
    //        
    // 该方法用于执行任何SQL语句, 如果执行后第一个结果为 ResultSet 对象, 这返回 true;
    //  如果执行后第一个结果为受影响的行数或没有任何结果, 则返回false
    boolean execute(String sql) throws SQLException:
    //    
    // 如果 Statement 执行了该方法, 则当所有依赖于该 Statement 的 ResultSet 关闭时,该Statement会自动关闭
    closeOnCompletion()
    //        
    // 用于判断 Statement 时是否打开了 closeOnCompletion
    isCloseOnCompletion() 
    //
    // 相当于  executeUpdate() 方法的增强版, 返回值类型为 long  
    executeLargeUpdate()

PreparedStatement 接口:

  • 预编译的 Statement 对象, 它允许数据库预编译SQL语句(这些SQL语句通常都带有参数), 以后每次只改变SQL命令参数, 避免数据库每次都要编译SQL语句

比 Statement 多的方法:

    // 该方法根据传入参数值的类型不同, 需要使用不同的方法,  
    void setXxx(int parameterIndex, Xxx value)

ResultSet 结果集对象:

  1. 该对象包含访问查询结果的方法, ResultSet 可以通过列索引或列名获得列数据;
  2. 当通过指针移动到指定行之后, ResultSet 可通过 getXxx(int columnIndex) 或 getXxx(String columnLabel) 方法来获取当前行, 指定列的值,前者根据列索引获取值, 否则根据列名获取值
  3. <T> T getObject(int columnIndex, class<T> type) 和 <T> T getObject(String columnLabel, class<T> type) 方法可以获取任意值

常用方法:

    // 释放 ResultSet 对象
    void close()
    //        
    // 将结果集的记录指针移动到第 row 行, 如果 row 为负数, 这移动到倒数第 row 行; 
    //  如果移动后的记录指针指向一条有效记录, 这返回true
    boolean absolute(int row)
    //        
    // 将 ResultSet 的记录指针定位到行首之前, 这是 ResultSet 结果集记录指针的初始状态,
    //  记录指针的起始位置位于第一行之前
    void beforeFirst()
    //        
    // 将 ResultSet 的记录指针定位到行首; 如果移动后的记录指针执行一条有效记录, 则返回true
    boolean first()
    //        
    // 将 ResultSet 的记录指针定位到上一行;  如果移动后的记录指针执行一条有效记录, 则返回true
    boolean previous()
    //        
    // 将 ResultSet 的记录指针定位到下一行;  如果移动后的记录指针执行一条有效记录, 则返回true
    boolean next()
    //        
    // 将 ResultSet 的记录指针定位到最后一行; 如果移动后的记录指针执行一条有效记录, 则返回true 
    boolean last()
    //        
    // 将 ResultSet 的记录指针定位到最后一行之后
    void afterLast()

3.2 JDBC 编程步骤

1. 第一步 加载数据库驱动

  • 通常使用 Class 类的 forName()静态方法来加载数据库的驱动
    // 加载驱动
    Class.forName(driverClass)
    // 
    // 加载 MySQL 数据库的驱动
    Class.forName("com.mysql.jdbc.Driver");   
    // 
    // 加载 Oracle 的驱动
    Class.forName("oracle.jdbc.dirver.OracleDriver");

2. 第二步 通过 DriverManager 获取数据库连接

  • 但是用 DriverManager 获取数据库连接时, 需要传入三个参数: 数据库url, 登录数据库的用户名, 密码
    // 获取数据库连接
    DriverManager.getConnection(Stirng url, String user, String pass)
    // 
    // 数据库URL写法:
        jdbc:subprotocol:other stuff
    // 
    // MySQL 数据库URL的写法:
    jdbc:mysql://hostname:port/databasename
    // 
    // Oracle 数据库URL的写法:
    jdbc:oracle:thin:@hostname:port:databasename

3. 第三步 通过 Connection 对象创建 Statement 对象

创建方法:

    // 创建基本的 Statement 对象
    createStatement()
    //                 
    // 根据传入的SQL语句创建预编译的 Statement 对象
    PreparedStatement(String sql)
    //         
    // 根据传入的 SQL 语句创建 CallableStatement 对象
    prepareCall(String sql)

4. 第四步 使用 Statement 执行 SQL 语句

  • 所有的 Statement 都有如下的三个方法来执行SQL语句

执行方法:

    // 可以执行任何SQL语句, 但比较麻烦
    execute()
    //        
    // 主要用于执行 DML 和 DDL 语句, 执行 DML 语句受SQL语句影响的行数，执行DDL语句时返回0
    executeUpdate()
    //        
    // 只能执行查询语句, 执行后返回代表查询结果的 ResultSet 对象
    executeQuery()

5. 第五步 操作结果集

  • 如果执行的SQL语句是查询语句, 则执行结果将返回一个 ResultSet 对象, 该对象你保存了SQL语句的查询结果; 程序可以通过操作该 ResultSet象来取出查询结果

ResultSet 对象提供的方法:

    //  这些事移动记录指针的方法
    next(), previous(), first(), last(), beforeFirst(), afterLast()
    //        
    // 该方法获取记录指针指向行, 特定列的值; 该方法既可使用列索引作为参数, 也可使用列名作为参数, 
    // 使用 列索引性能较好, 列名可读性好
    getXxx()

6. 第六步 回收数据库资源

  • 包括关闭 ResultSet, Statement 和 Connection 等资源

示例代码

    // 例子:
    import java.sql.*;
    public class JavaConnecteMysql {
        public static void main(String[] args) throws Exception {
            // 1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            try {
                // 2. 通过 DriverManager 获取数据库连接
                Connection conn = DriverManager.getConnection(
                    // "jdbc:mysql://127.0.0.1:3306/blog", 
                    "jdbc:mysql://localhost:3306/blog?useSSL=false&serverTimezone=UTC",
                    "root", "123456"); 
                // 3. 通过 Connection 来创建一个 Statement 对象
            Statement stmt = conn.createStatement();
            // 4. 执行SQL语句
            ResultSet rs = stmt.executeQuery("select * from blog");
            // 5. 操作数据
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t"
                        + rs.getString(2) + "\t"
                        + rs.getString(3) + "\t"
                        + rs.getString(4));
            }
            } catch (SQLException e) {
                e.getSQLState();
            } finally {
                System.out.println("What ?");
            }
        }
    }

4. 执行SQL语句方式

4.1 使用 Java8新增的 executeLargeUpdate 方法执行 DDL 和 DML 语句

正常流程

  • 不要直接把数据库连接信息写在程序当中, 而是使用一个 mysql.ini 文件(就是一个 properties文件)来保存数据库连接信息, 好处是当需要把应用程序从开发环境移植到生产环境的时, 无须修改源码, 只需要修改mysql.ini配置文件即可

示例代码

// 创建一张数据表
import java.sql.*;
// 
public class ExecuteDDL {
    private final String driver = "com.mysql.cj.jdbc.Driver";
    private final String url = "jdbc:mysql://localhost:3306/blog?useSSL=false&serverTimezone=UTC";
    private final String user = "root";
    private final String pass = "123456";
    public void createTable(String sql) throws Exception {
        // 加载驱动
        Class.forName(driver);
        try (
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 使用 Connection 来创建一个 Statement 对象
            Statement stmt = conn.createStatement()) {
                // 执行DDL语句, 创建数据表
                stmt.executeUpdate(sql);
            }
        }
    public static void main(String[] args) throws Exception {
        ExecuteDDL ed = new ExecuteDDL();
        ed.createTable("create table jdbc_test"
            + "(jdbc_id int auto_increment primary key, "
            + "jdbc_name varchar(255), "
            + "jdbc_desc text);");
        System.out.println("----建表成功-----");
    }
}

示例代码

// 在刚创建的数据表中插入数据
import java.sql.*;
// 
public class ExecuteDML {
    private final String driver = "com.mysql.cj.jdbc.Driver";
    private final String url = "jdbc:mysql://localhost:3306/blog?useSSL=false&serverTimezone=UTC";
    private final String user = "root";
    private final String pass = "123456";
    public int insertData(String sql) throws Exception {
        // 加载驱动
        Class.forName(driver);
        try (
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(url, user, pass);
            // 使用 Connection 来创建一个 Statement 对象
            Statement stmt = conn.createStatement()) {
                // 执行DDL语句, 创建数据表
                return stmt.executeUpdate(sql);
            }
        }
    public static void main(String[] args) throws Exception {
        ExecuteDML ed = new ExecuteDML();
        int result = ed.insertData("insert into jdbc_test values"
            + " (0, 'first', 'this is frist data'),"
            + " (0, 'third', 'this is third data');");
        System.out.println("一共 " + result + " 条记录受影响");
    }
}

4.2 使用 execute 方法执行 SQL　语句

  • Statement 的 execute() 方法几乎可以执行任何SQL语句, 但它执行SQL语句时比较麻烦, 通常最好不要使用, 而是使用比较简单的 executeQuery() 方法和 executeUpdate() 方法;
  • 使用 exexcute() 方法执行SQL语句的返回值是 boolean 值, 表示执行该条 SQL 语句是否返回了 ResultSet 对象

Statement 获取执行结果的方法:

// 获取该Statement执行查询语句所返回的 RestltSet 对象
getResultSet()
// 
//  获取 Statement 执行 DML 语句所受影响的记录行数  
getUpdateCount()

示例代码

import java.sql.*;
// 
public class ExecuteSQL {
    MyINI myini = new MyINI();
    // 
    public void executeSql(String sql) throws Exception {
        // 加载驱动
        Class.forName(myini.driver);
        try (
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(myini.url, myini.user, myini.pass);
            // 使用 Connection 来创建一个 Satement 对象 
            Statement stmt = conn.createStatement()){
            // 
            // 执行SQL语句, 返回boolean值表示是否包含ResultSet
            boolean hasResultSet = stmt.execute(sql);
            // 如果为true则获取结果集, 并且输出结果集
            if (hasResultSet) {
                try (
                    // 获取结果集
                    ResultSet rs = stmt.getResultSet()){
                    // ResultSetMetaData是用于分析结果集的元数据接口
                    ResultSetMetaData rsmd = rs.getMetaData();
                    int columnCount = rsmd.getColumnCount();
                    while (rs.next()) {
                        // 依次输出每列的值
                        for (int i = 0; i < columnCount; i++){
                            // ResultSet 的 getString() 方法可以获取除Blob之外的任意类型的值
                            // 因为所有的数据类型都可以自动转换成字符串类型
                            System.out.print(rs.getString(i + 1) + "\t");
                        }
                        System.out.print("\n");
                    }
                }
            } else {
                System.out.println("该SQL语句影响的记录有" 
                    + stmt.getUpdateCount() + "条");
            }
        }
    }
    // 
    public static void main(String[] args) throws Exception {
        ExecuteSQL es = new ExecuteSQL();
        // 
        // System.out.println("执行建表的DDL语句");
        // es.executeSql("create table wahaha"
        //     + " as select * from user;");
        // 执行DDL语句时显式受影响的行数为0
        // 
        // System.out.println("执行查询语句");
        // es.executeSql("show tables;");
        // 执行查询语句时输出查询结果
        // 
        // System.out.println("执行删除语句");
        // es.executeSql("drop table if exists wahaha");
        // 
        System.out.println("执行查询语句");
        es.executeSql("select * from wahaha;");
        // 
        // System.out.println("执行插入DML语句");
        // es.executeSql("insert into wahaha values( "
        //     + "0, 'first', '2019-10-10', '1');");
    }
}

4.3 使用 PreparedStatement 执行SQL语句

  1. PreparedStatement 接口是 Statement 接口的子接口, 它可以预编译SQL语句, 与编译后的SQL语句被存储在 PreparedStatement 对象中, 然后可以使用该对象多次搞笑的执行该语句
  2. PreparedStatement 也提供 execute(), executeUpdate(), executeQuery() 三个方法来执行SQL语句, 不过这三个方法无须参数, 因为 PreparedStatement 已存储了预编译的SQL语句
  3. 使用 PreparedStatement 预编译SQL语句时, 该SQL语句可以带占位符参数, 因此在执行SQL语句之前必须使用
    setXxx(int index, Xxx value)方法传入参数值; 如果程序不清楚预编译的SQL语句中个参数的类型, 则可以使用
    setObject()方法来传入参数, 由 PreparedStatement 来负责类型转换

PreparedStatement 比 Satement 的好处:

  1. PreparedStatement 预编译SQL语句, 性能更好
  2. PreparedStatement 无须 "拼接" SQL语句, 编程更简单
  3. PreparedStatement 可以防止SQL注入, 安全性更好

示例代码

import java.sql.*;
public class PreparedStatementTest {
    MyINI myini = new MyINI();
    /**
    * use Statement insert into table vlaues
    */
    public void insertUseStatement() throws Exception {
        long start = System.currentTimeMillis();
        // 加载驱动
        Class.forName(myini.driver);
        try (
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(myini.url, myini.user, myini.pass);
            Statement stmt = conn.createStatement()) {
            // 插入100条记录
            for (int i = 0; i < 50; i++) {
                stmt.executeUpdate("insert into new_user_1 values("
                    + " 0, '姓名" + i + "','2019-10-10', 0)");
            }
            System.out.println("使用Statement费时:" 
                + (System.currentTimeMillis() - start));
        }
    }
    /**
    * use PreparedStatement insert into table values
    */
    public void insertUsePrepare() throws Exception {
        long start = System.currentTimeMillis();
        // 加载驱动
        Class.forName(myini.driver);
        try (
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(myini.url, myini.user, myini.pass);
            // 使用Connection 来创建一个 PreparedStatement 对象
            PreparedStatement pstmt = conn.prepareStatement(
                "insert into new_user_2 values(0, ?, '2019-10-10', 0)")){
            // 100次为PreparedStatement的参数设值, 就可以插入100条记录
            for (int i = 0; i < 50; i++) {
                pstmt.setString(1, "姓名" + i);
                pstmt.executeUpdate();
            }
            System.out.println("使用PreparedStatement费时:" 
            + (System.currentTimeMillis() - start));
        }
    }
    public static void main(String[] args) throws Exception {
        PreparedStatementTest pst = new PreparedStatementTest();
        pst.insertUseStatement();
        pst.insertUsePrepare();
    }
}

4.4 使用 CallableStatement 调用存储过程

5. 管理结果集

  1. JDBC 使用 ResultSet 来封装执行查询到的查询结果, 然后通过一共 ResultSet 的记录指针来取出结果集的内容;
  2. JDBC 还允许通过 ResultSet 来更新记录, 并提供了 ResultSetMetaData 来获得 ResultSet 对象的相关信息

5.1 可滚动, 可更新的结果集

  1. 就是通过 Connection 对象开创建 Statement 对象的时候, 可以传入控制结果集可滚动, 可更新的参数
  2. 以默认方式打开的 ResultSet 是不可更新的, 如果希望创建可更新的 ResultSet, 则必须在创建 Statement 或PreparedStatement 时传入额外的参数, Connection 在创建 Statement 和 PreparedStatement 时还可以额外传入两个参数: resultSetType(控制 ResultSet 的类型), resultSetConcurrency(控制 ResultSet 的并发)
  3. 可更新的结果集还必须满足两个条件:
    1. 所有数据都应该来自一个表
    2. 选出的数据集必须包含主键列

resultSetType 参数取值:

  1. ResultSet.TYPE_FORWARD_ONLY; 该常量控制记录指针只能向前移动
  2. ResultSet.TYPE_SCROLL_INSENSITIVE; 该常量控制记录指针可以自由移动(可滚动结果集), 但底层数据的改变不会影响 ResultSet 的内容
  3. ResultSet.TYPE_SCROLL_SENSITIVE; 该常量控制记录指针可以自由移动(可滚动结果集), 而且底层数据的改变会影响 RestltSet 的内容

resultSetConcurrency 参数可接收如下两个参数:

  1. RestltSet.CONCUR_READ_ONLY; 该常量指示 ResultSet 是只读的并发模式(默认)
  2. ResultSet.CONCUR_UPDATABLE; 该常量指示 ResultSet 是可更新的并发模式

Java8 为 RestltSet 添加的默认方法:

  1. updateObject(String columnLabel, Object x, SQLType targetSqlType)
  2. updateObject(int columnIndex, Object x, SQLType targetSqlType) 以上两个方法可以直接用 Object 来修改记录指针所指记录, 特定列的值, 其中SQLType用于指定该数据列的类型

示例代码

// 使用 Connection 创建一个 PreparedStatement 对象
// 传入控制结果集可滚动, 可更新的参数
PreparedStatement pstmt = conn.PreparedStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE
    , ResultSet.CONCUR_UPDATABLE);

5.2 处理 Blob 类型的数据

Blob 类型数据:

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

将Blob类型数据插入数据库:

  • 将Blob类型的数据插入数据库需要使用 PreparedStatement, 该对象有一个方法: setBinaryStream(int parameterIndex,InputStream x), 该方法可以为指定参数传入二进制输入流, 从而可以实现将Blob数据保存到数据库的功能

从数据库取出Blob类型的数据:

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

示例代码
MySQL mediumblob 类型的图片存取

5.3 使用 ResultSetMetaData 分析结果集

  1. MateData 是元数据的意思, 即描述其他数据的数据, 因此 ResultSetMetaData 封装了描述 RestltSet 对象的数据,
  2. RestltSet 里包含了一个 getMetaData() 方法, 该方法返回该ResultSet对应的 ResultSetMetaData 对象; 一旦获得了 ResultSetMetaData 对象, 就可通过 ResultSetMetaData 提供的大量方法来返回 RestltSet 的描述信息

ResultSetMetaData 提供的方法:

// 返回该 RestltSet 的列数量
int getColumnCount() 
// 返回指定列索引的列名        
String getColumnName(int column) 
// 返回指定索引的列类型        
int getColumnType(int column)

示例代码

import java.awt.BorderLayout;
import java.sql.*;
import java.util.Vector;
import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
// 
public class QueryExecutor {
    JFrame jf = new JFrame("查询执行器");
    private JScrollPane scrollPane;
    private JButton execBn = new JButton("查询");
    // 用于输入查询语句的文本框
    private JTextField sqlField = new JTextField(45);
    private static Connection conn;
    private static Statement stmt;
    static MyINI myini = new MyINI();
    // 采用静态初始化块来初始化Connection Statement对象
    static {
        try {
            // 加载驱动
            Class.forName(myini.driver);
            // 获取数据库连接
            conn = DriverManager.getConnection(myini.url, myini.user, myini.pass);
            stmt = conn.createStatement();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // ----------------初始化界面方法----------------
    public void init() {
        JPanel top = new JPanel();
        top.add(new JLabel("输入查询语句: "));
        top.add(sqlField);
        top.add(execBn);
        // 为执行按钮, 单行文本框添加事件监听器
        execBn.addActionListener(new ExceListener());
        sqlField.addActionListener(new ExceListener());
        jf.add(top, BorderLayout.NORTH);
        jf.setSize(680, 480);
        jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        jf.setVisible(true);
    }
    // 定义监听器
    class ExceListener implements ActionListener {
        public void actionPerformed(ActionEvent evt) {
            // 删除原来的 JTable(JTable 使用 scrollPane 来包装)
            if (scrollPane != null) {
                jf.remove(scrollPane);
            }
            try (
                // 根据用户输入的SQL执行查询
                ResultSet rs = stmt.executeQuery(sqlField.getText())) {
                //取出ResultSet的MetaData
                ResultSetMetaData rsmd = rs.getMetaData();
                Vector<String> columnNames = new Vector<>();
                Vector<Vector<String>> data = new Vector<>();
                // 把ResultSet的所有列名添加到Vector里
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    columnNames.add(rsmd.getColumnName(i + 1));
                }
                // 把ResultSet的所有记录添加到Vector里
                while (rs.next()) {
                    Vector<String> v = new Vector<>();
                    for (int i = 0; i < rsmd.getColumnCount(); i++) {
                        v.add(rs.getString(i + 1));
                    }
                    data.add(v);
                }
                JTable table = new JTable(data, columnNames);
                scrollPane = new JScrollPane(table);
                // 添加新的 Table
                jf.add(scrollPane);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // @Override
        // public void actionPerformed(ActionEvent e) {
        // }
    }
    public static void main(String[] args) {
        new QueryExecutor().init();
    }
}

6. Java7 的 RowSet 1.1

  1. RowSet 接口继承了 ResultSet 接口, RowSet 接口包含了 JdbcRowSet, CachedRowSet, FilteredRowSet, JoinRowSet 和 WebRowSet 常用子接口; 其中 JdbcRowSet需要保持与数据库的连接之外, 其余4个子接口都是离线的RowSet, 无须保持与数据库的连接

  2. RowSet 默认是可滚动, 可更新, 可序列化的结果集, 而且作为 JavaBean 使用, 因此能方便的在网络上传输, 用于同步两端的数据

  3. 对离线的 RowSet, 程序在创建 RowSet 时已把数据从底层数据库加载到内存,从而降低数据库服务器的负载, 提高性能

6.1 Java7 新增的 RowSetFactory 与 RowSet

  1. java 6.0 之前没有为 RowSet 5个接口提供实现类, 所以用的不广泛
  2. Java 7 新增了 RowSetProvider类 和 RowSetFactory 接口, 其中 RowSetProvider 负责创建 RowSetFactory,而 RowSetFactory 通过方法创建 RowSet 实例, 这样就把应用程序与RowSet实现类分离开

RowSetFactory 提供的创建 RowSet 实例的方法:

  1. CachedRowSet createCachedRowSet()
    创建一个默认的 CachedRowSet
  2. FilteredRowSet createFilteredRowSet()
    创建一个默认的 FilteredRowSet
  3. JdbcRowSet createJdbcRowSet()
    创建一个默认的 JdbcRowSet
  4. JoinRowSet createJoinRowSet()
    创建一个默认的 JoinRowSet
  5. WebRowSet createWebRowSet()
    创建一个默认的 WebRowSet

示例代码

// 执行结果查询正常, 但是更新数据异常
import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
// 
public class RowSetFactoryTest {
    MyINI myini = new MyINI();
    //
    public void update(String sql) throws Exception {
        Class.forName(myini.driver);
        //
        // 使用 RowSetProvider 创建 RowSetFactory
        RowSetFactory factory = RowSetProvider.newFactory();
        try (
            // 使用 RowSetFactory 创建默认的 JdbcRowSet 实例
            JdbcRowSet jdbcRs = factory.createJdbcRowSet()) {
            jdbcRs.setUrl(myini.url);
            jdbcRs.setUsername(myini.user);
            jdbcRs.setPassword(myini.pass);
            // 设置SQL查询语句
            jdbcRs.setCommand(sql);
            // 执行查询语句
            jdbcRs.execute();
            jdbcRs.afterLast();
            // 向前滚动结果集
            while (jdbcRs.previous()) {
                System.out.println(jdbcRs.getString(1)
                    + "\t" + jdbcRs.getString(2));
                    // + "\t" + jdbcRs.getString(3)
                    // + "\t" + jdbcRs.getString(4)
                // if (jdbcRs.getInt("category_id") == 4) {
                //     // 修改指定记录
                //     jdbcRs.updateString("category_name", "PYTHON");
                //     jdbcRs.updateRow();
                // }
            }
        }
    }
    public static void main(String[] args) throws Exception {
        RowSetFactoryTest rsft = new RowSetFactoryTest();
        rsft.update("select * from category");
    }
}

6.2 离线 RowSet

  1. 离线 RowSet 会直接将底层数据读入内存中, 封装成 RowSet 对象, 而 RowSet 对象则完全可以当成 java Bean 来使用,
  2. CachedRowSet 是所有离线 RowSet 的父接口

示例代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
//
public class CachedRowSetTest {
    static MyINI myini = new MyINI();
    //
    public CachedRowSet query(String sql) throws Exception {
        // 加载驱动
        Class.forName(myini.driver);
        // 获取数据库连接
        Connection conn = DriverManager.getConnection(myini.url, myini.user, myini.pass);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        // 使用 RowSetProvider 来创建 RowSetFactory
        RowSetFactory factory = RowSetProvider.newFactory();
        // 创建默认的 CachedRowSet 实例
        CachedRowSet cachedRs = factory.createCachedRowSet();
        // 使用 ResultSet 装填 RowSet
        cachedRs.populate(rs);
        // 关闭资源
        rs.close();
        stmt.close();
        conn.close();
        return cachedRs;
    }
    //
    public static void main(String[] args) throws Exception {
        CachedRowSetTest ct = new CachedRowSetTest();
        CachedRowSet rs = ct.query("select * from user");
        rs.afterLast();
        while (rs.previous()) {
            System.err.println(rs.getString(1) 
                + "\t" + rs.getString(2)
                + "\t" + rs.getString(3)
                + "\t" + rs.getString(4));
            if (rs.getInt("user_id") == 2) {
                rs.updateString("create_date", "2019-11-29");
                rs.updateRow();
            }
        }
        Connection conn = DriverManager.getConnection(myini.url, myini.user, myini.pass);
        conn.setAutoCommit(false);
        // 把对RowSet 所做的修改同步到底层数据库
        rs.acceptChanges(conn);
    }
}

6.3 离线RowSet的查询分页

  1. 由于 CachedRowSet 会将数据记录直接全部加载到内存, 因此如果SQL查询返回的结果太大, 可能会占用大量内存, 导致内存溢出
  2. 分页查询功能就是一次只装载 ResultSet 里的某几条记录, 避免 CachedRowSet 占用大量内存

CachedRowSet 控制分页的方法:

  1. populate(RestltSet rs, int startRow)
    使用给定的 ResultSet 装填 RowSet, 从 ResultSet 的 第 startRow 条记录开始装填
  2. setPageSize(int pageSize)
    设置 CachedRowSet 每次返回多少条记录
  3. previousPage()
    在底层 RestltSet 可用的情况下, 让 CachedRowSet 读取上一页记录
  4. nextPage()
    在底层 RestltSet 可用的情况下, 让 CachedRowSet 读取下一页记录

示例代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
// 
public class CachedRowSetPage {
    static MyINI my = new MyINI();
    /**
    * 
    * @param sql  需要查询的SQL语句
    * @param pageSize  每页显示的记录条数
    * @param page  需要查询的页数
    * @return  返回 CachedRowSet 类型的实例
    * @throws Exception
    */
    public CachedRowSet query(String sql, int pageSize
        , int page) throws Exception {
        //加载驱动
        Class.forName(my.driver);
        try (
            // 获取数据库连接
            Connection conn = DriverManager.getConnection(my.url, my.user, my.pass);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql)){
            // 使用 RowSetProvider 来创建 RowSetFacory
            RowSetFactory factory = RowSetProvider.newFactory();
            // 创建默认的 CachedRowSet 实例
            CachedRowSet cachedRs = factory.createCachedRowSet();
            // 设置每页显式 pageSize 条记录
            cachedRs.setPageSize(pageSize);
            // 使用 ResultSet 装填 RowSet, 设置从第几条记录开始
            cachedRs.populate(rs, (page - 1) * pageSize + 1);
            return cachedRs;
        }
    }
    public static void main(String[] args) throws Exception {
        CachedRowSetPage cp = new CachedRowSetPage();
        // 下面代码显式要查询第2页的记录, 每页显式3条记录
        CachedRowSet rs = cp.query("select * from test_user", 3, 1);
        // 向后滚动结果
        while (rs.next()) {
            System.err.println(rs.getString(1) 
            + "\t" + rs.getString(2)
            + "\t" + rs.getString(3)
            + "\t" + rs.getString(4)); 
        }
    }
}

7. 事务处理

7.1 事务的概念和MySQL事务支持

事务:

  1. 事务是由一步或几步数据库操作序列组成的逻辑执行单元, 这系列操作要么全部执行, 要么全部放弃执行
  2. MySQL默认关闭事务(即打开自动提交)用户在MySQL控制台输入一条DML语句, 这条DML语句会立即保存在数据库里面;
  3. 通过 SET AUTOCOMMIT = {0 | 1}; 0 为关闭自动提交, 即开启事务; 自动提交和开启时事务恰好相反, 如果开启自动提交就关闭事务, 关闭自动提交就是开启事务
  4. 当通过 SET AUTOCOMMIT=0; 来显式开启事务时, 命令行所有输入的DML语句都不会立即生效, 上一个事务结束后第一条DML语句将开始一个新的事务, 而后续执行的所有SQL语句都处于该事务中, 除非显式的使用commit提交事务,或者正常退出, 或者运行 DDL, DCL语句导致事务隐式的提交. 也可以使用 rollback 回滚来结束事务, 使用 rollback结束事务将导致本次事务中 DML 语句所做的修改全部失效

事务的4个特性:

  1. 原子性(Atomicity): 事务是应用中最小的执行但无阿, 是不可再分的最小逻辑单元
  2. 一致性(Consistency): 事务执行的结果, 必须使数据库从一个一致性状态, 变到另一个一致性状态; 同时成功, 或同时失败
  3. 隔离性(Isolation): 各个事务的执行互不干扰, 任意一个事务的内部操作对其他并发的事务都是隔离的; 即并发执行的事务之前不能看到对方的中间状态, 并发执行的事务之间不能互相干扰
  4. 持续性(Durability): 持续性也被称为持久性(Persistence), 指事务一旦提交, 都数据所做的任何改变都要记录到永久存储器总

数据库的事务语句组成:

  1. 一组DML语句, 经过这组DML语句修改后的数据将保持较好的一致性
  2. 一条DDL语句
  3. 一条DCL语句
    DDL和DCL语句最多只能有一条, 因为DDL和DCL语句都会导致事务立即提交

事务提交的两种方式:(都会结束当前事务)

  1. 显式提交: 使用 commit
  2. 自动提交: 执行DDL和DCl语句, 或者程序正常退出

回滚事务(全部回滚)的两种方式:(都会结束当前事务)

  1. 显式回滚: 使用 rollback
  2. 自动回滚: 系统错误或强制退出

事务的中间点(可以回滚到中间点):

  1. 通过 savepoint 来设置事务的中间点可以让事务回滚到指定的中间点, 而不是回滚全部事务
  2. 设置中间点语法: savepoint a;
  3. 回滚到中间点: rollback to a;

开启或关闭事务:

SET AUTOCOMMIT = 0; -- 开启事务
SET AUTOCOMMIT = 1; -- 关闭事务

临时性的开启事务:(例子1)

  1. 临时性的开启事务表示只是开始一次事务
  2. 通过 transaction 或者 begin 两个命令来开启临时性事务
  3. 处于 transaction 或者 begin 两个命令后的 DML 语句不会立即生效, 除非使用commit显式提交, 或者执行DDL, DCL语句隐式提交事务

示例代码

    -- 临时性开启事务
    begin;
    -- 插入三条数据, 并设置中间点
    insert into user 
    values(3, 'aa', '2019/11/28', 0);
    -- 设置中间点
    savepoint a;
    insert into user 
    values(4, 'bb', '2019/11/28', 0);
    insert into user 
    values(5, 'cc', '2019/11/28', 0);
    -- 查询user表
    select * from user;
    -- 回滚到中间点
    rollback to a;
    -- 回滚事务
    rollback;

7.2 JDBC 的事务支持

  1. JDBC默认打开的是自动提交,即关闭事务

  2. 通过调用Connection 的 setAutoCommit() 方法来关闭自动提交, 开启事务
    语法: conn.setAutoCommit(false);

  3. 开启事务之后, 程序可以创建多个Statement对象, 执行任意条DML语句, 然后通过调用 Connection 的 commit() 方法来提交事务; 或者通过调用其 rollback() 方法来进行事务回滚

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

  5. Connection 也可以通过 Savepoint setSavepoint() 或者 Savepoint setSavepoint(String name) 来设置未命名或已命名的中间点; 事务回滚时, 可以调用 rollback(Savepoint savepoint) 来回滚到指定的中间点

7.3 Java8 增强的批量更新(了解)

  1. 使用批量更新之前需要先创建一个 Statement 对象, 然后利用该对象的 addBatch() 方法将多条SQL语句同时收集起来,最后调用 java8 为 Statement对象新增的 executeLargeBatch()(MySQL不支持) 或(executeBatch())方法同时执行这些SQL语句
  2. 执行 executeLargeBatch() 将返回要给 lang[] 类型的数组, 如果在批量更新的addBatch()方法中添加了 select 查询语句, 程序将直接报错
  3. 批量更新必须作为事务来处理, 如果在批量更新时失败, 则让事务回滚到批量操作之前的状态, 所有在批量操作之前必须关闭自动提交

示例代码

// 保存当前的自动提交模式
boolean autoCommit = conn.getAutoCommit();
// 关闭自动提交
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
// 使用 Satement 同时收集多条SQL语句
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
stmt.addBatch(sql4);
// 设置中间点
conn.setSavepoint("a");
// 同时提交所有的SQL语句
stmt.executeBatch();
// 提交修改
conn.commit();
// 恢复原有的自动提交模式
conn.setAutocommit(autoCommit);

8. 分析数据库信息

8.1 使用DatabaseMateData 分析数据库信息

  1. JDBC 提供了 DatabaseMateData 来封装数据库连接对应数据库的信息, 通过Connection提供的 getMateData() 方法就可以获取数据库对应的 DatabaseMateData 对象
  2. 许多 DatabaseMateData 方法以 ResultSet 对象的形式返回查询信息, 然后使用 ResultSet 的常规方法(如: getString() getInt())即可从这些 ResultSet 对象中获取数据; 如查询的信息不可用,将返回空的 ResultSet 对象

示例代码

package app;
// 
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
// 
public class DatabaseMetaDataTest {
    MyINI my = new MyINI();
    // 
    public void info() throws Exception {
        Class.forName(my.driver);
        try (
            Connection conn = DriverManager.getConnection(my.url, my.user, my.pass)) {
            // 获取 DatabaseMateData 对象
            DatabaseMetaData dbmd = conn.getMetaData();
            // 获取MySQL支持的所有表类型
            ResultSet rs = dbmd.getTableTypes();
            System.out.println("----MySQL支持的表类型信息----");
            System.out.println(rs);
            // 获取当前数据库的全部数据表
            rs = dbmd.getTables(null, null, "%", new String[] { "TABLE" });
            System.out.println("----当前数据库的所有数据表信息----");
            System.out.println(rs);
            // 获取 user 的主键
            rs = dbmd.getPrimaryKeys(null, null, "user");
            System.out.println("----user表的主键信息----");
            System.out.println(rs);
            // 获取当前数据库的全部存储过程
            rs = dbmd.getProcedures(null, null, "%");
            System.out.println("----当前数据库里的全部存储过程信息----");
            System.out.println(rs);
            // 获取blog表和user表之间的外键约束
            rs = dbmd.getCrossReference(null, null, "blog", null, null, "user");
            System.out.println("----blog表和user表之间的外键约束----");
            System.out.println(rs);
            // 获取blog表的全部数据列
            rs = dbmd.getColumns(null, null, "blog", "%");
            System.out.println("----blog表的全部数据列----");
            System.out.println(rs);
        }
    }
    // 
    public void printResultSet(ResultSet rs) throws SQLException {
        ResultSetMetaData rsmd = rs.getMetaData();
        // 打印 ResultSet的所有列标题
        for (int i = 0; i < rsmd.getColumnCount(); i++) {
            System.out.print(rsmd.getColumnName(i + 1) + "\t");
        }
        System.out.println("\n");
        // 大隐隐ResultSet 里的全部数据
        while (rs.next()) {
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                System.out.println(rs.getString(i + 1) + "\t");
            }
            System.out.println("\n");
        }
        rs.close();
    }
    public static void main(String[] args) throws Exception {
        DatabaseMetaDataTest dt = new DatabaseMetaDataTest();
        dt.info();
    }
}

8.2 使用系统表分析数据库信息

  1. 除了使用 DatabaseMetaData 来分析数据库底层信息之外, 还可以使用数据库的系统表来分析数据库信息系统表又叫数据字典, 通常由数据库系统负责维护, 用户只能查询信息, 但是不能修改数据字典的内容
  2. MySQL 数据库使用 infomation_schema 数据库来保存系统表

MySQL系统表:

  • tables: 存放数据库里的所有数据表信息
  • schemata: 存放数据库里所有数据库(与MySQL的schema对应) 的信息
  • views: 存放数据库里的所有视图的信息
  • columns: 存放数据库里的所有列的信息
  • trigger: 存放数据库里所有触发器的信息
  • routines: 存放数据库里所有存储过程和函数的信息
  • key_column_usage: 存放数据库里所有具有约束的键的信息
  • table_constraints: 存放数据库里全部约束的表信息
  • statistics: 存放数据库里全部索引的信息

9. 使用连接池管理连接

  1. 前面学习的通过 DriverManager 来创建一个数据库连接; 一个数据库连接均对应一个物理数据库连接, 每次操作都打开一个物理连接, 使用完毕后立即关闭
  2. 数据库连接池: 当应用程序启动时, 系统主动建立足够的数据库连接, 并将这些连接组成一个池, 每次应用程序请求数据库连接时, 无需重新打开连接, 而是从连接池中取出已有的连接使用, 使用完毕后不在关闭数据库连接而是直接将连接归还给连接池, 这样可以提高效率

9.1 DBCP 数据源

  1. DBCP是Apache下的开源连接池实现, 它依赖另一个开源系统, common-pool
  2. 使用该方式需要在系统中添加: commons-dbcp.jar(连接池的实现) 和 commons-pool.jar (连接池实现的依赖库)
  3. 下载站点

示例代码

    /**
     * 数据源和数据库连接不同 , 数据源无须创建多个, 它是产生数据库连接的工厂, 因此整个应用需要一个数据源即可
     * 一般情况: 可以将下面代码中的数据源对象设置成 static 成员变量, 并且在应用程序开始时立即初始化数据源对象, 程序中
     * 所有需要获取数据连接的地方直接范围该ds对象, 并获取数据库连接即可
     */
    // DBCP 获得数据库连接的方式
    // 创建数据源对象
    BasicDataSource ds = new BasicDataSource();
    // 设置连接池所需的驱动
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
    // 设置数据库连接的URL
    ds.setUrl("jdbc:mysql://localhost:3306/blog?useSSL=false&serverTimezone=UTC");
    // 设置数据库的用户名
    ds.serUsername("root");
    // 设置数据库连接的密码
    ds.setPassword("pass");
    //设置连接池的初始连接数
    ds.setInitialSize(5);
    // 设置连接池最多可有多少个活动的连接数
    ds.setMaxActive(20);
    //设置连接池中最少有2个空闲的连接
    ds.setMinIdle(2);

示例代码

    // 通过 DataSource 获取数据库连接的例子
    Connection conn = ds.getConnection();
    //关闭数据库连接
    // 这里的关闭不是物理连接的关闭, 只是将数据库连接交还给了连接池
    conn.close();

9.2 C3P0 数据源

  1. C3P0 相比于 DBPC 性能更好; C3P0 连接池不仅可以自动清理不在使用的 Connection, 还可以自动清理Statement和ResultSet
  2. 使用 C3P0 需要添加 c3p0-0.9.1.2.jar(C3P0连接池的实现) 的JAR文件
  3. JAR文件下载站点

示例代码

    //通过 C3P0 连接池来获取数据库连接:
    //创建连接池实例
    ComboPooledDataSource ds = new ComboPooledDataSource();
    / 设置连接池所需的驱动
    ds.setDriverClass("com.mysql.cj.jdbc.Driver");
    // 设置数据库连接的URL
    ds.setJdbcUrl("jdbc:mysql://localhost:3306/blog?useSSL=false&serverTimezone=UTC");
    // 设置数据库的用户名
    ds.serUser("root");
    // 设置数据库连接的密码
    ds.setPassword("pass");
    //设置连接池最大连接数
    ds.setMaxPoolSize(40);
    // 设置连接池的最小连接数
    ds.setMinPoolSize(2);
    // 设置连接池的初始连接数
    ds.setInitialPoolSize(10);
    // 设置连接池的缓存 Statement 的最大数
    ds.setMaxStatements(180);
    // 
    // 获取的数据库连接
    Connection conn = ds.getCoonnection();

# Java