0

Is there a possibility to run some custom SQL query generated via 3rd party tools like e.g. jOOQ and still benefit from Spring Data JDBC mapping features i.e. @Column annotations? I don't want to use @Query annotation.

class Model { @Id private Long id; @Column("column") private String prop; }

class MyRepo {
  public Model runQuery() {
    val queryString = lib.generateSQL()
    // run query as if it has been generated by Spring Data JDBC and map
    // results to Model automatically
  }
}
mcjkwlczk
  • 145
  • 15
  • If you are using jooq you can use it for run query in database also with jooq generated models – Eklavya Nov 21 '20 at 21:02
  • It seems that you have QueryDsl in mind, but it is not supported by Spring Data JDBC anymore. You can use it with JPA just fine. https://github.com/querydsl/querydsl – andreoss Nov 21 '20 at 21:09

1 Answers1

0

Perhaps JdbcTemplate can solve your problem? Specifically, one of the queryForObject() methods may be of interest since your are requesting a single object:

class MyRepo {
    private JdbcTemplate jdbcTemplate;

    @Autowired
    MyRepo(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }        

    public Model runQuery() {
        val query = lib.generateQuery();
        return jdbcTemplate.queryForObject(query, Model.class);
    }
}

More information and other use cases can be found in the Spring Guide Accessing Relational Data using JDBC with Spring

matsev
  • 32,104
  • 16
  • 121
  • 156
  • It's definitely one of the choices, but I'm afraid Spring's jdbc template won't take into account the mapping specified on the model class. If there's some way to obtain an instance of the very same row mapper Spring Data JDBC uses underneath then it solves the problem indeed. – mcjkwlczk Nov 21 '20 at 20:22
  • @mcjkwlczk I guess it depends on how the database table is setup, what queries you will execute, what the mappings look like and how the 3rd party tool works. For example, if the `id` column is configured with `AUTO_INCREMENT` or other means of generating unique ids it may work. Similarly, if the 3rd party tool that you are using to generate the SQL queries introspects the `Model` class and is capable of translating the mappings into a corresponding SQL query string it may also work. – matsev Nov 21 '20 at 20:57
  • 1
    The `RowMapper` used is the `EntityRowMapper` and it's public https://github.com/spring-projects/spring-data-jdbc/blob/master/spring-data-jdbc/src/main/java/org/springframework/data/jdbc/core/convert/EntityRowMapper.java – Jens Schauder Nov 22 '20 at 16:06
  • @JensSchauder are there any plans to allow users execute their own sql statement apart from `@Query` annotation? Perhaps as a part of `JdbcAggregateOperations` interface – mcjkwlczk Nov 23 '20 at 20:36
  • 1
    No specific plans, but that is absolutely within the range what we would consider. Feel free to create a ticket for it. – Jens Schauder Nov 24 '20 at 06:51