Spring Data JPA: Database connection - efficient and practical

If you are working with Spring Boot and want to connect to a database, Spring Data JPA is your best friend. It does a lot of the work for you by automating CRUD operations and providing a convenient abstraction layer for database access. In this article, we’ll take a look at how to properly use Spring Data JPA in your application, including practical code examples.

1. What is Spring Data JPA?

Spring Data JPA is a Spring boot extension for Java Persistence API (JPA). It helps you to access relational databases with minimal code without having to dig deep into SQL or JDBC implementations.

Advantages of Spring Data JPA

Automatic generation of CRUD methods
Integration with Hibernate as standard JPA provider
Flexible queries with JPQL or the Criteria API
Support of native SQL queries
Simple caching and transaction management

2. Set up project

2.1. Add dependencies

In order for Spring Boot to work with a database, you need the following dependencies in your pom.xml:

<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.postgresql</groupId> 
        <artifactId>postgresql</artifactId> 
        <scope>runtime</scope> 
    </dependency> 
</dependencies>

Here we use PostgreSQL, but you can also use MySQL or H2 for tests.

2.2. Configure database

You define the connection to the database in application.properties or application.yml:

spring.datasource.url=jdbc:postgresql://localhost:5432/meine_datenbank 
spring.datasource.username=myusername 
spring.datasource.password=mypassword 
spring.jpa.hibernate.ddl-auto=update 
spring.jpa.show-sql=true

The ddl-auto=update ensures that Hibernate updates the database schema automatically. In production, you should use validate or none to avoid unwanted changes.

3. Define an entity

Now we create a simple entity class that represents our database table:

import jakarta.persistence.*; 
import lombok.Getter; 
import lombok.Setter; 
 
@Entity 
@Table(name = "customers") 
@Getter 
@Setter 
public class Customer { 
    @Id 
    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    private Long id; 
     
    @Column(nullable = false) 
    private String name; 
     
    @Column(unique = true) 
    private String email; 
}

Here we have a customer entity with three fields: id, name and email. Lombok helps us to avoid boilerplate code for getters and setters.

4. Create repository

Spring Data JPA allows us to define repositories that automatically provide CRUD methods:

import org.springframework.data.jpa.repository.JpaRepository; 
import java.util.Optional; 
 
public interface CustomerRepository extends JpaRepository<Customer, Long> { 
    Optional<Customer> findByEmail(String email); 
}

Without a line of implementation, we now have access to CRUD methods such as save(), findById(), deleteById() and our own method findByEmail().

5. Implement service layer

The service layer is responsible for business logic and transaction management:

import org.springframework.stereotype.Service; 
import java.util.List; 
import java.util.Optional; 
 
@Service 
public class CustomerService { 
    private final CustomerRepository customerRepository; 
 
    public CustomerService(CustomerRepository customerRepository) { 
        this.customerRepository = customerRepository; 
    } 
 
    public List<Customer> allCustomer() { 
        return customerRepository.findAll(); 
    } 
 
    public Optional<Customer> findCustomerByEmail(String email) { 
        return customerRepository.findByEmail(email); 
    } 
 
    public Customer saveCustomer(Customer customer) { 
        return customerRepository.save(customer); 
    } 
}

6. Create REST controller

Now we still need a REST controller to interact with our application:

import org.springframework.web.bind.annotation.*; 
import java.util.List; 
import java.util.Optional; 
 
@RestController 
@RequestMapping("/customers") 
public class CustomerController { 
    private final CustomerService customerService; 
 
    public CustomerController(CustomerService customerService) { 
        this.customerService = customerService; 
    } 
 
    @GetMapping 
    public List<Customer> allCustomers() { 
        return customerService.allCustomers(); 
    } 
 
    @GetMapping("/{email}") 
    public Optional<Customer> customerByEmail(@PathVariable String email) { 
        return customerService.findCustomerByEmail(email); 
    } 
 
    @PostMapping 
    public Customer newCustomer(@RequestBody Customer customer) { 
        return customerService.saveCustomer(customer); 
    } 
}

Now you can retrieve or save customers via REST API.

7. Performance optimization and security

7.1. Lazy Loading and Fetch Types

By default, JPA loads relationships lazy (on demand). For example, if you have an order entity that refers to customer, you should explicitly set the loading strategy:

@ManyToOne(fetch = FetchType.LAZY) 
private Customer customer;

7.2. Avoid SQL injections

Never use direct SQL queries with string concatenation. Instead:

@Query("SELECT c FROM Customer c WHERE c.email = :email") 
Customer findCustomerSecurely(@Param("email") String email);

What is Parameter Binding?

The concept for avoiding SQL injections in JPA is called “parameter binding” or “prepared statements”.

Why is parameter binding important?

Inserting user input directly into an SQL query creates an SQL injection vulnerability. Attackers could manipulate the query and access or modify data without authorization.

How does parameter binding protect against SQL injection?

Instead of inserting user input directly into a query, placeholders (? or :parameter) are used and the values are securely bound.

Example with @Query and @Param in JPA

@Query("SELECT u FROM User u WHERE u.username = :username")
User findByUsername(@Param("username") String username);

✅ Secure: The value for :username is automatically escaped so that SQL injection is not possible.

Insecure variant (do not use!)

@Query("SELECT u FROM User u WHERE u.username = '" + username + "'")
User findByUsername(String username);

