自动化测开（java）JDBC

•如何让自己的数据长期保存？使用文件保存数据？
–没有文件以前，数据的存储方式以内存为主，无法长期保存
–文件系统产生，数据可以长期保存在文件中，常见的文件类型：
•纯文本：没有文件的格式要求
•数据传输格式：html, xml, json，规定一定的规范的纯文本文件
•指定公司特定格式：excel, csv等等
•专门做数据存储的公司：mysql，sqlsever，oracle，DB2等

•当我们提到“数据库”的时候有以下几种含义：
–数据库服务器：硬件
–数据库服务：软件
–数据库客户端：软件
•命令行：sql语句
•客户端软件：不是唯一的
•自开发的软件：我们自己写的软件
–数据库的名字：一般代表某一个应用程序

•企业应用通常都会使用数据库来保存和管理数据信息
•数据库的主要职能
–保存大量数据
–检索数据
–数据维护(备份、压缩等)
•根据实际项目的规模与需求
–选择合适的数据存储技术
–选择合适的数据库产品

SQL语句介绍

•SQL是Structured Query Language(结构化查询语言)的缩写，SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言
•SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础，并且现在几乎所有的数据库均支持SQL

SQL对数据库的操作

数据定义：SQL DDL数据库、基本表、视图和索引

数据操纵：SQL DML，数据查询和数据更新， 数据更新又包括插入、删除和更新

数据控制：DCL  视图的授权、完整性规则的描述，事务控制语句

其它要素： 规定SQL语句在宿主语言的程序中使用的规则

•数据库的建立与删除
–CREATE DATABASE <数据库名> [其它参数]
–DROP DATABASE <数据库名>

•基本表的定义及修改、删除
–CREATE TABLE[<数据库名>.]<表名>
( <列名> 数据类型 [缺省值] [NOT NULL|NULL]
[,<列名> 数据类型 [缺省值] [NOT NULL|NULL]]......
[,UNIQUE (列名[,列名]......)]
[,PRIMARY KEY(列名)]
[,FOREIGN KEY(列名[,列名]......)REFERENCE <表名>(列名[,列名
]......)]
[,CHECK(条件)] [其它参数] )
–ALTER TABLE [<数据库名>.]表名 add|drop ……
–DROP TABLE[<数据库名>.]表名

•视图的建立与删除
–CREATE  VIEW  视图名 AS SELECT语句
–DROP  VIEW  视图名

•索引的定义与删除
–CREATE ［UNIQUE］ INDEX  索引名  ON
［<数据库名>.］表名
  (列名 ［ASC/DESC］
［，列名［ASC/DESC］］......)
•SHOW INDEX FROM 表名
•DROP  INDEX  索引名 ON 表名

数据查询：查
–无条件查询 select * from <表名>
–条件查询(where查询)
–排序查询(order by field1 ASC|DESC)
–※联合查询
select employees.name from employees,jobs 
where employees.job_id=job.id and job.name=‘XX’
–计算查询(统计查询，SUM,MAX……)

数据更新：增、删、改
•INSERT INTO表名(列名1［，列名2］…) 
values(列值1［，列值2］…)
•DELETE  FROM表名［WHERE  条件表达式］
•UPDATE 表名 SET  列名=列改变值［WHERE  条件表达式］

–选择：select * from table1 where 范围
–插入：insert into table1(field1,field2) values(value1,value2)
–删除：delete from table1 where 范围
–更新：update table1 set field1=value1 where 范围
–查找：select * from table1 where field1 like ‘%value1%’
–排序：select * from table1 order by field1,field2 [desc]

–总数：select count * as totalcount from table1
–求和：select sum(field1) as sumvalue from table1
–平均：select avg(field1) as avgvalue from table1
–最大：select max(field1) as maxvalue from table1
–最小：select min(field1) as minvalue from table1

数据控制

•安全性控制
–授权:GRANT; 回收:REVOKE…
•完整性控制
–数据库的完整性是指数据的正确性和相容性，主要目的是防止语义上不正确的数据进入数据库。Alert Table 表名 add constraint 键名 foreign key (字段) references 表名(字段)
•事务控制
–事务提交:COMMIT; 事务撤消:ROLLBACK…
•并发控制
–数据库作为共享资源，允许多个用户程序并行地存取数据。当多个用户并行地操作数据库时，需要通过并发控制对它们加以协调、控制，以保证并发操作的正确执行，并保证数据库的一致性，LOCK TABLE 表名(或表名集合)IN 

 

JDBC的概念和类型

•JDBC是Java Database Connectivity的简称
•是由一组Java语言编写的类和接口组成，是一种用于执行SQL语句的规范
•官方网址：
http://www.oracle.com/technetwork/java/javase/tech/index-jsp-136101.html

•JDBC API 提供两类主要接口：
–一是面向开发人员的java.sql程序包，使得Java程序员能够进行数据库连接，执行SQL查询，并得到结果集合。
–一是面向底层数据库厂商的JDBC Drivers。

•JDBC是由一组Java语言编写的类和接口组成，是一种用于执行
SQL语句的规范

创建JDBC应用的步骤

•1.加载数据库的驱动程序（首先引入数据库驱动的jar包）
–Class.forName(“driverName”);
•2.建立数据库连接
–String connStr = “jdbc:<JDBC驱动程序名：端口号>:数据源”
–Connection conn=DriverManager.getConnection（connStr）;
•3.执行数据库操作SQL
–Statement stmt = conn.createStatement();
–ResultSet rs = stmt.executeQuery(sql);
•4.得到ResultSet进行结果处理
•5.关闭数据库连接
–conn.close();

2.加载数据库的驱动程序
•构造数据库的驱动字符
–MySQL驱动字符
•com.mysql.jdbc.Driver（mysql 5）
•com.mysql.cj.jdbc.Driver(mysql 8)
•加载MySQL驱动
–Class.forName(“com.mysql.jdbc.Driver”);
–Class.forName(“com.mysql.cj.jdbc.Driver”);

Driver

•Driver接口：每个JDBC数据库驱动程序都会提供Driver接口的具体实现
•如果想连接数据库，必须先加载数据库厂商提供的数据库驱动程序
•不同类型的JDBC数据库驱动程序在编程时的加载方法也不同
•JDBC驱动程序加载方法：
–Class.forName(“jdbcdriver_classname”).newInstance();
–对于MySql数据
•jdbcdriver_classname=com.mysq.jdbc.Driver。

3.建立数据库连接

•连接数据库的连接字符串
–MySQL连接字符串  jdbc:mysql://localhost:3366/databaseName
•jdbc:mysql //表示协议
•Localhost //表示IP
•3366 表示端口号
•databaseName 表示数据库名称
•建立连接MySQL的代码
Connection connectMySQL = 
DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbcstudy","root" ,"root" );

•JDBC URL 定义驱动程序与数据源之间的连接
–jdbc:mysql://[hostname][:port]/[dbname][?param1=value1][&param2=value2]….
–jdbc:mysql://localhost:3306/sample_db?user=root&password=your_password

DriverManager

DriverManager是 JDBC 的管理层，管理一组JDBC驱动程序的基本服务。
•DriverManager类的主要作用是跟踪可用的驱动程序，并在数据库和相应驱动程序之间建立连接。
•调用DriverManager.getConnection()方法将建立与数据库的连接，得到与数据库连接Connection对象。

Connection

•Connection类是JDBC规范中的最核心的类。
–Statement 对象和 ResultSet 对象等都直接或者间接的来源于它
•Connection对象表示与特定数据库的连接(会话)。
–得到 Statement 对象
• createStatement()
• prepareStatement(String sql)
• prepareCall(String sql)

//为了保证事务的原子性，Connection设置手动提交事务
conn.setAutoCommit(false);//关闭事务的自动提交
try {
        Statement stmt = conn.createStatement();
        stmt.execute (sql);
        conn.commit();//提交事务
}catch (SQLException e1) {
      conn.rollback();//事务回滚
}

4.执行数据库操作SQL

•Java.sql.Statement常用方法：
–ResultSet executeQuery(String sql)：执行给定的 SQL 语句，该语句返回单个 ResultSet 对象
–int executeUpdate(String sql)：执行给定 SQL 语句，可能为 INSERT、UPDATE 或 DELETE 句
–boolean isClosed(): 获取是否已关闭了此 Statement 对象
–ResultSet getResultSet(): 以 ResultSet 对象的形式获取当前结果 
–int getUpdateCount()：以更新计数的形式获取当前结果；如果结果为 ResultSet 对象或没有更结果，则返回 -1

Statement

•Statement是向数据库提交SQL语句并返回相应结果的工具。
语句可以是SQL查询、修改、插入或者删除
–PreparedStatement接口
•防止SQL注入攻击(使用占位符“?”)
•提高SQL的执行性能(在执行之前有预处理)
•避免使用SQL方言
•提高JDBC中有关SQL代码的可读性
–CallableStatement接口用于执行 SQL 存储过程的接口

常用方法：

execute：执行给定 SQL 语句,该语句可能返回多条结果
•executeQuery：执行给定的 SQL 语句，该语句返回单个 ResultSet 对象
•executeUpdate执行，该语句可能为 INSERT、UPDATE 或 DELET给定 
SQL 语句
•getConnection：获取生成此 Statement 对象的Connection 对象

•close：立即释放此 Statement 对象的数据库和 JDBC 资源，而不是等待该对象自动关闭时发此操作
•getFetchSize：获取结果集合的行数
•getQueryTimeout：获取驱动程序等待 Statement 对象执行的秒数

