您的当前位置：首页 MyBatis之增删查改功能

MyBatis之增删查改功能

来源：华佗小知识

文章目录

一、创建各种类
二、MyBatis的各种功能
- 1、查询<select>
- 2、增加<insert>
- 3、修改<update>
- 4、删除<delete>
三、总结

前言

在MyBatis项目中编写代码实现对MySql数据库的增删查改

一、创建各种类

1、在Java包的mapper文件下创建一个接口

我创建的接口名为：SelectMapper，

注意：接口名之后要和写sql的xml文件名保持一致

这个接口专门用来写增删查改的方法，和sql分离开

2、在resources包的mapper文件下创建一个xml文件

我的xml文件名为SelectMapper,和接口名保持一致

这个xml文件专门用来写sql语句

3、在text包的java文件下创建一个测试类

我的测试类名为SelectMapperText，这个名称没有固定的要求

这个测试类专门用来测试对数据库的操作是否成功

二、MyBatis的各种功能

在stu库下有一张名为User的表，下面对这张表进行各种功能实现

1、查询功能<select>

因为查询的内容返回的数据不止一条，因此我们用List集合作为返回值

SelectMapeer接口下定义方法名：List<User> getAllUser()

public interface SelectMapper {
    /*
        若查询多条，不能用实体类对象为返回值，否则报错
    */

    //查询所有信息
    List<User> getAllUser();

}

SelectMapper的xml文件下写sql语句

<?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.mybatis.mapper.SelectMapper">

<!--    List<User> getAllUser();-->
    <select id="getAllUser" resultType="User">
        select *from user
    </select>

</mapper>

注意：mapper的namespace必须对应接口名，select的id必须是我们写的方法名，两个必须要一样。查询结果需要返回，因此有resultType为实体类名

SelectMapperText类开始测试

import com.mybatis.Util.SqlSessionUtil;
import com.mybatis.mapper.SelectMapper;
import com.mybatis.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.Test;

import javax.crypto.spec.PSource;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class SelectMapperText {

    @Test
    public void getAllUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        List<User> allUser = mapper.getAllUser();
        allUser.forEach(s-> System.out.println(s));
    }

}

运行getAllUser后为：

成功查到所有用户信息！！！

2、增加功能<insert>

向User表增加一个用户，用户信息为：

用户名：sb,密码：123456，年龄：20，性别：男，邮件：123456@qq.com

SelectMapeer接口下定义方法名：void insertUser(User user);

package com.mybatis.mapper;

import com.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface SelectMapper {

    //查询所有信息
    List<User> getAllUser();

    //插入用户信息
    void insertUser(User user);
}

SelectMapper的xml文件下写sql语句

<?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.mybatis.mapper.SelectMapper">

<!--    List<User> getAllUser();-->
    <select id="getAllUser" resultType="User">
        select *from user
    </select>

<!--    void insertUser(User user);-->
    <insert id="insertUser">
        insert into user values (null,#{username},#{password},#{age},#{sex},#{email})
    </insert>

</mapper>

SelectMapperText类开始测试

import com.mybatis.Util.SqlSessionUtil;
import com.mybatis.mapper.SelectMapper;
import com.mybatis.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.Test;

import javax.crypto.spec.PSource;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class SelectMapperText {

    @Test
    public void getAllUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        List<User> allUser = mapper.getAllUser();
        allUser.forEach(s-> System.out.println(s));
    }

    @Test
    public void insertUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        User user = new User(null,"sb","123456",20,"男","123456@qq.com");
        mapper.insertUser(user);
    }

}

成功添加用户信息！！！

3、修改功能<update>

修改用户名为admin的信息，将性别改为女

SelectMapeer接口下定义方法名： void updateUser(String username)

package com.mybatis.mapper;

import com.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface SelectMapper {

    //查询所有信息
    List<User> getAllUser();

    //插入用户信息
    void insertUser(User user);

    //修改用户信息
    void updateUser(@Param("username") String username);//用注释当sql名
}

SelectMapper的xml文件下写sql语句

<?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.mybatis.mapper.SelectMapper">

<!--    List<User> getAllUser();-->
    <select id="getAllUser" resultType="User">
        select *from user
    </select>

<!--    void insertUser(User user);-->
    <insert id="insertUser">
        insert into user values (null,#{username},#{password},#{age},#{sex},#{email})
    </insert>

<!--    void updateUser(@Param("username") String username);-->
    <update id="updateUser">
        update user set sex = '女' where username=#{username}
    </update>

</mapper>

SelectMapperText类开始测试

import com.mybatis.Util.SqlSessionUtil;
import com.mybatis.mapper.SelectMapper;
import com.mybatis.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.Test;

import javax.crypto.spec.PSource;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class SelectMapperText {

    @Test
    public void getAllUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        List<User> allUser = mapper.getAllUser();
        allUser.forEach(s-> System.out.println(s));
    }

    @Test
    public void insertUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        User user = new User(null,"sb","123456",20,"男","123456@qq.com");
        mapper.insertUser(user);
    }

   @Test
    public void updateUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        mapper.updateUser("admin");
    }

}

成功修改用户信息！！！

4、删除功能<update>

删除用户为“root”的所有信息

SelectMapeer接口下定义方法名：void deleteUser(String username)

package com.mybatis.mapper;

import com.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface SelectMapper {

    //查询所有信息
    List<User> getAllUser();

    //插入用户信息
    void insertUser(User user);

    //修改用户信息
    void updateUser(@Param("username") String username);//用注释当sql名

    //删除用户信息
    void deleteUser(@Param("username") String username);
}

SelectMapper的xml文件下写sql语句

<?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.mybatis.mapper.SelectMapper">

<!--    List<User> getAllUser();-->
    <select id="getAllUser" resultType="User">
        select *from user
    </select>

<!--    void insertUser(User user);-->
    <insert id="insertUser">
        insert into user values (null,#{username},#{password},#{age},#{sex},#{email})
    </insert>

<!--    void updateUser(@Param("username") String username);-->
    <update id="updateUser">
        update user set sex = '女' where username=#{username}
    </update>

<!--    void deleteUser(@Param("username") String username)-->
    <delete id="deleteUser">
        delete  from user where username = #{username}
    </delete>
</mapper>

SelectMapperText类开始测试

import com.mybatis.Util.SqlSessionUtil;
import com.mybatis.mapper.SelectMapper;
import com.mybatis.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.Test;

import javax.crypto.spec.PSource;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

public class SelectMapperText {

    @Test
    public void getAllUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        List<User> allUser = mapper.getAllUser();
        allUser.forEach(s-> System.out.println(s));
    }

    @Test
    public void insertUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        User user = new User(null,"sb","123456",20,"男","123456@qq.com");
        mapper.insertUser(user);
    }

   @Test
    public void updateUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        mapper.updateUser("admin");
    }

    @Test
    public void deleteUser() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
        SqlSession sqlSession = sessionFactory.openSession(true);
        SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
        mapper.deleteUser("root");
    }
}

成功删除用户信息！！！

三、总结

首先创造好接口写入要进行的方法名，然后创建xml文件专门写sql语句，值得注意的是：namespace要和接口名相同，id名要和接口里面的方法名相同，在测试类中：先获取映射文件，然后创建数据会议工厂生成数据会议，最后创建mapper使用接口的函数。

总之流程为：

1、创建接口写方法名。

2、创建xml文件写sql语句。

3、测试类进行测试

如果本篇文章对你有用的话，请一键三连支持一下博主，一起共同学习共同进步！！！

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文