Oracle_Day05(约束、JDBC)
约束
- Not null 非空约束
- UNIQUE 唯一约束
- Primary key 主键约束
- Foreign key 外键约束
- Check 检查约束
约束的创建：可以在建表时，也可以在建表后
非空约束：
NOT NULL 作用：保证插入的值不为空，作用于字段
create table STU
(
stu_id NUMBER(8) not null,
stu_name VARCHAR2(20) not null,
stu_age NUMBER(4),
stu_schooldate DATE
)
唯一约束：
UNIQUE 作用：该字段的值不能重复，值是唯一的，允许出现多个空值
create table stu(
stu_id number(8) not null,
stu_name varchar2(20) UNIQUE,
stu_age number(4),
stu_schooldate date,
stu_email varchar2(50),
constraint stu_email_uk UNIQUE(stu_email)
);
主键约束：
PRIMARY KEY = NOT NULL + UNIQUE
外键约束：
FOREIGN KEY
create table stu(
stu_id number(8),
stu_name varchar2(20) UNIQUE,
stu_age number(4)
constraint stu_age_nn NOT NULL,
stu_schooldate date,
stu_email varchar2(50),
cid number(8),--外键
constraint stu_id_pk primary key(stu_id),--指定主键
constraint stu_cls_fk foreign key(cid) references cls(cid)--关联外键
);
--班级表
create table cls(
cid number(8),--主键
cname varchar2(20) not null,
constraint cls_id_pk primary key(cid)
)
外键关联时，cls表为父表，stu为子表，子表引用了父表的数据
删除时，可以随意删除子表的数据，删除父表的数据的时候，由于关联数据可能删除失败
删除父表时，同时删除该记录所关联的所有的子表记录（on delete cascade 级联删除）
删除父表时，将子表中的外键字段设置为null （on delete set null 级联置空）
create table stu(
stu_id number(8),
stu_name varchar2(20) UNIQUE,
stu_age number(4)
constraint stu_age_nn NOT NULL,
stu_schooldate date,
stu_email varchar2(50),
cid number(8),--外键
constraint stu_id_pk primary key(stu_id),--指定主键
constraint stu_cls_fk foreign key(cid) references cls(cid) on del--关联外键
);
检查约束：
CHECK：要求在设置某字段的值的时候，必须满足一定的条件
create table stu(
stu_id number(8),
stu_name varchar2(20) UNIQUE,
stu_age number(4)
constraint stu_age_ck check(stu_age > 18),--检查约束
stu_schooldate date,
stu_email varchar2(50),
cid number(8),--外键
constraint stu_id_pk primary key(stu_id),--指定主键
constraint stu_cls_fk foreign key(cid) references cls(cid) on delete cascade--关联外键
);
新增/修改约束：
ALTER TABLE 表名称 ADD(constraint )
ALTER TABLE 表名称 MODIFY(constraint )
--修改stu表中的stu_name的唯一约束为非空约束
alter table stu modify(stu_name varchar2(20) not null);
删除约束：
DROP CONSTRAINT 约束的名称
--删除stu的外键约束
alter table stu drop constraint stu_cls_fk;
JDBC
JDBC是通过java代码操作数据库
什么是JDBC：
JDBC是规范定义的接口，是由各个数据库厂商来实现的
JDBC是Java访问数据库的标准规范，真正怎么操作数据库还需要具体的实现类 ，实现类也就是数据库驱动，每个数据库厂商根据自己的数据库的通信格式编写自己的数据库驱动，所以我们只需要调用JDBC接口中的方法即可。数据库驱动是由数据库厂商提供。
使用JDBC的好处：
- 开发人员如果要开发访问数据库的程序，只需要会调用JDBC的接口中定义的方法即可。而不用去关心数据库厂商是如何实现的。
- 使用的是同一套Java接口，进行少量的修改就可以访问所有支持JDBC规范的数据库
使用JDBC开发使用的包：
- java.sql 所有与JDBC访问数据库相关的接口和类
- javax.sql 数据库扩展包，提供数据库额外的访问功能：如：连接池技术
- 数据库驱动 由数据库厂商提供，需要去单独下载，是对JDBC的接口的实现
JDBC核心API：
DriverManager ：
- 管理和注册数据库驱动
- 获取数据库的连接对象
Connection：
- 一个连接对象，可以获取Statement对象和PreparedStatement
Statement：
- 一个SQL语句的对象，用户将sql语句发送到数据库服务器
PreparedStatement：
- 一个SQL语句对象 是Statement子接口
ResultSet：
- 封装了数据库查询的结果集，返回给客户端Java程序
DriverManager：
Driver接口
public interface Driver
每个驱动程序类必须实现的接口。这个接口是供数据库厂商来使用的。在开发中，我们不直接访问Driver接口，而是使用他的DriverManager实现类实现对驱动的管理
不同数据库的驱动类：
- Oracle： oracle.jdbc.driver.OracleDriver
- Mysql:com.mysql.jdbc.Driver
驱动包的获取：
Oracle：
Mysql：
DriverManager：
是Driver接口的实现类，用于管理一组JDBC驱动程序的基本服务
作用：
- 管理和注册驱动
- 创建数据库连接
方法：
数据库连接的4个参数： - url：数据库连接字符串，不同数据库的写法不同：
- oracle：jdbc:oracle:thin:@数据库服务器的ip：端口：数据库实例名（sid）
- mysql: jdbc:mysql:// 数据库服务器的ip：端口/数据库名称[?参数列表]
- user：连接数据库的用户名
- password：连接数据库的密码
- driverClass 数据库的驱动类：
- Oracle： oracle.jdbc.driver.OracleDriver
- Mysql:com.mysql.jdbc.Driver
数据库连接的URL地址的格式：
JDBC urL 用于标识一个被注册的驱动程序，驱动管理器通过url选择正确的驱动程序，从而建立数据库的连接。
URL标准由三部分组成：
- oracle：jdbc:oracle:thin:@数据库服务器的ip：端口：数据库实例名（sid）
- mysql: jdbc:mysql:// 数据库服务器的ip：端口/数据库名称[?参数列表]
- jdbc：连接数据库的协议: 子协议
- 子名称：标识数据库的方法，子名称可以根据不同的子协议变化，目的就是为了定位到需要连接的数据库，为连接提供足够的信息。包括主机名、端口号、数据库标识
获取数据库的连接：
- 导入需要连接的数据库驱动包：
代码：
@Test
//获取数据库的连接
public void getConnection() throws ClassNotFoundException, SQLException {
// 使用反射 加载和注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取连接
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.25.129:1521:orcl","scott","tiger");
System.out.println(conn);
}
@Test
//获取数据库的连接
public void getConnection() throws ClassNotFoundException, SQLException {
// 使用反射 加载和注册驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 定义连接属性
String url = "jdbc:oracle:thin:@192.168.25.129:1521:orcl";
String user = "scott";
String password="tiger";
//获取连接
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
从jdbc3规范开始，很多数据库都已经实现了可以根据url来自动注册和加载驱动
Class.forName(“oracle.jdbc.driver.OracleDriver”);可以省略
@Test
// 简单写法
public void getConnectionSimple() throws SQLException {
// 定义连接属性
String url = "jdbc:oracle:thin:@192.168.25.129:1521:orcl";
String user = "scott";
String password="tiger";
//获取连接 可以根据url来自动完成驱动的加载和注册
Connection conn = DriverManager.getConnection(url,user,password);
System.out.println(conn);
}
上述的代码采用了硬编码的方式，将url、user、password都写在代码中
使用属性文件来配置连接属性：
@Test
public void getConnectionByProp() throws IOException, SQLException {
//加载配置文件
Properties prop = new Properties();
//加载文件
InputStream in= JDBCDemo.class.getClassLoader().getResourceAsStream("resources/jdbc.properties");
prop.load(in);
//读取配置信息
String url = prop.getProperty("oracle.url");
String user = prop.getProperty("oracle.user");
String password = prop.getProperty("oracle.password");
// 省略加载和注册驱动
// 获取连接
Connection con = DriverManager.getConnection(url, user, password);
System.out.println(con);
}
配置文件：
#oracle
oracle.url=jdbc:oracle:thin:@192.168.25.129:1521:orcl
oracle.user=scott
oracle.password=tiger
oracle.driverClassName=oralce.jdbc.driver.OracleDriver
#mysql
mysql.url = jdbc:mysql://localhost:3306/test
mysql.user = root
mysql.password=root
mysql.driverClassName=com.mysql.jdbc.Driver
好处：
- 实现了代码和数据的分离，如果需要修改配置信息，直接修改配置文件即可
- 如果修改了配置信息，此时我们将不再需要重新编译代码
Connection：
Connection接口是由数据库厂商实现，代表一个连接对象
与特定数据库的连接（会话）。 执行SQL语句并在连接的上下文中返回结果。
Statement：
用于执行静态SQL语句并返回其生成的结果的对象。
JDBC访问数据库的步骤：
- 注册和加载驱动（可以省略）
- 获取连接
- Connection获取Statement对象
- 使用Statement对象执行SQL语句
- 返回ResultSet 结果集
- 释放资源
Statement：
作用：就是一条sql‘语句对象，用于发送sql语句到服务器，执行静态sql 并返回其执行后的结果对象
执行DDL操作：
使用jdbc在oracle中创建一张学生表：
| 字段名 | 类型 | 是否为null |
|---|---|---|
| Id | Number（10） | × |
| Name | Varchar2（20） | √ |
| Gender | Number（1） | √ |
| Birthday | Date | √ |
@Test
public void createTableStudent() throws SQLException {
//1创建连接
String url="jdbc:oracle:thin:@192.168.25.129:1521:orcl";
String user = "scott";
String password = "tiger";
Connection con = DriverManager.getConnection(url,user,password);
//2 通过Connection获取Statement
Statement stmt = con.createStatement();
//3 执行SQL
String sql = "create table student(id number(10),name varchar2(20),gender number(1),birthday date, constraint stu_id_pk PRIMARY KEY(ID))";
int i= stmt.executeUpdate(sql);
System.out.println(i);//0
//4 处理结果
System.out.println("创建表成功");
// 5 释放资源
stmt.close();
con.close();
}
对于jdbc操作中的异常的处理:
@Test
public void createTableStudent() {
//1创建连接
String url="jdbc:oracle:thin:@192.168.25.129:1521:orcl";
String user = "scott";
String password = "tiger";
Connection con =null;
Statement stmt =null;
try{
con = DriverManager.getConnection(url,user,password);
//2 通过Connection获取Statement
stmt = con.createStatement();
//3 执行SQL
String sql = "create table student(id number(10),name varchar2(20),gender number(1),birthday date, constraint stu_id_pk PRIMARY KEY(ID))";
int i= stmt.executeUpdate(sql);
System.out.println(i);//0
//4 处理结果
System.out.println("创建表成功");
}catch (SQLException e){
e.printStackTrace();
}finally {
// 5 释放资源
if(stmt !=null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(con !=null){
try {
con.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
执行DML操作:
给学生表插入5条记录:
//插入记录
@Test
public void insertTest() throws SQLException {
//1 注册和加载驱动 创建连接对象
String url="jdbc:oracle:thin:@192.168.25.129:1521:orcl";
String user = "scott";
String password = "tiger";
Connection con = DriverManager.getConnection(url,user,password);
// 2 获取Statement对象
Statement stmt =con.createStatement();
//3 执行sql
int count= 0;
count += stmt.executeUpdate("insert into student values(1,'孙悟空',1,to_date('1991-12-21','yyyy-mm-dd'))");
count += stmt.executeUpdate("insert into student values(2,'猪八戒',1,to_date('1992-12-21','yyyy-mm-dd'))");
count += stmt.executeUpdate("insert into student values(3,'沙僧',1,to_date('1993-12-21','yyyy-mm-dd'))");
count += stmt.executeUpdate("insert into student values(4,'唐僧',1,to_date('1990-12-21','yyyy-mm-dd'))");
count += stmt.executeUpdate("insert into student values(5,'白骨精',0,to_date('1999-12-21','yyyy-mm-dd'))");
//4 处理执行结果 受影响的行数
System.out.println("插入了" + count+"条记录");
// 5释放资源
stmt.close();
con.close();
}
执行DQL操作：
ResultSet executeQuery(String sql)throws SQLException执行给定的SQL语句，返回单个ResultSet对象。
ResultSet 表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。
next() 将光标从当前位置向前移动一行。
GetXXX()：
参数可以是列的索引（从1开始），也可以是列的名称
| SQL 类型 | Jdbc 对应方法 | 返回类型 |
|---|---|---|
| BIT(1) bit(n) | getBoolean() | boolean |
| TINYINT | getByte() | byte |
| SMALLINT | getShort() | short |
| INT | getInt() | int |
| BIGINT | getLong() | long |
| CHAR,VARCHAR | getString() | String |
| Text(Clob) Blob | getClob getBlob() | Clob Blob |
| DATE | getDate() | java.sql.Date 只代表日期 |
| TIME | getTime() | java.sql.Time 只表示时间 |
| TIMESTAMP | getTimestamp() | java.sql.Timestamp 同时有日期和时间 |
查询所有的学生信息:
@Test
public void selectData() throws SQLException {
//1 注册和加载驱动 创建连接对象
String url="jdbc:oracle:thin:@192.168.25.129:1521:orcl";
String user = "scott";
String password = "tiger";
Connection con = DriverManager.getConnection(url,user,password);
// 2 获取Statement对象
Statement stmt =con.createStatement();
// 3 执行sql
String sql = "select * from student";
ResultSet rs = stmt.executeQuery(sql);
// 4 处理结果集
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString(2);
int gender = rs.getInt("gender");
Date birthday = rs.getDate("birthday");
//输出结果
System.out.println(id +"--" + name+"--" +gender +"--"+birthday);
}
//释放资源
rs.close();
stmt.close();
con.close();
}
实现代码的重构 :
将前边的代码中的重复的代码提炼出来 ，封装成一个工具类
工具类中：
- 将连接时所使用的字符串定义为常量
- 获取连接 getConnection
- 释放资源
public class JDBCUtils {
//定义连接数据库的属性常量
private static final String USER = "scott";
private static final String PASSWORD = "tiger";
private static final String URL = "jdbc:oracle:thin:@192.168.25.129:1521:orcl";
private static final String DRIVER ="oracle.jdbc.driver.OracleDriver";
//完成驱动的注册和加载
static{
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection(){
Connection conn = null;
try {
conn = DriverManager.getConnection(URL,USER,PASSWORD);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return conn;
}
//释放资源
public static void close(ResultSet resultSet , Statement stmt , Connection conn){
if(resultSet !=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//释放资源
public static void close( Statement stmt , Connection conn){
if(stmt != null){
try {
stmt.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
案例：用户登录:
准备工作 :
一张用户表
给其中插入几条记录
create table users(
id number(10),
name varchar2(20),
password varchar2(50),
constraint user_id_pk PRIMARY KEY (id)
)
insert into users values(1,'jack','123');
insert into users values(2,'admin','888888');
--登录成功
select * from users where name='admin' and password='888888';
--登录失败
select * from users where name='admin' and password='88888';
代码实现:
public class LoginDemo {
public static void main(String[] args) throws SQLException {
//让用户从键盘输入用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名：");
String name = sc.nextLine();
System.out.println("请输入密码：");
String password = sc.nextLine();
//此时调用登录方法，完成用户名和密码的校验
login(name,password);
}
//实现登录校验
public static void login(String name,String password) throws SQLException {
//获取连接
Connection conn = JDBCUtils.getConnection();
//得到执行对象
Statement stmt = conn.createStatement();
// 执行sql
String sql = "select * from users where name='"+name +"' and password='"+ password+"'";
System.out.println(sql);
ResultSet rs = stmt.executeQuery(sql);
// 处理结果集
if(rs.next()){
System.out.println("登陆成功");
}else{
System.out.println("登录失败");
}
//释放资源
JDBCUtils.close(rs,stmt,conn);
}
}
SQL注入:
此时我们发现用户名和密码都不对，但是居然登录成功
select * from users where name=‘tom’ and password=‘a’ or ‘1’=‘1’
相当于 SELECT * FROM users where 1=1
原因在于 sql语句使用了字符串拼接的方式，
解决方案：将输入的用户名和密码，不要使用简单的字符串拼接即可。
PreparedStatement：
是statement对象的子接口
作用和statement相同，都是将sql语句发送给数据库服务器执行，并接收执行的结果。
和statement的区别：
Statement所执行的sql是静态sql，这种sql是使用字符串拼接方式拼接出来的。
PreparedStatement 在发送之前，会将sql语句进行预编译，将sql语句所需要的参数绑定给PreparedStatement的对象。不用拼接sql 此时就可减少sql注入的风险，同时，由于sql语句被预编译过，后边在执行的时候，只是为PreparedStatement对象绑定参数值，提高效率。
使用PreparedStatement对象的步骤：
- 获取连接
- 获取PreparedStatement对象
- 编写sql，？表示占位符
Select * from users where name=’admin’ and password=’1230’;(静态sql)
--PreparedStatement：
SELECT * FROM users where name=? and password=?;
- 绑定参数
- 执行sql
- 释放资源
PreparedStatement prepareStatement(String sql) 创建一个 PreparedStatement对象，用于将参数化的SQL语句发送到数据库
绑定参数的方法：
使用PreparedStatement改造登录案例：
public class LoginDemoByPreparedStatement {
public static void main(String[] args) throws SQLException {
//让用户从键盘输入用户名和密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名：");
String name = sc.nextLine();
System.out.println("请输入密码：");
String password = sc.nextLine();
//此时调用登录方法，完成用户名和密码的校验
login(name,password);
}
//实现登录校验
public static void login(String name,String password) throws SQLException {
//获取连接
Connection conn = JDBCUtils.getConnection();
// 执行sql
String sql = "select * from users where name= ? and password=?";
//得到执行对象PreparedStatement 预编译sql
PreparedStatement pstmt = conn.prepareStatement(sql);
System.out.println(sql);
//绑定参数
pstmt.setString(1,name);
pstmt.setString(2,password);
//执行sql
ResultSet rs = pstmt.executeQuery();
// 处理结果集
if(rs.next()){
System.out.println("登陆成功");
}else{
System.out.println("登录失败");
}
//释放资源
JDBCUtils.close(rs,pstmt,conn);
}
}
表和类的关系：
一张表的结构类似于类
表中的一条记录就类似于一个对象
使用PreparedStatement查询一条student记录 ，并将结果封装成一个student对象：
@Test
public void getStudentObj() throws SQLException {
// 获取连接
Connection conn = JDBCUtils.getConnection();
String sql = "select * from student where id=?";
//获取语句对象
PreparedStatement pstmt = conn.prepareStatement(sql);
// 绑定参数
pstmt.setInt(1,5);
//执行sql
ResultSet rs = pstmt.executeQuery();
// 处理结果
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
int gender = rs.getInt("gender");
Date birthday = rs.getDate("birthday");
// System.out.println(name+"--"+ birthday);
// 将一条记录封装成一个对象
Student stu = new Student(id,name,gender,birthday);
System.out.println(stu);
}
JDBCUtils.close(rs,pstmt,conn);
}
查询所有的学生对象，将其封装成一个集合：
@Test
public void getStudentObj() throws SQLException {
// 获取连接
Connection conn = JDBCUtils.getConnection();
String sql = "select * from student ";
//获取语句对象
PreparedStatement pstmt = conn.prepareStatement(sql);
// 绑定参数
// pstmt.setInt(1,5);
//执行sql
ResultSet rs = pstmt.executeQuery();
// 声明一个集合
List<Student> stuList = new ArrayList<>();
// 处理结果
while(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
int gender = rs.getInt("gender");
Date birthday = rs.getDate("birthday");
// System.out.println(name+"--"+ birthday);
// 将一条记录封装成一个对象
Student stu = new Student(id,name,gender,birthday);
stuList.add(stu);
}
//遍历集合
for(Student stu : stuList){
System.out.println(stu);
}
JDBCUtils.close(rs,pstmt,conn);
}
使用PreparedStatement完成DML操作：
插入数据实现主键自增
@Test
public void insertTest() throws SQLException {
Connection conn = JDBCUtils.getConnection();
//查询序列 获取自定递增的id下一个值
PreparedStatement ps1 = conn.prepareStatement("select stu_seq.nextval from dual");
ResultSet rs1 = ps1.executeQuery();
int id= 0;
while(rs1.next()){
id = rs1.getInt(1);
}
// 创建数据插入
PreparedStatement ps2 = conn.prepareStatement("insert into student values (?,?,?,?)");
ps2.setInt(1,id);
ps2.setString(2,"白龙马");
ps2.setInt(3,1);
ps2.setDate(4,java.sql.Date.valueOf("1999-11-11"));
//执行sql
int row = ps2.executeUpdate();
System.out.println("插入了"+ row +"行记录");
JDBCUtils.close(rs1,ps2,conn);
}
@Test
public void update() throws SQLException {
Connection conn = JDBCUtils.getConnection();
String sql = "update student set name=? ,birthday=? where id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1,"斗战胜佛");
pstmt.setDate(2,java.sql.Date.valueOf("2021-2-4"));
pstmt.setInt(3,1);
int row = pstmt.executeUpdate();
System.out.println(row);
JDBCUtils.close(pstmt,conn);
}
@Test
public void delete() throws SQLException {
Connection conn = JDBCUtils.getConnection();
String sql = "delete from student where id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,5);
int row = pstmt.executeUpdate();
System.out.println(row);
JDBCUtils.close(pstmt,conn);
}
556
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
