Chapter 1: JDBC overview

1.1 Data persistence

  • Persistence: Save data to power-off storage devices for later use. In most cases, especially in enterprise applications, data persistence means “curing” the data in memory to hard disk, and this is done mostly through various relational databases.

  • The main application of persistence is to store in-memory data in relational databases, but it can also be stored in disk files, XML data files.

1.2 Data storage technology in Java

  • In Java, database access technologies fall into the following categories:

    • JDBC direct access to the database

    • Java Data Object (JDO) technology

    • Third-party O/R tools, such as Hibernate, Mybatis, etc

  • JDBC is the cornerstone of Java access database, JDO, Hibernate, MyBatis and so on are just better encapsulation of JDBC

1.3 the JDBC introduction

  • Java Database Connectivity (JDBC) is a common interface (a set of apis) for accessing and operating SQL databases independent of specific Database management systems. It defines a standard Java class library for accessing databases. (java.sql, Javax.sql) Use these libraries to easily access database resources in a standard way.
  • JDBC provides a unified way to access different databases, shielding developers from some of the details.
  • The goal of JDBC is to enable Java programmers to use JDBC to connect to any database system that provides a JDBC driver, thus greatly simplifying and speeding up the development process by eliminating the need for programmers to learn too much about the characteristics of a particular database system.
  • If there is no JDBC, then Java programs accessing the database look like this:

  • With JDBC, a Java program accesses a database like this:

  • Summary:

1.4 JDBC architecture

  • The JDBC interface (API) consists of two layers:
    • Application-oriented APIS: Java apis, abstract interfaces for application developers to use (connect to databases, execute SQL statements, get results).
    • Database-oriented API: Java Driver API for developers to develop database drivers.

JDBC is a set of interfaces provided by Sun for database operations that Java programmers need only program to.

Different database vendors need to provide different implementations of this interface. A collection of different implementations, that is, drivers for different databases. ———— Interface oriented programming

1.5 JDBC program writing steps

Add: ODBC(Open Database Connectivity), Microsoft launched in Windows platform. The consumer only needs to call the ODBC API in the program, and the ODBC driver converts the call into a call request for a specific database.

Chapter 2: Getting a database connection

2.1 Element 1: Driver interface implementation class

2.1.1 Driver Interfaces

  • The java.sql.Driver interface is the interface that all JDBC drivers need to implement. This interface is intended for use by database vendors, and different database vendors provide different implementations.

  • The classes that implement the Driver interface do not need to be accessed directly from the program. Instead, the Driver manager classes (java.SQL.DriverManager) call these Driver implementations.

    • Oracle driver: Oracle JDBC. Driver. OracleDriver
    • MySql Driver: com.mysql.jdbc.driver

  • Copy the above JAR packages into a directory of your Java project, customarily creating a new lib folder.

Right-click on the driver JAR –>Build Path–>Add to Build Path

Note: For a Dynamic Web Project, place the driver JAR in the Lib directory of the WEB-INF directory in the WebContent (some development tools called WebRoot) directory

2.1.2 Loading and Registering the JDBC Driver

  • Loading the driver: Loading the JDBC driver requires calling the static method forName() of the Class Class, passing it the name of the JDBC driver Class to be loaded

    • Class.forname (“. Com. Mysql. JDBC Driver “);

  • Register drivers: the DriverManager class is the DriverManager class, responsible for managing drivers

    • Use the DriverManager. RegisterDriver (. Com. Mysql. JDBC Driver) to register the Driver

    • It is usually not necessary to explicitly call the registerDriver() method of the DriverManager class to register an instance of the Driver class, because the Driver classes of the Driver interface contain a static block of code, Invokes the DriverManager. RegisterDriver () method to register an instance of itself. Here is the source code for the MySQL Driver implementation class:

2.2 Element 2: URL

  • The JDBC URL is used to identify a registered driver, and the driver manager selects the correct driver through this URL to establish a connection to the database

  • The JDBC URL standard consists of three parts, separated by colons

    • JDBC: subprotocol: subname
    • Protocol: The protocol in a JDBC URL is always JDBC
    • Subprotocol: A subprotocol is used to identify a database driver
    • Subname: a way to identify a database. Subnames can vary for different subprotocols, and the purpose of using subnames is to provide sufficient information to locate the database. Contains the host name (corresponding to the SERVER IP address), port number, and database name

  • For example:

  • JDBC URLS for several common databases

    • MySQL > connect URL:

      • JDBC :mysql:// host name :mysql server port number/database name? Parameter = value & Parameter = value
      • jdbc:mysql://localhost:3306/one
      • jdbc:mysql://localhost:3306/one? useUnicode=true&characterEncoding=utf8(If the JDBC program does not match the server-side character set, resulting in garbled characters, you can specify the server-side character set as an argument)
      • jdbc:mysql://localhost:3306/one? user=root&password=123456

    • Oracle 9i connection URL:

      • JDBC: Oracle :thin:@ Host name: Oracle server port number: database name
      • jdbc:oracle:thin:@localhost:1521:one

    • SQLServer connection URL:

      • JDBC :sqlserver:// host name :sqlserver server port number :DatabaseName= DatabaseName

      • jdbc:sqlserver://localhost:1433:DatabaseName=one

2.3 Element 3: User Name and Password

  • User and password can be specified as “attribute name = attribute value” to the database
  • The DriverManager class can be calledgetConnection()Method to establish a connection to the database

2.4 Examples of Database Connection Modes

2.4.1 Connection Mode 1

@Test
public void testConnection1(a) {
    try {
        //1. Provide objects for the java.sql.Driver interface implementation class
        Driver driver = null;
        driver = new com.mysql.jdbc.Driver();

        //2. Provide the URL to specify the data for the specific operation
        String url = "jdbc:mysql://localhost:3306/test";

        //3. Provide the object of Properties, specifying the user name and password
        Properties info = new Properties();
        info.setProperty("user"."root");
        info.setProperty("password"."abc123");

        //4. Call driver's connect() to get the connection
        Connection conn = driver.connect(url, info);
        System.out.println(conn);
    } catch(SQLException e) { e.printStackTrace(); }}Copy the code

Description: The API of the third-party database appears explicitly in the above code

2.4.2 Connection Mode 2

@Test
public void testConnection2(a) {
    try {
        //1. Instantiate the Driver
        String className = "com.mysql.jdbc.Driver";
        Class clazz = Class.forName(className);
        Driver driver = (Driver) clazz.newInstance();

        //2. Provide the URL to specify the data for the specific operation
        String url = "jdbc:mysql://localhost:3306/test";

        //3. Provide the object of Properties, specifying the user name and password
        Properties info = new Properties();
        info.setProperty("user"."root");
        info.setProperty("password"."abc123");

        //4. Call driver's connect() to get the connection
        Connection conn = driver.connect(url, info);
        System.out.println(conn);

    } catch(Exception e) { e.printStackTrace(); }}Copy the code

Note: In contrast to method 1, the Driver is instantiated using reflection, and the API of the third-party database is not embodied in the code. Embodies the idea of interface-oriented programming.

2.4.3 Connection Mode 3

@Test
public void testConnection3(a) {
    try {
        //1. The four basic elements of database connection:
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "abc123";
        String driverName = "com.mysql.jdbc.Driver";

        //2. Instantiate the Driver
        Class clazz = Class.forName(driverName);
        Driver driver = (Driver) clazz.newInstance();
        //3. Register the driver
        DriverManager.registerDriver(driver);
        //4. Obtain the connection
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    } catch(Exception e) { e.printStackTrace(); }}Copy the code

Use DriverManager to connect to the database. Learn the four basic elements necessary to achieve connectivity

2.4.4 Connection Mode 4

@Test
public void testConnection4(a) {
    try {
        //1. The four basic elements of database connection:
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "abc123";
        String driverName = "com.mysql.jdbc.Driver";

        //2. Load the Driver (① instantiate the Driver ② register the Driver)
        Class.forName(driverName);


        //Driver driver = (Driver) clazz.newInstance();
        //3. Register the driver
        //DriverManager.registerDriver(driver);
        Can / * comment out the reason of the above code, because the mysql Driver class declarations are: static {try {DriverManager. RegisterDriver (new Driver ()); } catch (SQLException var1) { throw new RuntimeException("Can't register driver!" ); }} * /


        //3. Obtain the connection
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println(conn);
    } catch(Exception e) { e.printStackTrace(); }}Copy the code

