ssm框架—实现数据库中两表关联查询（扩展：layui实现单表的增删改查）

小丫头爱打盹

已于 2022-08-25 15:55:23 修改

阅读量1.6k

点赞数

分类专栏： # Spring 文章标签：数据库 mybatis spring

于 2022-08-24 16:32:49 首次发布

本文链接：https://blog.csdn.net/Liu_wen_wen/article/details/126506707

版权

Spring 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

springMVC注解的配置方式+一对多+spring整合mybatis=实现数据库中数据表的查询

运行结果如下：

在这里插入图片描述

思路/步骤：

在这里插入图片描述

构建ssm框架步骤:

构建ssm框架步骤:
1.建库建表
2.创建java maven web项目
3.在pom.xml中配置相关jar包
   web,spring-context,jdbc,mybatis,mybatis-spring,mysql,lombok
4.创建实体包实体类
5.创建mapper接口
6.创建业务接口，业务实现
   a.业务实现类上加注解
   b.业务实现类中注入mapper对象
7.创建controller包，类
  a.controller类上加注解
  b.注入service对象
  c.配置请求地址映射对应方法 (@RequestMapping)

8.在resources下创建mybatis相关配置
   a.db.properties数据属性配置文件
   b.mybatis主配置文件，配置日志和下划线小驼峰映射,其他插件...
   c.mapper配置文件
9.在resources下创建spring-mybatis配置文件
  a.配置扫描业务包
  b.配置数据库属性文件
  c.配置连接池
  d.配置mybatis相关对象(SqlSessionFactory)
  e.配置mapper接口扫描

10.在resources下创建spring-mvc配置文件
   a.配置扫描controller
   b.配置注解驱动
   c.配置视图解析器
   d.配置default-servlet处理其他默认请求

11.配置web.xml内容
   a.配置spring监听器，构建spring容器对象
      1): 配置监听器要加载的spring配置文件
   b.配置springMVC的前端控制器，使用springMVC处理请求
      1):配置springMVC要使用的配置文件
   c.配置编码过滤器

代码整体布局：

在这里插入图片描述

（根据构建ssm框架步骤）代码如下：

创建数据库/表：

#创建分类表
create table category
(
	category_id int primary key,
	category_name varchar(50) not null
);

select * from category;

insert into category
(category_id ,category_name)
VALUES
(1,'食品'),
(2,'饮品'),
(3,'日用品');

create table goods
(
	goods_id int primary key auto_increment,
	goods_name varchar(200) not null,
	price decimal(5,1) not null,
	produce_date date not null,
	address varchar(200) default '郑州',
	category_id int
);


select * from goods;

