1,映射文件
<?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="mapper接口类文件路径">
执行语句
</mapper>
2, 参数处理方式
1,单个基本类型的参数处理
parameterType的类型需要和Mapper接口的方法参数类型保持一致
mapper接口:
public interface BookMapper {
int delete(int id);
}
mapper配置文件:
<delete id="delete" parameterType="int">
delete from book_info where bookId=#{id}
</delete>
2, 多个基本类型的参数处理
映射文件中不用配置parameterType属性
1,使用arg0,arg1...argN或param1,param2...paramN来表示参数的顺序
mapper接口:
public interface BookMapper {
int save(String bookName,Double price,Integer storage);
}
mapper配置文件:
<insert id="save">
insert into book_info values(0,#{arg0},#{arg1},#{arg2})
</insert>
<insert id="save">
insert into book_info values(0,#{param1},#{param2},#{param3})
</insert>
2,使用@Param注解进行参数的绑定
mapper接口:
public interface BookMapper {
in tsave
(@Param("name")Stringbook Name,
@Param("price")Double price,
@Param("storage") Integer storage);}
mapper配置文件:
<insert id="save">
insert into book_info values(0,#{name},#{price},#{storage})
</insert>
3, 对象类型的参数
mapper接口:
public interface BookMapper {
int add(Book book);
}
mapper配置文件:
<insert id="add" parameterType="Book">
insert into book_info values(0,#{bookName},#{price},#{storage})
</insert>
4, 集合类型的参数
mapper接口:
public interface BookMapper {
List<Book> search(Map map);
}
mapper配置文件:
<select id="search" parameterType="map" resultType="Book">
SELECT * FROM book_info WHERE price BETWEEN #{beginPrice} AND #{endPrice}
</select>
3, Mybatis中查询结果类型
1,resultType
1,resultType返回基本类型(适用于“单列”查询)
mapper接口:
public interface BookMapper {
String select1(Integer id);
}
mapper配置文件:
<select id="select1" parameterType="int" resultType="String">
SELECT bookName FROM book_info WHERE bookId=#{id}
</select>
2, resultType返回实体类(适用于所有列“*”的查询)
mapper接口:
public interface BookMapper {
List<Book> findAll();
}
mapper配置文件:
<select id="findAll" resultType="Book">
select * from book_info
</select>
3, resultType返回Map(适用于“多列”查询)
mapper接口:
public interface BookMapper {
Map select2(Integer id);
List<Map> select3();
}
mapper配置文件:
<select id="select2" parameterType="int" resultType="map">
SELECT bookName,price FROM book_info WHERE bookId=#{id}
</select>
<select id="select3" resultType="map">
SELECT bookName,price FROM book_info
</select>
2, resultMap
使用场合:查询列名和实体类的属性名不对应,用于多表连接查询
mapper接口:
public interface BookMapper {
List<Book> select();
}
mapper配置文件:
<resultMap id="rm" type="Book">
<id column="id" property="bookId"></id> id的值映射到property
<result column="name" property="bookName"></result>
</resultMap>
<select id="select" resultMap="rm">
SELECT bookId AS id,bookName AS `name`,price,`storage` FROM book_info
</select>
4,Mybatis中的占位符
1,#占位符
#占位符底层采用的是PreparedStatement实现类,该方式可以防止SQL注入问题
对象类型参数:参数名必须和对象的属性名保持一致,#占位符会自动调用属性的getter方法。
2,$占位符
$占位符底层采用的是Statement实现类,通过拼接SQL的方式进行参数设置的,
语法:’${参数}’
$占位符主要的应用场合:模糊查询中拼接查询条件
mapper接口:
public interface BookMapper {
List<Book> select(String keywords);
mapper配置文件:
<select id="select" parameterType="string" resultType="Book">
select * from book_info where bookName like '%${keywords}%'
</select>
5, Mybatis的动态SQL
动态SQL的功能是通过mybatis提供的一些动态标签来实现的,包括:if、choose、set、where、foreach和sql片段等。
1, 使用where和if进行综合条件查询
mapper接口:
public interface StudentMapper {
List<Student> search(@Param("name") String name,
@Param("age1") String age1,@Param("age2") String age2);
}
mapper配置文件:
<select id="search" resultType="Student">
select * from student
<where>
<if test="name!=null and name!=''">
and stu_name like '%${name}%'
</if>
<if test="age1!=null and age1!=''">
and stu_age >= #{age1}
</if>
<if test="age2!=null and age2!=''">
and stu_age <= #{age2}
</if>
</where>
</select>
2, 使用set和if实现动态更新
mapper接口:
public interface StudentMapper {
int update(Student student);
}
mapper配置文件:
<update id="update" parameterType="Student">
update student
<set>
<if test="stuName!=null and stuName!=''">
stu_name=#{stuName},
</if>
<if test="stuAge!=null">
stu_age=#{stuAge},
</if>
<if test="stuBir!=null">
stu_bir=#{stuBir},
</if>
</set>
where stu_id=#{stuId}
</update>
主程序:
修改完直接将对象传给mapper
public static void main( String[] args )
{
SqlSession session = MybatisUtils.getSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
Student student=new Student();
student.setStuId(1);
student.setStuName("张三丰");
student.setStuAge(26);
mapper.update(student);
session.commit();
}
3, sql片段的使用
解决SQL大量重复的问题
<!--定义SQL片段-->
<sql id="aa">
select * from student
</sql>
<select id="findAll" resultType="Student">
<!--包含SQL片段-->
<include refid="aa"/>
</select>
4,使用foreach实现批量删除和批量添加
1,实现批量删除
mapper接口:
public interface StudentMapper {
int deleteAll(int[]ids);
}
mapper配置文件:
<delete id="deleteAll">
delete from student where stu_id in
<foreach collection="array" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</delete>
主程序:
public static void main( String[] args )
{
SqlSession session = MybatisUtils.getSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
int[]ids= {6,7};
mapper.deleteAll(ids);
session.commit();
}
2, 实现批量添加
mapper接口:
public interface StudentMapper {
int addAll(List<Student> students);
}
mapper配置文件:
<insert id="addAll">
insert into student values
<foreach collection="list" separator="," item="stu">
(#{stu.stuId},#{stu.stuName},#{stu.stuSex},#{stu.stuAge},now(),#{stu.stuShenfen},
#{stu.stuYuanxi})
</foreach>
</insert>
主程序 :
public static void main( String[] args ){
SqlSession session = MybatisUtils.getSession();
StudentMapper mapper = session.getMapper(StudentMapper.class);
List<Student> list=new ArrayList<>();
list.add(new Student("6","小六","男",23,"111111","数学系"));
list.add(new Student("7","小七","男",23,"111111","数学系"));
list.add(new Student("8","小八","男",23,"111111","数学系"));
mapper.addAll(list);
session.commit();
}
6, Mybatis多表关联查询
1,配置many-to-one关系
1,实体类:在many方添加one方实体为其属性
public class Emp {
private Integer empId;
private String empName;
private Double salary;
private Dept dept;
。。。
}
2,映射文件:association
<?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.bjpn.mapper.EmpMapper">
<resultMap id="rm" type="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
<result column="salary" property="salary"></result>
<!--配置manyToOne-->
<association property="dept" javaType="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
</association>
</resultMap>
<select id="findById" parameterType="int" resultMap="rm">
select * from emp_info e inner join dept_info d on e.dept_id=d.dept_id where e.emp_id=#{id} --------- 注意:一定要使用表连接查询
</select>
</mapper>
2,配置one-to-many关系
1,在One方的实体类中添加List<Many>集合属性
public class Dept {
private Integer deptId;
private String deptName;
private List<Emp> emps;
。。。
}
2,配置One的映射文件:Collection
<?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.bjpn.mapper.DeptMapper">
<resultMap id="rm" type="Dept">
<id column="dept_id" property="deptId"/>
<result column="dept_name" property="deptName"/>
<!--OneToMany配置-->
<collection property="emps" ofType="Emp" column="dept_id">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"/>
</collection>
</resultMap>
<select id="findById" parameterType="int" resultMap="rm">
select * from emp_info e inner join dept_info d on e.dept_id=d.dept_id where d.dept_id=#{id}
</select>
</mapper>
7,Mybatis的缓存机制
缓存区是采用map集合实现的,在执行DML操作后mybatis会自动清空缓存(防止脏读)
Key:根据sql语句(占位符、where条件以及namespace+id等组合而成)
Value:查询的数据
缓存分类:
一级缓存:mybatis默认的缓存方式,是mybatis不可分割的部分,属于session级别的缓存 (保证同一session)
二级缓存:属于sessionFactory级别的缓存,可以在不同的session之间实现数据的共享,但 二级缓存不是mybatis的默认缓存,需要通过配置插件的方式来实现。