第九部分 数据库管理系统及JDBC编程

数据库管理系统及JDBC编程"/>

第九部分 数据库管理系统及JDBC编程

第九部分 数据库管理系统

9.1 数据库管理系统概述（MySQL）

9.1.1 数据库概念

数据库的定义：长期存储在计算机内，有组织的，可共享的大量数据的集合。

数据库管理系统：能够提供数据定义、数据组织、存储、管理、操纵、数据库维护等功能的软件

数据库的三级两映射与数据库的独立性原理：

9.1.2 关系数据库语言SQL

1、库相关

创建库：CREATE DATABASE NAME;

删除库：DROP DAABASE NAME;

2、表相关

创建表：CREATE TABLE TABLENAME(

<列名>, 数据类型 [PRIMARY KEY/UNIQUE/.....]

......

);

修改表：ALTER ATBLE TABLENAME

ADD NEWCOLNAME,DATATYPE [PRIMARY KEY/UNIQUE/......];

或drop colname;

或alter colomn colname datatype;

删除表：drop table tablename;

3、索引相关

创建索引： create index indexname on colname[asc/desc];

修改索引： alter index oldname rename to newname;

删除索引： drop index indexname;

4、单表查询相关

select a[,b,...] [as name] fron tablename [限制条件];

限制条件分为如下几类：

where 为真条件 （如果要模糊匹配 alike b%）

in(A,B,...)

between a and b

group by [......] having 为真条件

关键字：distinct（去重）/asc（升序）/desc（降序）

聚合函数：count/sum/avg/max/min等

5、多表查询相关

表名.表内列名

6、连接相关

（left/right）join

7、子查询相关

嵌入在其他SQL语句的select语句

8、语句分类：

DDL：create、drop、alter等

DML：insert、delete、update等

DQL：select等

DCL：grant、revoke、commit等

9、数据操作相关

插入数据：insert into tablename values(a,b,....);

子查询插入数据：insert into tablename (子查询语句)；

修改数据： update tablename set tablename = value,.... where 成真条件；

删除数据： delete from tablename where 成真条件

10、空值处理

空值（null）一旦产生，无法参与运算，例如null与10相加的结果是null+10

空值不能直接赋值，向age=null这样的语句是错误的

9.1.3 数据库安全

1、视图

定义：视图是一个虚表，只保留了视图的定义，不存放对应的数据，是由一个或几个基本表/视图到处的表

语句：create view viewname as 子查询语句;

drop view viewname

视图名可以出现在任何增删改查的SQL语句中充当子查询语句

作用：简化用户操作/提供一定程度的逻辑独立性/提供安全保护/灵活的看待同一数据/适当使用可以更清晰的表达查询

2、视图与表的区别

a.视图是已经编译好的SQL语句，表不是

b.视图没有实际的物理记录，表拥有

c.表示内容，视图是窗口

d.表占用物理空间，视图不占用物理空间

e.表示内模式，视图是外模式

3、事务

事务是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做

事务的acid特性

a.原子性：事务中包含的操作要么全都做，要么都不做

c.一致性C:事务的执行结果必定是从一个一致性状态到另一个一致性状态

i.隔离性：一个事务的执行不能被其他事务所感染

d.持续性：事务一旦提交，数据的改变就是永久性的

9.2 数据库与JDBC编程

9.2.1 JDBC是什么

JDBC（Java DataBase Connectivity，Java数据库连接）技术的简称，是一种用于执行SQL语句的Java API，它由一组用Java编程语言编写的类和接口组成。这个API由java.sql.*/javax.sql.*包中的一些类和接口组成。

9.2.2 JDBC工作原理

JDBC为多种关系数据提供了统一的访问方式，作为特定厂商数据库访问API的一种高级抽象，他主要包含一些通用的接口类。真正的数据库访问操作实现是有各自数据库厂商提供的。通常把厂商提供的特定于数据库的访问API成为数据库JDBC驱动程序。

JDBC的优势：

1、Java语言访问数据库操作完全面向抽象接口编程

2、开发数据库应用不用限定在特定数据库厂商的API

3、程序的可移植性大大增强

9.2.3 JDBC的使用步骤

 

