联合查询的问题，折腾了一天了

一个网站项目，网站有类别，类别是树状的，但目前只有两层；类别下可以有相应的产品。
下面两个是相应的VO：

public class CategoryModel implements Serializable
{
	private int id;

	private String name;
	
	private int parentCategoryId;

	private CategoryModel parentCategory;

	//省略相应的setter/getter方法
	......
}

public class SoftwareModel implements Serializable
{
	private int id;

	private String name;

	private CategoryModel category;

	//省略相应的setter/getter方法
	......
}

下面是iBatis的XML文件：

<sqlMapConfig>
	<settings
		cacheModelsEnabled="true"
		enhancementEnabled="true"
		lazyLoadingEnabled="true"
		useStatementNamespaces="true"/>

  	<sqlMap resource="com/demo/category/Category.xml"/>
  	<sqlMap resource="com/demo/software/Software.xml"/>
</sqlMapConfig>

Category.xml文件

<sqlMap namespace="Category">

	<typeAlias alias="category"
		type="com.demo.category.model.CategoryModel" />

	<cacheModel type="OSCACHE" id="category-cache">
		<flushInterval minutes="10" />
		<flushOnExecute statement="Category.addCartory"/>
	</cacheModel>

	<resultMap class="category" id="categoryResult">
		<result property="id" column="FID" javaType="int" jdbcType="int"
			nullValue="0" />
		<result property="name" column="FName" javaType="String"
			jdbcType="nvarchar" />
		<result property="parentCategoryId" column="FParentID" javaType="int" jdbcType="int"
			nullValue="0" />
		<!-- TODO:macconverter：如果放开这个配置会导致getSoftwareById抛异常，先暂时注释掉-->
		<result property="parentCategory" column="FParentID" select="Category.getCategoryById" />
	</resultMap>

	<!-- 根据位置ID取得位置 -->
	<select id="getCategoryById" resultMap="categoryResult"
		parameterClass="int" cacheModel="category-cache">
		SELECT * FROM t_categories 
		WHERE FID =#categoryId#
	</select>
</sqlMap>

Software.xml文件：

<sqlMap namespace="Software">

	<typeAlias alias="software"
		type="com.demo.software.model.SoftwareModel" />

	<cacheModel type="OSCACHE" id="software-cache">
		<flushInterval minutes="10" />
		<flushOnExecute statement="Software.addSoftware"/>
	</cacheModel>

	<resultMap class="software" id="softwareResult">
		<result property="id" column="FID" javaType="int" jdbcType="int"
			nullValue="0" />
		<result property="name" column="FName" javaType="String"
			jdbcType="nvarchar" />
		<result property="category" column="FCategoryID" select="Category.getCategoryById" />
	</resultMap>

	<select id="getSoftwareById" resultMap="softwareResult"
		parameterClass="int" cacheModel="software-cache">
		SELECT * FROM t_softwares 
		WHERE FID =#softwareId#
	</select>
	
	<select id="getSoftwareByName" resultMap="softwareResult"
		parameterClass="string" cacheModel="software-cache">
		SELECT * FROM t_softwares 
		WHERE FName =#name#
	</select>
</sqlMap>

通过Spring来调用iBatis，下面是Spring的配置文件：

<beans
	xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">
		
	<bean id="messageProperties" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
		<property name="locations">
			<list>
				<value>classpath:properties/cms.properties</value>
			</list>
		</property>
	</bean>	
	
	<bean id="cmsDbcpDataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName">
			<value>${cms.datasource.driverClassName}</value>
		</property>
		<property name="url">
			<value>${cms.datasource.url}</value>
		</property>
		<property name="username">
			<value>${cms.datasource.username}</value>
		</property>
		<property name="password">
			<value>${cms.datasource.password}</value>
		</property>
	</bean>

	<bean id="cmsSqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
		<property name="configLocation">
			<value>classpath:cms-sql-map-config.xml</value>
		</property>
		<property name="dataSource">
			<ref bean="cmsDbcpDataSource"/>
		</property>
	</bean>
	
	<bean id="cmsSqlMapClientTemplete" class="org.springframework.orm.ibatis.SqlMapClientTemplate">
		<property name="sqlMapClient">
			<ref bean="cmsSqlMapClient"/>
		</property>
	</bean>

	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<property name="dataSource">
			<ref local="cmsDbcpDataSource" />
		</property>
	</bean>

	<bean id="baseTxProxy" lazy-init="true" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean" abstract="true">
		<property name="transactionManager">
			<ref local="transactionManager" />
		</property>
		<property name="transactionAttributes">
			<props>
				<prop key="*">PROPAGATION_REQUIRED</prop>
				<prop key="create*">PROPAGATION_REQUIRED</prop>
				<prop key="add*">PROPAGATION_REQUIRED</prop>
				<prop key="delete*">PROPAGATION_REQUIRED</prop>
				<prop key="update*">PROPAGATION_REQUIRED</prop>
				<prop key="get*">PROPAGATION_REQUIRED</prop>
			</props>
		</property>
	</bean>

	<bean id="categoryDao"
		class="com.demo.category.dao.impl.ibatis.CategoryDaoImpl">
		<property name="cmsSqlMapClientTemplete">
			<ref bean="cmsSqlMapClientTemplete" />
		</property>
	</bean>
	
	<bean id="softwareDao"
		class="com.demo.software.dao.impl.ibatis.SoftwareDaoImpl">
		<property name="cmsSqlMapClientTemplete">
			<ref bean="cmsSqlMapClientTemplete" />
		</property>
	</bean>
