MyBatis: collection via annotation in one request
I have an xml mapper - one choice and one result. It works seamlessly. But I want to use annotations. My cartographer:
<resultMap id="readItemsRM" type="com.company.Item">
<id property="id" column="Id"/>
<result property="comment" column="Comment"/>
<collection property="textItems" ofType="com.company.TextItem">
<id property="id" column="TxId"/>
<result property="name" column="TxName"/>
</collection>
</resultMap>
So I liked this
@Results({
@Result(id=true, property="id", column="Id"),
@Result(property="comment", column="Comment"),
///,???
})
public List<Item> select();
I can't figure out how I can map my collection using annotation without doing another SQL query. As all examples I've found assume doing another query. Please, help.
AFAIK, you cannot use JOIN
if you are using an annotated mapping.
From the document on use @Many
,
Mapping to a property of a complex type collection. Attributes: select, which is the fully qualified name of the matched operator (i.e., the mapping method) that can load a collection of instances of the matching types, fetchType, which replaces the lazyLoadingEnabled global config parameter for this mapping. NOTE You will notice that attaching a mapping is not supported via API annotations. This is due to a limitation in Java annotations that does not allow circular references.
You can directly use ResultMap with annotations if you like:
@Select("SELECT QUERY")
@ResultMap("readItemsRM")
public List<Item> select();
source to share
I found out that you can actually do one-to-many or one-to-one using Java annotations in MyBatis
public class Master {
private String nama;
private Short usia;
private List<Contoh> contohs;
}
public interface MasterMapper {
@Select("SELECT master.nama, master.usia FROM test.master WHERE master.nama = #{nama}")
@Results(value = {
@Result(property="nama", column="nama"),
@Result(property="usia", column="usia"),
@Result(property="contohs", javaType=List.class, column="nama",
many=@Many(select="getContohs"))
})
Master selectUsingAnnotations(String nama);
@Select("SELECT contoh.id, contoh.nama, contoh.alamat "
+ " FROM test.contoh WHERE contoh.nama = #{nama}")
List<Contoh> getContohs(String nama);
}
source to share
Internal queries using the ibatis annotation example.
@Select("SELECT t1.column1, t1.column2, t2.column3, t2.column4 "
+ "FROM table1 AS t1 "
+ "INNER JOIN table2 AS t2 "
+ "ON (t1.column1 = t2.column3) WHERE t1.column1=#{id}")
@Results(value = {
@Result(property = "val1", column = "column1"),
@Result(property = "val2", column = "column2"),
@Result(property = "val3"'enter code here', column = "column3"),
@Result(property = "val4", column = "column4")})
MyBean getInnerJoinResult(@Param("id") Integer id);
source to share
I worked for me checking the link that @feco, but my table structure is slightly different, so the example shows the correspondence between the main table and the list, let's check the code.
CREATE TABLE IF NOT EXISTS item_service (
id int(11) NOT NULL AUTO_INCREMENT,
description varchar(200) NOT NULL DEFAULT '0'
service_id int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
CONSTRAINT fk1_item_service_service FOREIGN KEY (service_id) REFERENCES service (id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS service (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(200) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
package com.****;
import org.hibernate.annotations.OnDelete;
import org.hibernate.annotations.OnDeleteAction;
@Data
@Entity
@Table(name = "item_service")
public class Item {
/*
Each item has a service linked to the Service class.
Each Service is not mandatory has items. But, the service could have many items asociated. it depends the JPA Aproach that you have.
*/
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id", updatable = false, nullable = false) //could be redundant, doesn matter for the example
private Long id;
@Column(name = "description"),
private String description;
@ManyToOne
@JoinColumn(name = "service_id", nullable = false)
@OnDelete(action = OnDeleteAction.CASCADE)
private Service service;
/*getters and setters*/
}
package com.***;
@Data
@Entity
@Table(name = "item_service")
public class Service {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column
private String name;
/*getters and setter*/
}
@Mapper
public interface ItemServiceMapper {
@Select("SELECT * FROM item_service")
@Results(value = {
@Result(column = "service_id", property = "service", javaType = Service.class, one = @One(select = "getService")),
@Result(column = "receipt_id", property = "receipt", javaType = Receipt.class, one = @One(select = "getReceipt")),
})
List<ItemService> findAllall();
@Select("SELECT * FROM service WHERE id= #{id}")
Service getService(@Param("id") Long id);
@Select("SELECT * FROM service WHERE id= #{id}")
Receipt getReceipt(@Param("id") Long id)
}
source to share