mybatis动态sql的一些常规操作demo

本文介绍MyBatis框架中的动态SQL功能,并通过具体代码示例展示了如何实现条件查询、批量操作等功能,帮助开发者提高SQL编写效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验,你就能体会到根据不同条件拼接 SQL
语句的痛苦。例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
废话不说直接上代码

1.表结构

这里写图片描述

2. 映射类

public class User {
    private int id;
    private String name;
    private byte age;
}

3. Mapper接口

public interface UserMapper {
    User select(int id);

    int insert(User user);

    // 选择性更新
    int update(User user);

    int delete(int id);

    // 按条件模糊查询
    List<User> selectByParams(User user);

    // 查出的结果放在map中
    List<Map> selectAsMap();

    // 数组批量查询
    List<User> selectInArray(int[] arr);

    // list批量查询
    List<User> selectInList(List list);

    // set批量查询
    List<User> selectInSet(Set set);

    // 批量插入
    int insertBatch(List<User> users);

}

4.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="dao.UserMapper">
    <sql id="selectAll">
        select * from `user`
    </sql>
    <!-- parameterType可以不写 -->
    <select id="select" resultType="model.User">
        select * from `user` where id = #{id}
    </select>

    <insert id="insert" parameterType="model.User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `user`(name,age) VALUES (#{name},#{age})
    </insert>

    <update id="update" parameterType="model.User">
        UPDATE `user`
        <set>
            <if test="name!=null">
                name=#{name},
            </if>
            <if test="age!=0">
                age=#{age},
            </if>
        </set>
        WHERE id=#{id}
    </update>

    <delete id="delete" parameterType="int" >
        DELETE FROM `user` WHERE id = #{id}
    </delete>

    <select id="selectByParams" resultType="model.User">
        select * from `user`
        <where>
            <choose>
                <when test="id!=0">
                    id=#{id}
                </when>
                <otherwise>
                    <if test="name!=null">
                        /* 生成一个变量 */
                        <bind name="pattern" value="'%' + name + '%'" />
                        AND name like #{pattern}
                    </if>
                    <if test="age!=0">
                        AND age=#{age}
                    </if>
                </otherwise>
            </choose>
        </where>
    </select>
    <!-- resultType 可以填map、hashmap、java.util.Map-->
    <select id="selectAsMap"  resultType="java.util.Map">
        select * from `user`
    </select>

    <select id="selectInArray"  resultType="model.User">
        select * from `user`
        <where>
            <if test="array!=null and array.length>0">
                id IN
                <foreach collection="array" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

        </where>
    </select>
    <select id="selectInList"  resultType="model.User">
        select * from `user`
        <where>
            /*collection、list*/
            <if test="collection!=null and collection.size()>0">
                id IN
                <foreach collection="collection" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

        </where>
    </select>
    <select id="selectInSet"  resultType="model.User">
        select * from `user`
        <where>
            /*collection、list*/
            <if test="collection!=null and collection.size()>0">
                id IN
                <foreach collection="collectio" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

        </where>
    </select>


    <insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `user`(name,age) VALUES
        <foreach item="item" collection="list" separator=",">
            (#{item.name},#{item.age})
        </foreach>
    </insert>
</mapper>

5. 测试类

public class Test {
    public static void main(String[] args) throws Exception {

//        User user = session.selectOne("dao.UserMapper.select",1);
        testSelect();
//        testInsert();
//        testUpdate();
//        testDelete();
//        testSelectByParams();
//        testSelectAsMap();
//        testSelectInArray();
//        testSelectInList();
//        testSelectInSet();
//        testInsertBatch();
    }

    public static SqlSession getSession() {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);
        return session;
    }

    public static void testSelect()  {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.select(1);
        System.out.println("查询的结果:"+user);
        session.close();


    }

    public static void testInsert()  {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setName("斯坦李");
        user.setAge((byte)97);

        int total = mapper.insert(user);
        System.out.println("插入总数:"+total);
        System.out.println(user.getName()+"的id:"+user.getId());
        session.close();
    }
    public static void testUpdate()  {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setId((byte)3);
        user.setName(null);
        user.setAge((byte)58);

        int total = mapper.update(user);
        System.out.println("修改总数:"+total);
        session.close();
    }

    public static void testDelete() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);

        int total = mapper.delete(1);
        System.out.println("删除总数:"+total);
        session.close();
    }
    public static void testSelectByParams() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setId(1);
