[大数据]《疯狂java讲义》第13章 MySQL数据库与JDBC编程

第13章 MySQL数据库与JDBC编程

MySQL数据库和SQL语法

链接：数据库SQL和MySQL
之前学习数据库时B站看的，【Mosh老师的mysql教学笔记，转自知乎笔记】

13.3 JDBC的典型用法

• JDBC的全称是Java Database Connectivity，即Java数据库连接，它是一种可以执行SOL语句的Java API。程序可通过JDBCAPI连接到关系数据库，并使用结构化查询语言（SOL，数据库标准的查询语言）来完成对数据库的查询、更新。
  

13.3.1 JDBC 4.2 常用接口和类

• DriverManager：用于管理JDBC驱动的服务类。（获取Connection对象）
• Connection： 代表数据库连接对象，一个物理连接对象。
• Statement：用于执行SQL语句的工具接口。
• PreparedStatement：预编译的Statement对象。
• ResultSet：结果集对象。

13.3.2 JDBC编程步骤

（1）加载数据库驱动。通常使用Class类的forName()静态方法来加载驱动。

//加载MySQL的驱动
Class.forName("com.mysql.jdbc.Driver");
//加载Oracle的驱动
Class.forName("oracle.jdbc.driver.OracleDriver");

（2）通过DriverManager获取数据库连接。提供如下方法：

jdbc:mysql://hostname:port/databasename
jdbc:oracle:thin:@hostname:port:datebasename

（3）通过Connection对象创建Statement对象。

createStatement（）：创建基本的Statement对象。
prepareStatement（String sql）：根据传入的SQL 语句创建预编译的Statement对象。
prepareCall（String sql）：根据传入的SQL 语句创建CallableStatement对象。

（4）使用Statement执行SQL语句。

execute()：可以执行任何SQL语句，但比较麻烦。
executeUpdate()：主要用于执行DML和DDL语句。
executeQuery()：只能执行查询语句。

（5）操作结果集。

next()、previous()、first()、last()、beforeFirst()、afterLast()、absolute() 等移动记录指针的方法。
getXxx()方法获取记录指针指向行、特定列的值。该方法既可使用列索引作为参数，也可使用列

（6）回收数据库资源。关闭ResultSet、Statement、Connection等资源。

public class ConnMySq1
public static void main(String[]args) throws Exception{
	//1.加载驱动，使用反射知识，现在记住这么写
	Class.forName("com.mysql.jdbc.Driver");
	try(
		//2.使用DriverManager获取数据库连接
		//其中返回的Connection就代表了Java程序和数据库的连接
		//不同数据库的URL写法需要查驱动文档，用户名、密码由DBA分配
		Connection conn = DriverManager.getConnection("jdbc:mysq1：//127.0.0.1:3306/select test?useSSL=true"
,"root","32147");
		//3.使用connection来创建一个statement对象
		Statement stmt = conn.createStatement();
		//4.执行SQL语句
		/*
		Statement有三种执行SQL语句的方法：
		1.execute（）可执行任何SQL语句一返回一个boolean值如果执行后第一个结果是				ResultSet，则返回true，否则返回false
		2.executeQuery（）执行select语句一返回查询到的结果集
		3.executeUpdate（）用于执行DML语句一返回一个整数代表被SQL语句影响的记录条数
		*/
		ResultSet rs = stmt.executeQuery("select s.*, teacher_name"
+ "from student_table s, teacher_table t" + "where t.teacher_id = s.java_teacher"))
		{
			//ResultSet有一系列的getxxx（列索引l列名）方法，用于获取记录指针
			//指向行、特定列的值，不断地使用next（）将记录指针下移一行
			//如果移动之后记录指针依然指向有效行，则next（）方法返回true 
			while(rs.next()){
				System.out.println(rs.getInt(1) + "\t"
				+ rs.getString(2) + "t"
				+ rs.getString(3) + "\t"
				+ rs.getString(4));
			}
		}
	}
}

13.4 执行SQL语句 的方式

• 使用executeUpdate() 或 executeLargeUpdate() 方法执行DDL和DML语句。
• 使用execute() 方法执行任何 SQL语句。（不确定时）
• 使用PreparedStatement执行，（反复执行，使用占位符 ？）

//使用Connection创建一个PreparedStatement对象
PreparedStatement pstmt = conn.prepareStatement("insert into student_table value(null,?,?)");
pstmt.setString(1,"jack");

