cgb2106-day19

偏执的太偏执、 2021-09-11 03:28 290阅读 0赞

### 文章目录 ###

*   *  一,动态SQL
     *   *  \--1,foreach标签
         *   *  修改映射文件
             *  修改接口文件
             *  修改测试类
             *  总结
     *  二,ResultMap
     *   *  \--1,概述
         *  \--2,测试
         *   *  项目结构
             *  创建了表,加了记录
             *  修改pom.xml,添加mybatis的jar包
             *  创建核心配置文件
             *  创建映射文件
             *  创建接口类
             *  创建pojo类
             *  测试
             *  总结
         *  3,优化简化resultmap
         *   *  第一步：在核心配置文件中开启驼峰规则：
             *  第二步：resultMap标签中添加新属性 autoMapping=“true”
     *  三,SSM整合
     *   *  \--0,准备表和数据
         *  \--0,添加mybatis的jar包
         *  \--1,创建前端HTML网页
         *  \--2,创建启动类RunApp
         *  \--3,创建配置文件application.yml
         *  \--4,创建pojo类
         *  \--5,创建CarMapper接口
         *  \--6,创建映射文件CarMapper.xml
         *  \--7,创建CarService接口
         *  \--8,创建CarServiceImpl实现类
         *  \--9,创建CarController类
         *  \--10,测试
         *  \--11,总结
     *  标题

## 一,动态SQL ##

### –1,foreach标签 ###

![在这里插入图片描述][785b84208bf64c99947bd4a950d4943c.png]

#### 修改映射文件 ####

