spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis动态SQL——MyBatis foreach标签...

╰半橙微兮° 2023-09-28 23:54 12阅读 0赞

![7ba9f666bf37d15d7fbf1021c008dd4c.png][]

![7dc4da5fae327ca2c50350c9cd378952.png][]

![c9139ba7ed8054835397a6381ac1b2ae.png][]

=========================================================================================

这种情况下，语句怎么写：

![24e6a60d8d701babc2749a71e0bd692e.png][]

![f7b0c2055e9ed4a5506858f826e790eb.png][]

![37172e3b79b0c38ce0fcc9d4ca44f4be.png][]

![7767fb3d1225a6fb4810294f5512379b.png][]

![0bad20b4bf9b402363a9a65d3e3e8541.png][]

![14f08cca0a195182d406274d64c03fde.png][]

![e126c3632bfc2e9de89bc0e4c05be639.png][]

![b3edb655fe9f288bf061848db071e511.png][]

======================================================================

pojo类：

package org.example.entity;
    
    public class MyUser
    {
        private int id;
        private String name;
        private int age;
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public int getAge() {
            return age;
        }
    
        public void setAge(int age) {
            this.age = age;
        }
    
        @Override
        public String toString() {
            return "MyUser{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", age=" + age +
                    '}';
        }
    }

![864ddb601ac8e190f79e9c0b33556ad4.png][]

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="org.example.mapper.UserMapper">
    
        
        <select id="findAll" resultType="org.example.entity.MyUser">
            select * from users
        </select>
    
        <insert id="saveUser" parameterType="org.example.entity.MyUser">
            insert into users(id,name,age) values(#{id},#{name},#{age})
        </insert>
    
        <insert id="saveUser2" parameterType="org.example.entity.MyUser">
            insert into users(id,name,age) values(#{id},#{name},#{age})
        </insert>
    
        
        <insert id="saveInsertUser" parameterType="org.example.entity.MyUser">
            <selectKey keyProperty="id" keyColumn="id" order="AFTER" resultType="int">
                SELECT LAST_INSERT_ID();
            </selectKey>
            insert into users(id,name,age) values(#{id},#{name},#{age})
        </insert>
    
        
        <update id="updateUser" parameterType="org.example.entity.MyUser">
            update users set name = #{name},age = #{age} where id = #{id}
        </update>
    
        
        <delete id="deleteUser" parameterType="java.lang.Integer">
            delete from users where id = #{id}
        </delete>
    
        
        <delete id="deleteUserByName" parameterType="java.lang.String">
            delete from users where name = #{name}
        </delete>
    
        
        <select id="findById" parameterType="java.lang.Integer" resultType="org.example.entity.MyUser">
            select * from USERs where id = #{id}
        </select>
    
        
        <select id="findByName" parameterType="java.lang.String" resultType="org.example.entity.MyUser">
            select * from USERs where name = #{name}
        </select>
    
        
        <select id="findByNameMf" parameterType="java.lang.String" resultType="org.example.entity.MyUser">
            select * from USERs where name like #{name};
        </select>
    
        
        <select id="findByNameMf2" parameterType="java.lang.String" resultType="org.example.entity.MyUser">
            select * from USERs where name like '%${value}%';
        </select>
    
        
        <select id="findTotal" resultType="int">
            select count(id) from USERs；
        </select>
    
        
        <select id="findUserByCondition" parameterType="org.example.entity.MyUser" resultType="org.example.entity.MyUser">
            select * from USERs where 1=1
            <if test="name != null">
                and name = #{name}
            </if>
            <if test="id != null">
                or id = #{id}
            </if>
            <if test="age != null">
                or age = #{age}
            </if>
        </select>
    
        
        <select id="findUserByBetweenAge" parameterType="org.example.entity.MyUser" resultType="org.example.entity.MyUser">
            select * from USERs where 1=1
            <if test="age != null">
                and age between 1000 and #{age}
            </if>
        </select>
    
    
        
        <select id="findUserByWhere" parameterType="org.example.entity.MyUser" resultType="org.example.entity.MyUser">
            select * from USERs where 1=1
            <choose>
                <when test="name != null and name !=''">
                    AND name LIKE CONCAT('%',#{name},'%')
                </when>
                <when test="age != null and age !=''">
                    AND age > #{age}
                </when>
                <otherwise>
                    AND id is not null
                </otherwise>
            </choose>
        </select>
    
    
        
        <select id="findUserByWhereCondition" parameterType="org.example.entity.MyUser" resultType="org.example.entity.MyUser">
            select * from USERs
            <where>
                <if test="name != null and name !=''">
                    AND name LIKE CONCAT('%',#{name},'%')
                </if>
    
                <if test="age != null and age !=''">
                    AND age > #{age}
                </if>
            </where>
        </select>
    
    
        
        <update id="updateMyUserForSet" parameterType="org.example.entity.MyUser">
            UPDATE USERs
            <set>
                <if test="name != null and name !=''">
                    name=#{name}
                </if>
    
                <if test="age != null and age !=''">
                    age=#{age}
                </if>
    
            </set>
            WHERE id=#{id}
        </update>
    
        
        <select id="findUserIn" parameterType="org.example.entity.MyUser" resultType="org.example.entity.MyUser">
            SELECT id,name,age FROM USERs WHERE age in
            <foreach collection="list"  open="("  item="sage"  close=")" separator="," >       
                #{sage}
            </foreach>
        </select>
    
    </mapper>

![1362aa8ea52d53e2201018d771fd27dd.png][]

mapper接口：

package org.example.mapper;
    
    import org.example.entity.MyUser;
    import org.apache.ibatis.annotations.Mapper;
    import java.util.List;
    @Mapper
    public interface UserMapper
    {
        public List<MyUser> findAll();
    
        void saveUser(MyUser user);
    
        int saveUser2(MyUser user);
    
        int saveInsertUser(MyUser user);
    
        int updateUser(MyUser user);
    
        int deleteUser(Integer id);
    
        int deleteUserByName(String name);
    
        MyUser findById(Integer id);
    
        List<MyUser> findByName(String name);
    
        List<MyUser> findByNameMf(String name);
    
        List<MyUser> findByNameMf2(String name);
    
        int findTotal();
    
        /**
         *查询条件可能是id、用户名，年龄；也可能都有，也可能都没有
         */
        List<MyUser> findUserByCondition(MyUser user);
    
        List<MyUser> findUserByBetweenAge(MyUser user);
    
        List<MyUser> findUserByWhere(MyUser user);
    
        List<MyUser> findUserByWhereCondition(MyUser user);
    
        int updateMyUserForSet(MyUser user);
    
        public List<MyUser> findUserIn(List<Integer> ageList);
    
    
    }

![b4db3f7b01dfc16c6f18f68a11ac1daf.png][]

service接口：

package org.example.service;
    
    import org.example.entity.MyUser;
    import java.util.List;
    
    public interface UserService
    {
        List<MyUser> findAll();
    
        void saveUser(MyUser user);
    
        int saveUser2(MyUser user);
    
        int saveInsertUser(MyUser user);
    
        int updateUser(MyUser user);
    
        int deleteUser(Integer id);
    
        int deleteUserByName(String name);
    
        MyUser findById(Integer id);
    
        List<MyUser> findByName(String name);
    
        List<MyUser> findByNameMf(String name);
    
        List<MyUser> findByNameMf2(String name);
    
        int findTotal();
    
        List<MyUser> findUserByCondition(MyUser user);
    
        List<MyUser> findUserByBetweenAge(MyUser user);
    
        List<MyUser> findUserByWhere(MyUser user);
    
        List<MyUser> findUserByWhereCondition(MyUser user);
    
        int updateMyUserForSet(MyUser user);
    
        public List<MyUser> findUserIn(List<Integer> ageList);
    
    }

![95b18a6edbb27c46484221d3709bf893.png][]

实现service接口：

package org.example.service.Impl;
    
    import org.example.entity.MyUser;
    import org.example.mapper.UserMapper;
    import org.example.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import java.util.List;
    
    @Service
    public class UserServiceImpl implements UserService
    {
        @Autowired
        UserMapper userMapper;
    
        @Override
        public List<MyUser> findAll()
        {
            return userMapper.findAll();
        }
    
        @Override
        public void saveUser(MyUser user)
        {
            userMapper.saveUser(user);
        }
    
        public int saveUser2(MyUser user)
        {
            return userMapper.saveUser2(user);
        }
    
        public int saveInsertUser(MyUser user)
        {
    
            return userMapper.saveInsertUser(user);
        }
    
        public int updateUser(MyUser user)
        {
            return userMapper.updateUser(user);
        }
    
    
        public int deleteUser(Integer id)
        {
            return userMapper.deleteUser(id);
        }
    
        public int deleteUserByName(String name)
        {
            return userMapper.deleteUserByName(name);
        }
    
        public MyUser findById(Integer id)
        {
            return userMapper.findById(id);
        }
    
        public List<MyUser> findByName(String name)
        {
            return userMapper.findByName(name);
        }
    
        public List<MyUser> findByNameMf(String name)
        {
            return userMapper.findByNameMf(name);
        }
    
        public List<MyUser> findByNameMf2(String name)
        {
            return userMapper.findByNameMf2(name);
        }
    
        public int findTotal()
        {
            return userMapper.findTotal();
        }
    
        public List<MyUser> findUserByCondition(MyUser user)
        {
    
            return userMapper.findUserByCondition(user);
        }
    
        public List<MyUser> findUserByBetweenAge(MyUser user)
        {
            return userMapper.findUserByBetweenAge(user);
        }
    
        public List<MyUser> findUserByWhere(MyUser user)
        {
            return userMapper.findUserByWhere(user);
        }
    
        public List<MyUser> findUserByWhereCondition(MyUser user)
        {
            return userMapper.findUserByWhereCondition(user);
        }
    
        public int updateMyUserForSet(MyUser user)
        {
            return userMapper.updateMyUserForSet(user);
        }
    
        public List<MyUser> findUserIn(List<Integer> ageList)
        {
            return userMapper.findUserIn(ageList);
        }
    }

![f862d85c495e849e90347a1c7121f2de.png][]

控制器：

package org.example.controller;
    
    import java.util.ArrayList;
    import java.util.List;
    import org.example.entity.MyUser;
    import org.example.service.UserService;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    @RestController
    public class MyUserController
    {
        @Autowired
        private UserService userService;
    
        @GetMapping(value = "/1")
        public List<MyUser> home1()
        {
            // 查询所有网站
            List<MyUser> users = userService.findAll();
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
            return users;
        }
    
        @GetMapping(value = "/2")
        public void home2()
        {
            MyUser user2 = new MyUser();
            user2.setName("TTT");
            user2.setAge(7878);
    
            userService.saveUser(user2);
    
        }
    
        @GetMapping(value = "/3")
        public int home3()
        {
            MyUser user2 = new MyUser();
            user2.setName("RRR");
            user2.setAge(6666);
    
            int a = userService.saveUser2(user2);
    
            System.out.println(a);
    
            return a;
    
        }
    
        @GetMapping(value = "/4")
        public int home4()
        {
            MyUser user2 = new MyUser();
            user2.setName("ccc");
            user2.setAge(321123);
    
            userService.saveInsertUser(user2);
    
            int a = user2.getId();
    
            System.out.println(a);
    
            System.out.println(user2);
    
            return a;
    
        }
    
        @GetMapping(value = "/5")
        public int updateUser()
        {
            MyUser user2 = new MyUser();
            user2.setId(1013);
            user2.setName("sprint-boot-1013");
            user2.setAge(199999);
    
            int a = userService.updateUser(user2);
    
            System.out.println(a);
    
            return a;
    
        }
    
    
        @GetMapping(value = "/6")
        public int deleteUser()
        {
            int a = userService.deleteUser(1012);
    
            System.out.println(a);
    
            return a;
    
        }
    
        @GetMapping(value = "/7")
        public int deleteUserByName()
        {
            int a = userService.deleteUserByName("sprint-boot-1013");
    
            System.out.println(a);
    
            return a;
    
        }
    
        @GetMapping(value = "/8")
        public void findById()
        {
            MyUser user001 = userService.findById(1011);
    
            System.out.println(user001);
    
        }
    
        @GetMapping(value = "/9")
        public List<MyUser> findByName()
        {
            List<MyUser> users= userService.findByName("aaa");
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
            return users;
    
        }
    
        @GetMapping(value = "/10")
        public List<MyUser> findByNameMf()
        {
            List<MyUser> users= userService.findByNameMf("%ae%");
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
            return users;
    
        }
    
    
        @GetMapping(value = "/11")
        public List<MyUser> findByNameMf2()
        {
            List<MyUser> users= userService.findByNameMf2("ae");
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
            return users;
    
        }
    
        @GetMapping(value = "/12")
        public int findTotal()
        {
            int a = userService.findTotal();
    
            return a;
    
        }
    
    
        @GetMapping(value = "/13")
        public List<MyUser> findUserByCondition()
        {
            MyUser user2 = new MyUser();
            user2.setName("aaa");
            user2.setAge(111821);
    
    
            List<MyUser> users= userService.findUserByCondition(user2);
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
    
            return users;
    
        }
    
    
        @GetMapping(value = "/14")
        public List<MyUser> findUserByBetweenAge()
        {
            MyUser user2 = new MyUser();
            user2.setAge(500000);
    
    
            List<MyUser> users= userService.findUserByBetweenAge(user2);
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
    
            return users;
    
        }
    
    
        @GetMapping(value = "/15")
        public List<MyUser> findUserByWhere()
        {
            MyUser user2 = new MyUser();
    
            user2.setName("ac");
    
            user2.setAge(800);
    
    
            List<MyUser> users= userService.findUserByWhere(user2);
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
    
            return users;
    
        }
    
        @GetMapping(value = "/16")
        public List<MyUser> findUserByWhereCondition()
        {
            MyUser user2 = new MyUser();
    
            user2.setName("ac");
    
            user2.setAge(950);
    
    
            List<MyUser> users= userService.findUserByWhereCondition(user2);
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
    
            return users;
    
        }
    
    
    
        @GetMapping(value = "/17")
        public void updateMyUserForSet()
        {
            MyUser user2 = new MyUser();
    
            user2.setName("bbb");        //执行前：{"id":1011,"name":"aaaa","age":111821}
    
            user2.setId(1011);
    
            System.out.println(user2);
    
            System.out.println(userService.updateMyUserForSet(user2));
    
        }
    
    
    
        @GetMapping(value = "/18")
        public List<MyUser> findUserIn()
        {
            List<Integer> ageList = new ArrayList<Integer>();
            ageList.add(1005);
            ageList.add(7878);
            ageList.add(111821);
    
    
            List<MyUser> users= userService.findUserIn(ageList);
    
            for (MyUser user : users)
            {
                System.out.println(user.getId()+"  "+user.getName()+"  "+user.getAge());
            }
    
            System.out.println(users);
    
            return users;
    
        }
    
    
    
    
    }

![f74f296db02b973f8cbf72e7a75787c1.png][]

执行：

![05502d7b1e80fb8402705c8dceb0b159.png][]

![1ee71a2f790bb527b199c82b020639dd.png][]

[7ba9f666bf37d15d7fbf1021c008dd4c.png]: https://img-blog.csdnimg.cn/img_convert/7ba9f666bf37d15d7fbf1021c008dd4c.png
[7dc4da5fae327ca2c50350c9cd378952.png]: https://img-blog.csdnimg.cn/img_convert/7dc4da5fae327ca2c50350c9cd378952.png
[c9139ba7ed8054835397a6381ac1b2ae.png]: https://img-blog.csdnimg.cn/img_convert/c9139ba7ed8054835397a6381ac1b2ae.png
[24e6a60d8d701babc2749a71e0bd692e.png]: https://img-blog.csdnimg.cn/img_convert/24e6a60d8d701babc2749a71e0bd692e.png
[f7b0c2055e9ed4a5506858f826e790eb.png]: https://img-blog.csdnimg.cn/img_convert/f7b0c2055e9ed4a5506858f826e790eb.png
[37172e3b79b0c38ce0fcc9d4ca44f4be.png]: https://img-blog.csdnimg.cn/img_convert/37172e3b79b0c38ce0fcc9d4ca44f4be.png
[7767fb3d1225a6fb4810294f5512379b.png]: https://img-blog.csdnimg.cn/img_convert/7767fb3d1225a6fb4810294f5512379b.png
[0bad20b4bf9b402363a9a65d3e3e8541.png]: https://img-blog.csdnimg.cn/img_convert/0bad20b4bf9b402363a9a65d3e3e8541.png
[14f08cca0a195182d406274d64c03fde.png]: https://img-blog.csdnimg.cn/img_convert/14f08cca0a195182d406274d64c03fde.png
[e126c3632bfc2e9de89bc0e4c05be639.png]: https://img-blog.csdnimg.cn/img_convert/e126c3632bfc2e9de89bc0e4c05be639.png
[b3edb655fe9f288bf061848db071e511.png]: https://img-blog.csdnimg.cn/img_convert/b3edb655fe9f288bf061848db071e511.png
[864ddb601ac8e190f79e9c0b33556ad4.png]: https://img-blog.csdnimg.cn/img_convert/864ddb601ac8e190f79e9c0b33556ad4.png
[1362aa8ea52d53e2201018d771fd27dd.png]: https://img-blog.csdnimg.cn/img_convert/1362aa8ea52d53e2201018d771fd27dd.png
[b4db3f7b01dfc16c6f18f68a11ac1daf.png]: https://img-blog.csdnimg.cn/img_convert/b4db3f7b01dfc16c6f18f68a11ac1daf.png
[95b18a6edbb27c46484221d3709bf893.png]: https://img-blog.csdnimg.cn/img_convert/95b18a6edbb27c46484221d3709bf893.png
[f862d85c495e849e90347a1c7121f2de.png]: https://img-blog.csdnimg.cn/img_convert/f862d85c495e849e90347a1c7121f2de.png
[f74f296db02b973f8cbf72e7a75787c1.png]: https://img-blog.csdnimg.cn/img_convert/f74f296db02b973f8cbf72e7a75787c1.png
[05502d7b1e80fb8402705c8dceb0b159.png]: https://img-blog.csdnimg.cn/img_convert/05502d7b1e80fb8402705c8dceb0b159.png
[1ee71a2f790bb527b199c82b020639dd.png]: https://img-blog.csdnimg.cn/img_convert/1ee71a2f790bb527b199c82b020639dd.png

spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis动态SQL——MyBatis foreach标签...

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis动态SQL——MyBatis if标签——示例001...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——删除操作——int类型——根据name删除数据...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis动态SQL——MyBatis分页...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis多表连接——collection多对一...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis动态SQL——MyBatis trim标签...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis动态SQL——MyBatis set标签...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis多表连接——association一对一...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——MyBatis动态SQL——MyBatis foreach标签...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行——保存操作——int类型...

相关 spring boot——spring boot的基本配置——spring boot整合mybatis——本地实例运行