🚨 Insecure! An attacker can manipulate the query with ‘ OR ‘1’=’1.

7.3. Activate caching

With Spring Cache, Spring offers an easy way to speed up frequent requests:

@Cacheable("customer") 
public List<Customer> allCustomer() { 
    return customerRepository.findAll(); 
}

Don’t forget to activate Spring Boot Cache in application.properties:

spring.cache.type=simple

Derived Query Methods

Spring Data JPA allows methods to be generated automatically using so-called Derived Query Methods without having to write a separate JPQL or SQL query. These methods are based on the naming convention and are automatically converted into corresponding SQL statements by Spring.

Frequently used methods:

Spring Data JPA offers various predefined name prefixes for repository methods:

findBy... - Searches for an entity using a field

List<User> findByLastName(String lastName);
Optional<User> findByEmail(String email);

existsBy... - Checks whether an entity with a specific value exists

boolean existsByUsername(String username);

countBy... - Counts entities based on a specific field

long countByActive(boolean active);

deleteBy... - Deletes entities based on a specific field

void deleteByEmail(String email);

Expansion through And and Or:

Several criteria can be combined with And or Or:

findBy...And...

List<User> findByFirstNameAndLastName(String firstName, String lastName);

findBy...Or...

List<User> findByRoleOrStatus(String role, boolean status);

Comparison operators in methods:

Methods can also be extended with operators:

findByAgeGreaterThan(int age)
findBySalaryLessThan(double salary)
findByCreatedAtBetween(Date start, Date end)
findByNameLike(String pattern)
findByEmailStartingWith(String prefix)
findByUsernameEndingWith(String suffix)
findByDescriptionContaining(String keyword)

Sorting and limiting results:

Methods can also be sorted or limited to a certain number of results:

findTop3ByOrderBySalaryDesc() – The three best-paid employees
findFirstByOrderByCreatedAtAsc() – The oldest entry
findByDepartmentOrderByLastNameAsc(String department) – Employees of a department, sorted by last name

Sorting with the Sort parameter

Alternatively, sorting can also be specified dynamically at runtime using the sort parameter. This is particularly useful if the sort order depends on the user input:

List<User> findByDepartment(String department, Sort sort);

Exemplary use:

// Aufsteigende Sortierung nach Nachname
Sort sortAsc = Sort.by("lastName").ascending();
List<User> usersAsc = userRepository.findByDepartment("IT", sortAsc);
// Absteigende Sortierung nach Nachname
Sort sortDesc = Sort.by("lastName").descending();
List<User> usersDesc = userRepository.findByDepartment("IT", sortDesc);

Further sorting options

The Sort object can also be used to create more complex sorting logic, such as:

Multiple sort fields

Sort sortMulti = Sort.by("lastName").ascending().and(Sort.by("firstName").descending());

Consider zero values

As of Spring Data 2.0, you can also specify how null values are handled:

Sort sortNullsLast = Sort.by(Sort.Order.asc("lastName").nullsLast());

Conclusion

With Spring Data JPA, you can work efficiently and easily with relational databases. Thanks to automatic repositories, lean configuration and strong integration with Spring Boot, it is the first choice for many developers. If you have performance problems, it’s worth taking a look at caching, lazy loading and query optimization.

Test your knowledge and implement the example yourself – you’ll quickly see how much work Spring Data JPA can do for you! 🚀

For Customers

For Providers

Spring Data JPA: Database connection – efficient and practical

1. What is Spring Data JPA?

Advantages of Spring Data JPA

2. Set up project

2.1. Add dependencies

2.2. Configure database

3. Define an entity

4. Create repository

5. Implement service layer

6. Create REST controller

7. Performance optimization and security

7.1. Lazy Loading and Fetch Types

7.2. Avoid SQL injections

What is Parameter Binding?

Why is parameter binding important?

How does parameter binding protect against SQL injection?

Insecure variant (do not use!)

7.3. Activate caching

Derived Query Methods

Frequently used methods:

findBy... - Searches for an entity using a field

existsBy... - Checks whether an entity with a specific value exists

countBy... - Counts entities based on a specific field

deleteBy... - Deletes entities based on a specific field

Expansion through And and Or:

findBy...And...

findBy...Or...

Comparison operators in methods:

Sorting and limiting results:

Sorting with the Sort parameter

Further sorting options

Multiple sort fields

Consider zero values

Conclusion

For Customers

For Providers

Spring Data JPA: Database connection – efficient and practical

1. What is Spring Data JPA?

Advantages of Spring Data JPA

2. Set up project

2.1. Add dependencies

2.2. Configure database

3. Define an entity

4. Create repository

5. Implement service layer

6. Create REST controller

7. Performance optimization and security

7.1. Lazy Loading and Fetch Types

7.2. Avoid SQL injections

What is Parameter Binding?

Why is parameter binding important?

How does parameter binding protect against SQL injection?

Insecure variant (do not use!)

7.3. Activate caching

Derived Query Methods

Frequently used methods:

findBy... - Searches for an entity using a field

existsBy... - Checks whether an entity with a specific value exists

countBy... - Counts entities based on a specific field

deleteBy... - Deletes entities based on a specific field

Expansion through And and Or:

findBy...And...

findBy...Or...

Comparison operators in methods:

Sorting and limiting results:

Sorting with the Sort parameter

Further sorting options

Multiple sort fields

Consider zero values

Conclusion

Related Posts