Detailed explanation of Spring JdbcTemplate

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 nameexplain
    coreContains the core package of JDBC, including JdbcTemplate class, NamedParameterJdbcTemplate class, SimpleJdbcInsert class and SimpleJdbcCall class
    datasourceThe tool class used to access data sources, which has the implementation of multiple data sources, can test JDBC code outside the Java EE container
    objectAccess 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.
    supportContains 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-->
    <!--Import spring of tx coordinate-->
  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();
    //Create a JdbcTemplate object
    JdbcTemplate jdbcTemplate = new JdbcTemplate();
    // 3. Set data source to JdbcTemplate

    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"/>
      <!--to configure JdbcTemplate object-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
         <property name="dataSource" ref="dataSource"/>

    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:
    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.

Keywords: JDBC sping

Added by tekkenlord on Thu, 20 Jan 2022 05:06:28 +0200