Note: there is no need to explicitly register the driver. Because there is already a static code block in the DriverManager source code, the driver is registered.

2.4.5 Connection Mode 5 (Final Version)

@Test
public  void testConnection5(a) throws Exception {
    //1. Load the configuration file
    InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
    Properties pros = new Properties();
    pros.load(is);

    //2. Read configuration information
    String user = pros.getProperty("user");
    String password = pros.getProperty("password");
    String url = pros.getProperty("url");
    String driverClass = pros.getProperty("driverClass");

    //3. Load the driver
    Class.forName(driverClass);

    //4. Obtain the connection
    Connection conn = DriverManager.getConnection(url,user,password);
    System.out.println(conn);

}
Copy the code

The configuration file is declared in the SRC directory of the project: [jdbc.properties]

user=root
password=abc123
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
Copy the code

Note: Use the configuration file to save the configuration information and load the configuration file in the code

Benefits of using configuration files:

(1) The separation of code and data is realized. If the configuration information needs to be modified, it can be modified directly in the configuration file without going into the code. (2) If the configuration information is modified, the process of recompilation is saved.

Chapter 3: Implement CRUD operations using PreparedStatement

3.1 Operating and Accessing the Database

  • The database connection is used to send commands and SQL statements to the database server and accept the results returned by the database server. A database connection is actually a Socket connection.

  • There are three interfaces in the java.sql package that define different ways to call the database:

    • Statement: An object used to execute a static SQL Statement and return the result it generated
    • PrepatedStatement: SQL statements are precompiled and stored in this object, which can be used to efficiently execute the statement multiple times
    • CallableStatement: Used to execute an SQL stored procedure

3.2 Disadvantages of using Statement to manipulate tables

  • This object is created by calling the createStatement() method of the Connection object. This object is used to execute static SQL statements and return the results of the execution.

  • The Statement interface defines the following methods to execute SQL statements:

    int excuteUpdate(String sql) : Performs the update operationINSERT, the UPDATE,DELETE
ResultSet executeQuery(String sql) : Performs a query operationSELECT
Copy the code

  • However, there are drawbacks to using Statement to manipulate tables:

    • Problem one: existence spell string operation, tedious
    • Problem 2: THERE is an SQL injection problem

  • SQL injection takes advantage of systems that do not adequately check the data entered by the user and inject an invalid SQL statement segment or command into the data entered by the user (e.g. SELECT user, password FROM user_table WHERE user=’a’ OR 1 =’ AND password =’ OR ‘1’ =’ 1′)

  • For Java, to protect against SQL injection, simply replace Statement with PreparedStatement (which is an extension of Statement).

  • Code demo:

public class StatementTest {

	// Disadvantages of using Statement: you need to spell the SQL Statement, and there are problems with SQL injection
	@Test
	public void testLogin(a) {
		Scanner scan = new Scanner(System.in);

		System.out.print("Username:");
		String userName = scan.nextLine();
		System.out.print("Secret code:");
		String password = scan.nextLine();

		// SELECT user,password FROM user_table WHERE USER = '1' or ' AND PASSWORD = '='1' or '1' = '1';
		String sql = "SELECT user,password FROM user_table WHERE USER = '" + userName + "' AND PASSWORD = '" + password
				+ "'";
		User user = get(sql, User.class);
		if(user ! =null) {
			System.out.println("Login successful!");
		} else {
			System.out.println("Incorrect user name or password!"); }}// Use Statement to query a table
	public <T> T get(String sql, Class<T> clazz) {
		T t = null;

		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 1. Load the configuration file
			InputStream is = StatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
			Properties pros = new Properties();
			pros.load(is);

			// 2. Read configuration information
			String user = pros.getProperty("user");
			String password = pros.getProperty("password");
			String url = pros.getProperty("url");
			String driverClass = pros.getProperty("driverClass");

			// 3. Load the driver
			Class.forName(driverClass);

			// 4. Obtain the connection
			conn = DriverManager.getConnection(url, user, password);

			st = conn.createStatement();

			rs = st.executeQuery(sql);

			// Retrieve the metadata of the result set
			ResultSetMetaData rsmd = rs.getMetaData();

			// Get the number of columns in the result set
			int columnCount = rsmd.getColumnCount();

			if (rs.next()) {

				t = clazz.newInstance();

				for (int i = 0; i < columnCount; i++) {
					// //1. Obtain the column name
					// String columnName = rsmd.getColumnName(i+1);

					// 1. Obtain the alias of the column
					String columnName = rsmd.getColumnLabel(i + 1);

					// 2. Obtain the data in the corresponding data table according to the column name
					Object columnVal = rs.getObject(columnName);

					// 3. Encapsulate the data obtained from the data table into objects
					Field field = clazz.getDeclaredField(columnName);
					field.setAccessible(true);
					field.set(t, columnVal);
				}
				returnt; }}catch (Exception e) {
			e.printStackTrace();
		} finally {
			// Close the resource
			if(rs ! =null) {
				try {
					rs.close();
				} catch(SQLException e) { e.printStackTrace(); }}if(st ! =null) {
				try {
					st.close();
				} catch(SQLException e) { e.printStackTrace(); }}if(conn ! =null) {
				try {
					conn.close();
				} catch(SQLException e) { e.printStackTrace(); }}}return null; }}Copy the code

In conclusion:

3.3 Use of PreparedStatement

3.3.1 PreparedStatement is introduced

  • A preparedStatement object can be obtained by calling the preparedStatement(String SQL) method of the Connection object

  • The PreparedStatement interface is a subinterface of Statement and represents a precompiled SQL Statement

  • The parameters in the SQL statement represented by the PreparedStatement object are marked with question marks (?). The setXxx() method takes two arguments, the first is the index (starting at 1) of the arguments in the SQL statement to be set, and the second is the value of the arguments in the SQL statement to be set

3.3.2 rainfall distribution on 10-12 PreparedStatement vs the Statement

  • Code readability and maintainability.

  • PreparedStatement provides the greatest possible performance improvement:

    • DBServer toprecompiledStatement provides performance tuning. Because precompiled statements can be called repeatedly, soThe statement is cached after being compiled by the DBServer compiler, so the next call does not need to compile as long as it is the same precompiled statement, as long as the parameters are passed directly into the compiled statement execution code will be executed.
    • In a statement, the entire statement itself does not match because the data content is different even though the operation is the same. Therefore, there is no cached statement meaning. The fact is that no database caches the executed code after compiling normal statements. This compiles the passed statement each time it is executed.
    • (Syntax checking, semantics checking, translating into binary commands, caching)

  • PreparedStatement prevents SQL injection

3.3.3 Data type conversion table corresponding to Java and SQL

Java type SQL type
boolean BIT
byte TINYINT
short SMALLINT
int INTEGER
long BIGINT
String CHAR,VARCHAR,LONGVARCHAR
byte array BINARY , VAR BINARY
java.sql.Date DATE
java.sql.Time TIME
java.sql.Timestamp TIMESTAMP

3.3.4 Add, delete, and change operations using PreparedStatement

