1. What is Spring JdbcTemplate

1. Spring JdbcTemplate, also known as Spring JDBC module, is mainly responsible for database resource management and error detection to simplify the operation of the database.

2. Spring JdbcTemplate is an object provided in the spring framework. It is a simple encapsulation of the original cumbersome JDBC API object. The spring framework provides us with many operation template classes. For example: JdbcTemplate and HibernateTemplate for operating relational data, RedisTemplate for operating nosql database, JmsTemplate for operating message queue, etc.

3. JdbcTemplate is not the only way to access the database. There are NamedParameterJdbcTemplate, SimpleJdbcInsert and SimpleJdbcCall, and RDBMS objects (including MappingSqlQuery, SqlUpdate, and StoredProcedure). The JdbcTemplate is the classic and most popular Spring JDBC method.

4. Spring's JDBC module consists of four different packages:

   Package name	explain
   core	Contains the core package of JDBC, including JdbcTemplate class, NamedParameterJdbcTemplate class, SimpleJdbcInsert class and SimpleJdbcCall class
   datasource	The tool class used to access data sources, which has the implementation of multiple data sources, can test JDBC code outside the Java EE container
   object	Access the database in an object-oriented way. It allows you to execute queries and take the returned results as business objects. You can directly map the query results in the columns of the data table and the attributes of the business objects.
   support	Contains support classes for core and object packages

2. Use JdbcTemplate

The development steps of JdbcTemplate include:

1. Packages to import spring JDBC and spring TX:

   <!--Import spring of jdbc coordinate-->
   <dependency>  
      <groupId>org.springframework</groupId>  
      <artifactId>spring-jdbc</artifactId>
      <version>5.0.5.RELEASE</version>
   </dependency>
   <!--Import spring of tx coordinate-->
   <dependency>  
       <groupId>org.springframework</groupId> 
       <artifactId>spring-tx</artifactId> 
       <version>5.0.5.RELEASE</version>
   </dependency>
   

2. Create database tables and entities

3. Create a JdbcTemplate object: there are two ways to create a JdbcTemplate object: directly or through IOC.

   //1. Configure data source
   DriverManagerDataSource dataSource = new DriverManagerDataSource();
   dataSource.setDriverClassName("com.mysql.jdbc.Driver");
   dataSource.setUrl("jdbc:mysql://localhost:3306/spring");
   dataSource.setUsername("root");
   dataSource.setPassword("root");
   //Create a JdbcTemplate object
   JdbcTemplate jdbcTemplate = new JdbcTemplate();
   // 3. Set data source to JdbcTemplate
   jdbcTemplate.setDataSource(dataSource);
   

   This method is to instantiate the JdbcTemplate object, configure the data source for the object, and then perform database operations through the object. The following is completed by configuring the XML when the creation rights of the data source and the JdbcTemplate object are handed over to the IOC container:

    <!--Configure data sources-->
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/spring"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
   </bean>
     <!--to configure JdbcTemplate object-->
   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
   </bean>
   

   The above code configures two classes through XML. The specific calls are as follows:

   ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContent.xml");
   //Create the JdbcTemplate object. The data source has been configured to the object during the creation process
   JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate",         JdbcTemplate.class);  
   

4. Execute database operation: after creating the object, we need to use the object for CRUD operation. The details are as follows:

2. Specific database operation

1. Add, delete, and delete using the JdbcTemplate (INSERT, UPDATE, and DELETE): This is done through a series of updata() methods in the JdbcTemplate object. The common methods are as follows:

   method	explain
   int update(String sql)	This method is relatively simple. It directly executes the incoming sql statement and returns the number of rows affected by the search
   int update(String sql，PreparedStatementSetter pss)	This method sets the parameters in the sql statement through PreparedStatementSetter and returns the affected rows
   int update(PreparedStatementSetter pss)	This method executes PreparedStatementSetter and returns the affected rows
   int update(String sql,Object... args)	This method uses Object... To set the parameters in the sql statement, and the parameters are not NULL, and returns the affected rows

   //Continue to create the JdbcTemplate object above
   int num1 =jdbcTemplate.update("insert into user (name,age) values (?,?)", "zhangsan", 18);
   int num2 =jdbcTemplate.update("update into user  set age=? where name=?", "zhangsan", 18);
   int num3 =jdbcTemplate.update("delete from user where name=?","zhangsan");
   

2. Query using JdbcTemplate: it is completed through a series of query() methods in the JdbcTemplate object. Because there are too many methods, we can only remember the commonly used methods:

   //Query all
       List<User> userList = jdbcTemplate.query("select * from user", new BeanPropertyRowMapper<User>(User.class));
       //Query by criteria
       List<User> userList1 = jdbcTemplate.query("select * from user where age=? ", new BeanPropertyRowMapper<User>(User.class), "18");
       //Query a piece of data
       User user = jdbcTemplate.queryForObject("select * from user where age=? ", new BeanPropertyRowMapper<User>(User.class), "18");
       //Query a row of any type of data, and the last parameter specifies the return result type
       Integer num = jdbcTemplate.queryForObject("select count(*) from user", Integer.class);
       //Query a row of data and convert the row of data into Map return
       Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap("select * from user", new BeanPropertyRowMapper<User>(User.class));
   
   

Here is a brief explanation of the JdbcTemplate. Many contents of the JdbcTemplate are not explained, but simply describes the basic concepts and some basic usages, so as to pave the way for future learning.