Spring Data JDBC Examples

In this quick tutorial we will walkthrough a Spring Data JDBC Example with Spring Boot to demonstrate Object Mappings and configurations, and testing JDBC repositories, CRUD operations using Rest Controllers and Unit tests.

At the end of this tutorial you will find more Spring Data JDBC examples that we have provided.

1. Maven Dependency Configurations

1.1. Used Technologies :

1. Spring Boot 2.3.0.RELEASE
2. Spring Data JDBC 2.0.0.RELEASE
3. Spring Framework 5.2.6.RELEASE
4. H2 / MySql DB
5. Lombok 1.18.12
6. JUnit 5

1.2. Maven Dependencies

To start working with Spring Boot with Spring Data JDBC you need dependency spring-boot-starter-data-jdbc. Use Lombok to avoid boiler plate code. Here is complete dependency list used in the example application.

        <dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-validation</artifactId>
		</dependency>
		<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</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>

2. Spring Boot Configuration Properties

Here is complete configuration properties in application.properties

## webserver port mapping, default is 8080
server.port=8181
## custom root context, default is application name
server.servlet.context-path=/usermanager
server.error.whitelabel.enabled=false

## H2 DB properties
spring.datasource.url=jdbc:h2:mem:jpa_jbd
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

## Mysql DB properties
#spring.datasource.url=jdbc:mysql://localhost:3306/jpa_jbd?useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC&useSSL=false
#spring.datasource.username=root
#spring.datasource.password=password
#spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect


## Enables H2 console (A simple web interface to access H2 DB)
spring.h2.console.enabled=true

## custom H2 console url, Default path is 'h2-console'
spring.h2.console.path=/h2

## to Json Pretty print in browser
spring.jackson.serialization.indent-output=true

3. Model Mapping

When you are working with Spring Data JDBC, you’ll need to create entities that mapped to the Data Base table. To create Entities the class should follow the aggregate design of Domain Driven Design (DDD) and Entity Naming Strategy.

Following is simple mapping between User Table and User Entity.

3.1. User Table Structure :

      CREATE TABLE `USER` (
		`ID` int(11) NOT NULL AUTO_INCREMENT,
		`USER_NAME` varchar(45) NOT NULL,
		`PASSWORD` varchar(45) NOT NULL,
		`CREATED_TIME` datetime NOT NULL,
		`UPDATED_TIME` datetime DEFAULT NULL,
		`USER_TYPE` varchar(45) NOT NULL,
		`DOB` date NOT NULL,
		PRIMARY KEY (`ID`),
		UNIQUE KEY `USER_NAME_UNIQUE` (`USER_NAME`)
	);

3.2. Entity Mapping for User Table :

1. Lombok @Data is used to avoid generating boiler plate code (setters/getters, toString, hashcode, equals methods and RequiredArgsConstructor).
2. Entity class name and DB table name should be same, otherwise map table name with @Table at class level like @Table("USER_TABLE") if table name “USER_TABLE”.
3. To map DB Table identifier to the entity field, it must be annotated with @Id.
4. Table Column name and field name must be same for auto mapping otherwise use @Column annotation to map fields to the DB Table column.
5. To ignore fields to not persist in DB use Spring @Transient annotation.

@Data // lomok
public class User {

	@Id
	private Long id;
	private String userName;
	private String password;
	private Date createdTime;
	private Date updatedTime;
	@Column("DOB") // to map db column if property not same as column name
	private Date dateofBirth;
	private UserType userType; // Enum Type

	@org.springframework.data.annotation.Transient // to not persist into DB (just to expose to view/client)
	private String dateOfBirthString;
	
	// to display on view
	public String getDateOfBirthString() {
		return this.dateofBirth.toString();
	}
}

public enum UserType {
	
	EMPLOYEE, STUDENT;
}

4. Create a JDBC Repository

Spring Data JDBC 2.x.x supports CrudRepository, PagingAndSortingRepository to make it possible to work with most common DB operations. In this tutorial we have used CrudRepository.

@Repository
public interface UserRepository extends CrudRepository<User, Long>{

}

5. Test the configurations and Mapping

Implemented a Rest Controller and JUnit 5 tests to access User table data from DB. First Let’s have look in to unit tests.

5.1. JUnit5 Test Case to test CRUD operations :

@SpringBootTest
@TestMethodOrder(value = OrderAnnotation.class) // to run tests in order
public class Basic_Mapping_Test extends BaseTest {

	@Autowired
	private UserRepository userRepository;
	
	@Test
	@DisplayName("Create-User-Test ")
	@Order(1)
	void createUserTest() {
		
		User created = userRepository.save(getUser());
		
		assertTrue(created != null && created.getId() != null);
	}
	
	@Test
	@DisplayName("Update-User-Test ")
	@Order(2)
	void updateUserTest() {
		
		// Read all users
		List<User> allUsers = (List<User>) userRepository.findAll();
		
		allUsers.forEach(user -> {
			user.setPassword("ABC123abc#");
			User updated = userRepository.save(user);
			
			assertTrue(updated.getPassword().equals("ABC123abc#"));
		});
		
		// userRepository.saveAll(allUsers); // batch update
	}
	
	@Test
	@DisplayName("Delete-User-Test ")
	@Order(3)
	void deleteUserTest() {
		
		// Read all users
		List<User> allUsers = (List<User>) userRepository.findAll();
		
		allUsers.forEach(user -> {
			userRepository.delete(user);
			
			assertTrue(userRepository.findById(user.getId()).isEmpty());
		});
		
		// userRepository.deleteAll(allUsers); //batch delete
	}
}

5.2. Rest Controller to access User Data :

A Spring REST controller to access CRUD operations using Spring Data JDBC CrudRepository.

@RestController
@RequestMapping("/api/users")
public class UserController {

	@Autowired
	private UserRepository userRepository;

	public UserController() {
		super();
	}

	// Read - get all

	@GetMapping
	public List<User> getAllUsers() {
		return (List<User>) userRepository.findAll();
	}

	// Read - find one

	@GetMapping("/{id}")
	public ResponseEntity<User> getUserById(@PathVariable(value = "id") final Long userId) {

		User user = userRepository.findById(userId)
				.orElseThrow(() -> new NoSuchElementException("User not availbele for Id :" + userId));

		return ResponseEntity.ok().body(user);
	}

	// Write - create

	@PostMapping
	@ResponseStatus(HttpStatus.CREATED)
	public void createUser(@Valid @RequestBody User user) {

		System.out.println(user); // Just to inspect values for demo
		userRepository.save(user);
	}

	// Write - update

	@PutMapping("/{id}")
	@ResponseStatus(HttpStatus.OK)
	public void updateUser(@PathVariable("id") final long id, @RequestBody final User user) {
		User findUser = userRepository.findById(id)
				.orElseThrow(() -> new NoSuchElementException("User not availbele for Id :" + id));
		userRepository.save(user);
	}

	// Write - delete

	@DeleteMapping("/{id}")
	@ResponseStatus(HttpStatus.NO_CONTENT)
	public void deleteUser(@PathVariable final long id) {
		User user = userRepository.findById(id)
				.orElseThrow(() -> new NoSuchElementException("User not availbele for Id :" + id));
		userRepository.delete(user);
	}
}

6. Conclusion

In this tutorial we walked through a Spring Data JDBC with Spring Boot example application to demonstrate object mapping, configurations. Also covered testing Spring Data JDBC CrudRepository operations and access them via a REST Controller.

Checkout source code at Git Hub.

Other Spring Data JDBC Tutorials :

1. Spring Data JDBC – Embedded Entities
2. Spring Data JDBC – Pagination
3. Spring Data JDBC – One-to-Many
4. Spring Data JDBC – Many-to-Many
5. Spring Data JDBC – One-to-One
6. Spring Data JDBC – Query Derivation
7. Spring Boot – Loading Initial data

7. References

1. Spring Documentation
2. Lombok Data
3. Lombok @RequiredArgsConstructor
4. JUnit 5 Tutorial
5. Junit 5 Test Order
6. Spring Boot Junit 5 Test