频道栏目
首页 > 程序开发 > 软件开发 > 其他 > 正文
mybatis resaultMap 高级映射 (一对一,一对多,多对一,多对多)
2016-08-20 09:30:28         来源:J_Bang的博客  
收藏   我要投稿

一:resaultMap应场景

如果sql查询的列名和pojo的属性名不一致,可以使用resultMap将列名和pojo的属性名作一个对应关系,就可以映射成功了.
使用resultMap可以完成一些高级映射:将一些列聚合到一个pojo的对象中,完成一对一查询映射。将多行多列聚合到一个List中,完成一对多查询映射。

 


	 
	    
	 	
	 	
	 	
	 	
	 

	

二:mybatis高级映射

表结构:

1.sql语句:

 

CREATE TABLE `items` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) NOT NULL COMMENT '商品名称',
  `price` FLOAT(10,1) NOT NULL COMMENT '商品定价',
  `detail` TEXT COMMENT '商品描述',
  `pic` VARCHAR(64) DEFAULT NULL COMMENT '商品图片',
  `createtime` DATETIME NOT NULL COMMENT '生产日期',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Table structure for table `orderdetail` */

CREATE TABLE `orderdetail` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `orders_id` INT(11) NOT NULL COMMENT '订单id',
  `items_id` INT(11) NOT NULL COMMENT '商品id',
  `items_num` INT(11) DEFAULT NULL COMMENT '商品购买数量',
  PRIMARY KEY (`id`),
  KEY `FK_orderdetail_1` (`orders_id`),
  KEY `FK_orderdetail_2` (`items_id`),
  CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`orders_id`)
	 REFERENCES `orders` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`items_id`) 
	REFERENCES `items` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

/*Table structure for table `orders` */

CREATE TABLE `orders` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL COMMENT '下单用户id',
  `number` VARCHAR(32) NOT NULL COMMENT '订单号',
  `createtime` DATETIME NOT NULL COMMENT '创建订单时间',
  `note` VARCHAR(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),
  CONSTRAINT `FK_orders_id` FOREIGN KEY (`user_id`) 
	REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Table structure for table `user` */

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(32) NOT NULL COMMENT '用户名称',
  `birthday` DATE DEFAULT NULL COMMENT '生日',
  `sex` CHAR(1) DEFAULT NULL COMMENT '性别',`orderdetail`
  `address` VARCHAR(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

2.表关系图

 

\

二:一对一

 

1:resaultType

 

Mapper.xml

实体类(使用resultType,保证指定的pojo中属性和sql查询列一一对应,因为原来的orders中没有用户信息的字段,需要定义一个扩展的pojo包括 所有字段,一般情况建议对扩展的pojo采取自定义方式):

 

 

//对扩展对象,可以采用继承方式 ,让他继承拥有字段多的pojo
public class OrdersCustom extends Orders {	
	//包括 用户信息
	private String username;
	private String sex;
	private String address;
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
}
OrdersMapper.java

 

 

	//查询订单关联查询用户
	public List findOrdersAndUserList() throws Exception;
测试类:

 

 

public class OrdersMapperTest {
	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void setUp() throws Exception {
		// 创建会话工厂
		// 全局配置文件
		String resource = "SqlMapConfig.xml";
		// 创建配置文件的流
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 根据配置文件创建sqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder()
				.build(inputStream);
	}
	@Test
	public void testfindOrdersAndUserList() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//创建代理对象
		OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
		//调用ordersMapper方法		
		List list = ordersMapper.findOrdersAndUserList();
		System.out.println(list);		
		sqlSession.close();
	}	
}

 

 

 

2:resaultMap

 

Mapper.xml(一对一用

 

	
	
		
		
		
		
		
		
		
		
		
		 
		 	
		 	
		 	
		 	
		 	
		 			 
		 
	
	
	
	
实体类:(因为要映射User对象,因此Orders中要加入User对象)

 

 

public class Orders implements Serializable {
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;   
    //用户信息   
    private User user;
   //订单明细
    private List orderdetails;

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

   public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number == null ? null : number.trim();
    }
    public Date getCreatetime() {
        return createtime;
    }
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
    public String getNote() {
        return note;
    }
    public void setNote(String note) {
        this.note = note == null ? null : note.trim();
    }
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public List getOrderdetails() {
		return orderdetails;
	}
	public void setOrderdetails(List orderdetails) {
		this.orderdetails = orderdetails;
	}
  
}

 

OrdersMapper.java:

 

//一对一使用reusltMap
	public List findOrdersAndUserListResultMap() throws Exception;
测试类:

 

 

public class OrdersMapperTest {

	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void setUp() throws Exception {
		// 创建会话工厂
		// 全局配置文件
		String resource = "SqlMapConfig.xml";
		// 创建配置文件的流
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 根据配置文件创建sqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder()
				.build(inputStream);
	}
	
	@Test
	public void testfindOrdersAndUserListResultMap() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//创建代理对象
		OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
		//调用ordersMapper方法
		
		List list = ordersMapper.findOrdersAndUserListResultMap();
		System.out.println(list);
		
		sqlSession.close();
	}

}

 

 

三:一对多

 

1.Mapper.xml(一对多用Collection)

 

 

	
	
	extends="OrdersAndUserListResultMap">
		
	
		
			
			
			
			
					
				
	

	
通过继承前面写的resault省略部分代码

 

2.实体类(orders中添加List 属性)

 

 

public class Orders implements Serializable {
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;   
    //用户信息   
    private User user;
   //订单明细
    private List orderdetails;

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

   public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number == null ? null : number.trim();
    }
    public Date getCreatetime() {
        return createtime;
    }
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
    public String getNote() {
        return note;
    }
    public void setNote(String note) {
        this.note = note == null ? null : note.trim();
    }
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public List getOrderdetails() {
		return orderdetails;
	}
	public void setOrderdetails(List orderdetails) {
		this.orderdetails = orderdetails;
	}
  
}

 

