CODEWITHSUNDEEP
mybatis
lapots
lapots

Reputation: 13395

resultMap for complex query

I've got some query which returns data that does not have any object for it.

 SELECT  b.id,
            b.publisher_id,
            b.name,
            b.size,
            b.present,
            CASE WHEN b.id in (SELECT book_id FROM downloads) 
              THEN true
              ELSE false
            END as downloading,
            b.display,
            b.download_date
     FROM books as b
     WHERE b.publisher_id = ${pId} AND b.display = true
     LIMIT ${pageSize} OFFSET ${startId}

Field downloading does not have any column in database. The mapper for object b is 

<resultMap id="bookMap" type="Book">
    <id column="id" property="id" />
    <result column="publisher_id" property="publisherId" />
    <result column="name" property="name" />
    <result column="size" property="size" />
    <result column="present" property="present" />
    <result column="display" property="display" />
    <result column="download_date" property="downloadDate" />
</resultMap>

How to create mapper for such object - where part is already existed resultMap but added new column.

Upvotes: 0

Views: 974

Answers (1)

Roman-Stop RU aggression in UA
Roman-Stop RU aggression in UA

Reputation: 15851

One of the way is to create new class which will contain book with downloading status:

class BookWithDownloadingStatus {
    private Book book;
    private boolean downloading;
    // I've omitted setters and getters
}

After that you can use this class to map result of the above query:

<resultMap id="bookWithDownloadingStatusMap" type="BookWithDownloadingStatus">
    <result column="downloading" property="downloading"/>
    <association property="book" resultMap="bookMap"/>
</resultMap>

<select id="getBookWithDownloadingStatus" resultMap="bookWithDownloadingStatusMap">
    -- the above select
</select>

By the way, your query will not perform paging correctly. Database doesn't guarantee any order and without order limits and offsets are mostly useless. You should always specify ordering otherwise items may be distributed randomly between pages.

Upvotes: 2

Related Questions