1 summary

stay Java in , Database access technology can be divided into the following categories ：

• JDBC Direct access to database
• JDO technology （Java Data Object）
• The third party O/R Tools , Such as Hibernate, Mybatis etc.
  JDBC yes java The cornerstone of access to the database ,JDO, Hibernate It's just a better package JDBC.

1.1 What is? JDBC

JDBC(Java Database Connectivity) It's a Independent of a specific database management system （DBMS）、 General purpose SQL A common interface for database access and operation （ A group of API）, Defines the criteria for accessing databases Java Class library , You can use this library in a standard way 、 Easy access to database resources
JDBC Provides a way to access different databases A unified approach , Some details are blocked for developers .
JDBC The goal is to make Java Programmers use JDBC You can connect to any Provides JDBC The driver Database system of , In this way, programmers don't need to know too much about the characteristics of a specific database system , This greatly simplifies and speeds up the development process .

2 JDBC Programming steps

• Registration drive
• Get the connection
• Add, delete, modify and check
• Release resources

2.1 Demonstrate complete steps

preparation ： introduce JDBC The driver

The driver is provided and downloaded by the database provider . MySQL Driver download address ：http://dev.mysql.com/downloads/
Click here Connector/J→ choice Platform Independent→ download .zip file → Download good zip Inside jar Yes jar

• （1） hold mysql-connector-java-8.0.21.jar Copy to project lib Directory
  

• （2） Add to the classpath of the project
  Driving jar Right click –>Build Path–>Add to Build Path
  

Be careful ： If it is Dynamic Web Project（ Dynamic web project ） word , It's driving jar Put it in WebContent（ Some development tools are called WebRoot） In the directory WEB-INF In the directory lib Under the directory

（ One ） Load and register the driver

Load and register the driver ：
The load driver , Load the driver class into memory
Registration drive , Give the object that drives the class to DriverManager management , Used to create connections later .

• 1、Class.forName( )

DriverManager.registerDriver(new Driver());// This is the old method 
Class.forName("com.mysql.cj.jdbc.Driver");// It's a new way 

because Driver Interface driver classes contain static code blocks , In this static block of code , Would call DriverManager.registerDriver() Method to register an instance of itself , So you can load the driver in another way .（ That is, as long as you find a way to make this static code block of the driver class execute, you can register the driver class , And let this static code block execute , Just let the class be loaded by the class loader ）

call Class Class static methods forName(), Pass it the... To be loaded JDBC The class name of the driver
// By reflection , Loading and registering driver classes , Decoupling （ Don't rely directly on ）
Class.forName(“com.mysql.jdbc.Driver”);

• 2、 Service provider framework （ for example ：JDBC Driver program ） Automatic registration （ There are version requirements ）
  accord with JDBC 4.0 The standard driver contains a file META-INF/services/java.sql.Driver, In this file JDBC The class name of the driver implementation . for example ：mysql-connector-java-5.1.40-bin.jar You can find in the file java.sql.Driver file , Open the file with a text editor and you can see ：com.mysql.jdbc.Driver class .
  
  JVM The service provider framework will register the service when the application is started , for example ：MySQL Of JDBC The driver will be registered , And in the original code Class.forName(“com.mysql.jdbc.Driver”) It can still exist , But it won't work .
  But notice mysql-connector-java-5.0.8-bin.jar Version of jar There is no , as follows
  

（ Two ） Get database links

Can pass DriverManager Class to establish a connection to the database Connection：
DriverManager Attempted from registered JDBC Select an appropriate driver in the driver set .

-	public static Connection getConnection(String url) 
-	public static Connection getConnection(String url,String user, String password)
-	public static Connection getConnection(String url,Properties info) among Properties info Usually it should include at least  "user"  and  "password"  attribute 

for example ：

Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/girls?serverTimezone=UTC","root","root");

JDBC URL Used to identify a registered driver , Driver manager through this URL Choose the right driver , To establish a connection to the database .JDBC URL There are three parts to the standard , The parts are separated by colons .
jdbc:< Sub protocol >:< Child name >

• agreement ：JDBC URL The agreement in is always jdbc
• Sub protocol ： The sub protocol is used to identify a database driver
• Child name ： A method of identifying database . Sub names can be changed according to different sub protocols , The purpose of using subnames is to locate the database and provide enough information
  for example ：
  
  MySQL The connection of URL Compiling mode ：

jdbc:mysql:// Host name :mysql Service port number / Database name ? Parameters = value & Parameters = value 
jdbc:mysql://localhost:3306/testdb
jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf8（ If JDBC The character set of the program and the server is inconsistent , It will lead to confusion , Then you can specify the server-side character set through parameters ）
jdbc:mysql://localhost:3306/testdb?user=root&password=123456
"jdbc:mysql://localhost:3306/girls?serverTimezone=UTC","root","root"

MySQL The connection of URL Utilization of properties file
stay src Next create a new file ：jdbc.properties

（ 3、 ... and ） Operate or access a database

Database connections are used to send commands and SQL sentence , And accept the results returned by the database server .
In fact, a database connection is a Socket Connect .
stay java.sql There is... In the bag 3 Each interface defines different ways to call the database ：

• Statement： For execution static SQL Statement and returns the object for which it generated the result .
• PrepatedStatement：SQL Statements are precompiled and stored in this object , The statement can then be executed efficiently multiple times using this object .
• CallableStatement： Used to perform SQL stored procedure

3.1Statement

