SSM的整合进行多表查询

作者: 末日携手的半阳 | 来源:发表于2018-03-19 22:41 被阅读32次

SSM的整合进行多表查询
ssm框架进阶-----统一错误返回处理
SSM整合基础篇
SSM整合
SSM整合+简单的增删改查
MYSQL多表查询
关于Oracle多表查询的一个好办法
企业权限管理系统知识 day04 产品相关操作&订单相关操作
Oracle连接_子查询
整合SSM

①首先要导入spring和mybatis的依赖

<!--spring-->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-core</artifactId>
      <version>4.3.14.RELEASE </version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-beans</artifactId>
      <version>4.3.14.RELEASE </version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.3.14.RELEASE </version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-web</artifactId>
      <version>4.3.14.RELEASE </version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-webmvc</artifactId>
      <version>4.3.14.RELEASE </version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-orm</artifactId>
      <version>4.3.14.RELEASE </version>
    </dependency>
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>4.3.14.RELEASE </version>
    </dependency>
    <!--fastjson-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
        <version>1.2.31</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.0.31</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
        <version>1.3.1</version>
    </dependency>

②、配置整合的配置文件spring.xml、springDispatcherServlet-servlet.xml和web.xml，spring中已经包含mybatis中的内容，mybatis配置文件可以舍弃不使用了
spring.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: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.xhy"/>
   <mvc:annotation-driven>
      <!--mvc转化器-->
      <mvc:message-converters>
         <!--将返回的数据自动转为json-->
         <bean class="com.alibaba.fastjson.support.spring.FastJsonHttpMessageConverter">
            <!--<property name="supportedMediaTypes" value="application/json;charset=utf-8"/>-->
         </bean>
      </mvc:message-converters>
   </mvc:annotation-driven>
   <!--连接池-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/springmvc"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
    </bean>

    <!--mybatis会话工厂-->
    <bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
       <!--映射实体类的映射文件-->
        <property name="mapperLocations" value="classpath:mapper/*.xml"/>
    </bean>
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.xhy.dao"/>
    </bean>
</beans>

springDispatcherServlet-servlet.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"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
</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_3_1.xsd"
         version="3.1">
    <!--spring和springmvc整合-->
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:springmvc.xml</param-value>
    </context-param>

    <!--配置前端控制器并加载配置文件-->
    <servlet>
        <servlet-name>springDispatcherServlet</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <!--初始化加载resources目录下的前端控制器文件-->
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>classpath:springDispatcherServlet-servlet.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <!--前端控制器作用范围-->
    <servlet-mapping>
        <servlet-name>springDispatcherServlet</servlet-name>
        <url-pattern>*.action</url-pattern>
    </servlet-mapping>

    <!--配置过滤器设置编码-->
    <filter>
        <filter-name>encoder</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>encoder</filter-name>
        <url-pattern>*.action</url-pattern>
    </filter-mapping>
</web-app>

③、编写controller、service和dao层接口实现查询数据和搜索数据
controller

@Resource
   private UserService us;

   @RequestMapping("findUserAndRole")
   @ResponseBody
   public List<User> findUserAndRole(){
       System.out.println("controller");
        return us.findUserAndRole();
   }
   @RequestMapping("searchUserByKey/{type}/{key}")
   @ResponseBody
   public List<User> searchUserByKey(@PathVariable String key, @PathVariable String type){
       return us.searchUserByKey(key,type);
   }

service

@Resource//注入ud，一般使用这个比较多
    private UserDao ud;
    public List<User> findUserAndRole() {
        return ud.findUserAndRole();
    }
    public List<User> searchUserByKey(String key, String type) {
        Map<String, Object> params = new HashMap<String, Object>();
        if (key != null) {
            params.put("type", type);
            params.put("key", "%" + key + "%");
        }
        return ud.searchUserByKey(params);
    }

dao

  List<User> findUserAndRole();
  List<User> searchUserByKey(Map<String,Object> params);

④、编写user.xml和role.xml
user.xml

<!--自定义结果映射-->
    <resultMap id="m1" type="com.xhy.pojo.User">
        <result property="id" column="id"/>
        <result property="name" column="name2"/>
        <result property="roleName" column="roleName"/>
    </resultMap>
    <select id="findUserAndRole" resultMap="m1">
    /*#查询所有的用户名和角色名*/
        select u.id,u.name as name2,r.name as roleName from user u left join user_role ur on ur.uid=u.id left join role r on r.id=ur.rid
    </select>

    <!--根据关键字搜索;<if>:动态sql语句-->
    <select id="searchUserByKey" resultType="com.test.entity.User">
        select * from user u
        <if test="key!=null">
            <if test="type=='account'">
                where u.name like #{key}
            </if>
            <if test="type=='role'">
                left join user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where r.name like #{key}
            </if>
        </if>
    </select>

role.xml

<mapper namespace="com.xhy.dao.UserDao">
    <select id="findUserByRole" resultType="com.test.entity.User" parameterType="java.lang.String">select u.name from user u left JOIN user_role ur on u.id=ur.uid left join role r on ur.rid=r.id where r.name=#{name}</select>
</mapper>

SSM的整合进行多表查询
①首先要导入spring和mybatis的依赖 ②、配置整合的配置文件spring.xml、springDispa...
ssm框架进阶-----统一错误返回处理
文章链接 ssm入门 ssm框架进阶-----数据库增删改查3.ssm框架进阶-----数据库多表查询正文 1....
SSM整合基础篇
SSM整合需求:通过SSM整合做出对前端基本查询的jsp响应 1 数据库数据数据库数据如下创建父工程ssm_...
SSM整合
SSM整合目标熟练整合SSM 使用SSM环境进行增删改查项目结构一、创建数据库 CREATE DATABA...
SSM整合+简单的增删改查
SSM整合+简单的增删改查分页查询还没做，但是用到的findAll是分页查询的，...
MYSQL多表查询
多表查询就是从多张表中查找所需数据，并且整合在一起显示出来的意思多表查询拥有多种实现方式：内连接、外连接、子查询...
关于Oracle多表查询的一个好办法
Oracle学习里面难免遇见多表查询，但是遇到多表查询感觉总是找不到方向，其实完全可以用画图的方式来进行多表查询比...
企业权限管理系统知识 day04 产品相关操作&订单相关操作
一、SSM整合与产品查询 1.1 Spring环境搭建 1.1.1 编写Spring配置文件application...
Oracle连接_子查询
多表查询：多张表关联在一起进行查询，在多表查询中会频繁的使用表的别名。连接：内连接（等值连接）：select 列名...
整合SSM
SSM整合整合思路各自搭建SSM环境使用Spring整合Mybatis 使用Spring整合SpringMV...