SpringJdbcTemplate&Jdbc整合&SpringMVC

╰半橙微兮° 2021-09-25 11:52 379阅读 0赞

### 文章目录 ###

*  JdbcTemplate
 *  JdbcTemplate+Spring+Servlet
 *  SpringMVC

# JdbcTemplate #

（1）导入依赖

<dependencies>
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.3.4</version>
            </dependency>
    
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-jdbc</artifactId>
                <version>5.3.4</version>
            </dependency>
    
    
            <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-orm</artifactId>
                <version>5.3.4</version>
            </dependency>
    
    
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.13.2</version>
            </dependency>
    
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.23</version>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid</artifactId>
                <version>1.1.10</version>
            </dependency>
    
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
                <version>1.9.6</version>
            </dependency>
    
            
        </dependencies>

（2）db.properties

jdbc.driver=com.mysql.cj.jdbc.Driver
    jdbc.url=jdbc:mysql:///aa?serverTimezone=GMT%2B8
    jdbc.user=root
    jdbc.password=123456

（3）applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" 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 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd ">
    <context:component-scan base-package="com.tjetc"/>
        <context:property-placeholder location="classpath*:db.properties"/>
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
            <property name="driverClassName" value="${jdbc.driver}"></property>
            <property name="url" value="${jdbc.url}"></property>
            <property name="username" value="${jdbc.username}"></property>
            <property name="password" value="${jdbc.password}"></property>
        </bean>
        
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="dataSource"/>
        </bean>
        
        <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"/>
        </bean>
    
        
        <aop:config>
            <aop:pointcut id="mycut" expression="execution(* com.tjetc.service..*.*(..))"></aop:pointcut>
            <aop:advisor advice-ref="txAdvice" pointcut-ref="mycut"/>
        </aop:config>
        
        <tx:advice id="txAdvice" transaction-manager="txManager">
            <tx:attributes>
                <tx:method name="add*" propagation="REQUIRED"/>
                <tx:method name="update*" propagation="REQUIRED"/>
                <tx:method name="del*" propagation="REQUIRED"/>
                <tx:method name="*" read-only="true"/>
            </tx:attributes>
        </tx:advice>
    </beans>

（4）

package com.tjetc.domain;
    
    public class User { 
        private int id;
        private String username;
        private String password;
    
        @Override
        public String toString() { 
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    '}';
        }
    
        public User() { 
        }
    
        public User(String username, String password) { 
            this.username = username;
            this.password = password;
        }
    
        public User(int id, String username, String password) { 
            this.id = id;
            this.username = username;
            this.password = password;
        }
    
        public int getId() { 
            return id;
        }
    
        public void setId(int id) { 
            this.id = id;
        }
    
        public String getUsername() { 
            return username;
        }
    
        public void setUsername(String username) { 
            this.username = username;
        }
    
        public String getPassword() { 
            return password;
        }
    
        public void setPassword(String password) { 
            this.password = password;
        }
    }

（5）

package com.tjetc.service;
    
    import com.tjetc.domain.User;
    
    import java.util.List;
    
    public interface UserService { 
        void add(User user);
        void update(User user);
        void del(Integer id);
        User findById(Integer id);
        List<User> list(String username);
        int count(String username);
    }

（6）

package com.tjetc.service.impl;
    
    import com.tjetc.domain.User;
    import com.tjetc.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    @Service
    public class UserServiceImpl implements UserService { 
        @Autowired
        private JdbcTemplate jdbcTemplate;
        public void add(User user){ 
            jdbcTemplate.update("insert into user (username,password) values(?,?)",user.getUsername(),user.getPassword());
        }
    
        public void update(User user) { 
            jdbcTemplate.update("update user set username = ? ,password = ? where id = ?",user.getUsername(),user.getPassword(),user.getId());
        }
    
        public void del(Integer id) { 
            jdbcTemplate.update("delete from user where id = ?",id);
        }
    
        public User findById(Integer id) { 
            return jdbcTemplate.queryForObject("select * from user where id = ?",new BeanPropertyRowMapper<>(User.class),id);
        }
    
        public List<User> list(String username) { 
            return jdbcTemplate.query("select * from user where name like ?",new BeanPropertyRowMapper<>(User.class),"%"+username+"%");
        }
    
        public int count(String username) { 
            return jdbcTemplate.queryForObject("select count(*) from user where username like ?",int.class,"%"+username+"%");
        }
    }