By calling Connection Object's createStatement() Method to create the object
This object is used to perform static SQL sentence , And return the execution result
Statement The following methods are defined in the interface to execute SQL sentence ：
- int excuteUpdate(String sql)： Perform an update operation INSERT、UPDATE、DELETE, The return value is the number of rows changed
- ResultSet excuteQuery(String sql)： Perform query operation SELECT

executeUpdate

package mysql;

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

import com.mysql.jdbc.Driver;

public class sqltest {
    

	public static void main(String[] args) throws SQLException, ClassNotFoundException {
    
		//1  The load driver 
		//DriverManager.registerDriver(new Driver());
		Class.forName("com.mysql.cj.jdbc.Driver");
		
		//2. Get the connection 
		Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/girls?serverTimezone=UTC","root","root");
		
		//3. Add, delete, modify and check 
		
		//3-1  To write sql sentence 
		String sql="UPDATE boys SET boyName=' Lubumbashi wei ' where id=1";
		
		//3-2  Access to perform sql The execution object of the statement 
		Statement statement= connection.createStatement();
		
		//3-3  Use the command object to point to sql sentence 
		int update=statement.executeUpdate(sql);// This sentence can only be added, deleted or modified , Return the number of affected rows 
		
		
		//3-4 Process execution results 
		System.out.println(update>0?"yes":"no");
		
		//4  Close the connection , Open later and close first , symmetry 
		statement.close();
		connection.close();
	}

}

ResultSet

By calling Statement Object's excuteQuery() Method to create the object
ResultSet Object encapsulates the result set of database operations in the form of logical tables ,ResultSet The interface is implemented by the database manufacturer
ResultSet Object maintains a cursor to the current row of data , In the beginning , The cursor is one line before the first line , Can pass ResultSet Object's next() Method moves to the next line
ResultSet Common methods of interface ：

• boolean next()
• getXxx(String columnLabel)：columnLabel Use SQL AS Column label specified by clause . If not specified SQL AS Clause , Then the label is the column name
• getXxx(int index) : Index from 1 Start
  

package mysql;

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

import com.mysql.jdbc.Driver;

public class sqltest {
    

	public static void main(String[] args) throws SQLException, ClassNotFoundException {
    
		//1  The load driver 
		//DriverManager.registerDriver(new Driver());
		Class.forName("com.mysql.cj.jdbc.Driver");
		
		//2. Get the connection 
		Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/girls?serverTimezone=UTC","root","root");
		
		//3. Add, delete, modify and check 
		
		//3-1  To write sql sentence 
		String sql="select id,name,sex,borndate from beauty";
		
		//3-2  Access to perform sql The execution object of the statement 
		Statement statement= connection.createStatement();
		
		//3-3  Use the command object to point to sql sentence 
		ResultSet set=statement.executeQuery(sql);
		
		boolean flag= set.next();
		int id = set.getInt("id");
		String name = set.getString("name");
		String sex = set.getString("sex");
		Date date = set.getDate("borndate");
		
		System.out.println(id+"\t"+name+"\t"+sex+"\t"+date);
		
		//4  Close the connection , Open later and close first , symmetry 
		set.close();
		statement.close();
		connection.close();
	}

}

3.2 PrepatedStatement

You can call Connection Object's preparedStatement(String sql) Method to get PreparedStatement object
PreparedStatement Interface is Statement Sub interface of , It represents a precompiled SQL sentence

• PreparedStatement The object represents SQL The parameters in the statement are marked with question marks (?) To express , call PreparedStatement Object's setXxx() Method to set these parameters . setXxx() Method has two parameters , The first parameter is to be set SQL The index of the parameter in the statement ( from 1 Start ), The second one is set up SQL The value of the parameter in the statement
• ResultSet executeQuery() Execute the query , And return the query generated ResultSet object .
• int executeUpdate()： Perform the update , Including increase 、 Delete 、 Change

↑ To verify the account password will lead to sql Injection problem , Because if two parameters contain ’ " Equisign , There will be grammatical errors


↑ You can avoid sql Injection problem

2.2 encapsulation JDBCUtils.java

Because many steps are repeated , So you can encapsulate repeated operations

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/** *  This class is encapsulated JDBC Connected tool class  * *  function ： * 1、 Get the connection  * 2、 Release resources  * */
public class JDBCUtils {
    
	static String user;
	static String password;
	static String url;
	static String driver;
	
	static{
    
		try {
    
			Properties info = new Properties();
			info.load(new FileInputStream("src\\jdbc.properties"));
			 user = info.getProperty("user");
			 password = info.getProperty("password");
			 url = info.getProperty("url");
			 driver = info.getProperty("driver");
			//1. Registration drive 
			Class.forName(driver);
		} catch (Exception e) {
    
			throw new RuntimeException(e);
		}
	}
	/** *  function ： Get available connection objects  * @return  Connect  * @throws Exception */
	
	public static Connection getConnection(){
    
		
		try {
    
			return DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
    
			throw new RuntimeException(e);
		}
		
	}
	
	/** *  function ： Release resources  * @param set * @param statement * @param connection * @throws Exception */
	public static void close(ResultSet set,Statement statement,Connection connection){
    
		try {
    
			if (set!=null) {
    
				set.close();// If it's a query operation , Need to be closed set, Otherwise, a message will be sent here null
			}
			if (statement!=null) {
    
				statement.close();
			}
			if (connection!=null) {
    
				connection.close();
			}
		} catch (SQLException e) {
    
			throw new RuntimeException(e);
		}
		
		
	}
	
	

}

Here are the complete steps

3 JDBC Business

package com.atguigu.jdbc2;

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

import org.junit.Test;

import com.atguigu.jdbc1.JDBCUtils;

/** *  This class is used to demonstrate JDBC The transaction  * @author liyuting * *  Use steps ： * 1、 Start a new business  *  Cancels the automatic commit of implicit transactions  * setAutoCommit(false); * 2、 Write a group that makes up a transaction sql sentence  * * 3、 End the business  * commit(); Submit  * rollback(); Roll back  * *  details ： *  It is required that the connection object to open the transaction and the connection object to obtain the command must be the same ！ Otherwise, the transaction is invalid  * * * * * *  Case study ： Transfer case  *  Zhang Sanfeng turned to extinction 5000 * * */
public class TestTransaction {
    
	// No business 
	@Test
	public void testNoTransaction() throws Exception{
    
		//1. Get the connection 
		Connection connection = JDBCUtils.getConnection();
		
		//2. perform sql sentence 
		PreparedStatement statement = connection.prepareStatement("update account set balance = ? where stuname=?");
		
		// operation 1： Zhang Sanfeng's money -5000
		
		statement.setDouble(1, 5000);
		statement.setString(2, " Zhang Sanfeng ");
		
		statement.executeUpdate();
		
		int i = 1/0;// Simulate anomalies 
		
		// operation 2： Zhang Sanfeng's money -5000
		
		statement.setDouble(1, 15000);
		statement.setString(2, " Extinction teacher ");
		
		statement.executeUpdate();
		
		
		//3. Release resources 
		
		JDBCUtils.close(null, statement, connection);
		
		
		
	}

	// With a transaction 
	@Test
	public void testTransaction(){
    
		Connection connection  = null;
		PreparedStatement statement = null;
		
		try {
    
			//1. Get the connection 
			 connection = JDBCUtils.getConnection();
			//① Use steps of transaction 1： Open transaction , It means to cancel each statement automatically commit
			
			connection.setAutoCommit(false);
			
			//② Use steps of transaction 2： To write sql sentence , And perform 
			
			 statement = connection.prepareStatement("update account set balance = ? where stuname=?");
			// operation 1： Zhang Sanfeng's money -5000
			
			statement.setDouble(1, 5000);
			statement.setString(2, " Zhang Sanfeng ");
			
			statement.executeUpdate();
			
// int i = 1/0;// Simulate anomalies 
			
			// operation 2： Zhang Sanfeng's money -5000
			
			statement.setDouble(1, 15000);
			statement.setString(2, " Extinction teacher ");
			
			statement.executeUpdate();
			
			
			//③ Use steps of transaction 3： End the business 
			
			connection.commit();
		} catch (SQLException e) {
    
			try {
    
				// Roll back the transaction 
				connection.rollback();
			} catch (SQLException e1) {
    
				e1.printStackTrace();
			}
		}finally{
    
			JDBCUtils.close(null, statement, connection);
		}
		
	}
	
}

4 The batch

When you need to insert or update records in batches . May adopt Java Batch update mechanism of , This mechanism allows multiple statements to be submitted to the database for batch processing at one time . In general, it's more efficient than a single commit process .
JDBC The following two methods are included in the batch processing statement of ：

• addBatch()： Add... That needs batch processing SQL Statement or argument
• executeBatch()： Execute batch statements ;
• clearBatch(): Statement to empty the batch package
  There are two cases ：

1. multiple SQL Batch processing of statements ;
   
2. One SQL Batch transfer of statements ;
   

5 Blob Type data

MySQL in ,BLOB It's a binary large object , Is a container that can store a lot of data , It can hold data of different sizes . Can save
MySQL Four kinds of BLOB type ( Except in terms of the maximum amount of information stored , They are the same )
In actual use, different data are defined according to the data size to be stored BLOB type .
It should be noted that ： If the stored file is too large , Database performance will degrade

package com.atguigu.jdbc2;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.atguigu.jdbc1.JDBCUtils;

/** *  This class is used to demonstrate Blob Access to type data  * @author liyuting *  relevant API： * * setBlob( Placeholder index ,InputStream object ) * getBlob( Column index or column name ) * getBinaryStream( Column index or column name ) * */
public class TestBlob {
    

	// Save pictures 
	@Test
	public void testSave() throws SQLException, Exception{
    
		
		//1. Get the connection 
		Connection connection = JDBCUtils.getConnection();
		
		//2. Execute the modify statement 
		PreparedStatement statement = connection.prepareStatement("update beauty set photo=? where id = 1");
		
		statement.setBlob(1, new FileInputStream("src\\6.jpg"));
		int update = statement.executeUpdate();
		
		
		//3. Close the connection 
		JDBCUtils.close(null, statement, connection);
	}
	
	// Read pictures 
		@Test
		public void testRead() throws SQLException, Exception{
    
			
		//1. Get the connection 
		Connection connection = JDBCUtils.getConnection();
		
		//2. Execute the modify statement 
		PreparedStatement statement = connection.prepareStatement("select photo from beauty where id = 1");
		ResultSet set = statement.executeQuery();
		if(set.next()){
    
			// The way 1：
// Blob blob = set.getBlob("photo");
// InputStream binaryStream = blob.getBinaryStream();
			
			// The way 2：
			InputStream inputStream = set.getBinaryStream("photo");
			
			FileOutputStream fos = new FileOutputStream("src\\beauty.jpg");
			
			int len;
			byte[] b = new byte[1024];
			while((len=inputStream.read(b))!=-1){
    
				fos.write(b,0,len);
			}
			
			fos.close();
			inputStream.close();
		}
		
		
		
		//3. Close the connection 
		JDBCUtils.close(null, statement, connection);
		}
}