// Add, Delete, and modify; // Add, Delete, and modify; Reflection 2: For different tables)
public void update(String sql,Object ... args){
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        //1. Obtain the connection to the database
        conn = JDBCUtils.getConnection();

        //2. Obtain an instance of PreparedStatement (or: precompiled SQL statement)
        ps = conn.prepareStatement(sql);
        //3. Fill placeholders
        for(int i = 0; i < args.length; i++){ ps.setObject(i +1, args[i]);
        }

        //4. Run the SQL statement
        ps.execute();
    } catch (Exception e) {

        e.printStackTrace();
    }finally{
        //5. Close resourcesJDBCUtils.closeResource(conn, ps); }}Copy the code

3.3.5 Query Operations using PreparedStatement

// Generic query for different tables: return one object (Version 1.0)
public <T> T getInstance(Class<T> clazz, String sql, Object... args) {

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
                // 1. Obtain the database connection
                conn = JDBCUtils.getConnection();

                // 2. Prepare the SQL statement to obtain the PreparedStatement object
                ps = conn.prepareStatement(sql);

                // 3. Fill placeholders
                for (int i = 0; i < args.length; i++) {
                        ps.setObject(i + 1, args[i]);
                }

                // 4. Execute executeQuery() to get the ResultSet: ResultSet
                rs = ps.executeQuery();

                // 5. Obtain the ResultSetMetaData: ResultSetMetaData
                ResultSetMetaData rsmd = rs.getMetaData();

                // 6.1 Obtain columnCount,columnLabel from ResultSetMetaData; Get the column values from the ResultSet
                int columnCount = rsmd.getColumnCount();
                if (rs.next()) {
                        T t = clazz.newInstance();
                        for (int i = 0; i < columnCount; i++) {// Iterate over each column

                                // Get the column value
                                Object columnVal = rs.getObject(i + 1);
                                // Get the alias of the column: the alias of the column, using the property name of the class
                                String columnLabel = rsmd.getColumnLabel(i + 1);
                                // 6.2 Use reflection to assign values to the corresponding properties of the object
                                Field field = clazz.getDeclaredField(columnLabel);
                                field.setAccessible(true);
                                field.set(t, columnVal);

                        }

                        returnt; }}catch (Exception e) {

                e.printStackTrace();
        } finally {
                // 7. Close resources
                JDBCUtils.closeResource(conn, ps, rs);
        }

        return null;
}
Copy the code

Note: Query operations implemented by PreparedStatement can be used to replace query operations implemented by Statement, solving Statement spelling and SQL injection problems.

3.4 the ResultSet and ResultSetMetaData

3.4.1 track the ResultSet

  • The query calls the executeQuery() method in PreparedStatement, and the query result is an esultSet object

  • The ResultSet object encapsulates the ResultSet of the database operation in the form of a logical table. The ResultSet interface is implemented by the database vendor

  • What a ResultSet returns is actually a data table. There is a pointer to the first record in the data table.

  • A ResultSet object maintains a cursor pointing to the current data row. Initially, the cursor is before the first row and can be moved to the next row through the next() method of the ResultSet object. Call the next() method to check if the next line is valid. If valid, this method returns true with the pointer moved down. Equivalent to a combination of the hasNext() and next() methods of an Iterator object.

  • When a pointer points to a row, you can call getXxx(int index) or getXxx(int columnName) to obtain the value of each column.

    • Such as:getInt(1).getString("name")
    • Note: The indexes in the relevant Java apis for Java interacting with the database all start at 1.

  • Common methods of the ResultSet interface:

    • boolean next()
    • getString()
    • …

3.4.2 ResultSetMetaData

  • An object that can be used to get information about the types and properties of columns in a ResultSet object

  • ResultSetMetaData meta = rs.getMetaData();

    • GetColumnName (int Column) : Gets the name of the specified column

    • GetColumnLabel (int Column) : Gets the alias of the specified column

    • GetColumnCount () : Returns the number of columns in the current ResultSet.

    • GetColumnTypeName (int Column) : Retrieves the database specific type name for the specified column.

    • GetColumnDisplaySize (int Column) : Indicates the maximum standard width of a specified column, in characters.

    • IsNullable (int Column) : Indicates whether the value in the specified column can be NULL.

    • IsAutoIncrement (int Column) : Indicates whether the specified columns are automatically numbered so that they are still read-only.

Question 1: Once you have a result set, how do you know what columns are in the result set? What is the column name?

You need to use an object that describes the ResultSet, called ResultSetMetaData

Question 2: About ResultSetMetaData

  1. How do I obtain ResultSetMetaData: Calls a ResultSetgetMetaData()Methods can be
  2. Gets how many columns are in the ResultSet: calls ResultSetMetaDatagetColumnCount()methods
  3. What is the alias of each column in a ResultSet: calls ResultSetMetaDatagetColumnLabel()methods

3.5 Releasing Resources

  • Release a ResultSet, Statement, and Connection.
  • A database Connection is a rare resource that must be released as soon as it is used up. If the Connection is not shut down correctly in a timely manner, the system will break down. A Connection should be created as late as possible and released as early as possible.
  • In finally, the resource can be shut down in case any other code fails.

3.6 JDBC API Summary

  • Two kinds of thoughts

    • The idea of interface-oriented programming

    • Object Relational Mapping

      • A data table corresponds to a Java class
      • A record in a table corresponds to an object of a Java class
      • A field in the table corresponds to an attribute of the Java class

    SQL is written using a combination of column names and table attribute names. Notice the alias.

  • Two kinds of technology

    • JDBC result set metadata:ResultSetMetaData
      • Get the number of columns:getColumnCount()
      • Get the alias of the column:getColumnLabel()
    • Through reflection, an object of the specified class is created, the specified property is obtained, and the value is assigned

Chapter practice

Exercise 1: Insert a piece of data from the console into the database table customers, with the following table structure:

Create database table examstudent, table structure as follows:

Add the following data to the data table:

Code implementation 1: Inserts a new student information

Please enter the candidate’s details

Type:

IDCard:

ExamCard:

StudentName:

Location:

Grade:

Information entered successfully!

Code to achieve 2: in Eclipse to establish a Java program: enter the ID number or admission ticket number can be queried to the basic information of students. Here are the results:

Code implementation 3: complete the deletion of student information function

Chapter 4 Operates on BLOB type fields

4.1 MySQL BLOB type

  • In MySQ L, a BLOB is a binary large object, which is a container that can store large amounts of data. It can hold data of different sizes.

  • A PreparedStatement must be used to insert data of type BLOB because data of type BLOB cannot be written using string concatenation.

  • MySQL’s four BLOB types (they are equivalent except for the maximum amount of information stored)

  • In practice, different BLOB types are defined according to the size of the data to be stored.
  • Note that if the files stored are too large, the performance of the database may deteriorate.
  • If the error “XXX too large” is reported after specifying the type of Blob, then go to the my.ini file in the mysql installation directory and add the following configuration parameter: max_allowed_packet=16M. Also note: after modifying the my.ini file, you need to restart the mysql service.

4.2 Inserting big data types into a data table

// Get the connection
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into customers(name,email,birth,photo)values(? ,? ,? ,?) ";
PreparedStatement ps = conn.prepareStatement(sql);

// Fill the placeholder
ps.setString(1."Xu Haiqiang");
ps.setString(2."[email protected]");
ps.setDate(3.new Date(new java.util.Date().getTime()));
// Manipulate variables of type Blob
FileInputStream fis = new FileInputStream("xhq.png");
ps.setBlob(4, fis);
/ / execution
ps.execute();
		
fis.close();
JDBCUtils.closeResource(conn, ps);
Copy the code

4.3 Modifying Blob fields in a Data table

Connection conn = JDBCUtils.getConnection();
String sql = "update customers set photo = ? where id = ?";
PreparedStatement ps = conn.prepareStatement(sql);

// Fill the placeholder
// Manipulate variables of type Blob
FileInputStream fis = new FileInputStream("coffee.png");
ps.setBlob(1, fis);
ps.setInt(2.25);

ps.execute();

fis.close();
JDBCUtils.closeResource(conn, ps);
Copy the code

4.4 Reading big data types from data tables

String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1.8);
rs = ps.executeQuery();
if(rs.next()){
	Integer id = rs.getInt(1);
        String name = rs.getString(2);
	String email = rs.getString(3);
        Date birth = rs.getDate(4);
	Customer cust = new Customer(id, name, email, birth);
        System.out.println(cust); 
        // Read the fields of type Blob
	Blob photo = rs.getBlob(5);
	InputStream is = photo.getBinaryStream();
	OutputStream os = new FileOutputStream("c.jpg");
	byte [] buffer = new byte[1024];
	int len = 0;
	while((len = is.read(buffer)) ! = -1){
            os.write(buffer, 0, len);
	}
        JDBCUtils.closeResource(conn, ps, rs);
		
	if(is ! =null){
            is.close();
	}
		
	if(os ! =null){ os.close(); }}Copy the code

Chapter 5 Batch inserts

5.1 Batch Executing SQL Statements

