MyBatis进阶 - 《MySQL》

根据id查询用户
- 参数占位符：
{}：先使用?占位，执行SQL时将具体值赋值给?
- parameterType：
- SQL语句中特殊字符处理：
根据id删除用户
根据id修改用户
添加用户
- 通过属性useGeneratedKeys得到新增记录的主键值
多参数处理
- 多条件查询, 参数接收
动态SQL 多添加查询 if where标签
- 多条件查询：if标签
  - if标签的格式
  - 作用
- 多条件查询：where标签
  - 作用
动态SQL 修改部分字段 set标签
- set标签作用
动态SQL 批量删除用户 foreach标签
- foreach标签
  - foreach标签的属性
{变量名} 先使用?占位, 后面给?赋值
- 接口映射文件：resultMap输出映射

根据id查询用户

参数占位符：

{}：先使用?占位，执行SQL时将具体值赋值给?
${}：拼SQL，会存在SQL注入问题
parameterType：
用于设置参数类型，该参数可以省略
SQL语句中特殊字符处理：
转义字符
<![CDATA[ 内容 ]]> : CD提示

public class TestMybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test01() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectById(3);
        System.out.println(user);
        sqlSession.close();
    }
}
public interface UserMapper {
    User selectById(int uid);
}

<?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.gdkm.mapper.UserMapper">
    <!--根据id查询用户-->
    <select id="selectById" resultType="com.gdkm.pojo.User">
        <!--#{参数名} : 先使用?占位,把参数值赋值给?
            ${参数名} : 字符串拼接,可以有SQL注入 不建议使用-->
        SELECT * FROM USER WHERE id = ${uid};
    </select>
</mapper>

根据id删除用户

事务的处理

Java程序代码执行成功，但是数据库中并没有删除记录。
原因是没有提交事务，数据库的增、删、改要求提交事务。
方式一：手动提交事务
sqlSession.commit();
方式二：自动提交事务
sqlSession = factory.openSession(true);

public class TestMybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test02() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.deleteById(1);
        //注意:mybatis增删改需要提交事务
        //sqlSession.commit();   //手动提交事务
        sqlSession.close();
    }
}
public interface UserMapper {
    void deleteById(int uid);
}

<?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.gdkm.mapper.UserMapper">
    <!--根据id删除用户-->
    <delete id="deleteById">
        DELETE FROM USER WHERE id = #{uid};
    </delete>
</mapper>

根据id修改用户

public class TestMybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test03() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User(4, "蛤蟆吉", Date.valueOf("1249-07-02"), "男", "木叶村妙木山");
        int row = mapper.updateId(user);
        System.out.println("影响行数" + row);
        //注意:mybatis增删改需要提交事务
        //sqlSession.commit();   //手动提交事务
        sqlSession.close();
    }
}
public interface UserMapper {
    int updateId(User user);
}

<?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.gdkm.mapper.UserMapper">
    <!--根据id修改用户-->
    <update id="updateId">
        UPDATE USER SET username = #{username}, birthday= #{birthday}, sex = #{sex}, address = #{address} WHERE id = #{id};
    </update>
</mapper>

添加用户

通过属性useGeneratedKeys得到新增记录的主键值
useGeneratedKeys true，使用mysql生成的主键 keyProperty 实体类中对应的属性

public class TestMybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test04() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User(0, "盘丝大仙", Date.valueOf("1923-02-03"), "女", "水帘洞前址");
        int row = mapper.addId(user);
        System.out.println("影响行数" + row);
        //注意:mybatis增删改需要提交事务
        //sqlSession.commit();   //手动提交事务
        sqlSession.close();
    }
}
public interface UserMapper {
    int addId(User user);
}

<?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.gdkm.mapper.UserMapper">
    <!--添加用户-->
    <insert id="addId" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user VALUES (NULL, #{username}, #{birthday}, #{sex}, #{address});
    </insert>
</mapper>

多参数处理

多条件查询, 参数接收

散装参数: 如果方法中有多个参数，需要使用@Param(“SQL参数占位符名称”)
对象参数: 对象的属性名称要和参数占位符名称一致
Map集合参数: Map的键要和占位符名称一致

public class TestMybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test05() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        // 调用接口中的方法
        // MyBatis多参数方案1
        // List<User> userList = mapper.selectByCondition("%精%", "男");
        // MyBatis多参数方案2
        /*User user = new User();
        user.setUsername("%精%");
        user.setSex("男");
        List<User> userList = mapper.selectByCondition(user);*/
        // MyBatis多参数方案3: 传入Map
        Map<String, String> map = new HashMap<>();
        map.put("username", "%精%");
        map.put("sex", "男");
        List<User> userList = mapper.selectByCondition(map);
        userList.forEach(System.out::println);
        sqlSession.close();
    }
}
public interface UserMapper {
    //mybatis多参数处理,使用用户名及性别模糊查询
    //mybatis多参数方案1,每个参数添加@Param注解
    //List<User>selectByCondition(@Param("username") String username, @Param("sex") String sex);
    //mybatis多参数方案2,传入自定义类
    //List<User>selectByCondition(User user);
    //mybatis多参数方案3,传入map
    List<User>selectByCondition(Map<String,String> map);
}

<?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.gdkm.mapper.UserMapper">
    <!--模糊查询-->
    <select id="selectByCondition" resultType="com.gdkm.pojo.User">
        SELECT * FROM user WHERE username LIKE #{username} AND  sex = #{sex};
    </select>
</mapper>

动态SQL 多添加查询 if where标签

多条件查询：if标签

if标签的格式

SQL片段

作用

if标签的作用当条件为true就拼接SQL片段

多条件查询：where标签

作用

自动补全where这个关键字
去掉多余的and和or关键字

public class TestMybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test05() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         List<User> userList = mapper.selectByCondition(null, "男");
        userList.forEach(System.out::println);
        sqlSession.close();
    }
public interface UserMapper {
    //mybatis多参数处理,使用用户名及性别模糊查询
    //mybatis多参数方案1,每个参数添加@Param注解
    List<User>selectByCondition(
        @Param("username") String username, @Param("sex") String sex
    );
}

<?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.gdkm.mapper.UserMapper">
    <!--模糊查询-->
    <select id="selectByCondition" resultType="com.gdkm.pojo.User">
        SELECT * FROM user
        <where>
            <if test="username != null and username != ''">
                username LIKE #{username}
            </if>
            <if test="sex != null and sex != ''">
                AND sex = #{sex};
            </if>
        </where>
    </select>
</mapper>

动态SQL 修改部分字段 set标签

set标签作用

用在update语句中，相当于set关键字
去掉SQL代码片段中后面多余的逗号

public class TestMybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test03() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(4);
        user.setBirthday(Date.valueOf("1950-05-09"));
        int row = mapper.updateId(user);
        System.out.println("影响行数" + row);
        //注意:mybatis增删改需要提交事务
        //sqlSession.commit();   //手动提交事务
        sqlSession.close();
    }
public interface UserMapper {
    //根据id修改用户
    int updateId(User user);
}

<?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.gdkm.mapper.UserMapper">
    <!--根据id修改用户-->
    <update id="updateId">
        UPDATE USER
        <set>
            <if test="username != null and username != ''">
                username = #{username},
            </if>
            <if test="birthday != null">
                birthday= #{birthday},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="address != null and address != ''">
                address = #{address}
            </if>
        </set>
        WHERE id = #{id};
    </update>
</mapper>

动态SQL 批量删除用户 foreach标签

foreach标签

foreach标签的属性

collection 参数名
item 设置变量名，代表每个遍历的元素
separator 遍历一个元素添加的内容
{变量名} 先使用?占位, 后面给?赋值
open 在遍历前添加一次字符
close 在遍历后添加一次字符

public class TestMybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test06() {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int[] uid = new int[]{10, 11, 12};
        mapper.deleteByIds(uid);
        //注意:mybatis增删改需要提交事务
        sqlSession.commit();   //手动提交事务
        sqlSession.close();
    }
public interface UserMapper {
    //批量删除用户
    int deleteByIds(@Param("uid") int[] uid);
}

<?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.gdkm.mapper.UserMapper">
    <!--通过批量删除用户-->
    <delete id="deleteByIds">
        DELETE FROM user WHERE id IN
        <foreach collection="uid" item="id" open="(" separator="," close=");">
            #{id}
        </foreach>
    </delete>
</mapper>

接口映射文件：resultMap输出映射

概述

MyBatis可以把查询的结果自动封装为对象
但是有要求：查询结果的字段名称要与对象的成员变量名一致
当查询结果的字段名称和类的成员变量名不一致，名称不一致的成员变量没有数据

解决方式

SQL语句使用AS将名字改为与类变量一致
使用resultMap解决：resultMap可以建立查询的列与对象属性的对应关系
在核心配置文件中使用settings设置mapUnderscoreToCamelCase为true将映射下划线为驼峰命名法

public class Test02Mybatis {
    public static SqlSession sqlSession;
    @BeforeClass
    public static void demo() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);//自动提交事务
    }
    @Test
    public void test01() {
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        List<Order> orderList = mapper.findAllOrder();
        orderList.forEach(System.out::println);
        sqlSession.close();
    }
}
public interface OrderMapper {
    //查询所有商品信息
    List<Order>findAllOrder();
}

使用SQL语句AS重命名解决

<?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.gdkm.mapper.OrderMapper">
  <select id="findAllOrder" resultType="com.gdkm.pojo.Order">
    <!--order在SQL是关键字,需要使用票符号-->
    SELECT 
        o_id AS oId, 
        user_id AS userId, 
        number, 
        create_time AS createTime,
        note 
    FROM 
        `order`;
  </select>
</mapper>

在配置文件中使用settings设置mapUnderscoreToCamelCase解决

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration  
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <!--在控制台显示SQL语句-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--开启驼峰命名自动映射数据库的_命名-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

使用resultMap解决

<?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.gdkm.mapper.OrderMapper">
    <!--
      resultMap标签: 手动指定表中的字段名对应类中哪个成员变量
          id属性: 取个名字
          type属性: 查询的数据要封装的类型
          id子标签: 指定主键的对应关系
          result子标签: 指定普通字段的对应关系
              column属性: 表中的字段名
              property属性: 类中的成员变量名
    -->  
    <resultMap id="OrderMapper" type="Order">
        <id column="o_id" property="oId"/>(oId出现爆红没影响,MyBatisX插件的问题)
        <result column="user_id" property="userId"/>
        <result column="create_time" property="createTime"/>
    </resultMap>
  <!--
      resultType属性: 
          表中字段名和类中成员变量相同使用resultType自动处理查询结果封装到对象中
      resultMap属性: 
          表中字段名和类中成员变量不相同使用, resultMap手动处理查询结果封装到对象中
  -->
    <select id="findAllOrder" resultMap="OrderMapper">
        <!--order在SQL是关键字,需要使用票符号-->
        SELECT * FROM `order`;
    </select>
</mapper>