6 Database connection pool

（1） The need for database connection pooling
Problems with not using database connection pool :

• ordinary JDBC Database connection use DriverManager To get , Every time you establish a connection to the database, you need to Connection Load into memory , Revalidation IP Address , User name and password ( It costs 0.05s～1s Time for ). When you need a database connection , Just ask the database for a , Disconnect after execution . This way will consume a lot of resources and time . The connection resources of the database are not well reused . If there are hundreds or even thousands of people online at the same time , Frequent database connection operations will take up a lot of system resources , Serious even can cause the server to crash .
• For every database connection , They have to be disconnected after use . otherwise , If the program fails to close due to an exception , Will cause memory leaks in the database system , It will eventually lead to a restart of the database .
• This development does not control the number of connection objects created , System resources will be allocated without consideration , If there are too many connections , It can also cause memory leaks , Server crash .
  To solve the problem of database connection in traditional development , Database connection pool technology can be used （connection pool）.

The basic idea of database connection pool is to establish a database connection “ Buffer pool ”. Put a certain number of connections in the buffer pool in advance , When you need to establish a database connection , Just from “ Buffer pool ” Take out one of , Put it back after use . The database connection pool is responsible for allocation 、 Manage and release database connections , It allows applications to reuse an existing database connection , Instead of re establishing a . The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can hold , When the number of connections the application requests from the connection pool exceeds the maximum number of connections , These requests will be added to the waiting queue .

Advantages of database connection pool technology ：

• Resource reuse ：
  • Because database connections can be reused , Avoid frequent creation of , A lot of performance overhead caused by releasing connections . On the basis of reducing system consumption , On the other hand, it also increases the stability of the system operation environment .
• Faster system response
  • The database connection pool is in the process of initialization , Often a number of database connections have been created for standby in the connection pool . At this time, the initialization of the connection is completed . For business request processing , Take advantage of the available connections directly , Avoid the time cost of database connection initialization and release , Thus, the response time of the system is reduced
• New means of resource allocation
  • For systems where multiple applications share the same database , It can be configured in the application layer through the database connection pool , Limit of the maximum number of available database connections for an application , Avoid an application monopolizing all database resources
• Unified connection management , Avoid database connection leaks
  • In a more complete database connection pool implementation , It can be set according to the pre occupation timeout , Force recycling of occupied connections , In this way, the possible resource leakage in the normal database connection operation is avoided

（2） A variety of open source database connection pools
JDBC The database connection pool is used javax.sql.DataSource To express ,DataSource It's just an interface , The interface is usually provided by the server (Weblogic, WebSphere, Tomcat) Provide implementation , There are also open source organizations that provide implementations ：

• DBCP yes Apache Database connection pool provided , Speed is relative to c3p0 Faster , But because of its own existence BUG,Hibernate3 Support is no longer available
• C3P0 It is a database connection pool provided by an open source organization , Relatively slow , Stability is OK
• Proxool yes sourceforge The next open source project database connection pool , It has the function of monitoring connection pool status , More stable c3p0 almost
• BoneCP Is a database connection pool provided by an open source organization , Fast
• Druid It is the database connection pool provided by Ali , It's said to be a collection DBCP 、C3P0 、Proxool All in one database connection pool , But the speed doesn't know if there is BoneCP fast
  DataSource Often referred to as a data source , It includes connection pool and connection pool management , It's customary to put DataSource It's called a connection pool
  Be careful ：
• Different data source and database connection , Data sources do not need to create multiple , It's the factory that makes the database connection , So the whole application only needs one data source .
• When the database access is over , The program still closes the database connection as before ：conn.close(); but conn.close() The physical connection to the database is not closed , It just releases the database connection , Returned to database connection pool .
  （3）Druid（ Druid ） data source
  Druid It is a database connection pool on Alibaba open source platform , It is a combination of C3P0、DBCP、Proxool etc. DB The advantages of the pool , At the same time, log monitoring is added , It's a good monitor DB Pool connection and SQL Implementation of , It can be said that it was born for monitoring DB Connection pool , It is said to be the best connection pool at present .

1. Mode one

package com.atguigu.druid;

import java.sql.Connection;

import com.alibaba.druid.pool.DruidDataSource;

public class TestDruid {
    
