[JDBC] JDBC, JdbcUtil tool source code, sql injection problems, transactions, database connection pool (DBCP)

Article Directory

1. What is jdbc?

Database driver :

Establish a connection between the application and the database

jdbc :

In order to simplify the operation of the developer's consent, a Java operating database specification is proposed. The specification is completed by the specific manufacturer, and the developer only needs to master the jdbc interface.

Required package

javax.sql
com.mysql.jdbc.Driver (database driver package)

2. Steps to connect to the database:

1. Load the driver

class.forName(com.mysql.jdbc.Driver);


2. User information and url

//useUnicode=true 识别中文
//characterEncoding=utf8 字符集
//useSSL=true 安全连接
String url = "jdbc:mysql://local:3306/数据库名?+"
+"useUnicode=true&characterEncoding=utf8&useSSL=true";
String username = "root";
String passwd = "";


3. The connection is successful, the database object

//con 代表数据库;
Connection con = DriverManager.getConnection(url,root.passwd);


4. Objects that execute SQL

//statement :执行SQL的对象
Statement statement = con.createStatement();
preperStatement statement = con.preperStatement(); //预处理


5. The object that executes SQL to execute SQL may have a return value

String sql = "";
ResultSet rs = statement.executeQuery(sql);//查询
int rs = statement.executeUpdata(sql); //增删改
statement.execute(sql); //增删改查
//获取信息
whiel(rs.next()){
	String name = rs.getString("字段名");
}


6. Close the connection

rs.close();
statement.close();
con.close();

JdbcUtil tool class source code:

package sdpei.jsj.comment;

import java.sql.*;

/**
 * @Description 这个类是作为对数据库操作(增删改查)操作的标准工具类
 * @author HuXuehao
 */
public class JdbcUtil {
	private static final String URL = "jdbc:mysql://localhost:3306/gym?useSSL=false&serverTimezone=UTC";
	private static final String USER = "root";
	private static final String PASSWORD = "123123";
	protected static Statement s = null; // 发送语句用的
	protected static ResultSet rs = null; // 接收查询的结果集用的
	/*
	 * 这里我还是解释一下ThreadLocal的是什么, 怎使用吧
	 * 算了,我还是给链接吧:https://www.cnblogs.com/dreamroute/p/5034726.html
	 * 
	 */
	protected static ThreadLocal<Connection> tl = new ThreadLocal<>();

