How to run a native SQL query in Spring without an entity and a JPA Repository?

Question

I am trying to run some native SQL queries in my Spring application. I don´t have an entity or JpaRepository class. I know it's strange, but this is a microservice just to collect two count queries and send it to Kafka.

Well trust me, all I need is these two integers from the queries. I run these code and always returns 0. I can see in the logs that Hikari is connecting to the database, so I don't know what to do. Searched a lot, but all answers involved the @Query solution, which does not work for me.

@Repository
@AllArgsConstructor
public class ReportRepository {


  private final EntityManager em;

  public int numberOfAccounts() {
    String sql = "SELECT count(*) FROM account";
    Query query = em.createNativeQuery(sql);
    System.out.println(query.getFirstResult());
    return query.getFirstResult();
  }

  public int numberOfSubscriptions() {
    String sql = "SELECT count(*) FROM subscriptions";
    Query query = em.createNativeQuery(sql);
    System.out.println(query.getFirstResult());
    return query.getFirstResult();
  }
}

Answer 1

If you have EntityManager, and from what you are saying it can connect to DB, try this way:

public int numberOfSubscriptions() {
    // >> "subscriptions" has to be the exact name of your table
    // if does not work, consider trying SUBSCRIPTIONS or Subscriptions
    String sql = "SELECT count(*) FROM subscriptions";
    Query query = em.createNativeQuery(sql);

    // getSingleResult() instead :)
    return ((Number) query.getSingleResult()).intValue();
}

There is this (a bit old) JavaDoc for Query.getFirstResult() :

The position of the first result the query object was set to retrieve. Returns 0 if setFirstResult was not applied to the query object

So, I'd say that is not the right method for your case.

Happy Hacking :)

Answer 2

You should be using JDBC instead of an Entity Manager. Under the JPA uses JDBC but it requires defined entites to work. JDBC allows you to manage the connection and run the raw SQL queries.

Here's a link for how to do it in Spring: https://spring.io/guides/gs/relational-data-access/#_store_and_retrieve_data