05—JDBC（2）-CSDN博客

本文链接：https://blog.csdn.net/ChenxiLiylnf/article/details/81455811

JDBC

5、事务

需要执行SQL语句

create table account(
  id char(36) primary key,
  card_id varchar2(20) unique,
  name varchar2(8) not null,
  money number(10,2) default 0
)
insert into account values('6ab71673-9502-44ba-8db0-7f625f17a67d','1234567890','张三',1000);
insert into account (id,card_id,name) values('9883a53d-9127-4a9a-bdcb-96cf87afe831','0987654321','张三');

当批量处理数据时，如果中间有一句因为代码错误的原因可能会导致代码中断，如果代码执行一半后中断，执行权到catch，会导致出错之后的代码无法执行，但已执行的代码会提交。不能做到同时执行。所以需要经过处理

import java.sql.*;

public class Trasaction {

	public static void main(String[] args) {
		try {
			//1、加载JDBC驱动程序
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		Connection connection = null;
		Statement statement = null;
		try {
			//2、获取数据库连接
			String url = "jdbc:oracle:thin:@192.168.30.212:1522:lanqiao";
			connection = DriverManager.getConnection(url, "scott", "root");
			connection.setAutoCommit(false);//关闭默认自动事务提交（事务处理）
			//3、创建Statement实例
			statement = connection.createStatement();
			//4、执行SQL语句
			statement.executeUpdate("update account set money=money-100 where card_id= '1234567890'");
			statement.executeUpdate("update account set money=money100 where card_id= '0987654321'");
			connection.commit();//提交事务（统一提交）
		} catch (SQLException e) {
			if(connection != null)//防止connection出现空指针异常
                try {
				    connection.rollback();//撤销DML操作（统一撤销）
			    } catch (SQLException e1) {
				    e1.printStackTrace();
			    }
            }
			e.printStackTrace();
		}finally {
			//6、关闭JDBC对象，释放资源
			try {
				if(statement!=null) {
					statement.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				connection.close();//close方法会提交事务
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

四、JDBC整理

由于JDBC的连接过程比较繁琐，如果每次使用都需要使用大量的重复的代码，造成代码的冗余，所以需要需要我们把一些相同的过程截取出来封装

1、加载驱动

因为每次加载驱动都是从ojdbc中的固定文件中获取，所以可以抽取出来

public class DBLinks {
	
	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
}

2、获取连接

由于每次连接的数据库不固定，所以需要定义properties文件，以及propertiesUtil工具类；

user_name=scott
password=root
url=jdbc:oracle:thin:@127.0.0.1:1521:orcl

package com.xt.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

import com.xt.exception.NotFoundPropertiesException;

public class PropertiesUtil {

	static Properties properties;
	static {
		properties = new Properties();
		InputStream inStream = PropertiesUtil.class.getClassLoader().getResourceAsStream("db.properties");
		try {
			properties.load(inStream);
		} catch (IOException e) {
			e.printStackTrace();
		}catch (NullPointerException e) {
			throw new NotFoundPropertiesException ("没有在src根目录下找到properties文件！！");
		}
	}
	
	public static String getValue(String key) {
		return properties.getProperty(key);
	}
	
	public static void main(String[] args) {
		System.out.println(getValue("url"));
	}
}

之后再可以抽出以下代码：

private Connection getConnection() {
		Connection connection=null;
		try {
			connection = DriverManager.getConnection(PropertiesUtil.getValue("url"), PropertiesUtil.getValue("user_name"), PropertiesUtil.getValue("password"));
			return connection;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

3、释放资源

private void close(Connection connection,Statement statement,ResultSet rs) {
		if (rs!= null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		if (statement!= null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		if (connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
	}

4、修改数据

public boolean update(String sql) {
		Connection connection =getConnection();
		Statement statement =null;
		try {
			statement = connection.createStatement();
			int a =statement.executeUpdate(sql);
			return a>0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(connection,statement,null);
		}
		return false ;
	}
	/**
	 * 修改数据库中的数据（增，删，改）
	 * 
	 *@autnor 李晨曦
	 *
	 * @param sql
	 * @return
	 */	
	public boolean update(String sql,Object...values) {
		Connection connection =getConnection();
		@SuppressWarnings("unused")
		Statement statement =null;
		PreparedStatement ps = null;
		try {
			statement = connection.createStatement();
			ps =connection.prepareStatement(sql);
			for (int i = 1; i <= values.length; i++) {
				ps.setObject(i, values[i-1]);
			}
			int a=ps.executeUpdate();
			return a>0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(connection,ps,null);
		}
		return false;
	}

5、查询数据

public void select(String sql,IRowMapper rowMapper) {
		Connection connection =getConnection();
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement =connection.createStatement();
			rs = statement.executeQuery(sql);
			rowMapper.dealResult(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(connection, statement, rs);
		}
	}
	/**
	 * 查询数据库
	 * 
	 *@autnor 李晨曦
	 *
	 * @param sql
	 * @param rowMapper
	 */		
	public void select(String sql,IRowMapper rowMapper,Object...values) {
		Connection connection =getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			preparedStatement =connection.prepareStatement(sql);
			for (int i = 1; i <= values.length; i++) {
				preparedStatement.setObject(i, values[i-1]);
			}
			rs = preparedStatement.executeQuery();
			rowMapper.dealResult(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(connection, preparedStatement, rs);
		}
				
	}

7、批量操作数据

public boolean batchUpdate(String ...strings) {
		Connection connection = getConnection();
		Statement statement = null;
		try {
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (String string : strings) {
				statement.executeUpdate(string);
			}
			connection.commit();
			return true;
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
			e.printStackTrace();
		}finally {
			close(connection, statement, null);
		}
		return false;
	}

完整代码

package com.xt.util.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.xt.util.PropertiesUtil;
/**
 * 数据库连接
 * 
 * @author 李晨曦
 *
 */
public class DBLinks {
	
	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
/**
 * 连接数据库
 * 
 *@autnor 李晨曦
 *
 * @return
 */
	private Connection getConnection() {
		Connection connection=null;
		try {
			connection = DriverManager.getConnection(PropertiesUtil.getValue("url"), PropertiesUtil.getValue("user_name"), PropertiesUtil.getValue("password"));
			return connection;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
/**
 * 释放数据库资源
 * 	
 *@autnor 李晨曦
 *
 * @param connection
 * @param statement
 * @param rs
 */
	private void close(Connection connection,Statement statement,ResultSet rs) {
		if (rs!= null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		if (statement!= null) {
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
		if (connection!=null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}
	}
	/**
	 * 批量处理数据
	 * 
	 *@autnor 李晨曦
	 *
	 * @param strings
	 * @return
	 */
	public boolean batchUpdate(String ...strings) {
		Connection connection = getConnection();
		Statement statement = null;
		try {
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			for (String string : strings) {
				statement.executeUpdate(string);
			}
			connection.commit();
			return true;
		} catch (SQLException e) {
			if (connection != null) {
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
			e.printStackTrace();
		}finally {
			close(connection, statement, null);
		}
		return false;
	}
	/**
	 * 修改数据库中的数据（增，删，改）
	 * 
	 *@autnor 李晨曦
	 *
	 * @param sql
	 * @return
	 */
	public boolean update(String sql) {
		Connection connection =getConnection();
		Statement statement =null;
		try {
			statement = connection.createStatement();
			int a =statement.executeUpdate(sql);
			return a>0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(connection,statement,null);
		}
		return false ;
	}
	/**
	 * 修改数据库中的数据（增，删，改）
	 * 
	 *@autnor 李晨曦
	 *
	 * @param sql
	 * @return
	 */	
	public boolean update(String sql,Object...values) {
		Connection connection =getConnection();
		@SuppressWarnings("unused")
		Statement statement =null;
		PreparedStatement ps = null;
		try {
			statement = connection.createStatement();
			ps =connection.prepareStatement(sql);
			for (int i = 1; i <= values.length; i++) {
				ps.setObject(i, values[i-1]);
			}
			int a=ps.executeUpdate();
			return a>0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(connection,ps,null);
		}
		return false;
	}
	/**
	 * 查询数据库
	 * 
	 *@autnor 李晨曦
	 *
	 * @param sql
	 * @param rowMapper
	 */
	public void select(String sql,IRowMapper rowMapper) {
		Connection connection =getConnection();
		Statement statement = null;
		ResultSet rs = null;
		try {
			statement =connection.createStatement();
			rs = statement.executeQuery(sql);
			rowMapper.dealResult(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(connection, statement, rs);
		}
	}
	/**
	 * 查询数据库
	 * 
	 *@autnor 李晨曦
	 *
	 * @param sql
	 * @param rowMapper
	 */		
	public void select(String sql,IRowMapper rowMapper,Object...values) {
		Connection connection =getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet rs = null;
		try {
			preparedStatement =connection.prepareStatement(sql);
			for (int i = 1; i <= values.length; i++) {
				preparedStatement.setObject(i, values[i-1]);
			}
			rs = preparedStatement.executeQuery();
			rowMapper.dealResult(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(connection, preparedStatement, rs);
		}
				
	}
	/**
	 *创建数据库
	 * 	
	 *@autnor 李晨曦
	 *
	 * @param sql
	 * @return
	 */
	public boolean create(String sql) {
		Connection connection =getConnection();
		Statement statement =null;
		ResultSet rs = null;
		try {
			statement = connection.createStatement();
			rs = statement.executeQuery(sql);
			return rs.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close(connection, statement, rs);
		}
		return false;
	}
}