第13章 MySQL数据库与JDBC编程

1.关系数据库的基本理论

JDBC为数据库开发提供了标准的API，使用JDBC开发的数据库应用可以跨平台运行，而且可以跨数据库。

MySQL数据库通常采用InnoDB，InnoDB提供事务安全的存储机制。

 

2.标准SQL语句语法

（1）分类

查询语句：select关键字

DML(数据操作语言)：主要由insert、update和delete三个关键字组成。

DDL(数据定义语言)：主要由create、alter、drop和truncate关键字完成。

DCL(数据控制语言)：主要由grant和revoke两个关键字完成。

事务控制语句：主要由commit、rollback和savepoint三个关键字完成。

SQL语句关键字不区分大小写，列用column表示。

（2）DDL语句

创建表：

Create table test
(
   Test_id int;
   Test_name varchar(255) default ‘xxx’;
);

修改表：

Alter table test
add test_price int;
add
(
    aaa varchar(255) default ‘xxx’,
    bbb varchar(255)
);

增加列定义使用add关键字，修改列定义使用modify关键字。

Alter table test
Modify test_id varchar(255);
Drop aaa;   //删除table表中aaa字段
Rename to test2;  //重命名表名

删除表：

Drop table test;

（3）数据库约束

    1）NOT NULL约束

    2）UNIQUE约束

不允许出现重复值，如：

Create table test
(
   Test_id int not null,
   Test_name varchar(255) unique,
   Constraint test_1 unique(test_price)   //constraint可以为多列组合建立唯一约束并命名
);

3）primary key约束

主键约束，既不允许出现重复值也不允许出现null值。

4）foreign key约束

外键约束，从表外键参照的只能是主表主键列或者唯一键列，同一个表可以有多个外键。对于多对多的关联关系，需要额外增加一个连接表来记录它们的关联关系。可以使用reference关键字，如：

Create table student
(
    Student_id int auto_increment primary key,
    Student_name varchar(255),
    //若设置两个主键，可采用：
    //primary key(student_id , student_name)
    Java_teacher int reference teacher_table(teacher_id)
);

如果想定义当删除主表记录时，从表记录也会随之删除，则需要在建立外键约束后添加on delete cascade或on delete set null，第一种是联级删除，第二种是把从表记录的外键设为null。

（4）索引

创建索引的唯一作用就是加速对表的查询，索引通过使用快速路径访问方法来快速定位数据，从而减少了磁盘的I/O。但索引与目录类似，需要数据库系统进行维护，有一定系统开销，并且存储索引信息需要一定的磁盘空间。

Create index emp_last_name_idx    //创建索引
On employees(last_name);
Drop index emp_last_name_idx   //删除索引
On emplyees

（5）视图

视图不是数据表，不能存储数据，提供了对相同数据的不同显示，即查询结果。

视图的本质就是一条被命名的SQL查询语句。

Create or replace view view_test
As
Select teacher_name from teacher_table
With check option;  //指定不允许修改视图的数据
Drop view view_test;  //删除视图

（6）DML语句用法

     1）insert into语句

insert into teacher_table(teacher_name) values(‘xyz’);

    2)update语句

update teacher_table
set teacher_name = “李泽阳”
where teacher_id > 1;

    3）delete from语句

delete from teacher_table
where teacher_id > 2;

(7)单表查询

 可以使用concat函数进行字符串连接运算，也可以使用别名作为列标题，同样可以使用distinct关键字清除重复行。

表1  特殊的比较运算符

 

Order by子句：查询结果按某列值的大小进行排序，降序为desc，升序为asc（默认）

(8)数据库函数

根据函数对多行数据的处理方式，函数被分为单行函数和多行函数。

分组和组函数：

Avg()、count()、max()、min()、sum()

对可能出现null的列，可以使用ifnull函数来处理该列。

Group by子句：查询结果根据一列或多列进行分组，但会导致多条记录只有一条输出，如果需要过滤，则需要having子句，如：

Select *
from student
group by java_teacher
having count(*) > 2

(9)多表连接查询

等值连接、非等值连接、外连接和广义笛卡尔积

如右外连接：

select s.* , teacher_name
from student_table s , teacher_table t
where s.java_teacher  =  t.teacher_id(*);   //将teacher表中所有符合的记录输出

(10)子查询

子查询要用括号括起来

 

3.JDBC数据库访问的详细步骤

(1)常用接口和类

DriverManager:用于管理JDBC驱动的服务类，主要功能是获取Connection对象。

Connection:代表数据库连接对象，每个Connection代表一个物理对话。

Statement：用于执行SQL语句的工具接口。执行SQL查询时，返回查询到的结果集。

PrepareStatement:预编译的Statement对象，是Statement的子接口。

ResultSet:结果集对象，可以通过列索引或列名获得列数据。

常用方法：指针移前移、后移，指定位置，释放

(2)JDBC编程步骤

    1)加载数据库驱动

通常使用Class类的forName（）静态方法加载驱动，如：加载MySQL

