MyBatis做简单的增删改查，超级详细，保证一看就会

深藏阁楼爱情的钟 2021-09-13 16:12 213阅读 0赞

## MyBatis做简单的增删改查，超级详细，保证一看就会 ##

**工具：**  
idea

**环境：**  
mysql

**步骤：  
1.导入相关的jar包  
2.将需要的包结构建好  
3.配置文件中，配置数据库相关信息，引入mapper.xml文件  
4.写sql语句  
5.测试**

*  单个插入
 *  批量插入
 *  通过id进行 查询
 *  查询所有信息
 *  通过username进行模糊查询
 *  单个删除
 *  批量删除
 *  修改

准备工作先做好，导jar包，需要的项目结构  
**1.导入相关的jar包**  
这里通过maven来管理jar包，在pom.xml中进行配置  
需要的jar包  
mysql的包  
mybayis的包  
junit的测试包

<?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">
      <modelVersion>4.0.0</modelVersion>
    
      <groupId>org.fh</groupId>
      <artifactId>mybaits_first</artifactId>
      <version>1.0-SNAPSHOT</version>
      <packaging>war</packaging>
    
      <name>mybaits_first</name>
      
      <url>http://www.example.com</url>
    
      <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
        <spring.version>4.1.7.RELEASE</spring.version>
        <hibernate.version>4.3.11.Final</hibernate.version>
      </properties>
    
      <dependencies>
        
        <dependency>
          <groupId>org.mybatis</groupId>
          <artifactId>mybatis</artifactId>
          <version>3.4.6</version>
        </dependency>

<dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.6</version>
    </dependency>
    
    
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>

</dependencies>

**2.将需要的包结构建好**  
1.maper类  
2.实体类  
3.SqlMapConfig.xml文件，里边配置数据库相关的，包括引入相关的mapper文件  
4.相关的mapper.xml文件  
5.测试类

如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 1]

**3.配置文件中，配置数据库相关信息，引入mapper.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>
        <environments default="development">
            <environment id="development">
                <transactionManager type="JDBC" />
                
                <dataSource type="POOLED">
                    <property name="driver" value="com.mysql.jdbc.Driver" />
                    <property name="url" value="jdbc:mysql://localhost:3306/1908a" />
                    <property name="username" value="root" />
                    <property name="password" value="root" />
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
            
            <mapper resource="mapper/userMapper.xml"/>
        </mappers>
    
    </configuration>

**准备工作已做好，开始做基础的增删改查**

## 增加 ##

**单个增加**  
**UserMapper.java**中，写一个新增的有参数，无返回值的方法  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 2]  
**userMapper.xml文件  
超级全面的注释，看不懂多看几遍，绝对明白了**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 3]  
userMapper.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="com.fh.mapper.UserMapper">
        
        
        <insert id="insertUser" parameterType="com.fh.model.User" useGeneratedKeys="true" keyProperty="id">
    
            
            <selectKey resultType="int" order="AFTER" keyProperty="userId">select LAST_INSERT_ID() </selectKey>
            
            insert  into  t_user_idea(user_id,user_name,user_sex) values
                (#{userId},#{username},#{sex})
        </insert>
    </mapper>

**此处用junit测试是否成功**  
**UserMapperTest.java**

@Test
        public void insetUserTest() throws IOException {
            //从项目的resources文件夹，获取配置信息（url,username,password,mysqlDriver,ClothingMapper.xml文件路径）
            String resource="SqlMapConfig.xml";
            InputStream inputStream= Resources.getResourceAsStream(resource);
            //创建工厂（连接mysql数据库）
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
            //通过工厂创建会话
            SqlSession sqlSession=factory.openSession();
            //        //进行赋值
            User user = new User();
            // dept.setDeptId(1);
            user.setUsername("部门22");
            user.setSex(1);
            //第一个参数：所调用的sql语句：namespace+‘.’+SqlID
            //第二个参数：传入的参数
            int count = sqlSession.insert("com.fh.mapper.UserMapper.insertUser",user);
            //手动提交事务，因为mysql自动提交时间不确定。
            sqlSession.commit();
            System.out.println("插入数据条数："+count);
            //关闭session会话
            sqlSession.close();
    
        }

**单个插入完成**

## 批量插入 ##

**UserMapper.java**

//批量插入
      int batchInsertUser(List<User> userList);

**userMapper.xml**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 4]

