1, PreparedStatement

1.1 SQL injection

Before sql execution, problems may arise if you splice the sql statement string and the filled data

When the user inputs special data with sql statements and splices it with the original sql, the execution results of the sql statements may be inconsistent with the expectations, resulting in program errors

1.2 prevent SQL injection

prepareStatement is provided in the precompiled object. You can write sql in the form of placeholders and replace the placeholders with corresponding data before executing sql, so as to prevent sql injection

Mode of use

1,Class.forName(DriverStr)Load driver
2,DriverManager(url,user,password)Get connection object
3,prepare sql take sql Use placeholders for data in? replace
4,Call connection object prepareStatement Method to get the precompiled object and sql Statement incoming
5,call PreparedStatement Precompiled object setXXX(index,obj)Method to fill in the specified data sql sentence
6,call PreparedStatementt Execution methods specific to precompiled objects(No need to pass in again sql)implement
7,Processing result set
8,Close connection and free resources 

		//Load driver
		Class.forName("com.mysql.jdbc.Driver");
		//Get connection
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
		//Prepare sql to use placeholders for the data to be filled in? replace
		//Note that placeholders can only be used to represent the data to be filled in, not columns (English characters)
		String sql="select * from account where username=? and password=?";
		//Create precompiled declaration object
		//The sql statement with placeholders needs to be passed in when creating the precompiled declaration object
		PreparedStatement ps = con.prepareStatement(sql);
		//For precompiled sql statements, you need to fill in data through the precompiled object method before execution
		//setXXX(index,obj)
		//XXX represents the data type of data to be filled in. Generally, Object will be filled in automatically according to the obj type
		//index represents the position of the placeholder, starting with 1
		//obj represents the filled data, which is used to replace the data of the placeholder
		ps.setObject(1, "zhangsan");//Assign data of type String to the first placeholder
		ps.setObject(2, "123456");
		//implement
		//The PreparedStatement object additionally provides the methods executeQuery and executeUpdate for execution
		//Previously, sql has been passed in and filled in through precompiling, so there is no need to fill in sql again
		//Otherwise, the re filled sql will be executed, resulting in the? Placeholder has no assignment, sql error
		ResultSet rs = ps.executeQuery();
		//Processing result set
		System.out.println(rs.next());
		//close
		rs.close();
		con.close();

2, Connection pool

2.1 concept

It is essentially a container that uses a pool to manage connections, so that connections can be reused. With a pool, we don't have to create a connection ourselves, but get the connection object through the pool. When Connection is used, the close() method that calls Connection will not really turn off Connection, but return the Connection to the pool. The pool can reuse the connection object.

2.2 connection pool specification

Java provides a common interface for database connection pool: javax sql. Datasource, each manufacturer needs to make its own connection pool implement this interface. In this way, the application can easily switch the connection pools of different manufacturers!

• Common database connection pools

  • DBCP

    DBCP is an open source connection pool and a member of Apache Common. It is common in early enterprise development and is rarely used now!

  • C3P0

    c3p0 is an open source JDBC connection pool. c3p0 has richer configuration attributes than DBCP

  • Druid

    Database connection pool implementation technology, provided by Alibaba. It is a database connection pool dedicated to Taobao and Alipay, supporting all JDBC compatible databases.

  Today, we will demonstrate two database connection pools, C3P0 and Druid, and focus on the use of Druid!

2.3 c3p0 connection pool usage

1,Import jar package(c3p0 You need to import two jar package)
2,Write configuration file(Baidu copy can modify the corresponding parameters according to its own needs)
3,Create database connection pool object
4,Call method to get connection
5,Close the connection after the operation and put it back into the connection pool

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- This is the default configuration information -->
	<default-config>
		<!-- Connect four parameter configurations -->
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">root</property>
		<property name="password">123456</property>
		<!-- Pool parameter configuration -->
		<!-- If the data connection in the pool is insufficient, how many data connections will be increased at a time -->
        <property name="acquireIncrement">5</property>
        <!-- The number of connections when initializing the database connection pool -->
        <property name="initialPoolSize">20</property>
        <!-- The maximum number of database connections in the database connection pool -->
        <property name="maxPoolSize">25</property>
        <!-- The minimum number of database connections in the database connection pool -->
        <property name="minPoolSize">5</property>
	</default-config>
