ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jean-Francois Poilpret" <jfpoilp...@hcm.vnn.vn>
Subject RE: N+ 1 selects
Date Wed, 28 Sep 2005 23:52:43 GMT
Hello John,

As far as I can see in your xml excerpt, you are not using the "groupBy"
feature of SqlMaps to avoid the N+1 select problem.
What you are doing in that example is using the SQL "GROUP BY" clause which
is completely different from the SqlMaps N+1 solution (maybe it should be
renamed to avoid confusion?)

In your select statement you put a GROUP BY clause, but you did not put any
aggregate in the selected columns (eg, MAX, MIN, SUM...) which is forbidden
in SQL, I think the error you got is an error returned by your SQL engine
about that.

In addition, did you understand clearly what the "N+1" problem is about? In
your sample, you select from exactly ONE table, so the "N+1" problem cannot
occur here! You ought to read deeper the SqlMap documentation, I guess.

Hope this helps

	Jean-Francois

-----Original Message-----
From: John Chien [mailto:john.chien@ncmail.net] 
Sent: Thursday, September 29, 2005 3:27 AM
To: user-java@ibatis.apache.org
Subject: N+ 1 selects

Dear Sir:

I follow the FAQ to fix the N+1 selects, However, I continue get the 
compilation error.
My xml looks like:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<!DOCTYPE sqlMap
    PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN"
    "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="EhsInspector">

  <typeAlias alias="ehsInspectors" 
type="us.nc.state.enr.bets.util.EhsInspectorBean"/>
  <typeAlias alias="commonFinder"  
type="us.nc.state.enr.bets.finder.CommonFinder" />

  <resultMap id="getEhsInspectorsANDMap" class="java.util.HashMap" 
groupBy="key">
      <result property="key"   column="key" />
      <result property="value" resultMap="EhsInspector.ehsInspectorsMap"/>
  </resultMap>
 
  <resultMap id="ehsInspectorsMap" class="ehsInspectors">
    <result property="id"                column="id"               
jdbcType="NUMBER"  javaType="java.lang.String" />
    <result property="last_name"         column="last_name"        
jdbcType="VARCHAR" javaType="java.lang.String" />
    <result property="first_name"        column="first_name"       
jdbcType="VARCHAR" javaType="java.lang.String" />
    <result property="middle_initial"    column="middle_initial"   
jdbcType="VARCHAR" javaType="java.lang.String" />
    <result property="added_date"        column="added_date"       
jdbcType="VARCHAR" javaType="java.lang.String" />
    <result property="last_update_date"  column="last_update_date" 
jdbcType="VARCHAR" javaType="java.lang.String" />
    <result property="last_update_user"  column="last_update_user" 
jdbcType="VARCHAR" javaType="java.lang.String" />
  </resultMap>
 
  <select id="getEhsInspectorsAND" parameterClass="commonFinder" 
resultMap="getEhsInspectorsANDMap">
 
      SELECT DISTINCT
            EHS_ID               as key,
            EHS_ID               as id,
            EHS_NAME_LAST        as last_name,
            EHS_NAME_FIRST       as first_name,
            EHS_NAME_MI          as middle_initial,
            EHS_ADDED_DT         as added_date,
            EHS_LAST_UPDATE_DT   as last_update_date,
            EHS_LAST_UPDATE_USER as last_update_user
      FROM  EH.BETS_EHS_INSPECTOR
      GROUP BY EHS_ID
  </select>   
    
</sqlMap>

If you can help me, I will be appreciated.

Thanks,

John Chien



Mime
View raw message