使用PreparedStatement比Statement多三个好处：

PreparedStatement预编译SQL 语句，性能更好。
PreparedStatement 无须拼接SQL语句，编程更简单。
PreparedStatement可以防止SQL注入，安全性更好。

• 使用CallableStatement调用存储过程

//创建一个简单地存储过程
delimiter //
create procedure add_pro(a int, b int, out sum int)
begin
set sum = a + b;
end;
//

//使用Connection来创建一个CallableStatement对象
CallableStatement cstmt = conn.prepareCall("{call add_pro(?,?,?)}");
cstmt.setInt(1,4);
cstmt.setInt(2,5);
//注册 CallableStatement的第三个参数是int类型
cstmt.registerOutParameter(3,Types.INTEGER);
//执行存储过程
cstmt.execute();

13.5 管理结果集

JDBC使用ResultSet来封装执行查询得到的查询结果，然后通过移动ResultSet的记录指针来取出结果集的内容。JDBC还允许通过ResultSet来更新记录，并提供了ResultSetMetaData来获得ResultSet对象的相关信息。

13.5.1 可滚动、可更新的结果集

• 以默认方式打开的ResultSet是不可更新的，创建可更新的ResultSet，则必须在创建Statement 或PreparedStatement时传入额外的参数。
• Connection在创建Statement 或PreparedStatement时还可额外传入如下两个参数：
  （1）resultSetType：控制ResultSet的类型。可以取如下三个值：

ResultSet.TYPE_FORWARD_ONLY：该常量控制记录指针只向前移动。JDK1.4以前的默认值。
ResultSet.TYPE_SCROLL_INSENSITIVE：该常量控制记录指针可以自由移动（可滚动结果集），但底层数据的改变不会影响 ResultSet的内容。
ResultSet.TYPE_SCROLL_SENSITIVE：该常量控制记录指针可以自由移动（可滚动结果集），而且底层数据的改变会影响ResultSet的内容。

（2）resultSetConcurrency：控制ResultSet的并发类型，可以接收如下两个值：

ResultSet.CONCUR_READ_ONLY：该常量指示ResultSet是只读的并发模式（默认）。
ResultSet.CONCUR_UPDATABLE：该常量指示ResultSet是可更新的并发模式。

//使用Connection创建一个PreparedStatement对象
//传入控制结果集可滚动、可更新的参数
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

可更新的结果集还需要满足如下两个条件：

所有数据都应该来自一个表。
选出的数据集必须包含主键列。

13.5.2 处理Blob类型数据

• Blob（Binary Long Object）是二进制长对象，用于存储大文件（图片，声音文件）
• 将Blob数据插入数据库需要使用PreparedStatement，该对象有一个方法setBinaryStream（int parameterlndex，InputStream x），指定参数传入二进制输入流。
• 从ResultSet里取出Blob数据时， getBlob（int columnlndex）方法，将返回一个Blob对象，Blob对象提供了getBinaryStream（）方法来获取该Blob数据的输入流，也提供的getBytes（）方法直接取出该Blob对象封装的二进制数据。

13.5.3 使用ResultSetMetaData分析结果集

• ResultSetMetaData来获取关于ResultSet的描述信息。
• MetaData的意思是元数据，即描述其他数据的数据，因此ResultSetMetaData封装了描述ResultSet对象的数据；后面还要介绍的DatabaseMetaData则封装了描述Database的数据。
• ResultSet 里包含一个getMetaData（）方法，返回该ResultSet对应ResultSetMetaData对象，就可通过ResultSetMetaData提供的大量方法来返回ResultSet的描述信息。
• 常用的方法有如下三个：

int getColumnCount()：返回该ResultSet的列数量。
String getColumnName（int column）：返回指定索引的列名。
int getColumnType（int column）：返回指定索引的列类型。

13.6 Javar的RowSet

• RowSet 接口继承了ResultSet接口，RowSet接口下包含JdbcRowSet、CachedRowSet、FilteredRowSet、JoinRowSet和WebRowSet常用子接口。除JdbcRowSet需要保持与数据库的连接之外，其余4个子接口都是离线的RowSet，无须保持与数据库的连接。
• RowSet默认是可滚动、可更新、可序列化的结果集。它可以把底层的数据读取到内存中进行离线操作，操作完成后再同步到底层数据源。

