Below is a Spring Boot example that demonstrates Export of data to CSV by processing records in two ways with and without library.
- A
count()query to get the total number of records. - Paging (
PageRequest) to fetch records batch-by-batch. - Writing directly to the response’s
OutputStreamto avoid holding all records in memory.
✅ Assumptions
- You have an entity called
Personwithid,name, andemail. - You’re using Spring Data JPA and Spring Web.
📁 1. Entity Example
@Entity
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String email;
// Getters and setters
}
📁 2. Repository
public interface PersonRepository extends JpaRepository<Person, Long> {
}
📁 3. Service Layer for Batch Processing
@Service
public class CsvExportService {
private final PersonRepository personRepository;
public CsvExportService(PersonRepository personRepository) {
this.personRepository = personRepository;
}
public void exportToCsv(OutputStream outputStream, int batchSize) throws IOException {
long total = personRepository.count();
long pages = (total + batchSize - 1) / batchSize;
try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(outputStream));
CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
.withHeader("ID", "Name", "Email"))) {
for (int page = 0; page < pages; page++) {
Page<Person> persons = personRepository.findAll(PageRequest.of(page, batchSize));
for (Person person : persons) {
csvPrinter.printRecord(person.getId(), person.getName(), person.getEmail());
}
csvPrinter.flush(); // Optional: write each batch to output
}
}
}
}
🔸 Uses Apache Commons CSV (
org.apache.commons.csv.CSVPrinter). Add it to yourpom.xml:
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.10.0</version>
</dependency>
📁 4. Controller
@RestController
@RequestMapping("/api/export")
public class ExportController {
private final CsvExportService csvExportService;
public ExportController(CsvExportService csvExportService) {
this.csvExportService = csvExportService;
}
@GetMapping("/persons")
public void exportCsv(HttpServletResponse response,
@RequestParam(defaultValue = "100") int batchSize) throws IOException {
response.setContentType("text/csv");
response.setHeader("Content-Disposition", "attachment; filename=\"persons.csv\"");
csvExportService.exportToCsv(response.getOutputStream(), batchSize);
}
}
✅ Benefits
- Memory-efficient: Only processes one batch at a time.
- Scalable: Works well even for large datasets.
- Streamed Output: Client starts receiving data immediately.
Would you like a version that exports to a file instead of an HTTP response?
Without external library
Here’s a complete solution without using any external libraries like Apache Commons CSV.
This should be used with caution. You need to be sure that your records are safe (no commas, quotes, or newlines inside fields). You can just write plain comma-separated lines manually.
✅ Key Points
- Uses
BufferedWriterto write directly toOutputStream. - No external dependencies.
- Still paginates records to avoid memory issues.
📁 1. Service
@Service
public class CsvExportService {
private final PersonRepository personRepository;
public CsvExportService(PersonRepository personRepository) {
this.personRepository = personRepository;
}
public void exportToCsv(OutputStream outputStream, int batchSize) throws IOException {
long total = personRepository.count();
long pages = (total + batchSize - 1) / batchSize;
try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(outputStream))) {
// Write CSV header
writer.write("ID,Name,Email");
writer.newLine();
for (int page = 0; page < pages; page++) {
Page<Person> persons = personRepository.findAll(PageRequest.of(page, batchSize));
for (Person person : persons) {
writer.write(String.format("%d,%s,%s",
person.getId(),
person.getName(),
person.getEmail()));
writer.newLine();
}
writer.flush(); // Ensure each batch is flushed to client
}
}
}
}
📁 2. Controller
@RestController
@RequestMapping("/api/export")
public class ExportController {
private final CsvExportService csvExportService;
public ExportController(CsvExportService csvExportService) {
this.csvExportService = csvExportService;
}
@GetMapping("/persons")
public void exportCsv(HttpServletResponse response,
@RequestParam(defaultValue = "100") int batchSize) throws IOException {
response.setContentType("text/csv");
response.setHeader("Content-Disposition", "attachment; filename=\"persons.csv\"");
csvExportService.exportToCsv(response.getOutputStream(), batchSize);
}
}
✅ Notes
- You can adjust
batchSizevia query param:GET /api/export/persons?batchSize=500 - Writing with
BufferedWriterandflush()avoids memory pressure and keeps output stream moving. - No extra escaping is done — this assumes your fields do not contain commas or newlines.
Pagination Variation
Switching from PageRequest (which uses page number + size) to manual offset and limit (i.e., offset = batchSize * batchIndex) requires writing a custom query in your repository — because Spring Data JPA doesn’t support offset/limit out-of-the-box unless paging is used.
✅ Step-by-step: Export with OFFSET + LIMIT
📁 1. Modify Repository
Add a custom query with @Query and @Param annotations:
public interface PersonRepository extends JpaRepository<Person, Long> {
@Query("SELECT p FROM Person p ORDER BY p.id ASC")
List<Person> fetchBatch(@Param("offset") int offset, @Param("limit") int limit);
@Query(value = "SELECT * FROM person ORDER BY id ASC LIMIT :limit OFFSET :offset", nativeQuery = true)
List<Person> fetchBatchNative(@Param("offset") int offset, @Param("limit") int limit);
}
⚠️ Use the native query if you’re targeting a real SQL
OFFSET+LIMITpattern.
Otherwise, JPQL doesn’t support those directly, so the first version (fetchBatch) may not work depending on your JPA provider.
📁 2. Updated Service with Offset & Limit
@Service
public class CsvExportService {
private final PersonRepository personRepository;
public CsvExportService(PersonRepository personRepository) {
this.personRepository = personRepository;
}
public void exportToCsv(OutputStream outputStream, int batchSize) throws IOException {
long total = personRepository.count();
try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(outputStream))) {
// Header
writer.write("ID,Name,Email");
writer.newLine();
for (int offset = 0; offset < total; offset += batchSize) {
List<Person> batch = personRepository.fetchBatchNative(offset, batchSize);
for (Person person : batch) {
writer.write(String.format("%d,%s,%s",
person.getId(),
person.getName(),
person.getEmail()));
writer.newLine();
}
writer.flush(); // Optional: push to client
}
}
}
}
📁 3. Controller – no change
Same as before:
@GetMapping("/persons")
public void exportCsv(HttpServletResponse response,
@RequestParam(defaultValue = "100") int batchSize) throws IOException {
response.setContentType("text/csv");
response.setHeader("Content-Disposition", "attachment; filename=\"persons.csv\"");
csvExportService.exportToCsv(response.getOutputStream(), batchSize);
}
✅ Summary
LIMIT/OFFSETlogic avoids page calculation confusion.- More SQL-like and often easier to control in databases.
- Native SQL is required for true
LIMIT :limit OFFSET :offsetsupport.