Mybatis simply add, delete, modify and check

The select statement

• Id: indicates the method name in the namespace.

• ResultType: return value of Sql statement execution!

• ParameterType: indicates the parameterType.

Special note: No Chinese comments can appear in myBatis configuration file!

1, in the programming interface, according to the ID query user:

public interface UserDao {
    List<User> getUserList(a);

    // Query the user by ID
    User getUserById(int id);

Copy the code

Select * from mapper where SQL = ‘mapper’;

<-- To query the user by ID, the parameter isintSo parameterType ="int"-->
<select id="getUserById" parameterType="int" resultType="com.kuang.pojo.User">
    select * from mybatis.user where id = #{id}
</select>

Copy the code

Add test method to test class, then test:

    / / check
    @Test
    public void getUserById(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        // Query the user whose ID is 1
        User userById = mapper.getUserById(1);
        System.out.println(userById);

        / / close the SqlSession
        sqlSession.close();
    }

Copy the code

Fuzzy query

When Java code executes, pass the wildcard character % %

List<User> userLike = mapper.getUserLike("% %" li);

Copy the code

As shown in figure:

### #2, use wildcards in SQL concatenation!

select * from mybatis.user where name like "%"#{value}"%"

Copy the code

As shown in figure:

The Insert statement

1. Write interfaces:

// Insert to add a user
int addUser(User user);

Copy the code

Select * from mapper where SQL = ‘mapper’;

<! Insert id= insert id= insert id= insert id= insert id="addUser" parameterType="com.kuang.pojo.User">
    insert into mybatis.user (id,name ,pwd) value (#{id},#{name},#{pwd});
</insert>

Copy the code

3, write test class:

    // Note: Adding/deleting/modifying requires a transaction to commit
    / / to add
    @Test
    public void addUser(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        // Insert user ID 4, user name guiguzi, password 123465
        int res = mapper.addUser(new User(4."Guiguzi"."123465"));
        if (res>0){
            System.out.println("Insert successful!");
        }
        // Commit the transaction
        sqlSession.commit();
        / / close the SqlSession
        sqlSession.close();

    }

Copy the code

Use the update

1. Write interfaces:

// Modify the user
int updateUser(User user);

Copy the code

Select * from mapper where SQL = ‘mapper’;

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

Copy the code

3, write test class:

    // Note: Adding/deleting/modifying requires a transaction to commit
    / / change
    @Test
    public void updateUser(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        Change the user name to gugu Xiansheng and password to 654321 for user whose ID is 1
        mapper.updateUser(new User(1."Ghost Valley fairy"."654321"));
        // Commit the transaction
        sqlSession.commit();
        / / close the SqlSession
        sqlSession.close();
    }

Copy the code

Use the Delete

1. Write interfaces:

// Delete a user
int deleteUser(int id);

Copy the code

Select * from mapper where SQL = ‘mapper’;

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

Copy the code

3, write test class:

    // Note: Adding/deleting/modifying requires a transaction to commit
    / / delete
    @Test
    public void deleteUser(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        // Delete user 4
        mapper.deleteUser(4);
        // Commit the transaction
        sqlSession.commit();
        System.out.println("Deletion succeeded!");
        / / close the SqlSession
        sqlSession.close();
    }

Copy the code

The use of the Map

If there are too many tables, fields, or parameters in the entity class or database, we can use Map!

1. Write the Map interface

// The universal Map
int addUser2(Map<String,Object> map);

Copy the code

Select * from mapper where SQL = ‘mapper’;

<! Properties in an object that can be fetched and passed directlymapThe key - > < insert id ="addUser2" parameterType="map">
    insert into mybatis.user (id, pwd) values (#{userid},#{passWord});
</insert>

Copy the code

3, write test class:

    / / universal Map
    / / to add
    @Test
    public void addUser2(a){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        Map<String,Object> map = new HashMap<String, Object>();

        map.put("userid".4);
        map.put("passWord"."123456");

        mapper.addUser2(map);

        sqlSession.commit();
        sqlSession.close();
    }

Copy the code

Note:

Map pass parameter, directly in SQL extract key can be! 【parameterType=”map”】 【parameterType=”Object”】 If there is only one basic type parameter, you can directly fetch it from SQL. Multiple parameters are mapped, or annotated!

Resolve the inconsistency between the attribute name and the field name

The name of the field in the database is different from that of the field in the class.

Solution 1: Alias the SQL statement

<select id="getUserById" resultType="com.kuang.pojo.User">
    select id,name,pwd as password from mybatis.user where id = #{id}
</select>

Copy the code

Solution 2: Use the resultMap result set mapping

<! <resultMap id="UserMap" type="User"> <! --column specifies a column in the database, a property in the property entity class --> <result column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="pwd" property="password"/>
</resultMap>

<select id="getUserById" resultMap="UserMap">
    select * from mybatis.user where id = #{id}
</select>

Copy the code

Classification: [SSM framework]