Mybatis增删改查操作

原创

兮动人

发布于 2022-03-06 10:24:37

5370

1. 增加数据操作

编写UserMapper映射文件

<!--插入操作-->
<insert id="add" parameterType="com.xdr630.domain.User">
    insert into user values(#{id},#{username},#{password})
</insert>

创建user表
在这里插入图片描述
User实体类

public class User {
    private int id;
    private String username;
    private String password;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

映射文件UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="userMapper">

    <!--插入操作-->
    <insert id="add" parameterType="com.xdr630.domain.User">
        insert into user values(#{id}, #{username}, #{password})
    </insert>
    
</mapper>

核心文件SqlMapConfig.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"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/test"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--加载配置映射文件-->
    <mappers>
        <mapper resource="com/xdr630/mapper/UserMapper.xml"></mapper>
    </mappers>
</configuration>

测试插入数据

	@Test
    public void test1() throws IOException {

        //模拟 user 对象
        User user = new User();
        user.setId(5)
        user.setUsername("雲兮动人");
        user.setPassword("12345678");
        //获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
        //获得sqlsession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace + id
        int insert = sqlSession.insert("userMapper.add", user);
        System.out.println(insert);
        //提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }

添加操作注意的问题：

插入语句使用insert标签<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="userMapper">  <insert id="save" parameterType="com.xdr630.domain.User"> insert into user values(#{id}, #{username}, #{password}) </insert>  <delete id="delete" parameterType="java.lang.Integer"> delete from user where id = #{id} </delete> </mapper>
在映射文件中使用parameterType属性指定要插入的数据类型
Sql语句中使用#{实体属性名}方式引用实体中的属性值
插入操作使用的API是sqlSession.insert(“命名空间.id”,实体对象);
插入操作涉及数据库数据变化，所以要使用sqlSession对象显示的提交事务，即sqlSession.commit() 2. 删除数据操作
其他条件不变，修改映射文件 UserMapper.xml 如下
测试 delete，删除第五条数据

	@Test
    public void test2() throws IOException {

        //获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
        //获得sqlsession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace + id
        int delete = sqlSession.delete("userMapper.delete", 5);
        System.out.println(delete);
        //提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }

删除操作注意问题：

删除语句使用delete标签
Sql语句中使用#{任意字符串}方式引用传递的单个参数
删除操作使用的API是sqlSession.delete(“命名空间.id”,Object);3. 修改数据操作

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="userMapper">

    <!--插入操作-->
    <insert id="save" parameterType="com.xdr630.domain.User">
        insert into user values(#{id},#{username},#{password})
    </insert>

    <!--删除操作-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id}
    </delete>

    <!--修改操作-->
    <update id="update" parameterType="com.xdr630.domain.User">
        update user set username = #{username}, password=  #{password} where id = #{id}
    </update>
</mapper>

	@Test
    public void test3() throws IOException {

        //模拟 user 对象
        User user = new User();
        user.setId(4);
        user.setUsername("mike");
        user.setPassword("2233");
        //获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
        //获得sqlsession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace + id
        sqlSession.update("userMapper.update", user);
        //提交事务
        sqlSession.commit();
        //释放资源
        sqlSession.close();
    }

修改第四条数据
在这里插入图片描述

修改操作注意问题:

修改语句使用update标签
修改操作使用的API是sqlSession.update(“命名空间.id”,实体对象);

4. 查询数据操作

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="userMapper">

    <!--插入操作-->
    <insert id="save" parameterType="com.xdr630.domain.User">
        insert into user values(#{id},#{username},#{password})
    </insert>

    <!--删除操作-->
    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id}
    </delete>

    <!--修改操作-->
    <update id="update" parameterType="com.xdr630.domain.User">
        update user set username = #{username}, password=  #{password} where id = #{id}
    </update>
	
	<!--查询操作-->
    <select id="findAll" resultType="com.xdr630.domain.User">
        select * from user
    </select>
    
</mapper>

	@Test
    public void test() throws IOException {
        //获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
        //获得sqlsession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace + id
        List<User> userList = sqlSession.selectList("userMapper.findAll");
        //打印数据
        System.out.println(userList);
        //释放资源
        sqlSession.close();
    }

查询单个数据对象

<!--查询单个对象-->
<select id="findById" resultType="com.xdr630.domain.User">
    select * from user where id = #{id}
</select>

	//查询一个对象
    @Test
    public void test4() throws IOException {

        //获取核心配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapperConfig.xml");
        //获得sqlsession工厂对象
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        //获得session会话对象
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //执行操作 参数：namespace + id
        User user = sqlSession.selectOne("userMapper.findById", 1);
        System.out.println(user);
        //释放资源
        sqlSession.close();
    }