3.6,Spring JDBC Template

3.6.1, overview of JdbcTemplate

It is an object provided in the spring framework and is a simple encapsulation of the original cumbersome jdbc API objects. The spring framework provides us with a number of operation template classes. For example, Jdbc Template and Hibernate Template for manipulating relational data, Redis Template for manipulating Nosql databases, Jms Template for manipulating message queues, and so on.

3.6.2, Jdbc Template development steps

1. Importing spring-jdbc and spring-tx dependencies

(2) Creating data tables and entities

(3) Create Jdbc Template object

(4) Perform database operations

<!--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>

Create a websites database, websites tables and entities

@Data
@AllArgsConstructor
@NoArgsConstructor
public class website {
    private int id;
    private String name;
    private String url;
    private int alexa;
    private String country;
}

public class JdbcTemplateTest {

    @Test
    public void test1() throws PropertyVetoException {

        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/websites");
        //&serverTimezone=GMT%2B8&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        dataSource.setUser("root");
        dataSource.setPassword("admin");
        JdbcTemplate jdbcTemplate = new JdbcTemplate();

        jdbcTemplate.setDataSource(dataSource);
        int row = jdbcTemplate.update("insert into websites values (?,?,?,?,?)",7,"Baidu","https:///www.baidu.com",3,"cn");

        System.out.println(row);


    }
}

3.6.3, spring produces JdbcTmeplatform objects

We can give Spring the creation of the JdbcTemplate, Spring the creation of the data source DataSource, and inject the data source DataSource into the JdbcTemplate template object inside the Spring container with the following configuration:

<!--data source DataSource--> 
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"> 
    <property name="driverClass" value="com.mysql.cj.jdbc.Driver"></property> 
    <property name="jdbcUrl" value="jdbc:mysql:///websites"></property> 
    <property name="user" value="root"></property> 
    <property name="password" value="admin"></property>
</bean>
<!--JdbcTemplate--> 
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> 
    <property name="dataSource" ref="dataSource"></property>
</bean>

@Test
public void test2(){
    ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
    JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
    int update = jdbcTemplate.update("insert into websites values(?,?,?,?,?)", 8, "Big-ear Tutu", "good-omen.top", 2, "cn");
    System.out.println(update);
}

Encapsulate the four parameters of jdbc:

Create the jdbc.properties folder and introduce it into the applicationContext.xml file.

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/websites
jdbc.username=root
jdbc.password=admin

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context.xsd">

    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!--data source DataSource-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>
    <!--JdbcTemplate-->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

</beans>

	@Test
    public void test2(){
        ApplicationContext app = new ClassPathXmlApplicationContext("applicationContext.xml");
        JdbcTemplate jdbcTemplate = app.getBean(JdbcTemplate.class);
        int update = jdbcTemplate.update("insert into websites values(?,?,?,?,?)", 9, "Big-ear Tutu", "good-omen.top", 2, "cn");
        System.out.println(update);
    }

Common operations for 3.6.4, JdbcTemplate

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:applicationContext.xml")
public class JdbcTemplateCRUDTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    public void testUpdate(){

        jdbcTemplate.update("update websites set name = ? where name = ?","Diagram Learning Park","Big-ear Tutu");

    }

    @Test
    public void testDelete(){
        jdbcTemplate.update("delete from account where name=?","Diagram Learning Park");
    }

    @Test
    public void testQueryAll(){
        List<Website> websites = jdbcTemplate.query("select * from website", new BeanPropertyRowMapper<Website>(Website.class));
        for (Website website : websites) {
            System.out.println(website.toString());
        }
    }

    @Test
    //Test Query Single Object Operation
    public void testQueryOne(){
        Website website = jdbcTemplate.queryForObject("select * from Website where name=?", new BeanPropertyRowMapper<Website>(Website.class), "Baidu");
        System.out.println(website.getName());
    }
    @Test
    //Test Query Single Simple Data Operation (Aggregate Query)
    public void testQueryCount(){
        Long aLong = jdbcTemplate.queryForObject("select count(*) from Website", Long.class);
        System.out.println(aLong);
    }

}

3.7, SpringMVC Interceptor

3.7.1. interceptor Role

The Interceptor for Spring MVC is similar to the Filter in Servlet development for pre-processing and post-processing processors.

Interceptor chains are chains of interceptors in a certain order. When accessing an intercepted method or field, the interceptors in the Interceptor Chain are called in the order they were previously defined. Interceptors are also the concrete implementation of AOP.

Difference	Filter	Interceptor
Scope of use	Is part of the servlet specification and can be used by any Java web project	Is the springmvc framework itself, only projects that use the springmvc framework can use it
Interception Scope	After configuring /* in url-pattern, you can intercept all resources you want to access	After configuring /** in <mvc:mapping path= "" />, all resources can also be intercepted, but resources that do not need to be intercepted can be excluded by the <mvc:exclude-mapping path= "" /> tag

3.7.2, Quick Start for Interceptors

Custom interceptors:

1. Create interceptor class to implement HandleInterceptor interface

(2) Configuring interceptors

(3) Testing the interception effect of interceptors

import org.springframework.web.servlet.HandlerInterceptor;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * @Author tutu
 * @Date 2021/11/6
 * @Version 1.0
 */
public class MyInterceptor1 implements HandlerInterceptor {
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {

        System.out.println("preHandle running...");
        return true;
    }

    @Override
    public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView) throws Exception {

        System.out.println("postHandle...");
    }

    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {

        System.out.println("afterCompletion...");
    }
}

<!--spring-mvc.xml Configure Interceptors in    -->
<mvc:interceptors>
    <mvc:interceptor>
        <mvc:mapping path="/**"/>
        <bean class="com.spongebob.config.MyInterceptor1"/>
    </mvc:interceptor>
</mvc:interceptors>

@RequestMapping("quick24")
@ResponseBody
public ModelAndView quickMethod24(){
    System.out.println("Target Method Execution...");
    ModelAndView modelAndView = new ModelAndView();
    modelAndView.addObject("name","spongebob");
    modelAndView.setViewName("index");
    return modelAndView;
}

http://localhost:8080/spring_06_mvc_war_exploded/quick24

3.7.3, Multiple Interceptor Operations

As above, write another MyInterceptor2 operation to test the execution order.

Interceptor method description:

Method Name	Explain
preHandle()	The method is called before the request is processed. The return value of the method is of Boolean type. When it returns to false, the request ends and subsequent Interceptors and Controller s will not execute. When the return value is true, the call to the next Interceptor's preHandle method continues.
postHandle()	This method is called after the current request has been processed, provided that the return value of the preHandle method is true and it will be called before the Dispatcher Servlet renders the view return, so we can manipulate the ModelAndView object after the controller has been processed in this method.
afterHandle()	This method will execute after the entire request ends, that is, after the Dispatcher Servlet renders the corresponding view, provided the return value of the preHandle method is true.