JDBC 实现事务管理

左手的ㄟ右手 2022-09-24 14:20 156阅读 0赞

为了实现数据库数据的安全性，数据库有了事务管理机制。

事务管理具有

原子性 —— 具有相互关联的一系列操作，要么一次全部执行成功，要么执行失败，数据回滚；

一致性 —— 数据库在事务执行前后，数据库都应处于相同的状态；

持久性 —— 一旦事务提交，事务对于数据库的变更是持久的；

隔离性 —— 同一数据库各个事务之间独立执行，并不互相串扰。

现使用如下代码实现对数据库的事务的表述，通过前后事务执行过程中的冲突，表现事务的原子性。

create table tbl_user(
    id int(11) unsigned not null auto_increment,
    name varchar(50) not null default '',
    password varchar(50) not null default '',
    email varchar(50) default '',
    primary key (id))
    engine = InnoDB
    default charset = utf8;
    
    create table tbl_address(
    id int(11) unsigned not null auto_increment,
    city varchar(20) default null,
    country varchar(20) default null,
    user_id int(11) unsigned not null,
    primary key(id))
    engine = innodb
    default charset = utf8;
    
    insert into tbl_user(id,name,password,email) values
    (1,'xiaoming','123456','xiaoming@gmail.com'),
    (2,'xiangzhang','123456','xiaozhang@gmail.com');
    
    insert into tbl_address (city,country,user_id) values
    ('beijing','china',1);
    
    insert into tbl_address (city,country,user_id) values
    ('tianjin','china',2);

对数据库进行两次操作，第一次操作成功实现，第二次操作，数据库中原有的数据与插入数据出现主键冲突时，程序报错，假设两次操作是相互关联的操作，出于数据库事务的原子性考虑，现采用数据回滚解决报错。代码示例如下。

package jdbc_Pack_005;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    
    public class JDBC_Cls_005 {
    
    	public static Connection getConnection(){
    		Connection connection = null;
    		try{
    			Class.forName("com.mysql.jdbc.Driver");
    			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","1233211234567");
    			}
    		catch(Exception e){
    			e.printStackTrace();
    		}
    		return connection;
    	} 
    	
    	public static void insertUserData(){
    		Connection connection = getConnection();
    		try {
    			String sql = "INSERT INTO tbl_user(id,name,password,email)"
    					+ "VALUES(10,'Tom','123456','tom@gmail.com')";
    			Statement statement = (Statement) connection.createStatement();
    			int count = statement.executeUpdate(sql);
    			System.out.println("向用户表中插入了"+count+"条记录 ！");
    			connection.close();
    		} catch (Exception e) {
    			// TODO: handle exception
    			e.printStackTrace();
    		}
    	}
    
    
    	public static void insertAddrData(){
    		Connection connection = getConnection();
    		try {
    			String sql = "INSERT INTO tbl_address(id,city,country,user_id)"
    					+ "VALUES(1,'shanghai','china','10')";
    			Statement statement = (Statement) connection.createStatement();
    			int count = statement.executeUpdate(sql);
    			System.out.println("向地址表中插入了"+count+"条记录 ！");
    			connection.close();
    		} catch (Exception e) {
    			// TODO: handle exception
    			e.printStackTrace();
    		}
    	}
    	
    	public static void main(String []args) {
    		insertUserData();
    		insertAddrData();
    	}
    }

package jdbc_Pack_005;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.Statement;
    
    public class JDBC_Cls_006 {
    
    	public static Connection getConnection(){
    		Connection connection = null;
    		try{
    			Class.forName("com.mysql.jdbc.Driver");
    			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","1233211234567");
    			}
    		catch(Exception e){
    			e.printStackTrace();
    		}
    		return connection;
    	} 
    	
    	public static void insertUserData(Connection connection)throws Exception{
    		String sql = "INSERT INTO tbl_user(id,name,password,email)"
    				+ "VALUES(10,'Tom','123456','tom@gmail.com')";
    		Statement statement = (Statement) connection.createStatement();
    		int count = statement.executeUpdate(sql);
    		System.out.println("向用户表中插入了"+count+"条记录 ！");
    	}
     
    
    	public static void insertAddrData(Connection connection)throws Exception{
    		String sql = "INSERT INTO tbl_address(id,city,country,user_id)"
    				+ "VALUES(1,'shanghai','china','10')";
    		Statement statement = (Statement) connection.createStatement();
    		int count = statement.executeUpdate(sql);
    		System.out.println("向地址表中插入了"+count+"条记录 ！");
    	}
    	
    	public static void main(String []args) {
    		Connection connection = null;
    		try {
    			connection = getConnection();
    			connection.setAutoCommit(false);
    			
    			insertUserData(connection);
    			insertAddrData(connection);	
    			
    			connection.commit();
    		} catch (Exception e1) {
    			// TODO: handle exception
    			System.out.println("=========== 捕获到SQL异常 ===========");
    			e1.printStackTrace();
    			try {
    				connection.rollback();
    				System.out.println("=========== 事务回滚成功 ===========");
    			} catch (Exception e2) {
    				e2.printStackTrace();
    			}
    		}finally{
    			try{
    				if(connection != null){
    					connection.close();
    				}
    			}catch(Exception e3){
    				e3.printStackTrace();
    			}
    		}
    	}
    }

配置文件报错

Tue Aug 02 17:00:34 CST 2016 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

解决方案，配置文件修改为

driver = com.mysql.jdbc.Driver
    dburl = jdbc\:mysql\://localhost\:3306/jsp_db?characterEncoding=utf8&useSSL=false
    user = root
    password = 1233211234567