Mybatis高级查询（二）

多表查询

准备工作

新建一个Maven工程

添加依赖

<dependencies> <!-- spring-webmvc --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>5.1.6.RELEASE</version> </dependency> <!-- mybatis --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.3.0</version> </dependency> <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.32</version> </dependency> <!-- 连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.12</version> </dependency> <!-- 日志框架 --> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.15</version> </dependency> <!-- Servlet支持Request和Response --> <dependency> <groupId>javax.servlet</groupId> <artifactId>servlet-api</artifactId> <version>2.5</version> <scope>provided</scope> </dependency> <dependency> <!-- 整合对jsp的支持 --> <groupId>javax.servlet.jsp</groupId> <artifactId>jsp-api</artifactId> <version>2.2.1-b03</version> <scope>provided</scope> </dependency> <!-- 对象转json的工具类 --> <dependency> <groupId>org.immutables</groupId> <artifactId>gson</artifactId> <version>2.8.0</version> </dependency> </dependencies>

springmvc核心配置文件

springmvc-config.xml

<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:context="http://www.springframework.org/schema/context" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"> <!-- 开启注解扫描 --> <context:component-scan base-package="com.mvc.controller"/> <!-- 视图解析器对象 --> <bean id="internalResourceViewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver"> <!-- 前缀（目录） --> <property name="prefix" value="/WEB-INF/"/> <!-- 后缀 --> <property name="suffix" value=".jsp"/> </bean> <!-- 开启SpringMVC框架注解的支持 --> <mvc:annotation-driven/> </beans>

mybatis核心配置文件

sqlMapConfig.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"> <!-- MyBatis的全局配置文件 --> <configuration > <environments default="develop"> <environment id="develop"> <transactionManager type="JDBC"></transactionManager> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://local:3306/mybatis?characterEncoding=utf-8"/> <property name="username" value="username"/> <property name="password" value="password"/> </dataSource> </environment> </environments> <mappers> <mapper resource="mapper/OrdersMapper.xml"/> </mappers> </configuration>

OrdersMapper.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.mvc.dao.OrdersMapper"> </mapper>

添加接口OrdersMapper

/** * 查询接口 */ public interface OrdersMapper { /** * 订单关联用户信息 * @param orders * @return */ public List<OrdersCustom> findOrdersUser(Orders orders); }

提供pojo类并提供get/set方法以及重写toString方法，这里以User类为例，除User类以外还应提供数据库表对应的类，如Orders,item等

User类：

public class User { private Integer id; private String username; private String sex; private Date birthday; private String address; //用户创建的订单 private List<Orders> ordersList; @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", sex='" + sex + '\'' + ", birthday=" + birthday + ", address='" + address + '\'' + ", ordersList=" + ordersList + '}'; } public Integer getId() { return id; } public void setId(Integer id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public List<Orders> getOrdersList() { return ordersList; } public void setOrdersList(List<Orders> ordersList) { this.ordersList = ordersList; } }

一对一查询

所谓的一对一就是查询的那张表只关联一张表

在OrdersMapper中添加一个接口方法

/** * 使用resultType查询订单表和用户表 * @param orders * @return */ public List<Orders> findOrdersUserResultMap(Orders orders);

在OrdersMapper.xml添加结果映射关系

<!-- 查询订单表和用户表的结果映射集 --> <resultMap id="OrderUserResultMap" type="com.mvc.pojo.Orders"> <id column="id" property="id"/> <result column="user_id" property="user_Id"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> <!-- 关联的表--> <association property="user" javaType="com.mvc.pojo.User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap> <!-- 使用resultType查询订单和用户表（一对一查询）--> <select id="findOrdersUserResultMap" resultMap="OrderUserResultMap" parameterType="com.mvc.pojo.Orders"> select orders.*,user.username,user.sex,user.address from orders,user where orders.user_id=user.id and orders.id=#{id} </select>

执行测试方法

@Controller public class controller { @RequestMapping(value = "/test",produces="text/json;charset=UTF-8") @ResponseBody//转换json public String query(Orders orders) throws IOException { InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(); OrdersMapper mapper = session.getMapper(OrdersMapper.class); //开始查询 List<Orders> ordersUserResultMap = mapper.findOrdersUserResultMap(orders); String s = JSON.toJSONString(ordersUserResultMap); return s; } }

启动tomcat在浏览器地址栏输入localhost:8080/test,会接收到一串json数据，这是查询到的结果

注意如果使用阿里巴巴的json转换工具data类型的数据发送到前端会转换成long类型的数字

这显然是前端开发人员和用户不想看到的，那又该如何解决这个问题呢？

除了toJSONString方法还提供了另外一个方法***toJSONStringWithDateFormat***

在测试类中改造Json

String s = JSON.toJSONStringWithDateFormat(ordersAndOrderDetailResultMap,"yyyy-MM-dd HH:mm:ss"); return s;

可以看到在浏览器中接收到的json数据已经正常了

[{"createtime":"2020-02-05 03:22:35","id":3,"number":"1000010","orderdetails":[{"id":1,"itemsId":1,"itemsNum":1,"ordersId":3},{"id":2,"itemsId":2,"itemsNum":3,"ordersId":3}],"user":{"address":"四川省阿坝藏族自治州","id":1,"sex":"2","username":"王五"},"userId":1}]

一对多查询

一对多就是查询的那张表对应多个表或者查询的那张表他对应的表对应多张表

在OrdersMapper接口中添加一个接口方法

/** * 联合查询（一对多）查询订单表和用户表和订单明细 * @param orders * @return */ public List<Orders> findOrdersAndOrderDetailResultMap(Orders orders);

在OrdersMapper.xml添加结果映射关系

<!-- 查询订单表和用户表的结果映射集 --> <resultMap id="OrderUserResultMap" type="com.mvc.pojo.Orders"> <id column="id" property="id"/> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> <!-- 关联的表--> <association property="user" javaType="com.mvc.pojo.User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap> <!-- 一对多查询 订单表关联用户表，订单明细表--> <!-- extends:继承 --> <resultMap id="OrdersAndOrderDetailResultMap" type="com.mvc.pojo.Orders" extends="OrderUserResultMap"> <collection property="orderdetails" ofType="com.mvc.pojo.Orderdetail"> <id column="orderdetail_id" property="id"/> <result column="items_id" property="itemsId"/> <result column="items_num" property="itemsNum"/> <result column="orders_id" property="ordersId"/> </collection> </resultMap> <!-- 一对多查询订单表关联用户表和订单明细--> <select id="findOrdersAndOrderDetailResultMap" resultMap="OrdersAndOrderDetailResultMap" parameterType="com.mvc.pojo.Orders"> select orders.*,user.username,user.sex,user.address,orderdetail.id orderdetail_id,orderdetail.items_id, orderdetail.items_num,orderdetail.orders_id from orders,user,orderdetail where orders.user_id= user.id and orderdetail.orders_id=orders.id and orders.id=#{id} </select>

进行测试

@Controller public class controller { @RequestMapping(value = "/test",produces="text/json;charset=UTF-8") @ResponseBody//转换json public String query(Orders orders) throws IOException { InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in); SqlSession session = factory.openSession(); OrdersMapper mapper = session.getMapper(OrdersMapper.class); //开始查询 List<Orders> ordersAndOrderDetailResultMap = mapper.findOrdersAndOrderDetailResultMap(orders); String s = JSON.toJSONString(ordersAndOrderDetailResultMap); return s; } }

启动tomcat在浏览器地址栏输入localhost:8080/test,会接收到一串json数据，这是查询到的结果