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

Question

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) } }

Community · Accepted Answer · 2017-05-23 12:34:11Z

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.

Will I be able to get the EntityManager while using Spring Boot because we are not configuring this at all. It's all abstracted and handled by Spring.
@jDub9 that's the best part of Spring Boot, you can get elements created and autoconfigured by the framework and then use them in your custom classes, if you want.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related