A set of Mybaits result sets, a solution for nested List in Javabean, and a solution for mybaitsjavabean
In such a scenario, as a writer, I have written many articles. If I abstract myself into an object, so how can I get my articles written by Mybatis? In this case, the set of Mybatis result sets can be used to meet the requirements.
In my project, you need to obtain the corresponding product from the market.
The commodity and market Javabean are roughly as follows:
Markets. java
public class Markets extends DataEntity<Markets> {
private Integer id;
private String marketname;
private String marketcode;
private Integer market_weight;
private List<Goods> goods;
......
Goods. java
public class Goods extends DataEntity<Goods> {
private Long id;
private Integer market_id;
private String serial_num;
private String name;
Define its alias in the mybatis-config.xml as follows:
<typeAlias alias='Markets' type='com.cmower.database.entity.Markets' />
<typeAlias alias='Goods' type='com.cmower.database.entity.Goods' />
Define a resultMap in MarketMapper. xml. Its content is roughly as follows:
<resultMap type = "Markets" id = "BaseGoodMarketResultMap">
<id column = "id" property = "id" />
<result column = "marketcode" property = "marketcode" />
<result column = "marketname" property = "marketname" />
<result column = "market_weight" property = "market_weight" />
<result column = "market_type" property = "market_type" />
<!-This sentence is especially important, its role is to map the result set taken out by selectGoodsForMarket to the goods attribute in the Javabean of Markets->
<collection property = "goods" column = "id" ofType = "Goods" select = "selectGoodsForMarket" />
</ resultMap>
<!-Where the id in market_id = # (id) is the value of the column attribute passed by <collection>->
<select id = "selectGoodsForMarket" resultType = "Goods">
SELECT
g.id,
g.name,
g.price_str,
g.goods_thumb
FROM
ym_goods g
where g.del_flag = 0 and g.market_id = # {id}
order by g.id DESC
limit 6
</ select>
<select id = "selectGoodMakets" resultMap = "BaseGoodMarketResultMap">
select
m. *
from
market m
where
m.del_flag = 0 and m.market_type = 0
</ select>
The preceding content is a method of writing the set of Mybaits results. When selectGoodMakets is called for query, the returned result set is BaseGoodMarketResultMap, which is defined in BaseGoodMarketResultMap.<collection property="goods" column="id" ofType="Goods" select="selectGoodsForMarket"/>It indicates that a Goods set list to be retrieved from selectGoodsForMarket is returned to the goods attribute in the Javabean of Markets.
The SQL Execution sequence is as follows:
That is to say, selectGoodMakets is executed first, and then selectGoodsForMarket is executed three times in sequence.
This achieves our expected results, but if there are many selectGoodMakets result sets, selectGoodsForMarket will execute many times. Is there a better way, what if I run only one SQL statement to get the expected result set?
If yes, but the results subresults cannot be limit, this is not the result I want (the Stack Overflow does not find the desired answer), but it is just learning.
<resultMap type="Markets" id="BaseGoodMarketResultMap1">
<id column="id" property="id" />
<result column="marketcode" property="marketcode" />
<result column="marketname" property="marketname" />
<result column="market_weight" property="market_weight" />
<result column="market_type" property="market_type" />
<collection property="goods" ofType="Goods">
<id column="good_id" property="id" />
<result column="name" property="name" />
<result column="price_str" property="price_str" />
<result column="is_promote" property="is_promote" />
<result column="issue_price" property="issue_price" />
<result column="max_point" property="max_point" />
<result column="back_point" property="back_point" />
<result column="can_use_point" property="can_use_point" />
<result column="goods_thumb" property="goods_thumb" />
</collection>
</resultMap>
<select id="selectGoodMaketsOneSql" resultMap="BaseGoodMarketResultMap1">
select
g.id as good_id,
g.name,
g.price_str,
g.is_promote,
g.issue_price,
g.max_point,
g.back_point,
g.can_use_point,
g.goods_thumb,
m.*
from
market m
left join ym_goods g on m.id=g.market_id and g.del_flag = 0 and g.status =3 and g.is_onsale=1
where
m.del_flag = 0 and m.market_type = 0
</select>
The execution result is as follows:
It is an SQL statement. But there is no limit in the good table. I don't know how to obtain the first six items in the good table? Who has a good solution?
Getting up early every day is the greatest responsibility for life!
Copyright Disclaimer: This article is from the blog of silence Wang Er. The source must be indicated. Technical Exchange Group 120926808 http://blog.csdn.net/qing_gee/article/details/79214721