Spring JPA, Native Query, POJO, and Java Stream

Spring JPA can return a stream of objects in its repository API. This is a very good feature. It will help to resolve the performance issue when millions of data is returned from the database by a query. It seems to me that Spring JDBC template does not have this ability. The JdbcTemplate has a result set callback function. But that does not seem to achieve the same thing as a stream can do.

An issue with using the ORM is that the many domain data classes need to map to tables. For a legacy system, the easiest way is to use POJO and native query so that such a map is not needed. In this example application, I use POJO and native query in JPA to return a stream of the POJO object.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>spring-stream-sample</groupId>
	<artifactId>spring-stream-sample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>spring-boot-tutorial-basics</name>
	<description>Spring Boot Tutorial - Basic Concept Examples</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.1.7.RELEASE</version>
		<relativePath />
	</parent>


	<properties>
		<hibernate.version>5.4.0.Final</hibernate.version>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<!-- <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> 
			</dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-jpamodelgen</artifactId> 
			</dependency> -->

		<!-- https://mvnrepository.com/artifact/javax.persistence/javax.persistence-api -->
		<dependency>
			<groupId>javax.persistence</groupId>
			<artifactId>javax.persistence-api</artifactId>

		</dependency>


		<dependency>
			<groupId>com.oracle.database.jdbc</groupId>
			<artifactId>ojdbc8</artifactId>
			<version>12.2.0.1</version>
		</dependency>


		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>

		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

	<repositories>
		<repository>
			<id>spring-snapshots</id>
			<name>Spring Snapshots</name>
			<url>https://repo.spring.io/snapshot</url>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
		</repository>
		<repository>
			<id>spring-milestones</id>
			<name>Spring Milestones</name>
			<url>https://repo.spring.io/milestone</url>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</repository>
	</repositories>

	<pluginRepositories>
		<pluginRepository>
			<id>spring-snapshots</id>
			<name>Spring Snapshots</name>
			<url>https://repo.spring.io/snapshot</url>
			<snapshots>
				<enabled>true</enabled>
			</snapshots>
		</pluginRepository>
		<pluginRepository>
			<id>spring-milestones</id>
			<name>Spring Milestones</name>
			<url>https://repo.spring.io/milestone</url>
			<snapshots>
				<enabled>false</enabled>
			</snapshots>
		</pluginRepository>
	</pluginRepositories>


</project>

App.java

  package com.sample.app;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.ComponentScan;

@SpringBootApplication(scanBasePackages = { "com.sample.app" })
@ComponentScan({ "com.sample.app" })
@EnableConfigurationProperties
@EnableAutoConfiguration
public class App {

	public static void main(String[] args) {
		SpringApplication.run(App.class, args);
	}
}
  

SampleController.java

  package com.sample.app.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.Date;
import java.util.stream.Stream;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.sample.app.model.ShipmentModel;
import com.sample.app.repository.SampleRepository;

@RestController
@RequestMapping(value = "/sample")
public class SampleController {

	@PersistenceContext
	EntityManager entityManager;

	@Autowired
	private SampleRepository sampleRepository;

	@GetMapping(value = "/shipmentModel/stream/csv/{userId}")
	@Transactional(readOnly = true)
	public void generateCSVUsingStreamShipmentModel(@PathVariable("userId") long userId,
			HttpServletResponse response) {
		response.addHeader("Content-Type", "application/csv");
		response.addHeader("Content-Disposition", "attachment; filename=shipmentModel.csv");
		response.setCharacterEncoding("UTF-8");
		System.out.println("Start working. Time is " + new Date());
		try (Stream shipmentsStream = sampleRepository.findShipmentModels(userId);) {
			PrintWriter out = response.getWriter();
			// print column header
			out.write(String.join(",", ShipmentModel.headers));
			out.write("\n");
			shipmentsStream.forEach(shipmentModel -> {
				out.write(shipmentModel.toString());
				out.write("\n");
				entityManager.detach(shipmentModel);
			});
			System.out.println("Before flush. Time is " + new Date());
			out.flush();
			out.close();

			shipmentsStream.close();
			System.out.println("Stream closed. Time is " + new Date());
		} catch (IOException ix) {
			throw new RuntimeException("There is an error while downloading shipments.csv", ix);
		}

	}
}
  

ShipmentModel.java This is the POJO class.

  package com.sample.app.model;

import javax.persistence.Column;
import javax.persistence.ColumnResult;
import javax.persistence.ConstructorResult;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;