13.6.1 java 7 新增的RowSetFactory与RowSet

• Java 7新增了RowSetProvider类和RowSetFactory接口，其中RowSetProvider负责创建RowSetFactory
• RowSetFactory则提供了如下方法来创建 RowSet实例：

CachedRowSet createCachedRowSet（）：创建一个默认的CachedRowSet。
FilteredRowSet createFilteredRowSet（）：创建一个默认的 FilteredRowSet。
JdbcRowSet createJdbcRowSet（）：创建一个默认的JdbcRowSet。
JoinRowSet createJoinRowSet（）：创建一个默认的JoinRowSet。
WebRowSet createWebRowSet（）：创建一个默认的WebRowSet。

• 使用RowSetFactory，可以把应用程序与RowSet实现类分离开，避免直接使用JdbcRow Setlmpl等非公开的API，也更有利于后期的升级、扩展。
• RowSetFactory的几个工厂方法不难看出，使用RowSetFactory创建的RowSet其实并没有装填数据。
• 为了让RowSet能抓取到数据库的数据，需要为RowSet设置数据库的URL、用户名、密码等连接信息。因此，RowSet接口中定义了如下常用方法：

setUrl（String url）：设置该RowSet要访问的数据库的URL。
setUsername（String name）：设置该RowSet要访问的数据库的用户名。
setPassword（String password）：设置该RowSet要访问的数据库的密码。
setCommand（String sql）：设置使用该sql 语句的查询结果来装填该RowSet。
execute（）：执行查询。

13.6.2 离线RowSet

• 离线RowSet会直接将底层数据读入内存中，封装成RowSet对象，而RowSet对象则完全可以当成Java Bean来使用。
• 因此不仅安全，而且编程十分简单。CachedRowSet是所有离线RowSet的父接口。

13.6.3 离线RowSet的查询分页

• CachedRowSet提供了分页功能。就是一次只装载ResultSet里的某几条记录，这样就可以避免CachedRowSet占用内存过大的问题。
• CachedRowSet提供了如下方法来控制分页：

populate（ResultSet rs，int startRow）：使用给定的ResultSet装填RowSet，从ResultSet的第startRow条记录开始装填。
setPageSize（int pageSize）：设置CachedRowSet每次返回多少条记录。
previousPage（）：在底层ResultSet可用的情况下，让CachedRowSet读取上一页记录。
nextPage（）：在底层ResultSet可用的情况下，让CachedRowSet 读取下一页记录。

13.7 事务处理

13.7.1 事务的概念和MySQL事务支持

• 事务是由一步或几步数据库操作序列组成的逻辑执行单元，这系列操作要么全部执行，要么全部放弃执行。程序和事务是两个不同的概念。一般而言，一段程序中可能包含多个事务。
• 事务具备4个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。这4个特性也简称为ACID性。

原子性（Atomicity）：事务是应用中最小的执行单位，就如原子是自然界的最小颗粒，具有不可再分的特征一样，事务是应用中不可再分的最小逻辑执行体。
一致性（Consistency）：事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。
隔离性（Isolation）：各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务都是隔离的。
持续性（Durability）：持续性也称为持久性（Persistence），指事务一旦提交，对数据所做的任何改变都要记录到永久存储器中，通常就是保存进物理数据库。

• 事务的提交有两种方式：显示提交（使用commit）；自动提交（执行DDL或DCL语句，或者程序正常退出）。
• 事务回滚有两种方式：显示回滚（使用rollback，也可以回滚到指定中间点）；自动回滚（系统错误或强行退出）。

13.7.2 JDBC的事务支持

• JDBC连接的事务支持由Connection提供。
• 可以调用Connection的setAutoCommit()方法来关闭自动提交，开启事务。

conn.setAutoCommit(false);

• 还可调用Connection的getAutoCommit()方法返回该连接得自动提交模式。

stmt.executeUpdate(...);

• 如果所有的SQL语句都执行成功了，调用Connection的commit()方法提交事务。

conn.commit();

• 如果任意一条SQL语句执行失败，应该用Connection的rollback()方法来回滚事务。

conn.rollback();

• Connection 也提供了设置中间点的方法：

Savepoint setSavepoint()：在当前事务中创建一个未命名的中间点，并返回代表该中间点的Savepoint对象。
Savepoint setSavepoint（String name）：在当前事务中创建一个具有指定名称的中间点，并返回代表该中间点的Savepoint对象。