</beans>

下面是相应的DAO：

public abstract class BaseDaoImpl
{
	private SqlMapClientTemplate cmsSqlMapClientTemplete;

	public SqlMapClientTemplate getCmsSqlMapClientTemplete()
	{
		return cmsSqlMapClientTemplete;
	}

	public void setCmsSqlMapClientTemplete(
			SqlMapClientTemplate cmsSqlMapClientTemplete)
	{
		this.cmsSqlMapClientTemplete = cmsSqlMapClientTemplete;
	}
}

public class SoftwareDaoImpl extends BaseDaoImpl implements SoftwareDao
{

	@Override
	public SoftwareModel getSoftwareById(int softwareId)
	{
		return (SoftwareModel) getCmsSqlMapClientTemplete().queryForObject("Software.getSoftwareById", new Integer(softwareId));
	}

	@Override
	public SoftwareModel getSoftwareByName(String name)
	{
		return (SoftwareModel) getCmsSqlMapClientTemplete().queryForObject("Software.getSoftwareByName", name);
	}
}

public class CategoryDaoImpl extends BaseDaoImpl implements CategoryDao
{

	@Override
	public CategoryModel getCategoryById(int categoryId)
	{
		return (CategoryModel) getCmsSqlMapClientTemplete().queryForObject("Category.getCategoryById", new Integer(categoryId));
	}
	
	@Override
	public CategoryModel getCategoryByName(String name)
	{
		return (CategoryModel) getCmsSqlMapClientTemplete().queryForObject("Category.getCategoryByName", name);
	}
}

如果调用以下直接取得类别的代码不会有任何问题，证明这样子来取父类别是没有任何问题的

CategoryModel model = categoryDao.getCategoryById(273);

。
但是下面是取产品的代码就会抛出异常：

SoftwareModel model = softwareDao.getSoftwareById(111);//抛出异常
SoftwareModel model = softwareDao.getSoftwareByName("aaa");//抛出异常