/*
 * Notes:
 * 1. This is a POJO. It does not map to any database table. It is annotated as an Entity. But there is no annotation @Table for it.
 * 2. The query is a pure native query. It does not use any entity mapping.
 * 3. The critical connection between the query result set and the POJO is the @CoumnResult annotation. In that annotation, the "name"
 * attribute must exactly match the column name returned by the native query. After that, the POJO uses its constructor to get the value.
 * In the constructor, the position of the argument determines what a property of the POJO will be assigned the column value. The name
 * of the property does not matter. For example, the property "shipmentLabel" of the class ShipmentModel can be any name.  This is different
 * from the entity class that maps to an actual table. In that case, there can an implicit mapping between the property name and 
 * column name of the table. For example, the camel case property name shipmentLabel will map to the table column name shipment_label.
 * 
 */

@NamedNativeQuery(name = "findShipmentModels", query = ShipmentModel.sampleSql_native, resultClass = ShipmentModel.class, resultSetMapping = "ShipmentModelMapping")

@SqlResultSetMapping(name = "ShipmentModelMapping", classes = {
		@ConstructorResult(targetClass = ShipmentModel.class, columns = { @ColumnResult(name = "id", type = Long.class),
				@ColumnResult(name = "username", type = String.class),
				@ColumnResult(name = "label", type = String.class)

		}) })

@Entity
public class ShipmentModel {

	public static String[] headers = { "userId", "userName", "label" };

	public static void main(String[] args) {
		System.out.println(String.join(",", headers));
	}

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(updatable = false)
	private Long userId;

	private String userName;

	private String shipmentLabel;

	public ShipmentModel(Long userId, String userName, String label) {
		this.userId = userId;
		this.userName = userName;
		this.shipmentLabel = label;
	}

	public Long getUserId() {
		return userId;
	}

	public void setUserId(Long userId) {
		this.userId = userId;
	}

	public String getUserName() {
		return userName;
	}

	public void setUserName(String userName) {
		this.userName = userName;
	}

	public String getShipmentLabel() {
		return shipmentLabel;
	}

	public void setShipmentLabel(String shipmentLabel) {
		this.shipmentLabel = shipmentLabel;
	}

	@Override
	public String toString() {
		return String.join(",", "" + this.getUserId(), "" + this.getUserName(), "" + this.shipmentLabel);
	}

	final static String sampleSql_native = " SELECT user.*, shipment.label "
			+ " from user JOIN shipment ON user.id = shipment.user_id " + " WHERE user.id = :userId";

}

  

SampleRepository.java

  package com.sample.app.repository;

import java.util.stream.Stream;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.sample.app.model.ShipmentModel;

public interface SampleRepository extends JpaRepository {

	@Query(nativeQuery = true, name = "findShipmentModels")
	public Stream findShipmentModels(@Param("userId") Long userId);

}

  

application.properties in main/resources

  
server.port=9123

server.servlet.context-path=/api

spring.datasource.url=jdbc:mysql://localhost:3306/planet
spring.datasource.username=zach
spring.datasource.password=planet
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
logging.level.org.hibernate.type=trace

spring.jpa.properties.hibernate.type=trace

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
  

create.sql in main/resources

 CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `status` tinyint(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10001 ;


CREATE TABLE IF NOT EXISTS `shipment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `label` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  
  FOREIGN KEY (user_id) REFERENCES user(id)
 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10001 ;

 INSERT INTO `user` (`id`, `username`, `first_name`, `last_name`, `gender`, `password`, `status`) VALUES
(1, 'rogers6', 'rogers', 'miller', 'M', 'psqwer', 1),
(2, 'rogpa', 'rogers', 'paul', 'M', 'dfgderet', 1),
(3, 'davm', 'david', 'merz', 'M', 'wetwet', 1),
(4, 'ma', 'mary', 'sanders', 'M', 'dfghsgh', 1),
(5, 'jhojho', 'jhonson', 'johnson', 'F', '45yhererh', 1),
(6, 'dansc', 'daniel', 'scott', 'F', 'gfndftyet', 1);


INSERT INTO `shipment` (`id`, `user_id`, `label`) VALUES
(1, '2', 'heavy box'),
(2, '1', 'interseting work'),
(3, '3', 'stones'),
(4, '3', 'computers'),
(5, '4', 'Green pepper'),
(6, '5', 'tables');

 

readme.txt

Reference Source: 
https://github.com/greyseal/spring-boot-csv-download

To run the application, the MySQL database needs to be started first. Open Windows task manager and 
go to the service tab. Find "MYSQL57" and start it. 

Question: Where in the application is it configured to use MySQL?
Answer: It is configured in the file application.properties. 

To test shipment csv download, use the following url:

http://localhost:9123/api/sample/shipmentModel/stream/csv/{userId}

In the above, {userId} is the user Id. Example:
http://localhost:9123/api/sample/shipmentModel/stream/csv/3