PreparedStatement

•PreparedStatement 类：是Statement的子接口
•用 PreparedStatement 类效率会更高。使用PreparedStatement 有很多优势，总结如下：
–防止SQL注入攻击(使用占位符“?”)。
–提高SQL的执行性能(在执行之前有预处理)。
–避免使用SQL方言提高JDBC中有关SQL代码的可读性。
•PreparedStatement实例要通过Connection对象调用prepareStatement(String sql)方法获得。

•PreparedStateme中常用的一些方法：
–public ResultSetMetaData getMetaData()
–public void setInt(int parameterIndex, int x)
–public void setFloat(int parameterIndex, float x)
–public void setString(int parameterIndex, String x)
–public void setDate(int parameterIndex, Date x)

CallableStatement

•CallableStatement用于执行 SQL 存储过程的接口
•CallableStatement继承了PreparedStatement接口，所以也继承了PreparedStatement的方法

java项目中已经很少应用存储过程了

ResultSet

•ResultSet 表示数据库结果集的数据表，通常通过执行查询数据库的语句生成
•ResultSet 对象具有指向其当前数据行的光标
•ResultSet 类常用方法：
–String getString(int columnIndex)：以 Java 编程语言中 String 的形式获取此 ResultSet 对象的当前行中指定列的值

while (rs.next()) {
     System.out.println(rs.getString("name"));
}

常用方法：

–boolean next()：将光标从当前位置向前移一行。当调用 next 方法返回 false 时，光标位于最一行的后面
–int getInt(String columnIndex)：以 Java 编程语言中 int 的形式获取此 ResultSet 对象的当行中指定列的值。
–next：将光标从当前位置向前移一行
–getXXX：以某种对象的形式获取指定 JDBC 该类型参数的值
–absolute：将光标移动到此 ResultSet 对象的给定行编号
–afterLast：将光标移动到此 ResultSet 对象的末尾，正好位于最后一行之后
–beforeFirst：将光标移动到此 ResultSet 对象的开头，正好位于第一行之前

–close：立即释放此 ResultSet 对象的数据库和 JDBC 资源，而不是等待该对象自动关闭时发此操作
–first：将光标移动到此 ResultSet 对象的第一行
–getRow：获取当前行编号
–isFirst：获取光标是否位于此 ResultSet 对象的第一行
–isLast：获取光标是否位于此 ResultSet 对象的最后一行
–last：将光标移动到此 ResultSet 对象的最后一行
–previous：将光标移动到此 ResultSet 对象的上一行

数据类型：

•关系型数据库的字段有自己的类型
•JDBC中有Java数据类型
•对应关系：
–char-----java.lang.String
–varchar------ java.lang.String
–integer ----- java.lang.Integer
–date------java.sql.Date,java.util.Date
–……
•特殊类型：日期时间、布尔型……

关闭数据库连接

•数据库使用完毕后要进行手动关闭，否则造成系统资源浪
费。
–检测ResultSet是否关闭，否则关闭
–检测Statement是否关闭，否则关闭
–检测Connection是否关闭，否则关闭
 

if(rs!=null)try{rs.close();}catch(Exception e){e.printStackTrace();}
if(stmt!=null)try{stmt.close();}catch(Exception e){e.printStackTrace();}
if(conn!=null)try{conn.close();}catch(Exception e){e.printStackTrace();}

用JDBC进行数据库连接

//从数据库中查询出员工的信息，并打印到控制台上。
Class.forName("com.mysql.jdbc.Driver");
String url = 
"jdbc:mysql://127.0.0.1:3306/em?useUnicode=true&characterEncoding=UTF-8";
Connection conn = DriverManager.getConnection(url, "root", "");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from employees");
while (rs.next()) {
    System.out.println(rs.getString(“name”));
}
conn.close();

数据库优化

•现阶段的数据库连接代码存在的问题：
–1、现阶段代码一次连接一次查询，一般应用程序一次连接后可多次执行数据操作
–2、数据库的常量信息，每次都要进行代码修改？是否可以提取出相应的工程配置文件？
–3、对于现在的程序来说都是客户端直接访问数据库操作？这样不利于界面和数据访问的分离，何添加业务逻辑？

对数据库操作的封装

•将数据库的连接和断开操作封装到工具类，可以反复使用

public class ConnectionManager{
    public static Connection getConnection(){
      Connection connection = null;
      Class.forName("com.mysql.jdbc.Driver");
      String url = "jdbc:mysql:///jdbcstudy？useUnicode=true&characterEncoding=UTF-8";
      conn  = DriverManager.getConnection(url, "admin", "");      
      return  connection;
  }
}

MVC简介