当把Category.xml文件中“<result property="parentCategory" column="FParentID" select="Category.getCategoryById" />”这一行注释掉后，上面代码就能正确运行。
下面是取产品代码的异常堆栈：
org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [HY010]; error code [0];
--- The error occurred in com/demo/software/Software.xml.
--- The error occurred while applying a result map.
--- Check the Software.softwareResult.
--- Check the result mapping for the 'category' property.
--- Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/demo/category/Category.xml.
--- The error occurred while applying a result map.
--- Check the Category.categoryResult.
--- The error happened while setting a property on the result object.
--- Cause: java.sql.SQLException: Invalid state, the ResultSet object is closed.; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/demo/software/Software.xml.
--- The error occurred while applying a result map.
--- Check the Software.softwareResult.
--- Check the result mapping for the 'category' property.
--- Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/demo/category/Category.xml.
--- The error occurred while applying a result map.
--- Check the Category.categoryResult.
--- The error happened while setting a property on the result object.
--- Cause: java.sql.SQLException: Invalid state, the ResultSet object is closed.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)
at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForObject(SqlMapClientTemplate.java:271)
at com.shareweb.cms.component.software.dao.impl.ibatis.SoftwareDaoImpl.getSoftwareById(SoftwareDaoImpl.java:23)
at com.shareweb.cms.importdata.AdvertExcelImportManagerImplTest.testImportData(AdvertExcelImportManagerImplTest.java:54)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit3TestReference.run(JUnit3TestReference.java:130)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/demo/software/Software.xml.
--- The error occurred while applying a result map.
--- Check the Software.softwareResult.
--- Check the result mapping for the 'category' property.
--- Cause: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/demo/category/Category.xml.
--- The error occurred while applying a result map.
--- Check the Category.categoryResult.
--- The error happened while setting a property on the result object.
--- Cause: java.sql.SQLException: Invalid state, the ResultSet object is closed.
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:120)
at com.ibatis.sqlmap.engine.mapping.statement.CachingStatement.executeQueryForObject(CachingStatement.java:79)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at org.springframework.orm.ibatis.SqlMapClientTemplate$1.doInSqlMapClient(SqlMapClientTemplate.java:273)
at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)
... 21 more
Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/demo/category/Category.xml.
--- The error occurred while applying a result map.
--- Check the Category.categoryResult.
--- The error happened while setting a property on the result object.
--- Cause: java.sql.SQLException: Invalid state, the ResultSet object is closed.
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:201)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryForObject(MappedStatement.java:120)
at com.ibatis.sqlmap.engine.mapping.statement.CachingStatement.executeQueryForObject(CachingStatement.java:79)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:518)
at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForObject(SqlMapExecutorDelegate.java:493)
at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForObject(SqlMapSessionImpl.java:106)
at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForObject(SqlMapClientImpl.java:82)
at com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.getResult(ResultLoader.java:75)
at com.ibatis.sqlmap.engine.mapping.result.loader.LazyResultLoader.loadResult(LazyResultLoader.java:77)
at com.ibatis.sqlmap.engine.mapping.result.loader.ResultLoader.loadResult(ResultLoader.java:54)
at com.ibatis.sqlmap.engine.mapping.result.ResultMap.getNestedSelectMappingValue(ResultMap.java:501)
at com.ibatis.sqlmap.engine.mapping.result.ResultMap.getResults(ResultMap.java:341)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:384)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(SqlExecutor.java:300)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:189)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
... 28 more
Caused by: java.sql.SQLException: Invalid state, the ResultSet object is closed.
at net.sourceforge.jtds.jdbc.JtdsResultSet.checkOpen(JtdsResultSet.java:299)
at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:569)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:169)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at com.ibatis.common.jdbc.logging.ResultSetLogProxy.invoke(ResultSetLogProxy.java:47)
at $Proxy10.next(Unknown Source)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleResults(SqlExecutor.java:383)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(SqlExecutor.java:300)
at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:189)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteQuery(MappedStatement.java:221)
at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeQueryWithCallback(MappedStatement.java:189)
... 44 more

去iBatis官方论坛上也没有找到解决方法。难道没有人遇到过这种问题吗？

lz, 每得到出一个category都会查它的parent category,那这个递归什么时候停呢？

首选，这个是lazyLoading的，所以如果没有调用getParentCategory()方法的话是不会真正去加载parent category的。
其次，当递归到根结点时就停了。
问题不在这个上面，如果是因为递归原因，为什么我直接CategoryModel model = categoryDao.getCategoryById(273); 就可以，而SoftwareModel model = softwareDao.getSoftwareById(111);却出错。

在www.nabble.com上问了，也没有什么实质性的回答。大部份人认为可能是DB设置的问题，比如链接数是不是设置成1了。但我试过了，不是这个问题。javaeye的大侠们，没有人遇到过这种问题吗？

直接用inner join 实现联合查询就可以了不是么？

如果用inner join的话，就不能通过po一级一级地得到父类别了。此外，即使通过别的方法解决了，但还是想从技术弄明白为什么不能这样做。
在www.nabble.com上有人说是配置问题，于是试了下面代码，代码运行没问题，证明DB配置是没有问题的。
Connection cn1 = null;
PreparedStatement pst1 = null;
PreparedStatement pst2 = null;
ResultSet rs1 = null;
ResultSet rs2 = null;
DataSource ds = (BasicDataSource)ctx.getBean("cmsDbcpDataSource");

try
{
// Class.forName("net.sourceforge.jtds.jdbc.Driver").newInstance();
// cn1 = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost:1433/aa", "sa", "aa");
cn1 = ds.getConnection();
pst1 = cn1.prepareStatement("select fid, fname, fcategoryid from t_softwares");
rs1 = pst1.executeQuery();
while(rs1.next())
{
System.out.println("software:" + rs1.getString(2));
pst2 = cn1.prepareStatement("select fid, fname from t_categories where fid=?");
pst2.setInt(1, rs1.getInt(3));
rs2 = pst2.executeQuery();
if(rs2.next())
{
System.out.println("category:" + rs2.getString(2));
}
rs2.close();
pst2.close();
}
rs1.close();
pst1.close();
cn1.close();

}
catch(Exception e)
{
e.printStackTrace();
assertTrue(false);
}

两周过去了，还没解决问题