<?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="cn.tedu.dao.DeptMapper">
        
        <sql id="cols">
            id,dname,loc
        </sql>
    
        
        <delete id="delete">
            delete from dept where id in(
                 /*foreach用来完成遍历
        collection表示要遍历哪种集合里的数据,值是固定值:array/list/Map.key
        item表示即将遍历到的数据,separator是分隔符,#{ids}获取遍历到的数据
                 */
                <foreach collection="array" item="ids" separator=",">
                    #{ids}
                </foreach>
            )
        </delete>
    
        
        <select id="getByName" resultType="Dept">
            select 
             <include refid="cols"></include>
            from dept
            <if test="dname != null">
                where  dname=#{dname}
            </if>
        </select>
        
       
       <select id="getById" resultType="Dept">
           select 
           <include refid="cols"></include>
           from dept where id=1
       </select>
    
        
        <insert id="save">
            insert into dept values(#{id},#{dname},#{loc})
        </insert>
    </mapper>

#### 修改接口文件 ####

package cn.tedu.dao;
    import cn.tedu.pojo.Dept;
    
    import java.util.List;
    
    //接口的全路径=映射文件中namespace的值
    //接口的方法名=映射文件中id的值
    public interface DeptMapper { 
        Dept getById(); //根据id查
        List<Dept> getByName(String dname);//根据名字查
        void save(Dept dept);//新增部门记录
        void delete(int[] a);//删除
    }

#### 修改测试类 ####

package cn.tedu.test;
    import cn.tedu.dao.DeptMapper;
    import cn.tedu.pojo.Dept;
    import cn.tedu.pojo.User;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.jupiter.api.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class Test1 { 
        @Test
        public void get() throws IOException { 
            //读取配置文件
            InputStream in = Resources.getResourceAsStream(
                                        "mybatis-config.xml");
            //创建会话工厂
            SqlSessionFactory factory = new SqlSessionFactoryBuilder()
                                                            .build(in);
            //开启会话,准备执行SQL
            SqlSession session = factory.openSession();
            //定位SQL(namespace的值.id的值),并执行
            //selectOne执行查询的SQL,并只会返回一个结果
            Object o = session.selectOne("userMapper.getById",2);
            //User(id=1, name=hanmeimei, addr=北京, age=28)
            System.out.println(o);
    
            //定位SQL(namespace的值.id的值),并执行
            //selectList执行查询的SQL,并返回多个结果
            List<User> list = session.selectList("userMapper.getAll");
            for(User u : list){ 
                System.out.println(u);
            }
            //定位SQL(namespace的值.id的值),并执行,并给SQL传入参数
            User u = session.selectOne(
                            "userMapper.getByName","xiongda");
            System.out.println(u);
    
            //定位SQL(namespace的值.id的值)
    // Dept d = session.selectOne("deptMapper.getById");
    // System.out.println(d);
            //获取了指定的接口
            DeptMapper mapper = session.getMapper(DeptMapper.class);
            //调用接口里的方法
            Dept dd = mapper.getById();
            System.out.println(dd);
    
            List<Dept> ds = mapper.getByName(null);
            for (Dept d : ds) { 
                System.out.println(d+"=================");
            }
    
            Dept dept = new Dept();
            dept.setId(null).setDname("ios开发部").setLoc("上海");
            mapper.save(dept);
            //mybatis不会自动提交事务,增删改需要自己提交事务
            session.commit();
    
    
            mapper.delete(new int[]{ 103,104});
            session.commit();
    
        }
    }

#### 总结 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70]

## 二,ResultMap ##

### –1,概述 ###

resultType只能完成简单的ORM,只能完成那些 字段名 和 属性名一致的情况  
字段名 和 属性名 不一样的情况,resultType必须换成resultMap,否则无法ORM

### –2,测试 ###

#### 项目结构 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 1]

#### 创建了表,加了记录 ####

CREATE TABLE `user_info` (
      `id` int(11) NOT NULL auto_increment,
      `user_name` varchar(20) default NULL,
      `user_addr` varchar(20) default NULL,
      `user_age` int(11) default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

#### 修改pom.xml,添加mybatis的jar包 ####

<?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">
        <parent>
            <artifactId>cgb2106boot03</artifactId>
            <groupId>cn.tedu</groupId>
            <version>0.0.1-SNAPSHOT</version>
        </parent>
        <modelVersion>4.0.0</modelVersion>
    
        <artifactId>day19</artifactId>
    
    
        <dependencies>
            
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.4</version>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.48</version>
            </dependency>
        </dependencies>
    </project>

#### 创建核心配置文件 ####

<?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>
        
        <environments default="test">
            <environment id="test">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatisdb2?characterEncoding=utf8&serverTimezone=Asia/Shanghai" />
                    <property name="username" value="root"/>
                    <property name="password" value="root"/>
                </dataSource>
            </environment>
        </environments>
        
        <mappers>
            <mapper resource="userInfoMapper.xml"></mapper>
        </mappers>
    </configuration>

#### 创建映射文件 ####

<?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="cn.tedu.dao.UserInfoMapper">
        
        
        <resultMap id="abc" type="cn.tedu.pojo.UserInfo">
            <result column="user_name" property="userName"></result>
            <result column="user_addr" property="userAddr"></result>
            <result column="user_age" property="userAge"></result>
        </resultMap>
        
        <select id="selectList" resultMap="abc">
            select * from user_info
        </select>
    
    </mapper>

#### 创建接口类 ####

package cn.tedu.dao;
    
    import cn.tedu.pojo.UserInfo;
    
    import java.util.List;
    
    public interface UserInfoMapper { 
        List<UserInfo> selectList();//查询所有
    }

#### 创建pojo类 ####

package cn.tedu.pojo;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import lombok.experimental.Accessors;
    
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Accessors(chain = true)
    public class UserInfo { 
        private Integer id;
        private String userName;
        private String userAddr;
        private Integer userAge;
    }

#### 测试 ####

package cn.tedu.test;
    
    import cn.tedu.dao.UserInfoMapper;
    import cn.tedu.pojo.UserInfo;
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.junit.jupiter.api.Test;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.List;
    
    public class Test1 { 
        @Test
        public void all() throws IOException { 
            InputStream in = Resources.getResourceAsStream(
                            "mybatis-config.xml");
            SqlSessionFactory factory = new SqlSessionFactoryBuilder()
                                                .build(in);
            //true表示自动提交事务
            SqlSession session = factory.openSession(true);
            //获取指定接口
            UserInfoMapper mapper = session.getMapper(UserInfoMapper.class);
            //调用方法
            List<UserInfo> list = mapper.selectList();
            for (UserInfo uf : list) { 
                //UserInfo(id=1, userName=null, userAddr=null, userAge=null)
                System.out.println(uf);
            }
        }
    
    
    }

#### 总结 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 2]

### 3,优化简化resultmap ###

#### 第一步：在核心配置文件中开启驼峰规则： ####

<?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="mapUnderscoreToCamelCase" value="true" />
        </settings>
    
    
        
        <environments default="test">
            <environment id="test">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/mybatisdb2?characterEncoding=utf8&serverTimezone=Asia/Shanghai" />
                    <property name="username" value="root"/>
                    <property name="password" value="root"/>
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
            <mapper resource="userInfoMapper.xml"></mapper>
        </mappers>
    </configuration>

#### 第二步：resultMap标签中添加新属性 autoMapping=“true” ####

<?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="cn.tedu.dao.UserInfoMapper">
        
        
        <resultMap autoMapping="true" id="abc" type="cn.tedu.pojo.UserInfo">
           
        </resultMap>
        
        <select id="selectList" resultMap="abc">
            select * from user_info
        </select>
    
    </mapper>

## 三,SSM整合 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 3]

### –0,准备表和数据 ###

CREATE TABLE `car` (
      `id` int(11) NOT NULL auto_increment,
      `name` varchar(10) default NULL,
      `color` varchar(10) default NULL,
      `price` double default NULL,
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### –0,添加mybatis的jar包 ###

<?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">
        <parent>
            <artifactId>cgb2106boot03</artifactId>
            <groupId>cn.tedu</groupId>
            <version>0.0.1-SNAPSHOT</version>
        </parent>
        <modelVersion>4.0.0</modelVersion>
    
        <artifactId>SSM01</artifactId>
        <dependencies>
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.4</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.48</version>
                <scope>runtime</scope>
            </dependency>
        </dependencies>
    
    </project>

### –1,创建前端HTML网页 ###

<!DOCTYPE html>
    <html>
    	<head>
    		<meta charset="utf-8">
    		<title>ssm</title>
    	</head>
    	<body>
    		<a href="http://localhost:8080/car/get">点我请求服务器的资源</a>
    	</body>
    </html>

### –2,创建启动类RunApp ###

package cn.tedu;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    //扫描dao层接口文件所在的包,框架自动生成实现类
    @MapperScan("cn.tedu.dao")
    public class RunApp { 
        public static void main(String[] args) { 
            SpringApplication.run(RunApp.class);
        }
    }

### –3,创建配置文件application.yml ###

#是一个特殊的文件,特殊在这个文件将被springboot框架自动加载-开箱即用
    #格式非常严格,k: v
    #SpringBoot配置mysql信息
    spring:
      datasource:
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql:///mybatisdb?useUnicode=true&characterEncoding=utf8&useSSL=false
        username: root
        password: root
    #SpringBoot整合Mybatis配置
    mybatis:
      #指定UserMapper.xml文件的位置
      mapper-locations: classpath:*.xml
      #开启驼峰映射
      configuration:
        map-underscore-to-camel-case: true

### –4,创建pojo类 ###

package cn.tedu.pojo;
    
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import lombok.experimental.Accessors;
    import org.springframework.stereotype.Component;
    
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Accessors(chain = true)//lombok的链式编程
    @Component //ioc
    public class Car { 
        private Integer id;
        private String name;
        private String color;
        private Double price;
    }

### –5,创建CarMapper接口 ###

package cn.tedu.dao;
    
    import cn.tedu.pojo.Car;
    import java.util.List;
    @Component//没有啥意义
    public interface CarMapper { 
        List<Car> selectList(); //查询所有数据
    }

### –6,创建映射文件CarMapper.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="cn.tedu.dao.CarMapper">
    
        
        <select id="selectList" resultType="cn.tedu.pojo.Car">
            select * from car
        </select>
    
    </mapper>

### –7,创建CarService接口 ###

package cn.tedu.service;
    import cn.tedu.pojo.Car;
    import java.util.List;
    public interface CarService { 
        List<Car> selectList(); //查询所有数据
    }

### –8,创建CarServiceImpl实现类 ###

package cn.tedu.service;
    
    import cn.tedu.dao.CarMapper;
    import cn.tedu.pojo.Car;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import java.util.List;
    
    @Service //ioc
    public class CarServiceImpl implements CarService{ 
    
        @Autowired //di依赖注解dao层,为了使用dao的功能
        private CarMapper mapper;
    
        @Override //查询所有数据
        public List<Car> selectList() { 
            return mapper.selectList();
        }
    }

### –9,创建CarController类 ###

package cn.tedu.controller;
    
    import cn.tedu.pojo.Car;
    import cn.tedu.service.CarService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.util.List;
    
    @RestController//相当于@Controller+@ResponseBody
    @RequestMapping("car")
    public class CarController { 
        @Autowired //di
        private CarService carService ;
    
        //查询所有数据
        @RequestMapping("get")
        public List<Car> selectList(){ 
            return carService.selectList() ;
        }
    }

### –10,测试 ###

打开浏览器,访问服务器

### –11,总结 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 4]

## 标题 ##

[785b84208bf64c99947bd4a950d4943c.png]: /images/20210911/966ebbdbe52e41f7ad49b65a737c496b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70]: /images/20210911/ec7888dc88e24ab0a755bf6bd6ce47dc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 1]: /images/20210911/2c6a6df93bec432fa1cb3e60d3290112.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 2]: /images/20210911/43968aaa480f45e89f873dfd3a037f76.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 3]: /images/20210911/7e4d03a0809445e1a06ade3a6acfe231.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 4]: /images/20210911/f103cdd976d345baa77cc2fd7d5cc67e.png