目录
前言
浏览网页时我们经常发现一块区域无法将某一内容完全展示出来,但当我们点击这部分内容时,他会自动弹出该内容剩余部分,那我们该如何将这部分数据传输给前端同学呢。这是我们就需要用到resultMap工具,输出嵌套的列表,外层列表是一直要显示的数据,当用户想要了解详情,就可以看到内层列表的数据了。
这里我们用两张表来描述它
塔吊信息表
塔吊进出场时间表
在用户登录我们的网站时,可以看到两个塔吊的tcid,生产日期redate和承重weight
如果用户想要了解某个塔吊使用记录时,点击数据就可以根据tcid显示相应内容
一、Mybatis的resultMap工具
1.在导入相关包后配置文件
//mybatis.cfg.xml中
<?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>
<!-- <settings>-->
<!-- <!– 打印查询语句 可加可不加,动态sql中可以看到语句–>-->
<!-- <setting name="logImpl" value="STDOUT_LOGGING" />-->
<!-- </settings>-->
<!-- 给实体类起别名 方便引用-->
<typeAliases>
<typeAlias type="com.ybg.doublekill.entity.Towercares" alias="tower"></typeAlias>
<typeAlias type="com.ybg.doublekill.entity.InOuttime" alias="iotime"></typeAlias>
</typeAliases>
<environments default="gz">
<environment id="gz">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://自己虚拟机ip:3306/想要连接的数据库名字?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&allowPublicKeyRetrieval=true"/>
<property name="username" value="虚拟机账户"/>
<property name="password" value="密码"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/Towercares.xml"></mapper>
<mapper resource="mapper/InOutTime.xml"></mapper>
</mappers>
</configuration>
//Towercares.xml中
<?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.ybg.doublekill.mapper.TowercaresMapper">
<select id="findAll" resultType="tower">
select * from towercares
</select>
</mapper>
//InOutTime.xml中
<?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.ybg.doublekill.mapper.InOutTimeMapper">
<select id="findInOutTimeByTcid" parameterType="int" resultType="iotime">
select * from inouttime where tcid=#{tcid}
</select>
</mapper>
2.在Java中
//TowercaresMapper中
package com.ybg.doublekill.mapper;
import com.ybg.doublekill.entity.Towercares;
import java.util.List;
public interface TowercaresMapper {
public List<Towercares> findAll();
}
//InOutTimeMapper中
package com.ybg.doublekill.mapper;
import com.ybg.doublekill.entity.InOuttime;
import java.util.List;
public interface InOutTimeMapper {
public List<InOuttime> findInOutTimeByTcid(int cid);
public List<InOuttime> findInoutTime(InOuttime iot);
}
//Towercares中
package com.ybg.doublekill.entity;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Towercares {
private int tcid;
private Date redate;
private int weight;
private List<InOuttime> iots;
}
//列表iots用于存放通过tcid在第二个表中查询的数据
//InOuttime中
package com.ybg.doublekill.entity;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class InOuttime {
private int ioid;
private String project;
private int tcid;
private Date intime;
private Date outtime;
}
3.测试
1.通过for循环输出嵌套列表
package com.ybg.doublekill.demo;
import com.fasterxml.jackson.databind.json.JsonMapper;
import com.ybg.doublekill.entity.InOuttime;
import com.ybg.doublekill.entity.Towercares;
import com.ybg.doublekill.mapper.InOutTimeMapper;
import com.ybg.doublekill.mapper.TowercaresMapper;
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.Reader;
import java.util.List;
public class Demo {
public static void main(String[] args) throws Exception{
Reader read = Resources.getResourceAsReader("mybatis.cfg.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(read);
SqlSession session = factory.openSession();
TowercaresMapper tm = session.getMapper(TowercaresMapper.class);
InOutTimeMapper iom = session.getMapper(InOutTimeMapper.class);
List<Towercares> towers=tm.findAll();
System.out.println(towers);//输出在第一个表中的查询结果
for (Towercares tc:towers){
List<InOuttime> iot = iom.findInOutTimeByTcid(tc.getTcid());
tc.setIots(iot);
}
JsonMapper jm = new JsonMapper();
System.out.println(jm.writeValueAsString(towers));
//通过for循环将在第二个表的查询结果放入iot列表中,然后输出towers列表
}
}
如图,首先看外层,有tcid=1和tcid=2两个塔吊信息的生产日期redate和承重weight
而在每个weight后又有对应进出场表中的数据。
2.通过修改xml文件直接输出嵌套列表
//修改后的Towercares.xml
//注意resultType要改为resultMap
<?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.ybg.doublekill.mapper.TowercaresMapper">
<resultMap id="tower-iotime" type="tower">
<collection property="iots"//理解为根据column的值在iotime中查询,并将结果放入iots列表中
column="tcid"
select="com.ybg.doublekill.mapper.InOutTimeMapper.findInOutTimeByTcid"></collection>
</resultMap>
<select id="findAll" resultMap="tower-iotime">
select * from towercares
</select>
</mapper>
//修改过后Demo中
List<Towercares> towers=tm.findAll();
JsonMapper jm = new JsonMapper();
System.out.println(jm.writeValueAsString(towers));
也可以实现相同的效果
二、动态SQL
MyBatis提供了一种名为动态SQL的功能,它允许在SQL语句中根据不同的条件动态地生成不同的SQL片段。
动态SQL可以帮助您根据不同的情况构建灵活的查询语句,而无需编写多个静态的SQL语句。
1.配置文件
//在InOutTime.xml中加入语句
<?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.ybg.doublekill.mapper.InOutTimeMapper">
<select id="findInOutTimeByTcid" parameterType="int" resultType="iotime">
select * from inouttime where tcid=#{tcid}
</select>
<select id="findInoutTime" parameterType="iotime" resultType="iotime">
select * from inouttime
<where>
<if test="ioid!=0">
ioid=#{ioid}
</if>
<if test="project!=null">
and project=#{project}
</if>
<if test="tcid!=0">
and tcid=#{tcid}
</if>
<if test="intime!=null">
and intime=#{intime}
</if>
<if test="outtime!=null">
and outtime=#{outtime}
</if>
</where>
</select>
</mapper>
2.测试
package com.ybg.doublekill.demo;
import com.fasterxml.jackson.databind.json.JsonMapper;
import com.ybg.doublekill.entity.InOuttime;
import com.ybg.doublekill.entity.Towercares;
import com.ybg.doublekill.mapper.InOutTimeMapper;
import com.ybg.doublekill.mapper.TowercaresMapper;
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.Reader;
import java.util.List;
public class Demo {
public static void main(String[] args) throws Exception{
Reader read = Resources.getResourceAsReader("mybatis.cfg.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(read);
SqlSession session = factory.openSession();
InOutTimeMapper iom = session.getMapper(InOutTimeMapper.class);
InOuttime io=InOuttime.builder().build();//可以在builder()后加多个参数查询
System.out.println(iom.findInoutTime(io));
}
}
三、导入的部分包
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.12.3</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-annotations</artifactId>
<version>2.12.3</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.12.3</version>
</dependency>
总结
MyBatis 的 resultMap
和动态 SQL 是两个核心功能,用于在 MyBatis 中处理数据库结果映射和动态 SQL 查询。
可以参考 MyBatis 官方文档和示例代码,结合实际项目需求进行练习和深入学习。理解和熟练掌握 resultMap
和动态 SQL 的使用,可以更好地利用 MyBatis 进行数据库操作,并提高开发效率和代码质量。