快速上手：用IDEA创建Spring Web项目_ssm项目搭建-优快云博客

本文链接：https://blog.youkuaiyun.com/Java721/article/details/127172374

创建Web项目

1.通过idea创建一个maven项目，选择Archetype(原型)选择org.apache.maven.archetypes:maven-archetype-webapp
在这里插入图片描述

集成Spring

2.在新项目的pom文件中添加Spring相关的依赖包

    <properties>
        <spring.version>5.0.4.RELEASE</spring.version>
    </properties>

    <dependencies>
        <!--spring start-->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-core</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aspects</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-expression</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>${spring.version}</version>
        </dependency>
        <!--spring end-->

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

3.在/src/main下创建resources目录并标记为resources，在resources目录下创建applicationContext.xml配置文件，具体代码如下:

<?xml version="1.0" encoding="UTF-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
           http://www.springframework.org/schema/beans/
spring-beans-2.5.xsd
            http://www.springframework.org/schema/tx
            http://www.springframework.org/schema/tx/spring-tx.xsd
            http://www.springframework.org/schema/context
            http://www.springframework.org/schema/context/
spring-context-2.5.xsd">
<!--在/src/main/java目录下创建package: com.cy-->
<context:component-scan base-package="com.cy"></context:component-scan>

</beans>

context:component-scan/注解：扫描base-package包或者子包下所有的Java类，并把匹配的Java类注册成Bean。这里我们设置扫描com.cy包下的所有Java类。

4.在web.xml配置文件中添加如下代码：

!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
  <display-name>Archetype Created Web Application</display-name>
  <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>

context-param:整个项目的全局变量，相当于设置了一个固定值。param-name是键(Key)，相当于参数名,param-value是值(Value)，

相当于参数值。

ContextLoaderListener:ContextLoaderListener监听器实现了ServletContextListener接口，其作用是启动Web容器时，自动装配ApplicationContext的配置信息。在web.xml文件中配置这个监听器，之后在启动容器时，就会默认执行它实现的方法。

5.最后，在/src/main/text/com.cy.test 目录下创建SpringTest测试类，具体代码如下:

@Service
public class SpringTest {
    public void testSpring(){
        //获取应用上下文
        ApplicationContext applicationContext=new ClassPathXmlApplicationContext("applicationContext.xml");
        //获取SpringTest类
        //注解如果没有指定bean的名字，默认为小写开头的类名。SpringTest，则spring返回springTest的bean名。
        SpringTest springTest= (SpringTest) applicationContext.getBean("springTest");
        //调用sayHello方法
        springTest.sayHello();
    }

    public void sayHello(){
        System.out.println("hello");
    }
}

@Service:Spring会自动扫描到@Service注解的类,并把这些类纳入进Spring容器中管理。也可以使用@Component注解，只是@Service注解更能表明该类时服务层类。
ApplicationContext容器:ApplicationContext是Spring中较高级的容器，它可以加载配置文件中定义的Bean，并将所有的Bean集中在一起，当有请求的死后分配Bean。
ClassPathXmlApplicationContext:从类路径ClassPath中寻找指定的XML配置文件，找到并装载完成Application的实例化工作，具体代码如下：

 //装载单个配置文件实例化ApplicationContext容器
 ApplicationContext cxt = new ClassPathXmlApplicationContext("applicationContext.xml");
 //装载多个配置文件实例化ApplicationContext容器
 String[] configs = {"bean1.xml", "bean2.xml", "bean3.xml"};
 ApplicationContext cxt = new ClassPathXmlApplicationContext("configs");

FileSystemXmlApplicationContext:从指定的文件系统路径中寻找指定的XML配置文件，找到并装载完成Application的实例化工作，具体代码如下：

//装载单个配置文件实例化ApplicationContext容器
FileSystemXmlApplicationContext cxt = new FileSystemXmlApplicationContext("applicationContext.xml");
//装载多个配置文件实例化ApplicationContext容器
String[] configs = {"c:/bean1.xml", "c:/bean2.xml", "c:/bean3.xml"};
FileSystemXmlApplicationContext cxt = new FileSystemXmlApplicationContext("configs");

