How to map ALL names directly by JPA?

Question

Given a ZIP-code-like hierarchical code/name schema.

For example:

code = 101010

Code:

• 100000 level 1 code (10....)
• 101000 level 2 code (..10..)
• 101010 level 3 code (....10)

Name (short name)

• 100000 - A
• 101000 - a
• 101010 - i

Name (FullQualifiedName)

• 100000 - A
• 101000 - A->a
• 101010 - A-a->i

---

EDIT

I wanna following code (JPA pseudo code), but CANNOT.

@Entity
public class CodeName{
    // ....

    String code;   // 100101 levels = {100000, 100100, 100101}
    String name;   //  

    @HowToMapDirectedToNameOfCode('100000') // @SecondTable ?
    String name1;  

    @HowToMapDirectedToNameOfCode('100100')
    String name2; 

    @HowToMapDirectedToNameOfCode('100101')
    String name3; 

    String getFullQualifiedName(){
        return String.format("%s->%s->%s", name1, name2, name3);
    }

    // getter and setter
}

But it's relatively easier in native SQL:

SELECT (select p1.name from codename p1 where p1.code= concat( substring(p.code,1,2), "0000") ) province,
(select p2.name from codename p2 where p2.code= concat( substring(p.code,1,4), "00") ) city,
(select p3.name from codename p3 where p3.code=p.code) area 

FROM codename p WHERE p.code = '100101';

---

So, I implements it as following snippet.

@Entity
public class CodeName{
    // ....

    String code;   // 100000,    101000, 100101
    String name;   // province,  city  , area 

    @Transient
    String name1;  // mapping directly?

    @Transient
    String name2;  // mapping directly?

    @Transient
    String name3;  // mapping directly?

    String getFullQualifiedName(){
        return String.format("%s->%s->%s", name1, name2, name3);
    }

    // getter and setter
}

public interface CodeNameRepository extends CrudRepository<CodeName, Long>, CodeNameRepositoryCustom  {

    @Query(" FROM CodeName p  " +
           " WHERE p.code = CONCAT(SUBSTRING(?1, 1, 2), '0000') " +
           " OR p.code = CONCAT(SUBSTRING(?1, 1, 4), '00') " +
           " OR p.code = ?1")
    List<CodeName> findAllLevelsByCode(String code);

}

@Component
public class CodeNameRepositoryImpl implements CodeNameRepositoryCustom {
    @Autowired
    private CodeNameRepository codeNameRepository ;

    @Override
    public CodeName CodeNamefindFullQualifiedNameByCode(String code) {
        List<CodeName> codeNames= codeNameRepository .findAllLevelsByCode(code);
        CodeName codeName;
        // extra name1, name2, name3 from list,
        // fill code, name, name1, name2, name3 to codeName and 
        return codeName;
    }
}

But it have SO MANY limitations.

• Most likely, I need getFullQualifiedName(), to display it on UI, but every time I must have an extra call to populate all names.
• For each entity has CodeName as its children, no matter how deep the codeName is at, I MUST expand to the codeName and reload it with FQN.

Can we mapping all @Transient names directly by JPA?

Answer 1

It is possible to write a JPQL, which is equivalent to your SQL query. The only tricky part is to rewrite nested selects into cross joins, because nested selects are not supported by JPA and you need to join unrelated entities. On the other hand, functions CONCAT and SUBSTRING are supported by JPQL in the same way as in SQL. See the following JPQL query, which should give you the results as the SQL query in the question:

SELECT p1.name // province
  , p2.name // city
  , p.name // area
FROM CodeName p, CodeName p1, CodeName p2
WHERE p.code = '100101'
AND p1.code = concat( substring(p.code,1,2), "0000")
AND p2.code= concat( substring(p.code,1,4), "00")

The above query will give you 3 values in one row, which cannot be mapped into a single entity. The result of the query will therefore be a list of Object[] arrays. You may also add the original entity into the select clause: SELECT p1.name, p2.name, p.name, p FROM .... This way, you may later process the list of results and assign first three values into the transient fields of the entity:

Object[] rows = query.getResultList();
for (Object row : rows) {
  CodeName c = (CodeName)row[3];
  c.setName1((String)row[0]);
  c.setName2((String)row[1]);
  c.setName3((String)row[2]);
}

Answer 2

You could technically model your code repository entity as follows:

 public class CodeName {

   @Id
   @GeneratedValue(GenerationStrategy.AUTO)
   @Column
   private Long id;

   @ManyToOne
   private CodeName parent;

   @OneToMany(mappedBy = "parent")
   private List<CodeName> children;       

   @Column
   private String name;

   @Transient
   public String getFullyQualifiedName() {
     List<String> names = new ArrayList<>();         
     names.add(name);
     CodeName theParent = parent;
     while(theParent != null) {
       names.add(theParent.getName());
       theParent = theParent.parent;
     }
     Collections.reverse(names);
     return StringUtils.join(names, "->");
  }           
}

Because the parent relationships will be fetched EAGERLY because they mapped as @ManyToOne, you can basically start at any child CodeName entity and traverse up it's parent/child relationship to the root. This basically allows the getFullyQualifiedName method to build the name for you at runtime.

If performance becomes a problem doing this, you can always datamine the names ahead of time in your entity as you described by adding a @Column private String fullyQualifiedName and make sure that field is inserted when you create your codes. Then the transient method I added to my the entity can be dropped since you're caching the names at data insertion.