MyBatis unexpected behavior when no ID is specified
Locked
 
|
I have a query that joins data from several tables, the results have
no unique identifier, and so the mapping specifies no <id/> element. This all works fine until two rows contain identical data across all columns. It seems then that one of the rows is lost (as if it were a duplicate).. it leads me to think that when no ID is specified, MyBatis treats the combination of all columns (<result/>s) to be the ID and as the key, it toss a duplicate row. These rows are not in fact duplicate, it is possible/expected in this query for two identical rows to be returned... and so I am missing data in the result. Its as if I specified DISTINCT on the query, when I did not and do not want it to be de-duped. It seems to me that if no id is specified, MyBatis should consider each result row to be a unique record and not to assume that the combination of all columns constitutes a unique result. I am probably going to address this by generating a row number on my query and using it as the ID, but this seems like more of a workaround to me. Thoughts? |
|
|
Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if
this had changed. On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > I have a query that joins data from several tables, the results have > no unique identifier, and so the mapping specifies no <id/> > element. This all works fine until two rows contain identical data > across all columns. It seems then that one of the rows is lost (as > if it were a duplicate).. it leads me to think that when no ID is > specified, MyBatis treats the combination of all columns (<result/>s) > to be the ID and as the key, it toss a duplicate row. These rows are > not in fact duplicate, it is possible/expected in this query for two > identical rows to be returned... and so I am missing data in the > result. Its as if I specified DISTINCT on the query, when I did not > and do not want it to be de-duped. > > It seems to me that if no id is specified, MyBatis should consider > each result row to be a unique record and not to assume that the > combination of all columns constitutes a unique result. > > I am probably going to address this by generating a row number on my > query and using it as the ID, but this seems like more of a workaround > to me. Thoughts? |
Re: MyBatis unexpected behavior when no ID is specified
|
That happens indeed when using nested selects. MyBatis needs a way to
detect duplicated information coming from a join and it uses the id results if they exists or all the rows if there are no ids. 3.0.x will behave the same. 2012/5/1 Dan Gradl <[hidden email]>: > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > this had changed. > > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: >> I have a query that joins data from several tables, the results have >> no unique identifier, and so the mapping specifies no <id/> >> element. This all works fine until two rows contain identical data >> across all columns. It seems then that one of the rows is lost (as >> if it were a duplicate).. it leads me to think that when no ID is >> specified, MyBatis treats the combination of all columns (<result/>s) >> to be the ID and as the key, it toss a duplicate row. These rows are >> not in fact duplicate, it is possible/expected in this query for two >> identical rows to be returned... and so I am missing data in the >> result. Its as if I specified DISTINCT on the query, when I did not >> and do not want it to be de-duped. >> >> It seems to me that if no id is specified, MyBatis should consider >> each result row to be a unique record and not to assume that the >> combination of all columns constitutes a unique result. >> >> I am probably going to address this by generating a row number on my >> query and using it as the ID, but this seems like more of a workaround >> to me. Thoughts? |
|
|
But why would MyBatis need to detect duplicate information? If I
wanted to strip duplicate information I would put DISTINCT in my query. Otherwise, I would expect it to happily retrieve whatever the query result was. On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> wrote: > That happens indeed when using nested selects. MyBatis needs a way to > detect duplicated information coming from a join and it uses the id > results if they exists or all the rows if there are no ids. > > 3.0.x will behave the same. > > 2012/5/1 Dan Gradl <[hidden email]>: > > > > > > > > > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > > this had changed. > > > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > >> I have a query that joins data from several tables, the results have > >> no unique identifier, and so the mapping specifies no <id/> > >> element. This all works fine until two rows contain identical data > >> across all columns. It seems then that one of the rows is lost (as > >> if it were a duplicate).. it leads me to think that when no ID is > >> specified, MyBatis treats the combination of all columns (<result/>s) > >> to be the ID and as the key, it toss a duplicate row. These rows are > >> not in fact duplicate, it is possible/expected in this query for two > >> identical rows to be returned... and so I am missing data in the > >> result. Its as if I specified DISTINCT on the query, when I did not > >> and do not want it to be de-duped. > > >> It seems to me that if no id is specified, MyBatis should consider > >> each result row to be a unique record and not to assume that the > >> combination of all columns constitutes a unique result. > > >> I am probably going to address this by generating a row number on my > >> query and using it as the ID, but this seems like more of a workaround > >> to me. Thoughts? |
Re: MyBatis unexpected behavior when no ID is specified
|
Hi,
Me too, I have same problem. This, result 7 rows: list = session.selectList("ImageFlowReader.SelectImagenesIdConfig", 9 ); This, result 8 rows, last same that first, copied. list = session.selectList("ImageFlowReader.SelectImagesByMedio", 9 ); here, in the resultMap, the images.size() is 7
public void setImages(List<TImagenes> images) { this.imagenes = images; Log.log( this, "setImagenes", "size: " + this.imagenes.size() + ", imgs: " + this.imagenes );
} but after finish all process of list = session.selectList("ImageFlowReader.SelectImagesByMedio", 9 ); list.size() have 8 rows... The resultMap: <resultMap id="MDMedioImagesMap" type="MDMedioImagenes"> <id column="Id" jdbcType="INTEGER" property="id" />
<result column="Name" jdbcType="NVARCHAR" property="name" /> <result column="OrgInput" jdbcType="OTHER" property="orgInput" />
<result column="RetInput" jdbcType="OTHER" property="retInput" /> <result column="OrgOutput" jdbcType="OTHER" property="orgOutput" />
<result column="RetOutput" jdbcType="OTHER" property="retOutput" /> <result column="Status" jdbcType="INTEGER" property="status" />
<result column="Procces" jdbcType="INTEGER" property="procces" /> <result column="OrgTmp" jdbcType="OTHER" property="orgTmp" />
<result column="RetTmp" jdbcType="OTHER" property="retTmp" /> <collection column="Id" property="imagenes" ofType="TImagen"
javaType="List" select="SelectImagenesIdConfig" resultMap="TImagesMap"/> </resultMap> What is the problem? this is not correct.
if need, I can prepare a testcase... I use MyBatis 3.1.1 Thanks... ;) 2012/5/2 Dan Gradl <[hidden email]> But why would MyBatis need to detect duplicate information? If I ------------------------------------------------------------ "Sócrates tenía derecho a demostrar su propia sabiduría,
pero no a demostrar a los demás hombres su falta de verdadera discriminación". |
Re: MyBatis unexpected behavior when no ID is specified
|
|
In reply to this post by Dan Gradl
That is how it processes nested resultmaps (joins).
If you are not using a nested result map that should not happen. Could you post your statement/resultmap? 2012/5/2 Dan Gradl <[hidden email]>: > But why would MyBatis need to detect duplicate information? If I > wanted to strip duplicate information I would put DISTINCT in my > query. Otherwise, I would expect it to happily retrieve whatever the > query result was. > > > > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> > wrote: >> That happens indeed when using nested selects. MyBatis needs a way to >> detect duplicated information coming from a join and it uses the id >> results if they exists or all the rows if there are no ids. >> >> 3.0.x will behave the same. >> >> 2012/5/1 Dan Gradl <[hidden email]>: >> >> >> >> >> >> >> >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if >> > this had changed. >> >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: >> >> I have a query that joins data from several tables, the results have >> >> no unique identifier, and so the mapping specifies no <id/> >> >> element. This all works fine until two rows contain identical data >> >> across all columns. It seems then that one of the rows is lost (as >> >> if it were a duplicate).. it leads me to think that when no ID is >> >> specified, MyBatis treats the combination of all columns (<result/>s) >> >> to be the ID and as the key, it toss a duplicate row. These rows are >> >> not in fact duplicate, it is possible/expected in this query for two >> >> identical rows to be returned... and so I am missing data in the >> >> result. Its as if I specified DISTINCT on the query, when I did not >> >> and do not want it to be de-duped. >> >> >> It seems to me that if no id is specified, MyBatis should consider >> >> each result row to be a unique record and not to assume that the >> >> combination of all columns constitutes a unique result. >> >> >> I am probably going to address this by generating a row number on my >> >> query and using it as the ID, but this seems like more of a workaround >> >> to me. Thoughts? |
Re: MyBatis unexpected behavior when no ID is specified
|
|
of course...
another way? What is the correct way to do? thank very much for your help. That is... <select id="SelectImagesByMedio" resultMap="MDMedioImagesMap">
SELECT A.Id, A.Name, A.OrgInput, A.RetInput, A.OrgOutput, A.RetOutput, A.Status, A.Procces, A.OrgTmp, A.RetTmp, B.Id, B.IdConfig, B.ImageName, B.DateInput, B.DateProcces, B.IPTerminal, B.Users, B.Status
FROM Config A LEFT OUTER JOIN Imagenes B on A.Id = B.IdConfig WHERE A.Status = 1 </select> <resultMap id="MDMedioImagesMap" type="MDMedioImages">
<id column="Id" jdbcType="INTEGER" property="id" /> <result column="Name" jdbcType="NVARCHAR" property="name" /> <result column="OrgInput" jdbcType="OTHER" property="orgInput" />
<result column="RetInput" jdbcType="OTHER" property="retInput" /> <result column="OrgOutput" jdbcType="OTHER" property="orgOutput" />
<result column="RetOutput" jdbcType="OTHER" property="retOutput" /> <result column="Status" jdbcType="INTEGER" property="status" />
<result column="Procces" jdbcType="INTEGER" property="procces" /> <result column="OrgTmp" jdbcType="OTHER" property="orgTmp" />
<result column="RetTmp" jdbcType="OTHER" property="retTmp" /> <collection column="Id" property="imagenes" ofType="TImagen"
javaType="List" select="SelectImagenesIdConfig" resultMap="TImagesMap"/> </resultMap> <resultMap id="TImagesMap" type="TImagen">
<id column="id" jdbcType="INTEGER" property="id" /> <result column="IdConfig" jdbcType="INTEGER" property="idConfig" />
<result column="ImageName" jdbcType="NVARCHAR" property="imageName" /> <result column="DateInput" jdbcType="TIMESTAMP" property="dateInput" />
<result column="DateProcces" jdbcType="TIMESTAMP" property="dateProcces" /> <result column="IPTerminal" jdbcType="NVARCHAR" property="IPTerminal" />
<result column="Users" jdbcType="NVARCHAR" property="users" /> <result column="Status" jdbcType="INTEGER" property="status" />
</resultMap> 2012/5/2 Eduardo Macarron <[hidden email]> That is how it processes nested resultmaps (joins). ------------------------------------------------------------ "Sócrates tenía derecho a demostrar su propia sabiduría,
pero no a demostrar a los demás hombres su falta de verdadera discriminación". |
Re: MyBatis unexpected behavior when no ID is specified
|
|
Hi Jose Luis, you are not using a nested resultmap so in your case MB
should not be discarding any record. But I did not fully understand your problem: You said: This, result 8 rows, last same that first, copied. list = session.selectList("ImageFlowReader.SelectImagesByMedio", 9 ); If you enable the logging how many rows does the statement return? 2012/5/2 José Luis Mayoral <[hidden email]>: > of course... > another way? What is the correct way to do? > thank very much for your help. > > That is... > > <select id="SelectImagesByMedio" resultMap="MDMedioImagesMap"> > SELECT A.Id, A.Name, A.OrgInput, A.RetInput, A.OrgOutput, A.RetOutput, > A.Status, A.Procces, A.OrgTmp, A.RetTmp, > B.Id, B.IdConfig, B.ImageName, B.DateInput, B.DateProcces, B.IPTerminal, > B.Users, B.Status > FROM Config A > LEFT OUTER JOIN Imagenes B on A.Id = B.IdConfig > WHERE A.Status = 1 > </select> > > <resultMap id="MDMedioImagesMap" type="MDMedioImages"> > <id column="Id" jdbcType="INTEGER" property="id" /> > <result column="Name" jdbcType="NVARCHAR" property="name" /> > <result column="OrgInput" jdbcType="OTHER" property="orgInput" /> > <result column="RetInput" jdbcType="OTHER" property="retInput" /> > <result column="OrgOutput" jdbcType="OTHER" property="orgOutput" /> > <result column="RetOutput" jdbcType="OTHER" property="retOutput" /> > <result column="Status" jdbcType="INTEGER" property="status" /> > <result column="Procces" jdbcType="INTEGER" property="procces" /> > <result column="OrgTmp" jdbcType="OTHER" property="orgTmp" /> > <result column="RetTmp" jdbcType="OTHER" property="retTmp" /> > <collection column="Id" property="imagenes" ofType="TImagen" > javaType="List" select="SelectImagenesIdConfig" > resultMap="TImagesMap"/> > </resultMap> > > <resultMap id="TImagesMap" type="TImagen"> > <id column="id" jdbcType="INTEGER" property="id" /> > <result column="IdConfig" jdbcType="INTEGER" property="idConfig" /> > <result column="ImageName" jdbcType="NVARCHAR" property="imageName" /> > <result column="DateInput" jdbcType="TIMESTAMP" property="dateInput" /> > <result column="DateProcces" jdbcType="TIMESTAMP" property="dateProcces" > /> > <result column="IPTerminal" jdbcType="NVARCHAR" property="IPTerminal" /> > <result column="Users" jdbcType="NVARCHAR" property="users" /> > <result column="Status" jdbcType="INTEGER" property="status" /> > </resultMap> > > > 2012/5/2 Eduardo Macarron <[hidden email]> >> >> That is how it processes nested resultmaps (joins). >> >> If you are not using a nested result map that should not happen. Could >> you post your statement/resultmap? >> >> 2012/5/2 Dan Gradl <[hidden email]>: >> > But why would MyBatis need to detect duplicate information? If I >> > wanted to strip duplicate information I would put DISTINCT in my >> > query. Otherwise, I would expect it to happily retrieve whatever the >> > query result was. >> > >> > >> > >> > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> >> > wrote: >> >> That happens indeed when using nested selects. MyBatis needs a way to >> >> detect duplicated information coming from a join and it uses the id >> >> results if they exists or all the rows if there are no ids. >> >> >> >> 3.0.x will behave the same. >> >> >> >> 2012/5/1 Dan Gradl <[hidden email]>: >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if >> >> > this had changed. >> >> >> >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: >> >> >> I have a query that joins data from several tables, the results have >> >> >> no unique identifier, and so the mapping specifies no <id/> >> >> >> element. This all works fine until two rows contain identical >> >> >> data >> >> >> across all columns. It seems then that one of the rows is lost (as >> >> >> if it were a duplicate).. it leads me to think that when no ID is >> >> >> specified, MyBatis treats the combination of all columns >> >> >> (<result/>s) >> >> >> to be the ID and as the key, it toss a duplicate row. These rows >> >> >> are >> >> >> not in fact duplicate, it is possible/expected in this query for two >> >> >> identical rows to be returned... and so I am missing data in the >> >> >> result. Its as if I specified DISTINCT on the query, when I did not >> >> >> and do not want it to be de-duped. >> >> >> >> >> It seems to me that if no id is specified, MyBatis should consider >> >> >> each result row to be a unique record and not to assume that the >> >> >> combination of all columns constitutes a unique result. >> >> >> >> >> I am probably going to address this by generating a row number on my >> >> >> query and using it as the ID, but this seems like more of a >> >> >> workaround >> >> >> to me. Thoughts? > > > > > -- > > ------------------------------------------------------------ > "Sócrates tenía derecho a demostrar su propia sabiduría, > pero no a demostrar a los demás hombres su falta de verdadera > discriminación". > > > |
|
|
In reply to this post by Eduardo Macarron
Yes it is indeed a nested ResultMap... so it's not the lack of <id/>
but its the use of nesting like this. Ok, well this can be flattened out I guess. Its actually a particular setup to deal with paginated data and be able to have a count of the total rows to be able to say like "viewing 1-15 out of 250 results". The query has a count(*) that is mapped out separately from the individual records... is there a good/better pattern for getting the total results when using RowBounds? I could move total rows to the detail record and flatten this out, but from a domain perspective I don't like having that attribute there. <resultMap id="mapSubItemMovementHistory" type="[.....PageContainer]"> <result property="totalRows" column="TotalRows" javaType="int" /> <collection property="data" resultMap="mapSubItemMovementHistoryDetails" /> </resultMap> <resultMap id="mapSubItemMovementHistoryDetails" type="[...Detail]"> <result property="orderId" column="orderId" /> <result property="orderNum" column="orderNum" /> <result property="companyCode" column="companyCode" /> <result property="orderStatus" column="orderStatus" /> <result property="orderType" column="orderType" /> <result property="orderBy" column="orderBy" /> <result property="orderShipToName" column="orderShipToName" /> <result property="orderShipState" column="orderShipState" /> <result property="orderShipVia" column="orderShipVia" /> <result property="orderFreightCost" column="orderFreightCost" /> <result property="orderMaterialCost" column="orderMaterialCost" /> <result property="orderDate" column="orderDate" /> <result property="orderHandlingCost" column="orderHandlingCost" /> <result property="shipDate" column="shipDate" /> <result property="factOrderStatus" column="factOrderStatus" /> <result property="pieces" column="pieces" /> <result property="fulfillingOrderID" column="fulfillingOrderID" /> <result property="backOrderNum" column="backOrderNum" /> <result property="qtyOrdered" column="qtyOrdered" /> <result property="qtyShipped" column="qtyShipped" /> <result property="orderAmount" column="orderAmount" /> <result property="orderItemId" column="orderItemId" /> <result property="itemNumber" column="itemNum" /> <result property="itemDesc" column="itemDesc" /> <result property="repName" column="repName" /> <result property="customerNumber" column="customerNumber" /> </resultMap> On May 2, 1:58 am, Eduardo Macarron <[hidden email]> wrote: > That is how it processes nested resultmaps (joins). > > If you are not using a nested result map that should not happen. Could > you post your statement/resultmap? > > 2012/5/2 Dan Gradl <[hidden email]>: > > > > > > > > > But why would MyBatis need to detect duplicate information? If I > > wanted to strip duplicate information I would put DISTINCT in my > > query. Otherwise, I would expect it to happily retrieve whatever the > > query result was. > > > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> > > wrote: > >> That happens indeed when using nested selects. MyBatis needs a way to > >> detect duplicated information coming from a join and it uses the id > >> results if they exists or all the rows if there are no ids. > > >> 3.0.x will behave the same. > > >> 2012/5/1 Dan Gradl <[hidden email]>: > > >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > >> > this had changed. > > >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > >> >> I have a query that joins data from several tables, the results have > >> >> no unique identifier, and so the mapping specifies no <id/> > >> >> element. This all works fine until two rows contain identical data > >> >> across all columns. It seems then that one of the rows is lost (as > >> >> if it were a duplicate).. it leads me to think that when no ID is > >> >> specified, MyBatis treats the combination of all columns (<result/>s) > >> >> to be the ID and as the key, it toss a duplicate row. These rows are > >> >> not in fact duplicate, it is possible/expected in this query for two > >> >> identical rows to be returned... and so I am missing data in the > >> >> result. Its as if I specified DISTINCT on the query, when I did not > >> >> and do not want it to be de-duped. > > >> >> It seems to me that if no id is specified, MyBatis should consider > >> >> each result row to be a unique record and not to assume that the > >> >> combination of all columns constitutes a unique result. > > >> >> I am probably going to address this by generating a row number on my > >> >> query and using it as the ID, but this seems like more of a workaround > >> >> to me. Thoughts? |
Re: MyBatis unexpected behavior when no ID is specified
|
I don't know what kind of db are you using. But for oracle you can get the total number of rows with
count(distinct id) OVER() totalRows On Wednesday, May 2, 2012 2:51:04 PM UTC+1, Dan Gradl wrote: Yes it is indeed a nested ResultMap... so it's not the lack of <id/> |
Re: MyBatis unexpected behavior when no ID is specified
|
|
In reply to this post by Eduardo Macarron
More simple...
I want a matser-detail result, I have 2 pojos: private Integer id; private String name; ...... } public class MDMedioImagenes extends TMediosConfig { private NArrayList<TImagenes> imagenes; }
public class TImagenes extends NAObjectImpl { private Integer id; private Integer idConfig;
private String imageName; ...... } The select: <select id="SelectImagesByMedio" resultMap="MDMedioImagesMap">
SELECT A.Id, A.Name, ..., FROM Config A </select> And two resultMap: <resultMap id="MDMedioImagesMap" type="MDMedioImagenes">
<id column="Id" jdbcType="INTEGER" property="id" /> <result column="Name" jdbcType="NVARCHAR" property="name" /> <collection column="Id" property="imagenes" ofType="TImagen"
javaType="NArrayList" select="SelectImagenesIdConfig" resultMap="TImagesMap"/> </resultMap> <resultMap id="TImagesMap" type="TImagen">
<id column="id" jdbcType="INTEGER" property="id" /> <result column="IdConfig" jdbcType="INTEGER" property="idConfig"/>
<result column="ImageName" jdbcType="NVARCHAR" property="imageName"/> ....
</resultMap> public void setImagenes(NArrayList<TImagenes> imagenes) { this.imagenes = imagenes; Log.log( this, "setImagenes", "size: " + this.imagenes.size() );
} the log write: MDMedioImagenes.setImagenes() size: 7, That is correct, in DDBB I have 7 rows ok.
but at end, in the sentence:
list = session.selectList("ImageFlowReader.SelectImagesByMedio" ); the list.size() = 8. list have 8 rows, the row 8 have the Id = the Id of row 1 and all others columns null.
SELECT A.Id, A.Name, ....,
B.Id, B.IdConfig, B.ImageName, ... FROM Config A LEFT OUTER JOIN Imagenes B on A.Id = B.IdConfig it is similar result, the log write 7 rows, but at exit of list = session.selectList("ImageFlowReader.SelectImagesByMedio" );
list have 8 rows. The row 8 = the first row, like copied. The BBDD is SQLServer 2005.
Excuse my lack of experience
Please, a simple example of master/detail of two tables with the correct resultMap and query? Thank very much for your time... ;) 2012/5/2 Eduardo Macarron <[hidden email]> Hi Jose Luis, you are not using a nested resultmap so in your case MB ------------------------------------------------------------ "Sócrates tenía derecho a demostrar su propia sabiduría,
pero no a demostrar a los demás hombres su falta de verdadera discriminación". |
Re: MyBatis unexpected behavior when no ID is specified
|
|
Sorry, la other select...
<select id="SelectImagenesIdConfig" parameterType="TImagen" resultMap="TImagesMap">
SELECT Id, IdConfig, ImageName, ... FROM dbo.Imagenes
WHERE IdConfig = #{id} </select>
2012/5/2 José Luis Mayoral <[hidden email]> More simple... ------------------------------------------------------------ "Sócrates tenía derecho a demostrar su propia sabiduría,
pero no a demostrar a los demás hombres su falta de verdadera discriminación". |
Re: MyBatis unexpected behavior when no ID is specified
|
|
Jose Luis, first of all thanks for your patience but I still do not
understand the problem. The way to do a master-detail is the one you are using. You can execute two selects (using a collection and a nested select) or a join and using a nested result map. That is <select resultmap="master"> select * from master where master_id = #{id} </select> <select resultmap="detail"> select * from detail where fk_master_id = #{id} </select> <resultmap id="master> <collection property="detail" resultmap="detail" select="detail" ... > </resultmap> Or a join <select resultmap="master"> select * from master join detail on fk_master_id = master_id where master_id = #{id} </select> and a nested resultmap <resultmap id="master> <collection property="detail" resultmap="detail" > (note there is no select attribute) </resultmap> |
|
|
In reply to this post by Filipe Sousa
No you misunderstand.. that part is an easy thing. The query already
contains the total row count at the end of each row. The current resultMap creates a container object for the results with the total count (plucked off the result) and then a collection for the detailed rows.. which is mapped by a nested resultMap. But I'm losing rows because of the nested resultMap. So I want to flatten this out now. I don't want my individual records to all have a totalRows on it.. which is why there was this outer container. Now I do confess that having a total row count on the end of all the rows (which of course is an identical value) may be a bit strange and somewhat wasteful. So I see a couple of options: 1. Keep the total column on there and add a total field on my object (the one in the collection), map it, and I can pull it off any of my records I get back from selectList, but that means diluting my domain object with unrelated data 2. I have to add an additional query to get just the count prior to retrieving the details - I am not sure what the performance impact will be for this considering the complexity of some of the queries (running a count and then running it again to get the details may cost more performance-wise than the current approach) Maybe there's some other options I'm not considering, and I just wanted to know what other folks might be doing or what tended to be the common pattern for pagination (using RowBounds) and being able to say this is page 1 of 10, showing records 1-25 of 250. On May 2, 11:39 am, Filipe Sousa <[hidden email]> wrote: > I don't know what kind of db are you using. But for oracle you can get the > total number of rows with > count(distinct id) OVER() totalRows > > > > > > > > On Wednesday, May 2, 2012 2:51:04 PM UTC+1, Dan Gradl wrote: > > > Yes it is indeed a nested ResultMap... so it's not the lack of <id/> > > but its the use of nesting like this. Ok, well this can be flattened > > out I guess. Its actually a particular setup to deal with paginated > > data and be able to have a count of the total rows to be able to say > > like "viewing 1-15 out of 250 results". The query has a count(*) > > that is mapped out separately from the individual records... is there > > a good/better pattern for getting the total results when using > > RowBounds? I could move total rows to the detail record and flatten > > this out, but from a domain perspective I don't like having that > > attribute there. > > > <resultMap id="mapSubItemMovementHistory" type="[.....PageContainer]"> > > <result property="totalRows" column="TotalRows" javaType="int" /> > > <collection property="data" > > resultMap="mapSubItemMovementHistoryDetails" /> > > </resultMap> > > > <resultMap id="mapSubItemMovementHistoryDetails" type="[...Detail]"> > > <result property="orderId" column="orderId" /> > > <result property="orderNum" column="orderNum" /> > > <result property="companyCode" column="companyCode" /> > > <result property="orderStatus" column="orderStatus" /> > > <result property="orderType" column="orderType" /> > > <result property="orderBy" column="orderBy" /> > > <result property="orderShipToName" column="orderShipToName" /> > > <result property="orderShipState" column="orderShipState" /> > > <result property="orderShipVia" column="orderShipVia" /> > > <result property="orderFreightCost" column="orderFreightCost" /> > > <result property="orderMaterialCost" column="orderMaterialCost" /> > > <result property="orderDate" column="orderDate" /> > > <result property="orderHandlingCost" column="orderHandlingCost" /> > > <result property="shipDate" column="shipDate" /> > > <result property="factOrderStatus" column="factOrderStatus" /> > > <result property="pieces" column="pieces" /> > > <result property="fulfillingOrderID" column="fulfillingOrderID" /> > > <result property="backOrderNum" column="backOrderNum" /> > > <result property="qtyOrdered" column="qtyOrdered" /> > > <result property="qtyShipped" column="qtyShipped" /> > > <result property="orderAmount" column="orderAmount" /> > > <result property="orderItemId" column="orderItemId" /> > > <result property="itemNumber" column="itemNum" /> > > <result property="itemDesc" column="itemDesc" /> > > <result property="repName" column="repName" /> > > <result property="customerNumber" column="customerNumber" /> > > </resultMap> > > > On May 2, 1:58 am, Eduardo Macarron <[hidden email]> > > wrote: > > > That is how it processes nested resultmaps (joins). > > > > If you are not using a nested result map that should not happen. Could > > > you post your statement/resultmap? > > > > 2012/5/2 Dan Gradl <[hidden email]>: > > > > > But why would MyBatis need to detect duplicate information? If I > > > > wanted to strip duplicate information I would put DISTINCT in my > > > > query. Otherwise, I would expect it to happily retrieve whatever the > > > > query result was. > > > > > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> > > > > wrote: > > > >> That happens indeed when using nested selects. MyBatis needs a way to > > > >> detect duplicated information coming from a join and it uses the id > > > >> results if they exists or all the rows if there are no ids. > > > > >> 3.0.x will behave the same. > > > > >> 2012/5/1 Dan Gradl <[hidden email]>: > > > > >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > > > >> > this had changed. > > > > >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > > > >> >> I have a query that joins data from several tables, the results > > have > > > >> >> no unique identifier, and so the mapping specifies no <id/> > > > >> >> element. This all works fine until two rows contain identical > > data > > > >> >> across all columns. It seems then that one of the rows is lost > > (as > > > >> >> if it were a duplicate).. it leads me to think that when no ID is > > > >> >> specified, MyBatis treats the combination of all columns > > (<result/>s) > > > >> >> to be the ID and as the key, it toss a duplicate row. These rows > > are > > > >> >> not in fact duplicate, it is possible/expected in this query for > > two > > > >> >> identical rows to be returned... and so I am missing data in the > > > >> >> result. Its as if I specified DISTINCT on the query, when I did > > not > > > >> >> and do not want it to be de-duped. > > > > >> >> It seems to me that if no id is specified, MyBatis should consider > > > >> >> each result row to be a unique record and not to assume that the > > > >> >> combination of all columns constitutes a unique result. > > > > >> >> I am probably going to address this by generating a row number on > > my > > > >> >> query and using it as the ID, but this seems like more of a > > workaround > > > >> >> to me. Thoughts? |
Re: MyBatis unexpected behavior when no ID is specified
|
Hi all,
I'm not sure to understand what you try to do, but you might be looking for this: Since MB 3.1.1 it is possible to declare ids only for disambiguation, without embedding those ids in the result object. If you have a couple of columns (for instance a, b, and c) that might be duplicated : <resultMap id="myMap" type="MyType"> <result column="a" property="a"/> <result column="b" property="b"/> <result column="c" property="c"/> </resultMap> You can add a unique column to your select (some sort of rowid provided by your RDBM) and not put it in your result objects : <resultMap id="myMap" type="MyType"> <id column="rowid" /> <result column="a" property="a"/> <result column="b" property="b"/> <result column="c" property="c"/> </resultMap> It also works with nested collections. This way you can have your plain list of (a,b,c) *including* identical rows. Dridi http://www.zenika.com/ Le mercredi 2 mai 2012 22:43:48 UTC+2, Dan Gradl a écrit : No you misunderstand.. that part is an easy thing. The query already |
RE: MyBatis unexpected behavior when no ID is specified
|
Hi Dan, I’m not sure of what you are trying to do, but if you use a nested result map with <collection> MyBatis needs to know how to find duplicates of the same objects or it cannot populate a collection. If you really want to fetch all rows and you which to populate an object with a collection, I recommend you use a ResultHandler with a logic similar to this. class MyResultHandler implements ResultHandler { private List<Parent> parents; private Map<Parent, Integer> rowCounts; private Map<Parent, Collection<Child>> children; public void handleResult(ResultContext context) { ParentChildAggregate agg = (ParentChildAggregate) context.getResultObject; Parent parent = agg.getParent(); Child child = agg.getChild(); if (!parents.contains(parent)) parents.add(parent); rowCounts.put(parent, rowCounts.get(parent) + 1); children.get(parent).add(child); } } And the resultMap : <resultMap type=“ParentChildAggreate“> <association resultMap=“ParentMap“> <association resultMap=“ChildMap“> </resultMap> Christian De : [hidden email] [mailto:[hidden email]] De la part de Dridi Boukelmoune Hi all, No you misunderstand.. that part is an easy thing. The query already > I don't know what kind of db are you using. But for oracle you can get the > total number of rows with > count(distinct id) OVER() totalRows > > > > > > > > On Wednesday, May 2, 2012 2:51:04 PM UTC+1, Dan Gradl wrote: > > > Yes it is indeed a nested ResultMap... so it's not the lack of <id/> > > but its the use of nesting like this. Ok, well this can be flattened > > out I guess. Its actually a particular setup to deal with paginated > > data and be able to have a count of the total rows to be able to say > > like "viewing 1-15 out of 250 results". The query has a count(*) > > that is mapped out separately from the individual records... is there > > a good/better pattern for getting the total results when using > > RowBounds? I could move total rows to the detail record and flatten > > this out, but from a domain perspective I don't like having that > > attribute there. > > > <resultMap id="mapSubItemMovementHistory" type="[.....PageContainer]"> > > <result property="totalRows" column="TotalRows" javaType="int" /> > > <collection property="data" > > resultMap="mapSubItemMovementHistoryDetails" /> > > </resultMap> > > > <resultMap id="mapSubItemMovementHistoryDetails" type="[...Detail]"> > > <result property="orderId" column="orderId" /> > > <result property="orderNum" column="orderNum" /> > > <result property="companyCode" column="companyCode" /> > > <result property="orderStatus" column="orderStatus" /> > > <result property="orderType" column="orderType" /> > > <result property="orderBy" column="orderBy" /> > > <result property="orderShipToName" column="orderShipToName" /> > > <result property="orderShipState" column="orderShipState" /> > > <result property="orderShipVia" column="orderShipVia" /> > > <result property="orderFreightCost" column="orderFreightCost" /> > > <result property="orderMaterialCost" column="orderMaterialCost" /> > > <result property="orderDate" column="orderDate" /> > > <result property="orderHandlingCost" column="orderHandlingCost" /> > > <result property="shipDate" column="shipDate" /> > > <result property="factOrderStatus" column="factOrderStatus" /> > > <result property="pieces" column="pieces" /> > > <result property="fulfillingOrderID" column="fulfillingOrderID" /> > > <result property="backOrderNum" column="backOrderNum" /> > > <result property="qtyOrdered" column="qtyOrdered" /> > > <result property="qtyShipped" column="qtyShipped" /> > > <result property="orderAmount" column="orderAmount" /> > > <result property="orderItemId" column="orderItemId" /> > > <result property="itemNumber" column="itemNum" /> > > <result property="itemDesc" column="itemDesc" /> > > <result property="repName" column="repName" /> > > <result property="customerNumber" column="customerNumber" /> > > </resultMap> > > > On May 2, 1:58 am, Eduardo Macarron <[hidden email]> > > wrote: > > > That is how it processes nested resultmaps (joins). > > > > If you are not using a nested result map that should not happen. Could > > > you post your statement/resultmap? > > > > 2012/5/2 Dan Gradl <[hidden email]>: > > > > > But why would MyBatis need to detect duplicate information? If I > > > > wanted to strip duplicate information I would put DISTINCT in my > > > > query. Otherwise, I would expect it to happily retrieve whatever the > > > > query result was. > > > > > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> > > > > wrote: > > > >> That happens indeed when using nested selects. MyBatis needs a way to > > > >> detect duplicated information coming from a join and it uses the id > > > >> results if they exists or all the rows if there are no ids. > > > > >> 3.0.x will behave the same. > > > > >> 2012/5/1 Dan Gradl <[hidden email]>: > > > > >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > > > >> > this had changed. > > > > >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > > > >> >> I have a query that joins data from several tables, the results > > have > > > >> >> no unique identifier, and so the mapping specifies no <id/> > > > >> >> element. This all works fine until two rows contain identical > > data > > > >> >> across all columns. It seems then that one of the rows is lost > > (as > > > >> >> if it were a duplicate).. it leads me to think that when no ID is > > > >> >> specified, MyBatis treats the combination of all columns > > (<result/>s) > > > >> >> to be the ID and as the key, it toss a duplicate row. These rows > > are > > > >> >> not in fact duplicate, it is possible/expected in this query for > > two > > > >> >> identical rows to be returned... and so I am missing data in the > > > >> >> result. Its as if I specified DISTINCT on the query, when I did > > not > > > >> >> and do not want it to be de-duped. > > > > >> >> It seems to me that if no id is specified, MyBatis should consider > > > >> >> each result row to be a unique record and not to assume that the > > > >> >> combination of all columns constitutes a unique result. > > > > >> >> I am probably going to address this by generating a row number on > > my > > > >> >> query and using it as the ID, but this seems like more of a > > workaround > > > >> >> to me. Thoughts? No you misunderstand.. that part is an easy thing. The query already > I don't know what kind of db are you using. But for oracle you can get the > total number of rows with > count(distinct id) OVER() totalRows > > > > > > > > On Wednesday, May 2, 2012 2:51:04 PM UTC+1, Dan Gradl wrote: > > > Yes it is indeed a nested ResultMap... so it's not the lack of <id/> > > but its the use of nesting like this. Ok, well this can be flattened > > out I guess. Its actually a particular setup to deal with paginated > > data and be able to have a count of the total rows to be able to say > > like "viewing 1-15 out of 250 results". The query has a count(*) > > that is mapped out separately from the individual records... is there > > a good/better pattern for getting the total results when using > > RowBounds? I could move total rows to the detail record and flatten > > this out, but from a domain perspective I don't like having that > > attribute there. > > > <resultMap id="mapSubItemMovementHistory" type="[.....PageContainer]"> > > <result property="totalRows" column="TotalRows" javaType="int" /> > > <collection property="data" > > resultMap="mapSubItemMovementHistoryDetails" /> > > </resultMap> > > > <resultMap id="mapSubItemMovementHistoryDetails" type="[...Detail]"> > > <result property="orderId" column="orderId" /> > > <result property="orderNum" column="orderNum" /> > > <result property="companyCode" column="companyCode" /> > > <result property="orderStatus" column="orderStatus" /> > > <result property="orderType" column="orderType" /> > > <result property="orderBy" column="orderBy" /> > > <result property="orderShipToName" column="orderShipToName" /> > > <result property="orderShipState" column="orderShipState" /> > > <result property="orderShipVia" column="orderShipVia" /> > > <result property="orderFreightCost" column="orderFreightCost" /> > > <result property="orderMaterialCost" column="orderMaterialCost" /> > > <result property="orderDate" column="orderDate" /> > > <result property="orderHandlingCost" column="orderHandlingCost" /> > > <result property="shipDate" column="shipDate" /> > > <result property="factOrderStatus" column="factOrderStatus" /> > > <result property="pieces" column="pieces" /> > > <result property="fulfillingOrderID" column="fulfillingOrderID" /> > > <result property="backOrderNum" column="backOrderNum" /> > > <result property="qtyOrdered" column="qtyOrdered" /> > > <result property="qtyShipped" column="qtyShipped" /> > > <result property="orderAmount" column="orderAmount" /> > > <result property="orderItemId" column="orderItemId" /> > > <result property="itemNumber" column="itemNum" /> > > <result property="itemDesc" column="itemDesc" /> > > <result property="repName" column="repName" /> > > <result property="customerNumber" column="customerNumber" /> > > </resultMap> > > > On May 2, 1:58 am, Eduardo Macarron <[hidden email]> > > wrote: > > > That is how it processes nested resultmaps (joins). > > > > If you are not using a nested result map that should not happen. Could > > > you post your statement/resultmap? > > > > 2012/5/2 Dan Gradl <[hidden email]>: > > > > > But why would MyBatis need to detect duplicate information? If I > > > > wanted to strip duplicate information I would put DISTINCT in my > > > > query. Otherwise, I would expect it to happily retrieve whatever the > > > > query result was. > > > > > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> > > > > wrote: > > > >> That happens indeed when using nested selects. MyBatis needs a way to > > > >> detect duplicated information coming from a join and it uses the id > > > >> results if they exists or all the rows if there are no ids. > > > > >> 3.0.x will behave the same. > > > > >> 2012/5/1 Dan Gradl <[hidden email]>: > > > > >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > > > >> > this had changed. > > > > >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > > > >> >> I have a query that joins data from several tables, the results > > have > > > >> >> no unique identifier, and so the mapping specifies no <id/> > > > >> >> element. This all works fine until two rows contain identical > > data > > > >> >> across all columns. It seems then that one of the rows is lost > > (as > > > >> >> if it were a duplicate).. it leads me to think that when no ID is > > > >> >> specified, MyBatis treats the combination of all columns > > (<result/>s) > > > >> >> to be the ID and as the key, it toss a duplicate row. These rows > > are > > > >> >> not in fact duplicate, it is possible/expected in this query for > > two > > > >> >> identical rows to be returned... and so I am missing data in the > > > >> >> result. Its as if I specified DISTINCT on the query, when I did > > not > > > >> >> and do not want it to be de-duped. > > > > >> >> It seems to me that if no id is specified, MyBatis should consider > > > >> >> each result row to be a unique record and not to assume that the > > > >> >> combination of all columns constitutes a unique result. > > > > >> >> I am probably going to address this by generating a row number on > > my > > > >> >> query and using it as the ID, but this seems like more of a > > workaround > > > >> >> to me. Thoughts? No you misunderstand.. that part is an easy thing. The query already > I don't know what kind of db are you using. But for oracle you can get the > total number of rows with > count(distinct id) OVER() totalRows > > > > > > > > On Wednesday, May 2, 2012 2:51:04 PM UTC+1, Dan Gradl wrote: > > > Yes it is indeed a nested ResultMap... so it's not the lack of <id/> > > but its the use of nesting like this. Ok, well this can be flattened > > out I guess. Its actually a particular setup to deal with paginated > > data and be able to have a count of the total rows to be able to say > > like "viewing 1-15 out of 250 results". The query has a count(*) > > that is mapped out separately from the individual records... is there > > a good/better pattern for getting the total results when using > > RowBounds? I could move total rows to the detail record and flatten > > this out, but from a domain perspective I don't like having that > > attribute there. > > > <resultMap id="mapSubItemMovementHistory" type="[.....PageContainer]"> > > <result property="totalRows" column="TotalRows" javaType="int" /> > > <collection property="data" > > resultMap="mapSubItemMovementHistoryDetails" /> > > </resultMap> > > > <resultMap id="mapSubItemMovementHistoryDetails" type="[...Detail]"> > > <result property="orderId" column="orderId" /> > > <result property="orderNum" column="orderNum" /> > > <result property="companyCode" column="companyCode" /> > > <result property="orderStatus" column="orderStatus" /> > > <result property="orderType" column="orderType" /> > > <result property="orderBy" column="orderBy" /> > > <result property="orderShipToName" column="orderShipToName" /> > > <result property="orderShipState" column="orderShipState" /> > > <result property="orderShipVia" column="orderShipVia" /> > > <result property="orderFreightCost" column="orderFreightCost" /> > > <result property="orderMaterialCost" column="orderMaterialCost" /> > > <result property="orderDate" column="orderDate" /> > > <result property="orderHandlingCost" column="orderHandlingCost" /> > > <result property="shipDate" column="shipDate" /> > > <result property="factOrderStatus" column="factOrderStatus" /> > > <result property="pieces" column="pieces" /> > > <result property="fulfillingOrderID" column="fulfillingOrderID" /> > > <result property="backOrderNum" column="backOrderNum" /> > > <result property="qtyOrdered" column="qtyOrdered" /> > > <result property="qtyShipped" column="qtyShipped" /> > > <result property="orderAmount" column="orderAmount" /> > > <result property="orderItemId" column="orderItemId" /> > > <result property="itemNumber" column="itemNum" /> > > <result property="itemDesc" column="itemDesc" /> > > <result property="repName" column="repName" /> > > <result property="customerNumber" column="customerNumber" /> > > </resultMap> > > > On May 2, 1:58 am, Eduardo Macarron <[hidden email]> > > wrote: > > > That is how it processes nested resultmaps (joins). > > > > If you are not using a nested result map that should not happen. Could > > > you post your statement/resultmap? > > > > 2012/5/2 Dan Gradl <[hidden email]>: > > > > > But why would MyBatis need to detect duplicate information? If I > > > > wanted to strip duplicate information I would put DISTINCT in my > > > > query. Otherwise, I would expect it to happily retrieve whatever the > > > > query result was. > > > > > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> > > > > wrote: > > > >> That happens indeed when using nested selects. MyBatis needs a way to > > > >> detect duplicated information coming from a join and it uses the id > > > >> results if they exists or all the rows if there are no ids. > > > > >> 3.0.x will behave the same. > > > > >> 2012/5/1 Dan Gradl <[hidden email]>: > > > > >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > > > >> > this had changed. > > > > >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > > > >> >> I have a query that joins data from several tables, the results > > have > > > >> >> no unique identifier, and so the mapping specifies no <id/> > > > >> >> element. This all works fine until two rows contain identical > > data > > > >> >> across all columns. It seems then that one of the rows is lost > > (as > > > >> >> if it were a duplicate).. it leads me to think that when no ID is > > > >> >> specified, MyBatis treats the combination of all columns > > (<result/>s) > > > >> >> to be the ID and as the key, it toss a duplicate row. These rows > > are > > > >> >> not in fact duplicate, it is possible/expected in this query for > > two > > > >> >> identical rows to be returned... and so I am missing data in the > > > >> >> result. Its as if I specified DISTINCT on the query, when I did > > not > > > >> >> and do not want it to be de-duped. > > > > >> >> It seems to me that if no id is specified, MyBatis should consider > > > >> >> each result row to be a unique record and not to assume that the > > > >> >> combination of all columns constitutes a unique result. > > > > >> >> I am probably going to address this by generating a row number on > > my > > > >> >> query and using it as the ID, but this seems like more of a > > workaround > > > >> >> to me. Thoughts? No you misunderstand.. that part is an easy thing. The query already > I don't know what kind of db are you using. But for oracle you can get the > total number of rows with > count(distinct id) OVER() totalRows > > > > > > > > On Wednesday, May 2, 2012 2:51:04 PM UTC+1, Dan Gradl wrote: > > > Yes it is indeed a nested ResultMap... so it's not the lack of <id/> > > but its the use of nesting like this. Ok, well this can be flattened > > out I guess. Its actually a particular setup to deal with paginated > > data and be able to have a count of the total rows to be able to say > > like "viewing 1-15 out of 250 results". The query has a count(*) > > that is mapped out separately from the individual records... is there > > a good/better pattern for getting the total results when using > > RowBounds? I could move total rows to the detail record and flatten > > this out, but from a domain perspective I don't like having that > > attribute there. > > > <resultMap id="mapSubItemMovementHistory" type="[.....PageContainer]"> > > <result property="totalRows" column="TotalRows" javaType="int" /> > > <collection property="data" > > resultMap="mapSubItemMovementHistoryDetails" /> > > </resultMap> > > > <resultMap id="mapSubItemMovementHistoryDetails" type="[...Detail]"> > > <result property="orderId" column="orderId" /> > > <result property="orderNum" column="orderNum" /> > > <result property="companyCode" column="companyCode" /> > > <result property="orderStatus" column="orderStatus" /> > > <result property="orderType" column="orderType" /> > > <result property="orderBy" column="orderBy" /> > > <result property="orderShipToName" column="orderShipToName" /> > > <result property="orderShipState" column="orderShipState" /> > > <result property="orderShipVia" column="orderShipVia" /> > > <result property="orderFreightCost" column="orderFreightCost" /> > > <result property="orderMaterialCost" column="orderMaterialCost" /> > > <result property="orderDate" column="orderDate" /> > > <result property="orderHandlingCost" column="orderHandlingCost" /> > > <result property="shipDate" column="shipDate" /> > > <result property="factOrderStatus" column="factOrderStatus" /> > > <result property="pieces" column="pieces" /> > > <result property="fulfillingOrderID" column="fulfillingOrderID" /> > > <result property="backOrderNum" column="backOrderNum" /> > > <result property="qtyOrdered" column="qtyOrdered" /> > > <result property="qtyShipped" column="qtyShipped" /> > > <result property="orderAmount" column="orderAmount" /> > > <result property="orderItemId" column="orderItemId" /> > > <result property="itemNumber" column="itemNum" /> > > <result property="itemDesc" column="itemDesc" /> > > <result property="repName" column="repName" /> > > <result property="customerNumber" column="customerNumber" /> > > </resultMap> > > > On May 2, 1:58 am, Eduardo Macarron <[hidden email]> > > wrote: > > > That is how it processes nested resultmaps (joins). > > > > If you are not using a nested result map that should not happen. Could > > > you post your statement/resultmap? > > > > 2012/5/2 Dan Gradl <[hidden email]>: > > > > > But why would MyBatis need to detect duplicate information? If I > > > > wanted to strip duplicate information I would put DISTINCT in my > > > > query. Otherwise, I would expect it to happily retrieve whatever the > > > > query result was. > > > > > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> > > > > wrote: > > > >> That happens indeed when using nested selects. MyBatis needs a way to > > > >> detect duplicated information coming from a join and it uses the id > > > >> results if they exists or all the rows if there are no ids. > > > > >> 3.0.x will behave the same. > > > > >> 2012/5/1 Dan Gradl <[hidden email]>: > > > > >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > > > >> > this had changed. > > > > >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > > > >> >> I have a query that joins data from several tables, the results > > have > > > >> >> no unique identifier, and so the mapping specifies no <id/> > > > >> >> element. This all works fine until two rows contain identical > > data > > > >> >> across all columns. It seems then that one of the rows is lost > > (as > > > >> >> if it were a duplicate).. it leads me to think that when no ID is > > > >> >> specified, MyBatis treats the combination of all columns > > (<result/>s) > > > >> >> to be the ID and as the key, it toss a duplicate row. These rows > > are > > > >> >> not in fact duplicate, it is possible/expected in this query for > > two > > > >> >> identical rows to be returned... and so I am missing data in the > > > >> >> result. Its as if I specified DISTINCT on the query, when I did > > not > > > >> >> and do not want it to be de-duped. > > > > >> >> It seems to me that if no id is specified, MyBatis should consider > > > >> >> each result row to be a unique record and not to assume that the > > > >> >> combination of all columns constitutes a unique result. > > > > >> >> I am probably going to address this by generating a row number on > > my > > > >> >> query and using it as the ID, but this seems like more of a > > workaround > > > >> >> to me. Thoughts? |
|
|
Yea I went ahead and just tacked on an id like this. Thanks.
On May 3, 8:55 am, Poitras Christian <[hidden email]> wrote: > Hi Dan, > > I’m not sure of what you are trying to do, but if you use a nested result map with <collection> MyBatis needs to know how to find duplicates of the same objects or it cannot populate a collection. > > If you really want to fetch all rows and you which to populate an object with a collection, I recommend you use a ResultHandler with a logic similar to this. > class MyResultHandler implements ResultHandler { > private List<Parent> parents; > private Map<Parent, Integer> rowCounts; > private Map<Parent, Collection<Child>> children; > public void handleResult(ResultContext context) { > ParentChildAggregate agg = (ParentChildAggregate) context.getResultObject; > Parent parent = agg.getParent(); > Child child = agg.getChild(); > if (!parents.contains(parent)) parents.add(parent); > rowCounts.put(parent, rowCounts.get(parent) + 1); > children.get(parent).add(child); > } > > } > > And the resultMap : > <resultMap type=“ParentChildAggreate“> > <association resultMap=“ParentMap“> > <association resultMap=“ChildMap“> > </resultMap> > > Christian > > De : [hidden email] [mailto:[hidden email]] De la part de Dridi Boukelmoune > Envoyé : May-03-12 1:24 AM > À : [hidden email] > Objet : Re: MyBatis unexpected behavior when no ID is specified > > Hi all, > > I'm not sure to understand what you try to do, but you might be looking for this: > Since MB 3.1.1 it is possible to declare ids only for disambiguation, without embedding those ids in the result object. > > If you have a couple of columns (for instance a, b, and c) that might be duplicated : > <resultMap id="myMap" type="MyType"> > <result column="a" property="a"/> > <result column="b" property="b"/> > <result column="c" property="c"/> > </resultMap> > > You can add a unique column to your select (some sort of rowid provided by your RDBM) and not put it in your result objects : > <resultMap id="myMap" type="MyType"> > <id column="rowid" /> > <result column="a" property="a"/> > <result column="b" property="b"/> > <result column="c" property="c"/> > </resultMap> > > It also works with nested collections. > > This way you can have your plain list of (a,b,c) *including* identical rows. > > Dridihttp://www.zenika.com/ > > Le mercredi 2 mai 2012 22:43:48 UTC+2, Dan Gradl a écrit : > No you misunderstand.. that part is an easy thing. The query already > contains the total row count at the end of each row. The current > resultMap creates a container object for the results with the total > count (plucked off the result) and then a collection for the detailed > rows.. which is mapped by a nested resultMap. But I'm losing rows > because of the nested resultMap. So I want to flatten this out now. > I don't want my individual records to all have a totalRows on it.. > which is why there was this outer container. Now I do confess that > having a total row count on the end of all the rows (which of course > is an identical value) may be a bit strange and somewhat wasteful. > So I see a couple of options: > 1. Keep the total column on there and add a total field on my object > (the one in the collection), map it, and I can pull it off any of my > records I get back from selectList, but that means diluting my domain > object with unrelated data > 2. I have to add an additional query to get just the count prior to > retrieving the details - I am not sure what the performance impact > will be for this considering the complexity of some of the queries > (running a count and then running it again to get the details may cost > more performance-wise than the current approach) > > Maybe there's some other options I'm not considering, and I just > wanted to know what other folks might be doing or what tended to be > the common pattern for pagination (using RowBounds) and being able to > say this is page 1 of 10, showing records 1-25 of 250. > > On May 2, 11:39 am, Filipe Sousa <[hidden email]> wrote: > > > > > > > > > > > I don't know what kind of db are you using. But for oracle you can get the > > total number of rows with > > count(distinct id) OVER() totalRows > > > On Wednesday, May 2, 2012 2:51:04 PM UTC+1, Dan Gradl wrote: > > > > Yes it is indeed a nested ResultMap... so it's not the lack of <id/> > > > but its the use of nesting like this. Ok, well this can be flattened > > > out I guess. Its actually a particular setup to deal with paginated > > > data and be able to have a count of the total rows to be able to say > > > like "viewing 1-15 out of 250 results". The query has a count(*) > > > that is mapped out separately from the individual records... is there > > > a good/better pattern for getting the total results when using > > > RowBounds? I could move total rows to the detail record and flatten > > > this out, but from a domain perspective I don't like having that > > > attribute there. > > > > <resultMap id="mapSubItemMovementHistory" type="[.....PageContainer]"> > > > <result property="totalRows" column="TotalRows" javaType="int" /> > > > <collection property="data" > > > resultMap="mapSubItemMovementHistoryDetails" /> > > > </resultMap> > > > > <resultMap id="mapSubItemMovementHistoryDetails" type="[...Detail]"> > > > <result property="orderId" column="orderId" /> > > > <result property="orderNum" column="orderNum" /> > > > <result property="companyCode" column="companyCode" /> > > > <result property="orderStatus" column="orderStatus" /> > > > <result property="orderType" column="orderType" /> > > > <result property="orderBy" column="orderBy" /> > > > <result property="orderShipToName" column="orderShipToName" /> > > > <result property="orderShipState" column="orderShipState" /> > > > <result property="orderShipVia" column="orderShipVia" /> > > > <result property="orderFreightCost" column="orderFreightCost" /> > > > <result property="orderMaterialCost" column="orderMaterialCost" /> > > > <result property="orderDate" column="orderDate" /> > > > <result property="orderHandlingCost" column="orderHandlingCost" /> > > > <result property="shipDate" column="shipDate" /> > > > <result property="factOrderStatus" column="factOrderStatus" /> > > > <result property="pieces" column="pieces" /> > > > <result property="fulfillingOrderID" column="fulfillingOrderID" /> > > > <result property="backOrderNum" column="backOrderNum" /> > > > <result property="qtyOrdered" column="qtyOrdered" /> > > > <result property="qtyShipped" column="qtyShipped" /> > > > <result property="orderAmount" column="orderAmount" /> > > > <result property="orderItemId" column="orderItemId" /> > > > <result property="itemNumber" column="itemNum" /> > > > <result property="itemDesc" column="itemDesc" /> > > > <result property="repName" column="repName" /> > > > <result property="customerNumber" column="customerNumber" /> > > > </resultMap> > > > > On May 2, 1:58 am, Eduardo Macarron <[hidden email]> > > > wrote: > > > > That is how it processes nested resultmaps (joins). > > > > > If you are not using a nested result map that should not happen. Could > > > > you post your statement/resultmap? > > > > > 2012/5/2 Dan Gradl <[hidden email]>: > > > > > > But why would MyBatis need to detect duplicate information? If I > > > > > wanted to strip duplicate information I would put DISTINCT in my > > > > > query. Otherwise, I would expect it to happily retrieve whatever the > > > > > query result was. > > > > > > On May 1, 4:12 pm, Eduardo Macarron <[hidden email]> > > > > > wrote: > > > > >> That happens indeed when using nested selects. MyBatis needs a way to > > > > >> detect duplicated information coming from a join and it uses the id > > > > >> results if they exists or all the rows if there are no ids. > > > > > >> 3.0.x will behave the same. > > > > > >> 2012/5/1 Dan Gradl <[hidden email]>: > > > > > >> > Oh this is version 3.1.1 - I was onl 3.0.x but upgraded to see if > > > > >> > this had changed. > > > > > >> > On May 1, 1:31 pm, Dan Gradl <[hidden email]> wrote: > > > > >> >> I have a query that joins data from several tables, the results > > > have > > > > >> >> no unique identifier, and so the mapping specifies no <id/> > > > > >> >> element. This all works fine until two rows contain identical > > > data > > > > >> >> across all columns. It seems then that one of the rows is lost > > > (as > > > > >> >> if it were a duplicate).. it leads me to think that when no ID is > > > > >> >> specified, MyBatis treats the combination of all columns > > > (<result/>s) > > > > >> >> to be the ID and as the key, it toss a duplicate row. These rows > > > are > > > > >> >> not in fact duplicate, it is possible/expected in this query for > > > two > > > > >> >> identical rows to be returned... and so I am missing data in the > > > > >> >> result. Its as if I specified DISTINCT on the query, when I did > > > not > > > > >> >> and do not want it to be de-duped. > > > > > >> >> It seems to me that if no id is specified, MyBatis should consider > > > > >> >> each result row to be a unique record and not to assume that the > > > > >> >> combination of all columns constitutes a unique result. > > > > > >> >> I am probably going to address this by generating a row number on > > > my > > > > >> >> query and using it as the ID, but this seems like more of a > > > workaround > > > > >> >> to me. Thoughts? > > Le mercredi 2 mai 2012 22:43:48 UTC+2, Dan Gradl a écrit : > No you misunderstand.. that part is an easy thing. The query already > contains the total row count at the end of each row. The current > resultMap creates a container object for the results with the total > count (plucked off the result) and then a collection for the detailed > rows.. which is mapped by a nested resultMap. But I'm losing rows > because of the nested resultMap. So I want to flatten this out > ... > > read more » |
«
Return to mybatis-user
|
1 view|%1 views
Loading...
| Powered by Nabble | Edit this page |