import java.sql.*;public class JDBCtest {public static void main(String[] args) {try {Class.forName("com.mysql.jdbc.Driver");//标准加载驱动，需要手动导入jar包mysql-connector-java//在SQL驱动5.1后可以不需要主动加载，但还是要求驱动在class-path目录下} catch (ClassNotFoundException e) {e.printStackTrace();}//连接数据库，注意url格式//jdbc:databaseName://host:port/databaseName?p1=v1&p2=v2;//jdbc:mysql://localhost:3306/experment?try {Connection connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/experment?","root","Dived2014");//创建命令Statement statement =  connection.createStatement();//准备SQL语句，并且执行ResultSet resultSet = statement.executeQuery("select Student_ID,Student_Name,SChinese from student");//返回结果集，处理结果while (resultSet.next()){int id = resultSet.getInt("Student_ID");String name = resultSet.getString("Student_Name");//LocateDateTime time = resultSet.getTimestamp("colname").toLocalDateTime();System.out.println(String.format("ID:%d,Name:%s",id,name));}//关闭结果集resultSet.close();//关闭命令statement.close();//关闭连接connection.close();} catch (SQLException e) {e.printStackTrace();}}
}

 

import java.sql.*;
public class JDBCtest {public static void main(String[] args) {try {Class.forName("com.mysql.jdbc.Driver");//标准加载驱动，需要手动导入jar包mysql-connector-java//在SQL驱动5.1后可以不需要主动加载，但还是要求驱动在class-path目录下} catch (ClassNotFoundException e) {e.printStackTrace();}//连接数据库，注意url格式//jdbc:databaseName://host:port/databaseName?p1=v1&p2=v2;//jdbc:mysql://localhost:3306/experment?try {Connection connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/experment?useSSL=false","root","Dived2014");//创建命令Statement statement =  connection.createStatement();//准备SQL语句，并且执行int effect = statement.executeUpdate("delete from student where Student_ID = 6");System.out.println(effect);//关闭命令statement.close();//关闭连接connection.close();} catch (SQLException e) {e.printStackTrace();}}
}

import java.sql.*;public class JDBCtest {public static void main(String[] args) {try {Class.forName("com.mysql.jdbc.Driver");//标准加载驱动，需要手动导入jar包mysql-connector-java//在SQL驱动5.1后可以不需要主动加载，但还是要求驱动在class-path目录下} catch (ClassNotFoundException e) {e.printStackTrace();}//连接数据库，注意url格式//jdbc:databaseName://host:port/databaseName?p1=v1&p2=v2;//jdbc:mysql://localhost:3306/experment?try {Connection connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/experment?useSSL=false","root","Dived2014");//创建命令Statement statement =  connection.createStatement();//准备SQL语句，并且执行String sql = "insert into student values(6,'如来佛祖',55,85,45)";int effect = statement.executeUpdate(sql);System.out.println(effect);//关闭命令statement.close();//关闭连接connection.close();} catch (SQLException e) {e.printStackTrace();}}
}

通过模板设计模式可以简化上面的操作

package com.Dived2014.JDBC;/**国伟*2019/3/12*13:43*****/import java.sql.*;public abstract class JdbcTemplate {private Connection connection;private Statement statement;private ResultSet resultSet;private int effect = -1;private String url;public JdbcTemplate(String host,Integer port,String username,String password,String databaseName){this.url = String.format("jdbc:mysql://%s:%d/%s?user=%s&password=%s&useSSL=false",host,port,databaseName,username,password);}public final void call(){//加载驱动loadDriver();//创建连接createConnect();//创建命令createStatment();//准备SQL语句createSql();//执行execute();//处理结果//对于插入/删除/更新等返回数据变动数量//对于查询返回结果集handlerResult();//关闭结果集等closeAll();}private void closeAll() {if(this.resultSet!=null){try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if(this.statement!=null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if(this.connection!= null){try {connection.close();} catch (SQLException e) {e.printStackTrace();}}effect = -1;}private void handlerResult() {if(this.executeType()){this.handlerR(resultSet);}else{this.handlerCUD(effect);}}public abstract void handlerCUD(int effect);public abstract void handlerR(ResultSet resultSet);private void execute() {String sql = this.createSql();if(sql != null){if(this.executeType()){try {resultSet = statement.executeQuery(sql);} catch (SQLException e) {e.printStackTrace();}}else{try {effect = statement.executeUpdate(sql);} catch (SQLException e) {e.printStackTrace();}}}}//用户覆写这个方法public abstract String createSql();/*** @return true:查询语句 false:其他语句*/public abstract boolean executeType();private void createStatment() {try {statement =  connection.createStatement();} catch (SQLException e) {e.printStackTrace();}}private void createConnect() {try {connection =  DriverManager.getConnection(this.url);} catch (SQLException e) {e.printStackTrace();}}private void loadDriver() {try {Class.forName("com.mysql.jdbc.Driver");//标准加载驱动，需要手动导入jar包mysql-connector-java//在SQL驱动5.1后可以不需要主动加载，但还是要求驱动在class-path目录下} catch (ClassNotFoundException e) {e.printStackTrace();}}
}import java.sql.ResultSet;
import java.sql.SQLException;public class JDBCtest {public static void main(String[] args) {JdbcTemplate jdbcTemplate = new JdbcTemplate("localhost",3306,"root","Dived2014","experment") {@Overridepublic void handlerCUD(int effect) {//do nothing}@Overridepublic void handlerR(ResultSet resultSet) {try{while (resultSet.next()){int id = resultSet.getInt("Student_ID");String name = resultSet.getString("Student_Name");//LocateDateTime time = resultSet.getTimestamp("colname").toLocalDateTime();System.out.println(String.format("ID:%d,Name:%s",id,name));}}catch (SQLException e){}}@Overridepublic String createSql() {return "select Student_ID,Student_Name from student";}@Overridepublic boolean executeType() {return true;}};jdbcTemplate.call();}
}

然而上面的代码仍然存在只能使用一次的缺陷，可以结合工厂设计模式来进行优化。

9.2.4 SQL注入

SQL语句的拼接导致SQL注入，会引发安全性问题。

可以使用PreparedStatement这个接口可以防止SQL注入，属于一种预编译命令。具体实现方法是在SQL语句中用？占位，之后使用参数赋值。

import java.sql.*;public class JDBCtest {public static void main(String[] args) {try {Class.forName("com.mysql.jdbc.Driver");//标准加载驱动，需要手动导入jar包mysql-connector-java//在SQL驱动5.1后可以不需要主动加载，但还是要求驱动在class-path目录下} catch (ClassNotFoundException e) {e.printStackTrace();}//连接数据库，注意url格式//jdbc:databaseName://host:port/databaseName?p1=v1&p2=v2;//jdbc:mysql://localhost:3306/experment?try {Connection connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/experment?","root","Dived2014");//创建命令String sql = "select Student_ID,Student_Name,SChinese from student where name=?";//?为占位符，他不能表示多个参数//建议对参数进行校验防止参数的字符串拼接PreparedStatement statement =  connection.prepareStatement(sql);statement.setString(1,"唐僧");//set的下标是从1开始的//准备SQL语句，并且执行ResultSet resultSet = statement.executeQuery();//返回结果集，处理结果while (resultSet.next()){int id = resultSet.getInt("Student_ID");String name = resultSet.getString("Student_Name");//LocateDateTime time = resultSet.getTimestamp("colname").toLocalDateTime();System.out.println(String.format("ID:%d,Name:%s",id,name));}//关闭结果集resultSet.close();//关闭命令statement.close();//关闭连接connection.close();} catch (SQLException e) {e.printStackTrace();}}
}

Statement/PreparedStatement/CallableStatement的区别

Statement：用于执行不带参数的简单SQL语句

PreparedStatement：用于执行带或者不带参数的SQL语句

SQL语句会预编译在数据库系统

执行速度快鱼Statement对象

可以防止常见的SQL注入攻击

CallableStatement：用于执行数据库存储过程的调用

9.2.5 JDBC事务

默认情况下，每执行一条语句就会自动向数据库系统提交语句，如果想要实现事务特性，可以connection.setAutoCommit(false);禁用（需要在创建连接后立刻设置），之后connectionmit()方法来手动提交，一旦提交失败（需要通过自己编程判断）则可以使用connection.rollback()来进行回滚。但是注意：一定要在捕获异常的时候也要rollback回滚。