Mybatis2---Mapper的增删改查

来源：五一七教育网

上篇文章构建了,本篇文章继续做一些拓展

Mapper的增删改查

1.增—>添加insert

(1)insert

UserMapper.xml

<!--    添加记录-->
    <insert id="insertUser" parameterType="com.neuedu.entity.User">
        insert into user (username,sex,birthday,address)
        values(#{username},#{sex},#{birthday},#{address})
    </insert>

UserMapperTest

 @Test
    public void insertUser(){
//        Mybatis默认是开启事务的,如果开始事务就不会自动提交--->所以需要sqlSession.commit()
//        using语句
        try(SqlSession sqlSession = sqlSessionFactory.openSession()){
            User user = new User();

            user.setUsername("李四");
            user.setSex("男");
//            重设时间-->需要格式化
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
            user.setBirthday(sdf.parse("2002-10-10"));
            user.setAddress("唐山市");

            int insert = sqlSession.insert("com.neuedu.mapper.UserMapper.insertUser",user);
            System.out.println(insert);
//            提交事务
            sqlSession.commit();
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

*开启事务的方式

1.设为自动开启

  try(SqlSession sqlSession = sqlSessionFactory.openSession(true)){...}

2.通过commit开启事务
```
  sqlSession.commit();
```

(2)获取用户自增ID

大多数时候数据库的id都会设为自增的,但是我们总会对数据库进行一些更改—>这时候我们如何知道insert对象的id呢?
UserMapper.xml
// select LAST_INSERT_ID()—>sql语句—>进行id的查询

<!--    自增ID查询-->
    <insert id="insertGetId" parameterType="com.neuedu.entity.User">
<!--        keyColumns是我们要获取的对象
            keyProperty是获取对象的值之后,这个值要给谁
            order="AFTER" 指insert之后,再把对象给到我们
            -->
        <selectKey keyColumn="id" keyProperty="id" order="AFTER" resultType="int">
            select LAST_INSERT_ID()
        </selectKey>

        insert into user (username,sex,birthday,address)
        values(#{username},#{sex},#{birthday},#{address})
    </insert>

UserMapperTest

@Test
    public void insertGetId(){
        try(SqlSession sqlSession = sqlSessionFactory.openSession()){
            User user = new User();

            user.setUsername("王五");
            user.setSex("男");
//            重设时间-->需要格式化
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
            user.setBirthday(sdf.parse("2002-10-10"));
            user.setAddress("唐山市");

            int insert = sqlSession.insert("com.neuedu.mapper.UserMapper.insertGetId", user);
//            因为把参数传到了User类中,所以可以直接用getId()函数获取
            System.out.println(user.getId());
//            提交事务
            sqlSession.commit();
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

(3)获取UUID

如果两个表结构相同–>当我们想要合并时,因为id是自增的—>所以会出现重复的现象
怎么解决?—>UUID(全局标识)—>因为UUID为字符型,所以要修改一下表结构和User类
数据库–>复制一份表,id的类型改为varchar
User_1
复制一个User类,命名为User_1===>与数据库相对应的===>把id的数据类型改为String

package com.neuedu.entity;

import lombok.Data;

import java.util.Date;

//这里的内容 与数据库表信息对应
@Data
public class User_1 {
    private String id;
    private String username;
    private String sex;
    private Date birthday;
    private String address;
}

UserMapper.xml

<!--    获取UUID-->
    <insert id="insertGetUUID" parameterType="com.neuedu.entity.User_1">
<!--        这时候order改为before,因为UUID会先产生-->
        <selectKey keyColumn="id" keyProperty="id" order="BEFORE" resultType="string">
            select uuid()
        </selectKey>

        insert into user_1 (id,username,sex,birthday,address)
        values(#{id},#{username},#{sex},#{birthday},#{address})
    </insert>

UserMapperTest

    @Test
    public void testUUID(){
        try(SqlSession sqlSession = sqlSessionFactory.openSession()){
            User_1 user = new User_1();

            user.setUsername("张三");
            user.setSex("男");
//            重设时间-->需要格式化
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
            user.setBirthday(sdf.parse("2002-10-10"));
            user.setAddress("南京市");

            int insert = sqlSession.insert("com.neuedu.mapper.UserMapper.insertGetUUID", user);
//            因为把参数传到了User类中,所以可以直接用getId()函数获取
            System.out.println(user.getId());
//            提交事务
            sqlSession.commit();
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

2.删—>删除delete

UserMapper.xml

<!--    删除-->
<!--    原始数据类型,可以省略类型指定parameterType-->
<!--    <delete id="deleteById" parameterType="com.neuedu.entity.User">-->
    <delete id="deleteById">
        delete from user
        where id=#{id}
    </delete>

UserMapperTest

    @Test
    public void deleteUser(){
        try(SqlSession sqlSession = sqlSessionFactory.openSession()){
            int delete = sqlSession.delete("com.neuedu.mapper.UserMapper.deleteById", 6);
            sqlSession.commit();
        }
    }

3.改—>更新update

UserMapper.xml

<!--    更新-->
    <update id="updateUser" parameterType="com.neuedu.entity.User">
        update user set
        username=#{username},sex=#{sex},birthday=#{birthday},address=#{address}
        where id=#{id}
    </update>

UserMapperTest

    @Test
    public void updateUser(){
        try(SqlSession sqlSession = sqlSessionFactory.openSession()){
            User user = new User();
//            修改必须有id
            user.setId(7);

            user.setUsername("王酒");
            user.setSex("男");
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
            user.setBirthday(sdf.parse("2020-10-22"));
            user.setAddress("河北省");

            int update = sqlSession.update("com.neuedu.mapper.UserMapper.updateUser", user);

            sqlSession.commit();
        } catch (ParseException e) {
            throw new RuntimeException(e);
        }
    }

4.查—>查询select

上篇文章做了简单查询—>只能查询一条记录—>用到了selectOne函数

(1)查询所有记录

UserMapper.xml

    
<!--    查询全部数据-->
    <select id="findByAll" resultType="com.neuedu.entity.User">
        select * from user
    </select>

UserMapperTest

    @Test
    public void findByAll(){
        try(SqlSession sqlSession = sqlSessionFactory.openSession()){
            List<User> list = sqlSession.selectList("com.neuedu.mapper.UserMapper.findByAll");
            //以json格式输出查询结果
            System.out.println(JSON.toJSONString(list));
        }
    }

json--->[简单来说就是一种语言格式---(就类似于java,xml,js这些)]
	json(JavaScript Object Notation),即JavaScript对象标记法
	json是一种轻量级(Light-Meight),基于文本的(Text-Based),可读的(Human-Readable)格式

(2)Like #{}参数查询

UserMapper.xml

<!--    Like-#{}参数查询-->
    <select id="findByLikeParam" resultType="com.neuedu.entity.User">
        select * from user where address like #{value}
    </select>

UserMapperTest

        @Test
    public void findByLikeParam(){
            try(SqlSession sqlSession = sqlSessionFactory.openSession()){
                List<User> list = sqlSession.selectList(
                        "com.neuedu.mapper.UserMapper.findByLikeParam","%山%");
                System.out.println(JSON.toJSONString(list));
            }
        }

(3)Like ${}拼接查询

UserMapper.xml

<!--    Like-${}拼接查询-->
    <select id="findByLikeparam2" resultType="com.neuedu.entity.User">
        select * from user where address like ${value}
    </select>

UserMapperTest

        @Test
    public void findByLikeParam2(){
            try(SqlSession sqlSession = sqlSessionFactory.openSession()){
                List<User> list = sqlSession.selectList(
                        "com.neuedu.mapper.UserMapper.findByLikeparam2", "'%山%'");
                System.out.println(JSON.toJSONString(list));
            }
        }

(4)#{}和${}的区别

在使用mybatis时,我们会使用#{}和${}这两个符号来为sql语句传参数
二者有什么区别呢?

#{}是预编译处理,是占位符
防注入安全,也就是当使用参数查询时,会自动添加单引号
MyBatis在处理#{}的时候,会将sql中的#{}替换成 ?号 ,调用PreparedStatement来赋值

  eg.	select * from user where name=#{value};//假设我们注入的值为value=zhangsan
  		MyBatis处理#{}时,会将#{}替换为单引号:
  		select * from user where name=?
  		然后把value的值放进去,外面再加上单引号

${}是字符串替换,是拼接符
有sql注入风险.原生拼接,不会加单引号

  eg.select * from user where name=${value};//假设value=zhangsan
  	MyBatis在处理${}时,是直接把值拼接上去了:
  	select * from user where name=zhangsan

(5)${}的用法

a.动态排序

UserMapper.xml

<!--    ${}使用场景==>动态排序-->
    <select id="findByOrder" resultType="com.neuedu.entity.User">
        select * from user order by id ${value}
    </select>

UserMapperTest

        @Test
    public void findByOrder(){
            try(SqlSession sqlSession = sqlSessionFactory.openSession()){
                List<User> list = sqlSession.selectList(
                        "com.neuedu.mapper.UserMapper.findByOrder","desc");
                System.out.println(JSON.toJSONString(list));
            }
        }

b.动态表

UserMapper.xml

<!--    ${}动态表-->
    <select id="findByTable" resultType="com.neuedu.entity.User_1">
        select * from ${value}
    </select>

UserMapperTest

        @Test
    public void findByTable(){
            try(SqlSession sqlSession = sqlSessionFactory.openSession(true)){
                List<User_1> list = sqlSession.selectList(
                        "com.neuedu.mapper.UserMapper.findByTable", "user_1");
                System.out.println(JSON.toJSONString(list));
            }
        }

参考文档

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

查看全文