ssm综合练习第二阶段-CSDN博客

本文链接：https://blog.csdn.net/qq_39095899/article/details/106770983

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>

在这里插入图片描述