When you need to insert or update records in batches, you can use Java’s batch update mechanism, which allows multiple statements to be submitted to the database for batch processing at once. It is usually more efficient than submitting processing separately

JDBC batch processing statements include the following three methods:

  • AddBatch (String) : Adds the SQL statements or parameters to be processed in batches.
  • ExecuteBatch () : executeBatch processing statements;
  • ClearBatch () : clears the cached data

There are two types of batch execution of SQL statements:

  • Batch processing of multiple SQL statements;
  • Batch pass parameters for an SQL statement;

5.2 Efficient batch inserts

Example: Insert 20,000 data pieces into a data table

  • A goods table is provided in the database. Create as follows:
CREATE TABLE goods(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20));
Copy the code

5.2.1 Implementation Level 1: Statement is used

Connection conn = JDBCUtils.getConnection();
Statement st = conn.createStatement();
for(int i = 1; i <=20000; i++){ String sql ="insert into goods(name) values('name_' + "+ i +")";
	st.executeUpdate(sql);
}
Copy the code

5.2.2 Implementation Level 2: PreparedStatement

long start = System.currentTimeMillis();
		
Connection conn = JDBCUtils.getConnection();
		
String sql = "insert into goods(name)values(?) ";
PreparedStatement ps = conn.prepareStatement(sql);
for(int i = 1; i <=20000; i++){ ps.setString(1."name_" + i);
	ps.executeUpdate();
}
		
long end = System.currentTimeMillis();
System.out.println("The time spent is:" + (end - start));/ / 82340
		
		
JDBCUtils.closeResource(conn, ps);
Copy the code

5.2.3 Implementation level 3

/* * Modify 1: use addBatch()/executeBatch()/clearBatch() * Modify 2: Allow mysql to disable batch processing by default. *? RewriteBatchedStatements =true at the end of the url of the configuration file * modified 3: Use the updated mysql driver: mysql-connector-java-5.1.37-bin.jar * */
@Test
public void testInsert1(a) throws Exception{
	long start = System.currentTimeMillis();
		
	Connection conn = JDBCUtils.getConnection();
		
	String sql = "insert into goods(name)values(?) ";
	PreparedStatement ps = conn.prepareStatement(sql);
		
	for(int i = 1; i <=1000000; i++){ ps.setString(1."name_" + i);
			
		/ / 1. The "save" SQL
		ps.addBatch();
		if(i % 500= =0) {/ / (2) is carried out
			ps.executeBatch();
			/ / 3. Emptyps.clearBatch(); }}long end = System.currentTimeMillis();
	System.out.println("The time spent is:" + (end - start));//20000 pieces: 625 //1000000 pieces :14733
		
	JDBCUtils.closeResource(conn, ps);
}
Copy the code

5.2.4 Implementation level 4

* setAutoCommit(false)/commit() */
@Test
public void testInsert2(a) throws Exception{
	long start = System.currentTimeMillis();
		
	Connection conn = JDBCUtils.getConnection();
		
	//1. Do not automatically submit data
	conn.setAutoCommit(false);
		
	String sql = "insert into goods(name)values(?) ";
	PreparedStatement ps = conn.prepareStatement(sql);
		
	for(int i = 1; i <=1000000; i++){ ps.setString(1."name_" + i);
			
		/ / 1. The "save" SQL
		ps.addBatch();
			
		if(i % 500= =0) {/ / (2) is carried out
			ps.executeBatch();
			/ / 3. Emptyps.clearBatch(); }}//2. Submit data
	conn.commit();
		
	long end = System.currentTimeMillis();
	System.out.println("The time spent is:" + (end - start));/ / 1000000:4978
		
	JDBCUtils.closeResource(conn, ps);
}
Copy the code

Chapter 6: Database transactions

6.1 Introduction to Database Transactions

  • Transaction: A set of logical units of operation that transforms data from one state to another.

  • Transaction processing (transactional operations) : Ensure that all transactions are executed as a unit of work, and that failure does not change the way they are executed. When multiple operations are performed in a transaction, either all transactions are committed, and the changes are saved permanently; Either the database management system will abandon all the changes it has made and the entire transaction will rollback to its original state.

  • In order to ensure the consistency of the data in the database, data manipulation should be discrete groups of logical units: when it is complete, can maintain the consistency of the data, and as part of the unit operation fails, the entire transaction shall all be regarded as wrong, all from the starting point of operation should be back to the start state.

6.2 JDBC transaction processing

  • Once the data is committed, it cannot be rolled back.

  • When does data mean commit?

    • When a connection object is created, the transaction is automatically committed by default: each time a SQL statement is executed, it is automatically committed to the database if the execution is successful and cannot be rolled back.
    • Close the database connection and the data is automatically committed. If there are multiple operations, each using its own separate connection, there is no guarantee of a transaction. That is, multiple operations of the same transaction must be under the same connection.

  • To make multiple SQL statements execute as a single transaction in a JDBC program:

    • Call setAutoCommit(false) on the Connection object; To cancel the auto-commit transaction
    • After all SQL statements have successfully executed, call commit(); Method commit transaction
    • In case of an exception, call rollback(); Method to roll back the transaction

    If the Connection is not closed at this point and may be reused, it needs to be restored to its auto-commit state of setAutoCommit(true). In particular, when using database connection pooling technology, it is recommended to revert to the autocommit state before executing the close() method.

[Case: User AA transfers 100 to user BB]

public void testJDBCTransaction(a) {
	Connection conn = null;
	try {
		// 1. Obtain the database connection
		conn = JDBCUtils.getConnection();
		// 2. Start the transaction
		conn.setAutoCommit(false);
		// 3. Perform database operations
		String sql1 = "update user_table set balance = balance - 100 where user = ?";
		update(conn, sql1, "AA");

		// The analog network is abnormal
		//System.out.println(10 / 0);

		String sql2 = "update user_table set balance = balance + 100 where user = ?";
		update(conn, sql2, "BB");
		// 4. If there is no exception, commit the transaction
		conn.commit();
	} catch (Exception e) {
		e.printStackTrace();
		// 5. If there is an exception, roll back the transaction
		try {
			conn.rollback();
		} catch(SQLException e1) { e1.printStackTrace(); }}finally {
        try {
			//6. Restore the automatic commit function for each DML operation
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			e.printStackTrace();
		}
        //7. Close the connection
		JDBCUtils.closeResource(conn, null.null); }}Copy the code

Where, the database operation method is:

// Use the generic add, delete and modify operations after a transaction (Version 2.0)
public void update(Connection conn ,String sql, Object... args) {
	PreparedStatement ps = null;
	try {
		// 1. Obtain an instance of PreparedStatement (or: PreparedStatement)
		ps = conn.prepareStatement(sql);
		// 2. Fill placeholders
		for (int i = 0; i < args.length; i++) {
			ps.setObject(i + 1, args[i]);
		}
		// 3. Run the SQL statement
		ps.execute();
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		// 4. Close resources
		JDBCUtils.closeResource(null, ps); }}Copy the code

6.3 ACID properties of transactions

  1. Atomicity refers to the idea that a transaction is an indivisible unit of work in which all or none of the operations occur.

  2. Consistency transactions must make the database transition from one Consistency state to another.

  3. Isolation The Isolation of a transaction means that the execution of a transaction cannot be interfered with by other transactions. In other words, the operations and data used within a transaction are isolated from other concurrent transactions, and the concurrent transactions cannot interfere with each other.

  4. Durability means that once a transaction is committed, its changes to the data in the database are permanent and should not be affected by subsequent operations and database failures.

6.3.1 Database Concurrency Is Abnormal

  • With multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not in place, it can cause various concurrency problems:

    • Dirty read: For two transactions T1, T2, T1 reads a field that has been updated by T2 but has not yet been committed. Later, if T2 rolls back, what T1 reads is temporary and invalid.
    • Non-repeatable reads: For two transactions T1, T2, T1 reads a field, and T2 updates the field. Later, T1 reads the same field again with a different value.
    • Magic read: For two transactions T1, T2, T1 reads a field from a table, and T2 inserts some new rows into the table. Later, if T1 reads the same table again, there will be a few more rows.

  • Isolation of database transactions: A database system must have the ability to isolate transactions running concurrently so that they do not interact and avoid concurrency problems.

  • The degree to which a transaction is isolated from other transactions is called the isolation level. The database defines multiple transaction isolation levels, which correspond to different levels of interference. The higher the isolation level, the better the data consistency, but the less concurrency.

