mybatis配置一对多查询关系

1,部门bean

package com.hsk.mybatis.bean;

import java.util.List;

public class DeptBean {

	private String deptCode;
	private String deptName;
	private String cmpyCode;
	private List<UserBean> userList;
	public List<UserBean> getUserList() {
		return userList;
	}
	public void setUserList(List<UserBean> userList) {
		this.userList = userList;
	}
	public String getDeptCode() {
		return deptCode;
	}
	public void setDeptCode(String deptCode) {
		this.deptCode = deptCode;
	}
	public String getDeptName() {
		return deptName;
	}
	public void setDeptName(String deptName) {
		this.deptName = deptName;
	}
	public String getCmpyCode() {
		return cmpyCode;
	}
	public void setCmpyCode(String cmpyCode) {
		this.cmpyCode = cmpyCode;
	}
	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return deptName+"["+deptCode+"]"+cmpyCode+"["+userList.size()+"]";
	}
}

2,用户bean

package com.hsk.mybatis.bean;

/**
 * 用户bean
 * @author huangshikai
 *
 */
public class UserBean {

	private String userCode;
	private String userName;
	private String deptCode;
	private String cmpyCode;
	private DeptBean deptBean;
	public DeptBean getDeptBean() {
		return deptBean;
	}
	public void setDeptBean(DeptBean deptBean) {
		this.deptBean = deptBean;
	}
	public String getCmpyCode() {
		return cmpyCode;
	}
	public void setCmpyCode(String cmpyCode) {
		this.cmpyCode = cmpyCode;
	}
	public String getUserCode() {
		return userCode;
	}
	public void setUserCode(String userCode) {
		this.userCode = userCode;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getDeptCode() {
		return deptCode;
	}
	public void setDeptCode(String deptCode) {
		this.deptCode = deptCode;
	}
	@Override
	public String toString() {
		return "UserBean [userCode=" + userCode + ", userName=" + userName + ", deptCode=" + deptCode + "]";
	}
}

3,deptMapper.xml的一堆多配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace命名空间对sql分类化管理,隔离不同表的sql语句 -->
<mapper namespace="com.hsk.mybatis.dao.DeptDao">

	<!-- 配置结果集字段和javabean字段对应关系 -->
	<resultMap id="deptMap" type="com.hsk.mybatis.bean.DeptBean">
        <id property="deptCode" column="DEPT_CODE" />
        <result property="deptName" column="DEPT_NAME"/>
        <result property="cmpyCode" column="CMPY_CODE"/>
        <!-- 集合的column应为deptbean传入的关联查询字段 (关联查询配置一对多)-->
        <collection property="userList" ofType="com.hsk.mybatis.bean.UserBean"
        	javaType="java.util.List" column="DEPT_CODE">
        	<id property="userCode" column="USER_CODE"></id>
        	<result property="deptCode" column="DEPTCODE"/><!-- 连接查询的字段别名 -->
        	<result property="cmpyCode" column="CMPYCODE"/>
        	<result property="userName" column="USER_NAME"/>
        </collection>
    </resultMap>
    
    <!-- 配置结果集字段和javabean字段对应关系 -->
    <!-- 集合的column应为deptbean传入的关联查询字段 (子查询配置一对多)-->
    
	<resultMap id="deptMapSonQuery" type="com.hsk.mybatis.bean.DeptBean">
        <id property="deptCode" column="DEPT_CODE" />
        <result property="deptName" column="DEPT_NAME"/>
        <result property="cmpyCode" column="CMPY_CODE"/>
        
        <collection property="userList" ofType="com.hsk.mybatis.bean.UserBean"
        	javaType="java.util.List" column="DEPT_CODE" select="com.hsk.mybatis.dao.DeptDao.getUsersByCode">
        </collection>
    </resultMap>
    
    
	<!--
	 统一命名空间下id唯一 
	parameterType输入参数类型可以是java简单的数据类型也可以是java对象,这里指定string
	#{}表示占位符
	#{userCode}其中userCode为输入参数,参数名称为userCode
	resultMap指定sql输出结果所映射的java对象,注意resultMap要配置java对象属性与数据库字段对应关系
	resultType指定sql输出结果单条记录所映射的java对象,注意resultType配置的java类的属性需要与数据库字段名称一致
	-->
   <select id="getDeptByCode" parameterType="String" resultMap="deptMap">
       SELECT a.DEPT_CODE,DEPT_NAME,a.CMPY_CODE,
       b.dept_code DEPTCODE,b.USER_NAME,b.CMPY_CODE CMPYCODE,
       b.USER_CODE FROM SY_ORG_DEPT a
       left join SY_ORG_USER b on a.dept_code=b.dept_code
        WHERE a.DEPT_CODE=#{deptCode}
   </select>
   
   <!-- 子查询的一对多查询 -->
   <select id="getDeptUsers" parameterType="String" resultMap="deptMapSonQuery">
   	SELECT DEPT_CODE,DEPT_NAME,CMPY_CODE FROM SY_ORG_DEPT
   	WHERE DEPT_CODE=#{deptCode}
   </select>
   