//        user.setName("四");
        user.setAge((byte)43);

        List<User> users = mapper.selectByParams(user);
        System.out.println("users:"+users);
        session.close();
    }

    public static void testSelectAsMap() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<Map> map = mapper.selectAsMap();
        System.out.println("map:"+map);
        session.close();
    }

    public static void testSelectInArray() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        int[] arr = {1,2,3,4};
        List<User> users = mapper.selectInArray(arr);
        System.out.println("users:"+users);
        session.close();
    }

    public static void testSelectInList() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List list = new ArrayList();
        list.add(1);
        list.add(2);
        list.add(3);
        list.add(4);
        List<User> users = mapper.selectInList(list);
        System.out.println("users:"+users);
        session.close();
    }

    public static void testSelectInSet() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        Set set = new HashSet();
        set.add(1);
        set.add(2);
        set.add(3);
        set.add(4);
        List<User> users = mapper.selectInSet(set);
        System.out.println("users:"+users);
        session.close();
    }

    public static void testInsertBatch() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List list = new ArrayList();
        User user1 = new User("李5",(byte)25);
        User user2 = new User("李6",(byte)25);
        User user3 = new User("李7",(byte)25);
        list.add(user1);
        list.add(user2);
        list.add(user3);
        int total = mapper.insertBatch(list);
        System.out.println("插入总数:"+total);
        System.out.println("users:"+list);
        session.close();
    }
}
### Spring Boot 3与MyBatis集成教程及配置实例 #### 配置依赖项 为了使Spring Boot项目能够顺利运行并支持MyBatis功能,在`pom.xml`文件中需引入必要的依赖库。对于Spring Boot版本3.x而言,推荐使用如下Maven依赖来实现MyBatis的支持[^1]。 ```xml <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>3.0.1</version><!-- 版本号应根据实际情况调整 --> </dependency> <!-- MySQL驱动程序 (如果数据库为MySQL) --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> ``` #### 数据源配置 在`application.properties`或者`application.yml`文件里定义数据源属性以及MyBatis特定设置。下面是一个基于YAML格式的数据源配置样例: ```yaml spring: datasource: url: jdbc:mysql://localhost:3306/testdb?useSSL=false&serverTimezone=UTC username: root password: password driver-class-name: com.mysql.cj.jdbc.Driver mybatis: mapper-locations: classpath:mapper/*.xml type-aliases-package: com.example.demo.model ``` 此部分配置指定了JDBC连接字符串、用户名、密码以及其他一些参数;同时也设置了MyBatis映射器XML的位置和实体类所在的包名[^2]。 #### 创建Mapper接口 创建Java接口作为持久层访问入口,并通过注解方式指定SQL语句或关联到外部定义好的`.sql`文件中的查询语句。例如: ```java @Mapper public interface UserMapper { @Select("SELECT * FROM users WHERE id = #{id}") User findById(Integer id); } ``` 上述代码片段展示了如何利用`@Mapper`标记一个普通的Java接口成为MyBatis Mapper接口,并且内部方法可以直接编写简单的CRUD操作对应的SQL命令[^3]。 #### 启动类扫描组件 确保启动应用程序时自动加载所有的Mapper接口,可以在主应用类上加上`@MapperScan`注释以指示要扫描的目标路径下的所有Mapper接口。 ```java @SpringBootApplication @MapperScan("com.example.demo.mapper")// 替换成实际的mapper所在位置 public class DemoApplication { public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); } } ``` 完成以上步骤之后,就可以按照常规方式进行业务逻辑开发了。当执行涉及数据库的操作时,只需注入相应的Mapper对象即可调用其中的方法来进行增删改查等动作。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值