<insert id="batchInsertUser" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    
            
    
            insert  into t_user_idea(user_id,user_name,user_sex) values
            <foreach collection="list" item="user" index="index" separator=",">
                (#{user.userId},#{user.username},#{user.sex})
            </foreach>
        </insert>

**测试**  
**UserMapperTest.java**

/**
         * 批量插入
         * @throws IOException
         */
        @Test
        public void batchInsertUserTest() throws IOException {
            //从项目的resources文件夹，获取配置信息（url,username,password,mysqlDriver,ClothingMapper.xml文件路径）
            String resource="SqlMapConfig.xml";
            InputStream inputStream= Resources.getResourceAsStream(resource);
            //创建工厂（连接mysql数据库）
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
            //通过工厂创建会话
            SqlSession sqlSession=factory.openSession();
            //        //进行赋值
            List<User> userList = new ArrayList<>();
            for (int i = 0; i < 10; i++) {
                User user = new User();
                user.setUsername("username" + i);
                user.setSex(1);
                userList.add(user);
            }
            //第一个参数：所调用的sql语句：namespace+‘.’+SqlID
            //第二个参数：传入的参数
            int count = sqlSession.insert("com.fh.mapper.UserMapper.batchInsertUser",userList);
            //手动提交事务，因为mysql自动提交时间不确定。
            sqlSession.commit();
            System.out.println("插入数据条数："+count);
            //关闭session会话
            sqlSession.close();
        }

**批量插入完成**

未完待更新

## 通过id查询数据 ##

**UserMapper.java**

//通过ID进行查询
        User findUserById(Integer userId);

**userMapper.xml**

<select resultType="com.fh.model.User" parameterType="integer" id="findUserById">
            select user_id as userId,user_name as username,user_sex as sex from t_user_idea where user_id = #{userId}
        </select>

**UserMapperTest.java**

@Test
        public void findUserByIdTest() throws IOException {
            //从项目的resources文件夹，获取配置信息（url,username,password,mysqlDriver,ClothingMapper.xml文件路径）
            String resource="SqlMapConfig.xml";
            InputStream inputStream= Resources.getResourceAsStream(resource);
            //创建工厂（连接mysql数据库）
             SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
            //通过工厂创建会话
            SqlSession sqlSession=factory.openSession();
    
            //第一个参数：所调用的sql语句：namespace+‘.’+SqlID
            //第二个参数：传入的参数
            //这里调用的是selectOne方法，只能查询一条指定的数据，
            // 如果用它查询多条数据，会报异常(查询多条要用selectList)
            User user = sqlSession.selectOne("com.fh.mapper.UserMapper.findUserById",11);
            System.out.println(user);
            //关闭session会话
            sqlSession.close();
    
        }

结果：  
![在这里插入图片描述][20200521102122484.png]

## 查询所有信息 ##

**UserMapper.java**

//查询所有信息
        User findAllUser();

**userMapper.xml**

<select resultType="com.fh.model.User"  id="findAllUser">
            select user_id as userId,user_name as username,user_sex as sex from t_user_idea
        </select>

测试  
**UserMapperTest.java**

/**
         * 查询所有信息
         * @throws IOException
         */
        @Test
        public void findAllUserTest() throws IOException {
            //从项目的resources文件夹，获取配置信息（url,username,password,mysqlDriver,ClothingMapper.xml文件路径）
            String resource="SqlMapConfig.xml";
            InputStream inputStream= Resources.getResourceAsStream(resource);
            //创建工厂（连接mysql数据库）
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
            //通过工厂创建会话
            SqlSession sqlSession=factory.openSession();
    
            //第一个参数：所调用的sql语句：namespace+‘.’+SqlID
            //第二个参数：传入的参数
            //这里调用的是selectOne方法，只能查询一条指定的数据，
            // 如果用它查询多条数据，会报异常(查询多条要用selectList)
            List<User> userList = sqlSession.selectList("com.fh.mapper.UserMapper.findAllUser");
            for (User user:userList) {
                System.out.println(user);
            }
            //关闭session会话
            sqlSession.close();
        }

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 5]

