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);