**
数据库连接池
**
数据库连接池是一个容器,负责分配管理数据库连接
允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个,避免了传统的数据库连接的频繁释放和开启
允许释放超过最大空闲时间的连接,避免饥饿
**
预编译
**
三条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>