</c3p0-config>

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Test {
	public static void main(String[] args) throws SQLException {
		/Create database connection pool object
		ComboPooledDataSource cds=new ComboPooledDataSource();
		//Get connections take connections from the connection pool
		Connection con = cds.getConnection();
		String sql="select * from account where username=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ResultSetMetaData metaData = ps.getMetaData();//Get metadata object
		//The database data that can be obtained when the request is wrapped in metadata
		int columnCount = metaData.getColumnCount();
		ps.setString(1, "lisi");
		ResultSet rs = ps.executeQuery();
		while(rs.next()){
			for (int i = 1; i <=columnCount; i++) {
				System.out.print(rs.getObject(i)+" ");
			}
			System.out.println();
		}

		//Close the connection and put the connection back into the connection pool
		con.close();
	}
}

2.4 use of Druid connection pool

1,Import jar package
2,Write configuration file(Baidu copy can modify the corresponding parameters according to its own needs)
3,The loading configuration file is obtained through the factory DruidDataSourceFactory Connection pool object
4,Call method to get connection
5,Close the connection after the operation and put it back into the connection pool

druid.properties

url=jdbc:mysql://localhost:3306/test
#This can be by default and will be automatically identified according to the url
driverClassName=com.mysql.jdbc.Driver
username=root
password=123456

##The number of initial connections is 0 by default
initialSize=10
#Maximum connections, 8 by default
maxActive=30
#Minimum idle number
minIdle=10
#Gets the maximum wait time for a connection, in milliseconds
maxWait=2000
#Cache PreparedStatement, default false
poolPreparedStatements=true
#The maximum number of cached preparedstatements. The default is - 1 (no caching). When it is greater than 0, the cache PreparedStatement will be automatically turned on, so the setting of the previous sentence can be omitted
maxOpenPreparedStatements=20

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

//Druid connection pool
public class DruidTest {
	public static void main(String[] args) throws Exception {
		//Load profile
		Properties p=new Properties();
		InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
		p.load(is);
	 
		//Create connection pool object
		DataSource druidDataSource =DruidDataSourceFactory.createDataSource(p);
		//Get connection object gets an unused connection from the connections created in the connection pool
		Connection con = druidDataSource.getConnection();
		//Prepare sql
		String sql="select * from student";
		
		//Create precompiled declaration object
		PreparedStatement ps = con.prepareStatement(sql);
		//Get the source data object get the number of query result columns
		ResultSetMetaData metaData = ps.getMetaData();
		int columnCount = metaData.getColumnCount();
		
		//Execute sql
		ResultSet rs = ps.executeQuery();
		
		//Processing result set
		while(rs.next()){
			for (int i = 1; i <=columnCount; i++) {
				System.out.print(rs.getObject(i)+" ");
			}
			System.out.println();
		}		
		//Close the connection and put the connection back into the connection pool
		rs.close();
		con.close();	
	}
}

3, Tool writing and use

AJDBCUtil.java

package com.yunhe.day0714;

import java.io.InputStream;
import java.lang.reflect.Field;
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.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

//jdbc operation tool class
public class AJDBCUtil {

	private static DataSource druidDataSource = null;// Connection pool object

	private AJDBCUtil() {
	}