13.7.3 java 8 增强的批量更新

• 批量更新需要先创建一个Statement对象，利用该对象的addBatch()方法将多条SQL语句同时收集起来，最后调用executeLargeBatch()（或原有的executeBatch()）方法同时执行这些SQL语句。

Statement stmt = conn.createStatement();
//使用Statement同时收集多条SQL语句
stmt.addBatch(sql1);
stmt.addBatch(sql2);
...
//同时执行所有的SQL语句
stmt.executeLargeBatch();

• 为了让批量操作可以正确地处理错误，必须把批量执行的操作视为单个事务，如果批量更新在执行过程中失败，则让事务回滚到批量操作开始之前的状态。

13.8 分析数据库信息

• JDBC提供了DatabaseMetaData来封装数据库连接对应数据库的信息，通过Connection提供的getMetaData（）方法就可以获取数据库对应的DatabaseMetaData对象。
• DatabaseMetaData接口通常由驱动程序供应商提供实现，使用该接口的目的是发现如何处理底层数据库，尤其是对于试图与多个数据库一起使用的应用程序——因为应用程序需要在多个数据库之间切换，所以必须利用该接口来找出底层数据库的功能。
• 如果已经确定应用程序所使用的数据库系统，则可以通过数据库的系统表来分析数据库信息。前面已经提到，系统表又称为数据字典，数据字典的数据通常由数据库系统负责维护，用户通常只能查询数据字典，而不能修改数据字典的内容。
• 如果需要获得数据库信息，包括该数据库驱动提供了哪些功能，则应该利用DatabaseMetaData来了解该数据库支持哪些功能。
• 如果需要纯粹地分析数据库的静态对象，例如分析数据库系统里包含多少数据库、数据表、视图、索引等信息，则利用系统表会更加合适。

13.9 使用连接池管理连接

• 数据库连接池的解决方案是：当应用程序启动时，系统主动建立足够的数据库连接，并将这些连接组成一个连接池。每次应用程序请求数据库连接时，无须重新打开连接，而是从连接池中取出已有的连接使用，使用完后不再关闭数据库连接，而是直接将连接归还给连接池。通过使用连接池，将大大提高程序的运行效率。
• DBCP数据源：应在系统中增加两个jar文件

commons-dbcp.jar：连接池的实现。
commons-pool.jar：连接池实现的依赖库。

Tomcat的连接池正是采用该连接池实现的。

//创建数据源对象
BasicDataSource ds = new BasicDataSource();
//设置连接池所需的驱动
ds.setDriverClassName("com.mysql.jdbc.Driver");
//设置连接数据库的URL 
ds.setUrl("jdbc:mysql://localhost：3306/javaee");
//设置连接数据库的用户名
ds.setUsername("root");
//设置连接数据库的密码
ds.setPassword("pass");
//设置连接池的初始连接数
ds.setInitialSize(5);
//设置连接池最多可有多少个活动连接数
ds.setMaxActive(20);
//设置连接池中最少有2个空闲的连接
ds.setMinIdle(2);

//通过数据源获取数据库连接
Connection conn = ds.getConnection();
//释放数据库连接
conn.close();

• C3P0数据源：性能更胜一筹，Hibernate就推荐使用该连接池。不仅可以自动清理不再使用的Connection，还可以自动清理 Statement和ResultSet。
• 应在系统中增加如下JAR文件。

c3p0-0.9.1.2.jar:C3P0连接池的实现。

//创建连接池实例
ComboPooledDataSource ds = new ComboPooledDataSource();
//设置连接池连接数据库所需的驱动
ds.setDriverClass("com.mysq1.jdbc.Driver");
//设置连接数据库的URL 
ds.setJdbcUr1("jdbc:mysq1：//localhost：3306/javaee");
//设置连接数据库的用户名
ds.setUser("root");
//设置连接数据库的密码
ds.setPassword("32147");
//设置连接池的最大连接数
ds.setMaxPoolSize(40);
//设置连接池的最小连接数
ds.setMinPoolSize(2);
//设置连接池的初始连接数
ds.setInitialPoolSize(10);
//设置连接池的缓存Statement的最大数
ds.setMaxStatements(180);

//获取数据库连接
Connection conn = ds.getConnection();