Spring Boot使用AJAX从数据库读取数据异步刷新前端表格

　　近期项目需要是实现一个通过筛选选取所需数据刷新表格的功能，因为表格只占页面的一小部分，不希望整个也页面都随之刷新，所以首先想到了使用AJAX来实现。

　　以下介绍解决方法（请忽视拼音命名）。

　　筛选前先随便给table添加一些数据：

 

 

 

 

 

 

 

 

 

 

　　首先在domin中定义相应的数据结构，这里使用之前已经定义好的一个类，里面包含了我们所需的几个属性，所以就直接抱过来

public class JichutongjiTable {
    private int kaoshirenshu;
    private int yingdaorenshu;
    private double pingjunfen;
    private double fangcha;
    private int zhishidian;
    private int zuigaofen;
    private int zuidifen;

    public int getZuigaofen() {
        return zuigaofen;
    }

    public void setZuigaofen(int zuigaofen) {
        this.zuigaofen = zuigaofen;
    }

    public int getZuidifen() {
        return zuidifen;
    }

    public void setZuidifen(int zuidifen) {
        this.zuidifen = zuidifen;
    }

    public int getKaoshirenshu() {
        return kaoshirenshu;
    }

    public void setKaoshirenshu(int kaoshirenshu) {
        this.kaoshirenshu = kaoshirenshu;
    }

    public int getYingdaorenshu() {
        return yingdaorenshu;
    }

    public void setYingdaorenshu(int yingdaorenshu) {
        this.yingdaorenshu = yingdaorenshu;
    }

    public double getPingjunfen() {
        return pingjunfen;
    }

    public void setPingjunfen(double pingjunfen) {
        this.pingjunfen = pingjunfen;
    }

    public double getFangcha() {
        return fangcha;
    }

    public void setFangcha(double fangcha) {
        this.fangcha = fangcha;
    }

    public int getZhishidian() {
        return zhishidian;
    }

    public void setZhishidian(int zhishidian) {
        this.zhishidian = zhishidian;
    }
}

　　接着在mapper中定义定义所需的方法从数据库中取数据（请忽视乱糟糟的SQL......）

@Component
public interface JichutongjiMapper {
    //获取基础信息
    @Select("SELECT COUNT(*) as kaoshirenshu FROM (SELECT * FROM fenshu GROUP BY xuehao HAVING SUM(xueshengdefen)<>0) as t1 UNION ALL SELECT COUNT(*) as yingdaorenshu FROM (SELECT * FROM fenshu GROUP BY xuehao) as t1 UNION ALL SELECT FLOOR(sum(a)/COUNT(a)) as pingjunfen FROM (SELECT sum(xueshengdefen) as a FROM fenshu GROUP BY xuehao) AS b UNION ALL SELECT variance(a) as fangcha FROM (SELECT sum(xueshengdefen) as a FROM fenshu GROUP BY xuehao) AS b")
    public List<Integer> getJichutongji();
    //获取最高分
    @Select("SELECT MAX(tt.chengji) AS zuigaofen FROM (SELECT SUM(xueshengdefen) AS chengji FROM fenshu GROUP BY xuehao) AS tt")
    public Integer getMaxMark();
    //获取最低分
    @Select("SELECT MIN(tt.chengji) AS zuidifen FROM (SELECT SUM(xueshengdefen) AS chengji FROM fenshu GROUP BY xuehao) AS tt")
    public Integer getMinMark();
}

 

　　下一步编写service供controller调用

@Service
public class JichuqingkuangService {
    @Autowired
    private JichutongjiMapper jichutongjiMapper;
    @Autowired
    private  ShitifenxiMapper shitifenxiMapper;
    public List<Integer> getJichutongji(){
        List<Integer> list= jichutongjiMapper.getJichutongji();
        return list;
    }
        public Integer getMaxMark(){
        return jichutongjiMapper.getMaxMark();
    }
    public Integer getMinMark(){
        return jichutongjiMapper.getMinMark();
    }
}

　　然后，在controller中调用service并按规定路径返回给前台所需的数据

@Controller
public class IndexController {

    @Autowired
    private JichuqingkuangService jichuqingkuangService;
　　 @PostMapping(value="/easyuiIndexTable")
    @ResponseBody
    public Map<String,String> xiaoguoTable(){

        Map<String,String> map=new HashMap<>();
        List<Integer> list= jichuqingkuangService.getJichutongji();
        String highScore = ""+jichuqingkuangService.getMaxMark();
        String lowScore = ""+jichuqingkuangService.getMinMark();

        map.put("totalNumber",""+list.get(0));
        map.put("actualNumber",""+list.get(1));
        map.put("highScore",highScore);
        map.put("lowScore",lowScore);
        map.put("avgScore",""+list.get(2));
        map.put("variance",""+list.get(3));


        return map;
    }
}
    

　　在JS中处理数据并刷新前端显示：

<script type="text/javascript">
function refurbishIndex(){

    var optionJson=[];
    $.ajax({
        type:"post",
        url:"/easyuiIndexTable",
        data:{},
        async: false,
        success:function (data) {
            optionJson=data;

            var str="";

            str+="<tr>"+
                "<td>"+optionJson.totalNumber+"</td>"+
                "<td>"+optionJson.actualNumber+"</td>"+
                "<td>"+optionJson.highScore+"</td>"+
                "<td>"+optionJson.lowScore+"</td>"+
                "<td>"+optionJson.avgScore+"</td>"+
                "<td>"+optionJson.variance+"</td>"+
                "</tr>";

            document.getElementById("tbodydata").innerHTML=str;


        }
    });
}
</script>

　　前端HTML页面table代码：

<table style="width:100%;height:190px">
    <tr>
        <td style="width:100%">
            <table>
                <thead>
                <tr class="cen">
                    <th>实际人数（单位：人）</th>
                    <th>应到人数（单位：人）</th>
                    <th>最高分（单位：分）</th>
                    <th>最低分（单位：分）</th>
                    <th>考试平均分（单位：分）</th>
                    <th>考试方差（单位：1）</th>
                </tr>
                </thead>
                <tbody id="tbodydata">
                <tr>
                    <td>1111</td>
                    <td>2222</td>
                    <td>3333</td>
                    <td>4444</td>
                    <td>5555</td>
                    <td>6666</td>
                </tr>

                </tbody>
            </table>
        </td>
    </tr>

</table>

　　最后，在button中调用此刷新方法：

<button onclick="refurbishIndex()">筛选</button>　

 

　　至此，便完成了数据读取，传递，刷新的整个流程，点击筛选按钮后，table表格内的数据实现刷新

 

参考博文：http://blog.csdn.net/love254443233/article/details/48468397