Three Ways to Perform Queries in Spring Boot

Spring Data JPA provides three powerful methods to perform SELECT queries: standard repository methods, @Query annotations, and custom queries with EntityManager. Each approach offers unique advantages and built-in protections against SQL Injection. Below, I’ll demonstrate these using a Product entity, ensuring secure and efficient queries.

Standard Repository Methods

Spring Data JPA repositories provide built-in methods and allow custom queries through method naming conventions, with automatic query generation.

Example: Fetch products by category or name.

import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;

public interface ProductRepository extends JpaRepository<Product, Long> {
    List<Product> findByCategory(String category);
    List<Product> findByNameContainingIgnoreCase(String name);
}

Usage in Service

@Service
public class ProductService {
    @Autowired
    private ProductRepository productRepository;

    public List<Product> getProductsByCategory(String category) {
        return productRepository.findByCategory(category);
    }
}

Spring Data JPA uses prepared statements, binding inputs like category as parameters. This ensures malicious inputs (e.g., "; DROP TABLE products; --") are treated as data, not executable SQL, preventing injection attacks.

Using @Query Annotation

The @Query annotation enables custom JPQL or native SQL queries with explicit control over the query structure.

import com.example.demo.model.Product;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = "SELECT * FROM Product WHERE stock < :stock", nativeQuery = true)
    List<Product> findLowStockProducts(@Param(value = "stock") Integer stock);
}

Usage in Service

public List<Product> getExpensiveProducts(Double price) {
    return productRepository.findProductsAbovePrice(price);
}

Custom Queries with EntityManager

EntityManager is ideal for dynamic queries where conditions vary based on runtime inputs.

Example: Dynamically filter products by category and price.

import com.example.demo.model.Product;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.TypedQuery;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class CustomQuery {

    @PersistenceContext
    private EntityManager entityManager;

    public List<Product> findProductsByCriteria(String category, Double minPrice) {
        StringBuilder jpql = new StringBuilder("SELECT p FROM Product p WHERE 1=1");
        List<Object> params = new ArrayList<>();

        if (category != null) {
            jpql.append(" AND p.category = ?1");
            params.add(category);
        }
        if (minPrice != null) {
            jpql.append(" AND p.price >= ?").append(params.size() + 1);
            params.add(minPrice);
        }

        TypedQuery<Product> query = entityManager.createQuery(jpql.toString(), Product.class);
        for (int i = 0; i < params.size(); i++) {
            query.setParameter(i + 1, params.get(i));
        }

        return query.getResultList();
    }
}

Key Practices to Avoid SQL Injection

• Use parameter binding: Always use prepared statements via Spring’s mechanisms or setParameter.
• Avoid string concatenation: Never embed user input directly into queries.
• Validate inputs: Check inputs (e.g., ensure minPrice is a number) for added safety.
• Limit database permissions: Restrict database roles to minimize damage from potential vulnerabilities.