Search code examples
javasqlspring-bootjpajmix

Is there a way to use a custom, arbitrary SQL query for loading an entity in Jmix?

I am using Jmix 1.3.1.

I have a database structure (that I cannot change) that is as follows:

  • There is a large table with several columns, which I'll call table T.
  • One of these columns (string-valued), which I'll call column L, is the only one I am interested in. It has a few distinct values.

I want to build an entity picker where the user can select one of the values from column L for filtering a report by that column. I do not want the entire table T displayed in the entity picker, only the distinct values of column L.

My initial attempt was to create a @JmixEntity annotated class with only my column L in it with a @NamedNativeQuery annotation and a @SqlResultSetMapping annotation that contained an SQL query like SELECT DISTINCT L FROM T, similar to what I found in this answer. I have no idea whether this is even supposed to work with Jmix, I can only find information about this approach with JPA, not with Jmix.

My current attempt is to have two classes:

  • One annotated with @JmixEntity, @Table, @Entity and @Store like any other class for the entire table T, but with the only fields being the ID and the column L.
  • One annotated only with @JmixEntity with only the column L which also serves as @InstanceName and @Id.

Then I adapt the standard entity browser for the first one of these classes to have a query select distinct e.L FROM T e.

Both of these approaches are causing the entity picker to open, but no data to be displayed, and I am stuck on how to debug what is going on. I am not sure which of these approaches might even work or whether there might be another approach to achieve what I want.

Solution

  • I ended up solving this problem by not connecting the entity to the database at all (i.e. no @Table, no @Store, no @NamedNativeQuery annotation, no annotations other than @JmixEntity and @Entity).

    Then in my UI controller, in a method like the one knstvk suggested for debugging purposes, I created a JdbcTemplate and put my query into a jdbcTemplate.queryForList call. Then I could manually dataManager.create the needed entities.

    I am not claiming that this is an especially elegant solution, but it is working.