Mybatis-03-CRUD

强承望
2023-12-01

3.1、namespace

namespace中的包名要和Dao/Mapper接口的包名一致

3.2、select

根据id查询用户

  • id:namespace中的方法名

  • resultType:sql语句执行的返回值类型

  • parameterType:参数类型

  1. UserMapper中添加对应方法

    public interface UserMapper {
        //全查询
        List<user> GetList();
        //按名称查询
        List<user> GetList2(String value);
        //按id查询
        user GetUserById(int id);
    }
  2. UserMapper.xml中添加select语句

    <?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.mosang.dao.UserMapper">
    ​    //配置SQL语句,id必须与设置的方法名一致
        <select id="GetList" resultType="user">
            select * from mybatis.user;
        </select>
    ​
        <select id="GetList2" resultType="com.mosang.pojo.user">
            select * from user where name like #{values}
        </select>
    ​
        <select id="GetUserById" resultType="com.mosang.pojo.user" parameterType="int">
            select * from mybatis.user where id=#{id}
        </select>
    </mapper>
  3. 测试

    @Test
        public void test()
        {
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<user> users=mapper.GetList();
            for (user user:users)
            {
                System.out.println(user);
            }
            sqlSession.close();
    ​
        }
    ​
        @Test
        public void test2()
        {
            SqlSession sqlSession = MybatisUtils.getSqlSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            List<user> users=mapper.GetList2("%李%");
            for(user user:users)
            {
                System.out.println(user);
            }
            sqlSession.close();
    ​
        }
        @Test
        public void test3(){
            SqlSession sqlSession=MybatisUtils.getSqlSession();
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            user user = mapper.GetUserById(3);
            System.out.println(user.toString());
        }

模糊查询

第1种:在Java代码中添加sql通配符。

string wildcardname = “%smi%”;
list<name> names = mapper.selectlike(wildcardname);
​
<select id=”selectlike”>
select * from foo where bar like #{value}
</select>

第2种:在sql语句中拼接通配符,会引起sql注入

string wildcardname = “smi”;
list<name> names = mapper.selectlike(wildcardname);
​
<select id=”selectlike”>
    select * from foo where bar like "%"#{value}"%"
</select>

3.3、insert

方法一、pojo实体类注入

1、Mapper添加方法

public interface UserMapper {
    int InsertValue(user user);
}

2、xml配置

<insert id="InsertValue" parameterType="com.mosang.pojo.user">
    insert user values (#{id},#{name},#{password})
</insert>

3、测试

@Test
public void test4(){
    SqlSession sqlSession=MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    user user=new user(8,"默默","132456");
    mapper.InsertValue(user);
    /*Map<String,String> map=new HashMap<>();
    map.put("id","7");
    map.put("name","呼呼");
    map.put("password","1111111");
    int i=mapper.InsertValue(map);
    System.out.println(i);*/
    sqlSession.commit();
    sqlSession.close();
}

方法二、万能Map

1、定义以Map对象为参数的插入方法

public interface UserMapper {
    int InsertValue(Map<String,String> map);
}

2、在xml中配置SQL语句

<insert id="InsertValue" parameterType="map">
    insert user values (#{id},#{name},#{password})
</insert>

3、测试

@Test
public void test4(){
    SqlSession sqlSession=MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    Map<String,String> map=new HashMap<>();
    map.put("id","7");
    map.put("name","呼呼");
    map.put("password","1111111");
    int i=mapper.InsertValue(map);
    System.out.println(i);
    sqlSession.commit();//提交事务,重点!不写的话不会提交到数据库
    sqlSession.close();
}

3.4、update

1、Mapper类

public interface UserMapper {
    int UpdateUser(user user);
    int UpdateUser1(Map<String,String> map);
}

2、xml配置

<update id="UpdateUser" parameterType="com.mosang.pojo.user">
    update user set name =#{name},password=#{password} where id=#{id}
</update>
​
<update id="UpdateUser1" parameterType="map">
    update user set name =#{name},password=#{password} where id=#{id}
</update>

3、测试

@Test
public void test5(){
    SqlSession sqlSession=MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    /*user user=mapper.GetUserById(3);
    user.setPassword("abcdefg");
    System.out.println(mapper.UpdateUser(user));*/
    Map<String, String> map=new HashMap<>();
    map.put("id","3");
    map.put("password","666666");
    map.put("name","李四");
    mapper.UpdateUser1(map);
    sqlSession.commit();
    sqlSession.close();
}

3.5、delete

1、Mapper

public interface UserMapper {
    int DeleteUser(int id);
}

2、xml配置

<delete id="DeleteUser" parameterType="int">
    delete from user where id=#{id}
</delete>

3、测试

 @Test
public void test6(){
    SqlSession sqlSession=MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    mapper.DeleteUser(3);
    sqlSession.commit();
    sqlSession.close();
}

注意:

  • 所有的增、删、改操作需要提交事务!

    sqlSession.commit();

  • 为了规范操作,在SQL的配置文件中,我们尽量将Parameter参数和resultType都写上!

  • 有时候根据业务的需求,可以考虑使用map传递参数!

  • 接口所有的普通参数,尽量都写上@Param参数,尤其是多个参数时,必须写上!

 类似资料: