spring+springmvc+mybaits
实现的功能查询一对多多对一
在单表操作基础上,加入多表关联功能
账户表
id username money
订单表
id 名称 下单时间 金额 账户id
多表关联 查询 1对多 多对一
我是在第一阶段的基础上实现的,也可以新建一个项目导入相关的配置。
一对多的查询
在数据库中创建对应的表
时间是自动生成
在idea项目的pojo包中创建对应的实体类
比如我的是Orders
public class Orders {
private Integer id;
private String oName;
private Date oTime;
private Double oMoney;
private Integer aId;
//多对一
private ArAccount arAccount;
}
在ArAccount中加入
private List<Orders> orders;
controller控制层
import com.alibaba.fastjson.JSON;
import com.lanou.pojo.ArAccount;
import com.lanou.pojo.Orders;
import com.lanou.service.OrdersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.util.List;
@Controller
@RequestMapping("/ordersController")
public class OrdersController {
@Autowired
private OrdersService ordersService;
//订单到用户
@RequestMapping("/findOrderToArAccount")
@ResponseBody
public String findOrderToArAccount(){
List<Orders> orders = ordersService.findOrderToArAccount();
System.out.println(orders);
return JSON.toJSONString(orders);
}
}
service层(service包)
OrdersService 接口
import com.lanou.pojo.ArAccount;
import com.lanou.pojo.Orders;
import java.util.List;
public interface OrdersService {
List<Orders> findOrderToArAccount();
}
OrdersServiceImpl 实现类
import com.lanou.mapper.ArAccountMapper;
import com.lanou.mapper.OrdersMapper;
import com.lanou.pojo.ArAccount;
import com.lanou.pojo.Orders;
import com.lanou.service.OrdersService;
import com.lanou.utils.MyException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.List;
@Service
public class OrdersServiceImpl implements OrdersService {
@Autowired
private OrdersMapper ordersMapper;
@Override
public List<Orders> findOrderToArAccount() {
return ordersMapper.findOrderToArAccount();
}
}
mapper包(dao层)
import com.lanou.pojo.ArAccount;
import com.lanou.pojo.Orders;
import java.util.List;
public interface OrdersMapper {
//订单对账户
List<Orders> findOrderToArAccount();
}
OrdersMapper.xml
在mybatis-config.xml中引入
<mapper resource="com/lanou/mapper/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.lanou.mapper.OrdersMapper">
<select id="findOrderToArAccount" resultMap="orderToArAccount">
select o.id as oid,o.o_name,o.o_time,o.o_money,o.a_id,a.*
from orders o join ar_account a on o.a_id = a.id
</select>
<resultMap id="orderToArAccount" type="Orders" autoMapping="true">
<id column="oid" property="id"></id>
<association property="arAccount" column="a_id" javaType="ArAccount" autoMapping="true">
<id column="id" property="id"></id>
</association>
</resultMap>
</mapper>
orderToArAccount.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>订单对账户</title>
<script type="application/javascript" src="js/jquery-3.4.1.js"></script>
<script>
$(function () {
$.get("/ordersController/findOrderToArAccount",function (data) {
$.each(data,function (index,obj) {
console.log(obj.arAccount.username);
var tr = "<tr>" +
"<td>"+obj.id+"</td>" +
"<td>"+obj.oName+"</td>" +
"<td>"+obj.oMoney+"</td>" +
"<td>"+timestampToTime(obj.oTime)+"</td>" +
"<td>"+obj.arAccount.username+"</td>" +
"<td>"+obj.arAccount.money+"</td>" +
"</tr>";
$("tbody").append(tr);
});
},"json");
});
function timestampToTime(timestamp) {
var date = new Date(timestamp);//时间戳为10位需*1000,时间戳为13位的话不需乘1000
var Y = date.getFullYear() + '-';
var M = (date.getMonth()+1 < 10 ? '0'+(date.getMonth()+1) : date.getMonth()+1) + '-';
var D = (date.getDate() < 10 ? '0'+date.getDate() : date.getDate()) + ' ';
var h = (date.getHours() < 10 ? '0'+date.getHours() : date.getHours()) + ':';
var m = (date.getMinutes() < 10 ? '0'+date.getMinutes() : date.getMinutes()) + ':';
var s = (date.getSeconds() < 10 ? '0'+date.getSeconds() : date.getSeconds());
return Y+M+D+h+m+s;
}
</script>
<style type="text/css">
table, td, th {
border: 1px solid black;
padding: 5px 3px;
}
</style>
</head>
<body>
<table>
<tr>
<td>订单id</td>
<td>订单名</td>
<td>订单金额</td>
<td>下单时间</td>
<td>账户名</td>
<td>账户资金</td>
</tr>
</table>
</body>
</html>
多对一
在mapper层中加入方法
//账户对订单 一对多
List<ArAccount> findArAccountToOrder();
在OrderMapper.xml中写sql语句
<select id="findArAccountToOrder" resultMap="arAccountMap">
select o.id as oid,o.o_name,o.o_time,o.o_money,o.a_id,a.*
from orders o join ar_account a on o.a_id = a.id
</select>
<resultMap id="arAccountMap" type="ArAccount" autoMapping="true">
<id column="id" property="id"></id>
<collection property="orders" column="a_id" ofType="Orders" autoMapping="true">
<id column="oid" property="id"></id>
</collection>
</resultMap>
在service包中
在OrdersService接口中加入
List<ArAccount> findArAccountToOrder();
在OrdersServiceImpl实现类中实现刚才的方法
@Override
public List<ArAccount> findArAccountToOrder() {
return ordersMapper.findArAccountToOrder();
}
在控制层(controller包)中加入下方法
OrdersController
//账户到账单
@RequestMapping("/findArAccountToOrder")
@ResponseBody
public String findArAccountToOrder(){
List<ArAccount> accounts = ordersService.findArAccountToOrder();
return JSON.toJSONString(accounts);
}
accountToOrder.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>账户对账单</title>
<script type="application/javascript" src="js/jquery-3.4.1.js"></script>
<script>
$(function () {
$.get("/ordersController/findArAccountToOrder",function (data) {
$.each(data,function (index,obj) {
var tr = "<tr><td>"+obj.id+"</td><td>"+obj.username+"</td><td>"+obj.money+"</td>" +
"</tr>";
$("tbody").append(tr);
$.each(obj.orders,function (i,order) {
var tr1 = "<tr>" +
"<td></td>" +
"<td>"+order.oName+"</td>" +
"<td>"+order.oMoney+"</td>" +
"<td>"+timestampToTime(order.oTime)+"</td>" +
"</tr>"
$("tbody").append(tr1);
});
});
},"json");
});
function timestampToTime(timestamp) {
var date = new Date(timestamp);
var Y = date.getFullYear()+"-";
var M = (date.getMonth()+1) +"-";
var D = date.getDay()+" ";
var da = date.toLocaleTimeString('chinese' ,{ hour12 : false});
return Y+M+D+da;
}
</script>
<!--<style type="text/css">
table, td, th {
border: 1px solid black;
padding: 5px 3px;
}
</style>-->
</head>
<body>
<table>
<tr>
<td>账户id</td>
<td>账户名</td>
<td>账户资金</td>
</tr>
</table>
</body>
</html>