spring增删改查接口 spring整合实现增删改查

SSM基本整合

文章目录

• SSM基本整合

• 本文介绍
• 项目搭建
• Maven依赖

• pom.xml依赖

• 配置文件详解

• spring核心配置文件
• mybatis配置文件
• jdbc配置文件

• Spring继承Junit测试

• 创建测试类
• 对测试类进行配置

• 数据库准备

• 创建数据库
• 创建测试表

• 测试

• 实体类
• mapper接口
• service 和 实现类
• mapper映射文件
• 编写测试

• 测试findAll()

• 结果

• 测试addEmp()

• 结果

• 测试deleteEmpById()

• 结果

• 测试updateEmpById()

• 结果

• spring对springmvc整合

• 创建springmvc配置文件
• web.xml配置

• 服务器配置
• 创建Controller

• EmpController

• 创建前端页面

• 前端页面
• 使用apifox

• 测试findAll()
• 测试addEmp()
• 测试deleteEmpById()
• 测试updateEmpById

本文介绍

本文是基于spring,spring mvc,mybatis框架整合,适合已经学习完ssm框架的java开发人员

项目搭建

• 创建缺省包

Maven依赖

pom.xml依赖

<dependencies>
    <!--mysql驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.30</version>
    </dependency>
    <!--数据库连接池-->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.2.12</version>
    </dependency>
    <!--mybatis-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.11</version>
    </dependency>
    <!--mybatis对spring框架集成-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis-spring</artifactId>
        <version>2.0.2</version>
    </dependency>
    <!--mybatis分页助手-->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>5.3.2</version>
    </dependency>
    <!--spring框架依赖-->
    <!--
        spring-webmvc
        因为已经传递依赖的原因,所有只需要导入webmvc
    -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>5.3.23</version>
    </dependency>
    <!--aop-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aspects</artifactId>
        <version>5.3.23</version>
    </dependency>
    <!--spring-jdbc-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>5.3.23</version>
    </dependency>
    <!--spring-test spring继承Junit-->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-test</artifactId>
        <version>5.3.23</version>
    </dependency>

    <!--servlet-->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>3.1.0</version>
        <scope>provided</scope>
    </dependency>

    <!--工具-->
    <!--lombok-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.24</version>
    </dependency>
    <!--集成测试-->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
        <scope>test</scope>
    </dependency>
    <!--json转换-->
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.13.4.2</version>
     </dependency>
</dependencies>

配置文件详解

spring核心配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       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/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd">

    <!--加载数据库配置文件-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!--扫描service包-->
    <context:component-scan base-package="com.xmh.service"/>
    
    <!--配置数据源-->
    <bean id="dataSource"
          class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>

    <!--配置mybatis sqlSession工厂 -->
    <bean id="sqlSessionFactory"
          class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--数据源-->
        <property name="dataSource" ref="dataSource"/>
        <!--mapper文件路径-->
        <property name="mapperLocations" value="classpath:mappers/*.xml"/>
        <!--mybatis配置文件-->
        <property name="configLocation" value="classpath:sqlMappingConfig.xml"/>
        <!--对实体类起别名-->
        <property name="typeAliasesPackage" value="com.xmh.pojo"/>
    </bean>
    
    <!--配置mapper接口映射-->
    <bean id="mapperScannerConfigurer"
          class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--已经配置的sqlSessionFactoryBean-->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        <!--扫描mapper接口包
            spring容器会根据mapper接口代理生成实现类
            因此我们不需要在mapper接口上加注解-->
        <property name="basePackage" value="com.xmh.mapper"/>
    </bean>
    
    <!--配置事务管理器-->
    <bean id="dataSourceTransactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <!--注入属性-->
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <!--配置事务注解驱动-->
    <tx:annotation-driven transaction-manager="dataSourceTransactionManager"/>
</beans>

mybatis配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>

    <settings>
        <!--开启mybatis框架自带的slfj日志,可以查看sql语句的执行过程-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--开启驼峰映射,如表中first_name字段可自动映射为
          实体类中firstName属性-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--
        plugins在配置文件中的位置必须符合要求，否则会报错，顺序如下:
        properties?, settings?,
        typeAliases?, typeHandlers?,
        objectFactory?,objectWrapperFactory?,
        plugins?,
        environments?, databaseIdProvider?, mappers?
    -->
    <plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!--方言,当前使用的是那个数据库,如果没有配置,也会自动从url中推测出-->
            <property name="helperDialect" value="mysql"/>
            <!--分页合理化
                当页码<=0,查询第一页数据,
                当页面>最大页码时,查询最后一条记录-->
            <property name="reasonable" value="true"/>
            <!--查询总记录数-->
            <property name="defaultCount" value="true"/>
        </plugin>
    </plugins>