insert into goods
(goods_name,price,produce_date,address,category_id)
values
('面包',2.5,'2018-01-01','郑州',1),
('红茶',5,'2015-02-02','武汉',2),
('鸡蛋',1,'2021-02-02','洛阳',1),
('绿茶',2.5,'2005-02-02','漯河',2),
('咖啡',20,'2019-02-02','广州',2),
('洗衣粉',18,'2001-02-02','广州',3),
('香皂',4,'2001-02-02','武汉',3),
('奶茶',9,'2022-02-02','郑州',2),
('饺子',32,'2022-03-02','郑州',1),
('辣条',0.5,'2001-02-08','洛阳',1);

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>spring_0824_KTLX2</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>spring_0824_KTLX2 Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <maven.compiler.source>8</maven.compiler.source>
    <maven.compiler.target>8</maven.compiler.target>
    <servlet.version>4.0.1</servlet.version>
    <jsp.version>2.2</jsp.version>
    <jstl.version>1.2</jstl.version>
    <spring.version>5.3.14</spring.version>
    <commons-dbcp.version>1.4</commons-dbcp.version>
    <mybatis.version>3.4.6</mybatis.version>
    <mybatis-spring.version>1.3.3</mybatis-spring.version>
    <mysql-connector-java.version>8.0.11</mysql-connector-java.version>
    <fastjson.version>1.2.78</fastjson.version>
  </properties>

  <dependencies>
    <!--添加spring依赖-->
    <!-- 添加javaEE支持 -->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>${servlet.version}</version>
      <!-- provided只在编译时支持，发布时不拷贝文件 -->
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>jsp-api</artifactId>
      <version>${jsp.version}</version>
      <!-- provided只在编译时支持，发布时不拷贝文件 -->
      <scope>provided</scope>
    </dependency>
    <dependency>
      <groupId>jstl</groupId>
      <artifactId>jstl</artifactId>
      <version>${jstl.version}</version>
    </dependency>
    <!--引入spring框架-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <!--引入spring数据库操作的支持模块-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>${spring.version}</version>
    </dependency>
    <!-- dbcp数据库连接池 -->
    <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>${commons-dbcp.version}</version>
    </dependency>
    <!-- mybatis spring整合 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>${mybatis-spring.version}</version>
    </dependency>
    <!-- mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>${mybatis.version}</version>
    </dependency>
    <!--mybatis插件PageHelper-->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.0</version>
    </dependency>
    <!-- mysql驱动类 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>${mysql-connector-java.version}</version>
    </dependency>
    <!--mybatis通用mapper-->
    <dependency>
      <groupId>com.github.abel533</groupId>
      <artifactId>mapper</artifactId>
      <version>3.0.1</version>
    </dependency>
    <!--jackson-->
    <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.13.2.2</version>
    </dependency>
    <!--配置fastjson-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>${fastjson.version}</version>
    </dependency>
    <!--lombok-->
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.22</version>
    </dependency>
    <!--引入junit-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.13</version>
      <!--scope：表示该jar包的作用范围
      <scope>test</scope>
      -->
    </dependency>
  </dependencies>
</project>

Category:

package com.aaa.demo.entity;

import lombok.Data;

@Data
public class Category {
    private Integer categoryId;
    private String categoryName;

}

Goods:

package com.aaa.demo.entity;

import lombok.Data;

import java.util.Date;

@Data
public class Goods {
    private Integer goodsId;
    private String goodsName;
    private Double price;
    private Date produce_date;
    private String address;
    private Category category; //注意
}

GoodsMapper:

package com.aaa.demo.mapper;

import com.aaa.demo.entity.Goods;

import java.util.List;

public interface GoodsMapper {
    List<Goods> listAll();
}

GoodsMapper.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.aaa.demo.mapper.GoodsMapper">
    <!--配置结果映射-->
    <resultMap id="goodsMap" type="goods" autoMapping="true">
        <id property="goodsId" column="goods_id"/>
        <association property="category" javaType="category" autoMapping="true">
        </association>
    </resultMap>
    <select id="listAll" resultMap="goodsMap">
        select
            g.goods_id,g.goods_name,g.price,g.produce_date,g.address,
            c.category_id,c.category_name
        from goods g
                 join category c
                      on g.category_id = c.category_id
    </select>
</mapper>

IGoodsService:

package com.aaa.demo.service;

import com.aaa.demo.entity.Goods;

import java.util.List;

public interface IGoodsService {
    List<Goods> listAll();
}

GoodsServiceImpl ：

package com.aaa.demo.service.impl;

import com.aaa.demo.entity.Goods;
import com.aaa.demo.mapper.GoodsMapper;
import com.aaa.demo.service.IGoodsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class GoodsServiceImpl implements IGoodsService {
    @Autowired
    private GoodsMapper goodsMapper;

    @Override
    public List<Goods> listAll() {
        return goodsMapper.listAll();
    }
}

GoodsController：

package com.aaa.demo.controller;

import com.aaa.demo.entity.Goods;
import com.aaa.demo.service.IGoodsService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

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

@Controller
public class GoodsController {
    //@Resource
    @Autowired
    private IGoodsService iGoodsService;

    @RequestMapping("/list")
    public String list(Model model){
        //使用业务层对象查询数据
        List<Goods> goodsList=iGoodsService.listAll();
        //使用model存储数据，springMVC会自动把数据带到页面
        model.addAttribute("goodsList",goodsList);
        //返回视图名字,springMVC会自动用转发方式调到该页面
        return "goodsList";
    }
}