（7）

package com.tjetc.test;
    
    import com.tjetc.domain.User;
    import com.tjetc.service.UserService;
    import org.junit.Test;
    import org.springframework.context.support.ClassPathXmlApplicationContext;
    
    import java.util.List;
    
    public class Test1 { 
        @Test
        public void testAdd(){ 
            ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
            UserService userServiceImpl = (UserService) context.getBean("userServiceImpl");
            userServiceImpl.add(new User("zs2","123"));
        }
    
        @Test
        public void testUpdate(){ 
            ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
            UserService userServiceImpl = (UserService) context.getBean("userServiceImpl");
            userServiceImpl.update(new User(1,"zs2","123"));
        }
    
    
        @Test
        public void testDel(){ 
            ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
            UserService userServiceImpl = (UserService) context.getBean("userServiceImpl");
            userServiceImpl.del(1);
        }
    
        @Test
        public void testFindById(){ 
            ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
            UserService userServiceImpl = (UserService) context.getBean("userServiceImpl");
            User user = userServiceImpl.findById(1);
            System.out.println("user = " + user);
        }
    
        @Test
        public void testList(){ 
            ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
            UserService userServiceImpl = (UserService) context.getBean("userServiceImpl");
            List<User> list = userServiceImpl.list("z");
            for (User user : list) { 
                System.out.println("user = " + user);
            }
        }
    
    
        @Test
        public void testCount(){ 
            ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
            UserService userServiceImpl = (UserService) context.getBean("userServiceImpl");
            int count = userServiceImpl.count("z");
            System.out.println("count = " + count);
        }
    
    
    
    }

# JdbcTemplate+Spring+Servlet #

（1）导入依赖

<dependencies>
    
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.4</version>
        </dependency>
    
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.3.4</version>
        </dependency>
    
    
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.4</version>
        </dependency>
    
    
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.3.4</version>
        </dependency>
    
    
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
        </dependency>
    
    
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.23</version>
        </dependency>
    
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.10</version>
        </dependency>
    
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.9.6</version>
        </dependency>
    
        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
    
    </dependencies>

（2）

jdbc.driver=com.mysql.cj.jdbc.Driver
    jdbc.url=jdbc:mysql:///spring0713?serverTimezone=GMT%2B8
    jdbc.username=root
    jdbc.password=123456

（3）

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" 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 http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd ">
        <context:component-scan base-package="com.tjetc"/>
        <context:property-placeholder location="classpath*:db.properties"/>
        <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
            <property name="driverClassName" value="${jdbc.driver}"></property>
            <property name="url" value="${jdbc.url}"></property>
            <property name="username" value="${jdbc.username}"></property>
            <property name="password" value="${jdbc.password}"></property>
        </bean>
        
        <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
            <property name="dataSource" ref="dataSource"/>
        </bean>
        
        <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"/>
        </bean>
    
        
        <aop:config>
            <aop:pointcut id="mycut" expression="execution(* com.tjetc.service..*.*(..))"></aop:pointcut>
            <aop:advisor advice-ref="txAdvice" pointcut-ref="mycut"/>
        </aop:config>
        
        <tx:advice id="txAdvice" transaction-manager="txManager">
            <tx:attributes>
                <tx:method name="add*" propagation="REQUIRED"/>
                <tx:method name="update*" propagation="REQUIRED"/>
                <tx:method name="del*" propagation="REQUIRED"/>
                <tx:method name="*" read-only="true"/>
            </tx:attributes>
        </tx:advice>
    </beans>

（4）

（5）

（6）

package com.tjetc.service.impl;
    
    import com.tjetc.domain.User;
    import com.tjetc.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.stereotype.Service;
    
    import java.util.List;
    
    @Service
    public class UserServiceImpl implements UserService { 
        @Autowired
        private JdbcTemplate jdbcTemplate;
        public void add(User user){ 
            jdbcTemplate.update("insert into user (username,password) values(?,?)",user.getUsername(),user.getPassword());
        }
    
        public void update(User user) { 
            jdbcTemplate.update("update user set username = ? ,password = ? where id = ?",user.getUsername(),user.getPassword(),user.getId());
        }
    
        public void del(Integer id) { 
            jdbcTemplate.update("delete from user where id = ?",id);
        }
    
        public User findById(Integer id) { 
            return jdbcTemplate.queryForObject("select * from user where id = ?",new BeanPropertyRowMapper<>(User.class),id);
        }
    
        public List<User> list(String username) { 
            return jdbcTemplate.query("select * from user where username like ?",new BeanPropertyRowMapper<>(User.class),"%"+username+"%");
        }
    
        public int count(String username) { 
            return jdbcTemplate.queryForObject("select count(*) from user where username like ?",int.class,"%"+username+"%");
        }
    }