</configuration>

jdbc配置文件

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

Spring继承Junit测试

创建测试类

对测试类进行配置

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:springContext.xml")
public class SpringTest {

}

数据库准备

创建数据库

CREATE DATABASE test;

创建测试表

CREATE TABLE `myemp`
(
    `id`       int(0)                                                       NOT NULL AUTO_INCREMENT,
    `name`     varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
    `sex`      char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci     NULL DEFAULT '男',
    `job`      varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
    `salary`   decimal(7, 2)                                                NULL DEFAULT NULL,
    `hiredate` date                                                         NULL DEFAULT NULL,
    `deptno`   int(0)                                                       NULL DEFAULT NULL,
    PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 9
  CHARACTER SET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
  ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of myemp
-- ----------------------------
INSERT INTO `myemp`
VALUES (1, 'rose', '男', '研发部', 12345.67, '2013-05-21', 10);
INSERT INTO `myemp`
VALUES (2, 'jack', '男', NULL, NULL, '2022-11-03', NULL);
INSERT INTO `myemp`
VALUES (3, 'scott', '男', '市场', 5600.00, '2014-07-28', 20);
INSERT INTO `myemp`
VALUES (4, 'lucy', '女', '销售', 4300.00, '2015-08-08', 30);
INSERT INTO `myemp`
VALUES (5, 'lily', '女', '销售', 5800.00, '2017-06-07', 30);
INSERT INTO `myemp`
VALUES (6, 'bob', '男', '市场', 8500.00, '2016-06-23', 20);
INSERT INTO `myemp`
VALUES (7, 'hanmei', '女', '市场', 5200.00, '2018-02-23', 20);
INSERT INTO `myemp`
VALUES (8, 'Tom', '男', '研发部', 6200.00, '2017-02-18', 10);

测试

实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private int id;
    private String name;
    private String sex;
    private String job;
    private double salary;
    private Date hiredate;
    private int deptno;
}

mapper接口

public interface EmpMapper {
    /**
     * 查询所有员工
     *
     * @return 返回员工集合
     */
    List<Emp> findAll();

    /**
     * 新增一个员工
     *
     * @param emp 传入一个emp对象
     * @return 返回插入行数
     */
    int addEmp(Emp emp);

    /**
     * 删除一个员工根据id
     *
     * @param id 员工id
     * @return 返回删除行数
     */
    int deleteEmpById(int id);

    /**
     * 更新一个员工根据id
     *
     * @param emp 传入一个emp对象
     * @return 返回更新行数
     */
    int updateEmpById(Emp emp);
}

service 和 实现类

public interface EmpService {
    List<Emp> findAll();

    boolean addEmp(Emp emp);

    boolean deleteEmpById(int id);

    boolean updateEmpById(Emp emp);
}

@Service
@Transactional(rollbackFor = Exception.class)
public class EmpServiceImpl implements EmpService {
    private EmpMapper empMapper;

    @Autowired
    public void setEmpMapper(EmpMapper empMapper) {
        this.empMapper = empMapper;
    }

    @Override
    public List<Emp> findAll() {
        return empMapper.findAll();
    }

    @Override
    public boolean addEmp(Emp emp) {
//        System.out.println("事务开启");
        int rows = empMapper.addEmp(emp);
//        System.out.println("出现异常,事务回滚");
//        int a = 1/0;
        return rows != 0;
    }

    @Override
    public boolean deleteEmpById(int id) {
        return empMapper.deleteEmpById(id) != 0;
    }

    @Override
    public boolean updateEmpById(Emp emp) {
        return empMapper.updateEmpById(emp) != 0;
    }
}

mapper映射文件