	public static void main(String[] args) throws Exception {
    
		DruidDataSource ds = new DruidDataSource();
		ds.setUrl("jdbc:mysql://localhost:3306/0319db");
		ds.setUsername("root");
		ds.setPassword("123456");
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		
		ds.setInitialSize(10);// The default value is 0, belong maxActive and minIdle Between 
		ds.setMaxActive(20);// The default value is 8
		ds.setMinIdle(1);// The default value is 0
		//maxIdle yes Druid For convenience DBCP Increased by user migration ,maxIdle It's a confusing concept . The connection pool should only have maxPoolSize and minPoolSize,druid Just keep maxActive and minIdle, They are equivalent to maxPoolSize and minPoolSize.
		ds.setMaxIdle(5);
		
		//  Maximum wait time when getting a connection , Unit millisecond . Configured with maxWait after , Fair lock is enabled by default , Concurrency efficiency will decrease , If necessary, it can be configured through useUnfairLock The attribute is true Use unfair locks .
		ds.setMaxWait(1000);
		
		// Configure how often to test , Detects idle connections that need to be closed , In milliseconds 
		ds.setTimeBetweenEvictionRunsMillis(60000);
		
		// Configure the minimum lifetime of a connection in the pool , In milliseconds 
		ds.setMinEvictableIdleTimeMillis(300000);
		
		// Whether the cache preparedStatement, That is to say PSCache.PSCache Great improvement in database performance supporting cursors , for instance oracle. stay mysql The next suggestion is to close .
		ds.setPoolPreparedStatements(true);
		//  To enable the PSCache, Must be configured greater than 0, When more than 0 when ,poolPreparedStatements Auto trigger changed to true. stay Druid in , No existence Oracle Next PSCache The problem of using too much memory , You can configure this value to be larger , for instance 100
		ds.setMaxPoolPreparedStatementPerConnectionSize(10);
		// Configure multiple English commas to separate 
		// Configure the extension by alias , Common plug-ins are ：  For monitoring statistics filter:stat It's for the log filter:log4j defense sql Injected filter:wall
		ds.setFilters("stat,wall");
		
		Connection conn = ds.getConnection();
		System.out.println(conn);
	}
}

2. Mode two , Use properties file

package com.atguigu.utils;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

/** *  This class obtains connection objects through the Druid database connection pool  * @author liyuting * */
public class JDBCUtilsByDruid {
    
	static DataSource ds;
	static{
    
		try {
    
			Properties properties = new Properties();
			properties.load(new FileInputStream("src\\druid.properties"));
			
			//1. Create a database connection pool with specified parameters 
			 ds = DruidDataSourceFactory.createDataSource(properties);
		} catch (Exception e) {
    
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws Exception{
    
		//2. Get the available connection objects from the database connection pool 
		return ds.getConnection();
		
	}
	/** *  function ： Release resources  * @param set * @param statement * @param connection * @throws Exception */
	public static void close(ResultSet set,Statement statement,Connection connection){
    
		try {
    
			if (set!=null) {
    
				set.close();
			}
			if (statement!=null) {
    
				statement.close();
			}
			if (connection!=null) {
    
				connection.close();
			}
		} catch (SQLException e) {
    
			throw new RuntimeException(e);
		}
		
		
	}
}

properties file

url=jdbc:mysql://localhost:3306/0319db
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000
filters=wall

To configure	default	explain
name		The point of configuring this property is , If there are multiple data sources , Monitoring can be distinguished by name . If not configured , Will generate a name , The format is ：”DataSource-” + System.identityHashCode(this)
jdbcUrl		Connected to the database url, Different databases are different . for example ：mysql : jdbc:mysql://10.20.153.104:3306/druid2 oracle : jdbc:oracle:thin:@10.20.149.85:1521:ocnauto
username		User name to connect to the database
password		Password to connect to the database . If you don't want the password written directly in the configuration file , have access to ConfigFilter. Look here in detail ：https://github.com/alibaba/druid/wiki/%E4%BD%BF%E7%94%A8ConfigFilter
driverClassName		according to url Automatic identification This item can be matched or not , If you don't configure druid Will be based on url Automatic identification dbType, Then choose the appropriate driverClassName( It is recommended to configure )
initialSize	0	Number of physical connections established during initialization . Initialization occurs in the display call init Method , Or for the first time getConnection when
maxActive	8	Maximum number of connection pools
maxIdle	8	No longer in use , Configuration has no effect
minIdle		Minimum number of connection pools
maxWait		Maximum wait time when getting a connection , Unit millisecond . Configured with maxWait after , Fair lock is enabled by default , Concurrency efficiency will decrease , If necessary, it can be configured through useUnfairLock The attribute is true Use unfair locks .
poolPreparedStatements	false	Whether the cache preparedStatement, That is to say PSCache.PSCache Great improvement in database performance supporting cursors , for instance oracle. stay mysql The next suggestion is to close .
maxOpenPreparedStatements		-1
validationQuery		Used to check whether the connection is valid sql, The requirement is a query statement . If validationQuery by null,testOnBorrow、testOnReturn、testWhileIdle It doesn't work .
testOnBorrow	true	Execute on connection request validationQuery Check whether the connection is valid , This configuration will degrade performance .
testOnReturn	false	Execute... When returning the connection validationQuery Check whether the connection is valid , This configuration will degrade performance
testWhileIdle	false	Recommended configuration is true, No performance impact , And ensure safety . Check when applying for connection , If the free time is greater than timeBetweenEvictionRunsMillis, perform validationQuery Check whether the connection is valid .
timeBetweenEvictionRunsMillis		There are two meanings ： 1)Destroy The thread will detect the connection interval 2)testWhileIdle On the basis of , See in detail testWhileIdle Description of the property
numTestsPerEvictionRun		No longer use , One DruidDataSource Only one... Is supported EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls		When the physical connection is initialized sql
exceptionSorter		according to dbType Automatic identification When the database throws some unrecoverable exceptions , Abandon the connection
filters		Property type is string , Configure the extension by alias , Common plug-ins are ： For monitoring statistics filter:stat It's for the log filter:log4j defense sql Injected filter:wall
proxyFilters		The type is List, If it is configured at the same time filters and proxyFilters, It's a combination relationship , It's not a replacement relationship

