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

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

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

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

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

1 public classJichutongjiTable {2 private intkaoshirenshu;3 private intyingdaorenshu;4 private doublepingjunfen;5 private doublefangcha;6 private intzhishidian;7 private intzuigaofen;8 private intzuidifen;9

10 public intgetZuigaofen() {11 returnzuigaofen;12 }13

14 public void setZuigaofen(intzuigaofen) {15 this.zuigaofen =zuigaofen;16 }17

18 public intgetZuidifen() {19 returnzuidifen;20 }21

22 public void setZuidifen(intzuidifen) {23 this.zuidifen =zuidifen;24 }25

26 public intgetKaoshirenshu() {27 returnkaoshirenshu;28 }29

30 public void setKaoshirenshu(intkaoshirenshu) {31 this.kaoshirenshu =kaoshirenshu;32 }33

34 public intgetYingdaorenshu() {35 returnyingdaorenshu;36 }37

38 public void setYingdaorenshu(intyingdaorenshu) {39 this.yingdaorenshu =yingdaorenshu;40 }41

42 public doublegetPingjunfen() {43 returnpingjunfen;44 }45

46 public void setPingjunfen(doublepingjunfen) {47 this.pingjunfen =pingjunfen;48 }49

50 public doublegetFangcha() {51 returnfangcha;52 }53

54 public void setFangcha(doublefangcha) {55 this.fangcha =fangcha;56 }57

58 public intgetZhishidian() {59 returnzhishidian;60 }61

62 public void setZhishidian(intzhishidian) {63 this.zhishidian =zhishidian;64 }65 }

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

1 @Component2 public interfaceJichutongjiMapper {3 //获取基础信息

4 @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")5 public ListgetJichutongji();6 //获取最高分

7 @Select("SELECT MAX(tt.chengji) AS zuigaofen FROM (SELECT SUM(xueshengdefen) AS chengji FROM fenshu GROUP BY xuehao) AS tt")8 publicInteger getMaxMark();9 //获取最低分

10 @Select("SELECT MIN(tt.chengji) AS zuidifen FROM (SELECT SUM(xueshengdefen) AS chengji FROM fenshu GROUP BY xuehao) AS tt")11 publicInteger getMinMark();12 }

下一步编写service供controller调用

1 @Service2 public classJichuqingkuangService {3 @Autowired4 privateJichutongjiMapper jichutongjiMapper;5 @Autowired6 privateShitifenxiMapper shitifenxiMapper;7 public ListgetJichutongji(){8 List list=jichutongjiMapper.getJichutongji();9 returnlist;10 }11 publicInteger getMaxMark(){12 returnjichutongjiMapper.getMaxMark();13 }14 publicInteger getMinMark(){15 returnjichutongjiMapper.getMinMark();16 }17 }

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

1 @Controller2 public classIndexController {3

4 @Autowired5 privateJichuqingkuangService jichuqingkuangService;6 　　 @PostMapping(value="/easyuiIndexTable")7 @ResponseBody8 public MapxiaoguoTable(){9

10 Map map=new HashMap<>();11 List list=jichuqingkuangService.getJichutongji();12 String highScore = ""+jichuqingkuangService.getMaxMark();13 String lowScore = ""+jichuqingkuangService.getMinMark();14

15 map.put("totalNumber",""+list.get(0));16 map.put("actualNumber",""+list.get(1));17 map.put("highScore",highScore);18 map.put("lowScore",lowScore);19 map.put("avgScore",""+list.get(2));20 map.put("variance",""+list.get(3));21

22

23 returnmap;24 }25 }26

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

functionrefurbishIndex(){var optionJson=[];

$.ajax({

type:"post",

url:"/easyuiIndexTable",

data:{},

async:false,

success:function(data) {

optionJson=data;var str="";

str+="

"+

"

"+optionJson.totalNumber+""+

"

"+optionJson.actualNumber+""+

"

"+optionJson.highScore+""+

"

"+optionJson.lowScore+""+

"

"+optionJson.avgScore+""+

"

"+optionJson.variance+""+

"

";

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

}

});

}

前端HTML页面table代码：

1

2

3

4

5

6

7

实际人数(单位：人)

8

应到人数(单位：人)

9

最高分(单位：分)

10

最低分(单位：分)

11

考试平均分(单位：分)

12

考试方差(单位：1)

13

14

15

16

17

1111

18

2222

19

3333

20

4444

21

5555

22

6666

23

24

25

26

27

28

29

30

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

筛选

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