In ssm and springboot projects, we have one to many and many to many situations between tables, so they need to perform cascade query to query the associated data. The problem involved in cascade query is that there are objects (one to one) or object collections (one to many) of another entity class in one entity class, When using layui, we will page the query data and return it to the front end. Layui will page automatically. We often use to return the data of an entity class. In case of cascading query, we may not know how to transfer the data to the front end, and the table will be rendered automatically. We don't talk much about the subject.
First look at my entity class.
Admin.java(Admin Inside BuyTotal Set of, one to many)
package com.zlx.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import org.springframework.data.annotation.Id;
import javax.persistence.GeneratedValue;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
//Administrator account class
public class Admin {
@Id
@GeneratedValue
private Integer adminid;
private String username;
private String password;
private String identify;
//There are multiple records in one cashier
private List<BuyTotal> buyTotalList;
}
BuyTotal.java(Inside User Objects, one-on-one, and BuyRecord Set, one to many)
package com.zlx.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.data.annotation.Id;
import javax.persistence.GeneratedValue;
import java.util.List;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class BuyTotal {
@Id
@GeneratedValue
private int totalid;
private int uid;
private double salesMoney;
private double payMoney;
private double changeMoney;
private int adminid;
//There are multiple detailed records in a general record
private List<BuyRecord> buyRecordList;
//A total record corresponds to a user;
private User user;
}
User.java
package com.zlx.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import org.springframework.data.annotation.Id;
import javax.persistence.GeneratedValue;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
@Id
@GeneratedValue
private Integer uid;
private String idCard;
private String integral;
}
BuyRecord.java
package com.zlx.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@AllArgsConstructor
@Data
@NoArgsConstructor
public class BuyRecord {
private int totalid;
private int gid;
private int buyAmount;
//A detailed record records a commodity
private Goods goods;
}
Goods.java
package com.zlx.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Goods {
private Integer gid;
private String gNo;
private String gName;
private String price;
private String isBargainPrice;
private String bargainPrice;
private String unit;
private String stock;
}
The following is the cascade query source code in mybatis. Several tables are combined to query data through cascade query
<select id="queryAllRecordByCondition" parameterType="map" resultMap="AdminMapping">
SELECT buytotal.totalid AS buy_tid,idcard,gname,price,isbargainprice,bargainPrice,unit,buyAmount,salesMoney,payMoney,changeMoney,integral,username
FROM ADMIN ,buyrecord,buytotal,goods,USER WHERE
admin.adminid=buytotal.adminid AND user.uid=buytotal.uid
AND buytotal.totalid=buyrecord.totalid AND goods.gid = buyrecord.gid
<if test="username!=null and username!=''">
AND username like concat('%',#{username},'%')
</if>
<if test="idCard!=null and idCard!=''">
and idCard like concat('%',#{idCard},'%')
</if>
</select>
<resultMap id="AdminMapping" type="com.zlx.pojo.Admin">
<id column="adminid" property="adminid"></id>
<result column="username" property="username"></result>
<collection property="buyTotalList" ofType="com.zlx.pojo.BuyTotal" resultMap="buyTotalMapping"></collection>
</resultMap>
<resultMap id="buyTotalMapping" type="com.zlx.pojo.BuyTotal">
<id column="buy_tid" property="totalid"></id>
<result column="salesMoney" property="salesMoney"></result>
<result column="payMoney" property="payMoney"></result>
<result column="changeMoney" property="changeMoney"></result>
<association property="user" javaType="com.zlx.pojo.User" resultMap="UserMapping"></association>
<collection property="buyRecordList" ofType="com.zlx.pojo.BuyRecord" resultMap="BuyRecordMapping"></collection>
</resultMap>
<resultMap id="UserMapping" type="com.zlx.pojo.User">
<result column="idCard" property="idCard"></result>
<result column="integral" property="integral"></result>
</resultMap>
<resultMap id="BuyRecordMapping" type="com.zlx.pojo.BuyRecord">
<result column="buyAmount" property="buyAmount"></result>
<association property="goods" javaType="com.zlx.pojo.Goods" resultMap="GoodsMapping"></association>
</resultMap>
<resultMap id="GoodsMapping" type="com.zlx.pojo.Goods">
<result column="gName" property="gName"></result>
<result column="price" property="price"></result>
<result column="isBargainPrice" property="isBargainPrice"></result>
<result column="bargainPrice" property="bargainPrice"></result>
<result column="unit" property="unit"></result>
</resultMap>
The found data is finally paged through the controller and then transmitted to the front end. The most important step in whether to render the data to the layui table is to set the cols of the layui. Only one entity class is very simple, and the attributes of the entity class can be written there directly, while the cascade has object 1 Object 2 Property, as shown in the following code
cols:
[[
{
field: 'buyTotalList', templet: function (data) {
return data.buyTotalList[0].totalid;
}, title: 'Odd Numbers', width: 120, sort: true, align: "center"
},
{
field: 'user', templet: function (data) {
return data.buyTotalList[0].user.idCard;
}, title: 'Membership card number', width: 100, align: "center"
},
{
field: 'goods', templet: function (data) {
return data.buyTotalList[0].buyRecordList[0].goods.gname;
}, title: 'Trade name', width: 150, align: "center"
},
{
field: 'goods', templet: function (data) {
return data.buyTotalList[0].buyRecordList[0].goods.price;
}, title: 'original price', width: 80, sort: true, align: "center"
},
{
field: 'goods', templet: function (data) {
return data.buyTotalList[0].buyRecordList[0].goods.isBargainPrice;
}, title: 'Special price', width: 120, sort: true, align: "center"
},
{
field: 'goods', templet: function (data) {
return data.buyTotalList[0].buyRecordList[0].goods.bargainPrice;
}, title: 'Special price', width: 120, sort: true, align: "center"
},
{
field: 'goods', templet: function (data) {
return data.buyTotalList[0].buyRecordList[0].goods.unit;
}, title: 'Company', width: 80, align: "center"
},
{
field: 'buyRecordList', templet: function (data) {
return data.buyTotalList[0].buyRecordList[0].buyAmount;
}, title: 'Purchase quantity', width: 120, align: "center"
},
{
field: 'buyTotalList', templet: function (data) {
return data.buyTotalList[0].salesMoney;
}, title: 'Amount of this document', width: 120, align: "center"
},
{
field: 'buyTotalList', templet: function (data) {
return data.buyTotalList[0].payMoney;
}, title: 'Paid in amount', width: 120, align: "center"
},
{
field: 'buyTotalList', templet: function (data) {
return data.buyTotalList[0].changeMoney;
}, title: 'give change', width: 80, align: "center"
},
{
field: 'user', templet: function (data) {
return data.buyTotalList[0].user.integral;
}, title: 'integral', width: 80, sort: true, align: "center"
},
{field: 'username', title: 'Cashier No', width: 120, align: "center"},
, {title: 'operation',width:80, toolbar: '#currentTableBar', align: "center"}
]],
It can be seen that the field in cols is not the field name, but the subsequent object name,
For example, object 1 Object 2 Object 3 Property, the field writes object 3 and the mapped data is
Object 3 Attribute data corresponding to the attribute. You can also see that you need to add a function to return data.