7 utilize Druid Of CRUD encapsulation

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.atguigu.bean.Boys;

/** *  This class is used to encapsulate the general addition, deletion, modification and query methods  * @author liyuting *  function ： * 1、 Carry out addition, deletion and modification  * 2、 Execute the query  * * */
public class CRUDUtils {
    
	/** *  function ： Additions and deletions  *  Any addition, deletion and modification statements for any table  * @return * @throws Exception */
	public static int update(String sql,Object...params){
    
		
		try {
    
			//1. Get the connection 
			Connection connection = JDBCUtilsByDruid.getConnection();
			
			
			//2. perform sql sentence 
			PreparedStatement statement = connection.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
    
				statement.setObject(i+1, params[i]);
			}
			
			int update = statement.executeUpdate();
			return update;
		}  catch (Exception e) {
    
			throw new RuntimeException(e);
		}
		
	}
	/** * orm：object relation mapping * @param sql * @param params * @return * *  Only aim at Boys surface , Check the list  * @throws Exception */
	public static Boys querySingle(String sql,Object...params) throws Exception{
    
		Connection connection=null;
		PreparedStatement statement=null;
		ResultSet set = null;
		try {
    
			//1. Get the connection 
			 connection = JDBCUtilsByDruid.getConnection();
			
			//2. Execute the query 
			 statement = connection.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
    
				statement.setObject(i+1, params[i]);
			}
			 set = statement.executeQuery();
			
			if(set.next()){
    
				int id = set.getInt("id");
				String boyName = set.getString("boyname");
				int userCP = set.getInt("userCP");
				Boys bo = new Boys(id,boyName,userCP);
				return bo;
			}
			return null;
		} catch (Exception e) {
    
			throw new RuntimeException(e);
		}finally{
    
			JDBCUtilsByDruid.close(set, statement, connection);
		}
		
		
	}
	/** * orm：object relation mapping * @param sql * @param params * @return * *  Only aim at Boys surface , Query multiple  * @throws Exception */
	public static List<Boys> queryMulti(String sql,Object...params) throws Exception{
    
		Connection connection=null;
		PreparedStatement statement=null;
		ResultSet set = null;
		try {
    
			//1. Get the connection 
			 connection = JDBCUtilsByDruid.getConnection();
			
			//2. Execute the query 
			 statement = connection.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
    
				statement.setObject(i+1, params[i]);
			}
			 set = statement.executeQuery();
			List<Boys> list = new ArrayList<>();
			while(set.next()){
    
				int id = set.getInt("id");
				String boyName = set.getString("boyname");
				int userCP = set.getInt("userCP");
				Boys bo = new Boys(id,boyName,userCP);
				list.add(bo);
			}
			return list;
		} catch (Exception e) {
    
			throw new RuntimeException(e);
		}finally{
    
			JDBCUtilsByDruid.close(set, statement, connection);
		}
		
		
	}
	
}

boys class

package com.atguigu.bean;

public class Boys {
    

	private int id;
	private String boyName;
	private int userCP;
	
	public Boys() {
    
		super();
	}
	@Override
	public String toString() {
    
		return "Boys [id=" + id + ", boyName=" + boyName + ", userCP=" + userCP + "]";
	}
	public int getId() {
    
		return id;
	}
	public void setId(int id) {
    
		this.id = id;
	}
	public String getBoyName() {
    
		return boyName;
	}
	public void setBoyName(String boyName) {
    
		this.boyName = boyName;
	}
	public int getUserCP() {
    
		return userCP;
	}
	public void setUserCP(int userCP) {
    
		this.userCP = userCP;
	}
	public Boys(int id, String boyName, int userCP) {
    
		super();
		this.id = id;
		this.boyName = boyName;
		this.userCP = userCP;
	}
	
	
	
}

Call the instance

package com.atguigu.utils;

import java.util.List;

import org.junit.Test;

import com.atguigu.bean.Boys;

public class TestCRUDUtils {
    
	@Test
	public void testUpdate(){
    
		
// int update = CRUDUtils.update("update beauty set sex = ? where name=' Liu Yan '", " male ");
// 
// System.out.println(update>0?"success":"failure");
		
		
		int update = CRUDUtils.update("delete from admin where id>5");
		
		System.out.println(update>0?"success":"failure");
	}
	@Test
	public void testQuery() throws Exception{
    
// Boys boy = CRUDUtils.querySingle("select * from boys where id = ?", 2);
// System.out.println(boy);
		
		List<Boys> list = CRUDUtils.queryMulti("select * from boys");
		for (Boys boys : list) {
    
			System.out.println(boys);
		}
		
	}

}

8 DBUtils

commons-dbutils yes Apache An open source provided by the organization JDBC Tool library , It's right JDBC Simple encapsulation , The cost of learning is extremely low , And use dbutils Can greatly simplify jdbc The amount of coding work , At the same time, it will not affect the performance of the program .
1、DbUtils class
DbUtils ： Provide such as closing a connection 、 load JDBC Driver and other routine tools , All the methods in it are static . The main methods are as follows ：