   <!-- 注意子查询的返回结果应为resultMap而不是resultType否则返回的结果集的对象都为NULL -->
   <select id="getUsersByCode" parameterType="String" resultMap="com.hsk.mybatis.mapper.UserMapper.userMap">
   	SELECT USER_CODE,DEPT_CODE,CMPY_CODE,USER_NAME FROM SY_ORG_USER
   	WHERE DEPT_CODE=#{deptCode}
   </select>
   
   <!-- 
   	根据用户名称查找对应的用户集合
   	${}表示拼接sql串,将传参不加修饰的拼接在sql中,value表示简单类型的传参值
   	${}会引起sql注入,如传入 ' OR 1=1 OR '
   	#{}占位符,{}中可以写value或者其他值。根据传参类型修饰传参后拼接在sql中,如传入整型结果就是a=1,字符串就是a='1'
   	${value}:接收输入的传参内容,如果是简单的传参类型则${}中只能用value
   	#{},${}通过OGNL读取对象的属性值,通过  属性.属性.属性.....方式获取对象属性值
   	一般不推荐使用${}方式获取属性值
      注意:#{}会将传参拼接程指定的sql语法如a=#{name}会被转换成a='zhangda'
      ${}会将传参原封不动的塞进sql语句如a=${name}会被转换成a=zhangda
    -->
   <select id="getDeptByName" parameterType="String" resultType="com.hsk.mybatis.bean.DeptBean">
   		SELECT DEPT_NAME,DEPT_CODE,CMPY_CODE FROM SY_ORG_DEPT WHERE DEPT_NAME LIKE '%${_parameter}%'
   </select>
   
   <!-- 
   parameterType为pojo(java对象)类型参数
   #{}占位符中指定pojo属性,接收到pojo对象属性值后mybatis通过OGNL获取对象属性值
    -->
   <insert id="addDept" parameterType="com.hsk.mybatis.bean.DeptBean">
   	    insert into sy_org_dept (dept_code,dept_name,CMPY_CODE) values (#{deptCode},#{deptName},#{cmpyCode})
   </insert>
   
   <delete id="updateDept" parameterType="com.hsk.mybatis.bean.DeptBean">
   	   update sy_org_dept set dept_name=#{deptName} where dept_code=#{deptCode}
   </delete>
</mapper>

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
假设我们有两个表,一个是订单表(order),另一个是订单详情表(order_item),订单表和订单详情表是一对多关系(即一个订单可以对应多个订单详情),下面是使用MyBatis实现一对多查询的代码示例。 1. 定义Order类和OrderItem类 ```java public class Order { private Integer id; private String orderNo; private List<OrderItem> orderItemList; // 省略getter和setter方法 } public class OrderItem { private Integer id; private Integer orderId; private String productName; private Integer quantity; // 省略getter和setter方法 } ``` 2. 编写OrderMapper.xml文件 ```xml <?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.example.dao.OrderMapper"> <!-- 根据订单ID查询订单详情 --> <select id="findOrderDetailsByOrderId" resultMap="orderResultMap"> SELECT o.*, oi.id AS oi_id, oi.product_name, oi.quantity FROM `order` o LEFT JOIN order_item oi ON o.id = oi.order_id WHERE o.id = #{orderId} </select> <resultMap id="orderResultMap" type="com.example.entity.Order"> <!-- 订单信息映射 --> <id property="id" column="id"/> <result property="orderNo" column="order_no"/> <!-- 订单详情映射 --> <collection property="orderItemList" ofType="com.example.entity.OrderItem"> <id property="id" column="oi_id"/> <result property="productName" column="product_name"/> <result property="quantity" column="quantity"/> </collection> </resultMap> </mapper> ``` 3. 编写OrderMapper接口 ```java public interface OrderMapper { List<Order> findOrderDetailsByOrderId(Integer orderId); } ``` 4. 在Spring配置文件中配置OrderMapper ```xml <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource"/> <property name="typeAliasesPackage" value="com.example.entity"/> <property name="mapperLocations"> <array> <value>classpath*:mapper/*.xml</value> </array> </property> </bean> <bean id="orderMapper" class="org.mybatis.spring.mapper.MapperFactoryBean"> <property name="mapperInterface" value="com.example.dao.OrderMapper"/> <property name="sqlSessionFactory" ref="sqlSessionFactory"/> </bean> ``` 5. 调用OrderMapper接口方法进行一对多查询 ```java @Autowired private OrderMapper orderMapper; public void testFindOrderDetailsByOrderId() { Integer orderId = 1; List<Order> orderList = orderMapper.findOrderDetailsByOrderId(orderId); for (Order order : orderList) { System.out.println(order.getOrderNo()); List<OrderItem> orderItemList = order.getOrderItemList(); for (OrderItem orderItem : orderItemList) { System.out.println(orderItem.getProductName() + ", " + orderItem.getQuantity()); } } } ``` 通过以上步骤,我们就可以实现使用MyBatis进行一对多查询了。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值