3.OrdersMapper.java:

 

 

//一对多
	public List findOrdersAndDetailList() throws Exception;

 

4.测试类

 

 

public class OrdersMapperTest {

	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void setUp() throws Exception {
		// 创建会话工厂
		// 全局配置文件
		String resource = "SqlMapConfig.xml";
		// 创建配置文件的流
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 根据配置文件创建sqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder()
				.build(inputStream);
	}

	//一对多
	@Test
	public void testfindOrdersAndDetailList() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//创建代理对象
		OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
		//调用ordersMapper方法
		
		List list = ordersMapper.findOrdersAndDetailList();
		System.out.println(list);
		
		sqlSession.close();
	}
	
}

 

 

四:多对多

 

需求:

 

将用户信息映射到user对象中,最终所有用户生成一个List

 

用户创建的订单映射到user对象的List属性中

 

将订单所包括订单明细映射到orders中的List属性中。

 

将商品信息映射到orderdetail对象 的Item属性中

 

1.Mapper.xml

 

 


	
		
		
		
	 	
	 	
		
		
		 
		 	
		 	
			
			
			
			
			
				
			
				
				
			
				
				
						
				 
				 	
				 	
				 	
				 	
				 
			
			
		 
		
	

	

 

2.实体类(User中添加List ,Order 中添加User和List ,OrderDetail添加Items)

 

a:User

 

public class User implements Serializable {
	private int id;
	private String username;// 用户姓名
	private String sex;// 性别
	private Date birthday;// 生日
	private String address;// 地址
	
	//订单列表
	private List orders;
	
	
	public List getOrders() {
		return orders;
	}
	public void setOrders(List orders) {
		this.orders = orders;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	
	
}
b:Orders

 

 

public class Orders implements Serializable {
    private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;   
    //用户信息   
    private User user;
   //订单明细
    private List orderdetails;

    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

   public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public String getNumber() {
        return number;
    }
    public void setNumber(String number) {
        this.number = number == null ? null : number.trim();
    }
    public Date getCreatetime() {
        return createtime;
    }
    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }
    public String getNote() {
        return note;
    }
    public void setNote(String note) {
        this.note = note == null ? null : note.trim();
    }
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public List getOrderdetails() {
		return orderdetails;
	}
	public void setOrderdetails(List orderdetails) {
		this.orderdetails = orderdetails;
	}
  
}
c:OrderDetail

 

 

public class Orderdetail {
    private Integer id;

    private Integer ordersId;

    private Integer itemsId;

    private Integer itemsNum;
    
    //商品信息
    private Items items;
    
    

    public Items getItems() {
		return items;
	}

	public void setItems(Items items) {
		this.items = items;
	}

	public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getOrdersId() {
        return ordersId;
    }

    public void setOrdersId(Integer ordersId) {
        this.ordersId = ordersId;
    }

    public Integer getItemsId() {
        return itemsId;
    }

    public void setItemsId(Integer itemsId) {
        this.itemsId = itemsId;
    }

    public Integer getItemsNum() {
        return itemsNum;
    }

    public void setItemsNum(Integer itemsNum) {
        this.itemsNum = itemsNum;
    }

    
}
d:Items

 

 

public class Items {
    private Integer id;

    private String name;

    private Float price;

    private String pic;

    private Date createtime;

    private String detail;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public Float getPrice() {
        return price;
    }

    public void setPrice(Float price) {
        this.price = price;
    }

    public String getPic() {
        return pic;
    }

    public void setPic(String pic) {
        this.pic = pic == null ? null : pic.trim();
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail == null ? null : detail.trim();
    }

}

 

3.OrdersMapper.java:

 

//多对多查询
	public List findUserAndItemsList()throws Exception;

 

4.测试类

 

public class OrdersMapperTest {

	private SqlSessionFactory sqlSessionFactory;

	@Before
	public void setUp() throws Exception {
		// 创建会话工厂
		// 全局配置文件
		String resource = "SqlMapConfig.xml";
		// 创建配置文件的流
		InputStream inputStream = Resources.getResourceAsStream(resource);

		// 根据配置文件创建sqlSessionFactory
		sqlSessionFactory = new SqlSessionFactoryBuilder()
				.build(inputStream);
	}
	
	//多对多查询
	@Test
	public void testfindUserAndItemsList() throws Exception {
		SqlSession sqlSession = sqlSessionFactory.openSession();
		//创建代理对象
		OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
		//调用ordersMapper方法
		
		List list = ordersMapper.findUserAndItemsList();
		System.out.println(list);
		
		sqlSession.close();
	}		
}

四:注意

并不是一对多、多对多的操作必须使用resaultMap才能和完成,resaultType一样可以完成这些操作,但是在特定的的需求下要是用resaultMap进行操作,例如规定某个对象映射到某个对象之中,这时候就必须要视同resaultMap了,开发时要根据具体需求去执行。

五:总结

resultMap:也是对查询结果集进行输出映射,根据自己需求可以通过resultMap将查询结果集映射到pojo中pojo属性中,还可以将多条记录结果集映射到pojo中List集合属性中。一对多、多对多、一对多这是针对业务来说的,可以写成符合一对一、一对多、多对多业务的sql语句,sql语句的结果集的映射由mybatis完成。写成一个复杂的多对多的sql语句,使用resultMap或resultType完成结果集映射。


点击复制链接 与好友分享!回本站首页
相关TAG标签 一对一
上一篇:聊聊从web session的共享到可扩展缓存设计
下一篇:设计模式之禅--抽象工厂模式
相关文章
图文推荐
文章
推荐
点击排行

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站