6.3.2 Four Isolation Levels

  • The database provides four levels of transaction isolation:

  • Oracle supports two transaction isolation levels: READ COMMITED and SERIALIZABLE. The default transaction isolation level for Oracle is READ COMMITED.

  • Mysql supports four transaction isolation levels. The default transaction isolation level of Mysql is REPEATABLE READ.

6.3.3 Setting the Isolation Level in the MySql Database

  • Every time you start a mysql program, you get a separate database connection. Each database connection has a global variable @@TX_ISOLATION that represents the current transaction isolation level.

  • To view the current isolation level:

    SELECT @@tx_isolation;
Copy the code

  • Set the isolation level for the current mySQL connection:

    set  transaction isolation level read committed;
Copy the code

  • Set the global isolation level for the database system:

    set global transaction isolation level read committed;
Copy the code

  • Supplementary operations:

    • Create mysql database user:

      create user tom identified by 'abc123';
Copy the code

    • Grant permissions

      Grant user Tom full access to all tables in all libraries with password set to abc123
grant all privileges on*. *to tom@The '%'  identified by 'abc123';  

Grant Tom the right to insert, delete, modify, and search all tables in the one library
grant select.insert.delete.update on one.* to tom@localhost identified by 'abc123'; 
Copy the code

Chapter 7: DAO and related implementation classes

  • DAO: Data Access Object A class and interface for accessing Data information, including the CRUD (Create, Retrival, Update, Delete) of the Data but not any business-related information. Sometimes also called:BaseDAO
  • Function: In order to realize the modular function, more conducive to the maintenance and upgrade of code.

【 BaseDAO. Java 】

package com.one.bookstore.dao;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
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;


/** * defines a Dao that is used to inherit basic operations on the database@author HanYanBing
 *
 * @param <T>
 */
public abstract class BaseDao<T> {
	private QueryRunner queryRunner = new QueryRunner();
	// Define a variable to receive a generic type
	private Class<T> type;

	// Get the Class object of T, and get the type of the generic type, which is determined when the subclass inherits
	public BaseDao(a) {
		// Get the type of the subclass
		Class clazz = this.getClass();
		// Get the type of the superclass
		// getGenericSuperclass() is used to get the type of the superclass of the current class
		// ParameterizedType specifies the type with a generic type
		ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
		GetActualTypeArguments Gets the type of the specific generic type
		// This method returns an array of types
		Type[] types = parameterizedType.getActualTypeArguments();
		// Get the type of the specific generic type
		this.type = (Class<T>) types[0];
	}

	/** * General add, delete and change operation **@param sql
	 * @param params
	 * @return* /
	public int update(Connection conn,String sql, Object... params) {
		int count = 0;
		try {
			count = queryRunner.update(conn, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return count;
	}

	/** * get an object **@param sql
	 * @param params
	 * @return* /
	public T getBean(Connection conn,String sql, Object... params) {
		T t = null;
		try {
			t = queryRunner.query(conn, sql, new BeanHandler<T>(type), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return t;
	}

	/** * get all objects **@param sql
	 * @param params
	 * @return* /
	public List<T> getBeanList(Connection conn,String sql, Object... params) {
		List<T> list = null;
		try {
			list = queryRunner.query(conn, sql, new BeanListHandler<T>(type), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return list;
	}

	/** * get a simple method for performing tasks like select count(*)... SQL statement * *@param sql
	 * @param params
	 * @return* /
	public Object getValue(Connection conn,String sql, Object... params) {
		Object count = null;
		try {
			// Call queryRunner's query method to get a single value
			count = queryRunner.query(conn, sql, new ScalarHandler<>(), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		returncount; }}Copy the code

【 BookDAO. Java 】

package com.one.bookstore.dao;

import java.sql.Connection;
import java.util.List;
import com.one.bookstore.beans.Book;
import com.one.bookstore.beans.Page;

public interface BookDao {

	/** retrieve all records from the database **@return* /
	List<Book> getBooks(Connection conn);

	Insert a record into the database **@param book
	 */
	void saveBook(Connection conn,Book book);

	Delete a record from the database based on the id of the book **@param bookId
	 */
	void deleteBookById(Connection conn,String bookId);

	/** * select a record from the database by id **@param bookId
	 * @return* /
	Book getBookById(Connection conn,String bookId);

	/** * Update a record from the database based on the book ID **@param book
	 */
	void updateBook(Connection conn,Book book);

	/** * Get the book information with paging **@paramPage: a page object * that contains only the pageNo property entered by the user@returnThe Page object returned is a Page object */ containing all the properties
	Page<Book> getPageBooks(Connection conn,Page<Book> page);

	/** * Get book information with paging and price range **@paramPage: a page object * that contains only the pageNo property entered by the user@returnThe Page object returned is a Page object */ containing all the properties
	Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice);

}
Copy the code

【 UserDAO. Java 】

package com.one.bookstore.dao;
import java.sql.Connection;
import com.one.bookstore.beans.User;

public interface UserDao {

	/** * Retrieve a record from the database based on the User name and password in the User object **@param user
	 * @returnThere is a record in the User database. There is no record in the NULL database
	User getUser(Connection conn,User user);

	/** * Retrieve a record from the database based on the User name in the User object **@param user
	 * @returnTrue There is a record in the database false There is no record in the database */
	boolean checkUsername(Connection conn,User user);

	/** * Insert the User object into the database@param user
	 */
	void saveUser(Connection conn,User user);
}
Copy the code

【 BookDaoImpl. Java 】

package com.one.bookstore.dao.impl;

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

import com.one.bookstore.beans.Book;
import com.one.bookstore.beans.Page;
import com.one.bookstore.dao.BaseDao;
import com.one.bookstore.dao.BookDao;

public class BookDaoImpl extends BaseDao<Book> implements BookDao {

	@Override
	public List<Book> getBooks(Connection conn) {
		// Call BaseDao to get a List method
		List<Book> beanList = null;
		// Write the SQL statement
		String sql = "select id,title,author,price,sales,stock,img_path imgPath from books";
		beanList = getBeanList(conn,sql);
		return beanList;
	}