class.forName(“com.mysql.jdbc.Driver”);

   2)DriverManager获取数据库连接

DriverManager.getConnection(String url , String user , String password);

   3)通过Connection对象创建Statement对象

   4)使用Statement执行SQL语句（或PrepareStatement）

   5)操作结果集

   6)回收数据库资源

 

4.执行SQL语句的方式

(1)executeUpdate方法执行DDL和DML语句

使用executeUpdate方法，区别在于执行DDL语句后返回值为0，执行DML语句后返回值为受影响的记录条数。

如：

public void createTable(String SQL)throws Exception
{
  Class.foeName(driver);
  try(
    Connection conn = DriverManager.getConnection(url,user,pass);
    Statement stmt = conn.createStatement() )
    {
        Stmt.executeUpdate(SQL);
    }
}

(2)使用execute方法执行SQL语句

使用execute()方法执行SQL语句的返回值只是boolean值，表示是否返回了ReaultSet对象。两个常用方法为：

getResultSet():获取该Statement()执行查询语句后返回的ResultSet对象。

getUpdateCount()：获取该Statement()执行DML语句后所影响的记录行数。

其中ResultSet的getString()方法可以几乎获取除Blog之外的任意类型列的值，自动转换为字符串。

（3）使用PrepareStatement语句执行SQL语句

创建PrepareStatement对象使用Connection的prepareSTatement()方法，该方法需要传入一个字符串，该SQL字符串可以包含占位符参数。如：

P = conn.prepareStatement(“insert into student_table values(null , ? , 1)”);

对于结构相似的SQL语句，prepareStatement执行效率很高，相当于模板执行（预编译）。同时，prepareStatement无须拼接SQL语句，降低编程复杂度，而且还可以防止SQL注入，即利用SQL语句的漏洞来入侵。

 

5.管理结果集

（1）可滚动可更新的结果集

默认的ResultSet是不可更新的，但可以在Connection创建Statement或prepareStatement时额外传入两个参数：

    1）resultSetType:控制resultSet类型，可取：  

    ResultSet.TYPE_FOREARD_ONLY：只能向前移动

    ResultSet.TYPE_SCROLL_INSENSITIVE：控制指针自由移动，但底层数据的改变不会影响ResultSet内容。

    ResultSet.TYPE_SCROLL_SENSITIVE：控制指针自由移动，且底层数据的改变会影响ResultSet内容。

    2）resultSetConcurrency：控制ResultSet的并发类型，可取:

    ResultSet.CONCUR_READ_ONLY:表示ResultSet是只读的并发模式（默认）

    ResultSet.CONCUR_UPDATABLE:表示ResultSet是可更新的并发模式。

可更新的结果集必须满足：

     1）所有的数据都来自一个表

     2）选出的数据集必须包含主键列

 （2）处理Blob类型数据

Blob是二进制长对象，用于存储大文件，需要使用prepareStatement对象的一个方法：setBinaryStream(int parameterIndex , InputStream x)，为指定参数传入二进制输入流，实现Blob数据保存到数据库的功能。

（3）使用ResultSetMetaData分析结果集

ResultSetMetaData封装了描述ResultSet对象的数据。

常用方法有：

int getColumnCount():返回该ResultSet的列数量

String getColumnName(int column):返回指定索引的列名

int getColumnType(int column):返回指定索引的列类型

 

6.Javar的RowSet

RowSet接口继承了ResultSet接口，与ResultSet相比，RowSet默认是可滚动可更新可序列化的结果集，而且作为javabean使用，能够方便的在网络上传输，同步两端的数据。

(1)离线RowSet

离线RowSet直接将底层数据读入内存中，封装成RowSet对象，RowSet对象完全可以当成javabean使用。CachedRowSet是所有离线RowSet的父接口，通过程序返回CachedRowSet而不是ResultSet，程序依然可以读取，修改RowSet中的记录。

(2)离线RowSet查询分页

CachedRowSet提供了分页功能，常用方法有：

Populate(ResultSet rs , int startRow):使用给定的ResultSet装填RowSet，从第startRow条记录开始装填

SetPageSize(int pageSize):设置CachRowSet每次返回多少条记录。

previousPage()：在底层ResultSet可用的情况下，让CachedRowSet读取上一页记录。

nextPage()：在底层ResultSet可用的情况下，让CachedRowSet读取下一页记录。

 

7.事务处理

4个特性：原子性，一致性，隔离性和持续性。

DDL和DCL语句最多只能有一条，因为DDL和DCL语句都会导致事务立即提交。当事务所包含的任意一个数据库操作执行失败后，应该回滚（rollback）事务，分为显式回滚（即使用rollback）和自动回滚（系统错误或强行退出）。如果开启自动提交就是关闭事务，关闭自动提交（set autocommit = 0）就是开启事务。

 

JDBC连接的事务支持由Connection提供，默认打开自动提交，即关闭事务。每条SQL语句执行后，便会立即提交到数据库，无法回滚。

可以调用Connection的setAutoCommit()方法来关闭自动提交，如：

Conn.setAutoCommit(false);