	// Get connection
	// Get connections using Druid connection pool load configuration
	// The load configuration is executed in a static code block
	static {
		try {
			
			Properties p = new Properties();
			InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("druid.properties");
			p.load(is);
			// Create connection pool object
			druidDataSource = DruidDataSourceFactory.createDataSource(p);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// The essence of the method of obtaining a connection is to obtain a connection after obtaining a connection pool object using a connection pool factory object
	public static Connection getCon() {
		Connection con = null;
		try {
			con = druidDataSource.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}

	// Update operation method
	// Input parameters in the form of uncertain parameters
	public static int update(String sql, Object... obj) {
		// Get connection
		Connection con = getCon();
		int count=0;
		try {
			//Create a precompiled declaration object to precompile sql
			PreparedStatement ps = con.prepareStatement(sql);
			//Fill placeholder data in
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1, obj[i]);
			}
			//Execute sql
			count=ps.executeUpdate();
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return count;
	}

	
	//Methods to satisfy transaction operations
	public static int update(Connection con,String sql, Object... obj) throws SQLException {
		int count=0;
			//Turn off automatic commit of transactions
			con.setAutoCommit(false);
			//Create a precompiled declaration object to precompile sql
			PreparedStatement ps = con.prepareStatement(sql);
			//Fill placeholder data in
			for (int i = 0; i < obj.length; i++) {
				ps.setObject(i+1, obj[i]);
			}
			//Execute sql
			count=ps.executeUpdate();
		return count;
	}
	

	
	// Query operation method
	//Pass in sql. Pass in the class type of the class that saves the data. Pass in the sql parameter
	public static <E> ArrayList<E> query(String sql,Class<E> c,Object... obj){
		ArrayList<E> list=new ArrayList<>();
		new Thread(()->{
			//Get connection
			Connection con = getCon();
			try{
				//Create precompiled objects
				PreparedStatement ps = con.prepareStatement(sql);
				//Add data to
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i+1, obj[i]);
				}
				
				//Acquiring Metadata 
				ResultSetMetaData metaData = ps.getMetaData();
				//Get the number of query result columns
				int columnCount = metaData.getColumnCount();
				
				//Create a collection and save the result column name
				ArrayList<String> columnName=new ArrayList<>();
				for (int i = 1; i <=columnCount; i++) {
					String cName = metaData.getColumnName(i);
					columnName.add(cName);
				} 
				//Execute sql
				ResultSet rs = ps.executeQuery();
				//Processing result set
				while(rs.next()){
					//create object
					E e=c.newInstance();
					 
					//Traverse the column names and operate separately
					for (String name : columnName) {
						//Get data by column name
						Object value = rs.getObject(name);
						//Gets the specified property object of the current class through reflection
						Field nameField = c.getDeclaredField(name);
						nameField.setAccessible(true);
						//Add data to
						nameField.set(e, value);
					}
					//Storing objects into collections
					list.add(e);
				}
				
				
			}catch (Exception e) {
				 e.printStackTrace();
			}finally {
				try {
					if(con!=null){
						con.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}).start();
		return list;
	}
}

use

		//Composition and use of custom tool classes
		
		//Preparation before using tool class
		//druid connection pool is a jar package that connects to the database
		//druid connection pool profile
		//The class corresponding to the query database column name and property name
		
		//AJDBCUtil.getCon();
		//Get connection method
		//The druid connection pool is used for connection configuration, and the idle connections in the connection pool can be obtained directly through this method
		
		
		//AJDBCUtil.update(sql, obj);
		//Execute the method of adding, deleting and modifying sql statements, and fill in the sql statements with placeholders and corresponding parameters
		//Internal call to get the connection method precompiles the parameter data with sql through the uncertain parameter array
		//Returns the number of data affected after successful sql execution
		
		//AJDBCUtil.update(con, sql, obj);
		//The method of adding, deleting and modifying sql statements can realize transaction operations by obtaining connection methods
		//Prevent direct sql execution by setting the connection to close automatic submission
		//Transaction execution of multiple sql statements through corresponding con connection objects
		//After successful execution, the caller needs to manually execute commit and rollback to commit and rollback
		//Finally, close the connection ()
		
		//AJDBCUtil.query(sql, c, obj);
		//Execute the query statement and return the collection of classes represented by the class object according to the passed in class object
		//Get column names in query results through metadata
		//Execute sql processing to obtain the data corresponding to the column name by traversing the collection of column names in the result set object
		//Get the corresponding properties of the object represented by the current class class through reflection
		//Create the object of the corresponding class representative class through generic declaration variable reflection
		//Assign the data of the specified attribute to the corresponding object through the reflection method
		//Adding objects to the collection in turn returns