MyBatis框架学习笔记(三)
七. 高级映射
映射,就是对虚拟表中字段对应的值和对象中属性值,完成映射关系。之前,在做单表操作的时候,我们可以自定义映射的关系,当存储多表的时候,那么类中就涉及到对**关联对象(单个、集合)**的映射问题。
<resultMap>
标签:MyBatis最强大和最复杂的标签,通过该标签可以建立字段和属性之间的映射关系(单表)
7.1 关系描述
(1)数据中表和表之间的关系(多表)
-
主键:没有任何含义,就是一种标识
-
外键:永远建立在多方,外键相关说明
物理外键:
使用foreign key 作为外键关联另一张的字段的连接方法,而且限定了引擎为InnoDB
逻辑外键(又叫做事实外键,我们知道,数据库不知道,我们程序控制)
因为存在语法上的逻辑关联而产生的外键,需要有连接关键词inner join 或者left join 等等和连接部分,也就是on后面的部分,如果需要对应的设置,也可以加上set等语句。
- 一对一的关系
- 多表,建立外键在对方,并且该外键必须设置为索引为
unique
(唯一) - 多表,B标签中没有主键,B表中使用的主键为A表中的主键,一对一关系
- 多表,建立外键在对方,并且该外键必须设置为索引为
- 一对多和多对一的关系(分析角度不同)
- 多表关系,外键一定建立在多方
- 单表关系,自身一对多
- 多对多的关系
- 关系型数据库中不存在多对多的关系
- 实际我们使用中间表描述多对多的关系,将其拆分成两对一对多和多对一的关系
(2)Java 类和类之间关系(UML图)
- 继承关系(泛化关系)
- 实现关系
- 关联关系
- 1.一对一单向 2.一对一的双向
- 3.一对多单向 4.多对一的单向 5.一对多和多对一的双向
- 6.多对多的单向 7.多对多的双向
- 组合关系
- 聚合关系
- 依赖关系
7.2 一对一映射关系实现
数据库建立表:
(1)传统方式:由程序员控制逻辑
当我们查询已知的某一个学生的时候,我们可以获取学生的基本信息(包含班级信息)
- 第一步:通过已知的学生主键获取学生信息(包含外键信息)
- 第二步:获取外键字段对应的信息(多方外键和一方主键相关联)
- 第三步:通过班级主键获取班级信息
- 第四步:学生和班级建立关联关系
- 自定义student和classinfo对象
- xml文件:
<mapper namespace="com.yue.mapper.ClassInfoMapper">
<resultMap id="ClassInfoResultMapper" type="ClassInfo" autoMapping="false">
<!-- <id>标签:做主键映射,MyBatis框架通过ID标签区分是否是相同数据 -->
<!-- jdbcType/javaType可以省略不写 -->
<id column="class_id" property="classId"/>
<result column="class_name" property="className"/>
<result column="number" property="number"/>
</resultMap>
<sql id="classinfo_colums">
class_id,class_name,number
</sql>
<select id="getClassInfoByPK" parameterType="int" resultMap="ClassInfoResultMapper">
SELECT <include refid="classinfo_colums"/>
FROM classinfo
WHERE class_id = #{id}
</select>
</mapper>
<mapper namespace="com.yue.mapper.StudentMapper">
<resultMap id="StudentResultMapper" type="Student" autoMapping="false">
<!-- <id>标签:做主键映射,MyBatis框架通过ID标签区分是否是相同数据 -->
<!-- jdbcType/javaType可以省略不写 -->
<id column="student_id" jdbcType="INTEGER" property="studentId" javaType="java.lang.Integer"/>
<result column="student_name" jdbcType="VARCHAR" property="studentName" javaType="java.lang.String"/>
<result column="student_sex" jdbcType="VARCHAR" property="studentSex" javaType="java.lang.String"/>
<result column="age" jdbcType="INTEGER" property="age" javaType="java.lang.Integer"/>
<result column="birthday" jdbcType="DATE" property="birthday" javaType="java.util.Date"/>
<result column="class_id" property="classId"/>
</resultMap>
<sql id="student_colums">
student_id,student_name,student_sex,age,birthday,class_id
</sql>
<select id="getStudentByPK" parameterType="int" resultMap="StudentResultMapper">
SELECT
<include refid="student_colums"/>
FROM student
WHERE student_id=#{id};
</select>
</mapper>
- 测试文件:
/*传统编码方式:程序自己控制逻辑*/
@Test
public void test01(){
//第一步:通过已知的学生主键获取学生信息(包含外键信息)
Student student = studentMapper.getStudentByPK(666);
if (student!=null){
//第二步:获取外键字段对应的信息
System.out.println("111111111111");
Integer classId = student.getClassId();
System.out.println(classId);
if (classId!=null){
//第三步:通过班级主键获取班级信息
ClassInfo classInfo = classInfoMapper.getClassInfoByPK(classId);
if (classInfo!=null){
//第四步:学生和班级建立关联关系
student.setClassInfo(classInfo);
}
}
}
System.out.println("student = " + student);
}
- 结果:
(2)<resultMap>
标签下的子标签<association>
映射关联对象
<id>和<result>
只能对基本数据类型、简单引用数据类型(包装类、字符串、日期等)进行映射
<association>
映射单个对象:
property
:必填项,类中关联对象的属性名称javaType
:可以省略,关联对象的类型column
:传递哪个字段的值,跟select属性配合使用select
:定位执行指定的SQL语句,传递column对应的数据resultMap
:连接查询的时候,映射fetchType
:是否设置懒加载
resultMap
标签中extends
属性作用
<resultMap id="StudentSelectResultMapper" type="Student" autoMapping="false" extends="StudentResultMapper">
</resultMap>
继承
StudentResultMapper
的主键映射
- select方式配置
<resultMap id="StudentSelectResultMapper" type="Student" autoMapping="false" extends="StudentResultMapper">
<!-- 关联对象标签:底层完成硬编码的过程 -->
<association property="classInfo" javaType="ClassInfo"
column="class_id"
select="com.yue.mapper.ClassInfoMapper.getClassInfoByPK"/>
</resultMap>
<select id="loadStudentByPK" parameterType="int" resultMap="StudentSelectResultMapper">
SELECT
<include refid="student_colums"/>
FROM student
WHERE student_id=#{id};
</select>
硬编码的过程值指的是上述四步的代码
/*association标签,select定位方式*/
@Test
public void test02(){
//第一步:通过已知的学生主键获取学生信息(包含外键信息)
Student student = studentMapper.loadStudentByPK(666);
System.out.println ("student = " + student);
}
结果:执行成功
- 上述情况,生成SQL语句是两条,使用连接查询完成映射关系(自己完成)
<sql id="student_colums_alias">
${alias}.student_id,${alias}.student_name,${alias}.student_sex,${alias}.age,${alias}.birthday,${alias}.class_id
</sql>
<
resultMap id="StudentJoinResultMapper" type="Student" autoMapping="false">
<id column="student_id" property="studentId"/>
<result column="student_name" property="studentName"/>
<association property="classInfo">
<id column="class_id" property="classId"/>
<result column="class_name" property="className"/>
</association>
</resultMap>
<select id="queryStudentByPK" parameterType="int" resultMap="StudentJoinResultMapper">
SELECT
<include refid="student_columns_alias">
<property name="alias" value="s"/>
</include>
,
<include refid="com.yue.mapper.ClassInfoMapper.classinfo_columns_alias">
<property name="alias" value="c"/>
</include>
FROM student s
LEFT JOIN classinfo c ON s.class_id=c.class_id
WHERE s.student_id=#{id}
</select>
进化:
<sql id="student_colums_alias">
${alias}.student_id,${alias}.student_name,${alias}.student_sex,${alias}.age,${alias}.birthday,${alias}.class_id
</sql>
<resultMap id="StudentJoinResultMapper02" type="Student" autoMapping="false" extends="StudentResultMapper">
<association property="classInfo" resultMap="com.yue.mapper.ClassInfoMapper.ClassInfoResultMapper"/>
</resultMap>
<select id="selectStudentByPK" parameterType="int" resultMap="StudentJoinResultMapper02">
SELECT
<include refid="student_columns_alias">
<property name="alias" value="s"/>
</include>
,
<include refid="com.yue.mapper.ClassInfoMapper.classinfo_columns_alias">
<property name="alias" value="c"/>
</include>
FROM student s
LEFT JOIN classinfo c ON s.class_id=c.class_id
WHERE s.student_id=#{id}
</select>
7.3 多对一映射关系实现
面试题:
<association>
关联对象,处理每个学生都需要去查询对应学生的班级,当我们使用SELECT方式的时候,存在N+1问题?
查询4条数据:
DEBUG [main] - ==> Preparing: SELECT student_id,student_name,student_sex,age,birthday,class_id FROM student WHERE student_id <=7
DEBUG [main] - ==> Parameters:
DEBUG [main] - ====> Preparing: SELECT class_id,class_name,number FROM classinfo WHERE class_id=?
DEBUG [main] - ====> Parameters: 400(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - ====> Preparing: SELECT class_id,class_name,number FROM classinfo WHERE class_id=?
DEBUG [main] - ====> Parameters: 300(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - ====> Preparing: SELECT class_id,class_name,number FROM classinfo WHERE class_id=?
DEBUG [main] - ====> Parameters: 200(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - ====> Preparing: SELECT class_id,class_name,number FROM classinfo WHERE class_id=?
DEBUG [main] - ====> Parameters: 100(Integer)
DEBUG [main] - <==== Total: 1
DEBUG [main] - <== Total: 4
假如我们查询出N条学生的记录,需要查询N次班级信息,查询学生的SQL语句执行1条,查询学生的关联班级信息N条,所以产生N+1问题(其实,1+N问题更加合理)
使用懒加载
为了解决N+1问题,使用懒加载的方式,当我们使用关联对象的时候,才向数据库发送执行SQL语句,fetchType="lazy"
<resultMap id="StudentSelectResultMapper" type="Student" autoMapping="false"
extends="StudentResultMapper">
<!-- 关联对象标签:底层完成硬编码的过程 -->
<association property="classInfo" javaType="ClassInfo"
column="class_id"
select="com.yue.mapper.ClassInfoMapper.getClassInfoByPK"
fetchType="lazy"
/>
</resultMap>
<select id="listStudent" resultMap="StudentSelectResultMapper">
SELECT
<include refid="student_columns"/>
FROM student
WHERE student_id <=7
</select>
当使用关联对象的时候,才发送sql语句
使用连接查询,不存在懒加载问题
<select id="selectStudentList" resultMap="StudentJoinResultMapper02">
SELECT
<include refid="student_columns_alias">
<property name="alias" value="s"/>
</include>
,
<include refid="com.yue.mapper.ClassInfoMapper.classinfo_columns_alias">
<property name="alias" value="c"/>
</include>
FROM student s
LEFT JOIN classinfo c ON s.class_id=c.class_id
WHERE s.student_id <=7
</select>
7.4 一对多映射关系实现
(1)<resultMap>
标签下的子标签<collection>
映射关联集合对象
<collection>
的属性说明:
property
:必填项,类中关联对象集合的属性名称javaType
:可选,关联集合对象的类型ofType
:必填项,集合中泛型的类型column
:传递的字段值select
:执行定制的SQL语句,传递column字段的值resultMap
:连接查询映射
- select方式
<resultMap id="ClassInfoSelectResultMapper" type="ClassInfo" extends="ClassInfoResultMapper">
<!-- Java代码关联对象 List<Student> studentList; -->
<collection property="studentList"
javaType="java.util.List"
ofType="com.yue.model.Student"
column="class_id"
select="com.yue.mapper.StudentMapper.listStudentByClassId"/>
</resultMap>
<select id="getClassInfoByPK" parameterType="int"
resultMap="ClassInfoSelectResultMapper">
SELECT <include refid="classinfo_columns"/> FROM classinfo WHERE class_id=#{id}
</select>
- 连接查询:自己建立映射的时候
<id>
标签存在,根据id标签数据进行判断是否是同一条数据<id>
标签不存在,是比较每个字段都相等的时候判断其为同一条数据
<resultMap id="ClassInfoJointResultMapper" type="ClassInfo" extends="ClassInfoResultMapper">
<collection property="studentList" ofType="Student" resultMap="com.yue.mapper.StudentMapper.StudentResultMapper"/>
</resultMap>
<select id="loadClassInfoByPK" parameterType="int" resultMap="ClassInfoJointResultMapper">
SELECT
<include refid="classinfo_columns_alias">
<property name="alias" value="c"/>
</include>
,
<include refid="com.yue.mapper.StudentMapper.student_columns_alias">
<property name="alias" value="s"/>
</include>
FROM classinfo c
LEFT JOIN student s ON c.class_id=s.class_id
WHERE c.class_id=#{id}
</select>
八. MyBatis的注解配置
MyBatis的用户提供了快速的开发方式,因为我们使用大量XML配置文件编写比较繁琐(实际开发还是使用xml配置比较多),所以MyBatis提供了更加简便基于注解(Annotation)的配置方式
8.1 简单注解
首先将修改核心配置文件:
- 映射到接口
<!-- 加载映射文件:classpath下加载映射文件resources目录下 -->
<mappers>
<mapper class="com.yue.mapper.StudentMapper"/>
<mapper class="com.yue.mapper.ClassInfoMapper"/>
</mappers>
- 允许执行多条语句
<property name="jdbc.url" value="jdbc:mysql://127.0.0.1:3306/yue_mybatis?allowMultiQueries=true"/>
代码:
接口文件:
package com.yue.mapper;
import com.yue.model.ClassInfo;
import org.apache.ibatis.annotations.*;
import java.util.Map;
public interface ClassInfoMapper {
//映射插入语句(INSERT)
@Insert("INSERT INTO classInfo(class_name,number) VALUES (#{name},#{num})")//#{Map的KEY值}
int add(Map<String,Object> tempMap);
@Insert("INSERT INTO classInfo(class_name,number) VALUES (#{className},#{number})")//#{类中属性名称}
@Options(useGeneratedKeys = true,keyProperty = "classId")//自增长主键赋值给类中属性
int insert(ClassInfo classInfo);
//映射更新语句(UPDATE)
@Update("UPDATE classInfo SET number = #{num} WHERE class_id <= #{id}")
int update(@Param("num") Integer num, @Param("id") Integer classId);
//映射删除语句(DELETE)
@Delete("DELETE FROM classInfo WHERE class_id=#{id}")
int delete(Integer classId);
//物理关联,删除之前解除关系
@Delete("UPDATE student SET class_id = null WHERE class_id = #{id};DELETE FROM classInfo WHERE class_id = #{id}")
int remove(Integer classId);
//映射查询语句(SELECT)
@Select("SELECT * FROM classInfo WHERE class_id = #{id}")
Map<String,Object> getClassInfoMap(Integer classId);
}
测试文件:
@Test
public void test01(){
System.out.println("添加操作");
Map<String,Object> map = new HashMap<>();
map.put("name", "游戏");
map.put("num", "80");
int row = classInfoMapper.add(map);//自动返回影响的行数
System.out.println("添加Map的row = " + row);
ClassInfo classInfo = new ClassInfo();
classInfo.setClassName("地理");
classInfo.setNumber(90);
row = classInfoMapper.insert(classInfo);//自动返回影响的行数
System.out.println("添加classInfo的row = " + row+"返回主键"+classInfo);
System.out.println("更新操作");
row = classInfoMapper.update(66, 600);
System.out.println("更新row = " + row);
System.out.println("删除操作");
row = classInfoMapper.delete(500);
System.out.println("删除row = " + row);
row = classInfoMapper.remove(300);
System.out.println("删除row300 = " + row);
Map<String,Object> tempMap = classInfoMapper.getClassInfoMap(100);
System.out.println("tempMap = " + tempMap);
sqlSession.commit();
}
结果:
8.2 简单映射
- 接口文件:
//简单结果集映射
//映射查询ClassInfo语句(SELECT)
@Select("SELECT * FROM classInfo WHERE class_id = #{id}")
@Results(id = "StudentResultMapper",value = {
@Result(id = true,column = "class_id",property = "classId"),//相当于写了<id>标签: <id column="class_id" property="classId"/>
@Result(column = "class_name",property = "className"),
@Result(column = "number",property = "number"),
})//结果集复用性
ClassInfo getClassInfoByPK(Integer classId);
//映射查询ClassInfoList语句(SELECT)
@Select("SELECT * FROM classInfo WHERE class_id <=#{id}")
@ResultMap(value = "StudentResultMapper")//映射
List<ClassInfo> getClassInfoList(Integer classId);
- 测试文件:
//简单结果集映射
@Test
public void test02(){
System.out.println(classInfoMapper.getClassInfoByPK(200));
System.out.println(classInfoMapper.getClassInfoList(200));
}
- 结果:
8.3 高级映射
@Result
中一对一和一对多的属性
One one() default @One;
Many many() default @Many;
(1)一对多映射
注意:Results标签
@Results:做结果集映射,
做一对多映射的时候不能被继承.如果映射关联对象,需要重新映射
- classInfoMapper接口文件
//@Results:做结果集映射,不能被继承.如果映射关联对象,需要重新映射
@Select("SELECT * FROM classInfo WHERE class_id = #{id}")
@Results(id = "classInfoSelectResultMapper",value = {
@Result(id = true,column = "class_id",property = "classId"),//相当于写了<id>标签: <id column="class_id" property="classId"/>
@Result(column = "class_name",property = "className"),
@Result(column = "number",property = "number"),
@Result(column = "class_id",property = "studentList",
many = @Many(select = "com.yue.mapper.StudentMapper.getStudentListByClassId")
)
})//select方式
ClassInfo loadClassInfoByPK(Integer classId);
//@Results:做结果集映射,不能被继承.如果映射关联对象,需要重新映射
@Select("SELECT c.*,s.* FROM classInfo c LEFT JOIN student s ON c.class_id=s.class_id WHERE c.class_id = #{id}")
@Results(id = "classInfoJoinResultMapper",value = {
@Result(id = true,column = "class_id",property = "classId"),//相当于写了<id>标签: <id column="class_id" property="classId"/>
@Result(column = "class_name",property = "className"),
@Result(column = "number",property = "number"),
@Result(column = "class_id",property = "studentList",
many = @Many(resultMap = "com.yue.mapper.StudentMapper.studentResultMapper")
)
})//自定义映射方式
ClassInfo selectClassInfoByPK(Integer classId);
- studentMapper接口文件
@Select("SELECT * FROM student WHERE class_id = #{id}")
@Results(id = "studentResultMapper",value = {
@Result(id = true,column = "student_id",property = "studentId"),
@Result(column = "student_name",property = "studentName"),
@Result(column = "student_sex",property = "studentSex"),
@Result(column = "age",property = "age"),
@Result(column = "birthday",property = "birthday")
})
List<Student> getStudentListByClassId(Integer classId);
- 测试文件
//一对多映射
@Test
public void test03(){
System.out.println("一对多映射select方式");
System.out.println(classInfoMapper.loadClassInfoByPK(100));
System.out.println("连接查询");
System.out.println(classInfoMapper.selectClassInfoByPK(100));
}
- 结果
(2)一对一映射或者多对一映射
- 接口文件
//一对一
@Select("SELECT * FROM student WHERE student_id=#{id}")
@Results(id="studentSelectResultMapper",value = {
@Result(id = true,column = "student_id",property = "studentId"),
@Result(column = "student_name",property = "studentName"),
@Result(column = "student_sex",property = "studentSex"),
@Result(column = "age",property = "age"),
@Result(column = "birthday",property = "birthday"),
@Result(column = "class_id",property = "classInfo",
one = @One(select ="com.yue.mapper.ClassInfoMapper.getClassInfoByPK" )
)
})
Student getStudentByPK(Integer studentId);
//多对一select
@Select("SELECT * FROM student")
@ResultMap("studentSelectResultMapper")//复用性
List<Student> getStudentList();
//多对一连接查询
@Select("SELECT s.*,c.* FROM student s LEFT JOIN classInfo c ON s.class_id=c.class_id")
@Results(id="studentJoinResultMapper",value = {
@Result(id = true,column = "student_id",property = "studentId"),
@Result(column = "student_name",property = "studentName"),
@Result(column = "student_sex",property = "studentSex"),
@Result(column = "age",property = "age"),
@Result(column = "birthday",property = "birthday"),
@Result(column = "class_id",property = "classInfo",
one = @One(resultMap ="com.yue.mapper.ClassInfoMapper.StudentResultMapper" )//映射
)
})
List<Student> queryStudentList();
- 测试文件
//一对一和多对一映射
@Test
public void test04(){
System.out.println("一对一映射select方式");
System.out.println(studentMapper.getStudentByPK(2));
System.out.println("多对一映射select方式");
System.out.println(studentMapper.getStudentList());
System.out.println("多对一连接查询");
System.out.println(studentMapper.queryStudentList());
}
- 结果:
8.4 动态SQL语句
(1)使用script
标签,支持MyBatis提供的动态SQL语句的标签
/*支持动态脚部*/
/*支持动态脚本*/
@Insert("<script>" +
"INSERT INTO classInfo (class_name,number) " +
"<foreach collection='list' item='classInfo' open='VALUES' separator=','>" +
"(#{classInfo.className},#{classInfo.number})" +
"</foreach>" +
"</script>")
int addBatch(List<ClassInfo> classInfoList);
测试文件:
@Test
public void test05(){
List<ClassInfo> classInfoList = new ArrayList<>();
ClassInfo c1 = new ClassInfo();
c1.setClassName("古诗");
c1.setNumber(80);
classInfoList.add(c1);
c1 = new ClassInfo();
c1.setClassName("古词");
c1.setNumber(90);
classInfoList.add(c1);
c1 = new ClassInfo();
c1.setClassName("元曲");
c1.setNumber(90);
classInfoList.add(c1);
c1 = new ClassInfo();
c1.setClassName("汉赋");
c1.setNumber(100);
classInfoList.add(c1);
int row = classInfoMapper.addBatch(classInfoList);
System.out.println("row = " + row);
}
结果:
(2 使用自定义类,组装动态SQL语句
ClassInfoDynamicSQLProvider
代码在8.5- 测试文件在8.5
动态查询
- 接口文件
注意:List<ClassInfo> getClassList(String className,Integer number);
不能取别名@param
//自定义对象方式
@SelectProvider(type = //自定义对象方式
@SelectProvider(type = ClassInfoDynamicSQLProvider.class,method = "whereMethod01")
@ResultMap("StudentResultMapper")
List<ClassInfo> getClassList(String className,Integer number);
//sql对象+where标签
@SelectProvider(type = ClassInfoDynamicSQLProvider.class,method = "whereMethod02")
@ResultMap("StudentResultMapper")
List<ClassInfo> queryClassList(ClassInfo classInfo);
- 测试文件:
//自定义对象方式动态sql查询
@Test
public void test06(){
List<ClassInfo> list = classInfoMapper.getClassList("", null);
System.out.println("list01 = " + list);
list = classInfoMapper.getClassList("古诗", null);
System.out.println("list02 = " + list);
list = classInfoMapper.getClassList(" ", 90);
System.out.println("list03 = " + list);
list = classInfoMapper.getClassList("古诗", 80);
System.out.println("list04 = " + list);
}
SQL对象WHERE标签
@Test
public void test07(){
ClassInfo classInfo = new ClassInfo();
List<ClassInfo> list = classInfoMapper.queryClassList(classInfo);
System.out.println("list01 = " + list);
classInfo.setClassName("古诗");
list = classInfoMapper.queryClassList(classInfo);
System.out.println("list02 = " + list);
classInfo.setNumber(90);
list = classInfoMapper.queryClassList(classInfo);
System.out.println("list03 = " + list);
classInfo.setClassName("古诗");
classInfo.setNumber(80);
list = classInfoMapper.queryClassList(classInfo);
System.out.println("list04 = " + list);
}
动态更新
- 接口文件
//动态更新
@UpdateProvider(type = ClassInfoDynamicSQLProvider.class,method = "update01")
void edit(ClassInfo classInfo);
动态添加
- 接口文件
//动态添加
@UpdateProvider(type = ClassInfoDynamicSQLProvider.class,method = "add01")
void addClassinfo(ClassInfo classInfo);
动态删除
注意OR()
的使用
//动态删除
@DeleteProvider(type = ClassInfoDynamicSQLProvider.class,method = "delete")
void deleteClassInfo(int[] arr);
8.5 附录代码
- 生成器对象
ClassInfoDynamicSQLProvider
文件:
package com.yue.provider;
import com.yue.model.ClassInfo;
import org.apache.ibatis.jdbc.SQL;
public class ClassInfoDynamicSQLProvider {
//恒等式
public String whereMethod01(String className,Integer number){
String sql = "SELECT * FROM classinfo WHERE 1=1 ";
if(className!=null && className.trim().length()>0){
sql+=" AND class_name LIKE CONCAT('%','"+className+"','%')";
}
if(number!=null){
sql+=" AND number="+number;
}
System.out.println("sql = " + sql);
return sql;
}
//SQL对象WHERE标签
public String whereMethod02(ClassInfo classInfo){
SQL sql = new SQL();
sql.SELECT_DISTINCT("class_id","class_name","number");
sql.FROM("classinfo");
if(classInfo.getClassName()!=null && classInfo.getClassName().trim().length()>0){
sql.WHERE("class_name LIKE CONCAT('%',#{className},'%')");
}
if(classInfo.getNumber()!=null){
//sql.OR();
sql.WHERE("number=#{number}");//默认使用and连接
}
System.out.println("sql = " + sql.toString());
return sql.toString();
}
//SQL对象WHERE标签
public String update01(ClassInfo classInfo){
return new SQL(){
{
UPDATE("classinfo");
if(classInfo.getClassName()!=null && classInfo.getClassName().trim().length()>0){
SET("class_name=#{className}");
}
if(classInfo.getNumber()!=null){
SET("number=#{number}");
}
WHERE("class_id=#{classId}");
}
}.toString();
}
//SQL对象WHERE标签
public String add01(ClassInfo classInfo){
return new SQL(){
{
INSERT_INTO("classinfo");
if(classInfo.getClassName()!=null && classInfo.getClassName().trim().length()>0){
VALUES("class_name","#{className}");
}
if(classInfo.getNumber()!=null){
VALUES("number","#{number}");
}
}
}.toString();
}
//SQL对象WHERE标签
public String delete(int[] arr){
return new SQL(){
{
DELETE_FROM("classinfo");
for (int i = 0; i <arr.length ; i++) {
OR();
WHERE("class_id="+arr[i]);
}
}
}.toString();
}
}
- 测试文件代码
package com.yue.test;
import com.yue.mapper.ClassInfoMapper;
import com.yue.mapper.StudentMapper;
import com.yue.model.ClassInfo;
import com.yue.model.Student;
import com.yue.util.MyBatisUtils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MybatisTest {
private SqlSession sqlSession;
private StudentMapper studentMapper;
private ClassInfoMapper classInfoMapper;
@Before
public void init()throws Exception{
sqlSession = MyBatisUtils.getSqlSession();
studentMapper = sqlSession.getMapper(StudentMapper.class);
classInfoMapper = sqlSession.getMapper(ClassInfoMapper.class);
}
@After
public void close(){MyBatisUtils.closeSqlSession(sqlSession);}
@Test
public void test01(){
System.out.println("添加操作");
Map<String,Object> map = new HashMap<>();
map.put("name", "游戏");
map.put("num", "80");
int row = classInfoMapper.add(map);//自动返回影响的行数
System.out.println("添加Map的row = " + row);
ClassInfo classInfo = new ClassInfo();
classInfo.setClassName("地理");
classInfo.setNumber(90);
row = classInfoMapper.insert(classInfo);//自动返回影响的行数
System.out.println("添加classInfo的row = " + row+"返回主键"+classInfo);
System.out.println("更新操作");
row = classInfoMapper.update(66, 600);
System.out.println("更新row = " + row);
System.out.println("删除操作");
row = classInfoMapper.delete(500);
System.out.println("删除row = " + row);
row = classInfoMapper.remove(300);
System.out.println("删除row300 = " + row);
Map<String,Object> tempMap = classInfoMapper.getClassInfoMap(100);
System.out.println("tempMap = " + tempMap);
sqlSession.commit();
}
//简单结果集映射
@Test
public void test02(){
System.out.println(classInfoMapper.getClassInfoByPK(200));
System.out.println(classInfoMapper.getClassInfoList(200));
}
//一对多映射
@Test
public void test03(){
System.out.println("一对多映射select方式");
System.out.println(classInfoMapper.loadClassInfoByPK(100));
System.out.println("一对多连接查询");
System.out.println(classInfoMapper.selectClassInfoByPK(100));
}
//一对一和多对一映射
@Test
public void test04(){
System.out.println("一对一映射select方式");
System.out.println(studentMapper.getStudentByPK(2));
System.out.println("多对一映射select方式");
System.out.println(studentMapper.getStudentList());
System.out.println("多对一连接查询");
System.out.println(studentMapper.queryStudentList());
}
//动态添加sql语句
@Test
public void test05(){
List<ClassInfo> classInfoList = new ArrayList<>();
ClassInfo c1 = new ClassInfo();
c1.setClassName("古诗");
c1.setNumber(80);
classInfoList.add(c1);
c1 = new ClassInfo();
c1.setClassName("古词");
c1.setNumber(90);
classInfoList.add(c1);
c1 = new ClassInfo();
c1.setClassName("元曲");
c1.setNumber(90);
classInfoList.add(c1);
c1 = new ClassInfo();
c1.setClassName("汉赋");
c1.setNumber(100);
classInfoList.add(c1);
int row = classInfoMapper.addBatch(classInfoList);
System.out.println("row = " + row);
sqlSession.commit();
}
//自定义对象方式动态sql查询
@Test
public void test06(){
List<ClassInfo> list = classInfoMapper.getClassList("", null);
System.out.println("list01 = " + list);
list = classInfoMapper.getClassList("古诗", null);
System.out.println("list02 = " + list);
list = classInfoMapper.getClassList(" ", 90);
System.out.println("list03 = " + list);
list = classInfoMapper.getClassList("古诗", 80);
System.out.println("list04 = " + list);
}
SQL对象WHERE标签
@Test
public void test07(){
ClassInfo classInfo = new ClassInfo();
List<ClassInfo> list = classInfoMapper.queryClassList(classInfo);
System.out.println("list01 = " + list);
classInfo.setClassName("古诗");
list = classInfoMapper.queryClassList(classInfo);
System.out.println("list02 = " + list);
classInfo.setNumber(90);
list = classInfoMapper.queryClassList(classInfo);
System.out.println("list03 = " + list);
classInfo.setClassName("古诗");
classInfo.setNumber(80);
list = classInfoMapper.queryClassList(classInfo);
System.out.println("list04 = " + list);
}
//动态更新
@Test
public void test08(){
ClassInfo classInfo = new ClassInfo();
classInfo.setClassId(427);
classInfo.setClassName("唐诗");
classInfoMapper.edit(classInfo);
classInfo = new ClassInfo();
classInfo.setClassId(428);
classInfo.setClassName("宋词");
classInfo.setNumber(110);
classInfoMapper.edit(classInfo);
sqlSession.commit();
}
//动态添加
@Test
public void test09(){
ClassInfo classInfo = new ClassInfo();
classInfo.setClassName("楚辞");
classInfoMapper.addClassinfo(classInfo);
classInfo = new ClassInfo();
classInfo.setClassName("诗经");
classInfo.setNumber(110);
classInfoMapper.addClassinfo(classInfo);
sqlSession.commit();
}
//动态删除
@Test
public void test10(){
classInfoMapper.deleteClassInfo(new int[]{600,500,700});
sqlSession.commit();
}
}