11-19 Mybatis02

1.Mybatis自动提交事务的设置

//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.创建构建者对象
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
//3.创建 SqlSession 工厂对象
factory = builder.build(in);
//4.创建 SqlSession 对象
//这里openSession里面传入的参数是设置了自动提交事务，Mybatis默认是关闭了自动提交事务的
session = factory.openSession(true);
//5.创建 Dao 的代理对象
userDao = session.getMapper(IUserDao.class);

2. Mybatis 的动态 SQL 语句

2.1动态 SQL 之标签

2.1.1 if & where 标签

//根据用户信息查询
List<User> findByUser(User user);
//UserMapper.xml映射配置
<resultMap id="userMap" type="pojo.User">
    <id column="id" property="id"/>
    <result column="create_date" property="createDate"/>
    <result column="create_date" property="createDate"/>
</resultMap>
<select id="findByUser" resultMap="userMap" parameterType="user">
    select * from userinfo where 1=1  //这里是为了拼接sql语句
    <if test="username !=null and username != ''">
        and username like #{username}
    </if>
    <if test="name !=null and name != ''"> //这个if条件进不去，因为name判断为空
        and name like #{name}
    </if>
</select>
 //测试
@Test
public void findByUser() {
        User user = new User();
        user.setUsername("%a%");//这里没有设置name，所以第二个if判断条件进不去
        List<User> byUser = mapper.findByUser(user);
        sqlSession.commit();
        for (User u:
             byUser) {
            System.out.println(u);
        }
    }
//上面的xml中的select可以修改为
<select id="findByUser" resultMap="userMap" parameterType="user">
        select * from userinfo
        <where> //这里在执行第一个if语句时会省略and
            <if test="username !=null and username != ''">
                and username like #{username}
            </if>
            <if test="name !=null and name != ''">
                and name like #{name}
            </if>
        </where>
 </select>
 //这里的select编译过后会变成Preparing: select * from userinfo WHERE username like ? and name like ? 

2.1.2 foreach 标签

//删除多个用户信息
int deleteByIds(String... ids);
<delete id="deleteByIds" parameterType="List">//这里的parameterType参数不能设置为Array/array/Arrays
        delete from userinfo
        <where>
            <foreach collection="array" item="id" open="id in(" close=")
            " separator=",">
                #{id}
            </foreach>
            //这里如果遍历的是数组，collection的属性值设为array
            //如果遍历的是List，属性值设为list
            //如果为Map或者对象，属性值为对应的key
        </where>
</delete>
 //测试
@Test
    public void deleteByIds() {
        int i = mapper.deleteByIds("11", "12");
        sqlSession.commit();
        System.out.println(i);
    }
//这里的sql语句编译过后的结果为Preparing: delete from userinfo WHERE id in( ? , ? ) 
//Parameters: 11(String), 12(String)

2.1.3 trim标签

trim标签一般用于去除sql中的多余的关键字，以及对sql语句进行拼接等