preface

1. Review the established development rules and DAO mode—— Development standards introduced on Oracle official website (browse)
2. What is JDBC
3. Defects of JDBC
4. Birth of ORM
5. ORM integration efforts

1. JDBC

1.1 what is JDBC

The following contents are sorted to Baidu

JDBC (Java Database Connectivity) is an application program interface used in Java language to regulate how client programs access databases.
The JDBC API is mainly located in Java. Net in the JDK SQL package (the extended content is located in javax.sql package)

Specific specifications include:

• The Driver will load itself into the DriverManager.
• The Driver manager is responsible for loading various drivers and returning the corresponding database Connection to the caller according to different requests
• Connection database is a connection environment in which database interaction can be realized
• Statement is used to execute SQL queries and updates (for static SQL statements and single execution)
• PreparedStatement is used to execute SQL queries and updates containing dynamic parameters (compiled on the server side, allowing repeated execution to improve efficiency)
• SQLException means that an exception (i.e. error) occurred during the establishment and closing of database connection and the execution of SQL statement
  Note that JDBC 2 is now the mainstream 0 standard, that is, javax sql. Datasource replaces using DriverManager to get database connections

1.2 database driver

• MySQL driver managed by Maven

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.25</version>
</dependency> 

• Differentiated hardware drivers
  Why do independent graphics cards need drivers? Because the graphics card manufacturer wants to tell the operating system: "I'm strong. Please identify my advantages. The CPU can hand over the rendering work to me. Please use these API s when you need me". The operating system can well recognize and control the big guy of independent graphics card. The driver of hardware is the software higher than the operating system, which is sandwiched between the application and the operating system.
• What is database driver
  In the Java ecosystem, we are used to making MySQL connector java a Driver, or because its core class conforms to the Driver specification of JDBC, and the Driver is translated as a Driver. But different from the hardware Driver, the database Driver here is essentially application software. What is important to use navicat or other tools to operate MySQL—— connect. After entering the user name and password, navicat helps you connect to the database. If in a java program, the properties or yml file provides url, password and other information, what does MySQL connector java do—— connect. Therefore, it can be considered that the database Driver is the connection manager of the database.
• Relationship between MySQL connector Java and JDBC
  MySQL connector Java is a database connection manager that MySQL manufacturers follow the JDBC standard for programmers to use in their programs
  Therefore, when the driver is registered, the programmer can only use the JDBC oriented interface programming.

1.3 Spring integrated database connection pool

Spring's data access framework adopts JDBC 2 in the management of database resources 0 standard javax sql. Datasource is used for database connection. Since the addition and destruction of database connections are resource intensive and time-consuming, it is necessary to pool the connections. The specific ideas are as follows:

• Follow the DataSource interface of JDBC to create the implementation class A, and use this implementation class to obtain the connection
• connection. After close(), the database connection is not released but returned to the connection pool
• Use A to obtain database connection in the future
  The third point of the above idea is that it is best to give the Spring IOC container to provide the service, so the simplest way to make the application integration connection pool is to configure a bean with Spring and inject it when it is used. (id of this bean = 'dataSource' is used to override the default implementation of Spring)

    <!-- 1.load jdbc.properties File location -->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    
    <!-- 2.to configure druid Connection pool, id Is a fixed value, class yes druid Full path of connection pool class -->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <!-- Configure basic information for connecting to the database -->
        <property name="driverClassName" value="${db.driverClassName}"></property>
        <property name="url" value="${db.url}"></property>
        <property name="username" value="${db.username}"></property>
        <property name="password" value="${db.password}"></property>
    </bean>

The default configuration of Spring Boot is HikariCP connection pool

1.4 using JDBC to interact with database

With JDBC and database driver, you can use java program to interact with database. I picked a piece of code from the Internet: insert data

private void insert(Person p) {
		Connection conn = null;
		Statement stmt = null;
		// Connect to database url
		String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8";
		// Database user name
		String user = "root";
		// Database password
		String password = "root";
		try {
			// 1. Load drive
			Class.forName("com.mysql.jdbc.Driver");
			// 2. Create data connection object
			conn = DriverManager.getConnection(url, user, password);
			// 3. Create Statement object
			stmt = conn.createStatement();
			// Splicing sql
			String sql = "insert into t_person(name,age)values('" + p.getName()
					+ "','" + p.getAge() + "')";
			// Perform add operation
			stmt.executeUpdate(sql);	
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {// close database
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

2. Birth of ORM

The emergence of JDBC provides the convenience of accessing the database, but there is still a gap between object-oriented programming and object-oriented expression. The emergence of ORM is to eliminate these barriers as much as possible. It is worth mentioning that ORM is an idea, not unique to Java. See the following code to understand how the ORM encapsulates sql statements into objects.

public Double calcAmount(String customerid, double amount) 
{
    Customer customer = CustomerManager.getCustomer(custmerid); 
    // Get discount rules according to customer level
    Promotion promotion = PromotionManager.getPromotion(customer.getLevel()); 
    // Accumulate the total consumption of customers and save the cumulative results
    customer.setSumAmount(customer.getSumAmount().add(amount); 
    CustomerManager.save(customer); 
    return amount.multiply(protomtion.getRatio()); 
}

3. Spring integrated ORM framework

3.1 JdbcTemplate

To say that Spring integrates the ORM framework, we must first mention the JdbcTemplate formed by Spring encapsulating jdbc (remember this naming method, which also means the RabbitMqTemplate, RedisTemplate and RestTemplate provided by Spring in the later stage).

• Spring encapsulation template code
  Note the native JDBC implementation:

 		catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {// close database
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

Since you have to bring this large section with you every time you write code to access the database, why not use it Template method design pattern Package.

• Spring refines the semantics of SQLException
  SQLException in the JDBC standard has a relatively broad semantics. Different database manufacturers will throw SQLException in the method or represent different meanings. When the program troubleshooting, it looks confused, and all mistakes are SQLException. And SQLException is a checked Exception. Java's constraint on checked Exception is: the current exception is thrown, and the upper layer chooses to catch or continue to throw. Spring, as a model worker, looks at the source code and converts the vague SQLException into various well-known unchecked exceptions, that is, subclasses of the so-called RuntimeException. After being thrown, the upper layer can not capture it. Eg:

1. InvalidDataAccessApiUsageException invalid data access API usage exception
2. DataRetrievalFailureException data acquisition exception
3. DeadlockLoserDataAccessException deadlock
4. Dataintegrity violationexception consistency check exception

• Combining the two features, the JdbcTemplate supports the following wording (ignoring the code of dependency injection)

String sql="insert into user (name,deptid) values (?,?)";
List<Object[]> batchArgs=new ArrayList<Object[]>();
batchArgs.add(new Object[]{"caoyc",6});
batchArgs.add(new Object[]{"zhh",8});
batchArgs.add(new Object[]{"cjx",8});
jdbcTemplate.batchUpdate(sql, batchArgs);

3.2 Hibernate and MyBatis

Spring integrates Hibernate and MyBatis, which are similar to the idea of JdbcTemplate

1. Unified resource management
2. Translation of Spring exception management
3. Transaction management and control (this content is relatively large and will be updated later)