一、insert
parameterType ，入参的全限定类名或类型别名
keyColumn ，设置数据表自动生成的主键名。对特定数据库（如PostgreSQL），若自动生成的主键不是第一个字段则必须设置
keyProperty ，默认值unset，用于设置getGeneratedKeys方法或selectKey子元素返回值将赋值到领域模型的哪个属性中
useGeneratedKeys ，取值范围true|false(默认值)，设置是否使用JDBC的getGenereatedKeys方法获取主键并赋值到keyProperty设置的领域模型属性中。MySQL和SQLServer执行auto-generated key field，因此当数据库设置好自增长主键后，可通过JDBC的getGeneratedKeys方法获取。但像Oralce等不支持auto-generated key field的数据库就不能用这种方法获取主键了
statementType ，取值范围STATEMENT,PREPARED（默认值）,CALLABLE
flushCache ，取值范围true(默认值)|false，设置执行该操作后是否会清空二级缓存和本地缓存
timeout ，默认为unset（依赖jdbc驱动器的设置），设置执行该操作的最大时限，超时将抛异常
databaseId ，取值范围oracle|mysql等，表示数据库厂家，元素内部可通过`<if test="_databaseId = 'oracle'">`来为特定数据库指定不同的sql语句
1.返回值为插入的记录数目
XML方式普通例子
<insert id="insert" parameterType="com.sid.model.User" >
insert into user (id, name, password,
mobile_phone)
values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
#{mobilePhone,jdbcType=VARCHAR})
</insert>注解方式
@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insert(@Param("name") String name, @Param("age") Integer age);
2.返回值为操作后获取记录主键
xml方式
<insert id="insert" parameterType="com.sid.model.User" useGeneratedKeys="true" keyProperty="id" >
insert into user (id, name, password,
mobile_phone)
values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},
#{mobilePhone,jdbcType=VARCHAR})
</insert>注解方式
@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{user.name}, #{user.age})")
@Options(useGeneratedKeys = true, keyProperty = "user.id")
int insert(@Param("user") User user);@Options注解常用属性:
- flushCache:刷新缓存策略，有DEFAULT,TRUE,FALSE三种值，默认DEFAULT表示刷新查询语句的缓存
- useCache：默认true，表示使用缓存
- fetchSize：查询时的获取数量
- useGeneratedKeys：默认false，是否返回插入的id
- keyProperty：实体类id属性
- keyColumn：实体类属性对应数据库的字段
3.批量插入
1.foreach方式
mapper.java
//ra对应roleAuthMapper.xml中的，修改的话要同时改
void insertBatch(List<RoleAuth> ra);mapper.xml
<insert id="insertBatch" parameterType="List">
INSERT INTO role_auth
(role_id, menu_id, creator,create_time)
VALUES
<foreach collection ="list" item="ra" separator ="," close=";">
(#{ra.roleId}, #{ra.menuId}, #{ra.creator}, #{ra.createTime})
</foreach >
</insert>2.ExecutorType.BATCH方式
java
注意，这个opensession默认是关闭自动提交的，所以最后需要sqlSession.commit
long start = System.currentTimeMillis();
User user;
SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH/*,false*/);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
for (int i = 0; i < 5; i++) {
user = new User();
user.setId(i*100L);
user.setName("name" + i);
user.setPassword("123");
user.setMobilePhone("123");
mapper.insert(user);
}
sqlSession.commit();
sqlSession.close();
long end = System.currentTimeMillis();
System.out.println("---------------" + (start - end) + "---------------");二、select
1.普通例子
XML方式
<resultMap id="BaseResultMap" type="com.sid.model.User" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="mobile_phone" property="mobilePhone" jdbcType="VARCHAR" />
</resultMap>
<sql id="Base_Column_List" >
id, name, password, mobile_phone
</sql>
<select id="selectAll" resultMap="BaseResultMap" >
select
<include refid="Base_Column_List" />
from user
</select>注解方式
@Select("select id,name,age,mobile_phone from user where id = #{id}")
int select(@Param("id") Long id);2.动态SQL
1.<if>
xml方式
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG WHERE state = 'ACTIVE'
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>注解方式（动态SQL的注解方式都一样，不一一介绍）
用script标签包围，然后像xml语法一样写
@Select("<script>"
+"SELECT * FROM BLOG WHERE state = 'ACTIVE'"
+" <if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>"
+"</script>")
Blog select(@Param("title ") Long title ,@Param("author ") Author author );2.<choose> <when> <otherwise>
类似于Java 中的 switch 语句。还是上面的例子，但是这次变为提供了“title”就按“title”查找，提供了“author”就按“author”查找的情形，若两者都没有提供，就返回所有符合条件的 BLOG
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>3.<where>
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。
<select id="findActiveBlogLike" resultType="Blog">
SELECT * FROM BLOG
<where>
<if test="state != null">
state = #{state}
</if>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</where>
</select>4.<foreach>
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>3.关联查询<association>
1.一对一关联
相当于
public class Classes{
private Long id ;
private String name;
private Teacher teacher;
省略setter getter
}
public class Teacher{
private Long id;
private String name;
省略setter getter
}
XML方式
嵌套结果和嵌套查询。
<association javaType=‘Teacher’>指的是POJO中属性的类型
<!-- 根据班级id查询班级和教师信息 -->
<select id="getClass" resultMap="ClassResultMap">
select *
from class as c
left join
teacher as t on c.teacher_id = t.t_id
where c.c_id
= #{id}
</select>
<!-- 嵌套结果 -->
<resultMap type="Classes" id="ClassResultMap">
<id property="id" column="c_id" />
<result property="name" column="c_name" />
<association property="teacher" javaType="Teacher">
<id property="id" column="t_id" />
<result property="name" column="t_name" />
</association>
</resultMap>
<select id="getClass2" parameterType="int"
resultMap="ClassResultMap2">
select * from class where c_id=#{id}
</select>
<!-- 嵌套查询 -->
<resultMap type="Classes" id="ClassResultMap2">
<id property="id" column="c_id" />
<result property="name" column="c_name" />
<association property="teacher" column="teacher_id"
select="getTeacher" />
</resultMap>
<select id="getTeacher" parameterType="int" resultType="Teacher">
SELECT
t_id id, t_name name from teacher where t_id=#{id}
</select>注解方式@Results @Result
one=@one 代表一对一
select="com.sid.mapper.TeacherMapper.selectById" 是指的调用TeacherMapper接口的selectById方法，入参是@Result(column="teacher_id",property="teacher"中的teacher_id
fetchType=FetchType.LAZY 获取当前pojo后延迟加载对应数据
fetchType=FetchType.EAGER 获取当前pojo后立即加载对应数据
延迟加载是针对嵌套查询而言的，是指在进行查询的时候先只查询最外层的SQL，对于内层SQL将在需要使用的时候才查询出来。Mybatis的延迟加载默认是关闭的，即默认是一次就将所有的嵌套SQL一并查了将对象所有的信息都查询出来。
@Mapper
public interface ClassesMapper {
@Results({
@Result(id=true,column="id",property="id"),
@Result(column="name",property="name"),
@Result(column="teacher_id",property="teacher",
one=@One(
select="com.sid.mapper.TeacherMapper.selectById"/*,fetchType= FetchType.EAGER*/
/*fetchType= FetchType.LAZY*/))
})
@Select("select * " +
"from classes c " +
"where c.id = #{id}")
public Classes selectAssociat(Long id);
}@Mapper
public interface TeacherMapper {
@Select("select * from teacher where id = #{id}")
public Teacher selectById(@Param("id")Long id);
}
2.一对多关联
相当于
public class Classes{
private Long id ;
private String name;
private Teacher teacher;
private List<Student> students;
省略setter getter
}
public class Teacher{
private Long id;
private String name;
省略setter getter
}
public class Student{
private Long id;
private String name;
省略setter getter
}XML实现
<collection ofType=‘Student’>值得是映射到list集合属性中POJO的类型
<select id="getClass3" parameterType="int"
resultMap="ClassResultMap3">
select *
from class c
left join teacher t on c.teacher_id =
t.t_id
left join student
s on c.c_id = s.class_id
where c.c_id = #{id}
</select>
<resultMap type="Classes" id="ClassResultMap3">
<id property="id" column="c_id" />
<result property="name" column="c_name" />
<association property="teacher" column="teacher_id"
javaType="Teacher">
<id property="id" column="t_id" />
<result property="name" column="t_name" />
</association>
<collection property="students" ofType="Student">
<id property="id" column="s_id" />
<result property="name" column="s_name" />
</collection>
</resultMap>注解实现
Many=@Many
select="com.sid.mapper.StudentMapper.selectByClassId" 是指的调用StudentMapper接口的selectByClassId方法，入参是@Result(column="id",property="students"中的id,即是查询完后的课程ID
@Results({
@Result(id=true,column="id",property="id"),
@Result(column="name",property="name"),
@Result(column="teacher_id",property="teacher",
one=@One(
select="com.sid.mapper.TeacherMapper.selectById"
)),
@Result(column="id",property="students",
many=@Many(
select="com.sid.mapper.StudentMapper.selectByClassId"
))
})
@Select("select * " +
"from classes c " +
"where c.id = #{id}")
public Classes selectOneToMany(Long id);@Repository
public interface StudentMapper {
@Select("select * from student where class_id = #{classId}")
public List<Student> selectByClassId(@Param("classId")Long classId);
}
三、parametertype 多个入参
1.单个入参
<select id="getUserByMobilePhone" resultMap="BaseResultMap" parameterType="java.lang.String" > select <include refid="Base_Column_List" /> from user where mobile_phone = #{mobilePhone,jdbcType=VARCHAR} </select>
2.多个入参
XML方式
在mapper.java中使用@Param
List<WhiteList> selectAll(@Param("name")String name, @Param("url") String url);mapper.xml中不用@parameterType
取值#{url}不用再指明jdbcType=VARCHAR
<select id="selectAll" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from white_list
<where>
<if test="name!=null">
name like #{name}
</if>
<if test="url!=null">
and url like #{url}
</if>
</where>
</select>注解map方式(XML也能用map入参,@parameterType=‘java.util.Map’这里不演示)
@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER})")
int insertByMap(Map<String, Object> map);注解model类方式（XML也能用model入参，@parameterType=‘com.sid.User’这里不演示）
@Insert("INSERT INTO USER(NAME, AGE) VALUES(#{name}, #{age})")
int insertByUser(User user);四、update
XML方式示例
<update id="updateByPrimaryKey" parameterType="com.sid.model.User" >
update user
set name = #{name,jdbcType=VARCHAR},
password = #{password,jdbcType=VARCHAR},
mobile_phone = #{mobilePhone,jdbcType=VARCHAR}
where id = #{id,jdbcType=BIGINT}
</update>注解方式
@Update("update user set userId=#{userId},password=#{password},comment=#{comment} where userName=#{userName}")
public int update ( User user );
五、delete
XML方式示例
<delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
delete from user
where mobile_phone = #{mobilePhone,jdbcType=VARCHAR}
</delete>注解方式
@Delete("delete from user where userName = #{userName}")
public int delete ( String userName );