## 通过username进行模糊查询 ##

**UserMapper.java**

//通过name进行模糊查询
        User findUserByUserName(String username);

**userMapper.xml**

<select id="findUserByUserName" resultType="com.fh.model.User" parameterType="String">
    
            
            select user_id as userId,user_name as username,user_sex as sex from t_user_idea where user_name like concat("%",#{username},"%")
        </select>

**UserMapperTest.java**

/**
         * 通过name进行模糊查询
         * @throws IOException
         */
        @Test
        public void findUserByUserNameTest() throws IOException {
            String resource="SqlMapConfig.xml";
            //读取配置文件
            InputStream inputStream = Resources.getResourceAsStream(resource);
            //连接上数据库
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
            //开启会话
            SqlSession sqlSession = factory.openSession();
            //第一个参数定位带ClothingMapper.xml的findClothingByName方法
            //第二个参数的是findClothingByName方法的参数
            List<User> userList =  sqlSession.selectList("com.fh.mapper.UserMapper.findUserByUserName","2");
            for (User user:userList) {
                System.out.println(user);
            }
            System.out.println(userList.size());
        }

结果：  
![在这里插入图片描述][20200521110508996.png]

## 通过username和sex进行条件查询 ##

**UserMapper.java**

//通过username和sex进行模糊查询
        User queryUserByNameAndSexList(User user);

**userMapper.xml**

<select id="queryUserByNameAndSexList" parameterType="com.fh.model.User" resultType="com.fh.model.User">
    
            
            select user_id as userId,user_name as username,user_sex as sex from t_user_idea
            <where>
                <if test=" username != null and username != '' ">
                    and user_name like concat('%',#{username},'%')
                </if>
                <if test="sex != null">
                    and user_sex = #{sex}
                </if>
            </where>
    
        </select>

测试  
**UserMapperTest.java**

/**
         * 通过username和sex进行条件查询
         * @throws IOException
         */
        @Test
        public void queryUserByNameAndSexListTest() throws IOException {
            String resource="SqlMapConfig.xml";
            //读取配置文件
            InputStream inputStream = Resources.getResourceAsStream(resource);
            //连接上数据库
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
            //开启会话
            SqlSession sqlSession = factory.openSession();
            //模糊查询的条件
            User user = new User();
            user.setUsername("1");
            user.setSex(1);
            //第一个参数定位带ClothingMapper.xml的findClothingByName方法
            //第二个参数的是findClothingByName方法的参数
            List<User> userList =  sqlSession.selectList("com.fh.mapper.UserMapper.queryUserByNameAndSexList",user);
            for (User user1:userList) {
                System.out.println(user1);
            }
            System.out.println(userList.size());
        }

## 删除数据 ##

**UserMapper.java**

//通过ID删除数据
        void deleteUserById(Integer userId);

**userMapper.xml**

<delete id="deleteUserById" parameterType="int">
            delete from t_user_idea where user_id = #{userId}
        </delete>

测试  
**UserMapperTest.java**

/**
         * 删除
         * @throws IOException
         */
        @Test
        public void deleteDeptTest() throws IOException {
            //从项目的resources文件夹，获取配置信息（url,username,password,mysqlDriver,ClothingMapper.xml文件路径）
            String resource="SqlMapConfig.xml";
            InputStream inputStream= Resources.getResourceAsStream(resource);
            //创建工厂（连接mysql数据库）
             SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
            //通过工厂创建会话
            SqlSession sqlSession=factory.openSession();
    
            //第一个参数：所调用的sql语句：namespace+‘.’+SqlID
            //第二个参数：传入的参数
            //这里调用的是selectOne方法，只能查询一条指定的数据，
            // 如果用它查询多条数据，会报异常(查询多条要用selectList)
            sqlSession.delete("com.fh.mapper.UserMapper.deleteUserById",8);
    
            sqlSession.commit();
            //关闭session会话
            sqlSession.close();
        }

## 修改 ##

**UserMapper.java**

//修改
        void updateUser(User user);

**userMapper.xml**

<update id="updateUser" parameterType="com.fh.model.User">
    
            update t_user_idea
            set user_name=#{username},user_sex=#{sex}
            where user_id = #{userId}
    
        </update>

测试  
**UserMapperTest.java**

/**
         * 修改
         * @throws IOException
         */
        @Test
        public void updateDeptTest() throws IOException {
            //从项目的resources文件夹，获取配置信息（url,username,password,mysqlDriver,ClothingMapper.xml文件路径）
            String resource="SqlMapConfig.xml";
            InputStream inputStream= Resources.getResourceAsStream(resource);
            //创建工厂（连接mysql数据库）
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
            //通过工厂创建会话
            SqlSession sqlSession=factory.openSession();
    
            //第一个参数：所调用的sql语句：namespace+‘.’+SqlID
            //第二个参数：传入的参数
            User user = new User();
            //必须写id，这样才可以修改
            user.setUserId(7);
            user.setUsername("张三");
            user.setSex(0);
            sqlSession.update("com.fh.mapper.UserMapper.updateUser",user);
            sqlSession.commit();
            //关闭session会话
            sqlSession.close();
        }

## 批量删除 ##

**UserMapper.java**

//批量删除
        int deleteUserByIds(List<Integer> idList);

**userMapper.xml**

<delete id="deleteUserByIds" parameterType="list">
            delete from t_user_idea
            where user_id in
            
            <foreach collection="list" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </delete>

测试  
**UserMapperTest.java**

/**
         * 批量删除
         */
    
        @Test
        public void deleteUserByIdsTest() throws IOException {
            //从项目的resources文件夹，获取配置信息（url,username,password,mysqlDriver,ClothingMapper.xml文件路径）
            String resource="SqlMapConfig.xml";
            InputStream inputStream= Resources.getResourceAsStream(resource);
            //创建工厂（连接mysql数据库）
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(inputStream);
            //通过工厂创建会话
            SqlSession sqlSession=factory.openSession();
    
            ArrayList<Integer> idList = new ArrayList<>();
            idList.add(13);
            idList.add(14);
            idList.add(15);
    
            //第一个参数：所调用的sql语句：namespace+‘.’+SqlID
            //第二个参数：传入的参数
            //int row =  sqlSession.deleteUserByIds("com.fh.mapper.UserMapper.deleteUserById",idList);
            int row = sqlSession.delete("com.fh.mapper.UserMapper.deleteUserByIds",idList);
            sqlSession.commit();
            //关闭session会话
            sqlSession.close();
        }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70]: /images/20210913/47ca48f5fc7e4acc8dc579c7e7d3fd8c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 1]: /images/20210913/783d616b2806406aab86327fbdb8686a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 2]: /images/20210913/712e84bd92fb44c6b9ea631d6135c3d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 3]: /images/20210913/e0659d1ac2a2403482c0abf737a21239.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 4]: /images/20210913/88edbec0b82e47169088c34ccdf9adeb.png
[20200521102122484.png]: /images/20210913/5cc1423be44d4c97b71fe627e92e5eb9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3N1bnJqX25pdQ_size_16_color_FFFFFF_t_70 5]: /images/20210913/66720f300bfb413fa3afc9f96d90b049.png
[20200521110508996.png]: /images/20210913/46604edb55804008b639b02f8d301ff1.png