db.properties：

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/70813_db?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8&allowPublicKeyRetrieval=true
jdbc.username=root
jdbc.password=123456

mybatis-config.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!-- 配置日志工具 -->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--配置将下划线字段转为小驼峰-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

spring-mybatis.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

    <!--配置spring扫描包-->
    <context:component-scan base-package="com.aaa.demo.service"/>

    <!--通过spring配置数据库的连接属性文件-->
    <context:property-placeholder location="classpath:db.properties"/>
    <!--通过spring来配置数据库连接池的数据源对象-->
    <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource"
          destroy-method="close">
        <!--配置连接池数据源需要使用四大金刚(数据库连接属性)-->
        <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>

    <!--通过spring来配置mybatis的会话工厂和其他相关配置
        原来的时候，mybatis的会话工厂由程序员来创建，现在通过spring来创建管理
        这个就体现了Spring的IoC特征(控制反转)
    -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <!--配置SqlSession依赖的数据源对象-->
        <property name="dataSource" ref="ds"/>
        <!--配置mybatis要使用的类型别名定义-->
        <property name="typeAliasesPackage" value="com.aaa.demo.entity"/>
        <!--配置mybatis的主配置文件的路径,spring会自动加载mybatis的配置文件-->
        <property name="configLocation" value="classpath:mybatis-config.xml"/>
        <!--配置mybatis的Mapper文件的路径:spring中Mapper接口和Mapper文件路径可以由区别-->
        <property name="mapperLocations" value="classpath:com/mapper/*.xml"/>
    </bean>
    <!--通过spring来配置Mybatis的Mapper接口
        通过包扫描的方式进行配置
    -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--配置Mybatis的Mapper接口-->
        <property name="basePackage" value="com.aaa.demo.mapper"/>
        <!--配置可以创建Mapper接口对象的SqlSession使用的SqlSessionFactory对象的名字-->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>

</beans>

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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd">

    <!--扫描controller包，管理其中定义的controller类-->
    <context:component-scan base-package="com.aaa.demo.controller"/>
    <!--开启注解驱动
        使用注解方式配置springMVC组件
        处理器映射器，处理器适配器，
        能够解析配置的注解完成请求:@RequestMapping
    -->
    <mvc:annotation-driven/>

    <!--配置视图解析器(ViewResolver)-->
    <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <!--配置视图页面前缀-->
        <property name="prefix" value="/WEB-INF/page/"/>
        <!--配置视图页面后缀-->
        <property name="suffix" value=".jsp"/>
    </bean>

    <!--配置DefaultServlet处理其他请求（静态资源）-->
    <mvc:default-servlet-handler/>

</beans>

web.xml:

<?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">

  <!--配置spring监听器，用于创建spring容器-->
  <listener>
    <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  </listener>
  <!--定义spring的配置文件，用于在spring监听器监听网站启动的时候，创建spring容器-->
  <context-param>
    <param-name>contextConfigLocation</param-name>
    <param-value>classpath:spring-mybatis.xml</param-value>
  </context-param>


  <!--配置springMVC前端控制器-->
  <servlet>
    <servlet-name>springMVC</servlet-name>
    <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    <init-param>
      <!--
          springMVC需要配置组件。。。
      -->
      <param-name>contextConfigLocation</param-name>
      <param-value>classpath:spring-mvc.xml</param-value>
    </init-param>
  </servlet>
  <servlet-mapping>
    <servlet-name>springMVC</servlet-name>
    <!--
        凡是以.do结尾的请求都让springMVC来处理
        <url-pattern>*.do</url-pattern>
    -->
    <!--        <url-pattern>*.do</url-pattern>-->
    <!--
        斜杠配置（/)∶表示所有的默认请求都交给DisaptherServlet来处理
        导致的问题就是请求静态资源的时候无法处理
        <url-pattern>/</url-pattern>
    -->
    <url-pattern>/</url-pattern>
  </servlet-mapping>
</web-app>

goodsList.jsp:

<%--
  Created by IntelliJ IDEA.
  User: 33154
  Date: 2022/8/24
  Time: 11:20
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    ${goodsList}
</body>
</html>