（7）

<?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd" version="4.0">
        <context-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>classpath*:applicationContext.xml</param-value>
        </context-param>
        <listener>
            <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
        </listener>
    </web-app>

（8）

package com.tjetc.controller;
    
    import com.tjetc.domain.User;
    import com.tjetc.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.context.support.SpringBeanAutowiringSupport;
    
    import javax.servlet.ServletConfig;
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.util.List;
    
    @WebServlet("/user/*")
    public class UserServlet extends HttpServlet { 
        @Autowired
        private UserService userService;
    
        @Override
        public void init(ServletConfig config) throws ServletException { 
            SpringBeanAutowiringSupport.processInjectionBasedOnServletContext(this,config.getServletContext());
        }
    
        @Override
        protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { 
            String uri = req.getRequestURI();
            String op = uri.substring(uri.lastIndexOf("/") + 1);
            try { 
                Method method = this.getClass().getDeclaredMethod(op, HttpServletRequest.class, HttpServletResponse.class);
                method.invoke(this,req,resp);
            } catch (NoSuchMethodException e) { 
                e.printStackTrace();
            } catch (IllegalAccessException e) { 
                e.printStackTrace();
            } catch (InvocationTargetException e) { 
                e.printStackTrace();
            }
    
        }
    
    
        protected void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { 
            String username = req.getParameter("username");
            String password = req.getParameter("password");
            User user = new User(username, password);
            userService.add(user);
            resp.sendRedirect(req.getContextPath()+"/user/list");
        }
    
    
        protected void list(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { 
            String username = req.getParameter("username");
            username = username==null?"":username;
            List<User> list = userService.list(username);
            req.setAttribute("list",list);
            req.getRequestDispatcher("/list.jsp").forward(req,resp);
        }
    }

（9）

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
    <%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>Title</title>
        <base href="${pageContext.request.contextPath}/"/>
    </head>
    <body>
        <table border="1" cellspacing="0" cellpadding="0">
            <tr>
                <td>序号</td>
                <td>姓名</td>
                <td>性别</td>
            </tr>
            <c:forEach items="${list}" var="u">
                <tr>
                    <td>${u.id}</td>
                    <td>${u.username}</td>
                    <td>${u.password}</td>
                </tr>
            </c:forEach>
        </table>
    </body>
    </html>

（10）

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
    <html>
    <head>
        <title>Title</title>
        <base href="${pageContext.request.contextPath}/"/>
    </head>
    <body>
        <form action="/user/add" method="post">
            用户名:<input type="text" name="username">
            密码:<input type="text" name="password">
            <input type="submit" value="提交">
        </form>
    </body>
    </html>

# SpringMVC #

（1）

导入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>5.3.4</version>
        </dependency>
    
        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        
    </dependencies>

（2）web.xml中配置

<servlet>
            <servlet-name>springmvc</servlet-name>
            <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
            <init-param>
                <param-name>contextConfigLocation</param-name>
                <param-value>classpath*:springmvc.xml</param-value>
            </init-param>
        </servlet>
        
        <servlet-mapping>
            <servlet-name>springmvc</servlet-name>
            <url-pattern>/</url-pattern>
        </servlet-mapping>

（3） springmvc.xml

（4）HelloController.java

package com.tjetc.controller;
    
    import org.springframework.stereotype.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.RequestMapping;
    
    import javax.servlet.http.HttpServlet;
    
    //<bean id="helloController" class="com.tjetc.controller.HelloController">
    @Controller
    public class HelloController extends HttpServlet { 
        @RequestMapping("/hello")
        public String hello(Model model){ 
            System.out.println("Hello SpringMVC!");
            model.addAttribute("msg","Hello SpringMVC!");
            return "index";//请求转发到/index.jsp
        }
    }

（5）index.jsp