private Integer id;
private Integer provinceId;
private Integer month;
private Integer industryInvest;
private Integer industryIncome;
private Integer industryProfits;//工业利润
private Double industryProfitsMargin;//工业利润率
private Double industryBiZhong;//工业收入比重
private Integer eConsumer;
private Integer agInvest;
private Integer agIncome;
private Integer agProfits;//农业利润
private Double agProfitsMargin;//农业利润率
//省份
private Province pro;
private Integer pid;
private String name;
private Integer pnum;
//查询条件
private Integer pnumMin;//最小人数
private Integer pnumMax;//最大人数
private Integer profitsMin;//最小总利润率
private Integer profitsMax;//最大总利润率
private Integer industryInvestMin;//最小工业投入
private Integer industryInvestMax;//最大工业投入
private Integer agIncomeMin;//最小农业收入
private Integer agIncomeMax;//最大农业收入
private Integer monthMin;//最小月份
private Integer monthMax;//最大月份
private Integer yearMin;//最小全年收入
private Integer yearMax;//最大全年收入
private Integer pid;
private String name;
private Integer pnum;
PageInfo<Gdp> findAll(Gdp gdp,Integer pageNum,Integer pageSize);
List<Gdp> findAll(Gdp gdp);
@Service
public class GdpServiceImpl implements GdpService{
@Autowired
GdpDao gdpDao;
public PageInfo<Gdp> findAll(Gdp gdp, Integer pageNum, Integer pageSize) {
PageHelper.startPage(pageNum, pageSize);
List<Gdp> list = gdpDao.findAll(gdp);
return new PageInfo<Gdp>(list);
}
}
1 <?xml version="1.0" encoding="UTF-8" ?>
2 <!DOCTYPE mapper
3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
5 <!-- 命名空间的值为dao层接口的权限定名 -->
6 <mapper namespace="com.bawei.dao.GdpDao" >
7
8 <select id="findAll" resultMap="maps">
9 select *,(g.industryIncome-g.industryInvest) as industryProfits,
10 ((g.industryIncome-g.industryInvest)/g.industryIncome*100) as industryProfitsMargin,
11 (g.agIncome-g.agInvest) as agProfits,
12 ((g.agIncome-g.agInvest)/g.agIncome*100) as agProfitsMargin,
13 (g.industryIncome/(g.industryIncome+g.agIncome)*100) as industryBiZhong
14 from tb_gdp g LEFT JOIN tb_province p ON g.provinceId=p.pid
15 <where>
16 <if test="provinceId!=null and provinceId!=0 ">
17 and provinceId=#{provinceId}
18 </if>
19 <if test="pnumMin!=null ">
20 and pnum >= #{pnumMin}
21 </if>
22 <if test="pnumMax!=null ">
23 and pnum <= #{pnumMax}
24 </if>
25 <if test="profitsMin!=null ">
26 and ((g.agIncome+g.industryIncome-g.agInvest-g.industryInvest)/(g.agIncome+g.industryIncome)*100) >= #{profitsMin}
27 </if>
28 <if test="profitsMax!=null ">
29 and ((g.agIncome+g.industryIncome-g.agInvest-g.industryInvest)/(g.agIncome+g.industryIncome)*100) >= #{profitsMax}
30 </if>
31 <if test="industryInvestMin!=null ">
32 and g.industryInvest >= #{industryInvestMin}
33 </if>
34 <if test="industryInvestMax!=null ">
35 and g.industryInvest <= #{industryInvestMax}
36 </if>
37 <if test="agIncomeMin!=null ">
38 and g.agIncome >= #{agIncomeMin}
39 </if>
40 <if test="agIncomeMax!=null ">
41 and g.agIncome <= #{agIncomeMax}
42 </if>
43 <if test="monthMin!=null ">
44 and g.month >= #{monthMin}
45 </if>
46 <if test="monthMax!=null ">
47 and g.month <= #{monthMax}
48 </if>
49 </where>
50 GROUP BY g.id
51 <trim prefix="having" prefixOverrides="and" >
52 <if test="yearMin!=null ">
53 and SUM(g.agIncome+g.industryIncome) >= #{yearMin}
54 </if>
55 <if test="yearMax!=null ">
56 and SUM(g.agIncome+g.industryIncome) <= #{yearMax}
57 </if>
58 </trim>
59 </select>
60
61 <resultMap type="Gdp" id="maps">
62 <id property="id" column="id" />
63 <result property="provinceId" column="provinceId" />
64 <result property="month" column="month" />
65 <result property="industryInvest" column="industryInvest" />
66 <result property="industryIncome" column="industryIncome" />
67 <result property="eConsumer" column="eConsumer" />
68 <result property="agInvest" column="agInvest" />
69 <result property="agIncome" column="agIncome" />
70
71 <result property="industryProfits" column="industryProfits" />
72 <result property="industryProfitsMargin" column="industryProfitsMargin" />
73 <result property="agProfits" column="agProfits" />
74 <result property="agProfitsMargin" column="agProfitsMargin" />
75 <result property="industryBiZhong" column="industryBiZhong" />
76
77 <association property="pro" javaType="Province">
78 <id property="pid" column="pid" />
79 <result property="name" column="name" />
80 <result property="pnum" column="pnum" />
81 </association>
82 </resultMap>
83
84
85
86
87 </mapper>
@Controller
public class GdpController {
@Reference
GdpService gdpService;
@RequestMapping("findAll")
public String findAll(Model model,Gdp gdp,@RequestParam(defaultValue = "1")Integer pageNum,@RequestParam(defaultValue = "5")Integer pageSize) {
PageInfo<Gdp> info = gdpService.findAll(gdp, pageNum, pageSize);
System.err.println(info);
model.addAttribute("info", info);
model.addAttribute("gdp", gdp);
return "list";
}
}
1 <%@ page language="java" contentType="text/html; charset=utf-8"
2 pageEncoding="utf-8"%>
3 <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
4 <!DOCTYPE html>
5 <html>
6 <head>
7 <meta charset="utf-8">
8 <title>Insert title here</title>
9 <link href="${pageContext.request.contextPath}/resource/bootstrap4/css/bootstrap.css" rel="stylesheet" >
10 <script type="text/javascript" src="${pageContext.request.contextPath}/resource/jquery/jquery-3.4.1.js"></script>
11 <script type="text/javascript" src="${pageContext.request.contextPath}/resource/bootstrap4/js/bootstrap.min.js"></script>
12 </head>
13 <body>
14
15 <form action="findAll" method="post">
16 省份:<select name="provinceId">
17 <option value="0">请选择</option>
18 <option ${gdp.provinceId==1?"selected":"" } value="1">辽宁</option>
19 <option ${gdp.provinceId==2?"selected":"" } value="2">山西</option>
20 <option ${gdp.provinceId==3?"selected":"" } value="3">河南</option>
21 <option ${gdp.provinceId==4?"selected":"" } value="4">吉林</option>
22 <option ${gdp.provinceId==5?"selected":"" } value="5">黑龙江</option>
23 </select>
24
25 人口数量:<input type="text" name="pnumMin" value="${gdp.pnumMin }" >--<input type="text" name="pnumMax" value="${gdp.pnumMax }" >
26
27 总利润率:<input type="text" name="profitsMin" value="${gdp.profitsMin }" >--<input type="text" name="profitsMax" value="${gdp.profitsMax }" >
28 <br>
29 工业投入:<input type="text" name="industryInvestMin" value="${gdp.industryInvestMin }" >--<input type="text" name="industryInvestMax" value="${gdp.industryInvestMax }" >
30
31 农业收入:<input type="text" name="agIncomeMin" value="${gdp.agIncomeMin }" >--<input type="text" name="agIncomeMax" value="${gdp.agIncomeMax }" >
32
33 月份:<input type="text" name="monthMin" value="${gdp.monthMin }" >--<input type="text" name="monthMax" value="${gdp.monthMax }" >
34 <br>
35 整月收入:<input type="text" name="yearMin" value="${gdp.yearMin }" >--<input type="text" name="yearMax" value="${gdp.yearMax }" >
36
37 <input type="submit" value="查询" >
38
39 </form>
40
41 <table class="table">
42 <tr>
43 <td>全选</td>
44 <td>省份</td>
45 <td>月份</td>
46 <td>人口数量</td>
47 <td>工业收入</td>
48 <td>工业投入</td>
49 <td>工业利润</td>
50 <td>工业利润率</td>
51 <td>耗电量</td>
52 <td>农业投入</td>
53 <td>农业收入</td>
54 <td>农业利润</td>
55 <td>农业利润率</td>
56 <td>工业收入比重</td>
57 <td>操作</td>
58 </tr>
59 <c:forEach items="${info.list }" var="g">
60 <tr>
61 <td></td>
62 <td>${g.pro.name }</td>
63 <td>${g.month }</td>
64 <td>${g.pro.pnum }</td>
65 <td>${g.industryIncome }</td>
66 <td>${g.industryInvest }</td>
67 <td>${g.industryProfits }</td>
68 <td>${g.industryProfitsMargin }%</td>
69 <td>${g.eConsumer }</td>
70 <td>${g.agInvest }</td>
71 <td>${g.agIncome }</td>
72 <td>${g.agProfits }</td>
73 <td>${g.agProfitsMargin }%</td>
74 <td>${g.industryBiZhong }%</td>
75 <td><a>修改</a></td>
76
77 </tr>
78 </c:forEach>
79 </table>
80 <a href="findAll?pageNum=${info.pageNum==1?info.pageNum:info.pageNum-1 }">上一页</a>
81 <a href="findAll?pageNum=${info.pageNum==info.pages?info.pageNum:info.pageNum+1 }">下一页</a>
82 </body>
83 </html>
一对多复杂sql查询