Which is fastest way to fetch large data's from database in spring boot Application

I'm currently working on spring boot project. In my project i am using criteria builder for fetch data from postgres database its work fine for smaller data. But didn't work for larger data.so I decided to move to an other options ( JPQL, jdbc template). Could you give suggestions which is fastest way to fetch data in spring boot

( **JPQL, jdbc template, raw query **). Could you give suggestions which is fastest way to fetch data in spring boot

Here are the best approaches for handling large datasets in Spring Boot, ordered by performance:

1. JdbcTemplate (Fastest)

   • Provides the best performance as it's closest to the database
   • Allows direct control over fetch size and connection parameters
   • Best for very large datasets (millions of records)
   • No entity mapping overhead
   • Drawback: Manual mapping required

2. Native Queries with Streaming

   • Nearly as fast as JdbcTemplate
   • Allows SQL optimization specific to your database
   • Supports cursor-based fetching
   • Good balance between performance and convenience

3. JPQL with Streaming

   • Decent performance for large datasets
   • More maintainable than raw SQL
   • Database-agnostic
   • Supports lazy loading
   • Slightly slower than native queries due to mapping overhead

4. Pagination

   • Good for presenting data to users
   • Consistent memory usage
   • Works well with REST APIs
   • Can be slower for processing entire datasets as it requires multiple queries

Key recommendations for optimizing large data fetches:

1. Set appropriate fetch size (typically 100-1000)
2. Use read-only transactions when possible
3. Stream results rather than loading everything into memory
4. Consider pagination for user-facing operations
5. Use proper indexes on your database
6. Select only required columns rather than fetching entire entities

Some code examples // 1. Using JdbcTemplate with Stream

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void processLargeData() {
        String sql = "SELECT * FROM users WHERE active = true";
        
        jdbcTemplate.setFetchSize(1000); // Set appropriate fetch size
        jdbcTemplate.query(
            sql,
            resultSet -> {
                while (resultSet.next()) {
                    // Process each row here
                    User user = User.builder()
                        .id(resultSet.getLong("id"))
                        .name(resultSet.getString("name"))
                        .build();
                    processUser(user);
                }
            }
        );
    }
}

// 2. Using JPA/JPQL with Streaming

@Service
public class UserJpaService {
    @Autowired
    private EntityManager entityManager;
    
    @Transactional(readOnly = true)
    public void streamUsers() {
        String jpql = "SELECT u FROM User u WHERE u.active = true";
        
        try (Stream<User> userStream = entityManager.createQuery(jpql, User.class)
                .setHint(QueryHints.FETCH_SIZE, 1000)
                .getResultStream()) {
            
            userStream.forEach(this::processUser);
        }
    }
}

// 3. Using Native Query with Cursor

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Query(value = "SELECT * FROM users WHERE active = true", 
           nativeQuery = true)
    @QueryHints(value = {
        @QueryHint(name = JDBC_FETCH_SIZE, value = "1000"),
        @QueryHint(name = READ_ONLY, value = "true")
    })
    Stream<User> streamAllUsersWithCursor();
}

// 4. Using Pagination

@Service
public class UserPaginationService {
    @Autowired
    private UserRepository userRepository;
    
    public void processUsersInBatches() {
        int pageSize = 1000;
        int pageNumber = 0;
        Page<User> userPage;
        
        do {
            userPage = userRepository.findAll(PageRequest.of(pageNumber, pageSize));
            processUserBatch(userPage.getContent());
            pageNumber++;
        } while (userPage.hasNext());
    }
}