JDBC课程
两日大纲
● JDBC基本操作
● 预设语句对象
● 自定义数据库工具类
● JDBC批处理
● 事务
● 连接池
*************************************************************************************************
1. JDBC简介
JDBC全称为：Java DataBase Connectivity（java数据库连接）, 主要作用是用java代码来连接数据库
它是SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范，称之为JDBC。
没有jdbc的时候
有jdbc之后
简单的来说，没有jdbc，连接几个不同种类的数据库，就需要写几套完全不同的代码。
有了jdbc之后，只需要写几套很相似的代码（API完全相同）。因为虽然有jdbc标准，但是各大数据库对标准sql语句的支持还是不一样。
比如：mysql 自动增长和 sql server 以及 Oracle 互相之间就完全不一样。
*************************************************************************************************
2. JDBC基本操作
jdbc需要mysql驱动jar包，所以：
先找到mysql的驱动（jdbc的实现类），并且拷贝到WEB工程WEB-INF中的lib文件夹中。
如果不是web工程请build-path。
2.1 添加数据
2.1.1 添加数据快速入门
/**
* 添加数据
*
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void insertData() throws SQLException {
// 注册驱动：告诉java去连接哪种数据库
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
// 建立连接，获得连接对象
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/mao", "root", "123456");
//创建语句对象
Statement stmt = con.createStatement();
//执行sql语句
stmt.executeUpdate("insert into class(className,classDesc)values('158期Java就业','和尚班')");
stmt.close();//关闭语句对象
con.close();//关闭连接对象
}
DriverManager：驱动管理类，负责管理驱动，创建连接对象
Connection ：连接对象类，负责连接，并创建语句对象
Statement ：语句对象，负责执行sql语句
*************************************************************************************************
2.1.2 注册驱动
// 注册驱动：告诉java去连接哪种数据库
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
|
static void |
registerDriver(Driver driver) |
参数：Driver---来自于你要连接的数据库（如果连接orcale，来自于oracle。如果要连接mysql，来自于mysql）
registerDriver(new com.mysql.jdbc.Driver());
导入源码：
但是这样写驱动会注册两次。（因为在mysql中的Driver中有静态代码块，已经注册了。）
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
// ~ Static fields/initializers
// ---------------------------------------------
//
// Register ourselves with the DriverManager
//
static {
try {
java.sql.DriverManager.registerDriver(new Driver());
} catch (SQLException E) {
throw new RuntimeException("Can't register driver!");
}
}
......
所以，我们可以直接写成：
// 注册驱动：告诉java去连接哪种数据库
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
new com.mysql.jdbc.Driver();
但是这样写
1、静态初始化已经new了一个Driver的对象，注册到DriverManager中去，在此再建立一个Driver对象则是完全没有必要的，浪费空间。
2、不够灵活，如果需要换数据库的话，需要改动代码，此时最好把要变的内容改成字符串的形式，可以由变量读取外部配置文件进行传入。
所以，我们最后写成：
//注册驱动：告诉java去连接哪种数据库
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//new com.mysql.jdbc.Driver();
Class.forName("com.mysql.jdbc.Driver");
Class.forName()的作用是要求JVM查找并加载指定的类，也就是说JVM会执行该类的静态代码段。
既然在静态初始化器的中已经进行了注册，所以我们在使用JDBC时只需要Class.forName(XXX.XXX);就可以了。
*************************************************************************************************
2.1.3 获得连接对象
// 建立连接，获得连接对象
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mao", "root", "123456");
|
static Connection |
getConnection(String url, String user, String password)
|
url：连接到某一个具体的数据库
user：数据库的用户名
password：数据库用户名对应的密码。
url："jdbc:mysql://localhost:3306/mao"
jdbc jdbc协议
mysql jdbc子协议（表示mysql协议）
localhost 本地万能域名（也可以换成ip地址）
3306 mysql数据库端口号
mao 数据库名称
如果连接的mysql是在本机，并且mysql的端口是3306，比如：
url："jdbc:mysql://localhost:3306/mao"
那么url可以简写为：
url："jdbc:mysql:///mao"
jdbc:mysql://localhost:3306/mao?userUnicode=true&characterEncoding=utf8
url后面可以跟这个参数去解决数据库的乱码问题。
添加的作用是：指定字符的编码、解码格式。
例如：mysql数据库用的是gbk编码，而项目数据库用的是utf8编码。这时候如果添加了useUnicode=true&characterEncoding=UTF8 ，那么作用有如下两个方面：
1. 存数据时：
数据库在存放项目数据的时候会先用UTF-8格式将数据解码成字节码，然后再将解码后的字节码重新使用GBK编码存放到数据库中。
2.取数据时：
在从数据库中取数据的时候，数据库会先将数据库中的数据按GBK格式解码成字节码，然后再将解码后的字节码重新按UTF-8格式编码数据，最后再将数据返回给客户端。
*************************************************************************************************
2.1.4 创建语句对象
//创建语句对象
Statement stmt = con.createStatement();
|
createStatement()
|
*************************************************************************************************
2.1.5 执行更新sql语句
//执行sql语句
stmt.executeUpdate("insert into class(className,classDesc)values('158期Java就业','和尚班')");
|
int |
executeUpdate(String sql)
|
int 影响的行数（sql语句改变了数据库中几条数据） =executeUpdate(String sql)：向数据库发送执行更改语句。用于向数据库发送insert、update或delete语句
*************************************************************************************************
2.1.6 关闭语句对象和连接对象
stmt.close();//关闭语句对象
con.close();//关闭连接对象
注意：切记一定要关闭，关闭顺序是先关后创建的对象，再关先创建的对象。
不关不会出错，但是会占用数据库连接，网站会也来越慢，而且非常不好查出来，比语法错误，逻辑错误，都要难解决。
危险仅次于RP错误（时好时坏错误）
*************************************************************************************************
2.2 删除数据
删除数据的代码和添加一模一样，只是传入的sql语句不同。
/**
* 删除数据
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void deleteData() throws ClassNotFoundException, SQLException
{
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获得连接对象
String url = "jdbc:mysql:///mao";
Connection con = DriverManager.getConnection(url,"root","123456");
//获得语句对象
Statement stmt = con.createStatement();
//执行sql语句
String sql = "delete from class where id = 4";
int i = stmt.executeUpdate(sql);//返回影响行数
System.out.println(i);
//关闭相关对象
stmt.close();
con.close();
}
*************************************************************************************************
2.3 修改数据
修改数据的代码和添加一模一样，只是传入的sql语句不同。
/**
* 修改数据
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void updateData() throws ClassNotFoundException, SQLException
{
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获得连接对象
String url = "jdbc:mysql:///mao";
Connection con = DriverManager.getConnection(url,"root","123456");
//获得语句对象
Statement stmt = con.createStatement();
//执行sql语句
String sql = "update class set classDesc = '美女班' where id = 4";
int i = stmt.executeUpdate(sql);//返回影响行数
System.out.println(i);
//关闭相关对象
stmt.close();
con.close();
}
*************************************************************************************************
2.4 查询数据
2.4.1 查询数据快速入门
/**
* 查询数据
*
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void queryData() throws ClassNotFoundException, SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 获得连接对象
String url = "jdbc:mysql:///mao";
Connection con = DriverManager.getConnection(url, "root", "123456");
// 获得语句对象
Statement stmt = con.createStatement();
// 执行查询的sql语句
String sql = "select * from class";
ResultSet rs = stmt.executeQuery(sql);// 返回结果集对象（类似与java中的集合）
// 遍历结果集并取出结果集对象里面的内容
//rs.next();// 将rs游标下移，并返回该行之后是否还有数据，有数据为true，没数据为false
while(rs.next())
{
int id = rs.getInt("id");// 根据列名取得列的值
String className = rs.getString(2);// 根据列索引取得列的值,列索引从1开始
String classDesc = rs.getString("classDesc");
System.out.println(id);
System.out.println(className);
System.out.println(classDesc);
}
rs.close();//关闭结果集对象
stmt.close();//关闭语句对象
con.close();//关闭连接对象
}
*************************************************************************************************
2.4.2 执行查询sql语句
// 执行查询的sql语句
String sql = "select * from class";
ResultSet rs = stmt.executeQuery(sql);// 返回结果集对象（类似与java中的集合）
|
executeQuery(String sql)
|
*************************************************************************************************
2.4.3 遍历结果集
// 遍历结果集并取出结果集对象里面的内容
//rs.next();// 将rs游标下移，并返回该行之后是否还有数据，有数据为true，没数据为false
while(rs.next())
{
int id = rs.getInt("id");// 根据列名取得列的值
String className = rs.getString(2);// 根据列索引取得列的值,列索引从1开始
String classDesc = rs.getString("classDesc");
System.out.println(id);
System.out.println(className);
System.out.println(classDesc);
}
rs.next()：将rs游标下移，并返回该行之后是否还有数据，有数据为true，没数据为false
rs.getXXX(参数)：根据列名或者列索引取得列的值
如果有别名，按照别名去获取。
建议使用按照列名去获取数据。
ResultSet其它API（了解）：
|
boolean |
absolute(int row)
|
|
void |
afterLast()
|
|
void |
beforeFirst()
|
|
boolean |
previous()
|
*************************************************************************************************
2.4.4 关闭三大对象
rs.close();//关闭结果集对象
stmt.close();//关闭语句对象
con.close();//关闭连接对象
切记：一定要按照打开的反顺序关闭。
*************************************************************************************************
3. 预设语句对象（重中之重）
3.1 Statement的缺陷
3.1.1 容易被sql注入
sql注入：在页面表单中输入sql的片段。达到串改程序中sql语句。
正常情况：
select * from user where username='zhangsan' and password = '123456';
当sql被注入之后的语句：
select * from user where username='zhangsan' and password = '' or '1'='1'
3.1.2 效率不高
Statement每执行一次sql进行一次编译。即使每次的sql语句格式都一样。
*************************************************************************************************
3.2 PreparedStatement的优势
3.2.1 防止sql注入
PreparedStatement会对特殊字符进行转义，并通过参数形式设置到语句中，而不再是变量拼凑成sql语句。
3.2.2 预编译功能
相同格式的sql语句只被编译一次，后一条格式相同的sql语句运行时，只需改变参数的值即可。
*************************************************************************************************
3.3 PreparedStatement更新数据
更新包括增加、删除、修改三种操作。
/**
* 更新数据（PreparedStatement方式）
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void updateData() throws ClassNotFoundException, SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 获得连接对象
String url = "jdbc:mysql:///mao";
Connection con = DriverManager.getConnection(url, "root", "123456");
// 获得PreparedStatement（预设语句对象）
// 此时就需要传入sql语句，sql语句写成问号参数形式
String sql = "update class set classDesc = ? where id = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
//按照参数的顺序设置问号参数的值，索引从1开始
pstmt.setString(1, "小美女班");
pstmt.setInt(2, 4);
//执行更新,返回影响行数
int i = pstmt.executeUpdate();
pstmt.close();//关闭语句对象
con.close();//关闭连接对象
}
增加、删除、修改代码全都一样，只是传入的sql语句不同。
*************************************************************************************************
3.3 PreparedStatement查询数据
/**
* 查询数据（PreparedStatement方式）
*
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void queryData() throws ClassNotFoundException, SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 获得连接对象
String url = "jdbc:mysql:///mao";
Connection con = DriverManager.getConnection(url, "root", "123456");
// 获得PreparedStatement（预设语句对象）
// 此时就需要传入sql语句，sql语句写成问号参数形式，如果没有参数就不用设置
String sql = "select * from class where id = ?";
PreparedStatement pstmt = con.prepareStatement(sql);
//设置问号参数的值
pstmt.setInt(1, 4);
//执行查询
ResultSet rs = pstmt.executeQuery();
//遍历结果集
while(rs.next())
{
int id = rs.getInt("id");
String className = rs.getString("className");
String classDesc = rs.getString("classDesc");
//打印出结果
System.out.println(id);
System.out.println(className);
System.out.println(classDesc);
}
//关闭三大对象
rs.close();
pstmt.close();
con.close();
}
*************************************************************************************************
3.4 PreparedStatement API
|
prepareStatement(String sql)
|
注意：sql提前创建好的。sql语句中需要参数。使用？进行站位。
举例：
select *from user where username=zhangsan and password = 123456;
使用？进行站位
select * from user where username=? and password=?
1、conn.prepareStatement(sql); -----需要你事先传递sql。如果sql需要参数，使用？进行占位。
2、设置参数（执行sql之前）：prepStmt.setXXX(int index, 要放入的值) -----根据不同类型的数据进行方法的选择。
方法的参数说明：
第一个参数：int index ;表示的是问号出现的位置。问号是从1开始计数
第二个参数：要问号的位置传入的值。
3、执行，不需要在传递sql语句了。
pStmt.executeQuery();---执行select
pStmt.executeUpdate();---执行insert，delete，update
*************************************************************************************************
4. 自定义数据库工具类
4.1 需求和设计思路
JDBC增删改用法代码完全一样，就传入的sql语句和参数不不一样。查询的代码与他们也有很多地方是一样的。
这些相同的代码我们可以把它抽象出来，形成自己的方法，更方便让程序员使用。
普通设计思路：
DBUtils
getConnection(); //获得连接对象
close(); //关闭相关对象
设计思路：
DBService（数据库服务类）
execUpdate(sql); 执行更新sql语句
execQuery(sql); 执行查询sql语句
4.2 实现代码
package cn.itcast;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SqlService {
// 四大金刚
String driver = "com.mysql.jdbc.Driver";// 驱动名称
String url = "jdbc:mysql:///mao";// 连接字符串
String username = "root";// 用户名
String password = "123456";// 密码
// 三剑客
Connection con = null;// 连接对象
PreparedStatement pstmt = null;// 语句对象
ResultSet rs = null;// 结果集对象
/**
* 获得连接对象
*
* @return连接对象
* @throws ClassNotFoundException
* @throws SQLException
*/
public Connection getConnection() throws ClassNotFoundException,
SQLException {
Class.forName(driver);
con = DriverManager.getConnection(url, username, password);
return con;
}
/**
* 关闭三剑客
*
* @throws SQLException
*/
public void close(ResultSet rs,PreparedStatement pstmt,Connection con) {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
/**
* 执行更新
*
* @param sql
* 传入的预设的sql语句
* @param params
* 问号参数列表
* @return影响行数
*/
public int execUpdate(String sql, Object[] params) {
try {
this.getConnection();// 获得连接对象
this.pstmt = this.con.prepareStatement(sql);// 获得预设语句对象
if (params != null) {
// 设置参数列表
for (int i = 0; i < params.length; i++) {
// 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
this.pstmt.setObject(i + 1, params[i] + "");
}
}
return this.pstmt.executeUpdate();// 执行更新，并返回影响行数
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
this.close(this.rs,this.pstmt,this.con);
}
return 0;
}
/**
* 执行查询
*
* @param sql
* 传入的预设的sql语句
* @param params
* 问号参数列表
* @return查询后的结果
*/
public List<Map<String, Object>> execQuery(String sql, Object[] params) {
try {
this.getConnection();// 获得连接对象
this.pstmt = this.con.prepareStatement(sql);// 获得预设语句对象
if (params != null) {
// 设置参数列表
for (int i = 0; i < params.length; i++) {
// 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
this.pstmt.setObject(i + 1, params[i] + "");
}
}
// 执行查询
ResultSet rs = pstmt.executeQuery();
List<Map<String, Object>> al = new ArrayList<Map<String, Object>>();
// 获得结果集元数据（元数据就是描述数据的数据，比如把表的列类型列名等作为数据）
ResultSetMetaData rsmd = rs.getMetaData();
// 获得列的总数
int columnCount = rsmd.getColumnCount();
// 遍历结果集
while (rs.next()) {
Map<String, Object> hm = new HashMap<String, Object>();
for (int i = 0; i < columnCount; i++) {
// 根据列索引取得每一列的列名,索引从1开始
String columnName = rsmd.getColumnName(i + 1);
// 根据列名获得列值
Object columnValue = rs.getObject(columnName);
// 将列名作为key，列值作为值，放入hm中，每个hm相当于一条记录
hm.put(columnName, columnValue);
}
// 将每个hm添加到al中,al相当于是整个表，每个hm是里面的一条记录
al.add(hm);
}
return al;
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
this.close(this.rs,this.pstmt,this.con);
}
return null;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
// 测试执行更新
// String sql = "insert into student"
// + "(stuName,stuSex,chineseScore,englishScore,"
// + "mathScore,birthday,classId)" + "values(?,?,?,?,?,?,?)";
//
// Object[] params = { "毛大龙", 'm', 99, 98, 97, "1981/11/17", 1 };
// int i = new SqlService().execUpdate(sql, params);
// System.out.println(i);
// 测试执行查询
// String sql = "select * from class where id = ?";
// List<Map<String, Object>> al = new SqlService().execQuery(sql,
// new Object[] { 1 });
//
// for (Map<String, Object> map : al) {
// System.out.println(map.get("className"));
// }
// 测试执行查询,没有参数
String sql = "select * from class";
List<Map<String, Object>> al = new SqlService().execQuery(sql,null);
for (Map<String, Object> map : al) {
System.out.println(map.get("className"));
}
}
}
*************************************************************************************************
5. JDBC批处理
5.1 JDBC批处理介绍
业务场景：当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率。
其实就是批量操作，比如批量更新（批量添加、批量删除、批量修改）
实现批处理有两种方式：
1、第一种方式使用Statement
2、第一种方式使用PreparedStatement
*************************************************************************************************
5.2 Statement实现批处理
插入100条数据
/**
* 插入数据（批处理Statement方式）
*
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void insertData() throws ClassNotFoundException, SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 获得连接对象
String url = "jdbc:mysql:///mao";
Connection con = DriverManager.getConnection(url, "root", "123456");
// 获得语句对象
Statement stmt = con.createStatement();
for (int i = 0; i < 100; i++) {
// 产生n条格式相同的sql语句
String sql = "insert into student(stuName,stuSex,chineseScore," +
"englishScore,mathScore,birthday,classId)values('大毛"
+ i + "号','m',98.5,70,50,'1981-11-17',1)";
stmt.addBatch(sql);// 将这些sql语句添加到stmt对象中
}
// 批量执行,返回数组，这个数组是说明每条命令所影响的行数
stmt.executeBatch();
// 关闭相关对象
stmt.close();
con.close();
}
*************************************************************************************************
5.3 PreparedStatement实现批处理
添加100条数据
/**
* 修改数据（批处理PreparedStatement方式）
*
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void updateData() throws ClassNotFoundException, SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 获得连接对象
String url = "jdbc:mysql:///mao";
Connection con = DriverManager.getConnection(url, "root", "123456");
// 获得PreparedStatement（预设语句对象）
// 此时就需要传入sql语句，sql语句写成问号参数形式
String sql = "insert into student" +
"(stuName,stuSex,chineseScore,englishScore," +
"mathScore,birthday,classId)" +
"values(?,?,?,?,?,?,?);";
//获得预设语句对象
PreparedStatement pstmt = con.prepareStatement(sql);
for (int i = 0; i < 100; i++) {
//设置参数
pstmt.setString(1, "大毛"+i+"号");
pstmt.setString(2, "m");
pstmt.setFloat(3, i);
pstmt.setFloat(4, i);
pstmt.setFloat(5, i);
//添加当前日期
pstmt.setDate(6, new Date(System.currentTimeMillis()));
pstmt.setInt(7, (i%4+1));
//把当前参数添加到批处理中
pstmt.addBatch();
}
//执行批处理
pstmt.executeBatch();
// 关闭相关对象
pstmt.close();
con.close();
*************************************************************************************************
5.4 清除批处理
clearBatch()方法：清除批处理命令
/**
* 修改数据（批处理PreparedStatement方式）
*
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void updateData() throws ClassNotFoundException, SQLException {
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
// 获得连接对象
String url = "jdbc:mysql:///mao";
Connection con = DriverManager.getConnection(url, "root", "123456");
// 获得PreparedStatement（预设语句对象）
// 此时就需要传入sql语句，sql语句写成问号参数形式
String sql = "insert into student" +
"(stuName,stuSex,chineseScore,englishScore," +
"mathScore,birthday,classId)" +
"values(?,?,?,?,?,?,?);";
//获得预设语句对象
PreparedStatement pstmt = con.prepareStatement(sql);
for (int i = 0; i < 100; i++) {
//设置参数
pstmt.setString(1, "大毛"+i+"号");
pstmt.setString(2, "m");
pstmt.setFloat(3, i);
pstmt.setFloat(4, i);
pstmt.setFloat(5, i);
pstmt.setDate(6, new Date(System.currentTimeMillis()));
pstmt.setInt(7, (i%4+1));
//把当前参数添加到批处理中
pstmt.addBatch();
if(i%10 == 0)
{
//清除批处理
pstmt.clearBatch();
//执行批处理
pstmt.executeBatch();
}
}
//执行批处理
pstmt.executeBatch();
// 关闭相关对象
pstmt.close();
con.close();
}
*************************************************************************************************
6. 事务
6.1 事务的概念
指的的逻辑上的一组（一组sql,insert update ,delete）操作，组成这组操作的各个单元(各个sql)，要不全部成功，要么全部失败。
举例：转账 a 给b 转账 100 a原来有1000 b原来也有1000
account 是一个表名表示的账务表，里面有人的信息，和人的金额。
update account set money=money-100 where name = a;
update account set money=money+100 where name = b;
假设：没有事务
update account set money=money-100 where name=a; a 900
出现了异常。
update account set money=money+100 where name=b; b 1000
后果： a的余额是900 b的余额 1000
问题：那100跑哪里去了？
事务的特点：全成功，全失败。
update account set money=money-100 where name=a; a 900
出现了异常。可以控制数据会发生回滚，a 依旧是 1000
update account set money=money+100 where name=b; b 1000
*************************************************************************************************
6.2 MySQL中的事务
6.2.1 模拟转账业务
#如果存在mao数据库，则删除它
drop database if exists mao;
#创建数据库
create database mao;
#使用数据库
use mao;
#创建账户表
create table account
(
id int auto_increment,#账户id，自动增长
accountName varchar(50),#账户姓名
money float(11,2),#账户余额
primary key(id)
);
#初始化账户表
insert into account(accountName,money)values('大毛',1000);
insert into account(accountName,money)values('二毛',1000);
select * from account;
#开始转账业务
update account set money = money - 100 where id = 1;
show tablesxxx;#发生了错误
update account set money = money + 100 where id = 2;
select * from account;
mysql中，默认事务是自动提交的。一条sql一个事务。这样的方式在我们的业务中，等于是没有事务。
*************************************************************************************************
6.2.1 事务管理
start transaction ---- 开启一个事务。以后的sql都在一个事务中。更改的内容不会自动提交。
rollback ---事务的回滚—同时失败的情况。--事务结束，并且全部失败，数据回复到开始之前的状态
commit ----------事务的提交----同时成功---事务结束。全部成功。
start transaction;#开始事务
update account set money = money - 100 where id = 1;
rollback;#事务回滚
commit;#成功提交
*************************************************************************************************
6.3 JDBC操作事务
6.3.1 没有事务失败案例
/**
* ，没有事务，并且失败的转账
* @throws ClassNotFoundException
* @throws SQLException
*/
@Test
public void changeMoneyFail() throws ClassNotFoundException, SQLException {
SqlService sqlService = new SqlService();
//大毛钱-100
String sql1 = "update account set money = money-? where id = ?";
sqlService.execUpdate(sql1, new Object[]{100,1});
int num = 5/0;//发生异常
//二毛钱+100
String sql2 = "update account set money = money+? where id = ?";
sqlService.execUpdate(sql2, new Object[]{100,2});
}
*************************************************************************************************
6.3.2 事务API
JDBC的事务管理，通过Connection 对象来完成的事务管理。
|
void |
setAutoCommit(boolean autoCommit)
|
当我们传递的参数是 false的时候相当于start transaction; 开启事务
当传递true的时候，自动提交。相当于一条sql语句一个事务。并且jdbc中。默认就是true。
开启事务：con.setAutoCommit(false);
|
void |
rollback()
|
相当于rollback；表示的是事务结束。并且更改无效。把数据恢复到开启事务时候的状态。
|
void |
commit()
|
相当于commit 。表示事务的结束。并且数据更改有效
一旦数据commit之后，永久更改了。不能回滚了。
一般rollback 要放入到catch中。并且抓取异常的时候，尽可能抓取最大的（不是绝对）。
*************************************************************************************************
6.3.3 编写事务代码
/**
* 有事务的转账，再也不怕有异常了
*
* @throws SQLException
* @throws ClassNotFoundException
*/
@Test
public void changeMoneyOK() throws ClassNotFoundException, SQLException{
SqlService sqlService = new SqlService();
// 获得连接对象
Connection con = sqlService.getConnection();
// 设置开启手动事务(关闭自动提交)
con.setAutoCommit(false);
PreparedStatement pstmt = null;
try {
// 大毛的钱-100
String sql1 = "update account set money = money-? where id = ?";
pstmt = con.prepareStatement(sql1);
pstmt.setInt(1, 100);
pstmt.setInt(2, 1);
pstmt.executeUpdate();
int num = 5/0;//主动发生错误
// 二毛的钱+100
String sql2 = "update account set money = money+? where id = ?";
pstmt = con.prepareStatement(sql2);
pstmt.setInt(1, 100);
pstmt.setInt(2, 2);
pstmt.executeUpdate();
//提交事务
con.commit();
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
con.rollback();
}finally{
//关闭相关对象
sqlService.close(null, pstmt, con);
}
}
*************************************************************************************************
6.4 事务的特点
6.4.1 事务四大特性
简称ACID
原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位（最小的一个整体），事务中的操作要么都发生，要么都不发生。
一组操作时一个整体。不能分割。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
一致性和原子性相关。只有都成功或者，都失败（原子性），就可以保证事务的一致性。
隔离性（Isolation）
事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。
多个事务是独立存在的。多个事物不能够相互干扰。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
*************************************************************************************************
6.4.2 事务不隔离产生的问题
如果不考虑事务的隔离性，将会产生以下问题：
由数据事务的并发造成的问题。
脏读，---最严重的事情。
不可重复读
幻读（虚读）
1、脏读
脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。
一个事务读取了另外一个事务没有的提交的数据。非常严重。尽可能去避免掉脏读。
假设 A 转给B 100元
update account set money = money - 100 where id = 1;
update account set money = money + 100 where id = 2;
第1条sql执行完，第2条还没执行，如果此时B查询自己的账户，就会发现多了100元。
如果A等B走后在回滚，B还以为自己很有钱，结果......
依据脏数据所做的操作可能是不正确的，也可以能会很危险。
2、不可重复读
不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。
简单的说：在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
不可重复读强调的是某条记录数据内容的改变 update。
3、幻读（虚读）
一个事务读取另外一个事务已经提交过的数据。但强调的是条目数的改变insert，delete
例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中居然还有没修改的数据行，就好象发生了幻觉一样。
*************************************************************************************************
6.4.3 事务隔离级别
数据库共定义了四种隔离级别：
Serializable：可避免脏读、不可重复读、虚读情况的发生
Repeatable read：可避免脏读、不可重复读情况的发生
Read committed：可避免脏读情况发生
Read uncommitted：最低级别，以上情况均无法保证
设置事务的隔离级别
set session transaction isolation level 设置事务隔离级别
select @@tx_isolation 查询当前事务隔离级别
注意：只有当并发端都设置了此事务级别，并且开启了事务，该事务隔离才会生效。
1、Read uncommitted
#设置事务隔离级别为：最低级别
set session transaction isolation level Read uncommitted
#查询事务级别
select @@tx_isolation
#开始事务
start transaction;
2、Read committed
#设置事务隔离级别为：避免脏渎
set session transaction isolation level Read committed;
#查询事务级别
select @@tx_isolation;
#开始事务
start transaction;
3、Repeatable read
#设置事务隔离级别为：避免脏渎，不可重复读
set session transaction isolation level Repeatable read;
#查询事务级别
select @@tx_isolation;
#开始事务
start transaction;
在mysql中。mysql数据库本身，对幻读（虚读）已经进行了优化处理。所以展示不出幻读（虚读）的发生。
4、Serializable
#设置事务隔离级别为：串行化，避免一切问题
set session transaction isolation level Serializable;
#查询事务级别
select @@tx_isolation;
#开始事务
start transaction;
串行化的可以避免所有的问题。数据库让其他的事务进行等待，等待一个事务结束之后，这个事务再去操作。
切记：此级别定要慎用！
REPEATABLE-READ 是mysql默认的隔离级别
Read committed 是 oracle 默认的隔离级别
mysql中，对幻读（虚读）进行了处理。避免幻读（虚读）的发生。但是处理的结果不太满意。有时候还会发生。
*************************************************************************************************
6.4.4 隔离级别的性能
性能比较
Serializable 性能最差：事务一个一个执行的。排队。
Serializable < Repeatable read < Read committed < Read uncommitted
安全性比较
Serializable 安全性最好：所有问题避免掉。
Serializable > Repeatable read > Read committed > Read uncommitted
Read uncommitted 避免不了最重问题，脏读。
Serializable：性能太差。
*************************************************************************************************
7. 连接池
7.1 连接池的概念
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。
最小连接数：是连接池一直保持的数据库连接。
最大连接数：是连接池能申请的最大连接数，如果数据库连接请求超过此数，后面的数据库连接请求将被加入到等待队列中。
最大空闲值：当经过一个高峰时间后，连接池可以慢慢将已经用不到的连接慢慢释放一部分，一直减少到最大空闲值为止。
最小空闲值：当空闲的连接数少于阈值时，连接池就会预申请去一些连接，以免洪峰来时来不及申请。
*************************************************************************************************
7.2 常用连接池的使用
常用的连接池主要有：C3P0，Proxool，DBCP，Druid等。
Spring 推荐使用dbcp
Hibernate 推荐使用c3p0和proxool
我 推荐使用Druid
7.2.1 dbcp连接池
DBCP(DataBase connection pool),数据库连接池。是 apache 上的一个 java 连接池项目，也是 tomcat 使用的连接池组件。单独使用dbcp需要2个包：commons-dbcp.jar,commons-pool.jar由于建立数据库连接是一个非常耗时耗资源的行为，所以通过连接池预先同数据库建立一些连接，放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完后再放回去。
/**
* dbcp连接池测试
* @throws SQLException
*/
@Test
public void dbcpTest() throws SQLException
{
//创建dbcp连接池
BasicDataSource ds = new BasicDataSource();
//设置各种参数
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql:///mao");
ds.setUsername("root");
ds.setPassword("123456");
//最大连接数
ds.setMaxActive(50);
//通过连接池对象创建连接
Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement("select * from account");
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("accountName"));
}
rs.close();
pstmt.close();
con.close();//并没有真的关闭连接对象，而是放回连接池中去了
}
也可以通过属性配置文件来获得各种参数：
url:jdbc:mysql:///mao
driverClassName:com.mysql.jdbc.Driver
username:root
password:123456
/**
* dbcp连接池测试，通过属性配置文件
* @throws Exception
*/
@Test
public void dbcpTest2() throws Exception
{
//获得文件输入流
InputStream is = new FileInputStream("src/dbcp.properties");
//创建属性文件对象
Properties p = new Properties();
//将文件输入流封装到属性文件对象中
p.load(is);
//创建dbcp连接池
DataSource ds = BasicDataSourceFactory.createDataSource(p);
//通过连接池对象创建连接
Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement("select * from account");
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("accountName"));
}
rs.close();
pstmt.close();
con.close();//并没有真的关闭连接对象，而是放回连接池中去了
}
*************************************************************************************************
7.2.2 c3p0连接池
C3P0是一个开源的JDBC连接池。目前使用它的开源项目有Hibernate，Spring等。
/**
* c3p0连接池测试
* @throws SQLException
* @throws PropertyVetoException
*/
@Test
public void c3p0Test() throws SQLException, PropertyVetoException
{
//创建c3p0连接池
ComboPooledDataSource ds = new ComboPooledDataSource();
//设置各种参数
ds.setDriverClass("com.mysql.jdbc.Driver");
ds.setJdbcUrl("jdbc:mysql:///mao");
ds.setUser("root");
ds.setPassword("123456");
//最大连接数
ds.setMaxPoolSize(50);
//通过连接池对象创建连接
Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement("select * from account");
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("accountName"));
}
rs.close();
pstmt.close();
con.close();//并没有真的关闭连接对象，而是放回连接池中去了
}
也可以通过XML配置文件来获得各种参数：
我们把xml的配置文件放大src的目录下，并且名字要是c3p0-config.xml
c3p0 会自己去定位这个xml文件，并且自己去读取xml里面的信息。
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<!-- 配置连接的参数信息 -->
<!-- 使用property 配置连接参数 name属性，表示的连接参数的key
标签中的内容，就是key对应值
-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///mao</property>
<property name="user">root</property>
<property name="password">123456</property>
</default-config>
</c3p0-config>
/**
* c3p0连接池测试,使用xml配置文件
* @throws SQLException
* @throws PropertyVetoException
*/
@Test
public void c3p0Test2() throws SQLException, PropertyVetoException
{
//创建c3p0连接池
ComboPooledDataSource ds = new ComboPooledDataSource();
//通过连接池对象创建连接
Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement("select * from account");
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("accountName"));
}
rs.close();
pstmt.close();
con.close();//并没有真的关闭连接对象，而是放回连接池中去了
}
也可以使用自定义配置连接名称：
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="itcast">
<!-- 配置连接的参数信息 -->
<!-- 使用property 配置连接参数 name属性，表示的连接参数的key
标签中的内容，就是key对应值
-->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql:///mao</property>
<property name="user">root</property>
<property name="password">123456</property>
</named-config>
</c3p0-config>
/**
* c3p0连接池测试,使用xml配置文件,并自定义连接名称
* @throws SQLException
* @throws PropertyVetoException
*/
@Test
public void c3p0Test3() throws SQLException, PropertyVetoException
{
//创建c3p0连接池
ComboPooledDataSource ds = new ComboPooledDataSource("itcast");
//通过连接池对象创建连接
Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement("select * from account");
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("accountName"));
}
rs.close();
pstmt.close();
con.close();//并没有真的关闭连接对象，而是放回连接池中去了
}
*************************************************************************************************
7.2.3 druid连接池
现在最好的连接池，国产阿里巴巴的出品的技术框架
需要同时导入druid 和log4j的jar包
/**
* druid连接池测试
* @throws SQLException
* @throws PropertyVetoException
*/
@Test
public void druidTest() throws SQLException, PropertyVetoException
{
//创建druid连接池
DruidDataSource ds = new DruidDataSource();
//设置各种参数
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql:///mao");
ds.setUsername("root");
ds.setPassword("123456");
//通过连接池对象创建连接
Connection con = ds.getConnection();
PreparedStatement pstmt = con.prepareStatement("select * from account");
ResultSet rs = pstmt.executeQuery();
while(rs.next())
{
System.out.println(rs.getString("accountName"));
}
rs.close();
pstmt.close();
con.close();//并没有真的关闭连接对象，而是放回连接池中去了
}
配置日志文件 log4j.properties （目前只需简单了解）
log4j.properties文件名称固定，且必需放置在src目录下。
log4j.rootCategory=INFO,console,logfile
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Threshold=INFO
log4j.appender.console.ImmediateFlush=true
log4j.appender.console.Target=System.out
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%p:%m %d %n
log4j.appender.logfile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.logfile.Append=true
log4j.appender.logfile.File= ../logs/vbyou/log
log4j.appender.logfile.DatePattern=yyyy-MM-dd'.txt'
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=%p:%m %d %n
log4j.appender.logfile.encoding=UTF-8
*************************************************************************************************
7.3 使用连接池的数据库工具类
package cn.itcast;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.alibaba.druid.pool.DruidDataSource;
public class SqlService {
// 三剑客
Connection con = null;// 连接对象
PreparedStatement pstmt = null;// 语句对象
ResultSet rs = null;// 结果集对象
/**
* 获得连接对象
*
* @return连接对象
* @throws ClassNotFoundException
* @throws SQLException
*/
public Connection getConnection() throws ClassNotFoundException,
SQLException {
// 创建druid连接池
DruidDataSource ds = new DruidDataSource();
// 设置各种参数
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl("jdbc:mysql:///mao3");
ds.setUsername("root");
ds.setPassword("123456");
// 通过连接池对象创建连接
con = ds.getConnection();
return con;
}
/**
* 关闭三剑客
*
* @throws SQLException
*/
public void close(ResultSet rs, PreparedStatement pstmt, Connection con) {
try {
if (rs != null)
rs.close();
if (pstmt != null)
pstmt.close();
if (con != null)
con.close();
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
}
/**
* 执行更新
*
* @param sql
* 传入的预设的sql语句
* @param params
* 问号参数列表
* @return影响行数
*/
public int execUpdate(String sql, Object[] params) {
try {
this.getConnection();// 获得连接对象
this.pstmt = this.con.prepareStatement(sql);// 获得预设语句对象
if (params != null) {
// 设置参数列表
for (int i = 0; i < params.length; i++) {
// 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
this.pstmt.setObject(i + 1, params[i] + "");
}
}
return this.pstmt.executeUpdate();// 执行更新，并返回影响行数
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
this.close(this.rs, this.pstmt, this.con);
}
return 0;
}
/**
* 执行查询
*
* @param sql
* 传入的预设的sql语句
* @param params
* 问号参数列表
* @return查询后的结果
*/
public List<Map<String, Object>> execQuery(String sql, Object[] params) {
try {
this.getConnection();// 获得连接对象
this.pstmt = this.con.prepareStatement(sql);// 获得预设语句对象
if (params != null) {
// 设置参数列表
for (int i = 0; i < params.length; i++) {
// 因为问号参数的索引是从1开始，所以是i+1，将所有值都转为字符串形式，好让setObject成功运行
this.pstmt.setObject(i + 1, params[i] + "");
}
}
// 执行查询
ResultSet rs = pstmt.executeQuery();
List<Map<String, Object>> al = new ArrayList<Map<String, Object>>();
// 获得结果集元数据（元数据就是描述数据的数据，比如把表的列类型列名等作为数据）
ResultSetMetaData rsmd = rs.getMetaData();
// 获得列的总数
int columnCount = rsmd.getColumnCount();
// 遍历结果集
while (rs.next()) {
Map<String, Object> hm = new HashMap<String, Object>();
for (int i = 0; i < columnCount; i++) {
// 根据列索引取得每一列的列名,索引从1开始
String columnName = rsmd.getColumnName(i + 1);
// 根据列名获得列值
Object columnValue = rs.getObject(columnName);
// 将列名作为key，列值作为值，放入hm中，每个hm相当于一条记录
hm.put(columnName, columnValue);
}
// 将每个hm添加到al中,al相当于是整个表，每个hm是里面的一条记录
al.add(hm);
}
return al;
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
this.close(this.rs, this.pstmt, this.con);
}
return null;
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
// 测试执行更新（插入）
// String sql = "insert into student"
// + "(stuName,stuSex,chineseScore,englishScore,"
// + "mathScore,birthday,classId)" + "values(?,?,?,?,?,?,?)";
//
// Object[] params = { "毛大龙2222", 'm', 99, 98, 97, "1981/11/17", 1 };
// int i = new SqlService().execUpdate(sql, params);
// System.out.println(i);
// 测试执行查询
// String sql = "select * from class where id = ?";
// List<Map<String, Object>> al = new SqlService().execQuery(sql,
// new Object[] { 1 });
//
// for (Map<String, Object> map : al) {
// System.out.println(map.get("className"));
// }
// 测试执行查询,没有参数
// String sql = "select * from class";
// List<Map<String, Object>> al = new SqlService().execQuery(sql, null);
//
// for (Map<String, Object> map : al) {
// System.out.println(map.get("className"));
// }
}
}
*************************************************************************************************
8. 总结