	/**
	 * @Description 建立与数据 库的链接
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @date 2020年10月16日下午6:03:54
	 * @return Connection 对象
	 */
	private static synchronized Connection getConnection() {
		Connection conn  = tl.get();
		if (conn != null) // 这里的目的是为了让事务中的数据操作是使用的conn和开启事务的conn是同一个
			return conn; 
		try {
			Class.forName("com.mysql.cj.jdbc.Driver"); // 加载com.mysql.jdbc.Driver这个驱动类
			conn = DriverManager.getConnection(URL, USER, PASSWORD); // 连接数据库
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn; // 连接成功后的对象
	}

	/**
	 * @Description 对数据库进行插入、更新、删除操作(只适合相对静态SQL语句,
	 *              其实我们也可以换一种方式实现动态语句,比如在java中我们可以这样做: 
	 *              int userId = 10; 
	 *              String sql = "select * from table where id="+userId;
	 *              上述的这种sql传到executeUpdate(String sql)是不可以改变的,但是但是在传之前userId是可以
	 *              变化的。)
	 *              
	 * 注意:需要手动调用该类中的close()方法
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @date 2020年10月16日下午6:05:08
	 * @param sql
	 * @return 操作成功就返回影响的行数,操作不成功就返回0
	 */
	public static int executeUpdate(String sql) {
		int result = 0;
		try {
			s = getConnection().createStatement(); // 通过conn这个对象获取Statement对象s,s用来发送SQL语句
			result = s.executeUpdate(sql); // 执行增删改语句,使用Statement是有参数的
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return result;
	}

	/**
	 * @Description 对数据库进行查询操作(同上)
	 * 注意:需要手动调用该类中的close()方法
	 * 
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @date 2020年10月16日下午6:05:38
	 * @param sql
	 * @return Result结果集
	 */
	public static ResultSet executeQuery(String sql) {
		try {
			s = getConnection().createStatement();
			rs = s.executeQuery(sql); // 执行查询语句
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * @Description 执行动态的SQL语句 之所以可以实现动态语句,是因为它的参数中的SQL语句是要带“?”的 
	 * 例如: 
	 * String sql="insert into information(userName,userType)"+ "values(?,?)";
	 * PreparedStatement ps=null; 
	 * ps.executePreparedStatement(String sql) //这里面是有参数的 ps.setString(1,"huxuehao"); ps.setString(2,18);
	 * int result = ps.executeUpdate(); //这里面是没有参数的
	 * 
	 * 注意:需要手动调用该类中的close()方法
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @date 2020年10月16日下午6:12:07
	 * @param sql
	 * @return preparedStatement对象
	 */
	public static PreparedStatement executePreparedStatement(String sql) {
		PreparedStatement ps = null;
		try {
			ps = getConnection().prepareStatement(sql); // 只是获取到了发送动态语句的对象ps
		} catch (Exception e) {
			e.printStackTrace();
		}
		return ps;
	}

	/**
	 * @Description 关闭数据库连接对象
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @date 2020年10月16日下午6:06:21
	 */
	public static void close() {
		Connection conn = tl.get();
		try {
			if (rs != null)
				rs.close();
			if (s != null)
				s.close();
			if (conn != null)
				conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//--------------------------------------事务的手动提交以及事务的回滚----------------------------------------------
	/**
	 * 下面是开启事务,事务提交,事务回滚的三个方法 
	 * 用法: 
	 * try{ 
	 * 		JdbcUtil.beginTransaction() //开启事务
	 *		 对数据库的操作..... 
	 * 		JdbcUtil.commitTransaction() //提交事务 
	 * }catch{
	 * 		JdbcUtil.rollbackTransaction() //回滚事务 
	 * }
	 * 上面的 getConnection()方法中有一判断conn是否不为空,当conn不为空时,直接返回已经创建的链接conn,保证对数据库的操作是使用
	 * 的是同一个conn,即保证在同一个事务中
	 * 
	 * 注意:存在一个致命的问题,就是我在开启事务调用增删改查方法后,他就把conn给关了,那么我后面的提交事务和回滚事务就是无效操作
	 * 	         所以我在上面的方法中并没有调用上面定义的close()方法,这就意味着当你没有创建事务但是调用了操作数据库的方法时,我们需要在手动调用该工具类中的close()方法
	 * @Description 开启事务
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @return
	 * @date 2020年10月29日下午1:43:13
	 */
	public static void beginTransaction() {
		Connection conn = tl.get(); //获取自己的链接,接下来判断其是否为空
		if (conn == null) throw new RuntimeException("你说你是不是手欠,已经开启了事务,咱就不要重复操作了");
		conn = getConnection(); //创建连接
		try {
			conn.setAutoCommit(false); // 事务设置为手动提交
			tl.set(conn); //把当前线程的链接保存起来,为了给下面的使用
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @Description 事务的提交
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @date 2020年10月29日下午1:43:33
	 */
	public static void commitTransaction() {
		Connection conn = tl.get();
		if (conn == null) throw new RuntimeException("还没开启事务呢,你找啥急去提交事务");
		try {
			conn.commit(); //提交事务
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				tl.remove(); //移除当前连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @Description 事务的回滚
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @date 2020年10月29日下午1:43:56
	 */
	public static void rollbackTransaction() {
		Connection conn = tl.get();
		if (conn == null) throw new RuntimeException("还没开启事务呢,你找啥急去回滚事务");
		try {
			conn.rollback(); //回滚事务
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				tl.remove(); //移除当前连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * @Description 事务回滚(弃用)
	 * @author HuXuehao Email:[email protected]
	 * @version
	 * @date 2020年10月16日下午6:06:08
	 */
	/*
	 * public static void rollback() { try { getConnection().rollback(); //回滚 }
	 * catch (SQLException e) { e.printStackTrace(); } }
	 */

}


Three, sql injection problem:

There are vulnerabilities in SQL, which can lead to attacks (sql has splicing [or])

Through SQL statements, log in without an account or even tamper with the database.

Example:
The SQL statement added to the background is as follows:

String sql = “select * from user where username=' “+userName+” 'and password='”+password+"'";

The user input is as follows:

UserName input: or 1=1-
password input: 00000 (input whatever you want)

Then the original SQL statement becomes:

select * from user where username='' or 1=1--and password ='00000'
This statement is obviously always true
username='' or 1=1,--and password ='00000' is obviously one Annotation

prepareStatement object (preprocessing) can prevent SQL injection and is more efficient

//本质:将传递进来的参数当做字符,其中存在引号,则会转译
PrepareStatement pst = con.prepareStatement("....?,?,?");
pst.setInt(1,1); //第一1代表上面的第一个“?”,第二个1代表当前“?"的具体内容
pst.setString(2,""); //同上
pst.setData(3, java.sql.Data(new Data().getTime())); //同上
pst.executeQuery(); //执行查询操作
pst.executeUpdate(); //执行增、删、改操作

//释放资源.
con.close(); 
pst.close();



Four, affairs

effect:

The program is abnormal during the opening and committing of the transaction, then all SQL execution during this period will be rolled back

ACID principle:

Atomicity: Either all are completed or neither are completed
Consistency: The total number remains unchanged
Isolation: More non-interference with each other
Persistence: One single commit, no rollback


Isolation issues:

Dirty read: One transaction reads another uncommitted data.
Non-repeatable read: In a transaction, the data in the table is read repeatedly, and the table data has changed.
Virtual read (xuan read): In a transaction, read Data inserted by others, resulting in inconsistent data read before and after

example

------------------[Transfer]------------------

Prerequisite: Assuming that the con object has been connected

try{
	//1. 关闭自动提交,并开启事务
	con.setAutoCommit(false)

	2.其他SQL操作.....	

	//3.提交事务
	con.commit() 
}catch(Exception e){
	try{
		//4.回滚
		con.rollback();
	}catch(Exception e){
		e.printStackTrance();
	}
	
}finally{
	关闭资源
}


Five, database connection pool

Pooling technology:

Prepare some resources in advance and use them as you need them.
Exist [Maximum number of connections]
Existence [Minimum number of connections]
Existence [Waiting timeout]

Write connection pool:

Essentially is to implement the interface

Commonly used open source data sources

1. DBCP
2. C3P0
3. Druid (Alibaba)



[Use of DBCP] (Requires commons-dacp.jar and commons-pool.jar)

Insert picture description here



dbcp.properties file

########DBCP configuration file########## #driver
name
driverClassName=com.mysql.jdbc.Driver
#url
url=jdbc:mysql://local:3306/database name? useUnicode = to true & characterEncoding = UTF8 & useSSL = to true
# username
username = user
# password
password = 123456
# first test connections
initialSize = 50
# The maximum number of active
maxTotal = 35
# maximum idle
maxIdle = 20 is
# minimum idle number
minIdle = 10
# Max wait Time (milliseconds)
maxWaitMillis=1000 #Whether
the connection in the program is recycled by the connection pool after it is not used (this version needs to use removeAbandonedOnMaintenance and removeAbandonedOnBorrow)
#removeAbandoned=true
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true #The
connection is not used within the specified number of seconds Will be deleted (seconds) (only configured for 1 second to cooperate with the test program)
removeAbandonedTimeout=1


java code:

//1.通过类加载器进行获取properties文件流
InputStream  in = 共有类名.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
//2.创建Properties类
Properties properties = new Properties();
//3.加载流文件
properties.load(in);
//4.创建数据源(工程模式———>创建)
DataSource datasource = BasicDataSourceFactory.createDataSource(properties);
//5.创建连接
Connection = con datasource.getConnection()
//连接已经穿件好了,剩下的就是增删改查操作