目录
第二章 Statement(executeQuery/executeUpdate/execute)的用法与PrepareStatement的介绍
第三章 CallableStatement与存储过程、PrepareStatement模糊查询
1、分析Statement和PreparedStatment
第一章 初识JDBC
一、主要知识点
1、JDBC概述
- 什么是JDBC【掌握】
英文命名:Java DataBase Connection(JDBC),Java数据库连接,是一组由Java编写的类和接口组成。
- JDBC实现的功能【掌握】
功能:
- 用于执行数据库访问的应用程序API
2) 用统一的语法对多种关系数据库进行访问,不必关心数据库语言之间的差异
- JDBC的体系结构【了解】
JDBC的体系结构分为两层:
- 驱动程序管理器接口(JDBC Driver Interface)
- JDBC应用程序接口(JDBC API),即包含的类和接口
- JDBC的特点【理解】
优点:
1)使开发人员从复杂的不同的驱动器调用接口解脱出来,主要关注核心的业务逻辑即可
2)支持不同的数据库,大大增强了程序的移植性
3)JDBC的API是面向对象的,可以对其提供的方法进行二次封装
缺点:
- 使访问数据库的速度受一定的影响(肯定不如直接操作数据库快)
2)JDBC体系结构中包含不同厂家的产品,更改数据源时会带来一定的麻烦
- JDBC的核心接口与类【掌握】
- 核心类:
DriverManager: 负责管理JDBC的驱动程序
SQLException: 封装了数据库操作产生的异常
- 核心接口:
Connection: 代表特定数据库的连接,即连接通道
Statement: 执行静态SQL语句
PreparedStatement: 用于执行预编译的SQL语句,是Statement的子接口
ResultSet: 代表执行查询语句返回的结果集
CallableStatement:用于执行存储过程,并处理存储过程返回的结果
2、创建JDBC应用
- 创建JDBC应用程序的步骤【重点掌握】
- 载入JDBC驱动程序
- 建立连接,需要指定连接数据库的URL、用户名、密码(这是连接数据库的三要素)
- 创建statement对象
- 执行SQL语句
- 处理结果集
- 关闭数据库的连接
- 数据库驱动程序【深入理解】
分类:
1)Type1: JDBC-ODBC桥
通过JDBC-ODBC桥来操作ODBC数据源对应的数据库,此种方法需要服务器安装ODBC组件。
2)Type2: 本地API驱动
把JDBC调用转变为数据库的标准调用函数再去访问数据库,此种方法需要本地数据库驱动代码
3)Type3: 网络协议驱动
使用网络协议将请求发送给中间服务器,再由中间服务器去访问数据库服务器
4)Type4: 本地协议驱动(目前最常用的)
直接把JDBC调用转换为符合数据库系统协议规范的请求,属于直接调用数据库,省略中间组件或中间服务器环节。
完全由Java编写,实现了平台独立性。
注意:4)也叫直连,可以用现实生活中的找对象的例子阐述(传统找对象用媒婆,现在不用)
连接Oracle11g的驱动程序:
- 按步骤实现JDBC操作
- 步骤1:加载JDBC驱动程序
会抛出:ClassNotFoundException异常,用try catch捕获
- 步骤2:建立连接
会抛出:SQLException异常,用try catch捕获
- 步骤3:创建Statement对象
-
- 步骤4:执行SQL语句
- 步骤5:处理结果集
-
- 步骤6:关闭数据库连接
二、示例代码
示例1、查询scott用户下的部门信息
示例2、重构示例1,把结果集的数据放入部门对象,并把部门对象放入List里
重构后的项目结构如下:
实体类DeptInfo的结构如下:
DeptDB类:
三、补充知识点
1、sql语句的写法:select *和select 字段列表
推荐使用select字段列表,性能要比select *要高;因为select *要执行全表扫描。
2、sql语句在程序中的编写规范
SQL关键字一律大写,其他可小写
3、Oracle经历的版本号:
Oracle 8i/9i
I:internet:改变了Oracle的访问方式
Oracle 10g/11g
g:grid,改变了Oracle的数据存储方式,提高了数据查询效率
Oracle 12c
C:component:改变了Oracle实例的运行方式
4、关于开发工具Eclipse Mars
Eclipse版本:Mars 4.5
JDK版本:JDK1.7+ 6位
5、安装Eclispe和JDK说明
1)直接解压到没有中文和空格的目录里
2)配置JDK的环境变量JAVA_HOME,并把JAVA_HOME配置到Path里
3) 验证JDK是否配置成功,在cmd窗口输入javac -version或者java –version
后,如果显示版本号,则证明配置成功
6、包的作用及命名规则:
1)包是用来区分相同类名的类
2)包的命名规则反域名
如:www.neusoft.com--域名 反域名:com.neusoft.www
Cls3.neusoft.com
四、常见错误/异常分析
1、如果把驱动程序的包名写错,会报什么异常?
2、如果把url写错(ip地址、端口号),会报什么异常?
3、如果把用户名/密码写错,会报什么异常?
4、在处理结果集时,有如下代码片断,位置(1)、位置(2)、位置(3)代码因写法不同,运行结果会如何?哪种写法代码最优呢?
5、结果集里的字段大小写与程序中SQL语句中的字段大小写有关系吗?
6、出下如下异常可能的原因:
1)
产生的原因:
- Oracle服务器上的监听没有启动,启动:lsnrctl start
- Oracle数据库的ip地址、端口号写错了
2)
产生的原因:
- 监听器的启动顺序后于oracle实例的启动,则会导致此异常;应该先启动监听,然后再启动oracle的实例
- 实例名写错了
五、课后作业
- 什么是JDBC,何时用到JDBC?
答:Java DataBase Connection,java数据库连接,是一组由java编写的类和接口组成
2、JDBC有哪些特点?
3、JDBC有哪些核心类和接口,其作用分别是什么?
4、创建JDBC的核心步骤有哪些?
5、数据库驱动程序分为哪几类,其区别是什么?
6、上机示例:按照JDBC的标准步骤,查询scott用户下的emp表的信息员工编号、姓名、职位、薪水、部门名称。
第二章 Statement(executeQuery/executeUpdate/execute)的用法与PrepareStatement的介绍
一、主要知识点
1、创建JDBC应用
1)执行SQL语句,用Statement对象
Statement执行SQL语句的各种方法:
- executeQuery(String sql) 【掌握】
- 执行SQL查询语句,并返回单个结果集。
- 此方法只执行数据库的SELECT的语句
- executeUpdate(String sql) 【掌握】
- 用于执行 INSERT、UPDATE 或 DELETE 语句
- 也可用于执行SQL DDL(数据定义语言)语句,例如 CREATE TABLE 和 DROP TABLE。此种情况很少应用,因为在正式企业应用中,数据库中的表只被创建一次,而对表中的记录可以执行多次更新操作(包括insert、update、delete)
- INSERT、UPDATE 或 DELETE 语句的效果是修改表中零行或多行中的一列或多列。
- executeUpdate 的返回值是一个整数,指示受影响的行数(即更新计数)。
- 对于 CREATE TABLE 或 DROP TABLE 等不操作行的语句,executeUpdate 的返回值总为零。
- execute(String sql) 【了解】
- 用于执行返回多个结果集、多个更新计数或二者组合的语句。此种情况不常见,此方法很少被应用
2)SQL类型对应的Java的数据类型
数据库有Date类型,强烈推荐用Java.util.Date类,因为java.sql.date已经被淘汰了。
3)ResultSet接口的方法【掌握】
- next():记录指针移动,有记录则返回true,无记录则返回false
- getXXX(String columnName):根据指定的列名来获取结果集中对应列的值
- getXXX(int columnNo):根据指定的索引号来获取结果集中对应的列的值,注意:索引号是从1开始的
4)JDBC日期时间处理
2、连接查询(扩展)
Oracle连接的种类:
- 相等连接【掌握】
通过两个表具有相同意义的列,可以建立相等连接条件。
示例1:查询员工信息以及对应的员工所在的部门信息
结果如下:
注意:deptno的值在两个表中都出现且值相等的行才会出现在查询结果中。Deptno=40的部门信息没有在结果集中出现
- 不等连接【掌握】
两个表中的相关的两列进行不等连接,比较符号一般为BETWEEN.. AND..、 > 、<
示例2:显示员工的编号,姓名,工资,以及工资所对应的级别
结果如下:
- 外连接
以下三种连接中的关键字OUTER可省略不写
- 左外连接(LEFT OUTER JOIN/ LEFT JOIN) 【掌握】
LEFT JOIN是以左表的记录为基础的,即左边的表的记录不加限制。
示例3:显示员工信息以及所对应的部门信息(注意:此时无法显示没有员工的部门信息)
结果集:
- 右外连接(RIGHT OUTER JOIN/RIGHT JOIN)【掌握】
和LEFT JOIN的结果刚好相反,是以右表记录为基础的,右边的表的记录不加限制。结果集的记录数以右表为主
示例4:显示员工信息以及所对应的部门信息,显示没有员工的部门信息
结果集如下:
- 全外连接(FULL OUTER JOIN/FULL JOIN)【不常用】
左表和右表的记录都不做限制,所有的记录都显示,两表不足的字段均显示为NULL
示例5:显示员工表和部门两个表的所有信息
结果集如下:
- 自连接【掌握】
自连接是数据库中经常要用的连接方式;使用自连接,可以将自身表的一个镜像当作另一个表来对待,从而能够得到一些特殊的数据。
请看示例,示例:显示雇员的编号,名称,以及该雇员的经理名称
员工表的记录如下:
结果集如下:
二、示例代码
1、用列的索引号完成部门信息的查询
2、获取员工表里的员工的薪资和入职时间
员工信息实体类:
结果集的取法:
3、获取员工的姓名、入职时间、薪资、所在部门
提示:用左外连接
4、用视图完成:获取员工的姓名、入职时间、薪资、所在部门
提示:多表连接,导致SQL语句过长,可在Oracle中创建视图
5、完成新增一个部门信息
6、完成一个新增员工信息
三、补充知识点
1、Oracle如何创建和使用视图
1)如果用普通用户创建视图,则要给普通授予DBA的权限(注意:授权只能用sys用户登录给普通用户授权)
注意:如果撤销DBA的权限,如下:
2)用普通用户登录创建视图
3)使用视图查询结果集
2、Oracle几中连接的区别
参考:主要知识点2
3、视图和表的区别
1)视图是按照你的sql语句生成的一个虚拟的东西,本身并不占数据库空间;
2)表是物理存在的,需要占据数据库的存储空间
3) JDBC操作数据库时,在网络上传输视图要比传输一大堆SQL语句快,减轻网络传输压力
4、DDL、DCL、DML主要指哪些
DDL(CREATE、DROP):操作表
DCL(GRANT、REVOKE):操作系统用户的权限
DML(SELECT、INSERT、UPDATE、DELETE):操作表的记录
5、Oracle的缓存和事务
6、程序的健壮性:
当数据库的某个字段为空值的时候,在程序里一定要判断取出来的值是不是Null,增强程序的健壮性。
- StringBuffer:
Buffer是缓存的意思,String
链式编程:stringBuffer.append(“”).append(“”);
四、异常及错误分析
异常1:在获取结果的字段的值 的时候,出现如下异常
可能 的原因:
- 字段的名称写错
- SQL语句写错
异常2:向数据库的DEPT表插入字段时,出现如下异常
可能的原因:
- 给字段赋值过大,Oracle一个汉字占3-4个字节,一个英文字符占1个字节
异常3:向数据库的DEPT表插入字段时,出现如下异常
可能的原因:
- 给有唯一约束的字段插入了重复的值
异常4:使用列索引获取结果集的值时,出如下异常:
可能的原因:
没有把索引号从1开始,而是从0开始
异常4:
日期显示格式有误,正确的写法是
五、课后作业
- 利用3w1h方法,总结当天笔记
知识点:一、SQL的数据类型与Java的数据类型的对比 二、Oracle的连接(在实际开发中常用的) 三、调用Oracle视图 一、
BIT Boolean NUMBER(N,M) BigDecimal Date Date(java.util.Date)强烈推荐 (java.sql.Date)已淘汰 -------------------------------------------------------------------------------------------------------------------------------- 注:BigDecimal在小数的精度方面比Double表示得更加准确,不用扩大小数的精度,也不用缩小小数的精度。 2、JDBC时间处理 将yyyy-MM-dd输出成为yyyy年MM月dd日 SimpleDateFormat sdf=new SimpleDateFormat(); Date date =new Date(); String str=sdf.format(date); 3、大写MM表示月,小写mm表示分钟
二、
executeQuery(String sql)------------------------SELECT executeUpdate(String sql)-----------------------UPDATE、DELETE、INSERT、CREATE、DROP execute(String sql) --------------------------------不常用 3、Oracle的缓存和事务 4、闪回技术:把真正删除的内容找回 三、 1、视图是伪表,加快了网络传输的速度 2、创建视图,例子:
3、通过视图建立查询
4、sys超级用户密码是tiger 授权的SQL语句:grant DBA to XXX 撤权的SQL语句:revoke DBA from XXX 5、列索引从1开始 6、类名的命名: 1)见名之意:看到这个类知道这个类做什么(主要功能) 2)类名首字母大写,驼峰命名法 3)每个类只做一件事 |
2、简要说明视图和表的区别?
(1)表是物理存在的,有实际的物理记录,是实表,视图是虚拟的内存表,没有实际的物理记录,是虚表
(2)表是占用物理空间,而视图不占用物理空间,只是逻辑概念的存在
(3)表是内模式,视图是外模式
(4)视图的建立和删除只影响视图本身,不影响表结构
(5)视图是建立在表的基础上的
- Oracle有几种连接方式,它们之间的区别是什么?
(1)相等连接:通过两个表具有相同意义的列,可以建立相等连接条件。
(2)不等连接:两个表中的相关的两列进行不等连接,比较符号一般为BETWEEN..AND..、 > 、<
(3)外连接:以下三种连接中的关键字OUTER可省略不写
左外连接(LEFT OUTER JOIN/ LEFT JOIN)
右外连接(RIGHT OUTER JOIN/RIGHT JOIN)
全外连接(FULL OUTER JOIN/FULL JOIN)
4)自连接:自连接是数据库中经常要用的连接方式;使用自连接,可以将自身表的一个镜像当作另一个表来对待,从而能够得到一些特殊的数据。
- Oracle的SQL语句分为哪几类,分别是什么及其作用是什么?
(1)数据操作(DML):用来操作数据库中数据的命令。包括:select、delete、update、insert
(2)数据控制(DCL):用来控制数据库组件的存取许可、权限等的命令。包括:grant、deny、revoke。
(3)数据定义(DDL):用来建立数据库、数据库对象和定义列的命令。包括:create、alter、drop。
- String和StringBuffer的区别
(1)String 是常量,而StringBuffer是变量。StringBuffer对象的内容可以修改,而String对象一旦生成后就不可以被修改,重新赋值其实是两个对象;
(2)StringBuffer的内部实现方式和String不同,StringBuffer在进行字符串处理时,不生成新的对象,在内存使用上要优于String类。所以在实际使用时,如果经常需要对一个字符串进行修改,例如插入、删除等操作,使用StringBuffer要更加适合一些。
(3)String:在String类中没有用来改变已有字符串中的某个字符的方法,由于不能改变一个Java字符串中的某个单独字符,所以在JDK文档中称String类的对象是不可改变的。然而,不可改变的字符串具有一个很大的优点:编译器可以把字符串设为共享的。StringBuffer:StringBuffer类属于一种辅助类,可预先分配指定长度的内存块建立一个字符串缓冲区。这样使用StringBuffer类的append方法追加字符 比 String使用 + 操作符添加字符 到 一个已经存在的字符串后面有效率得多。
(4)StringBuffer是线程安全的,在多线程程序中也可以很方便的进行使用,但是程序的执行效率相对来说就要稍微慢一些。在线程安全上,StringBuilder是线程不安全的,而StringBuffer是线程安全的
String:适用于少量的字符串操作的情况
StringBuilder:适用于单线程下在字符缓冲区进行大量操作的情况
StringBuffer:适用多线程下在字符缓冲区进行大量操作的情况
处理速度:StringBuilder>StringBuffer>String
6、上机完成:
1)用JDBC完成:
1)创建表:T_User(id[int],uname,password,email,birthday,age),T_UserType(id[int],tname)
2)分别向用户表里插入10条记录,用户类型表里5条记录
3)查询用户的用户名、Email、出生日期、账号类型(用视图实现)
4)统计一下每个账号类型的用户数量
5)删除某个账号类型的用户
第三章 CallableStatement与存储过程、PrepareStatement模糊查询
一、主要知识点
1、项目重构
1)背景(即目前项目存的问题):
- 几乎每个类中都有重复冗余的代码
- 没有充分利用面向对象的思想设计
- 类的设计过于繁琐
- 包的划分结构不清晰,不利于分模块开发
2)思想:采用分包(即分模块开发)的思想+纯面向对象的思想
包的结构:
com.neusoft.cl3
.db :存放操作数据库的类(如:EmpDB)
.entity :存放数据封装的javabean(如:Emp类)
.test :存放测试的类(如:EmpTest类)
.utils :存放以上几个包中可能用到的工具类(如:DBUtils类)
2、PreparedStatement
1)PreparedStatement是用来执行SQL查询语句的API之一;
Java提供了 Statement、PreparedStatement 和 CallableStatement三种方式来执行查询语句。
- Statement 用于通用查询
- PreparedStatement 用于执行参数化查询
- CallableStatement则是用于存储过程
2)PreparedStatement与Statement执行查询比较,其优势:
- PreparedStatement可以写动态参数化的查询,如:
因此:通过使用相同的sql语句和不同的参数值来做查询比创建一个不同的查询语句要好
PreparedStatement比 Statement 更快,原因在于
SQL语句会预编译在数据库系统中。
执行计划同样会被缓存起来,它允许数据库做参数化查询
使用预处理语句比普通的查询更快,因为它做的工作更少(数据库对SQL语句的分析,编译,优化已经在第一次查询前完成了)。
- PreparedStatement可以防止SQL注入式攻击
SQL注入攻击:是黑客利用SQL语句的语法不健全,在正常的SQL语句中加入非法的SQL语句执行操作。
因此 :企业级应用开发中强烈推荐使用PreparedStatement
3、CallableStatement
此接口可用来调用Oracle的存储过程;
二、示例代码
1、分析Statement和PreparedStatment
利用用户表,分析Statement和PreparedStatment查询方式的不同
Statement查询的条件是字符串拼接
PreparedStatement的查询条件是参数化查询,使用占位符
2、SQL注入攻击示例代码
1):用scott用户登录oracle,创建用户表账号表T_User(id,uname,passwd),并添加两个账号信息(amdin,1234567a?)(guest,1234567b?)
2)从控制台模拟用户登录,提示用户输入用户名和密码。
- 情景1:如果用户名和密码输入正确,提示登录成功;输入错误,提示登录失败。
运行结果:
- 情景2:采用SQL注入攻击的方式绕过用户名和密码验证,直接提示登录成功。
- 改进(或加固)措施:
- 防止SQL注入,措施一:用Statement方式实现,相当麻烦!
代码如下:
-
- 防止SQL注入,措施二:用PreparedStatement实现起来就非常简单
代码如下:
再执行SQL注入攻击,效果如下:
3、存储过程示例
以下所有代码都在PLSQL Developer里完成
示例1:
输出当前系统的时间
结果:
示例2:
用带参数存储过程实现,当参数值 是1时,向部门表插入一条信息;参数为2时,更新部门表编号为40的部门信息
示例3
如果输入1,计算某个部门的员工数量;如果输入2,计算某个部门的员工工资总和。如果输入其他,提示:输入参数错误。
4、使用CallableStatement调用存储过程
- Call存储过程名称,有参必加,无参,不需要()
- 有参情况下,参数的个数(包括IN/OUT )与?的个数与占位符一致
- SetXXX是给输入参数赋值
- RegistOutParameter(?,?);给输出参数做注册,注册的作用是为了与占位符匹配,同时在占位符处接收存储过程返回值
- Java.sql.Types.有很多枚举
- JAVA调用数据库存储过程用executeQuery();
5、PrepareStatement执行模糊查询
三、补充知识点
1、关于Scanner的使用
2、Oracle的存储过程
- 定义:
存储过程(Stored Procedure):已预编译为一个可执行过程的一个或多个SQL语句。
- 预编译的过程,过程就是一个PL/SQL块
BEGIN
SQL1;
SQL2
…….
END;
- 由n多条SQL语句组成的
- 与SQL语句对比,优势:
- 提高性能
SQL语句在创建时进行分析和编译。
存储过程是预编译的,在首次运行一个存储过程时,查询优化器对其进行分析、优化,并给出最终被存在系统表中的存储计划,这样,在执行过程时便可节省此开销。
- 降低网络开销
存储过程调用时只需用提供存储过程名和必要的参数信息,从而可降低网络的流量。
视图也具有这个优点。
- 便于进行代码移植
数据库专业人员可以随时对存储过程进行修改,但对应用程序源代码却毫无影响,从而极大的提高了程序的可移植性。
- 更强的安全性
系统管理员可以对执行的某一个存储过程进行权限限制,避免非授权用户对数据的访问。
使用过程参数有助于避免 SQL 注入攻击。 因为参数输入被视作文字值而非可执行代码,所以,攻击者将命令插入过程内的 Transact-SQL 语句并损害安全性将更为困难。
- 与SQL语句对比,不足:
- 存储过程需要专门的数据库开发人员进行维护,但实际情况是,往往由程序开发员人员兼职。
2)设计逻辑变更,修改存储过程没有SQL灵活
- 注意事项:
1)存储过程不能直接使用SELECT语句,必须把SELECT语句放到游标里,或者单独使用SELECT INTO 变量;
四、异常/错误分析
1:用PreparedStatement时,出现如下异常
可能的原因如下:
2:在PLSQL Dev进行数据修改时不提交事务,导致的结果程序读到的脏数据
五、课后作业
1、PreparedStatment与Statement相比的优势?
(1)Statement每次执行的SQL都要分析、编译、优化,而Preparedstatement只要执行一次的分析、编译、优化,之后传输的值有改变就不需要在此分析、编译、优化,直接传值即可;
(2)安全性较高,preparedstatement可以防止SQL注入,一定程度上防止了黑客的攻击。
- Oracle数据库存储过程的优点?
(1)提高性能
(2)降低网络开销
(3)便于进行代码移植
(4)更强的安全性
3、JDBC如何调用存储过程?
四种情况:
. (1)返回结果集的proc
(2) 输出参数
(3)使用带有返回状态的存储过程
(4)受影响行数
4、上机练习:
某银行要处理一批固定资产,包括钱币、IT设备、办公耗材三类资产,请用JDBC设计一个应用:
- 用户可以实现资产类型的录入、修改和查询
- 用户可以资产的录入、修改、作废(除非客户有强烈的要求物理删除,否则Update)
- 用户可以统计每个类型资产的价值(单位:元)·
//4)用JDBC+存储过程实现:如果用户输入的是1,汇总每个类型下面资产的数量;如果输入的是2,汇总所有资产的总价值
自学:
- 自学Oracle的游标
提示:利用3W1H方法学习游标
2、在Oracle的存储过程如何使用游标
3、如何利用存储过程+游标返回结果集给Java程序