<?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.xmh.mapper.EmpMapper">
    <insert id="addEmp">
        insert into myemp(name, sex, job, salary, hiredate, deptno)
        VALUES (#{name}, #{sex}, #{job}, #{salary}, #{hiredate}, #{deptno})
    </insert>
    <update id="updateEmpById">
        update myemp
        <set>
            <if test="name!=null">name=#{name},</if>
            <if test="sex!=null">sex=#{sex},</if>
            <if test="job!=null">job=#{job},</if>
            <if test="salary!=0">salary=#{salary},</if>
            <if test="hiredate!=null">hiredate=#{hiredate},</if>
            <if test="deptno!=0">deptno=#{deptno},</if>
        </set>
        where id = #{id}
    </update>
    <delete id="deleteEmpById">
        delete
        from myemp
        where id = #{id};
    </delete>

    <select id="findAll" resultType="com.xmh.pojo.Emp">
        select id, name, sex, job, salary, hiredate, deptno
#         select *
        from myemp;
    </select>
</mapper>

编写测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:springContext.xml")
public class SpringTest {
    private EmpService empService;

    @Autowired
    public void setEmpService(EmpService empService) {
        this.empService = empService;
    }

测试findAll()

@Test
public void findAll() {
    long start = System.currentTimeMillis();
    List<Emp> empList = empService.findAll();
    long end = System.currentTimeMillis();
    System.out.println("总耗时:" + (end - start));
    System.out.println(empList);
}

结果

Emp(id=1, name=rose, sex=男, job=研发部, salary=12345.67, hiredate=Tue May 21 00:00:00 CST 2013,deptno=10)
Emp(id=2, name=jack, sex=男, job=null, salary=0.0, hiredate=Thu Nov 03 00:00:00 CST 2022, deptno=0)
Emp(id=3, name=scott, sex=男, job=市场, salary=5600.0, hiredate=Mon Jul 28 00:00:00 CST 2014, deptno=20)
Emp(id=4, name=lucy, sex=女, job=销售, salary=4300.0, hiredate=Sat Aug 08 00:00:00 CST 2015, deptno=30)
Emp(id=5, name=lily, sex=女, job=销售, salary=5800.0, hiredate=Wed Jun 07 00:00:00 CST 2017, deptno=30)
Emp(id=6, name=bob, sex=男, job=市场, salary=8500.0, hiredate=Thu Jun 23 00:00:00 CST 2016, deptno=20)
Emp(id=7, name=hanmei, sex=女, job=市场, salary=5200.0, hiredate=Fri Feb 23 00:00:00 CST 2018, deptno=20)
Emp(id=8, name=Tom, sex=男, job=研发部, salary=6200.0, hiredate=Sat Feb 18 00:00:00 CST 2017, deptno=10)
Emp(id=11, name=test, sex=男, job=测试, salary=5000.0, hiredate=Thu Nov 03 00:00:00 CST 2022, deptno=10)

测试addEmp()

@Test
public void addEmp() {
    boolean is = empService.addEmp(new Emp(0, "test", "男", "测试", 2000, new Date(), 10));
    System.out.println(is);
}

结果

true

测试deleteEmpById()

@Test
public void deleteEmpById() {
    boolean is = empService.deleteEmpById(9);
    System.out.println(is);
}

结果

true

测试updateEmpById()

@Test
public void updateEmpById() {
    Emp emp = new Emp();
    emp.setId(11);
    emp.setJob("测试");
    emp.setName("test");
    emp.setSalary(5000);
    boolean is = empService.updateEmpById(emp);
    System.out.println(is);
}

结果

true

spring对springmvc整合

创建springmvc配置文件

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       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/mvc
       http://www.springframework.org/schema/mvc/spring-mvc.xsd">
    <!--扫描包-->
    <context:component-scan base-package="com.xmh.controller"/>
    <!--注解驱动-->
    <mvc:annotation-driven/>
    <!--处理静态资源-->
    <mvc:default-servlet-handler/>
</beans>

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>ssm</display-name>
    <!--加载springContext配置文件-->
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:springContext.xml</param-value>
    </context-param>
    
    <!--处理delete和put请求-->
    <filter>
        <filter-name>formContentFilter</filter-name>
        <filter-class>org.springframework.web.filter.FormContentFilter</filter-class>
    </filter>
    <filter-mapping>
        <filter-name>formContentFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
    
    <!--处理编码-->
    <filter>
        <filter-name>characterEncodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>characterEncodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>

    <!--spring加载配置文件监听器-->
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    
    <!--springmvc前端控制器-->
    <servlet>
        <servlet-name>dispatcherServlet</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>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>dispatcherServlet</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
</web-app>

服务器配置

创建Controller

EmpController

@RestController
public class EmpController {
    private EmpService empService;

    @Autowired
    public void setEmpService(EmpService empService) {
        this.empService = empService;
    }

    @GetMapping("/emp")
    public JsonResult findAll() {
        JsonResult jsonResult = new JsonResult();
        jsonResult.setCode(200);
        jsonResult.setMessage("查询成功");
        jsonResult.setData(empService.findAll());
        return jsonResult;
    }

    @PostMapping("/emp")
    public JsonResult addEmp(Emp emp) {
        System.out.println(emp);
        if (empService.addEmp(emp)) {
            return JsonResult.success();
        }
        return JsonResult.notSuccessful();
    }

    @DeleteMapping("/emp")
    public JsonResult deleteEmpById(int id) {
        if (empService.deleteEmpById(id)) {
            return JsonResult.success();
        }
        return JsonResult.notSuccessful();
    }

    @PutMapping("/emp")
    public JsonResult deleteEmpById(Emp emp) {
        if (empService.updateEmpById(emp)) {
            return JsonResult.success();
        }
        return JsonResult.notSuccessful();
    }
}

创建前端页面

前端页面

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form id="addEmp" method="post">
    姓名:<input type="text" name="name"><br>
    性别:<input type="text" name="sex"><br>
    职位:<input type="text" name="job"><br>
    工资:<input type="text" name="salary"><br>
    入职日期:<input type="date" name="hiredate"><br>
    部门编号<input type="text" name="deptno"><br>
    <p id="addMsg"></p>
    <input type="button" value="添加" onclick="addEmp()">
</form>

<hr style="margin-top: 20px;margin-bottom: 20px">

删除员工根据id:<input type="text" name="delId"><br>
<p id="deleteMsg"></p>
<input type="button" value="删除" onclick="deleteById()">

<hr style="margin-top: 20px;margin-bottom: 20px">

<form id="updateEmp">
    员工id:<input type="text" name="id"><br>
    姓名:<input type="text" name="name"><br>
    性别:<input type="text" name="sex"><br>
    职位:<input type="text" name="job"><br>
    工资:<input type="text" name="salary"><br>
    入职日期:<input type="date" name="hiredate"><br>
    部门编号<input type="text" name="deptno"><br>
    <p id="updateMsg"></p>
    <input type="button" value="修改" onclick="updateEmp()">
</form>
<hr style="margin-top: 20px;margin-bottom: 20px">
<table>
    <thead>
    <tr>
        <td>id</td>
        <td>username</td>
        <td>sex</td>
        <td>job</td>
        <td>salary</td>
        <td>hiredate</td>
        <td>deptno</td>
    </tr>
    </thead>
    <tbody id="findAll">
    </tbody>
</table>
<script src="./js/jquery.serializejson.js"></script>
<script src="./js/jquery-3.6.1.min.js"></script>
<script>
    $(document).ready(function () {
        $.ajax({
            url: "emp",
            type: "get",
            success: function (res) {
                console.log(res)
                for (let i = 0; i < res.data.length; i++) {
                    $("#findAll").append('<tr>' +
                        '<td>' + res.data[i].id + '</td>' +
                        '<td>' + res.data[i].name + '</td>' +
                        '<td>' + res.data[i].sex + '</td>' +
                        '<td>' + res.data[i].job + '</td>' +
                        '<td>' + res.data[i].salary + '</td>' +
                        '<td>' + res.data[i].hiredate + '</td>' +
                        '<td>' + res.data[i].deptno + '</td>' +
                        '</tr>')
                }
            }
        })
    })

    function addEmp() {
        $.ajax({
            url: "/emp",
            type: "post",
            data: "name=" + $("input[name=name]").val() +
                "&sex=" + $("input[name=sex]").val() +
                "&job=" + $("input[name=job]").val() +
                "&salary=" + $("input[name=salary]").val() +
                "&hiredate=" + $("input[name=hiredate]").val() +
                "&deptno=" + $("input[name=deptno]").val(),
            success: function (res) {
                $("#addMsg").html(res.message)
            }
        })
    }

    function deleteById() {
        $.ajax({
            url: "emp" + $("input[name=delId]").val(),
            type: "delete",
            success: function (res) {
                $("#addMsg").html(res.message)
            }
        })
    }

    function updateEmp() {
        $.ajax({
            url: "emp",
            type: "put",
            data:
                "id=" + $("input[name=id]").val() +
                "&name=" + $("input[name=name]").val() +
                "&sex=" + $("input[name=sex]").val() +
                "&job=" + $("input[name=job]").val() +
                "&salary=" + $("input[name=salary]").val() +
                "&hiredate=" + $("input[name=hiredate]").val() +
                "&deptno=" + $("input[name=deptno]").val(),
            success: function (res) {
                $("#addMsg").html(res.message)
            }
        })
</script>
</body>
</html>

我更推荐后端开发使用专门的工具发请求,可以发请求和接受参数就可以,可以节约很多时间

使用apifox

测试findAll()

测试addEmp()

测试deleteEmpById()

测试updateEmpById