【Java】Mybatis框架学习 vol2单表CRUD及注意事项

参考对应:

https://www.bilibili.com/video/av69742084?p=3
https://www.bilibili.com/video/av69742084?p=4

CRUD操作[create、read、update、delete]

---

从Vol1可知:
mybatis-config.xml的[映射器mappers]必须匹配UserMapper.xml
再由UserMapper.xml的[命名空间namespace]匹配到UserDao接口/UserMapper

---

查询Select：

id：-> dao/mapper接口的方法名称
resultType：-> 方法的结果类型
parameterType：-> 方法的参数类型 查询所有并不需要参数

	List<User> getAll();
	-----------------------------------------------------------------
	<select id="getAll" resultType="pojo.User">
	        select * from user ;
	</select>

根据id查询

	User getUserById(int id);
	-----------------------------------------------------------------
	<select id="getUserById" resultType="pojo.User" parameterType="int" >
	        select * from user where id = #{id};
	</select>

---

添加insert：

要注意的是，增、删、改、都不需要resultType结果类型(返回的是记录数)

int addUser(User user);
----------------------------------------
<insert id="addUser" resultType="int" parameterType="pojo.User" >
	       insert into user(id,name,password) values(#{id},#{name},#{password});
</insert>

测试时发现虽然操作记录为1是成功的，但是数据库更新没有发生变化

	@Test
    public void addUser(){
        sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.addUser(new User(6,"巨魔","123321"));
        System.out.println(i);
        sqlSession.close();
    }

这是因为我们没有执行事务提交

// 执行事务提交
sqlSession.commit();

同样，增、删、改都需要进行提交操作

---

修改Update:

int updateUser(User user);
--------------------------------------------------------------
<update id="updateUser" parameterType="pojo.User">
        update user set name = #{name},password = #{password} where id = #{id};
</update>

---

删除Delete:

id删除

int deleteUserById(int id);
--------------------------------------------------------------
<delete id="updateUser" parameterType="int">
        delete from user where id = #{id};
</delete>

name删除

int deleteUserById(String name);
--------------------------------------------------------------
<delete id="updateUser" parameterType="String">
        delete from user where name = #{name};
</delete>

---

注意事项

UserMapper.xml是不能存在中文字符，包括注释的字符在内:
否则初始化异常

sql标签要对应sql语句的CRUD来写
错误示范:

<insert id="getUserById" resultType="pojo.User" parameterType="int" >
	        select * from user where id = #{id};
</insert>

在mybatis-config.xml中mappers映射器的resource属性路径只能使用" / "
否则初始化异常
错误示范:

// <mapper resource="dao/UserMapper.xml"/>
<mappers>
        <mapper resource="dao.UserMapper.xml"/>
</mappers>

maven的导出资源问题，源码目录内的配置文件不会被maven导出
(在父/总项目的pom.xml添加此配置)

<!--在build中配置resources，来防止我们资源导出失败的问题-->
    <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
    </build>

---

模糊查询 & Map灵活类型入参

根据上面的Dao的CRUD操作可得知
基本都是围绕User对象作为入参手段(或者int id，String name这样)
实际上，我们可以使用Map键值对形式的来入参，只要sql获取对应的键值即可

通过map来入参:

参数类型替换为Map，类型的可选性更广了

只要 #{key} 等于 Map的 key即可

int addUserByMap(Map<String,Object> map);
----------------------------------------------------
<insert id="addUser" resultType="int" parameterType="map" >
	insert into user(id,name,password) values(#{id},#{name},#{password});
</insert>
---------------------------------------------------------
	@Test
    public void addUserByMap(){
        sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("id",7);
        map.put("name","皇子");
        map.put("password","332143");
        int i = mapper.addUserByMap(map);
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();
    }

Map -> key
Bean -> field
BasicType -> value
适用场景:传入的字段参数过多，使用Map会更清晰明了，错误更少

---

模糊查询:

List<User> getUserLike(String chars);
-----------------------------------------------------------
<select id="getUserLike" parameterType="String" resultType="pojo.User">
	select * from user where name like #{chars};
</select>
---------------------------------------------------------------
	@Test
    public void getUserLike(){
        sqlSession = MybatisUtil.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> users = mapper.getUserLike("李");
        for (User user:users) {
            System.out.println(user);
        }
        sqlSession.close();
    }

查询结果没有任何显示

原因是需要通配符以进行修饰

// @Test
List<User> users = mapper.getUserLike("%李%");

返回了结果

但是在传参阶段使用通配符容易出现sql注入的数据泄露危险

// 注入危险
	select * from user where id = ? [? -> 1 or 1 = 1 ]

所以应该使用死拼接法、在UserMapper.xml配置内写死以防止这样的问题

<select id="getUserLike" parameterType="String" resultType="pojo.User">
	select * from user where name like "%"#{chars}"%";
</select>

---