mybatis创建临时表_MyBatis的多表查询
teacher表
student表
多对⼀
结果嵌套
1：创建mybatis配置⽂件（l）
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-////DTD Config 3.0//EN"
"/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"/>
<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>
<mapper resource="cn/sxt/l" />
旅游景点网页设计免费模板</mappers>
</configuration>
这⾥使⽤了properties⽂件，可以不使⽤
sql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc
username=root
password=1234
2：创建⼯具类（MybatisUtils.java）⽤于获取SqlSession对象
public static SqlSessionFactory getSqlSessionFactory() throws IOException{
String resource = "l";
InputStream inputStream = ResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
return sqlSessionFactory;
}
public static SqlSession getSqlSession() throws IOException{
SqlSession sqlsession = getSqlSessionFactory().openSession();
return sqlsession;
}
}
String resource ="l" 为“包名+⽂件名”，此处mybatis配置⽂件直接放在src下，省略包名3：创建实体类对象
public class Student {
小说素材网private int id;
private String name;
private int tid;
private Teacher teacher;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
mysql语句多表查询public void setName(String name) {overflow css
this.name = name;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
}
}
从Student⼊⼿的多对⼀查询，需要在Student中添加⼀个Teacher类属性⽤于存放teacher表
private int id;
mysql训练课程private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
4：创建mapper映射⽂件⽤于书写SQL语句
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-////DTD Mapper 3.0//EN"
"/dtd/mybatis-3-mapper.dtd">
<mapper namespace="ity.student-mapper">
<select id="getStudentAndTeacher" resultMap="getStudentAndTeacherMap">
select s.id,s.name,s.tid,t.id,t.name from student s,teacher t where s.tid=t.id
</select>
<resultMap type="ity.Student" id="getStudentAndTeacherMap">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="tid" property="tid"/>
<!-- association为关联对象，在进⾏多表查询且相应返回类型的实体类中拥有其他类时使⽤。property表⽰数据库中的表，JavaType表⽰对应的实体类 --> <association property="teacher" javaType="ity.Teacher">
<id column="id" property="id"/>
<result column="name" property="name"/>
</association>
</resultMap>
</mapper>
从Student⼊⼿的多对⼀查询，需要在Student实体类中添加⼀个Teacher类属性，同时在mapper映射⽂件中使⽤resultMap标签，在其内使⽤association标签。association表⽰关联对象，其内的property属性表⽰数据库中的表，JavaType表⽰对应的实体类数据库中的两个表的列名是相同的，为避免混乱，在查询时最好使⽤别名，如下（不使⽤也可，如上）
<mapper namespace="ity.student-mapper">
<select id="getStudentAndTeacher" resultMap="getStudentAndTeacherMap">
select s.id sid,s.name sname,s.tid stid,t.id tid,t.name tname from student s,teacher t where s.tid=t.id
</select>
<resultMap type="ity.Student" id="getStudentAndTeacherMap">
<id column="sid" property="id"/>
<result column="sname" property="name"/>
<result column="stid" property="tid"/>
<!-- association为关联对象，在进⾏多表查询且相应返回类型的实体类中拥有其他类时使⽤。property表⽰数据库中的表，JavaType表⽰对应的实体类 --> <association property="teacher" javaType="ity.Teacher">
<id column="tid" property="id"/>
<result column="tname" property="name"/>
</association>
</resultMap>
</mapper>
5：创建Dao（StudentDao.java）
public class StudentDao {
public List<Student> getStudentAndTeacher() throws IOException{
SqlSession sqlsession = SqlSession();
List<Student> list = sqlsession.selectList("StudentAndTeacher");
sqlsessionmit();
return list;
}
}
测试类
public class Test {
public static void main(String[] args) throws IOException {
StudentDao dao = new StudentDao();
List<Student> list = StudentAndTeacher();
for(Student u:list){
System.out.Id()+" "+u.getName()+" "+u.getTid()+" "+u.getTeacher().getId()+" "+u.getTeacher().getName());
}
}
}
测试结果
1 张⼀ 1 1 张三
2 李⼆ 1 1 张三
3 王三 1 1 张三
查询嵌套
1：mapper映射⽂件中association标签的另⼀种使⽤⽅法
<mapper namespace="ity.student-mapper">
<select id="getStudentAndTeacher" resultMap="getStudentAndTeacherMap">
select * from student
</select>
<resultMap type="ity.Student" id="getStudentAndTeacherMap">
<association property="teacher" column="tid" javaType="ity.Teacher" select="Teacher">
</association>
</resultMap>
</mapper>
association标签中property属性表⽰数据库中的表，column属性表⽰外键，JavaType属性表⽰实体类，select属性表⽰对应表的查询语句
select属性表⽰对应表的查询语句，⽤“包名+⽂件名表⽰”，⼀般对于另⼀表的查询会写在另⼀个mapper映射⽂件之内。也可以写在同⼀⽂件内，若写在同⼀⽂件内，则包名可省略
结果嵌套对数据库查询⼀次，查询嵌套对数据库查询两次，所以结果嵌套的效率会更⾼，但查询嵌套看起来会更清爽点
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-////DTD Mapper 3.0//EN"
"/dtd/mybatis-3-mapper.dtd">
<mapper namespace="acher-mapper">
<select id="getTeacher" resultType="ity.Teacher">
select * from teacher
</select>
</mapper>
测试类
public class Test {
public static void main(String[] args) throws IOException {
StudentDao dao = new StudentDao();
List<Student> list = StudentAndTeacher();
for(Student u:list){
System.out.println(+u.getId()+" "+u.getName()+" "+u.getTid()+" "+u.getTeacher().getId()+" "+u.getTeacher().getName());
}
}
测试结果
1 张⼀ 0 1 张三
2 李⼆ 0 1 张三
3 王三 0 1 张三
此处Student的tid⽆法查询，原因未知
⼀对多
结果嵌套
1：创建mybatis配置⽂件（l）
2：创建⼯具类（MybatisUtils.java）⽤于获取SqlSession对象
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论