• public static void close(…) throws java.sql.SQLException：　DbUtils Class provides three overloaded closing methods . These methods check whether the parameters provided are NULL, If not , They shut down Connection、Statement and ResultSet.
• public static void closeQuietly(…): This kind of method can not only be used in Connection、Statement and ResultSet by NULL Avoid shutting down , You can also hide some of the things that are thrown out of the program SQLEeception.
• public static void commitAndClose(Connection conn)throws SQLException The transaction used to commit the connection , Then close the connection
• public static void commitAndCloseQuietly(Connection conn)： The transaction used to commit the connection , Then close the connection , And don't throw out when closing the connection SQL abnormal .
• public static void rollback(Connection conn)throws SQLException allow conn by null, Because there is a judgment inside the method
• public static void rollbackAndClose(Connection conn)throws SQLException
• rollbackAndCloseQuietly(Connection)
• public static boolean loadDriver(java.lang.String driverClassName)： This party loads and registers JDBC The driver , If you succeed, go back true. Using this method , You don't need to catch this exception ClassNotFoundException.

2、QueryRunner class
This class encapsulates SQL Implementation , It's thread safe .
（1） It can increase 、 Delete 、 Change 、 check 、 The batch 、
（2） Consider that transaction processing needs to share Connection.
（3） The main thing about this class is to simplify SQL Inquire about , It is associated with ResultSetHandler Together, you can do most of the database operations , Can greatly reduce the amount of coding .
QueryRunner Class provides two constructors ：

• QueryRunner()： Default constructor
• QueryRunner(DataSource ds)： Need one javax.sql.DataSource To construct parameters .
  （1） to update
• public int update(Connection conn, String sql, Object… params) throws SQLException: Used to perform an update （ Insert 、 Update or delete ） operation .
  （2） Insert
• public T insert(Connection conn,String sql,ResultSetHandler rsh, Object… params) throws SQLException： Only support INSERT sentence , among rsh - The handler used to create the result object from the ResultSet of auto-generated keys. Return value : An object generated by the handler. Automatically generated key value
  （3） The batch
• public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException： INSERT, UPDATE, or DELETE sentence
• public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException： Only support INSERT sentence
  （4） Use QueryRunner Class to implement the query
• public Object query(Connection conn, String sql, ResultSetHandler rsh,Object… params) throws SQLException： Perform a query operation , In this query , The value of each element in the object array is used as the replacement parameter of the query statement . The method will handle itself PreparedStatement and ResultSet Create and close .
  3、ResultSetHandler Interface
  This interface is used for processing java.sql.ResultSet, Transform data into another form as required .ResultSetHandler Interface provides a separate method ：Object handle (java.sql.ResultSet rs) The return value of this method will be used as QueryRunner Class query() Return value of method .
  The interface has the following implementation classes, which can be used ：
• ArrayHandler： Convert the first row of data in the result set to an object array .
• ArrayListHandler： Turn each row of data in the result set into an array , Store it in List in .
• BeanHandler： Encapsulate the first row of data in the result set into a corresponding JavaBean In the example .
• BeanListHandler： Encapsulate each row of data in the result set into a corresponding JavaBean In the example , Store in List in .
• ColumnListHandler： Store the data of a column in the result set in List in .
• KeyedHandler(name)： Encapsulate each row of data in the result set into a Map in , And then put these map Save one more map in , Its key For the specified key.
• MapHandler： Encapsulate the first row of data in the result set into a Map in ,key Is the column name ,value Is the corresponding value .
• MapListHandler： Encapsulate each row of data in the result set into a Map in , Then store it in List
  4、 Table and JavaBean
  
  Example code

package com.atguigu.jdbc4;

import java.sql.Connection;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.atguigu.utils.Admin;
import com.atguigu.utils.Boys;
import com.atguigu.utils.JDBCUtilsByDruid;

/** *  This class is used to demonstrate DBUtils Use  * @author liyuting *  function ： Encapsulates some methods related to database access  *  General additions, deletions, modifications, checks, etc  * * QueryRunner class ： * update(connection,sql,params): Execute any addition, deletion or modification statements  * query(connection,sql,ResultSetHandler,params)： Execute any query statement  * ResultSetHandler Interface  * BeanHandler: The first row of the result set , Encapsulated as an object , And back to  new BeanHandler<>(XX.class) * BeanListHandler: All rows in the result set , Encapsulated into a collection of objects , And back to  new BeanListHandler<>(XX.class) * ScalarHandler: The first row and the first column in the result set , With Object returns  new ScalarHandler() * * *  Use steps ： * * 1、 Import jar package commons-dbutils-1.3.jar * 2、 See help  * 3、 Use  * */
public class TestDBUtils {
    
	@Test
	public void testUpadte() throws Exception{
    
		
		//1. Get the connection 
		Connection connection = JDBCUtilsByDruid.getConnection();
		
		//2. Carry out addition, deletion and modification 
		QueryRunner qr = new QueryRunner();
		int update = qr.update(connection, "update boys set boyname=? where id=4", " Murong fu ");
		
		System.out.println(update>0?"success":"failure");
		
		//3. Close the connection 
		JDBCUtilsByDruid.close(null, null, connection);
	}
	
	@Test
	public void testQuerySingle() throws Exception{
    
		
		//1. Get the connection 
		Connection connection = JDBCUtilsByDruid.getConnection();
		
		//2. Carry out addition, deletion and modification 
		QueryRunner qr = new QueryRunner();
		
// Admin admin = qr.query(connection, "select * from admin where id=?", new BeanHandler<>(Admin.class),3);
// System.out.println(admin);
		
		
		Boys boys = qr.query(connection, "select * from boys where usercp=?", new BeanHandler<>(Boys.class),300);
		System.out.println(boys);
		
		
		//3. Close the connection 
		JDBCUtilsByDruid.close(null, null, connection);
	}

