MyBatis简单CRUD操作，以及动态SQL

最新推荐文章于 2023-04-28 10:58:02 发布

－觅－

最新推荐文章于 2023-04-28 10:58:02 发布

阅读量166

点赞数

分类专栏： SpringBoot

本文链接：https://blog.csdn.net/qq_36732299/article/details/107578444

版权

SpringBoot 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

本文详细介绍了如何使用MyBatis进行简单的CRUD操作，并探讨了动态SQL的运用。从dao层的实现，到Controller的控制，再到service的服务层处理，以及mybatis-config.xml和springContext.xml的配置，全方位解析MyBatis在实际项目中的应用。

摘要由CSDN通过智能技术生成

在这里插入图片描述

dao层

@Repository
public interface UserMapper {
    //1 查询全部用户
    List<User> findAll();
    //2 添加用户
    void addUser(User user);
//    //3 根据id修改用户
    void updateUser(User user);
//    //4 根据id删除用户
    void deleteUser(User user);

    /*
    动态SQL练习
     */
    //if
    List<User> findUserSelective(User user);
    //choose when otherwise
    List<User> findUserChoose(User user);
    //where
    List<User> findUserWhere(User user);
    //set 用于update操作
    void updateUserSet(User user);


    //trim 1.查询where
    //trim 2.set根据id更新用户信息
    
    List<User> selectUserTrim(User user);
    void updateUserByIdTrim(User user);

    //includ和
    /*prefixOverrides 去掉第一个条件开头对应的值（and 或 or）
      suffixOverrides 去掉最后一个内容的逗号*/
    List<User> selectInclude(User user);

    //foreach就是in
    List<User> selectForeach(List<Integer> list);
}

