How to mix JPA and JDBCTemplate because of Hibernate @Formula bug

Question

I'm using Hibernate through Spring Data JPA and trying to add a calculated field. A simple SELECT 1*1 query works, but when I add the real formula, Hibernate gets totally confused and generates a syntactically invalid query.

Parent table:

@Entity
@Table(name = "szallitolevel")
public class Szallitolevel {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    @Min(1)
    private Long szam;

    @ManyToOne(cascade = CascadeType.REFRESH, fetch = FetchType.EAGER)
    @Fetch(FetchMode.JOIN)
    private Partner partner;

    @Formula("(select sum(xx.mennyiseg) from szallitolevel_sor xx where xx.szallitolevel = id)")
//    @Formula("(select 1*1)")
    private Long sumMennyiseg;

    @OneToMany(mappedBy="szallitolevel", cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval=true)
    @Fetch(FetchMode.SUBSELECT)
    @Valid
    private List<SzallitolevelSor> sorok = new AutoPopulatingList<SzallitolevelSor>(SzallitolevelSor.class);
}

Child table:

@Entity
@Table(name = "szallitolevel_sor")
public class SzallitolevelSor {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;

    private String nev;

    @Min(0)
    private Long mennyiseg;

    @ManyToOne
    private Szallitolevel szallitolevel;
}

There is not even a sum() in the generated query, and the formula somehow found its way to the WHERE part:

select 
  sorok0_.szallitolevel as szallito4_2_1_, 
  sorok0_.id as id1_3_1_, 
  sorok0_.id as id1_3_0_,
  sorok0_.mennyiseg as mennyise2_3_0_, 
  sorok0_.nev as nev3_3_0_, 
  sorok0_.szallitolevel as szallito4_3_0_ 
from szallitolevel_sor sorok0_ 
where sorok0_.szallitolevel 
  in (select szallitole0_.id 
      from szallitolevel_sor xx 
      where xx.szallitolevel = szallitole0_.id) as formula0_0_,
        partner1_.nev as nev2_1_1_, 
        partner1_.penz as penz3_1_1_ 
from szallitolevel szallitole0_ 
  left outer join partner partner1_ 
    on szallitole0_.partner=partner1_.id) 

 {FAILED after 0 msec}
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as formula0_0_, partner1_.nev as nev2_1_1_, partner1_.penz as penz3_1_1_ from sz' at line 1

UPDATE: I'm using JDBCTemplate for the relevant query, see answer

I tried to structure the SQL a bit. It actually doesn't really work. — Stefan Steinegger, Jun 21 '16 at 18:09
Well, this was the last straw for me. JPA looks useful until I try to apply it to real-world tasks. I spent the last 3 days solving problem after problem instead of making progress. Moving back to JDBCTemplate. — Arthur, Jun 21 '16 at 19:52

score 4 · Accepted Answer · edited May 23 '17 at 12:08

So finally I decided to try to keep JPA for simple CRUD and allow a JDBC escape path if it takes more than 10 minutes to make something work with JPA. Using JDBC for the above problem requires the following steps:

Create a JDBCTemplate bean in one of the config classes:

@Bean
public NamedParameterJdbcTemplate jdbcTemplate(DataSource dataSource) {
    return new NamedParameterJdbcTemplate(dataSource);
}

Set calculated entity field transient so Hibernate ignores it. Also make sure there is a getter and a setter:

@Entity
@Table(name = "szallitolevel")
public class Szallitolevel {
    ...
    @Transient
    private Long sumMennyiseg = 0L;
    ...
}

Customize the JPA repository (you can also see a fix here for the Hibernate N+1 problem by providing a custom query to findAll):

public interface SzallitolevelRepoCustom {
    List<Szallitolevel> customFindAll();
}


public interface SzallitolevelRepo extends CrudRepository<Szallitolevel, Long>, SzallitolevelRepoCustom {
    @Query("select s from Szallitolevel s left join fetch s.partner p")
    List<Szallitolevel> findAll();
}


public class SzallitolevelRepoImpl implements SzallitolevelRepoCustom {
    private static final String FIND_ALL = "SELECT sz.id, sum(sor.mennyiseg) as sumMennyiseg, partner.nev as 'partner.nev' "
            + "from szallitolevel sz "
            + "left join szallitolevel_sor sor on sor.szallitolevel = sz.id "
            + "left join partner on partner.id = sz.partner "
            + "group by sz.id ";

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    @Override
    public List<Szallitolevel> customFindAll() {
        List<Szallitolevel> result = jdbcTemplate.query(FIND_ALL, new NestedRowMapper<>(Szallitolevel.class));
        return result;
    }
}

Note the alias partner.nev in the SQL. The row mapper will use this to create an empty Partner object and set only the nev property. (If you need the entire object filled and don't mind another DB query, you can also write a Spring converter from Long to Partner that retrieves the entire object from the database.)

Stole NestedRowMapper from here.

How to mix JPA and JDBCTemplate because of Hibernate @Formula bug

1 Answers1