网站建设以及网页设计需要会什么,重庆外贸网站建设公司,wordpress新建界面,北京注册公司需要多少钱动态SQL
什么是MyBatis的动态SQL#xff1f;
**定义#xff1a;**根据不同的条件拼接SQL语句#xff0c;实现对数据库更准确的操作#xff1b;
**实现#xff1a;**映射器配置文件或者注解
常用的动态SQL元素 if 元素#xff1a;判断语句#xff0c;单条件分 支判断…动态SQL
什么是MyBatis的动态SQL
**定义**根据不同的条件拼接SQL语句实现对数据库更准确的操作
**实现**映射器配置文件或者注解
常用的动态SQL元素 if 元素判断语句单条件分 支判断.choose 元素 whenotherwise 多条件分支判断等同于java 的 switch.trim whereset 辅助元素用于处理一些 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 ListStudent selectIf(Student s);StudentMapper.xml
!-- OGNL 对象图导航语言 属性 运算符 逻辑 字符串增强 --!-- test中的表达式成立 就把if标签里面的字串拼接 --
select idselectIf parameterTypestudentresultTypestudentinclude refidstusql/includewhere 11if testssex ! nulland ssex#{ssex}/ifif testclassid ! 0and 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);
ListStudent 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“条件”满足条件的语句/ whenotherwise 满足其他条件的语句otherwise
/choosechoose类似于switch 只要满足条件只走一个
注意拼接SQL语句的时候要注意AND和逗号 实现
StudentMapper.java
public ListStudent selectChoose(Student s);StudentMapper.xml
select idselectChoose parameterTypestudent resultTypestudentselect * from studentwherechoosewhen testsnameand sname#{sname}/whenwhen testbirthdayand birthday#{birthday}/whenwhen testSsexand Ssex#{ssex}/whenwhen testclassidand classid#{classid}/whenotherwiseclassid2/otherwise/choose/where
/select测试
SqlSession sqlSession DaoUtil.getSqlSession();
StudentMapper studentMapper sqlSession.getMapper(StudentMapper.class);
Student s new Student();
s.setClassid(1);
ListStudent list studentMapper.selectChoose(s);
list.forEach(System.out::println);
DaoUtil.closeSqlSession(sqlSession);trim、where、set元素
trim不常用 语法
语法
trim prefix “”suffixOverrides “” prefixOverrides“”suffix“”/trimprefix 需要拼接的子句可以是whereor 或者setsuffixOvrrides 忽略通过管道分隔的文本序列后缀。一般不与prefixOvrrides 同时使用prefixOvrrides 忽略通过管道分隔的文本序列前缀。一般不与suffixOvrrides 同时使用实现
StudentMapper.java
public ListStudent findStudentTrim(Student s);StudentMapper.xml
!-- trim 万能标签prefix 开始添加一个什么prefixOverrides 开始去掉一个什么suffix 结束添加一个什么suffixOverrides 结束去掉一个什么--select idfindStudentTrim parameterTypestudent resultTypestudentselect * from student trim prefix where prefixOverridesand if testssex ! null and ssex #{ssex}/ifif testclassid ! 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);
ListStudent list studentMapper.findStudentTrim(s);
list.forEach(System.out::println);
DaoUtil.closeSqlSession(sqlSession);StudentMapper.java
public int updateTrim(Student s);StudentMapper.xml
update idupdateTrim parameterTypestudentupdate studenttrim prefixset prefixOverrides suffix suffixOverrides,if testsname!nullsname#{sname},/ifif testbirthday!nullbirthday#{birthday},/ifif testSsex!nullSsex#{ssex},/ifif testclassid!0classid#{classid},/if/trimwhere 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
语法
语法
whereif test ”条件” 满足条件的语句 /if
/where说明where 元素只会在至少有一个子元素的 条件返回SQL子句的情况下才去插入 “WHERE”子句。而且若语句的开头为 “AND”或“OR”where 元素也会将它们去除。 实现
StudentMapper.java
public ListStudent selectWhere(Student s);StundetMapper.xml
!-- where标签 1.添加一个where关键词 2. 去掉where后的第一个and 3.当没where标签中没有任何成立的字串时 什么也不添加 --
select idselectWhere parameterTypestudentresultTypestudentselect * from studentwhereif testssex ! nulland ssex#{ssex}/ifif testclassid ! 0and 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);ListStudent list studentMapper.selectWhere(s);
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);set
语法
setif test ”条件” 满足条件的语句 /if
/set说明 set 标签元素主要是用在更新操作的时候 它的主要功能和 where 标签元素其实是差不多的主要是在包含的语句前输出一个 set 然后如果包含的语句是以逗号结束的话将会把该逗号忽略如果 set 包含的内容为空的 话则会出错。有了 set 元素就可以动态的更 新那些修改了的字段。 实现
StudentMapper.java
public int updateSet(Student s);StudentMapper.xml
update idupdateSet parameterTypestudentupdate studentsetif testsname!nullsname#{sname},/ifif testbirthday!nullbirthday#{birthday},/ifif testSsex!nullSsex#{ssex},/ifif testclassid!0classid#{classid},/if/setwhere 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“”/foreachitem 循环中的当前元素
index 当前循环元素的位置下标
collection 方法传递的参数一个数组或者集合
close 以什么符号结束将这些集合元素包装起来
open 以什么符号开始将这些集合元素包装起来
separator 各个元素的间隔符号。参数是数组
实现
StudentMapper.java
public ListStudent selectForEach(int[] array);StudentMapper.xml
select idselectForEach resultTypestudent!-- select * from student where sid in --include refidstusql/includewhere sid in!-- collection 参数名 item 要遍历的元素别名 open 起始字符 separator 分隔符 close 结束字符 --foreach collectionarray itemitem open( separator,close)#{item}/foreach
/select测试
SqlSession sqlSession DaoUtil.getSqlSession();
StudentMapper studentMapper sqlSession.getMapper(StudentMapper.class);
int[] array { 1, 3, 5, 7, 9 };
ListStudent list studentMapper.selectForEach(array);
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);参数是ArrayList
StudentMapper.java
public ListStudent selectForEachList(ListInteger list);StudentMapper.xml
select idselectForEachList resultTypestudentselect * from student where sid inforeach collectionlist itemitem open( separator, close)#{item}/foreach
/select测试
SqlSession sqlSession DaoUtil.getSqlSession();
StudentMapper studentMapper sqlSession.getMapper(StudentMapper.class);
ListInteger listInteger Arrays.asList(1, 3, 5, 7, 9);
ListStudent list studentMapper.selectForEachList(listInteger);
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);批量增加
实现
StudentMapper.java
public int insertlist(ListStudent list);StudentMapper.xml
!-- 批量添加 --
insert idinsertlistinsert into student(sname,birthday,ssex,classid) valuesforeach collectionlist items 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(男);ListStudent 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”
/bindname 自定义变量的变量名
value 自定义变量的变量值
_parameter 传递进来的参数实现
StudentMapper.java
// 模糊查询
public ListStudent selectStudentLike(String keyname);StudentMapper.xml
select idselectStudentLike parameterTypeStringresultTypestudent!-- 方式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 namev value%_parameter%/select * from student where sname like #{v}
/select测试
SqlSession sqlSession DaoUtil.getSqlSession();
StudentMapper studentMapper sqlSession.getMapper(StudentMapper.class);
// 方式1:业务层进行拼接
ListStudent list studentMapper.selectStudentLike(%三%);
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);SqlSession sqlSession DaoUtil.getSqlSession();
StudentMapper studentMapper sqlSession.getMapper(StudentMapper.class);
// 方式2345
ListStudent list studentMapper.selectStudentLike(三);
list.forEach(System.out::println);
// 释放资源
DaoUtil.closeSqlSession(sqlSession);#{}和${}区别
#{}匹配的是一个占位符相当于JDBC中的一个?会对一些敏感的字符进行过滤编译过后会对传递的值加上双引号因此可以防止SQL注入问题。 匹配的是真实传递的值传递过后会与 s q l 语句进行字符串拼接。 {}匹配的是真实传递的值传递过后会与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 ListStudent selectIf(Student s);public ListStudent selectWhere(Student s);public ListStudent selectChoose(Student s);public ListStudent findStudentTrim(Student s);public int updateSet(Student s);public int updateTrim(Student s);public ListStudent selectForEach(int[] array);public ListStudent selectForEachList(ListInteger list);public int insertlist(ListStudent list);// 模糊查询public ListStudent selectStudentLike(String keyname);
}StudentMapper.xml
?xml version1.0 encodingUTF-8 ?!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
!-- 指向接口的类路径 --
mapper namespacemapper.StudentMapper!-- sql片段 --sql idstusqlselect * from student/sqlselect idselectIf parameterTypestudentresultTypestudentinclude refidstusql/includewhere 11if testssex ! nulland ssex#{ssex}/ifif testclassid ! 0and classid#{classid}/if/selectselect idselectWhere parameterTypestudentresultTypestudentselect * from studentwhereif testssex ! nulland ssex#{ssex}/ifif testclassid ! 0and classid#{classid}/if/where/select!-- trim 万能标签prefix 开始添加一个什么prefixOverrides 开始去掉一个什么suffix 结束添加一个什么suffixOverrides 结束去掉一个什么--select idfindStudentTrim parameterTypestudent resultTypestudentselect * from student trim prefix where prefixOverridesand if testssex ! null and ssex #{ssex}/ifif testclassid ! 0 and classid #{classid}/if/trim/selectselect idselectChoose parameterTypestudentresultTypestudentselect * from studentwherechoosewhen testsnameand sname#{sname}/whenwhen testbirthdayand birthday#{birthday}/whenwhen testSsexand Ssex#{ssex}/whenwhen testclassidand classid#{classid}/whenotherwiseclassid2/otherwise/choose/where/selectupdate idupdateSet parameterTypestudentupdate studentsetif testsname!nullsname#{sname},/ifif testbirthday!nullbirthday#{birthday},/ifif testSsex!nullSsex#{ssex},/ifif testclassid!0classid#{classid},/if/setwhere sid#{sid}/updateupdate idupdateTrim parameterTypestudentupdate studenttrim prefixset prefixOverrides suffix suffixOverrides,if testsname!nullsname#{sname},/ifif testbirthday!nullbirthday#{birthday},/ifif testSsex!nullSsex#{ssex},/ifif testclassid!0classid#{classid},/if/trimwhere sid#{sid}/updateselect idselectForEach resultTypestudent!-- select * from student where sid in --include refidstusql/includewhere sid in!-- collection 参数名 item 要遍历的元素别名 open 起始字符 separator 分隔符 close 结束字符 --foreach collectionarray itemitem open( separator,close)#{item}/foreach/selectselect idselectForEachList resultTypestudentselect * from student where sid inforeach collectionlist itemitem open( separator, close)#{item}/foreach/select!-- 批量添加 --insert idinsertlistinsert into student(sname,birthday,ssex,classid) valuesforeach collectionlist items separator,(#{s.sname},#{s.birthday},#{s.ssex},#{s.classid})/foreach/insertselect idselectStudentLike parameterTypeStringresultTypestudent!-- 方式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 namev 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);// ListStudent list studentMapper.selectIf(s);// 动态SQL
// ListStudent list studentMapper.selectWhere(s);
// ListStudent list studentMapper.selectChoose(s);
// ListStudent list studentMapper.findStudentTrim(s);// int[] array { 1, 3, 5, 7, 9 };
// ListStudent list studentMapper.selectForEach(array);
// ListInteger listInteger Arrays.asList(1, 3, 5, 7, 9);
// ListStudent list studentMapper.selectForEachList(listInteger);// 方式1:业务层进行拼接
// ListStudent list studentMapper.selectStudentLike(%三%);// 方式2345ListStudent 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(男);
//
// ListStudent 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 ListStudent findAll();Select(select * from student where sname#{v})
public Student findByName(String name);测试
SqlSession sqlSession DaoUtil.getSqlSession();
StudentMapper studentMapper sqlSession.getMapper(StudentMapper.class);
ListStudent 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 方法还是不能重载
传递多个参数的方式
方法1Map 方式 跟sqlmap一样方法2JavaBean 方式跟sqlmap一样方法3Param 方式 实现
StudentMapper.java
Select(select * from student where ssex#{sex} limit #{cpage},#{size})
public ListStudent selectSexLimit(Param(sex) String sex,Param(cpage) int cpage, Param(size) int size);测试
SqlSession sqlSession DaoUtil.getSqlSession();
StudentMapper studentMapper sqlSession.getMapper(StudentMapper.class);
ListStudent 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(idsmaster_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 ListSMaster 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);
ListSMaster 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 ListStudent selectStudentAndClazz();Clazz.java
Select(select * from class where classid#{v})
public ListClazz selectAll(int classid);测试
SqlSession sqlSession DaoUtil.getSqlSession();
StudentMapper studentMapper sqlSession.getMapper(StudentMapper.class);
ListStudent 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 ListClazz selectClazzAndStudent();StudentMapper.java
Select(select * from student where classid#{v})
public ListStudent selectStudentByClassId(int classid);测试
SqlSession sqlSession DaoUtil.getSqlSession();
ClazzMapper clazzMapper sqlSession.getMapper(ClazzMapper.class);
ListClazz 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 ListClazz selectAll(int classid);Select(select * from class)public ListClazz 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 ListClazz 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 ListStudent findAll();Select(select * from student where sname#{v})public Student findByName(String name);Select(select * from student where ssex#{sex} limit #{cpage},#{size})public ListStudent 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 ListStudent selectStudentAndClazz();Select(select * from student where classid#{v})public ListStudent 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);
// ListStudent list studentMapper.findAll();ListStudent 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);ListSMaster 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);
// ListStudent list studentMapper.selectStudentAndClazz();
// for (Student student : list) {
// System.out.println(student);
// }ClazzMapper clazzMapper sqlSession.getMapper(ClazzMapper.class);ListClazz list clazzMapper.selectClazzAndStudent();for (Clazz clazz : list) {System.out.println(clazz);}DaoUtil.closeSqlSession(sqlSession);}
}
