how to many to one with mybatis annotation

[jdeo...@gmail.com]

I hope you will understand what I'm looking for in myBatis annotation

The table A return me many values. The table B return me one value for each values of the table A.

And I would like to store all the values from the table B in a list.

I can do it by making to query. One from the table A, get the list, and make a "if" in JAVA with the query for table B and store the result in a list.

But I'm try to see if I can do something like this directly in myBatis:

@Select(SELECT id FROM table_A WHERE a_value = #{aValue}) @Results({ @Result(property = "images", javaType = List.class, column="id", one= @One(select = "findOneValueInTableB"))})

list<String> fingManyThingsInTableA(string aValue);

@Select(SELECT value FROM table_B WHERE id_table_A = #{id}) String findOnValueInTableB(int id);

Unfortunately this doesn't work and give me the following error:

"nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2"

Thanks for your help and your suggestions.

[Guy Rouillier]

Looks like it is working, but the query on table_B is returning 2 rows instead of 1.  Why not just use an outer join and do the whole thing in a single query?  As currently written, the code would make a round trip to the database for each row found in table_A, which is not very efficient.

--

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/a7d6aa03-c8fb-4bd1-b2fb-7ecdb5e94ab1n%40googlegroups.com.

[JDEOWorld]

Thanks Guy ! you Always try to help 😊

 

First I’m so sorry about my bad explication, I’m a new learner and my knowledge are very low.

 

I did make it works directly with a pure SQL query, Subquery or Inner Query

 

I was just interested to know If I could do the same only with myBatis. To learn more about it.

 

 

The table B will always return 1 value if table A return 1 value. It’s like one to one relationship.

So it works very well when table A return 1 value.

Soon as table A return 2 values then it crash

 

To help you to understand better there is my query in SQL that works very well

 

SELECT * FROM tb_images WHERE id IN (

SELECT id_Images FROM tb_tags T

INNER JOIN tb_profile_composite PC ON T.profile_Id = PC.profile_Id

INNER JOIN tb_users U ON PC.id_Users = U.id

WHERE U.user_Id = 'GuqyZ0JinmZ4GUTjGdKFiVSZSrywo5');

 

 

So, as I was explaining I’m trying to write the same code with only myBatis writing

 

 

Provenance : Courrier pour Windows 10

 

De : Guy Rouillier
Envoyé le :mercredi 6 janvier 2021 07:15
À : MyBatis User
Objet :Re: how to many to one with mybatis annotation

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/em1e03fba3-ae34-4d46-bf0a-77159e74103e%40asus.

 

[Guy Rouillier]

I found some time to resurrect some old test code that I wrote long ago for this type of thing.  So, I think I'm in a better place to help.

I don't think this is going to work as you originally presented it.  The @One annotation introduces an association.  You can read more about associations in the MyBatis User Guide.  An association needs something to associate to.  So, to do things the way you were originally thinking, you would need at least 2 output values, perhaps the "id" from table A and the "value" from table B.  You are attempting to output (via Result) just the set of values from B.

But this is easily solved.  If all you want are the values from table B, then invert your query, so that selecting of values from table B is in the outer query, and the selection of the ids from table A is in a subquery.  I think this is what you are describing in your example immediately below.

You can do this nested query directly in a single annotation, so I'm not understanding what you mean by "I’m trying to write the same code with only myBatis".  For me to help any further, I'm going to need some working sample code.  Hope this helps.

--

Guy Rouillier

To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/DD43FFD4-81CC-495C-87EA-5AC6BA30D5F5%40hxcore.ol.