请问一下Ibatis3/Mybatis该如何进行一对一联合查询且不出现N+1问题
我的两个表：
- SQL code
CREATE TABLE `t_customers` ( `customer_id` int(32) NOT NULL auto_increment, `customer_name` varchar(50) default NULL, `customer_address` varchar(200) default NULL, PRIMARY KEY (`customer_id`))CREATE TABLE `t_orders` ( `ORDER_ID` int(32) NOT NULL auto_increment, `ORDER_NUMBER` varchar(32) default NULL, `ORDER_DATE` date default NULL, `ORDER_TOTAL` double(10,2) default NULL, `PAYMENT_STATE` int(1) default NULL, `SEND_STATE` int(1) default NULL, `ORDER_CUSTOMER_ID` int(32) default NULL, PRIMARY KEY (`ORDER_ID`), KEY `fk_orders_customers` (`ORDER_CUSTOMER_ID`), CONSTRAINT `fk_orders_customers` FOREIGN KEY (`ORDER_CUSTOMER_ID`) REFERENCES `t_customers` (`customer_id`)-- 每一个Order(订单)对应一个Customer(客户)，t_customer的主键是t_orders的一个外键)
Order.java:
- Java code
package com.yuwen.domain;import java.math.BigDecimal;import java.util.Date;public class Order { private long orderId; private String orderNumber; private Date orderDate; private BigDecimal orderTotal; private int paymentState; private int sendState; private Customer customer; public Customer getCustomer() { return customer; } public void setCustomer(Customer customer) { this.customer = customer; } public long getOrderId() { return orderId; } public void setOrderId(long orderId) { this.orderId = orderId; } public String getOrderNumber() { return orderNumber; } public void setOrderNumber(String orderNumber) { this.orderNumber = orderNumber; } public Date getOrderDate() { return orderDate; } public void setOrderDate(Date orderDate) { this.orderDate = orderDate; } public BigDecimal getOrderTotal() { return orderTotal; } public void setOrderTotal(BigDecimal orderTotal) { this.orderTotal = orderTotal; } public int getPaymentState() { return paymentState; } public void setPaymentState(int paymentState) { this.paymentState = paymentState; } public int getSendState() { return sendState; } public void setSendState(int sendState) { this.sendState = sendState; } }Customer.java:
- Java code
package com.yuwen.domain;public class Customer { private long customerId; private String customerName; private String customerAddress; public long getCustomerId() { return customerId; } public void setCustomerId(long customerId) { this.customerId = customerId; } public String getCustomerName() { return customerName; } public void setCustomerName(String customerName) { this.customerName = customerName; } public String getCustomerAddress() { return customerAddress; } public void setCustomerAddress(String customerAddress) { this.customerAddress = customerAddress; } }dao-configuration.xml:
- XML code
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-config.dtd"> <configuration> <properties resource="properties/config.properties"/> <settings> <setting name="lazyLoadingEnabled" value="true"/> </settings> <typeAliases> <typeAlias type="com.yuwen.domain.Order" alias="order" /> <typeAlias type="com.yuwen.domain.Customer" alias="customer"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}" /> <property name="url" value="${url}" /> <property name="username" value="${username}" /> <property name="password" value="${password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="com/yuwen/domain/Order.xml" /> </mappers> </configuration>
我认为问题就出在这的Order.xml：
- XML code
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd"> <mapper namespace="com.yuwen.dao.OrderssDao"> <resultMap type="customer" id="customerMapper"> <id property="customerId" column="customer_id" javaType="long" jdbcType="INTEGER"/> <result property="customerName" column="customer_name" javaType="java.lang.String" jdbcType="VARCHAR"/> <result property="customerAddress" column="customer_address" javaType="java.lang.String" jdbcType="VARCHAR"/> </resultMap> <resultMap type="order" id="orderMapper"> <id property="orderId" column="ORDER_ID" javaType="long" jdbcType="INTEGER"/> <result column="ORDER_NUMBER" property="orderNumber" javaType="java.lang.String" jdbcType="VARCHAR"/> <result column="ORDER_DATE" property="orderDate" javaType="java.util.Date" jdbcType="DATE"/> <result column="ORDER_TOTAL" property="orderTotal" javaType="java.math.BigDecimal" jdbcType="INTEGER"/> <result column="PAYMENT_STATE" property="paymentState" javaType="int" jdbcType="INTEGER"/> <result column="SEND_STATE" property="sendState" javaType="int" jdbcType="INTEGER"/> <association property="customer" column="ORDER_CUSTOMER_ID" javaType="customer" resultMap="customerMapper" /> </resultMap> <select id="selectOrdersList" resultMap="orderMapper" > select c.customer_id as customerId, c.customer_name as customerName, o.ORDER_ID as orderId, o.ORDER_NUMBER as orderNumber, o.ORDER_DATE as orderDate, o.ORDER_TOTAL as orderTotal, o.PAYMENT_STATE as paymentState, o.SEND_STATE as sendState from t_orders o,t_customers c where o.ORDER_CUSTOMER_ID=c.customer_id </select></mapper>
然后再创建SqlSession调用selectList("selectOrdersList")方法，我就不贴代码了，返回一个List<Order>,但是返回的数据我通过log4g的日志可以看到：返回的结果正常:
3:44:01,015 DEBUG ResultSet:27 - <== Columns: customerId, customerName, orderId, orderNumber, orderDate, orderTotal, paymentState, sendState
13:44:01,031 DEBUG ResultSet:27 - <== Row: 1, 张三, 1, 201009013-A00001, 2010-09-13, 500.0, 1, 0
13:44:01,031 DEBUG ResultSet:27 - <== Row: 1, 张三, 6, 2010-0003, 2010-09-13, 655.0, 0, 1
13:44:01,031 DEBUG ResultSet:27 - <== Row: 2, 李四, 7, 2010-0004, 2010-09-13, 700.66, 1, 0
13:44:01,031 DEBUG ResultSet:27 - <== Row: 2, 李四, 8, 101009015-a00002, 2010-09-13, 800.0, 1, 1
---------------------------------
我的数据库里t_orders有四条记录，t_customers有2两条记录,从log4j的日志中可以看出查询数据和返回数据都很正常，而且也没有N+1问题，但是返回的List<Order>中的每个元素Order，只包括orderId,orderNumber,orderDate,orderTotal,paymentState,sendState;而并不包括customer属性，这样我就无法把张三和李四的名字通过order.getCustomer.getCustomerName()取出，因为order.getCustomer是Null值，请问我该怎么办？我实在没有分了，望不吝指教！谢谢！
[解决办法]
order.getCustomer是Null值,原因如下：
<resultMap type="customer" id="customerMapper">
<id property="customerId" column="customer_id" javaType="long" jdbcType="INTEGER"/>
<result property="customerName" column="customer_name" javaType="java.lang.String" jdbcType="VARCHAR"/>
<result property="customerAddress" column="customer_address" javaType="java.lang.String" jdbcType="VARCHAR"/>
</resultMap>
<select id="selectOrdersList" resultMap="orderMapper" >
select
c.customer_id as customerId,
c.customer_name as customerName,
o.ORDER_ID as orderId,
o.ORDER_NUMBER as orderNumber,
o.ORDER_DATE as orderDate,
o.ORDER_TOTAL as orderTotal,
o.PAYMENT_STATE as paymentState,
o.SEND_STATE as sendState
from t_orders o,t_customers c
where o.ORDER_CUSTOMER_ID=c.customer_id
</select>
[color=#FF0000][/color]
红色部分标出了不匹配之处，根源是column的值要与sql语句里别名一致，不是与数据库表的列名一致，另外customer_address在查询语句里未出现，就没有构成customer的信息。