CODEWITHSUNDEEP
javasqlspringspring-data-jpa
wheeleruniverse
wheeleruniverse

Reputation: 1625

How to Execute a Query stored as String with Spring Data JPA?

I am programmatically constructing my SQL query as String and want to execute it as a SQL statement, but I am using Spring Data JPA and am not able to use JDBC with Prepared Statements. Is this possible in JPA?

public class Polygon {

public static void main(String[] args) {

    // Build Lat/Longs

    Set<String[]> coordinates = new HashSet<>();

    coordinates.add(new String[]{"42", "-72.95333862304689"});
    coordinates.add(new String[]{"42.05337156043361", "-71.06643676757814"});
    coordinates.add(new String[]{"41.32732632036624", "-71.06643676757814"});
    coordinates.add(new String[]{"41.32732632036624", "-72.95333862304689"});
    coordinates.add(new String[]{"42.05337156043361", "-72"});

    // Trim & Build Query

    String query = "SELECT * FROM SITE WHERE (LATITUDE LIKE ";

    for(String[] c : coordinates){

        c[0] += ".0";
        c[1] += ".0";

        String[] latArray = c[0].split("\\.");
        String[] lngArray = c[1].split("\\.");

        String lat = latArray[0] + "." + latArray[1].substring(0, 1) + "%";
        String lng = lngArray[0] + "." + lngArray[1].substring(0, 1) + "%";

        query += lat + " AND LONGITUDE LIKE " + lng + ") OR (LATITUDE LIKE ";
    }

    query = query.substring(0, query.length() - 20) + ");";
    System.err.println(query);

    /* 
     * This is the Query I am printing, which is correct
     * 
     * SELECT * FROM SITE 
     * WHERE (LATITUDE LIKE 41.3% AND LONGITUDE LIKE -71.0%) 
     *    OR (LATITUDE LIKE 42.0% AND LONGITUDE LIKE -72.0%) 
     *    OR (LATITUDE LIKE 42.0% AND LONGITUDE LIKE -72.9%) 
     *    OR (LATITUDE LIKE 42.0% AND LONGITUDE LIKE -71.0%) 
     *    OR (LATITUDE LIKE 41.3% AND LONGITUDE LIKE -72.9%);
     *    
     * How to execute this with Spring Data JPA?
     */

    // @Query("SELECT * FROM SITE :where", nativeQuery = true)
    // public List<SiteDTO> executeSiteQuery(@Param("where") String where); 
    // Doesn't Work, because (Syntax error on token ""SELECT * FROM SITE :where"", invalid MemberValuePairs)
}
}

Upvotes: 3

Views: 3981

Answers (1)

Aritz
Aritz

Reputation: 31649

Your query looks too dynamic to be understood by the Spring Data JPA annotation. In fact, the workaround you're trying won't be valid as you're trying to inject SQL into the param fields, so the framework will stop you from doing that in order to prevent SQL injection.

You can add custom behaviour to your repository and implement a prepared statement (see how to grab the session from the EntityManager here). Basically:

public class MyRepositoryImpl<SiteDTO, ID extends Serializable>
  extends SimpleJpaRepository<SiteDTO, ID> implements MyRepository<SiteDTO, ID> {

  private EntityManager entityManager;

  // There are two constructors to choose from, either can be used.
  public MyRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
    super(domainClass, entityManager);

    // This is the recommended method for accessing inherited class dependencies.
    this.entityManager = entityManager;
  }

  public List<SiteDTO> executeSiteQuery(List<String> longitudesToMatch, List<String> latitudesToMatch) {
    // Here you can grab the session from the entity manager and create your own query
  }
}

Otherwise, if you see it as an overkill, you might consider doing the or part of your query in your Spring Service. I mean, with a query like this public List<SiteDTO> executeSiteQuery(@Param("latitude") String latitude, @Param("longitude") String longitude); and invoking it multiple times. Less performant, but sometimes simplicity pays.

Upvotes: 2

Related Questions