	@Override
	public void saveBook(Connection conn,Book book) {
		// Write the SQL statement
		String sql = "insert into books(title,author,price,sales,stock,img_path) values(? ,? ,? ,? ,? ,?) ";
		// Call BaseDao's usual add, delete, and change methods
		update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(),book.getImgPath());
	}

	@Override
	public void deleteBookById(Connection conn,String bookId) {
		// Write the SQL statement
		String sql = "DELETE FROM books WHERE id = ?";
		// Call BaseDao's common add, delete, and change methods
		update(conn,sql, bookId);
			
	}

	@Override
	public Book getBookById(Connection conn,String bookId) {
		// Call the method in BaseDao to get an object
		Book book = null;
		// Write the SQL statement
		String sql = "select id,title,author,price,sales,stock,img_path imgPath from books where id = ?";
		book = getBean(conn,sql, bookId);
		return book;
	}

	@Override
	public void updateBook(Connection conn,Book book) {
		// Write the SQL statement
		String sql = "update books set title = ? , author = ? , price = ? , sales = ? , stock = ? where id = ?";
		// Call BaseDao's usual add, delete, and change methods
		update(conn,sql, book.getTitle(), book.getAuthor(), book.getPrice(), book.getSales(), book.getStock(), book.getId());
	}

	@Override
	public Page<Book> getPageBooks(Connection conn,Page<Book> page) {
		// Get the total number of books in the database
		String sql = "select count(*) from books";
		// Call the BaseDao method that gets a single value
		long totalRecord = (long) getValue(conn,sql);
		// Set the total number of records in the page object
		page.setTotalRecord((int) totalRecord);

		// Retrieve the List of records in the current page
		String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books limit ? That?";
		// Call the method in BaseDao that gets a collection
		List<Book> beanList = getBeanList(conn,sql2, (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
		// Set the List to the page object
		page.setList(beanList);
		return page;
	}

	@Override
	public Page<Book> getPageBooksByPrice(Connection conn,Page<Book> page, double minPrice, double maxPrice) {
		// Get the total number of books in the database
		String sql = "select count(*) from books where price between ? and ?";
		// Call the BaseDao method that gets a single value
		long totalRecord = (long) getValue(conn,sql,minPrice,maxPrice);
		// Set the total number of records in the page object
		page.setTotalRecord((int) totalRecord);

		// Retrieve the List of records in the current page
		String sql2 = "select id,title,author,price,sales,stock,img_path imgPath from books where price between ? and ? limit ? That?";
		// Call the method in BaseDao that gets a collection
		List<Book> beanList = getBeanList(conn,sql2, minPrice , maxPrice , (page.getPageNo() - 1) * Page.PAGE_SIZE, Page.PAGE_SIZE);
		// Set the List to the page object
		page.setList(beanList);
		
		returnpage; }}Copy the code

【 UserDaoImpl. Java 】

package com.one.bookstore.dao.impl;

import java.sql.Connection;

import com.one.bookstore.beans.User;
import com.one.bookstore.dao.BaseDao;
import com.one.bookstore.dao.UserDao;

public class UserDaoImpl extends BaseDao<User> implements UserDao {

	@Override
	public User getUser(Connection conn,User user) {
		// Call the method in BaseDao to get an object
		User bean = null;
		// Write the SQL statement
		String sql = "select id,username,password,email from users where username = ? and password = ?";
		bean = getBean(conn,sql, user.getUsername(), user.getPassword());
		return bean;
	}

	@Override
	public boolean checkUsername(Connection conn,User user) {
		// Call the method in BaseDao to get an object
		User bean = null;
		// Write the SQL statement
		String sql = "select id,username,password,email from users where username = ?";
		bean = getBean(conn,sql, user.getUsername());
		returnbean ! =null;
	}

	@Override
	public void saveUser(Connection conn,User user) {
		// Write the SQL statement
		String sql = "insert into users(username,password,email) values(? ,? ,?) ";
		// Call BaseDao's usual add, delete, and change methodsupdate(conn,sql, user.getUsername(),user.getPassword(),user.getEmail()); }}Copy the code

【 Book. Java 】

package com.one.bookstore.beans;
/** * Books *@author songhongkang
 *
 */
public class Book {

	private Integer id;
	private String title; / / title
	private String author; / / the author
	private double price; / / price
	private Integer sales; / / sales
	private Integer stock; / / inventory
	private String imgPath = "static/img/default.jpg"; // The path of the cover image
	Get (), set(), and toString() methods are omitted
}
Copy the code

[Page. Java]

package com.one.bookstore.beans;

import java.util.List;
/** *@author songhongkang
 *
 */
public class Page<T> {

	private List<T> list; // A collection of records found on each page
	public static final int PAGE_SIZE = 4; // Number of records to display per page
	private int pageNo; / / the current page
// private int totalPageNo; // The total number of pages is calculated
	private int totalRecord; // The total number of records is obtained by querying the database
}
Copy the code

【 User. Java 】

package com.one.bookstore.beans;
/** * User class *@author songhongkang
 *
 */
public class User {
	private Integer id;
	private String username;
	private String password;
	private String email;
}
Copy the code

Chapter 8: Database connection pooling

8.1 JDBC Database connection Pool Is necessary

  • When developing database-based Web applications, the traditional model is basically the following steps:

    • Establish a database connection in the main program (such as servlets, beans)
    • Performing SQL operations
    • Disconnect from the database

  • The problems of this model development are as follows:

    • A normal JDBC database Connection is obtained using DriverManager. Each time a Connection is established to the database, the Connection is loaded into memory and the user name and password are verified (which takes 0.05s to 1s). When you need a database connection, ask for one from the database and disconnect after the execution is complete. This approach will consume a lot of resources and time. ** Database connection resources are not well reused. ** If there are hundreds or even thousands of people online at the same time, frequent database connection operations will occupy a lot of system resources, or even cause a serious server crash.
    • For every database connection, it must be disconnected after use. Otherwise, if the program fails to close due to an exception, it will cause a memory leak in the database system, which will eventually cause the database to restart. (Understanding: What is a Java memory leak?)
    • This kind of development does not control the number of connection objects created, system resources will be allocated without consideration, if too many connections, may also lead to memory leaks, server crash.

8.2 Database Connection Pool Technology

  • To solve the problem of database connection in traditional development, database connection pool technology can be adopted.

  • The basic idea of database connection pooling is to create a “buffer pool” for database connections. A certain number of connections are put into the buffer pool in advance, and when a database connection needs to be established, one is taken out of the “buffer pool” and put back after use.

  • The database connection pool is responsible for allocating, managing, and releasing database connections, allowing applications to reuse an existing database connection rather than re-establishing one.

    • When a database connection pool is initialized, a certain number of database connections are created into the connection pool. The number of these database connections is set by the minimum number of database connections. Whether or not these database connections are used, the connection pool will always guarantee to have at least this number of connections. The maximum number of database connections in the connection pool limits the maximum number of connections that the connection pool can hold. When an application requests more than the maximum number of connections to the connection pool, the requests are added to the waiting queue.

  • Working principle:

  • Advantages of database connection pooling technology

    1. Resource reuse

    Because database connections are reused, frequent creation is avoided, freeing up the large performance overhead associated with connections. On the basis of reducing the system consumption, on the other hand, it also increases the stability of the system operating environment.

    2. Faster system response

    Database connection pool During initialization, several database connections are often created and placed in the connection pool for standby. The initialization of the connection is complete. For business request processing, directly using the existing available connections avoids the time overhead of database connection initialization and release, thus reducing the response time of the system

    3. New means of resource allocation

    For a system where multiple applications share the same database, you can configure the database connection pool at the application layer to limit the maximum number of available database connections for an application, preventing an application from monopolizing all database resources

    4. Unified connection management to prevent database connection leakage

    In the more perfect database connection pool implementation, the occupied connection can be forcibly reclaimed according to the preset occupation timeout, so as to avoid the possible resource leakage in the routine database connection operation

8.3 Multiple open source database connection pools

  • JDBC database connection pool usagejavax.sql.DataSourceA DataSource is simply an interface that is usually implemented by the server (Weblogic, WebSphere, Tomcat), but also by some open source organizations:
    • DBCP is a database connection pool provided by Apache. The Tomcat server provides DBCP database connection pool. Compared with C3P0, Hibernate3 is no longer supported due to bugs.
    • C3P0 is an open source organization to provide a database connection pool, ** relatively slow, stability is ok. ** Hibernate official recommended use
    • Proxool is an open source project on SourceForge that monitors connection pool status and is less stable than C3P0
    • BoneCP is a fast database connection pool provided by an open source organization
    • Druid is a database connection pool provided by Ali. It is said to be a combination of DBCP, C3P0, and Proxool advantages, but it is not sure whether it is as fast as BoneCP
  • A DataSource is often referred to as the DataSource. It consists of a connection pool and connection pool management. A DataSource is often referred to as a connection pool
  • DataSource replaces DriverManager to obtain Connection. Obtaining speed is fast and greatly improves database access speed.
  • Special attention:
    • A data source is different from a database connection. You do not need to create multiple data sources. A data source is the factory that generates database connections, so you only need one data source for the entire application.
    • When the database access is complete, the program closes the database connection as before: conn.close(); Conn.close () does not close the physical connection to the database. It simply releases the database connection and returns it to the database connection pool.

8.3.1 C3P0 Database connection pool

  • Obtain connection method 1
// Use the C3P0 database connection pool to obtain the database connection: not recommended
public static Connection getConnection1(a) throws Exception{
    ComboPooledDataSource cpds = new ComboPooledDataSource();	
    cpds.setDriverClass("com.mysql.jdbc.Driver"); 	
    cpds.setJdbcUrl("jdbc:mysql://localhost:3306/test");
    cpds.setUser("root");	
    cpds.setPassword("abc123");	
    cpds.setMaxPoolSize(100);
    Connection conn = cpds.getConnection();	
    return conn;
}
Copy the code
  • Obtain connection mode 2
// Use the C3P0 database connection pool configuration file to obtain the database connection: recommended
private static DataSource cpds = new ComboPooledDataSource("helloc3p0");
public static Connection getConnection2(a) throws SQLException{
    Connection conn = cpds.getConnection();	
    return conn;
}
Copy the code

SRC: [c3P0-config.xml] [c3P0-config.xml]


      
<c3p0-config>
    <named-config name="helloc3p0">		
    <! -- get the 4 basic information about the connection -->		
        <property name="user">root</property>		
        <property name="password">abc123</property>		
        <property name="jdbcUrl">jdbc:mysql:///test</property>		
        <property name="driverClass">com.mysql.jdbc.Driver</property>				
        <! -> Database connection pool -> database connection pool		
        <! If the number of connections in the database is not sufficient, how many connections can be requested from the database server at a time -->		
        <property name="acquireIncrement">5</property>		
        <! -- Number of connections when initializing the database connection pool -->		
        <property name="initialPoolSize">5</property>		
        <! The minimum number of database connections in the database connection pool -->		
        <property name="minPoolSize">5</property>		
        <! Maximum number of database connections in the database connection pool -->		
        <property name="maxPoolSize">10</property>		
        <! -- C3P0 number of statements that can be maintained by the database connection pool -->		
        <property name="maxStatements">20</property>		
        <! Number of Statement objects that can be used at the same time per connection -->		
        <property name="maxStatementsPerConnection">5</property>	
    </named-config>
</c3p0-config>
Copy the code

8.3.2 DBCP Database connection pool

  • DBCP is an open source connection pool implementation under the Apache Software Foundation that relies on another open source system under the organization: Common-pool. To use this connection pool implementation, add the following two JAR files to the system:
    • Commons-dbcp.jar: Implementation of connection pooling
    • Commons-pool.jar: dependencies for connection pooling implementations
  • Tomcat’s connection pool is implemented using this connection pool. The database connection pool can be used either in conjunction with the application server or independently of the application.
  • A data source is different from a database connection. You do not need to create multiple data sources. A data source is the factory that generates database connections, so you only need one data source for the entire application.
  • When the database access is complete, the application closes the database connection as before:conn.close();But the above code does not close the physical connection to the database, it just releases the database connection back to the database connection pool.
  • Configuration Attribute Description
attribute The default value instructions
initialSize 0 The initial number of connections created when the connection pool is started
maxActive 8 Maximum number of simultaneous connections in the connection pool
maxIdle 8 Maximum number of idle connections in the connection pool. Excess idle connections will be released. If the value is set to negative, no limit is set
minIdle 0 The minimum number of free connections in the connection pool before new connections are created. The closer this parameter is to maxIdle, the better the performance, because connection creation and destruction are resource-intensive; But not too big.
maxWait unlimited Maximum wait time: The maximum time that a connection pool can wait for a connection to be released when no connection is available. An exception will be thrown if the time limit is exceeded. If -1 is set, the wait is unlimited
poolPreparedStatements false Whether the pool Statement is prepared
maxOpenPreparedStatements unlimited The maximum number of simultaneous connections in the pool after prepared is enabled
minEvictableIdleTimeMillis The time when the connection in the connection pool is idle and is exported from the connection pool
removeAbandonedTimeout 300 Unusable (discarded) connections were recovered after time limit was exceeded
removeAbandoned false After the time of removeAbandonedTimeout, whether to reclaim unused connections (discarded)
  • Obtaining connection method 1:
public static Connection getConnection3(a) throws Exception {
	BasicDataSource source = new BasicDataSource();
		
	source.setDriverClassName("com.mysql.jdbc.Driver");
	source.setUrl("jdbc:mysql:///test");
	source.setUsername("root");
	source.setPassword("abc123");
	
	source.setInitialSize(10);
		
	Connection conn = source.getConnection();
	return conn;
}
Copy the code
  • Obtaining connection method 2:
// Use the DBCP database connection pool configuration file to obtain the database connection: recommended
private static DataSource source = null;
static{
	try {
		Properties pros = new Properties();
		
		InputStream is = DBCPTest.class.getClassLoader().getResourceAsStream("dbcp.properties");
			
		pros.load(is);
		// Create a DataSource object based on the supplied BasicDataSourceFactory
		source = BasicDataSourceFactory.createDataSource(pros);
	} catch(Exception e) { e.printStackTrace(); }}public static Connection getConnection4(a) throws Exception {
	Connection conn = source.getConnection();
	return conn;
}
Copy the code

The SRC configuration file is [dbcp.properties].

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test? rewriteBatchedStatements=true&useServerPrepStmts=false
username=root
password=abc123

initialSize=10
#...
Copy the code

8.3.3 Druid database connection pool

Druid is a DB connection pool implementation, it is combined with C3P0, DBCP, Proxool and other DB pool advantages, at the same time to add log monitoring, can be very good monitoring DB pool connection and SQL execution, can be said to be for monitoring and generated DB connection pool. Arguably one of the best connection pools available.

package com.one.druid;

import java.sql.Connection;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

public class TestDruid {
	public static void main(String[] args) throws Exception {
		Properties pro = new Properties();
                pro.load(TestDruid.class.getClassLoader().getResourceAsStream("druid.properties")); DataSource ds = DruidDataSourceFactory.createDataSource(pro); Connection conn = ds.getConnection(); System.out.println(conn); }}Copy the code

[druid.properties] [druid.properties]

url=jdbc:mysql://localhost:3306/test? rewriteBatchedStatements=true
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=20
maxWait=1000
filters=wall
Copy the code
  • Detailed configuration parameters:
configuration The default instructions
name The point of configuring this property is that if multiple data sources exist, they can be identified by their names when monitoring them. If not configured, a name is generated in the format “DataSource-” + System.identityHashCode(this)
url The URL used to connect to the database varies from database to database. For example: mysql: JDBC: mysql: / / 10.20.153.104:3306 / druid2 oracle: JDBC: oracle: thin: @ 10.20.149.85:1521: ocnauto
username User name for connecting to the database
password Password for connecting to the database. You can use ConfigFilter if you don’t want the password written directly in the configuration file. See the details here:Github.com/alibaba/dru…
driverClassName If druID is not configured, dbType will be automatically identified according to the URL, and then select the corresponding dbType (recommended configuration).
initialSize 0 Number of physical connections established during initialization. Initialization occurs when the init method is called, or the first getConnection is called
maxActive 8 Maximum number of connection pools
maxIdle 8 It is no longer in use and has no effect on the configuration
minIdle Minimum number of connection pools
maxWait The maximum wait time to get a connection, in milliseconds. After maxWait is configured, fair lock is enabled by default, and the concurrency efficiency decreases. If necessary, you can set useUnfairLock to true to use non-fair locks.
poolPreparedStatements false Whether to cache the preparedStatement, that is, PSCache. PSCache provides a huge performance boost for databases that support cursors, such as Oracle. This is recommended under mysql.
maxOpenPreparedStatements – 1 To enable PSCache, the configuration must be greater than 0, at which point the poolPreparedStatements automatically triggers the change to true. In Druid, there is no problem with Oracle PSCache using too much memory. You can configure this value to be larger, such as 100
validationQuery The SQL used to check whether the connection is valid requires a query statement. If validationQuery is null, testOnBorrow, testOnReturn, and testWhileIdle have no effect.
testOnBorrow true When a connection is requested, validationQuery is executed to check whether the connection is valid. This configuration degrades performance.
testOnReturn false ValidationQuery is executed to check whether the connection is valid when the connection is returned. This configuration degrades performance
testWhileIdle false You are advised to set this parameter to True, which does not affect performance and ensures security. Apply for connection, if free time is more than timeBetweenEvictionRunsMillis, performing validationQuery test connection is valid.
timeBetweenEvictionRunsMillis TestWhileIdle ()testWhileIdle (); testWhileIdle (); testWhileIdle
numTestsPerEvictionRun No longer in use, a DruidDataSource supports only one EvictionRun
minEvictableIdleTimeMillis
connectionInitSqls SQL executed when the physical connection is initialized
exceptionSorter According to dbType automatic identification, when the database throws some unrecoverable exception, discard the connection
filters The attribute type is a string. You can configure extension plug-ins using aliases. The common plug-ins are as follows: Filter :stat for statistics monitoring Filter :log4j for logs Filter: WALL for defense against SQL injection
proxyFilters If both filters and proxyFilters are configured, this is a combination relationship, not a replacement relationship

Chapter 9: Apache-dbutils implements CRUD operations

9.1 Apache – DBUtils profile

  • Commons – Dbutils is an open source JDBC tool library provided by the Apache organization. It is a simple encapsulation of JDBC. The cost of learning is very low, and using Dbutils can greatly simplify the work of JDBC coding without compromising the performance of the application.

  • The API is introduced:

    • org.apache.commons.dbutils.QueryRunner
    • org.apache.commons.dbutils.ResultSetHandler
    • Tools: org.apache.com mons. Dbutils. Dbutils

  • API package description:

9.2 Usage of major apis

9.2.1 DbUtils

  • DbUtils: Provides utility classes that do routine work such as closing connections, loading JDBC drivers, etc. All methods in this class are static. The main methods are as follows:
    • Public static void close (…). Throws Java.SQl.SQlexception: The DbUtils class provides three overloaded closing methods. These methods check if the supplied arguments are NULL, and if not, they close the Connection, Statement, and ResultSet.
    • Public static void closeQuietly (...).: This class of methods can not only avoid closing Connection, Statement, and ResultSet NULL, but also hide some SQLEeception thrown by the program.
    • public static void commitAndClose(Connection conn)throws SQLException: The transaction used to commit the connection and then close the connection
    • public static void commitAndCloseQuietly(Connection conn): is used to commit the connection and then close it without throwing SQL exceptions.
    • public static void rollback(Connection conn)throws SQLExceptionConn is allowed to be null because the method is determined internally
    • public static void rollbackAndClose(Connection conn)throws SQLException
    • rollbackAndCloseQuietly(Connection)
    • public static boolean loadDriver(java.lang.String driverClassName): This side loads and registers the JDBC driver, returning true on success. With this method, you do not need to catch the exception ClassNotFoundException.

9.2.2 QueryRunner class

  • This class simplifies SQL queries and can be used in combination with ResultSetHandler to perform most database operations, greatly reducing the amount of coding required.

  • The QueryRunner class provides two constructors:

    • The default constructor
    • A javax.sql.DataSource is required for the argument constructor

  • The main methods of the QueryRunner class are:

    • update
      • public int update(Connection conn, String sql, Object... params) throws SQLException: Used to perform an update (insert, update, or delete) operation.
      • .
    • insert
      • public <T> T insert(Connection conn,String sql,ResultSetHandler<T> rsh, Object... params) throws SQLException: Supports only INSERT statements, where RSH – The handler used to create The result object from The ResultSet of auto-generated keys. Return value: An object generated by the handler. That is, automatically generated key values
      • .
    • The batch
      • public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException: INSERT, UPDATE, or DELETE statements
      • public <T> T insertBatch(Connection conn,String sql,ResultSetHandler<T> rsh,Object[][] params)throws SQLException: Supports only INSERT statements
      • .
    • The query
      • public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException: Performs a query operation in which the value of each element in the array of objects is used as a replacement parameter for the query statement. This method handles the creation and closing of a PreparedStatement and ResultSet itself.
      • .

  • test

// Test added
@Test
public void testInsert(a) throws Exception {
	QueryRunner runner = new QueryRunner();
	Connection conn = JDBCUtils.getConnection3();
	String sql = "insert into customers(name,email,birth)values(? ,? ,?) ";
	int count = runner.update(conn, sql, "He Chengfei"."[email protected]"."1992-09-08");

	System.out.println("Added" + count + "One record");
		
	JDBCUtils.closeResource(conn, null);

}
Copy the code
// Test delete
@Test
public void testDelete(a) throws Exception {
	QueryRunner runner = new QueryRunner();
	Connection conn = JDBCUtils.getConnection3();
	String sql = "delete from customers where id < ?";
	int count = runner.update(conn, sql,3);

	System.out.println("Deleted" + count + "One record");
		
	JDBCUtils.closeResource(conn, null);

}
Copy the code

9.2.3 ResultSetHandler Interface and implementation class

  • This interface is used to process java.sql.ResultSet, transforming the data into another form as required.

  • The ResultSetHandler interface provides a separate method: Object Handle (java.sql.ResultSet rs).

  • The main implementation classes of the interface:

    • ArrayHandler: Converts the first row of data in the result set into an array of objects.

    • ArrayListHandler: Convert each row of the result set into an array and store it in a List.

    • BeanHandler: Encapsulates the first row of data in the result set into a corresponding JavaBean instance.

    • BeanListHandler: Encapsulates each row in the result set into a corresponding JavaBean instance, stored in the List.

    • ColumnListHandler: Stores the data of a column in the result set to the List.

    • KeyedHandler(name) : Encapsulates each row of data in the result set into a Map and stores these maps in another Map. The key is the specified key.

    • MapHandler: Encapsulates the first row of data in the result set into a Map. Key is the column name and value is the corresponding value.

    • MapListHandler: Encapsulates each row in the result set into a Map, which is then stored in a List

    • ScalarHandler: Queries a single value object

  • test

/* * Test query: query a record * * use ResultSetHandler implementation class: BeanHandler */
@Test
public void testQueryInstance(a) throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id = ?";
		
	BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
	Customer customer = runner.query(conn, sql, handler, 23);
	System.out.println(customer);	
	JDBCUtils.closeResource(conn, null);
}
Copy the code
/* * Test query: query a collection of multiple records * * Use the implementation class BeanListHandler */
@Test
public void testQueryList(a) throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id < ?";
		
	//
	BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
	List<Customer> list = runner.query(conn, sql, handler, 23);
	list.forEach(System.out::println);
		
	JDBCUtils.closeResource(conn, null);
}
Copy the code
/* * Custom ResultSetHandler implementation class */
@Test
public void testQueryInstance1(a) throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id = ?";
		
	ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {

		@Override
		public Customer handle(ResultSet rs) throws SQLException {
			System.out.println("handle");
// return new Customer(1,"Tom","[email protected]",new Date(123323432L));
				
			if(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date birth = rs.getDate("birth");
					
				return new Customer(id, name, email, birth);
			}
			return null; }}; Customer customer = runner.query(conn, sql, handler,23);
		
	System.out.println(customer);
		
	JDBCUtils.closeResource(conn, null);
}
Copy the code
/* * How to query data like Max, min, average, sum, number, * using ScalarHandler * */
@Test
public void testQueryValue(a) throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	// Test 1:
// String sql = "select count(*) from customers where id < ?" ;
//	ScalarHandler handler = new ScalarHandler();
//	long count = (long) runner.query(conn, sql, handler, 20);
//	System.out.println(count);
		
	// Test 2:
	String sql = "select max(birth) from customers";
	ScalarHandler handler = new ScalarHandler();
	Date birth = (Date) runner.query(conn, sql, handler);
	System.out.println(birth);
		
	JDBCUtils.closeResource(conn, null);
}
Copy the code

JDBC summary

@Test
public void testUpdateWithTx(a) {
		
	Connection conn = null;
	try {
		//1. Obtain the connection operation (
		// for a handwritten connection: jdbCutils.getConnection ();
		// select * from C3P0; DBCP; Druid
		//2. Perform a series of CRUD operations on the data table
		PreparedStatement (Version 1.0 \ Version 2.0)
Public void update(Connection CONN,String SQL,Object... args){}
Public 
      
        T getInstance(Connection conn,Class
       
         clazz,String SQL,Object... args){}
       
		//② Use the QueryRunner class provided in the jar package provided by dbutils
			
		// Submit data
		conn.commit();
			
	
	} catch (Exception e) {
		e.printStackTrace();
			
			
		try {
			// Roll back the data
			conn.rollback();
		} catch(SQLException e1) { e1.printStackTrace(); }}finally{
		//3. Close the connection
		/ / 1) JDBCUtils closeResource ();
		// the dbutils class is provided in the jar package provided by dbutils}}Copy the code