JDBCTemplate

JDBC template (concept and preparation)

1. What is a JdbcTemplate

   1. The Spring framework encapsulates JDBC and uses JdbcTemplate to facilitate database operation

2. preparation

   1. Introduce related jar packages

      [the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-noywzana-163884493137) (C: \ users \ master \ appdata \ roaming \ typora \ typora user images \ image-20211204205952748. PNG)]

   2. Database connection pool

          <!--Database connection pool-->
          <bean id="dateSource" class="com.alibaba.druid.pool.DruidDataSource" destroy-method="close">
              <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
              <!--Note here to use utf8 code-->
              <property name="url" value="jdbc:mysql://localhost:3306/dxz?characterEncoding=utf8"></property>
              <property name="username" value="root"></property>
              <property name="password" value="DXZDXZ4811"></property>
          </bean>
      

   3. Configure the JdbcTemplate object and inject DataSource

          <!--establish jdbcTemplate object-->
          <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
              <!--injection dataSource object-->
              <property name="dataSource" ref="dateSource"></property>
          </bean>
      

   4. Create a service class, create dao class, and inject JDBC template object into dao

      Profile:

          <!--Component scan-->
          <context:component-scan base-package="com.dxz.demo.book"></context:component-scan>
      

      source:

      @Service
      public class BookSource {
          @Autowired
          private BookImp bookImp;
      }
      
      

      BookImp:

      @Component
      public class BookImp implements Book{
          @Autowired
          private JdbcTemplate jdbcTemplate;
      }
      

3. JdbcTemplate operation database (add)

   1. Create entity class corresponding to database

   2. Write service and dao

      1. Add database in Book

      2. Call the updata method in the JdbcTemplate object to implement the add operation

         This method has two parameters:

         The first parameter is sql statement, and the second parameter is variable parameter, which sets the value of sql statement

         @Component
         public class BookImp implements Book{
             @Autowired
             private JdbcTemplate jdbcTemplate;
         
             public void add(User user){
                 String sql1 = "insert into spring values(?,?,?);";
                 Object[] args = {user.getUserId(),user.getUserName(),user.getUserStatus()};
                 int result = jdbcTemplate.update(sql1,args);
                 System.out.println(result);
             }
         }
         

      3. Complete the operation of adding data in BookSource

         @Service
         public class BookSource {
             @Autowired
             private BookImp bookImp;
         
             public void bookAdd(User user){
                 bookImp.add(user);
             }
         }
         

4. JdbcTemplate operation database (modification and deletion)

   1. Add, delete and modify two methods in the Book interface

      public interface Book {
          //Add method
          public void add(User user);
          //Modification method
          public void update(User user);
          //Delete method
          public void delete(String id);
      }
      

   2. Override, delete, and modify the two methods in the BookImp implementation class

          //Modification method
          public void update(User user){
              String sql2 = "update spring set name=?,status=? where id=?;";
              Object[] args = {user.getUserName(),user.getUserStatus(),user.getUserId()};
              int result = jdbcTemplate.update(sql2,args);
              System.out.println(result);
          }
          //Delete method
          public void delete(String id) {
              String sql3 = "delete from spring where id=?";
              int result = jdbcTemplate.update(sql3,id);
              System.out.println(result);
          }
      

   3. Add, remove, and modify in the BookSource class

          //Database modification
          public void bookUpdate(User user){
              bookImp.update(user);
          }
          //Delete operation of database
          public void bookDelete(String id){
              bookImp.delete(id);
          }
      

5. JdbcTemplate operation database (query)

   1. The query returns a value

      //Number of all data returned by query
          @Override
          public int selectCountBook() {
              String sql4 = "select count(*) from spring";
              int result = jdbcTemplate.queryForObject(sql4,Integer.class);
              System.out.println(result);
              return result;
          }
      

   2. Query return object

      1. Scenario: query book details

      2. JdbcTemplate implements query return

         [the external chain image transfer fails. The source station may have an anti-theft chain mechanism. It is recommended to save the image and upload it directly (img-riaguf5p-163884493138) (C: \ users \ master \ appdata \ roaming \ typora \ typora user images \ image-20211205110020838. PNG)]

         There are three parameters in total

         (1) sql statement.

         (2) RowMapper is an interface that uses the implementation classes in the interface to complete data encapsulation for returning different types of data

         (3) sql statement value

         *Note that the field names in the database must be the same as those in java

             @Override
             public User selectObjectBook(String id) {
                 String sql5 = "select * from spring where id=?";
                 User user = jdbcTemplate.queryForObject(sql5, new BeanPropertyRowMapper<User>(User.class),id);
                 return user;
             }
         

   3. Query return collection

      1. Scenario: query book list Pagination

      2. Call the JdbcTemplate method to implement the query return collection

         The called method is query, and the return value is the list collection

         There are three parameters in total

         (1) sql statement.

         (2) RowMapper is an interface that uses the implementation classes in the interface to complete data encapsulation for returning different types of data

         (3) sql statement value

6. JdbcTemplate operation database (batch operation)

   1. Batch operation refers to multiple records in the operation table

   2. JdbcTemplate implements batch addition

      batchUpdate(String sql,List<Object[]> batchArgs)
      

      There are two parameters

      First parameter: sql statement

      The second parameter: List set, adding multiple records

          @Override
          public void adds() {
              String sql7 = "insert into spring values(?,?,?);";
              List<Object[]> listBatch = new ArrayList<>();
              Object[] o1 = {"3","java","a"};
              Object[] o2 = {"4","c++","b"};
              Object[] o3 = {"5","mysql","c"};
              listBatch.add(o1);
              listBatch.add(o2);
              listBatch.add(o3);
              int[] result = jdbcTemplate.batchUpdate(sql7,listBatch);
              System.out.println(Arrays.toString(result));
          }
      

istBatch.add(o1);
listBatch.add(o2);
listBatch.add(o3);
int[] result = jdbcTemplate.batchUpdate(sql7,listBatch);
System.out.println(Arrays.toString(result));
}
```