•MVC是模型(model)－视图(view)－控制器(controller)的缩写。
•一般的合理的工程目录中要将此三部分进行分离。
–视图层：是应用程序中用户界面相关的部分，是用户看到并与之交互的界面。
–控制层：是根据用户的输入，控制用户界面数据显示和更新model对象状态。
–模型层：通常模型对象负责在数据库中存取数据。

数据库操作的完整封装（DAO）

•一般情况下Java中的实体类对应数据库中的表。
–例如：数据库的User表，对应Java程序中的User类，字段也相互对应
•所以我们对于一张表的所有操作，在Java程序中体现到了一个实体类的所有的操作。
•一般标准工程中一个实体类会有一个对应的DAO类（Data Access Object），来进行这个类的所有的操作。

public class UserDao {
ConnectionManager cm = new ConnectionManager();
public List<User> selectAll(){
    List<User> list = new ArrayList<User>();
    Connection conn = cm.getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("select * from users");
    while (rs.next()) {
         User u = new User();
        //设置user的值
            list.add(u);
}
//....
return list;
}
}

Java程序分包

•全包名:一般格式公司的网址倒叙：com.baidu
–com.baidu.ui：界面包
–com.baidu.controller：程序的控制器包
–com.baidu.business（com.baidu.service）：程序的业务逻辑包
–com.baidu.dao：所有实体类的数据访问包
–com.baidu.entity（ com.baidu.model）：实体类所在包
–com.baidu.utils：工具类

数据库连接池的提出

•通过JDBC访问数据库，每次访问都要经历与数据库建立连接，打开连接，访问数据库，关闭连接几个步骤，与数据库建立并打开连接是一件既费力又费时的工作，频繁的发生这种操作会严重耗费系统资源，导致系统性能下降，严重的会导致系统崩溃
•数据库连接池就是为了解决上面的问题而出现的。数据库连接池负责动态的分配、管理和释放据库连接

关于DataSource

•DataSource属于javax.sql包，是对java.sql包的扩展部分。DataSource用于提供到此 DataSource对象所表示的物理数据源的连接
•DataSource 接口由驱动程序供应商实现。共有三种类型的
实现：
–基本实现
–连接池实现
–分布式事务实现

DataSource常用方法

•getConnection：尝试建立与此 DataSource对象所表示的数据源的连接
•与DriverManager. getConnection()方法相比，
DataSource有以下特点：
–中间层
–池化
–完全实现数据库和应用程序的分离

开源的数据库连接池产品

Apache DHCP
•DHCP是一个依赖Jakarta commons-pool对象池机制的数据库连接池
.DBCP可以直接的在应用程序用使用
•C3P0
•C3P0是一个开放源代码的JDBC连接池，它在lib目录中与Hibernate一起
发布,包括了实现jdbc3和jdbc2扩展规范说明的Connection 和Statement 
池的DataSources 对象。
•Proxool
•快速，成熟，健壮。可以透明地为你现存的JDBC驱动程序增加连接池功能

作业：

•使用em数据库，编写SQL语句实现功能
–统计职务为“经理”的员工数
–列出所有基本工资大于12000的员工信息
–将所有工资小于12000的员工工资加1000
–统计职务为“技术部”且工资大于2000的员工数

实现查询的程序代码
•理解JDBC以及各接口
•使用Java程序实现：
–统计职务为“经理”的员工数
–列出所有基本工资大于1200的员工信息
–将所有工资小于1200的员工工资加100
–统计职务为“技术部”且工资大于2000的员工数

•作业：补全如下方法的实现
–public static void queryStudent()
–public static void insertStudent()
–public static void deleteStudent()
–public static void updateStudent()
•考虑以上四个方法的参数和返回值（面向对象）。
•Statement、PreparedStatement和CallableStatement的区别是什么？
•尝试修改课上例题，去连接你的sqlserver数据库获取数据内容。
•连接不同数据库的修改点，在Java中会使用配置文件来保存(Java property)。
–数据库驱动串
–数据库连接字符串
–用户名密码

•作业：
–public static List<Student> queryStudentByName() 
–public static List<Student> queryStudentById() 
–public static List<Student> queryStudentByScore() 
–public static int insertStudent(Student s)
–public static int deleteStudentByName()
–public static int deleteStudentById()
–public static int updateStudent(Student s)
–public static Connection getDBContection()
–public static int clostDBContection()
•通过Properties实现，数据库驱动串，数据库连接字符串，用户名，密码的程序隔离。做出在不修改程
序的情况下，连接不同的数据库。
•程序分包：
–com.wyl.model
–com.wyl.dao
–com.wyl.ui

•扩展如下方法：
–int insertBatchStudent(List<Student> ls)
•对比如下操作花费的时间：
–1、插入100条学生信息，每次插入信息都去连接关闭数据库
–2、插入100条学生信息，先连接，插入100条，关闭数据库
•尝试用DataSource或者C3P0搭建数据库连接池程序