Posted In: Spring, Spring Boot, Spring Data

Spring Boot + Spring Data JPA + MySQL + Tomcat – Complexity1

Technologies used

1. Spring Boot 1.5.6

2. Spring data commons 1.13.6

3. Spring data jpa 1.11.6

4. MySQL connector 5.1.43

5. Tomcat JDBC 8.5.16

Steps

1. Create Spring Boot starter project

2. Verify maven pom.xml to have spring-boot-starter, spring-boot-starter-data-jpa, mysql-connector-java, spring-boot-starter-test

3. Add application properties

4. Map the POJO properties with Database table columns

5. Implement CrudRepository interface

6. Test User Repository

1a. Create Spring Boot starter project

1b. Create MySQL table

CREATE TABLE user_master (
  USER_ID int(10) NOT NULL AUTO_INCREMENT,
  USER_LOGIN_ID varchar(20) DEFAULT NULL,
  USER_PASSWORD varchar(50) DEFAULT NULL,
  FIRST_NM varchar(50) DEFAULT NULL,
  LAST_NM varchar(50) DEFAULT NULL,
  DT_OF_BIRTH date DEFAULT NULL,
  EMAIL_ID varchar(50) DEFAULT NULL,
  PRIMARY KEY (USER_ID)
)

2. Verify maven pom.xml

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

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

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

3. Add application properties

spring.datasource.url=jdbc:mysql://localhost:3306/javausecase_db
spring.datasource.username=root
spring.datasource.password=pass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.tomcat.max-wait=10000
spring.datasource.tomcat.max-active=50
spring.datasource.tomcat.test-on-borrow=true

4. Map the POJO properties with Database table columns

@Entity – A JPA entity class is a POJO (Plain Old Java Object) class, i.e. an ordinary Java class that is marked (annotated) as having the ability to represent objects in the database.

@Table – Database table name

@Id – Primary key column

@GeneratedValue – The annotation is to configure the way of increment of the specified column

@Column- Database column name

package com.example.springapp;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "user_master")
public class User implements Serializable {
	private static final long serialVersionUID = 65007985219089594L;

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long userId;
	@Column(name = "USER_LOGIN_ID")
	private String userLoginId;
	@Column(name = "USER_PASSWORD")
	private String userPassword;
	@Column(name = "FIRST_NM")
	private String firstName;
	@Column(name = "LAST_NM")
	private String lastName;
	@Column(name = "DT_OF_BIRTH")
	private Date dateofBirth;
	@Column(name = "EMAIL_ID")
	private String emailId;
        
        //GETTERS AND SETTERS .....
}

5. Implement CrudRepository interface

package com.example.springapp;

import java.util.Date;
import java.util.List;
import java.util.stream.Stream;

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

public interface UserRepository extends CrudRepository<User, Long> {
	List<User> findByEmailId(String emailId);

	List<User> findByDateofBirth(Date date);

	@Query("select u from User u where u.firstName = :firstName")
	Stream<User> findByFirstNameReturnStream(@Param("firstName") String firstName);
}

6. Test User Repository

package com.example.springapp;

import java.text.SimpleDateFormat;
import java.util.stream.Stream;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringRunner.class)
@SpringBootTest
@AutoConfigureMockMvc
public class UserRepositoryTest {

	private static final Logger LOGGER = LoggerFactory.getLogger(UserRepositoryTest.class);

	@Test
	public void contextLoads() {
	}

	@Autowired
	UserRepository userRepository;

	@Test
	@Transactional(readOnly = true)
	public void testUserRepository() throws Exception {
		LOGGER.debug("Running testUserRepository()....");

		LOGGER.debug("\n\nfindByAll()");
		for (User user : userRepository.findAll()) {
			LOGGER.debug(user.toString());
		}

		LOGGER.debug("\n\nfindByEmailId(String emailId)");
		for (User user : userRepository.findByEmailId("abh@as.com")) {
			LOGGER.debug(user.toString());
		}

		LOGGER.debug("\n\nfindByDateofBirth(Date date)");
		for (User user : userRepository.findByDateofBirth(
				new SimpleDateFormat("yyyy-MM-dd").parse("1976-09-09"))) {
			LOGGER.debug(user.toString());
		}

		LOGGER.debug("\n\nfindByEmailReturnStream(@Param(firstName) String firstName)");
		try (Stream<User> stream = userRepository.findByFirstNameReturnStream("Abhijit1")) {
			stream.forEach(x -> LOGGER.debug(x.toString()));
		} catch (Exception e) {
			LOGGER.error(e.getMessage(), e);
		}
	}
}

Transaction is started for Stream stream = userRepository.findByFirstNameReturnStream

org.springframework.test.context.transaction.TransactionContext|101|
startTransaction|Began transaction (1) for test context 
.......
.......
org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@6dde5c8c], 
contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader'
rollback [true]

Possible InvalidDataAccessApiUsageException error. This is due to @Transactional. Stream requires transaction.

org.springframework.dao.InvalidDataAccessApiUsageException: 
You're trying to execute a streaming query method without a surrounding transaction 
that keeps the connection open so that the Stream can actually be consumed. Make sure the code consuming 
the stream uses @Transactional or any other way of declaring a (read-only) transaction.

 

 

by , on July 27th, 2017

  • Categories