Add, delete, change and check CRUD

First, Mybatis implementation of CRUD steps

  1. Modify the implementation class interface
  2. Modify the XML configuration file of the implementation class
  3. Modify the Test file to Test

2. Specific CRUD examples

  1. Select: find the user whose ID is 1

Implementation class interface:

User getUserById(int id);
Copy the code

XML configuration file for the implementation class

<select id="getUserById" resultType="com.wu.pojo.User" parameterType="int">
        select *
        from mybatis.user
        where id = #{id};
</select>
Copy the code

The Test file

    @Test
    public void getUserById(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = mapper.getUserById(1);
        System.out.println(user);

        sqlSession.close();
    }
Copy the code
  1. Insert adds a user

Implementation class interface

int insertUser(User user);
Copy the code

XML configuration file for the implementation class

<insert id="insertUser" parameterType="com.wu.pojo.User">
        insert into mybatis.user (id, name, pwd)
        values (#{id}, #{name}, #{pwd});
    </insert>
Copy the code

The Test file

    @Test
    public void insertUser(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        User user = new User(4."Flower"."987654");
        mapper.insertUser(user);

        sqlSession.commit();

        sqlSession.close();
    }
Copy the code
  1. Delete Deletes the user whose ID is 4

Implementation class interface

int deleteUser(int id);
Copy the code

XML configuration file for the implementation class

<delete id="deleteUser" parameterType="int">
        delete
        from mybatis.user
        where id = #{id};
    </delete>
Copy the code

The Test file

    @Test
    public void deleteUser(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.deleteUser(4);

        sqlSession.commit();

        sqlSession.close();
    }
Copy the code
  1. Update user name =2; update user name =2

Implementation class interface

int updateUser(User user);
Copy the code

XML configuration file for the implementation class

<update id="updateUser" parameterType="com.wu.pojo.User">
        update mybatis.user
        set name = #{name},
            pwd  = #{pwd}
        where id = #{id};
    </update>
Copy the code

The Test file

    @Test
    public void updateUser(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.updateUser(new User(2."Small dragon"."123321"));

        sqlSession.commit();
        sqlSession.close();
    }
Copy the code

Iii. Use of Map

  1. Why use Map? If there are too many tables, fields or parameters in the database, the SQL statement written in the XML configuration file of the implementation class will be too long. Map can solve this problem well.
  2. For comparison purposes, I’ve overwritten the updateUser2 method with a Map, and the database implementation willThe user name whose ID =2 is changed to Blue
    1. Implementation class interface
    int updateUser2(Map<String,Object>map);
Copy the code
    1. XML configuration file for the implementation class
    <update id="updateUser2" parameterType="map">
    update mybatis.user
    set name=#{name}
    where id = #{id}
</update>
Copy the code
    1. The Test file
        @Test
public void updateUser2(a){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    Map map = new HashMap();
    map.put("id".2);
    map.put("name"."Little blue");

    mapper.updateUser2(map);

    sqlSession.commit();
    sqlSession.close();
}
Copy the code
  3. Map comparisons are versatile, especially when there are a lot of entity classes and database attributes.

Four, fuzzy query

How do I find all the information in the database for people with “small” in their names?

  1. Implementation class interface
List<User> selectUser2(String value);
Copy the code
  1. XML configuration file for the implementation class
    <select id="selectUser2" parameterType="String" resultType="com.wu.pojo.User" >
        select * from mybatis.user where name like #{value}
    </select>
Copy the code
  1. The Test file
    @Test
    public void selectUser2(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        List<User> users = mapper.selectUser2("%小%");

        for (User user : users) {
            System.out.println(user);
        }



        sqlSession.close();
    }
Copy the code

Matters needing attention:

Two methods:

  1. When Java code is executed, it is safe to pass the wildcard % % -> in common
List<User> users = mapper.selectUser2("%小%");
Copy the code
  1. SQL injection will occur when wildcard characters are used in SQL splicing, and database information is not secure and easy to leak
select * from mybatis.user where name like "#"#{value}"#"
Copy the code

Five, matters needing attention

Map pass parameters, directly in SQL fetch key can be! 【 parameter = “map”

Object pass parameters, directly in SQL to retrieve the object properties can be! 【 parameter = “Object”

If there is only one parameter of the basic type, it can be fetched directly from SQL without writing the parameter attribute

Multiple parameters with Map, or annotations!