mybatis

**

数据库连接池

**
数据库连接池是一个容器，负责分配管理数据库连接
允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个，避免了传统的数据库连接的频繁释放和开启
允许释放超过最大空闲时间的连接，避免饥饿

**

预编译

**

三条sql语句预编译一次将id的值用‘？’替代并放入缓存。预编译可以提高性能，防止sql注入。

@Delete("delete from emp where id =#{id}")                  delete from emp where id=?
    public void delete(Integer id);

执行sql时会将#{}替换为？，生成预编译sql

@Delete("delete from emp where id =${id}")                  delete from emp where id=1
    public void delete(Integer id);

拼接sql，直接将参数拼接在sql语句中。

**

mybatis增删改查注解

**

@Mapper
public interface EmpMapper {

    @Insert("insert into emp(username, name"
            +" gender, image, job, entrydate, dept_id, create_time, update_time)" +
            " values(#{username},#{name},#{gender},#{image}),#{job},#{entrtdate}" +
            "#{deptId},#{createTime},#{updateTime}")
    public void insert(Emp emp);
    @Delete("delete from emp where id =#{id}")
    public void delete(Integer id);

    @Update("update emp set username=#{username},name=#{name}"+
            "gender=#{gender},image=#{image},job=#{job}"+
            "entrydate=#{entrydate},dept_id=#{deptId},update_time=#{updateTime} where id=#{id}")
    public void update(Emp emp);



    //在application.properties中开启mybatis的驼峰命名自动映射开关，否则名字不一样不会自动映射到对象中
    @Select("select * from emp where id=#{id}")
    public Emp getByID(Integer id);

    
    
    //条件查询
    @Select("select * from emp where name like concat('%',#{name},'%') and gender=#{gender} " +
            "and entrydate between #{begin} and #{end}")
    public List<Emp> getByConditions(String name, Short gender, LocalDate begin,LocalDate end);
}

mybatis增删改查用XML文件

**

动态sql-if

**

<?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.mybatis01.mapper.EmpMapper">
    <update id="update">
        update emp
        <set>**<!--用了set标签可以不要sql后面的逗号-->**
            <if test="username!=null">username=#{username}</if>
            <if test="name!=null">name=#{name}</if>
            <if test="gender!=null">gender=#{gender}</if>
            <if test="image!=null">image=#{image}</if>
            <if test="job!=null">job=#{job}</if>
            <if test="entrydate!=null">entrydate=#{entrydate}</if>
            <if test="deptId!=null">dept_id=#{deptId}</if>
            <if test="updateTime!=null">update_time=#{updateTime}</if>
        </set>
        where id = #{id}
    </update>

</mapper>

**

<?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.tlias.mapper.EmpMapper">

    <select id="getPageByCondition" resultType="com.tlias.pojo.Emp">
        select *
        from emp
        <where>
            <if test="name!=null">name like concat('%',#{name},'%')</if>
            <if test="gender!=null">and gender=#{gender}</if>
            <if test="begin!=null and end!=null">and entrydate between #{begin} and #{end}</if>
            limit #{index},#{pageSize}
        </where>
    </select>
</mapper>

动态sql-foreach

**

EmpMapper.xml
//collection 遍历集合    item遍历元素    separator 分隔符   open遍历前添加的sql  close遍历后添加的sql
<delete id="deleteByIds">
		delete from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
 </delete>