XmlWebApplicationContext:从Web应用中中寻找指定的XML配置文件，找到并装载完成Application的实例化工作。这是为Web项目量身定制的，使用WebApplicationContextUtils类的getRequiredWebApplicationContext方法可以在JSP与Servlet中取得IOC容器的引用。

6.运行上面代码中的单元测试方法testSpring()，便可看到运行后的结果，表实Web应用成功集成了Spring框架。
在这里插入图片描述

集成Spring MVC框架

添加Maven依赖包到pom.xml文件中

<properties>
    <spring.version>5.0.4.RELEASE</spring.version>
    <javax.servlet.version>4.0.0</javax.servlet.version>
    <jstl.version>1.2</jstl.version>
</properties>
<dependency>
    <groupId>jstl</groupId>
    <artifactId>jstl</artifactId>
    <version>${jstl.version}</version>
</dependency>
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>${javax.servlet.version}</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>${spring.version}</version>
</dependency>

其次，在web.xml配置文件中添加DispatcherServlet配置

<!--配置DispatcherServlet-->
<servlet>
    <servlet-name>spring-dispatcher</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <!--配置SpringMVC需要加载的配置文件 spring-mvc.xml-->
    <init-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:spring-mvc.xml</param-value>
    </init-param>
    <load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
    <servlet-name>spring-dispatcher</servlet-name>
    <!--默认匹配所有的请求-->
    <url-pattern>/</url-pattern>
</servlet-mapping>

DispatcherServlet类：DispatcherServlet是前置控制器，主要用于拦截匹配的请求，拦截匹配规则要自己定义，把拦截下来的请求，依据相应的规则分发到目标Controller来处理，这是配置Spring MVC的第一步。
init-param：整个项目的局部变量，相当于设定了一个固定值，只能在当前的Servlet中被使用。param-name是，相当于参数名，param-value是值，相当于参数值。容器启动时会加载配置文件spring-mvc.xml。
load-on-startup：表示启动容器时初始化该Servlet。当值为0或者大于0时，表示容器在应用启动时加载并初始化这个Servlet。如果值小于或未指定时，则表示容器在该Servlet被选择时才加载。正值越小，Servlet的优先级越高，应用启动时就越先加载。值相同时，容器就会自己选择加载的顺序。
servlet-mapping：标签声明了与该Servlet相应的匹配规则，每个标签代表1个匹配规则。
url-pattern：URL匹配规则，表示哪些请求交给Sping MVC处理，“/”表示拦截所有的请求。

URL匹配规则有如下几种：

（1）精准匹配

中的配置项必须与URL完全精准匹配。

<servlet-mapping>
    <servlet-name>spring-dispatcher</servlet-name>
    <!--精准匹配-->
    <url-pattern>/cy</url-pattern>
    <url-pattern>/index.html</url-pattern>
    <url-pattern>/test/cy.html</url-pattern>
</servlet-mapping>

当在浏览器输入如下几种URL时，都会被匹配到该Servlet，具体代码如下：

http://localhost/cy
http://localhost/index.html
http://localhost/test/cy.html

（2）扩展名匹配

以“*”开头的字符串被用于扩展名匹配。

<servlet-mapping>
    <servlet-name>spring-dispatcher</servlet-name>
    <!--精准匹配-->
    <url-pattern>*.jsp</url-pattern>>
</servlet-mapping>

当在浏览器输入如下几种URL时，都会被匹配到该Servlet，具体代码如下：

http://localhost/cy.html
http://localhost/cz.html

（3）路径匹配

