How it works

1. The stack

• Spring Boot 3.5 on Java 21 - web server, dependency injection, configuration.
• Spring MVC + Thymeleaf - controllers return a template name; HTML is rendered on the server.
• Spring Data JPA (Hibernate) - entities and repositories; most SQL is generated.
• SQL Server 2022 Express - database ImportExportLabDB, login iel_app.
• Flyway - db/migration/V1__schema.sql creates the tables on first start.
• Apache POI (Excel, Word), OpenPDF (PDF), Commons CSV, Jackson (JSON), JDK StAX/DOM (XML).

2. Layers

Controllers only read the request and choose a page. Services hold the rules. Repositories talk to the database. Keeping these apart makes each part small and testable.

3. Packages

com.example.ielab
├─ product/    Product entity, ProductRepository, ProductFilter (JPA Specification), ProductField
├─ export/     ExportService, ExportRequest, ExportTemplate, ExportLog
│  └─ format/  ExcelExporter, CsvExporter, PdfExporter, WordExporter, JsonExporter, XmlExporter, TextExporter
├─ importer/   ImportService, ImportValidator, HeaderMapper, ImportReports, SampleFiles
│  └─ parser/  ExcelRowParser, CsvRowParser, JsonRowParser, XmlRowParser
├─ schedule/   ScheduledExport, ScheduledExportRunner (@Scheduled), ScheduleService (cron)
├─ mail/       MailService (SMTP or .eml outbox), EmailLog
├─ common/     DataDirs (files on disk), DemoDataSeeder
└─ web/        controllers and form objects

4. Feature map

71	Export to Excel	ExcelExporter - styles, number formats, SUM() formula, auto filter, frozen header
72	Export to CSV	CsvExporter - UTF-8 with BOM so Excel shows any language
73	Export to PDF	PdfExporter - landscape table, header repeats on every page, page numbers via a page event
74	Export to Word	WordExporter - XWPF table with shading and a repeating header row
75	Export to JSON	JsonExporter - streaming JsonGenerator, numbers stay numbers
76	Export to XML	XmlExporter - StAX XMLStreamWriter escapes text for you
77	Export to TXT	TextExporter - fixed-width table or a {sku} - {name} line pattern
78	Selected rows	app.js keeps ticked ids in sessionStorage; ExportService.findRows uses id IN (...)
79	Filtered rows	ExportRequest extends ProductFilter - the same Specification builds the grid and the export
80	Custom template	ExportTemplate (columns, headers, title, footer, TXT pattern) + TemplateController
81	Scheduled export	ScheduledExportRunner polls the table; CronExpression.next() sets the next run
82	E-mail export	MailService - MimeMessageHelper with an attachment; no SMTP = .eml in the outbox
83	Import Excel	ExcelRowParser - finds the header row, reads numbers without losing precision
84	Import CSV	CsvRowParser - removes the BOM, guesses comma / semicolon / tab
85	Import JSON	JsonRowParser - array or {"rows": [...]}
86	Import XML	XmlRowParser - DOM with DOCTYPE disabled (blocks XXE attacks)
87	Validation	ImportValidator - one set of rules for every format; errors block, warnings pass
88	Preview	Rows are saved in import_rows as JSON with their problems; nothing touches products yet
89	Error report	ImportReports - Excel/CSV with row, field, problem and the original values
90	History	import_batches - counts, mode, status, timings

5. Ideas worth learning

Strategy pattern with Spring

Every exporter implements one interface. Spring injects them all as a List, and the service picks one by format:

public ExportService(List<Exporter> exporterBeans, ...) {
    exporterBeans.forEach(e -> exporters.put(e.format(), e));
}
...
exporters.get(req.getFormat()).write(doc, out);

To add a new format (say Markdown) you write one new @Component class - nothing else changes.

Dynamic filters with JPA Specifications

Specification<Product> spec = (root, query, cb) -> cb.and(
    cb.equal(root.get("category"), "Toys"),
    cb.lessThanOrEqualTo(root.get("stock"), 10));
productRepository.findAll(spec, Sort.by("sku"));

Empty filter fields are simply left out, so one method handles every combination.

Preview first, write later

An import is two requests. The upload only reads, checks and stores the rows. The confirm button runs again inside one @Transactional method and re-checks the rows, because the database may have changed in between. If anything fails, SQL Server rolls back every row.

SQL Server details

• 2,100 parameters per statement. WHERE sku IN (?, ?, ...) uses one per value, so the import looks SKUs up 1,000 at a time.
• NVARCHAR for Unicode. Columns are NVARCHAR and hibernate.type.prefer_nationalized_strings=true sends Java strings as NVARCHAR.
• IDENTITY keys (GenerationType.IDENTITY): Hibernate cannot batch these inserts, so the 5,000 preview rows are written with JdbcTemplate.batchUpdate.
• DECIMAL(12,2) for money - never float. In Java it is BigDecimal.

Scheduling stored in the database

@Scheduled(cron = "...") is fixed at compile time. Here a small runner checks every 15 seconds WHERE enabled = 1 AND next_run_at <= now, so users can add, pause and edit jobs while the app runs.

6. Look at the data in SQL Server

sqlcmd -S localhost\SQLEXPRESS -E -d ImportExportLabDB -Q "SELECT TOP 5 sku, name, price, stock FROM products ORDER BY updated_at DESC"
sqlcmd -S localhost\SQLEXPRESS -E -d ImportExportLabDB -Q "SELECT id, file_name, status, total_rows, error_rows FROM import_batches"
sqlcmd -S localhost\SQLEXPRESS -E -d ImportExportLabDB -Q "SELECT version, description, success FROM flyway_schema_history"

7. Exercises

1. Add a Markdown export: a new ExportFormat value and one Exporter class.
2. Add a "barcode" column: a Flyway V2__barcode.sql, a field in Product and ProductField, a rule in ImportValidator.
3. Make the import also delete products that are missing from the file (a third mode).
4. Export a second sheet in Excel with totals per category (GROUP BY in a repository query).
5. Write a unit test for a new validation rule in ImportValidatorTest.