当前位置：首页 > news >正文

MyBatis3（动态SQL 常用的动态SQL 元素映射器注解基本注解结果映射注解）

news 2024/11/1 20:15:46

动态SQL

什么是MyBatis的动态SQL？

**定义：**根据不同的条件拼接SQL语句，实现对数据库更准确的操作；

**实现：**映射器配置文件或者注解

常用的动态SQL元素

if 元素：判断语句，单条件分支判断.
choose 元素（when，otherwise）：多条件分支判断，等同于java 的 switch.
trim （where，set）：辅助元素，用于处理一些 SQL 拼接的问题.
foreach 元素：循环语句，在in 语句等列举条件常用
bind 元素：自定义上下文变量，传递参数.

if元素

语法：

语法
< if  test =”条件”> 满足条件的语句 </ if>

注意：拼接SQL语句的时候要注意AND符号

实现：

Student.java 下面所需要的bean同这个一样

package bean;import java.io.Serializable;
import java.util.Date;public class Student implements Serializable{private int sid;private String sname;private Date birthday;private String Ssex;private int classid;private Clazz clazz;public int getSid() {return sid;}public void setSid(int sid) {this.sid = sid;}public String getSname() {return sname;}public void setSname(String sname) {this.sname = sname;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getSsex() {return Ssex;}public void setSsex(String ssex) {Ssex = ssex;}public int getClassid() {return classid;}public void setClassid(int classid) {this.classid = classid;}public Clazz getClazz() {return clazz;}public void setClazz(Clazz clazz) {this.clazz = clazz;}public Student(int sid, String sname, Date birthday, String ssex, int classid, Clazz clazz) {super();this.sid = sid;this.sname = sname;this.birthday = birthday;Ssex = ssex;this.classid = classid;this.clazz = clazz;}public Student() {super();}@Overridepublic String toString() {return "Student [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", Ssex=" + Ssex + ", classid="+ classid + ", clazz=" + clazz + "]";}
}

StudentMapper.java

public List<Student> selectIf(Student s);

StudentMapper.xml

<!-- OGNL 对象图导航语言 属性 运算符 逻辑 字符串增强 == --><!-- test中的表达式成立 就把if标签里面的字串拼接 -->
<select id="selectIf" parameterType="student"resultType="student"><include refid="stusql"></include>where 1=1<if test="ssex != null">and ssex=#{ssex}</if><if test="classid != 0">and classid=#{classid}</if>
</select>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student s = new Student();
//s.setSname("测试");
//s.setBirthday(new Date());
//s.setSsex("女");
s.setClassid(1);
List<Student> list = studentMapper.selectIf(s);//s.setSid(1);
list.forEach(System.out::println);
DaoUtil.closeSqlSession(sqlSession);

choose 、when 、otherwise 元素

为什么用choose 元素

场景1 当新闻编号不为空，则只用新闻编号作为查询条件；
场景2 当新闻编号为空，而新闻标题不为空，则用新闻标题作为条件进行模糊查询
场景3 当新闻编号和新闻标题都为空，则要求新闻作者不能为空

语法：

<choose>  
<when test=“条件”>满足条件的语句</ when><otherwise> 满足其他条件的语句<otherwise>
</choose>

choose类似于switch 只要满足条件只走一个

注意：拼接SQL语句的时候要注意AND和逗号

实现：

StudentMapper.java

public List<Student> selectChoose(Student s);

StudentMapper.xml

<select id="selectChoose" parameterType="student" resultType="student">select * from student<where><choose><when test="sname">and sname=#{sname}</when><when test="birthday">and birthday=#{birthday}</when><when test="Ssex">and Ssex=#{ssex}</when><when test="classid">and classid=#{classid}</when><otherwise>classid=2</otherwise></choose></where>
</select>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student s = new Student();
s.setClassid(1);
List<Student> list = studentMapper.selectChoose(s);
list.forEach(System.out::println);
DaoUtil.closeSqlSession(sqlSession);

trim、where、set元素

trim（不常用）

语法：

语法：
<trim   prefix = “”suffixOverrides = “” prefixOverrides=“”suffix=“”></trim>prefix 需要拼接的子句，可以是where，or 或者set；suffixOvrrides 忽略通过管道分隔的文本序列后缀。一般不与prefixOvrrides 同时使用prefixOvrrides 忽略通过管道分隔的文本序列前缀。一般不与suffixOvrrides 同时使用

实现：

StudentMapper.java

public List<Student> findStudentTrim(Student s);

StudentMapper.xml

<!-- trim 万能标签prefix 开始添加一个什么prefixOverrides 开始去掉一个什么suffix 结束添加一个什么suffixOverrides 结束去掉一个什么--><select id="findStudentTrim" parameterType="student" resultType="student">select * from student <trim prefix=" where " prefixOverrides="and" ><if test="ssex != null"> and ssex= #{ssex}</if><if test="classid != 0"> and classid = #{classid}</if></trim></select>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);Student s = new Student();
//		s.setSname("测试");
//		s.setBirthday(new Date());
//		s.setSsex("女");s.setClassid(1);
List<Student> list = studentMapper.findStudentTrim(s);
list.forEach(System.out::println);
DaoUtil.closeSqlSession(sqlSession);

StudentMapper.java

public int updateTrim(Student s);

StudentMapper.xml

<update id="updateTrim" parameterType="student">update student<trim prefix="set" prefixOverrides="" suffix="" suffixOverrides=","><if test="sname!=null">sname=#{sname},</if><if test="birthday!=null">birthday=#{birthday},</if><if test="Ssex!=null">Ssex=#{ssex},</if><if test="classid!=0">classid=#{classid},</if></trim>where sid=#{sid}
</update>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student s = new Student();
s.setSname("测试");
s.setBirthday(new Date());
s.setSsex("女");
s.setClassid(1);s.setSid(38);
int count = studentMapper.updateTrim(s);
if (count > 0) {sqlSession.commit();System.out.println("更新成功~");
} else {sqlSession.rollback();System.out.println("更新失败~");
}
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

where

语法：

语法：
<where><if  test =”条件”> 满足条件的语句 </if>
</where>

说明：where 元素只会在至少有一个子元素的条件返回SQL子句的情况下才去插入 “WHERE”子句。而且，若语句的开头为 “AND”或“OR”，where 元素也会将它们去除。

实现：

StudentMapper.java

public List<Student> selectWhere(Student s);

StundetMapper.xml

<!-- where标签 1.添加一个where关键词 2. 去掉where后的第一个and 3.当没where标签中没有任何成立的字串时 什么也不添加 -->
<select id="selectWhere" parameterType="student"resultType="student">select * from student<where><if test="ssex != null">and ssex=#{ssex}</if><if test="classid != 0">and classid=#{classid}</if></where>
</select>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student s = new Student();
//s.setSname("测试");
//s.setBirthday(new Date());
//s.setSsex("女");
s.setClassid(1);
s.setSid(38);List<Student> list = studentMapper.selectWhere(s);
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

set

语法：

<set><if  test =”条件”> 满足条件的语句 </if>
</set>

说明： set 标签元素主要是用在更新操作的时候，它的主要功能和 where 标签元素其实是差不多的，主要是在包含的语句前输出一个 set，然后如果包含的语句是以逗号结束的话将会把该逗号忽略，如果 set 包含的内容为空的话则会出错。有了 set 元素就可以动态的更新那些修改了的字段。

实现：

StudentMapper.java

public int updateSet(Student s);

StudentMapper.xml

<update id="updateSet" parameterType="student">update student<set><if test="sname!=null">sname=#{sname},</if><if test="birthday!=null">birthday=#{birthday},</if><if test="Ssex!=null">Ssex=#{ssex},</if><if test="classid!=0">classid=#{classid},</if></set>where sid=#{sid}
</update>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student s = new Student();
s.setSname("测试");
s.setBirthday(new Date());
s.setSsex("女");
s.setClassid(1);
s.setSid(2);int count = studentMapper.updateSet(s);
if (count > 0) {sqlSession.commit();System.out.println("更新成功~");
} else {sqlSession.rollback();System.out.println("更新失败~");
}
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

foreach元素

语法：

<foreach   item = “” index=“” collection=“” open=“” separator=“” close=“”></foreach>item 循环中的当前元素；
index 当前循环元素的位置下标；
collection 方法传递的参数，一个数组或者集合；
close 以什么符号结束将这些集合元素包装起来；
open 以什么符号开始将这些集合元素包装起来；
separator 各个元素的间隔符号。

参数是数组

实现：

StudentMapper.java

public List<Student> selectForEach(int[] array);

StudentMapper.xml

<select id="selectForEach" resultType="student"><!-- select * from student where sid in --><include refid="stusql"></include>where sid in<!-- collection 参数名 item 要遍历的元素别名 open 起始字符 separator 分隔符 close 结束字符 --><foreach collection="array" item="item" open="(" separator=","close=")">#{item}</foreach>
</select>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
int[] array = { 1, 3, 5, 7, 9 };
List<Student> list = studentMapper.selectForEach(array);
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

参数是ArrayList

StudentMapper.java

public List<Student> selectForEachList(List<Integer> list);

StudentMapper.xml

<select id="selectForEachList" resultType="student">select * from student where sid in<foreach collection="list" item="item" open="(" separator="," close=")">#{item}</foreach>
</select>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Integer> listInteger = Arrays.asList(1, 3, 5, 7, 9);
List<Student> list = studentMapper.selectForEachList(listInteger);
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

批量增加

实现：

StudentMapper.java

public int insertlist(List<Student> list);

StudentMapper.xml

<!-- 批量添加 -->
<insert id="insertlist">insert into student(sname,birthday,ssex,classid) values<foreach collection="list" item="s" separator=",">(#{s.sname},#{s.birthday},#{s.ssex},#{s.classid})</foreach>
</insert>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student s1 = new Student();
s1.setBirthday(new Date());
s1.setClassid(1);
s1.setSname("刘备");
s1.setSsex("男");
Student s2 = new Student();
s2.setBirthday(new Date());
s2.setClassid(2);
s2.setSname("关羽");
s2.setSsex("男");
Student s3 = new Student();
s3.setBirthday(new Date());
s3.setClassid(3);
s3.setSname("张飞");
s3.setSsex("男");List<Student> stulist = new ArrayList<>();
stulist.add(s1);
stulist.add(s2);
stulist.add(s3);
int ret = studentMapper.insertlist(stulist);if (ret == stulist.size()) {sqlSession.commit();
} else {sqlSession.rollback();
}// 释放资源
DaoUtil.closeSqlSession(sqlSession);

bind元素

定义一个变量

语法：

<bind   name=“”value=“_parameter”>
</bind>name 自定义变量的变量名
value 自定义变量的变量值
_parameter 传递进来的参数

实现：

StudentMapper.java

// 模糊查询
public List<Student> selectStudentLike(String keyname);

StudentMapper.xml

<select id="selectStudentLike" parameterType="String"resultType="student"><!-- 方式1 在业务层处理  不推荐--><!-- select * from student where sname like #{v} --><!-- 方式2 mysql的函数进行拼接 --><!-- select * from student where sname like concat('%',#{v},'%') --><!-- 方式3 sql语法 --><!-- select * from student where sname like "%"#{v}"%" --><!-- 方式4 ${v} 不推荐,不能防止sql注入--><!-- select * from student where sname like '%${v}%' --><!-- 方式5 bind标签  推荐 --><bind name="v" value="'%'+_parameter+'%'"/>select * from student where sname like #{v}
</select>

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
// 方式1:业务层进行拼接
List<Student> list = studentMapper.selectStudentLike("%三%");
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
// 方式2，3，4，5
List<Student> list = studentMapper.selectStudentLike("三");
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

#{}和${}区别

#{}匹配的是一个占位符，相当于JDBC中的一个?，会对一些敏感的字符进行过滤，编译过后会对传递的值加上双引号，因此可以防止SQL注入问题。
${}匹配的是真实传递的值，传递过后，会与sql语句进行字符串拼接。$ {}会与其他sql进行字符串拼接，不能预防sql注入问题。

#{}是预编译处理，$ {}是字符串替换。

mybatis在处理#{}时，会将sql中的#{}替换为?号，调用PreparedStatement的set方法来赋值；

mybatis在处理 $ { } 时，就是把 ${ } 替换成变量的值。使用 #{} 可以有效的防止SQL注入，提高系统安全性。

示例完整代码：

StudentMapper.java

package mapper;import java.util.List;
import java.util.Map;import bean.Student;public interface StudentMapper {public List<Student> selectIf(Student s);public List<Student> selectWhere(Student s);public List<Student> selectChoose(Student s);public List<Student> findStudentTrim(Student s);public int updateSet(Student s);public int updateTrim(Student s);public List<Student> selectForEach(int[] array);public List<Student> selectForEachList(List<Integer> list);public int insertlist(List<Student> list);// 模糊查询public List<Student> selectStudentLike(String keyname);
}

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 指向接口的类路径 -->
<mapper namespace="mapper.StudentMapper"><!-- sql片段 --><sql id="stusql">select * from student</sql><select id="selectIf" parameterType="student"resultType="student"><include refid="stusql"></include>where 1=1<if test="ssex != null">and ssex=#{ssex}</if><if test="classid != 0">and classid=#{classid}</if></select><select id="selectWhere" parameterType="student"resultType="student">select * from student<where><if test="ssex != null">and ssex=#{ssex}</if><if test="classid != 0">and classid=#{classid}</if></where></select><!-- trim 万能标签prefix 开始添加一个什么prefixOverrides 开始去掉一个什么suffix 结束添加一个什么suffixOverrides 结束去掉一个什么--><select id="findStudentTrim" parameterType="student" resultType="student">select * from student <trim prefix=" where " prefixOverrides="and" ><if test="ssex != null"> and ssex= #{ssex}</if><if test="classid != 0"> and classid = #{classid}</if></trim></select><select id="selectChoose" parameterType="student"resultType="student">select * from student<where><choose><when test="sname">and sname=#{sname}</when><when test="birthday">and birthday=#{birthday}</when><when test="Ssex">and Ssex=#{ssex}</when><when test="classid">and classid=#{classid}</when><otherwise>classid=2</otherwise></choose></where></select><update id="updateSet" parameterType="student">update student<set><if test="sname!=null">sname=#{sname},</if><if test="birthday!=null">birthday=#{birthday},</if><if test="Ssex!=null">Ssex=#{ssex},</if><if test="classid!=0">classid=#{classid},</if></set>where sid=#{sid}</update><update id="updateTrim" parameterType="student">update student<trim prefix="set" prefixOverrides="" suffix="" suffixOverrides=","><if test="sname!=null">sname=#{sname},</if><if test="birthday!=null">birthday=#{birthday},</if><if test="Ssex!=null">Ssex=#{ssex},</if><if test="classid!=0">classid=#{classid},</if></trim>where sid=#{sid}</update><select id="selectForEach" resultType="student"><!-- select * from student where sid in --><include refid="stusql"></include>where sid in<!-- collection 参数名 item 要遍历的元素别名 open 起始字符 separator 分隔符 close 结束字符 --><foreach collection="array" item="item" open="(" separator=","close=")">#{item}</foreach></select><select id="selectForEachList" resultType="student">select * from student where sid in<foreach collection="list" item="item" open="(" separator="," close=")">#{item}</foreach></select><!-- 批量添加 --><insert id="insertlist">insert into student(sname,birthday,ssex,classid) values<foreach collection="list" item="s" separator=",">(#{s.sname},#{s.birthday},#{s.ssex},#{s.classid})</foreach></insert><select id="selectStudentLike" parameterType="String"resultType="student"><!-- 方式1 在业务层处理  不推荐--><!-- select * from student where sname like #{v} --><!-- 方式2 mysql的函数进行拼接 --><!-- select * from student where sname like concat('%',#{v},'%') --><!-- 方式3 sql语法 --><!-- select * from student where sname like "%"#{v}"%" --><!-- 方式4 ${v} 不推荐,不能防止sql注入--><!-- select * from student where sname like '%${v}%' --><!-- 方式5 bind标签  推荐 --><bind name="v" value="'%'+_parameter+'%'"/>select * from student where sname like #{v}</select>
</mapper>

测试类：

package test;import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;import org.apache.ibatis.session.SqlSession;import bean.Student;
import dao.DaoUtil;
import mapper.StudentMapper;public class Test {public static void main(String[] args) {SqlSession sqlSession = DaoUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
//		Student s = new Student();
//		s.setSname("测试");
//		s.setBirthday(new Date());
//		s.setSsex("女");
//		s.setClassid(1);
//
//		s.setSid(2);//		List<Student> list = studentMapper.selectIf(s);// 动态SQL
//		List<Student> list = studentMapper.selectWhere(s);
//		List<Student> list = studentMapper.selectChoose(s);
//		List<Student> list = studentMapper.findStudentTrim(s);//		int[] array = { 1, 3, 5, 7, 9 };
//		List<Student> list = studentMapper.selectForEach(array);
//		List<Integer> listInteger = Arrays.asList(1, 3, 5, 7, 9);
//		List<Student> list = studentMapper.selectForEachList(listInteger);// 方式1:业务层进行拼接
//		List<Student> list = studentMapper.selectStudentLike("%三%");// 方式2，3，4，5List<Student> list = studentMapper.selectStudentLike("三");list.forEach(System.out::println);//		int count = studentMapper.updateSet(s);
//		int count = studentMapper.updateTrim(s);
//		if (count > 0) {
//			sqlSession.commit();
//			System.out.println("更新成功~");
//		} else {
//			sqlSession.rollback();
//			System.out.println("更新失败~");
//		}//		Student s1 = new Student();
//		s1.setBirthday(new Date());
//		s1.setClassid(1);
//		s1.setSname("刘备");
//		s1.setSsex("男");
//		Student s2 = new Student();
//		s2.setBirthday(new Date());
//		s2.setClassid(2);
//		s2.setSname("关羽");
//		s2.setSsex("男");
//		Student s3 = new Student();
//		s3.setBirthday(new Date());
//		s3.setClassid(3);
//		s3.setSname("张飞");
//		s3.setSsex("男");
//		
//		List<Student> stulist = new ArrayList<>();
//		stulist.add(s1);
//		stulist.add(s2);
//		stulist.add(s3);
//		int ret = studentMapper.insertlist(stulist);
//
//		if (ret == stulist.size()) {
//			sqlSession.commit();
//		} else {
//			sqlSession.rollback();
//		}
//
//		// 释放资源DaoUtil.closeSqlSession(sqlSession);}
}

映射器注解

映射器配置文件的缺陷

繁琐：配置文件的书写本身繁琐，需要掌握的内容比较多
不直观：配置文件和接口直接只是名称相同，对应起来比较麻烦.

常用的注解

基本注解：实现简单的增删改查操作。
结果映射注解：实现结果的映射关系，也可以完成级联映射。
动态SQL注解：实现动态 SQL 的内容

基本注解

基本注解的分类

增加操作 @Insert 类似 < insert > 完成新增
删除操作 @Delete 类似 < delete > 完成删除
修改操作 @Update 类似 < update > 完成修改
查询操作 @Select 类似 < select > 完成查询

@Insert注解

**功能：**完成新增操作，类似配置文件的元素；

**说明：**新增时所用的参数取值是接口方法的入参，可以是对象，也可以是 Map 集合。

语法：

@Insert （“ sql 语句”）

主键回填

**功能：**完成数据库自增主键的回填

语法：

@Options(useGeneratedKeys = true, keyProperty = "主键属性")

实现：

实体类Student.java

package bean;import java.util.Date;public class Student {private int sid;private String sname;private Date birthday;private String Ssex;private int classid;private Clazz clazz;public int getSid() {return sid;}public void setSid(int sid) {this.sid = sid;}public String getSname() {return sname;}public void setSname(String sname) {this.sname = sname;}public Date getBirthday() {return birthday;}public void setBirthday(Date birthday) {this.birthday = birthday;}public String getSsex() {return Ssex;}public void setSsex(String ssex) {Ssex = ssex;}public int getClassid() {return classid;}public void setClassid(int classid) {this.classid = classid;}public Clazz getClazz() {return clazz;}public void setClazz(Clazz clazz) {this.clazz = clazz;}public Student(int sid, String sname, Date birthday, String ssex, int classid, Clazz clazz) {super();this.sid = sid;this.sname = sname;this.birthday = birthday;Ssex = ssex;this.classid = classid;this.clazz = clazz;}public Student() {super();}@Overridepublic String toString() {return "Student [sid=" + sid + ", sname=" + sname + ", birthday=" + birthday + ", Ssex=" + Ssex + ", classid="+ classid + ", clazz=" + clazz + "]";}
}

StudentMapper.java

@Insert("insert into student(sname,birthday,ssex,classid) " + "values(#{sname},#{birthday},#{ssex},#{classid})")
@Options(useGeneratedKeys = true, keyProperty = "sid")
// 主键回填注解@Options
public int addStudent(Student s);

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student s = new Student();
s.setSname("一猫人");
s.setBirthday(new Date());
s.setSsex("男");
s.setClassid(1);//s.setSid(18);
System.out.println(s);
int count = studentMapper.addStudent(s);
System.out.println(s);
if (count > 0) {sqlSession.commit();
} else {sqlSession.rollback();
}
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

主键自增

**功能：**完成自定义主键的自增

语法：

@SelectKey ( statement = "自增规则", keyProperty = "主键属性", 
resultType = 结果类型, before = true )

@Delete删除

**功能：**完成删除操作，类似配置文件的元素；

说明：删除时所用的参数取值是接口方法的入参，可以是对象，也可以是 Map 集合。

语法：

@Delete （“ sql 语句”）

实现：

StudentMapper.java

@Delete("delete from student where sid=#{v}")
public int deleteStudent(int sid);

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
int count = studentMapper.deleteStudent(42);if (count > 0) {sqlSession.commit();
} else {sqlSession.rollback();
}
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

@Update更新

**功能：**完成更新操作，类似配置文件的元素；

**说明：**更新时所用的参数取值是接口方法的入参，可以是对象，也可以是Map 集合。

语法：

@Update （“ sql 语句”）

实现：

StudentMapper.java

@Update("update student set sname=#{sname},birthday=#{birthday},ssex=#{ssex},classid=#{classid} where sid=#{sid}")
public int updateStudent(Student s);

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
// 修改
Student s = new Student();
s.setSname("一猫人");
s.setBirthday(new Date());
s.setSsex("男");
s.setClassid(1);s.setSid(41);
int count = studentMapper.updateStudent(s);
if (count > 0) {sqlSession.commit();
} else {sqlSession.rollback();
}
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

@Select查询

**功能：**完成查询操作，类似配置文件的元素；

**说明：**查询时所用的参数取值是接口方法的入参，可以是对象，也可以是 Map 集合。

语法：

@Selete （“ sql 语句”）

实现：

StudentMapper.java

@Select("select * from student")
public List<Student> findAll();@Select("select * from student where sname=#{v}")
public Student findByName(String name);

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> list = studentMapper.findAll();
for (Student student : list) {System.out.println(student);
}
Student s = studentMapper.findByName("贾策");
System.out.println(s);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

注解和sqlMapper.xml 可以同时使用

注解底层还是sqlMapper 方法还是不能重载

传递多个参数的方式

方法1：Map 方式跟sqlmap一样
方法2：JavaBean 方式跟sqlmap一样
方法3：@Param 方式

实现：

StudentMapper.java

@Select("select * from student where ssex=#{sex} limit #{cpage},#{size}")
public List<Student> selectSexLimit(@Param("sex") String sex,@Param("cpage") int cpage, @Param("size") int size);

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> list = studentMapper.selectSexLimit("男", (3 - 1) * 3, 3);
for (Student student : list) {System.out.println(student);
}
// 释放资源
DaoUtil.closeSqlSession(sqlSession);

结果映射注解

@Results结果映射

功能： 完成数据库字段和 JavaBean 属性的映射关系；

说明：每个 @Results 可以包含多个 @Result，其中通过 id 属性来判断是否为主键。

语法：

@Results({ @Result(id = 是否为主键, column = "字段", property = "属性" ) })

@Results复用

实现：

SMasterMapper.java

//	@Results(id="smaster_map",value = {
//			@Result(column = "smid",property = "smid"),
//			@Result(column = "sm_name",property = "smname"),
//			@Result(column = "smsex",property = "smsex")
//	})@Results({@Result(column = "smid",property = "smid"),@Result(column = "sm_name",property = "smname"),@Result(column = "smsex",property = "smsex")})@Select("select * from schoolmaster")public List<SMaster> findAll();@Select("select * from schoolmaster where smid=#{v}")
//	@ResultMap("smaster_map")public SMaster findById(int id);

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
SMasterMapper sMasterMapper = sqlSession.getMapper(SMasterMapper.class);
List<SMaster> list = sMasterMapper.findAll();
list.forEach(System.out::println);
SMaster s = sMasterMapper.findById(2);
System.out.println(s);

注解映射各用各的

一对一映射

功能：一对一的关系映射；

说明：FetchType.lazy 是延时加载，FetchType.EAGER 是即时加载。

语法：

@One( Select = 一对一查询方法, 
fetchType = FetchType.EAGER )

实现：

StudentMapper.java

@Results({ @Result(column = "classid", property = "classid"),@Result(property = "clazz", column = "classid", one = @One(select = "mapper.ClazzMapper.selectAll")) })
@Select("select * from student")
public List<Student> selectStudentAndClazz();

Clazz.java

@Select("select * from class where classid=#{v}")
public List<Clazz> selectAll(int classid);

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> list = studentMapper.selectStudentAndClazz();
for (Student student : list) {System.out.println(student);
}
DaoUtil.closeSqlSession(sqlSession);

注解没有表联查只有单表和自己写的映射关系

一对一映射的实现案例

一对多映射

功能：一对多的关系映射；

说明：FetchType.lazy 是延时加载，FetchType.EAGER 是即时加载。

语法：

@Many( Select = 一对多查询方法, fetchType = FetchType.EAGER )

实现：

ClazzMapper.java

@Results({ @Result(column = "classid", property = "classid"),@Result(column = "classid", property = "stulist", many = @Many(select = "mapper.StudentMapper.selectStudentByClassId")) })@Select("select * from class")public List<Clazz> selectClazzAndStudent();

StudentMapper.java

@Select("select * from student where classid=#{v}")
public List<Student> selectStudentByClassId(int classid);

测试：

SqlSession sqlSession = DaoUtil.getSqlSession();
ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);
List<Clazz> list = clazzMapper.selectClazzAndStudent();
for (Clazz clazz : list) {System.out.println(clazz);
}
DaoUtil.closeSqlSession(sqlSession);

一对多映射的实现案例

示例完整代码：

ClazzMapper.java

package mapper;import java.util.List;import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;import bean.Clazz;public interface ClazzMapper {@Select("select * from class where classid=#{v}")public List<Clazz> selectAll(int classid);@Select("select * from class")public List<Clazz> selectClazzId(int classid);@Results({ @Result(column = "classid", property = "classid"),@Result(column = "classid", property = "stulist", many = @Many(select = "mapper.StudentMapper.selectStudentByClassId")) })@Select("select * from class")public List<Clazz> selectClazzAndStudent();
}

StudentMapper.java

package mapper;import java.util.List;
import java.util.Map;import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.UpdateProvider;
import org.apache.ibatis.jdbc.SQL;import bean.Student;public interface StudentMapper {@Select("select * from student")public List<Student> findAll();@Select("select * from student where sname=#{v}")public Student findByName(String name);@Select("select * from student where ssex=#{sex} limit #{cpage},#{size}")public List<Student> selectSexLimit(@Param("sex") String sex, @Param("cpage") int cpage, @Param("size") int size);@Insert("insert into student(sname,birthday,ssex,classid) " + "values(#{sname},#{birthday},#{ssex},#{classid})")@Options(useGeneratedKeys = true, keyProperty = "sid")// 主键回填注解@Optionspublic int addStudent(Student s);@Update("update student set sname=#{sname},birthday=#{birthday},ssex=#{ssex},classid=#{classid} where sid=#{sid}")public int updateStudent(Student s);@Delete("delete from student where sid=#{v}")public int deleteStudent(int sid);@Results({ @Result(column = "classid", property = "classid"),@Result(property = "clazz", column = "classid", one = @One(select = "mapper.ClazzMapper.selectAll")) })@Select("select * from student")public List<Student> selectStudentAndClazz();@Select("select * from student where classid=#{v}")public List<Student> selectStudentByClassId(int classid);
}

测试1：

package test;import java.util.Date;
import java.util.List;import org.apache.ibatis.session.SqlSession;import bean.Student;
import dao.DaoUtil;
import mapper.StudentMapper;public class Test01 {public static void main(String[] args) {SqlSession sqlSession = DaoUtil.getSqlSession();StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
//		List<Student> list = studentMapper.findAll();List<Student> list = studentMapper.selectSexLimit("男", (3 - 1) * 3, 3);for (Student student : list) {System.out.println(student);}
//
//		Student s = studentMapper.findByName("贾策");
//		System.out.println(s);// 添加
//		Student s = new Student();
//		s.setSname("一猫人");
//		s.setBirthday(new Date());
//		s.setSsex("男");
//		s.setClassid(1);
//
//		s.setSid(41);
//		System.out.println(s);
//		int count = studentMapper.addStudent(s);
//		System.out.println(s);//		int count = studentMapper.updateStudent(s);
//		int count = studentMapper.deleteStudent(42);//		if (count > 0) {
//			sqlSession.commit();
//		} else {
//			sqlSession.rollback();
//		}// 释放资源DaoUtil.closeSqlSession(sqlSession);}
}

测试2：

package test;import java.util.List;import org.apache.ibatis.session.SqlSession;import bean.SMaster;
import dao.DaoUtil;
import mapper.SMasterMapper;public class Test02 {public static void main(String[] args) {SqlSession sqlSession = DaoUtil.getSqlSession();SMasterMapper sMasterMapper = sqlSession.getMapper(SMasterMapper.class);List<SMaster> list = sMasterMapper.findAll();list.forEach(System.out::println);SMaster s = sMasterMapper.findById(2);System.out.println(s);}
}

测试3：

package test;import java.util.List;import org.apache.ibatis.session.SqlSession;import bean.Clazz;
import bean.Student;
import dao.DaoUtil;
import mapper.ClazzMapper;
import mapper.StudentMapper;public class Test03 {public static void main(String[] args) {SqlSession sqlSession = DaoUtil.getSqlSession();
//		StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
//		List<Student> list = studentMapper.selectStudentAndClazz();
//		for (Student student : list) {
//			System.out.println(student);
//		}ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);List<Clazz> list = clazzMapper.selectClazzAndStudent();for (Clazz clazz : list) {System.out.println(clazz);}DaoUtil.closeSqlSession(sqlSession);}
}

查看全文

http://www.mrgr.cn/news/63490.html