JDBC PreparedStatement，工具类封装，悲观锁【JDBC实例 --- 模拟用户登录】

JDBC学习

Java养成计划78，79天

---

jdbc连接数据库应用，功能查询

---

之前已经分享了jdbc编程6步： 注册驱动，告诉程序要连接哪种数据库，使用DriverManager；建立数据库连接对象，Connect对象获取也依靠DriverManager的getConnection方法；建立数据库操作对象；有了连接就可以在连接的基础上建立很多操作对象，这里就使用connect的实例方法获取Statement对象createStatement；执行SQL语句，如果是DQL，那么需要ResultSet对象存储数据；处理查询结果集；释放资源，依次释放上述的ResultSet，Statement，Connect对象close

这里可以再写一次，因为步骤时固定的，越熟练越好，注意ip不要写错了

package test;

import java.sql.*;

public class JdbcTest {
	public static void main(String[] args) {
		Connection conn = null;
		Statement state = null;
		ResultSet result = null;
		try {//时区为北京时区GMT%2B8    这里的?servertimezone=GMT%2B8  可加可不加，有的时候报错就是因为这个，这是url的query
			DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cfengtest?servertimezone=GMT%2B8", "cfeng", "**********");
			state = conn.createStatement();
			result = state.executeQuery("SELECT empno,ename,sal FROM emp LIMIT 6");
			while(result.next()) {
				System.out.println(result.getString(1) + "  " + result.getString(2) + "  " + result.getString(3));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(result != null) {
				try {
					result.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(state != null) {
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

接下来继续来分享新的内容

注册驱动的第二种方式

上面的程序中可以看出注册驱动的第一种方式为使用DriverManager的类方法来注册，这里还有其他的方式来注册驱动，这里可以介绍一下

之前做的java笔试面试题中就有一个题目，加载驱动光选项中就有3种方法，除了DriverManager，还有添加系统的jdbc.driver属性和反射类加载的方式

• 这是因为Driver类中有静态代码块，随类的加载而加载，所以可以使用反射机制

之前讲过反射，就是利用字节码的各种方法就可以得到结果了，String s = (String)Class.forName(“java.lang.String”).getDeclaredConstructor().newInstance(); 只是要注意直接.newInstance在version9就过时了

这里看一下使用类加载【之后讲数据写入配置文件】就可以方便进行程序维护

//利用Class类的froName(String classname)可以获取识别的类的字节码
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/cfengtest", "cfeng", "***********");
//这里就是前两步，这里成立的原因就是

类加载的时候类中所有的static修饰的部分都会随之加载，比如DriveMananger中那些静态方法就包含在类的字节码中，这里又设计到了JVM和类加载的过程了

可以看一下Driver的源码

static{
    try{
        java.sql.DriverManager.registerDriver(new Driver()); 
    }catch(SQLException E){
        throw new RuntimeException("Can't register driver");
    }
}

这里有一个静态代码块，专门用来进行forname初始化执行操作，所以就相当于之前的普通操作，十分方便

类加载的过程 — 装载，连接，初始化 ，所以精确一点来说虽然所有的静态的都会随类加载而加载，但是只要不调用变量和方法，那就不会执行，而静态代码块中的是直接会随着初始化而执行的

执行静态代码块的几种情况

• 第一次new A()的时候会执行静态代码块，这个过程包括了初始化
• 第一次Class.forName(“A”)的时候会执行，因为这个过程相当于 Class.forName(“A”,true,this.getClass.getClassLoader()) true代表要初始化
• 与之相对应的Class.forName(“A”,false,this.getClass.getClassLoader()) 不会执行，因为指出步进行初始化，那么就不会执行

使用配置文件来存放信息

上面的程序，比如数据库的名称，用户名，密码之类的String都可以放入配置文件进行读取，方便操作修改维护，之前在Java反射中就使用到了这个技术

写一个配置文件Mysql connectivity configuration mysql连接配置

那么从配置文件中获取属性有很多中方式，这里介绍两种

• 第一种： 使用Properties类

//这种方式需要文件的绝对路径，较为不便
Properties prop = new Properties();  //创建一个对象
prop.load(new FileInputStream("wenjianlujing"));  //通过文件流获取到文件
prop.getProperties(属性名);  //取得相关属性String

• 第二种方式 资源绑定器ResourceBundle

使用这种方式只能绑定扩展名为.properties文件，并且这个文件必须在src路径下（即默认搜索路径是src下），如果文件不是直接在src下，而是在其子目录中，则需要写出访问的相对路径

//这种方式相对容易一点，使用资源绑定类ResourceBundle
//也就是说，只有直接在src下的properties才可以直接写名称，如果是在src下面的包中，则还需要加一个包名
ResourceBundle bundle = ResourceBundle.getBundle("resource/db");

这里专门加一个包Resource，用来存放资源

//这里的配置文件在src下面建立了一个资源包Resource，里面只放了配置文件，所以和普通的包是有区别的；绑定的时候需要加上包名，不然不能找到
//这里就讲所有的jdbc的配置信息放入了db.properties
package test;

import java.sql.*;
import java.util.ResourceBundle;

public class JdbcTest {
	public static void main(String[] args) {
		ResourceBundle bundle = ResourceBundle.getBundle("Resource/db");
		String driver = bundle.getString("driver");
		String user = bundle.getString("user");
		String url = bundle.getString("url");
		String password = bundle.getString("password");
		String sql = bundle.getString("sql");
		Connection conn = null;
		Statement state = null;
		ResultSet result = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
			state = conn.createStatement();
			result = state.executeQuery(sql);
			while(result.next()) {
				System.out.println(result.getString(1) + "  " + result.getString(2) + "  " + result.getString(3));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(result != null) {
				try {
					result.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(state != null) {
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

信息都在配置文件中【解耦合，高扩展】配置文件使用#可以注释

########### mysql connectivity configuration########
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/cfengtest?servertimezone=GMT%2B8
user = cfeng
password = *************
sql = SELECT empno,ename,sal FROM emp LIMIT 6

注意 ： 之后使用jdbc连接数据库的时候就将数据库的配置文件，里面放入信息，比如driver,url,user,password

连接数据库都要有配置文件，这是开发的主要思想，因为交付的程序是让用户自己去修改自己的用户名称和密码

模拟用户登录

我们使用上面的知识来实现一个简单的功能： 用户的登录

用户数据用户名和密码，输入之后java程序进行比对，如果比对成功，就登录成功，否则就是登录失败

这里首先要有一张数据库表，所以这里我们新建一个数据库表在cfengbase中

USE cfengbase;
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
id int PRIMARY KEY AUTO_INCREMENT,
logname  VARCHAR(25) UNIQUE,
logpassword  VARCHAR(25) NOT NULL,
realname  VARCHAR(25)
);
INSERT INTO t_user (logname,logpassword,realname) VALUES ("admin","123","管理员");
INSERT INTO t_user (logname,logpassword,realname) VALUES ("zhangsan","123","张三");
INSERT INTO t_user (logname,logpassword,realname) VALUES ("lisi","123","李四");
SELECT * FROM t_user;

这样这张数据库表就建立完成了

现在只需要修改一下url就可以了将cfengtest修改为cfengbase

在sql语句中如何使用动态的变量

这在sql语句中当然不会出现，出现的情况是在jdbc中，这里因为要使用变量，其实很简单，因为sql语句是字符串String，所以只要使用字符串拼接就可以了

欢迎进入本系统，请输入正确的用户名和密码
用户名: 张三
密码: 123
java.sql.SQLSyntaxErrorException: Unknown column '张三' in 'where clause'
登录失败

这里就是因为没有把变量用单引号括起来，但是要将其放入双引号中

需要注意查询的名称要用单引号括起来，如果不括起来就会当成字段处理，所以这里可以用双引号加单引号

String sql = "SELECT ename,empno FROM emp WHERE ename =  '" + name + "'";
//注意jdbc中的sql语句不需要加分号

这样就可以动态查询数据了

可以看一下完整的实现过程

package test;

import java.sql.*;
import java.util.*;

public class JdbcTest {
	
	/**
	 * 登录的界面
	 * @return Map<>
	 */
	public static Map<String, String> logUI() {
		System.out.println("欢迎进入本系统，请输入正确的用户名和密码");
		@SuppressWarnings("resource")
		Scanner input = new Scanner(System.in);
		System.out.print("用户名: ");
		String user = input.next();
		System.out.print("密码: ");
		String passwrd = input.next();
		Map<String,String> userinfo = new HashMap<>();
		userinfo.put("logpassword", passwrd);
		userinfo.put("loguser", user);
		return userinfo;
	}
	
	public static boolean check(String loguser,String logpassword) {
		boolean ready = false;
		ResourceBundle bundle = ResourceBundle.getBundle("Resource/db");
		String driver = bundle.getString("driver");
		String user = bundle.getString("user");
		String url = bundle.getString("url");
		String password = bundle.getString("password");
		Connection conn = null;
		Statement state = null;
		ResultSet result = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);
			state = conn.createStatement();
			String sql = "SELECT * FROM t_user WHERE logname =  " + loguser + " AND logpassword = " + logpassword;
			result = state.executeQuery(sql);//最多一条记录
			if(result.next()) //有记录
				return true;
		} catch (Exception e) {
			System.out.println(e);
		}finally {
			if(result != null) {
				try {
					result.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(state != null) {
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return ready;
	}
	
	public static void main(String[] args) {
		//用户登录界面
		Map<String,String>  userinfo = logUI();
		//连接数据库并判断是否连接成功
		boolean ready =  check(userinfo.get("loguser"),userinfo.get("logpassword"));
		System.out.println(ready?"登录成功":"登录失败");	
	}
}

这里可以测试一下数据

欢迎进入本系统，请输入正确的用户名和密码
用户名: zhangsan 
密码: 123
登录成功

//这里再测一组数据

欢迎进入本系统，请输入正确的用户名和密码
用户名: lisi
密码: 234
登录失败

jdbc编程就可以明显理解之前数据库的优化策略，因为连接需要时间，如果查询时间过程，要很久才可以出来数据，这里要优化查询，加快速度，让客户满意

SQL注入【随意的用户名，考究的密码登录成功】

这里出现了一个有意思的情况，和之前的print有关

• Scanner的nex()只是吸取字符，遇到空格，tab，回车就会停止吸取
• nextLine是按行吸取，会吸取空格和tab，只是遇到回车停止吸取

输出也有一对，分别是out.print和out.prinln；这里简单一点，一个输出后换行，一个输出后不换行；println相当于print + \n

这里有趣的现象就是我们如果允许密码有空格，将密码的输入改为nextLine，就会出现问题

System.out.print("密码: ");
String passwrd = input.next();

• 但是这里修改要两个一起修改，不然下面的不会执行；因为print,这里的输出显示都没有换行，那么就在同一行，系统不能识别，因为都是读取字符串

欢迎进入本系统，请输入正确的用户名和密码
用户名: zhangsan
密码: 234’ OR ‘1’ = '1
登录成功

这里就出现问题了，只要考究一下输入密码，那么就一定能登录成功，这是因为密码中的也被当成SQL语句执行了

欢迎进入本系统，请输入正确的用户名和密码
用户名: zhangsan
密码: 234' OR '1' = '1
SELECT * FROM t_user WHERE logname =  'zhangsan' AND logpassword = '234' OR '1' = '1'
登录成功

这里OR后面的条件是1 = 1, 这是恒成立的，所以不管密码是什么都可以执行

现在的高级别网站都解决了这个问题，但是一些个人网站如果没有注意就可能会出现问题，这也提示我们以后搭建网站的时候要解决SQL注入问题，那么如何解决SQL注入问题呢？

sql注入发生的原因是因为用户输入的数据执行了sql语句的编译，扭曲原信息，主要是先进行字符串拼接，之后才编译的

解决sql注入问题

解决该问题的终极办法就是不使用statement数据库操作对象获取了

• 因为statement的特点就是先进行字符串的拼接，之后才会进行sql语句的编译

  • 优点 ： 可以进行sql语句的拼接
  • 缺点 ： 因为拼接的存在，可能导致程序SQL注入

• Statement有一个子接口为Preparedstatement接口，该接口的特点就是先编译再传值；文档中表明其表示sql语句的预编译对象，sql语句预编译存储在PreparedStatement中，然后可以多次高效执行该语句

  • 优点： 避免SQL注入
  • 缺点 ： 只能传值，不能拼接

其实选择不是绝对的，因为有的时候必须进行sql语句的拼接，那么就要使用Statement，这个时候只要都是next(),用户密码不允许有空格，那也是可以避免这种问题的

PreparedStatement的使用

因为PreparedSatatement是预编译对象，所以sql语句不是在第四步执行sql语句处了，而是在第三步，使用的特殊符号是 ? 这个符号在泛型中也使用过，为通配符；在jdbc中为占位符

//和上面程序的不同点
PreparedSatatement state = null;

//获取预编译数据库操作对象
String sql = "SELECT * FROM t_user WHERE logname = ? AND logpassword = ?" 
state = prepareStatement(sql);  //要预编译，给一个sql语句
//给占位符赋值，这里是字符串，就赋值[使用setString]方法
state.setString(1,loguser);
state.setString(2,logpassword);
//执行的时候，不需要给sql了
result = state.executeQuery();   //因为上面已经编译过了，不需要编译了

1代表第一个问号，2代表第二个问号

注意，这里的下标是从1开始的，除了limit是从0开始，其余大部分都是从1开始的

欢迎进入本系统，请输入正确的用户名和密码
用户名: zhangsan
密码: 123
登录成功

欢迎进入本系统，请输入正确的用户名和密码
用户名: zhangsan
密码: 123' OR '1' = '1
登录失败

及时用户数据中有关键字，只要不进行编译，那就没有事情；?占位符，两边不能有单引号；如果是字符串，会自动识别

使用Statement场景

上面提到过，有的时候为了防止SQL注入，所以就不使用Statement数据库操作对象，而是使用Prepared预编译对象；但是事物不能绝对化；

Statement的优点就是可以进行语句的拼接

但是一旦我们输入的数据中必须含有Mysql关键字，且关键字要起作用的时候，就必须要就使用Statement，而不能使用PreparedStatement

• 所以选择的依据是时候让用户输入数据中的关键字编译；编译就选择Statement，不编译就选择PreparedStatement

现在实现一个功能：排序，用户可以选择将数据升序或者降序排列

String sql = "SELECT empno,ename,sal FROM emp ORDER BY sal " + orderkey;
result = state.executeQuery(sql);//最多一条记录
while(result.next()) //有记录
	System.out.println(result.getString(1) + "  " + result.getString(2) + "  " + result.getString(3));

这样就可以实现降序或者升序排列了

但是问题就是容易产生SQL注入： 所以一个解决办法就是不让用户输入，只是让用户选择

欢迎进入本系统，请输入DESC或则和ASC【DESC降序，ASC升序】
ASC
7369  SMITH  800.0
7900  JAMES  950.0
7876  ADAMS  1100.0
7521  WARD  1250.0
7654  MARTIN  1250.0

这里我注意到一个问题，像这种SQL语句包含变量的，就不能写入配置文件，不然会报错

Statement.executeQuery() cannot issue statements that do not produce result sets

使用PreparedStatement完成CUD和模糊查询

虽然上面 说过当用户所选择输入的数据位mysql关键字且必须编译的时候，就要选择Statement；并且会采用其他的手段来避免sql注入；但是其余的用户所输入的数据位普通的值，不具有特殊的意义，那么就是用预编译对象PreparedStatement

作为一个Programmer，必须具备基本的CRUD技能，现在就演示使用PreparedStatement完成CUD的过程

其实上面已经演示过了，但是上面使用的是Statement，容易发生sql注入，所以这里就使用普通的数据就可以了

java.sql.SQLException: Field ‘empno’ doesn’t have a default value 像这种就是因为empno是NOT NULL约束，必须赋值

package test;

import java.sql.*;
import java.util.*;

public class JdbcTest {
	private static Connection conn = null;
	private static PreparedStatement state = null;
	//增删改，不需要处理查询结果集
	private static ResourceBundle bundle = ResourceBundle.getBundle("Resource/db");
	private static String url = bundle.getString("url");
	private static String user = bundle.getString("user");
	private static String paaword = bundle.getString("password");
	private static String driver = bundle.getString("driver");
	//jdbc五步
	public static void addEmp(int empno,String ename, double sal) {
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, paaword);
			String sql = "INSERT INTO emp (empno,ename,sal) VALUES (?,?,?)";
			state = conn.prepareStatement(sql); //预编译【获取数据库预编译对象并赋值】
			state.setInt(1, empno);
			state.setString(2, ename);
			state.setDouble(3, sal);
			state.executeUpdate();  //执行sql语句
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(state != null) {
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static void delEmp(String ename) {
		//给出员工姓名删除数据
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, paaword);
			String sql = "DELETE FROM emp WHERE ename = ?";
			state = conn.prepareStatement(sql); //预编译【获取数据库预编译对象并赋值】
			state.setString(1, ename);
			state.executeUpdate();  //执行sql语句
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(state != null) {
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static void updateEmp(String ename,Double newSal) {
		//修改员工信息
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, paaword);
			String sql = "UPDATE emp SET sal = ? WHERE ename = ?";
			state = conn.prepareStatement(sql); //预编译【获取数据库预编译对象并赋值】
			state.setDouble(1, newSal);
			state.setString(2, ename);
			state.executeUpdate();  //执行sql语句
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(state != null) {
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
	public static void main(String[] args) {
		addEmp(7769,"zhangsan",300.00);
//		delEmp("zhangsan");
		updateEmp("zhangsan",400.00);
	}

}

这里就演示了增删改

可以看一下效果

 7769 | zhangsan | NULL      | NULL | NULL       |  300.00 |    NULL |   NULL
 
 
 14 rows in set (0.00 sec)
 
 7769 | zhangsan | NULL      | NULL | NULL       |  400.00 |    NULL |   NULL |

这里分别就表示的是增删改；并且增加只能一次，因为UNIQUE约束，主键

其实可以让一个变量去接收executeUpdate的返回值，这样可以看到底影响了几条记录，来判断程序是否写正确

对于模糊查询的写法

• ?占位符是不能用其他的字符来修饰的，它所占据的就是一个完整的位置，和前面的部分有空格间隔
  • 所以模糊查询不能是%?, 应该直接是?
  • SELECT * FROM emp WHERE ename LIKE ?

String sql = "SELECT ename,sal FROM emp WHERE ename = ?";  //写错了sql语句就查询不出来结果


public static void queryEmp(String name) {
		ResultSet result = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, paaword);
			String sql = "SELECT ename,sal FROM emp WHERE ename LIKE ?";
			state = conn.prepareStatement(sql); //预编译【获取数据库预编译对象并赋值】
			state.setString(1, name);
			result = state.executeQuery();
			//处理查询结果集
			while(result.next()) {
				System.out.println(result.getString(1) + "\t" + result.getString(2));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(result != null) {
				try {
					result.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(state != null) {
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

queryEmp("%A%");

执行之后的结果是

ALLEN	1600.0
WARD	1250.0
MARTIN	1250.0
BLANK	2850.0
CLARK	2450.0
ADAMS	1100.0
JAMES	950.0

得到的查询结果是正确的，名字中都含有A

jdbc事务

在mysql中我们已经提到过事务了，事务的隔离级别有4个，分别是read uncommitted ，read committed，repeatable read，serializable ；并且如果不start transaction，那么默认是执行一次提交一次

那么在jabc中是事务是如何进行的呢？

这里我们以转账为例子,首先创建一个.sql文件

USE cfengbase;
DROP TABLE IF EXISTS user_account;
CREATE TABLE user_account(
id  INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
account DOUBLE(10,2)  /* 10代表有效数字，2代表小数位*/
);
INSERT INTO user_account (name,account) VALUES ("zhangsan",20000.00);
INSERT INTO user_account (name,account) VALUES ("lisi",0.00);
SELECT * FROM user_account;

产生数据库表之后，使用jdbc连接数据库，这里一个数据库操作对象可以操作多个sql语句

public static void transactionTest() {
		try {//一个数据库操作对象可以操作多个sql
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, paaword);
			String sql = "UPDATE user_account SET account = ? WHERE name = ?";
			state = conn.prepareStatement(sql); //预编译【获取数据库预编译对象并赋值】
			//张三的账户减少10000
			state.setDouble(1, 10000.00);
			state.setString(2, "zhangsan");
			int count = state.executeUpdate();  //执行sql语句
			
			Thread.sleep(1000 * 50); //线程沉睡50s观察便于效果
			
			//lisi的账户增加10000
			state.setDouble(1, 10000.00);
			state.setString(2, "lisi");
			count += state.executeUpdate();
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(state != null) {
				try {
					state.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

这里让线程沉睡了50s，这样两个sql语句执行的效果就可以很明显查看，运行时线程确实沉睡了50s，在数据库中观察几次的结果

mysql> SELECT * FROM user_account;
+----+----------+----------+
| id | name     | account  |
+----+----------+----------+
|  1 | zhangsan | 20000.00 |
|  2 | lisi     |     0.00 |
+----+----------+----------+

mysql> SELECT * FROM user_account;
+----+----------+----------+
| id | name     | account  |
+----+----------+----------+
|  1 | zhangsan | 10000.00 |
|  2 | lisi     |     0.00 |
+----+----------+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM user_account;
+----+----------+----------+
| id | name     | account  |
+----+----------+----------+
|  1 | zhangsan | 10000.00 |
|  2 | lisi     | 10000.00 |
+----+----------+----------+
2 rows in set (0.00 sec)

可以发现都是执行成功了的，count的结果是2，就是因为改变了两行的数据

可以发现在睡眠的过程中，只是上面的sql语句执行了，但是mysql的隔离级别是第三级，既然查的到数据，说明jdbc在执行上面的数据的时候就自动提交了，和程序是否结束没有关系

• jdbc默认情况下支持自动提交，也就是执行一条DML语句就会提交一次

所以这里就一定会产生一个问题了

在mysql中，单纯提事务感觉用处不大，其实其的主要作用是发挥在程序中，而不是单纯的DML语句反悔；事务表示一个完整的业务逻辑，这里的事务就是转账的整个过程；但是默认情况时是执行一条语句就执行一次，这显然是会出现问题的 ---------- 就像刚刚的沉睡50s，一旦中间发生了异常，那么这个业务就失败了，可以模拟一下

String s = null;
s.indexOf(0);

java.lang.NullPointerException: Cannot invoke "String.indexOf(int)" because "s" is null

mysql> SELECT * FROM user_account;
+----+----------+----------+
| id | name     | account  |
+----+----------+----------+
|  1 | zhangsan | 20000.00 |
|  2 | lisi     |     0.00 |
+----+----------+----------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM user_account;
+----+----------+----------+
| id | name     | account  |
+----+----------+----------+
|  1 | zhangsan | 10000.00 |
|  2 | lisi     |     0.00 |
+----+----------+----------+
2 rows in set (0.00 sec)

这显然是不满足条件的，这就是因为中间的异常导致的，所以这里就必须开启事务以让各条sql语句同时成功或者失败 也就是ACID。所以必须开启事务来制止mysql的默认自动提交

出现异常后就是进入catch语句块，打印错误信息，并且执行finally语句块，之后执行try，catch之后的语句，而try中异常后面的就不会执行了，所以不是try中语句越多越好，不然就失去效果了

关闭自动提交

因为上面已经发现自动提交的弊端，所以在实际开发中，一般都要关闭自动提交，改为手动提交，那么如何操作？

• 在mysql中是直接进行START TRANSACTION就可以了，jdbc中是不一样的

在java的sql包中的Connection接口中有一个方法为setAutoCommit ----- 将此连接的自动提交模式改为指定状态。

true代表启动自动提交模式，false代表禁用自动提交模式

• 事务的提交是Connect中的commit方法，代表提交事务
• 事务的回滚是Connect中的rollback方法

Connection conn = null;
PreparedStatement = null;
//所以当处理一个sql业务时，正常的操作步骤
try{
Class.froName(driver); //获取驱动
Conn = DriverManager.getConnection(url,user,password); //获取数据库连接对象
conn.setAutoCommit(false);   //开启事务
String sql = ……;
state = conn.preparedStatement(sql);   //获取sql操作对象【包含sql语句】
sate.setString ……   
state.executeUpdate();   //执行语句
……   //另外的sql语句
conn.commit();  //提交事务 【前面有异常那么都失败】

//释放资源 .close

在catch中加上rollback回滚

连接之后就开启事务，开始执行sql语句

现在在尝试一下添加事务之后的操作

Class.forName(driver);
conn = DriverManager.getConnection(url, user, paaword);
conn.setAutoCommit(false);
String sql = "UPDATE user_account SET account = ? WHERE name = ?";
state = conn.prepareStatement(sql); //预编译【获取数据库预编译对象并赋值】
//张三的账户减少10000
state.setDouble(1, 10000.00);
state.setString(2, "zhangsan");
int count = state.executeUpdate();  //执行sql语句
			
String s = null;
s.indexOf(0);     //都是try语句块中的语句，这里发生异常，下面的都不会执行
			
//lisi的账户增加10000
state.setDouble(1, 10000.00);
state.setString(2, "lisi");
count += state.executeUpdate();
System.out.println(count);
conn.commit();  //提交事务

}catch (Exception e) {
			if(conn != null) { //有异常那就回滚
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		}finally 

ava.lang.NullPointerException: Cannot invoke “String.indexOf(int)” because “s” is null

mysql> SELECT * FROM user_account;
+----+----------+----------+
| id | name     | account  |
+----+----------+----------+
|  1 | zhangsan | 20000.00 |
|  2 | lisi     |     0.00 |
+----+----------+----------+
2 rows in set (0.00 sec)

可以发现同时失败了，是满足事务的一致性的

删除中间的异常语句

mysql> SELECT * FROM user_account;
+----+----------+----------+
| id | name     | account  |
+----+----------+----------+
|  1 | zhangsan | 10000.00 |
|  2 | lisi     | 10000.00 |
+----+----------+----------+
2 rows in set (0.01 sec)

同时成功

所以从这里就可以发现确实将资源释放放在finally中的好处

JDBC的封装

通过上面的代码可以发现每次写代码的时候都在做大量的重复动作，比如注册驱动，还有释放资源，那么为了解决重复问题，那就应该将重复的代码封装起来，在C中就是函数，在这里就封装成为类DBUtils

首先要保证驱动只注册一次？ 如何保证，单例模式？ 不是，使用静态代码块就可以解决问题了，因为com.sql.cj.jdbc.driver就是在静态代码块，只会执行一次,在同一个程序中，类只加载一次

package test;

import java.sql.*;
import java.util.ResourceBundle;
/**
 * 工具类中的构造方法一般都是私有的，因为工具类中的方法一般都是静态的，不需要new 对象
 * 工具类都是方便使用的，都是要静态化，构造方法私有化
 * 注册驱动只有一次，所以就放在静态代码块中
 */
public class DBUtils {
	//注册驱动,static 修饰的都是类加载的时候加载，按先后顺序执行
	private static ResourceBundle bundle = ResourceBundle.getBundle("Resource/db");
	
	static {
		try {
			Class.forName(bundle.getString("driver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	//获取连接
	public static Connection getConnection() throws SQLException { //因为释放资源的时候会处理，所以这里的异常上抛
		String url = bundle.getString("url");
		String user = bundle.getString("user");
		String password = bundle.getString("password");
		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}
	
	//释放资源
	public static void close(Connection conn, Statement state,ResultSet result) {//这里传入Statement，因为PreparedStatement是子类
		if(result != null) {
			try {
				result.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(state != null) {
			try {
				state.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

接下来测试一下工具类

package test;

import java.sql.*;

public class UtilTest {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement state = null;
		ResultSet result = null;
		//注册驱动，获取连接对象
		try {
			conn = DBUtils.getConnection(); //这个时候类加载了一次，所以驱动已经注册了
			String sql = "SELECT * FROM user_account";
			state = conn.prepareStatement(sql);
			result = state.executeQuery();
			while(result.next()) {
				System.out.println(result.getString(1) + "\t" + result.getString(2));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBUtils.close(conn, state, result);
		}
	}
}

看一下是否正常

1	zhangsan
2	lisi

发现是完全正常的，如果没有结果集对象，调用close方法的时候第三个result传入null

可以发现减少了很多代码，这就是封装的好处，避免重复

• DBUtils的封装了注册驱动，获取连接和释放资源，注册驱动在静态代码块中，只执行一次；而获取连接和释放资源都是私有的静态方法实现

行级锁for update，悲观锁

sql语句的事务操作的时候就发现其serializable和synchronized有点类似，其实sql中是有锁的概念的，悲观锁和乐观锁，对关于DQL语句的悲观锁？

• 在一个DQL语句后面可以加上关键字FRO UPDATE
  • 比如SELECT * FROM emp WHERE job = ‘SALESMAN’ FOR UPDATE;
  • 这里的含义是，在本次事务执行的过程当作，job = 'SALESMAN’被查询，这些记录的查询过程中，任何人或者事务多久不能对这些记录进行修改，直到事务结束----- 和事务的最高隔离级别不同

这种机制被称为： 行级锁机制（又称为悲观锁）

使用事务就要使用3行语句，开启，提交，回滚

这里写程序模拟一下

首先要一个类来模拟开启第一个事务支持DQL并加悲观锁

package test;

import java.sql.*;
/**
 * 在当前事务中进行job = SALESMAN 的记录进行查询锁定，使用悲观锁
 *
 */
public class UtilTest {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement state = null;
		ResultSet result = null;
		//注册驱动，获取连接对象
		try {
			conn = DBUtils.getConnection(); //这个时候类加载了一次，所以驱动已经注册了
			conn.setAutoCommit(false);   //开启事务
			String sql = "SELECT ename,sal FROM emp WHERE  job = ? FOR UPDATE"; //这里加悲观锁
			state = conn.prepareStatement(sql);
			state.setString(1, "SALESMAN");
			result = state.executeQuery();
			while(result.next()) {
				System.out.println(result.getString(1) + "\t" + result.getString(2));
			}
			
			Thread.sleep(1000 * 50);  //这里为了验证悲观锁，睡眠一下
			
			conn.commit(); //结束事务
		} catch (Exception e) {
			if(conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}  //事务回滚
			}
			e.printStackTrace();
		}finally {
			DBUtils.close(conn, state, result);
		}
	}
}

第二个类同样要对job字段进行操作

package test;

import java.sql.*;

public class PressmisticTest {
	public static void main(String[] args) {
		Connection conn = null;
		PreparedStatement state = null;
		try {
			conn = DBUtils.getConnection();
			conn.setAutoCommit(false);
			String sql = "UPDATE emp SET sal = sal * 10 WHERE job = ?";
			state = conn.prepareStatement(sql);
			state.setString(1, "SALESMAN");
			int count = state.executeUpdate();
			System.out.println(count);
			conn.commit();
		} catch (SQLException e) {
			if(conn != null) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		}finally {
			DBUtils.close(conn, state, null);
		}
	}
}

这样执行之后，发现两个程序都会等待，要第一个程序结束了之后，第二个程序才会结束，这就是悲观锁

这样加上悲观锁就是可以锁住查询的部分，比序列化更细腻

Oracle并且只是锁住查询的记录,比如这里查询其他记录是不会被锁住的

Mysql中要注意悲观锁的使用，分别由行锁，表锁，无锁 是否有索引，有为行锁；否则为表锁；如果没有查到数据，为无锁

行级锁比序列化细腻，只是查询的时候锁住，并且其他的行是可以正常使用的

最好锁有索引的字段，整个表锁了就不方便了