以“/“字符开头，并以”/*“结尾的字符串用于路径匹配。

<servlet-mapping>
    <servlet-name>spring-dispatcher</servlet-name>
    <!--精准匹配-->
    <url-pattern>/cy/*</url-pattern>>
</servlet-mapping>

当在浏览器输入如下几种URL时，都会被匹配到该Servlet，具体代码如下：

http://localhost/cy/cy.jsp
http://localhost/cy/cy.html
http://localhost/cy/action
http://localhost/cy/xxxx
http://localhost/cy/xxxxx.do

*注意：路径匹配和扩展名无法同时设置，如果设置了，启动Tomcat服务器会报错。

（4）默认匹配

<servlet-mapping>
    <servlet-name>spring-dispatcher</servlet-name>
    <!--精准匹配-->
    <url-pattern>/</url-pattern>
</servlet-mapping>

（5）匹配顺序

当一个URL与多个Servlet的匹配规则可以匹配时，则按照"精确路径>最长路径>扩展名“这样的优先级匹配到对应的Servlet。

接着，在/src/main/resources目录下创建配置文件spring-mvc.xml，具体的代码如下：

<?xml version="1.0" encoding="UTF-8" ?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xmlns:aop="http://www.springframework.org/schema/aop"
       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/mvc
        http://www.springframework.org/schema/mvc/spring-mvc.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd">
    <!--扫描base-package包或者子包下面的所有controller类，并且扫描其中的注解，并把匹配的controller类注册成Bean-->
    <context:component-scan base-package="com.cy.controller"></context:component-scan>
    <!--该注解会自动注册RequestMappingHandlerMapping和RequestMappingHandlerAdapter两个Bean，是Spring MVC为@Controller分发请求所必须的-->
    <mvc:annotation-driven></mvc:annotation-driven>
    <!--配置JSP 显示ViewResolver（视图解析器）-->
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="viewClass" value="org.springframework.web.servlet.view.JstlView"></property>
        <property name="prefix" value="/WEB-INF/views/"></property>
        <property name="suffix" value=".jsp"></property>
    </bean>

</beans>

context:component-scan：扫描base-package包或者子包下所有的controller类，并把匹配的controller类注册成Bean。
mvc:annotation-driven/：该注解会自动注册RequestMappingHandlerMapping和RequestMappingHandlerAdapter两个Bean，是Spring MVC为@Controller分发请求所必须的，并提供的数据绑定支持
InternalResourceViewResolver：最常用的视图解析器，当控制层返回”hello“时，InternalResourceViewResolver解析器会自动添加前缀和后缀，最终路径结果为：/WEB-INF/views/hello.jsp。

最后，在/src/main/java目录下创建包com.cy.controller，并创建控制层类CyTestController，具体代码如下：

package com.cy.controller;


import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
@RequestMapping("/test")
public class CyTestController {

    @GetMapping("/sayHello")
    public String sayHello(){
        return "hello";
    }
}

在/src/main/webapp/WEB-INF目录下创建views文件夹，在views文件夹下创建hello.jsp文件，具体代码如下：

<%@page language="java" contentType="text/html;charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE HTML>
<html>
<head>
    <title>Getting started:Serving Web Content</title>
    <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"/>
</head>
<body>
hello,world!
</body>
</html>

至此，Web项目集成Spring MVC大功告成。把项目部署到Tomcat服务器上，成功启动服务器后，在浏览器输入访问路径：http://localhost:8080/test/sayHello。当出现如图所示的结果，代表Web项目成功集成了Spring MVC。
在这里插入图片描述

集成Mybatis框架

把集成Mybatis框架所需要的依赖包添加到pom.xml文件中，具体代码如下：

<properties>
    <spring.version>5.0.4.RELEASE</spring.version>
    <javax.servlet.version>4.0.0</javax.servlet.version>
    <jstl.version>1.2</jstl.version>
    <mybatis.version>3.4.6</mybatis.version>
    <mysql.connector.java.version>8.0.29</mysql.connector.java.version>
    <druid.version>1.1.9</druid.version>
    <mybatis.spring.version>1.3.2</mybatis.spring.version>
</properties>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.connector.java.version}</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>${druid.version}</version>
</dependency>
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${spring.version}</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>${mybatis.version}</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>${mybatis.spring.version}</version>
</dependency>

其次，在/src/main/resources目录下创建jdbc.properties配置文件，具体的代码如下：

#驱动
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
#mysql连接信息
jdbc.url=jdbc:mysql://127.0.0.1:3306/databaseName?serverTimezone=GMT
#用户名
jdbc.username=root
#密码
jdbc.password=123456

在applicationContext.xml配置文件中添加如下的配置：

<!--1、配置数据库相关参数-->
<context:property-placeholder location="classpath:jdbc.properties" ignore-unresolvable="true"/>
<!--2、数据源-->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
      init-method="init" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driverClassName}"></property>
    <property name="url" value="${jdbc.url}"></property>
    <property name="username" value="${jdbc.username}"></property>
    <property name="password" value="${jdbc.password}"></property>
</bean>
<!--3、配置sqlSessionFactory对象-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <!--注入数据库连接池-->
    <property name="dataSource" ref="dataSource"/>
    <!--扫描sql配置文件:mapper需要的xml文件-->
    <property name="mapperLocations" value="classpath:mapper/*.xml"/>
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory"/>
</bean>
<bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    <property name="basePackage" value="com.cy.dao"/>
</bean>

在/src/main/java/com/cy/model目录下创建数据库的表对应的实体类对象User

public class User implements Serializable {

    private Integer id;
    private String username;
    private String password;
    private String nickname;
    private String email;
    private String phone;
    private String address;
    private Date create_time;
    private String avatar_url;
    private String role;
    //省略set、get方法
}

在/src/main/java/com.cy.dao目录下创建对应的DAO对象UserDao，UserDao是一个接口，提供了findAll方法用来查询所有的用户。

package com.cy.dao;

import com.cy.model.User;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface UserDao {
    List<User> findAll();
}

在/src/main/java/com.cy.service目录下创建对应的服务层接口UserService，服务层接口UserService只提供了一个查询所有用户的方法findAll()。

package com.cy.service;

import com.cy.model.User;

import java.util.List;

public interface UserService {
    List<User> findAll();
}

在/src/main/java/com.cy.service.impl开发对应的服务层实现类UserServiceImpl，实现类树妖注入UserDao接口，并实现findAll()方法。

package com.cy.service.impl;

import com.cy.dao.UserDao;
import com.cy.model.User;
import com.cy.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;


@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserDao userDao;
    @Override
    public List<User> findAll() {
        return userDao.findAll();
    }
}

在/src/main/java/com.cy.controller目录下创建控制层类UserController，并注入服务层接口。

package com.cy.controller;

import com.cy.model.User;
import com.cy.service.UserService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.annotation.Resource;
import java.util.List;

@Controller
@RequestMapping(value = "/user")
public class UserController {

    @Resource
    private UserService userService;

    @GetMapping("/findAll")
    public String findAll(Model model){
        List<User> userList = userService.findAll();
        for (User user:userList){
            System.out.println("id:"+user.getId());
            System.out.println("name:"+user.getUsername());
        }
        return "hello";
    }
}

在/src/main/resources/mapper目录下创建UserMapper.xml文件。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cy.dao.UserDao">
    <sql id="userField">
        sys_user.id as "id",
        sys_user.username as "username",
        sys_user.password as "password",
        sys_user.nickname as "nickname",
        sys_user.email as "email",
        sys_user.phone as "phone",
        sys_user.address as "address",
        sys_user.create_time as "create_time",
        sys_user.avatar_url as "avatar_url",
        sys_user.role as "role"
    </sql>

    <select id="findAll" resultType="com.cy.model.User">
        select
        <include refid="userField"/>
        from sys_user
    </select>
</mapper>

标签说明：

mapper：namespace主要用于绑定Dao接口。
select id=“findAll”：select标签，用来编写select查询语句，id属性值与UserDao接口中的方法名逐一对应。

集成Log4j日志框架

在pom.xml文件中添加所需依赖包

<!--log4j2-->
<properties>
    <slf4j.version>1.7.7</slf4j.version>
    <log4j.version>1.2.17</log4j.version>
</properties>
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>${log4j.version}</version>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>${slf4j.version}</version>
</dependency>
<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>${slf4j.version}</version>
</dependency>

在/src/main/java/resources/下创建配置文件log4j.properties文件。

### set log levels ###
log4j.rootLogger=DEBUG,Console

#Console
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.Target=System.out
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n

集成Junit4测试框架

在pom.xml文件中添加相关依赖包

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

在/src/main/test/com.cy.test目录下创建测试基类

package com.cy.test;


import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext.xml"})
public class BaseJunit4Test {
}

在/src/main/test/com.cy.test目录下创建测试类，简单测试是否成功集成了JUnit框架，具体代码如下：

package com.cy.test;

import com.cy.dao.UserDao;
import com.cy.model.User;
import org.junit.Test;

import javax.annotation.Resource;
import java.util.List;

public class UserDaoTest extends BaseJunit4Test {

    @Resource
    private UserDao userDao;

    @Test
    public void testFindAll() {
        List<User> userList = userDao.findAll();
        System.out.println(userList.size());
    }
}

UserDaoTest 类需要继承 BaseJunit4Test 测试基类。UserDaoTest 类代码开发完成之后，运行testFindAll方法，便可在控制台看到相关的输出信息。

Mybatis映射器与动态SQL

Mybatis映射器

Mybatis提供了强大的映射器，并且提供了丰富的映射器元素。

select元素

<select id="findById" parameterType="int" resultType="resultType="com.cy.model.User"">
    select *
    from sys_user
    where id = #{id}
</select>

接口UserDao中定义的方法:

User findById(Integer id);

示例一：通过名称查询用户

<select id="findByName" parameterType="String" resultType="com.cy.model.User">
    select *
    from sys_user
    where username = #{username}
</select>

对应的UserDao接口如下

User findByName(String username);

示例二：通过名称查询用户个数

<select id="countByRole" parameterType="String" resultType="int">
    select count(*)
    from sys_user
    where role = #{role}
</select>

对应的UserDao接口如下

int countByRole(String role);

insert元素

示例1：插入用户数据

<insert id="insert" parameterType="com.cy.model.User">
    insert into sys_user(id, username, password, nickname, email, phone, address, create_time, avatar_url, role)
    values (#{id}, #{username}, #{password}, #{nickname}, #{email}, #{phone}, #{address}, #{create_time},
    #{avatar_url}, #{role})
</insert>

示例2：插入用户数据，主键自增

<insert id="insert" parameterType="com.cy.model.User" useGeneratedKeys="true" keyProperty="id">
    insert into sys_user(id, username, password, nickname, email, phone, address, create_time, avatar_url, role)
    values (#{id}, #{username}, #{password}, #{nickname}, #{email}, #{phone}, #{address}, #{create_time},
    #{avatar_url}, #{role})
</insert>

对应的UserDao接口如下：

int insert(User user);

update元素

<update id="update" parameterType="com.cy.model.User">
    update sys_user set
    username=#{username},
    password=#{password},
    nickname=#{nickname}
    where id = #{id}
</update>

对应的UserDao接口如下

int update(User user);

delete元素

<delete id="delete" parameterType="int">
    delete
    from sys_user
    where id = #{id}
</delete>

对应的UserDao接口如下：

int delete(Integer id);

sql元素

    <sql id="userField">
        su.id as "id",
        su.username as "username",
        su.password as "password",
        su.nickname as "nickname",
        su.email  as  "email",
        su.phone  as  "phone",
        su.address  as "address",
        su.create_time  as  "create_time",
        su.avatar_url  as  "avatar_url",
        su.role  as "role"
    </sql>

    <select id="findAll" resultMap="UserMap">
        select
        <include refid="userField"/>
        from sys_user as su
    </select>

使用include元素的refid属性进行引用

    <sql id="userField">
        ${prefix}.id as "id",
        ${prefix}.username as "username",
        ${prefix}.password as "password",
        ${prefix}.nickname as "nickname",
        ${prefix}.email  as  "email",
        ${prefix}.phone  as  "phone",
        ${prefix}.address  as "address",
        ${prefix}.create_time  as  "create_time",
        ${prefix}.avatar_url  as  "avatar_url",
        ${prefix}.role  as "role"
    </sql>

    <select id="findAll" resultMap="UserMap">
        select
        <include refid="userField">
            <property name="prefix" value="su"/>
        </include>
        from sys_user as su
    </select>

#与$的区别

（1）#{}将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号，具体实例如下：

order by #{id}
//如果id传入11，则sql解析成：
order by "11"

（2）${}将传入的数据直接显示生成在sql中，具体示例如下：

order by #{id}
//如果id传入11，则sql解析成：
order by 11

（3）#方式能够很大程度防止sql注入，$方式无法防止sql注入。

综上所述，一般采用#，而不是$。

resultMap结果映射集

 <sql id="userField">
        ${prefix}.id as "id",
        ${prefix}.username as "username",
        ${prefix}.password as "password",
        ${prefix}.nickname as "nickname",
        ${prefix}.email  as  "email",
        ${prefix}.phone  as  "phone",
        ${prefix}.address  as "address",
        ${prefix}.create_time  as  "create_time",
        ${prefix}.avatar_url  as  "avatar_url",
        ${prefix}.role  as "role"
    </sql>

    <!--resultMap结果映射集-->
    <resultMap id="UserMap" type="com.cy.model.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <result property="nickname" column="nickname"></result>
        <result property="email" column="email"></result>
        <result property="phone" column="phone"></result>
        <result property="address" column="address"></result>
        <result property="create_time" column="create_time"></result>
        <result property="avatar_url" column="avatar_url"></result>
        <result property="role" column="role"></result>
    </resultMap>

    <select id="findAll" resultMap="UserMap">
        select
        <include refid="userField">
            <property name="prefix" value="su"/>
        </include>
        from sys_user as su
    </select>

动态SQL

if元素

    <sql id="userField">
        ${prefix}.id as "id",
        ${prefix}.username as "username",
        ${prefix}.password as "password",
        ${prefix}.nickname as "nickname",
        ${prefix}.email  as  "email",
        ${prefix}.phone  as  "phone",
        ${prefix}.address  as "address",
        ${prefix}.create_time  as  "create_time",
        ${prefix}.avatar_url  as  "avatar_url",
        ${prefix}.role  as "role"
    </sql>


    <resultMap id="UserMap" type="com.cy.model.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <result property="nickname" column="nickname"></result>
        <result property="email" column="email"></result>
        <result property="phone" column="phone"></result>
        <result property="address" column="address"></result>
        <result property="create_time" column="create_time"></result>
        <result property="avatar_url" column="avatar_url"></result>
        <result property="role" column="role"></result>
    </resultMap>


    <select id="findByNameAndPassword" parameterType="String" resultMap="UserMap">
        select
        <include refid="userField">
            <property name="prefix" value="su"/>
        </include>
        from sys_user su
        where 1=1
        <if test="username != null and username!=''">
            and username = #{username}
        </if>
        <if test="password != null and password!=''">
            and password = #{password}
        </if>
    </select>

对应的UserDao接口代码如下：

List<User> findByNameAndPassword(@Param("username") String username, @Param("password") String password);

choose、when、otherwise元素

与if元素二重选择相比，choose、when、otherwise元素提供了三重选择，有点类似switch…case…default语句，具体代码如下：

    <sql id="userField">
        su.id as "id",
        su.username as "username",
        su.password as "password",
        su.nickname as "nickname",
        su.email  as  "email",
        su.phone  as  "phone",
        su.address  as "address",
        su.create_time  as  "create_time",
        su.avatar_url  as  "avatar_url",
        su.role  as "role"
    </sql>
    
   
    <resultMap id="UserMap" type="com.cy.model.User">
        <id property="id" column="id"></id>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
        <result property="nickname" column="nickname"></result>
        <result property="email" column="email"></result>
        <result property="phone" column="phone"></result>
        <result property="address" column="address"></result>
        <result property="create_time" column="create_time"></result>
        <result property="avatar_url" column="avatar_url"></result>
        <result property="role" column="role"></result>
    </resultMap>

    <select id="findByNameAndPassword" parameterType="String" resultMap="UserMap">
        select
        <include refid="userField"></include>
        from sys_user su
        where 1=1
        <choose>
            <when test="username!=null and username !=''">
                and username = #{username}
            </when>
            <when test="password !=null and password!=''">
                and password = #{password}
            </when>
            <otherwise>
                order by id desc
            </otherwise>
        </choose>
    </select>

标签里可以包含多个标签，是可选的并不是必填选项

    <select id="findByNameAndPassword" parameterType="String" resultMap="UserMap">
        select
        <include refid="userField"></include>
        from sys_user
        where 1=1
            <choose>
                <when test="username!=null and username !=''">
                    and username = #{username}
                </when>
                <when test="password !=null and password!=''">
                    and password = #{password}
                </when>
            </choose>
    </select>

trim、where、set元素

    <select id="findByNameAndPassword" parameterType="String" resultMap="UserMap">
        select
        <include refid="userField">
            <property name="prefix" value="su"/>
        </include>
        from sys_user su
        <trim prefix="WHERE" prefixOverrides="AND">
            <choose>
                <when test="username!=null and username !=''">
                    and username = #{username}
                </when>
                <when test="password !=null and password!=''">
                    and password = #{password}
                </when>
                <otherwise>
                    order by id desc
                </otherwise>
            </choose>
        </trim>
    </select>

    <update id="update" parameterType="com.cy.model.User">
        update sys_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="username!=null and username!=''">
                username=#{username},
            </if>
            <if test="password!=null and password!=''">
                password=#{password},
            </if>
            <if test="nickname!=null and nickname!=''">
                nickname=#{nickname}
            </if>
        </trim>
        where id = #{id}
    </update>

prefix：表示在trim标签包裹的部分前面添加内容。注意：是在没有prefixOverrides，suffixOverrides属性的情况下。
prefixOverrides：有prefix属性的情况下，prefixOverrides属性表示去掉SQL语句前缀的内容。
suffix：表示在trim标签包裹的部分后面添加内容，注意：是在没有prefixOverrides，suffixOverrides属性的情况下。
suffixOverrides：有prefix属性的情况下，suffixOverrides属性表示去掉SQL语句后缀的内容。

where1=1这样的条件显然很奇怪，所以可以使用WHERE标签优化上面的SQL语句，具体代码如下：

    <select id="findByName" parameterType="String" resultType="com.cy.model.User">
        select *
        from sys_user
        <where>
            <if test="username!=null and username != '' ">
                username = #{username}
            </if>
        </where>
    </select>

foreach元素

//根据Id集合查询用户列表    
<select id="findByIds" resultType="list" resultMap="UserMap">
        select * from sys_user
        where id in
        <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

foreach元素的属性

item：循环中当前的元素
index：配置的是当前元素在集合的位置，即下标
collection：接口传进来的参数名称，可以是一个数组、列表(List)、集合(Set)
open：配置的是以什么符号作为起始符号将集合的元素包装起来，如"("
separator：各个元素的分隔符
close：配置的是以什么符号作为终止符号将集合的元素包装起来，如")"

bind元素

bind元素可以从OGNL表达式中创建一个变量并将其绑定到上下文。在惊醒模糊查询时，比如通过用户名称username拆线呢用户，就常会用到bind元素。

示例：

<select id="findByNameAndPassword" parameterType="String" resultType="com.cy.model.User">
    <bind name="name_pattern" value="'%'+username+'%'"/>
    <bind name="password_pattern" value="'%'+password+'%'"/>
    select * from sys_user 
    <where>
        <if test="username != null and username!=''">
            and username like #{name_pattern}
        </if>
        <if test="password != null and password!=''">
            and password like #{password_pattern}
        </if>
    </where>
</select>

在上述的select语句中，定义了多个bind元素，bind元素的属性value的值："‘%’+username+‘%’"会赋值给name_pattern，然后就可以在SQL中直接使用name_pattern变量。

分页插件 PageHelper

在项目的pom.xml文件中添加PageHelper依赖包

		<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.4</version>
        </dependency>
        
        <!--springmvc进行json转换需要的jar包-->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-core</artifactId>
            <version>2.9.6</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.9.6</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.9.6</version>
        </dependency>

在applicationContext.xml文件中添加PageHelper相关的配置

<!--3、配置sqlSessionFactory对象-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--注入数据库连接池-->
        <property name="dataSource" ref="dataSource"/>
        <!--扫描sql配置文件:mapper需要的xml文件-->
        <property name="mapperLocations" value="classpath:mapper/*.xml"/>

        <!--配置分页插件-->
        <property name="plugins">
            <array>
                <bean class="com.github.pagehelper.PageInterceptor">
                    <property name="properties">
                        <value>
                        
                            helperDialect=mysql
                            reasonable=true
                        </value>
                    </property>
                </bean>
            </array>
        </property>
    </bean>

在UserDaoTest编写测试用例，具体的代码如下：

    @Test
    public void testPageHelper() {
        PageHelper.startPage(1,5);
        List<User> userList = userDao.findAll();
        PageInfo pageInfo=new PageInfo(userList);
        System.out.println(pageInfo);
    }