	@Test
	public void testQueryMulti() throws Exception{
    
		
		//1. Get the connection 
		Connection connection = JDBCUtilsByDruid.getConnection();
		
		//2. Carry out addition, deletion and modification 
		QueryRunner qr = new QueryRunner();
		
// Admin admin = qr.query(connection, "select * from admin where id=?", new BeanHandler<>(Admin.class),3);
// System.out.println(admin);
		
		List<Admin> list2 = qr.query(connection, "select * from admin", new BeanListHandler<>(Admin.class));
		
		for (Admin admin : list2) {
    
			System.out.println(admin);
		}
// 
// List<Boys> list = qr.query(connection, "select * from boys where usercp>?", new BeanListHandler<>(Boys.class),10);
// 
// for (Boys boys : list) {
    
// System.out.println(boys);
// }
		
		
		//3. Close the connection 
		JDBCUtilsByDruid.close(null, null, connection);
	}
	@Test
	public void testScalar() throws Exception{
    
		
		//1. Get the connection 
		Connection connection = JDBCUtilsByDruid.getConnection();
		
		//2. Execute a query for a single value 
		QueryRunner qr = new QueryRunner();
		Object query = qr.query(connection, "select * from admin", new ScalarHandler());
		System.out.println(query);
		
		//3. close 
		JDBCUtilsByDruid.close(null, null, connection);
		
		
	}

}

9 DAO And general methods of addition, deletion, modification and query

DAO：Data Access Object Classes and interfaces for accessing data information , Including the data of CRUD（Create、Retrival、Update、Delete）, It doesn't contain any business-related information
effect ： In order to realize the modularization of functions , More conducive to code maintenance and upgrade .

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.atguigu.utils.JDBCUtilsByDruid;

public class BasicDao<T> {
    
	QueryRunner qr = new QueryRunner();
	/* *  function ：  General method of adding, deleting and modifying , For any table  */
	public int update(String sql,Object...param){
    
		Connection connection = null;
		try {
    
			connection = JDBCUtilsByDruid.getConnection();
			int update = qr.update(connection, sql, param);
			return update;
		}  catch (Exception e) {
    
			throw new RuntimeException(e);
		}finally{
    
			JDBCUtilsByDruid.close(null, null, connection);
		}
	}
	/** *  function ： Return a single object , For any table  * @param sql * @param clazz * @param params * @return */
	public  T querySingle(String sql,Class<T> clazz,Object...params){
    
		Connection connection = null;
		try {
    
			connection = JDBCUtilsByDruid.getConnection();
			
			// Execute the query 
			return qr.query(connection, sql, new BeanHandler<T>(clazz), params);
			
			
		}  catch (Exception e) {
    
			throw new RuntimeException(e);
		}finally{
    
			JDBCUtilsByDruid.close(null, null, connection);
		}
	}
	
	/** *  function ： Returns multiple objects , For any table  * @param sql * @param clazz * @param params * @return */
	public List<T> queryMulti(String sql,Class<T> clazz,Object...params){
    
		Connection connection = null;
		try {
    
			connection = JDBCUtilsByDruid.getConnection();
			
			// Execute the query 
			return qr.query(connection, sql, new BeanListHandler<T>(clazz), params);
			
			
		}  catch (Exception e) {
    
			throw new RuntimeException(e);
		}finally{
    
			JDBCUtilsByDruid.close(null, null, connection);
		}
	}
	/** *  function ： Returns a single value  * @param sql * @param params * @return */
	public Object scalar(String sql,Object...params){
    
		Connection connection = null;
		try {
    
			connection = JDBCUtilsByDruid.getConnection();
			
			
			// Execute the query 
			return qr.query(connection, sql, new ScalarHandler(),params);
			
			
		}  catch (Exception e) {
    
			throw new RuntimeException(e);
		}finally{
    
			JDBCUtilsByDruid.close(null, null, connection);
		}
	}

}

It's just basic addition, deletion, inspection and modification , Suitable for any class , You also need to define an entity class for each table , Then use different classes DAO Inherit basicdao

for instance adminDAO,Admin Generic specified

package com.atguigu.dao;

import com.atguigu.bean.Admin;

public class AdminDao extends BasicDao<Admin> {
    
}

And then again AdminService Inside

package com.atguigu.service;

import com.atguigu.dao.AdminDao;

public class AdminService {
    
	AdminDao dao = new AdminDao();
	
	public boolean login(String username,String password){
    
		
		Long count =  (Long)dao.scalar("select count(*) from admin where username=? and password=?", username,password);
		return count>0;
	
	}
	
}

At last view Layer calls

package com.atguigu.view;

import java.util.Scanner;

import com.atguigu.service.AdminService;

public class StudentView {
    
	AdminService as = new AdminService();
	public static void main(String[] args) {
    
		new StudentView().login();
	}

	public void login(){
    
		Scanner input = new Scanner(System.in);
		System.out.println(" Please enter a user name ：");
		String username = input.next();
		System.out.println(" Please input a password ：");
		String password = input.next();
		
		
		if( as.login(username, password)){
    
			System.out.println(" Login successful ！");
			showMainMenu();
		}else{
    
			System.out.println(" Login failed ！");
		}
		
	}

	/** *  function ： Show main menu  */
	private void showMainMenu() {
    
		System.out.println(" Show main menu ");
	}
}