Problems and Solutions for using external connections in JPA QL

This time I encountered a problem involving four entities for fuzzy search. The facility entity references the address entity, the address entity references the country entity and the State entity, and the facility entity is the primary entity (the corresponding table is the primary table ). The cause is that some data is not retrieved when a joint fuzzy query is performed on the facility entity and other associated entities. In addition, the query statement is written in the form of JPA Ql, "select O from facility O where o. objaccount. acntid = 1 and O. facstatus = 1 and (lower (O. strfacilityname) Like 'cer % 'or lower (O. addresseslist. addaddress1) Like 'cer % 'or lower (O. addresseslist. addcity) Like 'cer % 'or lower (O. addresseslist. states. statename) Like 'cer % 'or lower (O. addresseslist. countries. countryname) Like 'cer % ') order by O. strfacilityname ASC ". at that time, I thought of it, probably because the table and table The internal connection is used, so the external connection should be used. However, I only used the original SQL to write the external connection statement, but I still don't know how to write the external connection in this jpa SQL. So I started to try and wrote a lot of data without running the parsing. Today, I suddenly thought of a step-by-step implementation, that is, first trying to associate the facility entity with the address entity, "select O from facility o left join O. addresseslist P where o. objaccount. acntid = 1 and O. facstatus = 1 and (lower (O. strfacilityname) Like 'cer % 'or lower (P. addaddress1) Like 'cer % 'or lower (P. addcity) Like 'cer % ') order by O. strfacilityname ASC ". as a result, if it succeeds, it is basically certain that the jpa SQL external connection is parsed by generating the final SQL statement through the nesting relationship of the alias. Therefore, the final SQL statement is "select O from facility o left join O. addresseslist P left join p. countries C left join p. states s where o. objaccount. acntid = 1 and O. facstatus = 1 and (lower (O. strfacilityname) Like 'cer % 'or lower (P. addaddress1) Like 'cer % 'or lower (P. addcity) Like 'cer % 'or lower (S. statename) Like 'cer % 'or lower (C. countryname) Like 'cer % ') order by O. strfacilityname ASC "there are still some questions, some documents say, between ejb3 Default table and table Is left Outer Join. That's strange. Why didn't I retrieve the corresponding data from my previously written jpa SQL? Is it because I wrote the condition to change the external connection into an internal connection? When I write a jpa SQL statement, I noticed a problem. Once each alias is specified, the following conditions must be referenced by the alias. Otherwise, it will still be viewed by the internal connection. It seems that the "O. addresseslist. States. statename" and "O. addresseslist. Countries. countryname" statements in the where condition may re-establish the inner connection between tables. Of course, I need further confirmation.

The above is my summary after thinking about the final solution. There are many twists and turns in the middle. No such examples are available for Internet access, regardless of Baidu or Google. Please remember this time.