mysql - mybatis select statement problem

Question

Message reminder continued, this message may be that someone else directly replied to your article. At this time, you need to perform database operations to associate the article table to obtain the content of the corresponding article [Message reminder: There is a new reply to your article xxx], or it may be Someone else has replied to your comment. At this time, the comment table is associated to obtain the content of the comment [Message reminder: There is a new reply to your comment xxx]. After clicking on the message, the details will appear like this.

The database table structure is as follows

mbelongbid is the id of the article to which the message belongs, and mbelongcid is the id of the comment to which the message belongs.
When mbelongcid is empty, it means that the message is a direct reply to the article, and the article table is associated at this time;
When mbelongcid is not empty, it means that the message reply object is a certain comment, and the comment table is associated at this time.

How to write sql statements to meet this requirement?
My current thoughts are:

select

r.*, 
<if test="mbelongcid == null">`blog`.btitle</if>
<if test="mbelongcid != null">`comment`.ccontent</if>

from
(

select 
    mid, mreferuid, mbelongbid, mbelongcid
from 
    message
where mid = #{_parameter}

)r,
<if test="mbelongcid == null">

`blog` where r.mbelongbid = `blog`.bid

</if>
<if test="mbelongcid != null">

`comment` where r.mbelongcid = `comment`.cid

</if>

There is a problem with writing it directly like this. The general idea is to associate different tables to obtain different fields based on whether mbelongcid is null. Are there any good solutions or suggestions?

Answer 1

1. mbelongcid is not part of the parameters you passed in, so mybatis does not know whether it is null! , if you want to realize the logic you want, you should start from the database side, such as creating a view, which is composed of the union of two queries.

select mid, mreferuid, 'blog' as type, mbelongbid as rid
from message m, blog b
where mbelongcid  is null and mbelongbid is not null and mbelongbid = b.bid
union
select mid, mreferuid, 'comment' as type, mbelongcid as rid
from message m, comment c
where mbelongcid is not null and mbelongcid = c.cid

1. When you encounter this kind of strange syntax when writing a program, please review the design plan first. Usually the reason is that there is a problem with the design.

2. Who designed the data sheet? At least add an underscore when deducting wages. m_belong_cid, student party members, take your time.

Answer 2

Our project uses annotated SQL. When encountering this kind of situation, we directly use SQL in the Provider to solve it.

Answer 3

The statements in this part of MessageMapper.xml are as follows:

message class is as follows: