复杂查询环境配置
编写数据库
`CREATE TABLE `teacher` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8 INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); CREATE TABLE `student` ( `id` INT(10) NOT NULL, `name` VARCHAR(30) DEFAULT NULL, `tid` INT(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `fktid` (`tid`), CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
导入Lombok依赖maven包
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.20</version> </dependency>
db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/mybtias03?useSSL=false&useUnicode=true&characterEncoding=utf8 username=root password=123456
配置全局配置文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="db.properties"/> <typeAliases> <package name="com.lele.pojo"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <package name="com.lele.dao"/> </mappers> </configuration>
Util工具类
package com.lele.util; 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 java.io.IOException; import java.io.InputStream; public class MybatisUtil { private static SqlSessionFactory sqlSessionFactory; static { try { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } //获取SqlSession连接 public static SqlSession getSession(){ return sqlSessionFactory.openSession(true); } }
Mybatis代码实现
pojo类
package com.lele.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor public class Student { private int id; private String name; private Teacher teacher; }
package com.lele.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; }
给Mapper接口添加文件
public interface StudentMapper { List<Student> selectTeacher(); }
package com.lele.dao; public interface TeacherMapper { }
Mapper接口映射文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lele.dao.StudentMapper"> <resultMap id="studentTeacher" type="Student"> <id column="sid" property="id"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="id" column="tid"></result> <result property="name" column="tname"/> </association> </resultMap> <select id="selectTeacher" resultMap="studentTeacher"> select s.id sid, s.name sname , t.name tname,t.id tid from student s,teacher t where s.tid = t.id </select> </mapper>
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.lele.dao.TeacherMapper"> </mapper>
测试类
package com.lele.test; import com.lele.dao.StudentMapper; import com.lele.pojo.Student; import com.lele.util.MybatisUtil; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class Test01 { @Test public void test2(){ SqlSession session = MybatisUtil.getSession(); StudentMapper mapper = session.getMapper(StudentMapper.class); List<Student> students = mapper.selectTeacher(); for (Student student : students) { System.out.println(student); } } }
Mybatis注意点
接口映射文件xml注意点
接口映射文件xml的namespace为"包名+sql映射文件名"
映射文件xml的id名要与接口的方法名一致
resultMap结果映射集
collection用于 一对多关联
assocication用于一对一 多对一关联
Mybatis全局配置文件中
使用接口类导入 <mappers> <mapper class="com.lele.dao.StudentMapper"/> </mappers> 使用包名导入 <mappers> <package name="com.lele.dao"/> </mappers> 使用类路径导入 <mappers> <mapper resource="com/lele/dao/StudentMapper.xml"/> </mappers>