Mybatis接口结合xml、字段属性匹配以及联表查询
1.接口结合xml文件（相对于注解方式来说联表方便）
2.字段与实体类属性不匹配的处理
3.联表查询
一、接口结合xml文件
1.右键工程建议名为resources的Source Folder文件夹，用于存放config.xml、映射mappper包、properties文件
（经过编译后文件内容都会在bin文件夹下）
2. dao包下建接口
1.映射文件下<mapper>标签属性namespace要和接口所在的包匹配 2.各操作标签id要与接口中对应方法名一致==>作为接口的实现类 public interface RoleDao { public void addRole(Role role); public List<Role> selectById(@Param("min")int min,@Param("max")int max); } //@param注解参数命名,默认为[0, 1, param1, param2],见补充五 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.zhiyou100.yj.dao.OrderDao"> <insert id="addRole" parameterType="com.zhiyou100.yj.bean.Role"> insert into role(uname,description) values(#{uname},#{description}) </insert> <select id="selectById" resultType="com.zhiyou100.yj.bean.Role"> select * from role where id>#{min} and id<=#{max}; </select> </mapper>
3.测试
class RoleMapperTest { static SqlSession session=null; static RoleDao roleDao=null; @BeforeAll static void setUpBeforeClass() throws Exception { Reader reader = Resources.getResourceAsReader("config.xml"); SqlSessionFactory SessionFactory = new SqlSessionFactoryBuilder().build(reader); session = SessionFactory.openSession(); roleDao = session.getMapper(RoleDao.class); } @Test void testAddRole() { Role role=new Role("李大炳","助理"); roleDao.addRole(role); } @Test void testSelectById() { List<Role> roles = roleDao.selectById(3, 4); System.out.println(roles); } @AfterAll static void tearDownAfterClass() throws Exception { session.commit(); } }
补充一:接口结合xml如何实现参数传入
直接传参会报告以下异常: Error querying database. Cause:org.apache.ibatis.binding.BindingException: Parameter 'uname' not found. Available parameters are [0, 1, param1, param2] 这是由于mabatis把参数默认封装到map中,它的键有两种形式[0, 1, param1, param2] 解决这个问题有3种方法: 1、把参数改为Mybatis默认的参数[arg0, argX, param1, paramX]（X对应参数个数）; 2、把参数封装为一个JavaBean,然后把接口参数改为这个JavaBean; 3、方法传入map参数; 4、接口参数上使用@Param注解;（推荐）
二、字段名与实体类属性不匹配
public class Role{ private int id; private String uname; private String description; } <update id="updateRole"> update role set role_uname=#{uname},role_description=#{description} where role_id=#{id} </update> 报错:(查询时不匹配属性结果心显示为null) org.apache.ibatis.binding.BindingException: Type interface com.zhiyou100.yj.dao.RoleDao is not known to the MapperRegistry. 方法一:为字段起别名以匹配实体类属性 <select id="selectById" parameterType="int" resultType="com.zhiyou100.yj.bean.Role"> select role_uname name,role_description description,role_id id from role where role_id=#{id} </select> 方法二:resultMap属性替换resultType属性,并在resultMap中写字段属性对照关系 <select id="selectById" parameterType="int" resultMap="roleMap"> select * from role where role_id=#{id} </select> <resultMap type="com.zhiyou100.yj.bean.Role" id="roleMap"> <!-- type:表示那个实体类与表的对应关系,类比返回类型--> <id column="role_id" property="id"/> <result column="role_description" property="description"/> <result column="role_uname" property="name"/> </resultMap>
三、联表查询
1、多对一查询;一对一查询(一的一方作为另一方的属性,查询结果封装到对象) //如每个班级对应一个班主任 2、一对多查询(多的一方构成集合作为一的属性,查询结果封装到集合) //如每个班级包含多个学生 1、多对一查询;一对一查询 public class Clazz { private int cId; private String cName; private int teacheId; private Teacher teacher; //关联的是老师对象 private List<Student> students; //关联的是学生对象集合 } public class Teacher { private int id; private String tName; } public class Student { private int sId; private String sName; private int classId; } <!-- 一对一联表 --> <select id="selectClazzById" resultMap="ClazzMap"> select * from class c,teacher t where c.teacher_id=t.t_id and c_id=#{id} </select> <resultMap type="com.zhiyou100.yj.bean.Clazz" id="ClazzMap"> <id column="c_id" property="cId"/> <result column="c_name" property="cName"/> <result column="teacher_id" property="teacheId"/> <!-- property:Teacher对象属性 javaType:Teacher对象类型 --> <association property="teacher" javaType="com.zhiyou100.yj.bean.Teacher"> <id column="t_id" property="id"/> <result column="t_name" property="tName"/> </association> </resultMap> <!-- 一对一嵌套 --> <select id="selectClazzById2" resultMap="ClazzMap2"> select * from class where c_id=#{cid} <!--查询班级id,根据查询结果中获得的班主任id进一步查询--> </select> <resultMap type="com.zhiyou100.yj.bean.Clazz" id="ClazzMap2"> <id column="c_id" property="cId"/> <result column="c_name" property="cName"/> <result column="teacher_id" property="teacheId"/> <!-- property:Teacher对象属性 javaType:Teacher对象类型 select:对应进一步查询语句的id（另一张表查询） column:进一步查询对应的条件字段（外键字段） --> <association property="teacher" javaType="com.zhiyou100.yj.bean.Teacher" select="selectTeacher" column="teacher_id"> </association> </resultMap> <!--写法一:属性字段不匹配,起别名--> <select id="selectTeacher" resultType="com.zhiyou100.yj.bean.Teacher"> select t_id id,t_name tName from teacher where t_id=#{teacher_id} </select> <!--写法二:属性字段不匹配,resultMap--> <select id="selectTeacher" resultMap="TeacherMap"> select * from teacher where t_id=#{teacher_id} </select> <resultMap type="com.zhiyou100.yj.bean.Teacher" id="TeacherMap" > <id column="t_id" property="id"/> <result column="t_name" property="tName"/> </resultMap> <!-- 一对多联表 --> <select id="selectClazzById3" resultMap="ClazzMap3"> select * from class c,student s,teacher t where c.c_id=s.class_id and t.t_id=c.teacher_id and c.c_id=#{id}; </select> <resultMap type="com.zhiyou100.yj.bean.Clazz" id="ClazzMap3"> <id column="c_id" property="cId"/> <result column="c_name" property="cName"/> <result column="teacher_id" property="teacheId"/> <association property="teacher" javaType="com.zhiyou100.yj.bean.Teacher"> <!--对象类型:teacher--> <id column="t_id" property="id"/> <result column="t_name" property="tName"/> </association> <collection property="students" ofType="com.zhiyou100.yj.bean.Student"> <!-- 集合类型:students;ofType:集合泛型的数据类型--> <id column="s_id" property="sId"/> <result column="s_name" property="sName"/> <result column="class_id" property="classId"/> </collection> </resultMap> <!-- 一对多嵌套 --> <select id="selectClazz4" resultMap="ClazzMap4"> select * from class where c_id=#{cid} </select> <resultMap type="com.zhiyou100.yj.bean.Clazz" id="ClazzMap4"> <id column="c_id" property="cId" /> <result column="c_name" property="cName" /> <result column="teacher_id" property="teacheId" /> <association property="teacher" javaType="com.zhiyou100.yj.bean.Teacher" select="selectTeacher" column="teacher_id"> </association> <collection property="students" ofType="com.zhiyou100.yj.bean.Student" select="selectStudent" column="c_id"> </collection> </resultMap> <select id="selectTeacher" resultType="com.zhiyou100.yj.bean.Teacher"> select t_id id,t_name tName from teacher where t_id=#{teacher_id} </select> <select id="selectStudent" resultType="com.zhiyou100.yj.bean.Student"> select s_id sId,s_name sName,class_id classId from student where class_id=#{c_id} </select>
补充二:MyBatis中$与#的区别
$:解析时不会为内容添加"",是sql语句的拼接,存在sql注入危害 传入的是列名或表名时,可以使用$ eg:如果value的值是anything' OR 'x'='x,那么sql语句就会是select * from user where name='anything' or 'x'='x' #:解析时会为内容添加"",采用占位符,传递过来的#{xxx}的内容会被转义,然后替换掉"?"的内容 eg:select * from student where name='anything\' or \'x\'=\'x'
补充三:添加对象时如何把生产的id返回
<insert id="addRole" useGeneratedKeys="true" keyProperty="id">
insert into role(uname,description) values(#{uname},#{description})
</insert>
