Day 3 learning to punch in (Mybatis: CURD)

Posted by ciciep on Sat, 22 Jan 2022 11:03:03 +0100

3.CURD

namespace:

Mapper. The subcontracting name in namespace used in XML should be consistent with the package name in Dao/Mapper interface.

Process:

After completing the implementation of MyBatis, operate the database:

All operations are only related to "interface" and "configuration file"
Entity class, tool class, mybatis config. XML no longer needs to be changed
mapper.xml only needs to go to mybatis config. Configure once in XML, mapper Only one < mapper > is required in XML

dao layer:

UserMapper interface (renamed UserMapper from UserDao)

UserMapper configuration file (equivalent to entity class usermapperinpl)

Mapper.xml element concept

id: is the method name in the corresponding nsnamespace
Resulttype: return value of SQL statement execution
parameterType: parameter type

Steps:

(1) Write interface

In UserMapper

public interface UserMapper {
    
    //Query user by id
    User getUserById(int id);

    //insert user
    int insertUser(User user);

    //Modify user
    int updateUser(User user);

    //delete user
    int deleteUserById(int id);

}

(2) Write the corresponding mapper sql statements in XML

In usermapper In XML

<?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">


<!--namespace=Bind a corresponding Dao/Mapper Interface-->
<mapper namespace="com.kuang.dao.UserMapper">
    <!--according to id Query user-->
    <select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User">
        select * from `user` where `id`=#{id};
    </select>

    <!--Insert user-->
    <insert id="insertUser" parameterType="com.kuang.pojo.User">
        insert into `user`(`name`,`pwd`) values ('zhaoliu','zhao123456');
    </insert>

    <!--Modify user-->
    <update id="updateUser" parameterType="com.kuang.pojo.User">
        update `user` set `name`=#{name},`pwd`=#{pwd} where id=#{id};
    </update>

    <!--according to id delete user-->
    <delete id="deleteUserById" parameterType="int">
        delete from `user` where id=#{id};
    </delete>
</mapper>

(3) Testing

In UserMapperTest

public class UserMapperTest {

    //Find users by id
    @Test
    public void getUserById() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class); //Equivalent to obtaining the implementation class object
        User user = mapper.getUserById(1);

        System.out.println(user);

        sqlSession.close();

    }
    
    //Insert user
    @Test
    public void insertUser(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        int i = mapper.insertUser(new User(0, "liu", "liu123"));
        if(i>0){
            System.out.println("Insert successful");
        }

        //[key] adding, deleting, modifying and querying in Mybatis requires submitting a transaction to take effect
        sqlSession.commit();

        sqlSession.close();
    }

    //Modify user
    @Test
    public void updateUser(){
        SqlSession sqlSession=MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        int i = mapper.updateUser(new User(1, "liu", "liu123"));
        if(i>0){
            System.out.println("Modified successfully");
        }

        //[key] adding, deleting, modifying and querying in Mybatis requires submitting a transaction to take effect
        sqlSession.commit();

        sqlSession.close();
    }

    //delete user
    @Test
    public void deleteUserById(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        int i = mapper.deleteUserById(4);
        if(i>0){
            System.out.println("Delete succeeded");
        }

        //[key] adding, deleting, modifying and querying in Mybatis requires submitting a transaction to take effect
        sqlSession.commit();

        sqlSession.close();
    }
}

In mapper In XML, the attributes in the object can be taken out directly and correspond to the entity class

The addition, deletion and modification of Mybatis must commit a transaction to take effect

Note that the SqlSession object is closed after use

(4) Problems encountered

Mapper. Don't match the curd tag of XML incorrectly
Mapper.xml needs to be to mybatis config. In resource XML, the path should use '/', and the preceding namespace uses the package name '.'
```
<mappers>
    <mapper resource="com/kuang/dao/UserMapper.xml"/>
</mappers>
```
Program configuration file mybatis config XML must conform to the specification

NullPointerException, which is not registered to the resource, promotes the variable sqlSessionFactory defined inside and outside the scope in MybatisUtils

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;//Promote scope

    static {
        //The first step in using Mybatis: get the SqlSessionFactory object
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream =Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //With SqlSessionFactory, we can get an instance of SqlSession from it
    //SqlSession provides all the methods required to execute SQL commands in the database.
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

If there is garbled code in the output xml file, you can delete it
maven resource export problem, POM Add < build > to XML

(5) Universal Map

Mapper. If the parameter type in XML is an entity class, you need to initialize all fields and pass the entire entity class object during testing; If the sql operation only needs a few fields, it is not appropriate;
Field names in entity classes and parameters must be consistent.

In the interface method, the parameters are directly passed to the entity class User;

//insert user
int insertUser(User user);

When writing sql statements, you need to pass the parameter type, which is the entity class "com.kuang.pojo.User"

<!--Insert user-->
<!--Use entity classes: test Attribute in object passed in must correspond to field name in database table-->
<insert id="insertUser" parameterType="com.kuang.pojo.User">
    insert into `user`(`name`,`pwd`) values (#{name},#{pwd});
</insert>

When using the method, you need to create an entity class User object and assign all attributes

Use entity class: attribute in object passed in test must correspond to field name in database table

//Insert user
@Test
public void insertUser(){
    SqlSession sqlSession=MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    int i = mapper.insertUser(new User(0, "Tianqi 1", "tian123456"));
    if(i>0){
        System.out.println("Insert successful");
    }

    //[key] adding, deleting, modifying and querying in Mybatis requires submitting a transaction to take effect
    sqlSession.commit();

    sqlSession.close();
}

Consider using map as a parameter. The key value in the map can also be customized. You only need to define the corresponding key value in the test class.

In the interface method, parameters are directly transferred to Map;

//insert user
int insertUser(User user);

When writing sql statements, you need to pass the parameter type, which is Map

<!--Insert user-->
<!--Use universal Map: test Passed in“ map of key Value can be customized without corresponding to field name in database table-->
<insert id="insertUser2" parameterType="map">
    insert into `user`(`name`,`pwd`) values (#{username},#{password});
</insert>

When using the method, you need to create an entity class User object and assign all attributes

The "key value of map" passed in the universal map: test can be customized without corresponding to the "field name in database table"

//Insert user
@Test
public void insertUser2(){
    SqlSession sqlSession=MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    Map<String,Object> map=new HashMap<>();
    map.put("username","pseudo-ginseng");
    map.put("password","tian123456");
    int i = mapper.insertUser2(map);
    if(i>0){
        System.out.println("Insert successful");
    }

    //[key] adding, deleting, modifying and querying in Mybatis requires submitting a transaction to take effect
    sqlSession.commit();

    sqlSession.close();
}

Pass parameter type:

Object transfer parameters: directly get the attribute of the object in sql (when defining an entity class, the attribute corresponds to the field name of the database table one by one) [parameterType = "object"]
Map transfer parameters: just take the key from the sql directly. The key value of the map does not need to be consistent with the field name of the database table, [parameterType = "map"]
There is only one transfer parameter type, and it is a basic type, such as int, which can be omitted without writing, and directly obtained in sql.

map or annotation for multiple parameters

Return result type:
When it is an entity class object, it needs to be written. When it is int, it can be omitted.

(6) Fuzzy query

1. The string passed in test is spliced, and the wildcard% is passed

It is easy to cause sql injection. Generally, the value passed in by the user can only be a value, not a string

 List<User> userList = mapper.getUserLike("%Lee%");

2. In mapper XML in sql. Wildcards are recommended

select * from `user` where name like "%"#{name}"%"

Topics: Mybatis

Programmer Think