实现类

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
        "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="com.liu.demo.dao.UserMapper">
    <select id="findAll" resultType="user">
        select * from users
    </select>
    <insert id="addUser">
    insert into users values(#{id},#{username},#{account},#{password})
    </insert>
    <update id="updateUser" >
    update users set username=#{username},account=#{account},password=#{password} where id=#{id}
    </update>
    <delete id="deleteUser" parameterType="int">
        delete from users where id=${id}
    </delete>

<!--    动态SQL练习0722-->
<!--    SQL-if-->
    <select id="findUserSelective" resultType="user" parameterType="user">
        select * from users where 1=1
        <if test="id!=0">and id=#{id}</if>
        <if test="username!=null">or username like '%${username}%'</if>
    </select>
<!--    SQL-choose
类似与switch cash
-->
    <select id="findUserChoose" resultType="user">
        select * from users where true
        <choose>
            <when test="id!=0">and id=${id}</when>
            <when test="username!=null">and username like '%${username}%'</when>
            <otherwise>and 1!=1</otherwise>
        </choose>
    </select>
<!--    SQL-where-->
<select id="findUserWhere" resultType="user">
    select * from users
    <where>
        <if test="id=2">id=2</if>
        <if test="username!=null">and username like '%${username}%'</if>
    </where>
</select>
<!--  set 用于update操作
默认去掉最后一个逗号。
-->
    <select id="updateUserSet" parameterType="user">
        update users
        <set>
            password=#{password},account=#{account}
        </set>
        where id=${id};
    </select>
<!--    //trim 1.查询where-->
    <select id="selectUserTrim" resultType="user" >
        select * from users
        <trim prefix="WHERE">
            <if test="id!=0">id=#{id}</if>
            <if test="username=null">and username like '%${username}%'</if>
        </trim>
    </select>
<!--    //trim 2.set根据id更新用户信息-->
    <update id="updateUserByIdTrim" >
        update users
        <trim prefix="set" suffixOverrides=",">
        <if test="account!=null">account=#{account},</if>
        <if test="password!=null">password=#{password},</if>
        </trim>
        where id=#{id}
    </update>

    <!-- SQL片段 includ和
       prefixOverrides 去掉第一个条件开头对应的值（and 或 or）
      suffixOverrides 去掉最后一个内容的逗号-->
    <select id="selectInclude" resultType="user" parameterType="user">
        select * from users
        <include refid="whereid1"></include>
    </select>
    <sql id="whereid1">
        <trim prefix="where" prefixOverrides="and">
            <if test="id!=0">and id=#{id}</if>
            <if test="username!=null">and username=#{username}</if>
        </trim>
    </sql>

<!--    foreach-->
    <select id="selectForeach" resultType="user" parameterType="int">
        select * from users where id in
        <foreach collection="list" item="id" index="index" open="(" separator="," close=")"></foreach>
            #{id}
    </select>
</mapper>

Controller

@Controller
public class UserController {
    public static void main(String[] args) {
        //都spring配置文件
        ApplicationContext context = new ClassPathXmlApplicationContext("springContext.xml");
        UserService userService = (UserService) context.getBean("userService");
        //查询
//        List<User> users = userService.findAll();
//        System.out.println(users);
        //添加
//        User user = new User(3,"黄忠","1003","333333");
//        userService.addUser(user);
        //更新
//        User user1 = new User(3,"张飞","1003","333333");
//        userService.updateUser(user1);
        //删除
//        User user1 = new User(3,"张飞","1003","333333");
//        userService.deleteUser(user1);
        /*
        0720 动态SQL练习
         */
        //第一种where set查询if
       /* User user2 = new User();
        user2.setId(1002);
        user2.setUsername("羽");
        List<User> userSelective = userService.findUserSelective(user2);
        System.out.println(userSelective);*/

        //第二种where set查询
      /*  User user3 = new User();
        user3.setId(1);
        user3.setUsername("备");
        List<User> userChoose = userService.findUserChoose(user3);
        System.out.println(userChoose);*/

        //<where>
        /*User user4 = new User();
        user4.setId(2);
        user4.setUsername("羽");
        List<User> userWhere = userService.findUserWhere(user4);
        System.out.println(userWhere);*/

        //<set>
//        User user5 = new User();
//        user5.setId(2);
//        user5.setAccount("1012");
//        user5.setPassword("关羽");
//        userService.updateUserSet(user5);

        //1.动态SQL- <trim prefix="WHERE">
        /*User user6 = new User();
        user6.setId(2);
        user6.setPassword("关");
        List<User> users = userService.selectUserTrim(user6);
        System.out.println(users);*/

        //2.动态SQL-  <trim prefix="set">
        /*User user6 = new User();
       user6.setId(2);
       user6.setPassword("222222");
       user6.setAccount("1002");
       userService.updateUserByIdTrim(user6);*/

        //SQL片段  include
       /* User user = new User();
        user.setId(2);
        user.setUsername("关羽");
        List<User> users = userService.selectInclude(user);
        System.out.println(users);*/

        foreach就是in

        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        List<User> users = userService.selectForeach(list);
        System.out.println(users);
    }
}

service

@Controller
public class UserController {
    public static void main(String[] args) {
        //都spring配置文件
        ApplicationContext context = new ClassPathXmlApplicationContext("springContext.xml");
        UserService userService = (UserService) context.getBean("userService");
        //查询
//        List<User> users = userService.findAll();
//        System.out.println(users);
        //添加
//        User user = new User(3,"黄忠","1003","333333");
//        userService.addUser(user);
        //更新
//        User user1 = new User(3,"张飞","1003","333333");
//        userService.updateUser(user1);
        //删除
//        User user1 = new User(3,"张飞","1003","333333");
//        userService.deleteUser(user1);
        /*
        0720 动态SQL练习
         */
        //第一种where set查询if
       /* User user2 = new User();
        user2.setId(1002);
        user2.setUsername("羽");
        List<User> userSelective = userService.findUserSelective(user2);
        System.out.println(userSelective);*/

        //第二种where set查询
      /*  User user3 = new User();
        user3.setId(1);
        user3.setUsername("备");
        List<User> userChoose = userService.findUserChoose(user3);
        System.out.println(userChoose);*/

        //<where>
        /*User user4 = new User();
        user4.setId(2);
        user4.setUsername("羽");
        List<User> userWhere = userService.findUserWhere(user4);
        System.out.println(userWhere);*/

        //<set>
//        User user5 = new User();
//        user5.setId(2);
//        user5.setAccount("1012");
//        user5.setPassword("关羽");
//        userService.updateUserSet(user5);

        //1.动态SQL- <trim prefix="WHERE">
        /*User user6 = new User();
        user6.setId(2);
        user6.setPassword("关");
        List<User> users = userService.selectUserTrim(user6);
        System.out.println(users);*/

        //2.动态SQL-  <trim prefix="set">
        /*User user6 = new User();
       user6.setId(2);
       user6.setPassword("222222");
       user6.setAccount("1002");
       userService.updateUserByIdTrim(user6);*/

        //SQL片段  include
       /* User user = new User();
        user.setId(2);
        user.setUsername("关羽");
        List<User> users = userService.selectInclude(user);
        System.out.println(users);*/

        foreach就是in

        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        List<User> users = userService.selectForeach(list);
        System.out.println(users);
    }
}

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>
<!--    别名-->
    <typeAliases>
        <package name="com.liu.demo"/>
    </typeAliases>
<!--    mapper映射文件-->
    <mappers>
        <package name="com.liu.demo.dao"/>
    </mappers>

</configuration>

springContext.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 http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd">
<!--包扫描-->
    <context:component-scan base-package="com.liu.demo"/>
<!-- 引入db配置文件-->
    <context:property-placeholder file-encoding="UTF-8" location="db.properties"/>
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${driver}"></property>
        <property name="url" value="${url}"></property>
        <property name="username" value="${user}"></property>
        <property name="password" value="${pwd}"></property>
    </bean>

<!--    配置SqlSessionFactory对象-->
   <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
       <!-- 注入数据库连接池-->
       <property name="dataSource" ref="dataSource"/>
       <property name="configLocation" value="mybatis-config.xml"/>
   </bean>
<!--    配置studentMapper对象-->
    <bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="com.liu.demo.dao.UserMapper"/>
        <property name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean>
</beans>