Spring Data JPA and SQL

Custom User Experiences



Spring Data JPA and SQL Datastores

The Spring Data JPA brings to the table many tools to cleanly manage external datastores.

Adding Spring Data JPA to a Springboot Project

To add Spring Data JPA to an existing Springboot project, add


    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'

to your build.gradle file.

or


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

to your pom.xml maven file.

This will pull the appropriate release of the Spring Data JPA for your Springboot project.

If starting a new project, I recommend using the Spring Initializr, you can select the Spring Data JPA there.

Configure SQL Datastore in Spring Project

To configure your SQL database, you must add a few lines to your application.properties file.


spring.jpa.hibernate.ddl-auto=update
spring.datasource.url=jdbc:mysql://${MYSQL_HOST:localhost}:3306/<database-name>?useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=<db_username>
spring.datasource.password=<db_user_pw>
spring.jpa.open-in-view=false

Replacing the "<>"s with the appropriate values.

Database Entities

JPA database entities are POJO beans that represent a row in a datastore table. All JPA enitities must have a default(no-args) constructor.

Spring Data JPA Annotations for Entities

Part of the magic of Spring, the annotations, are the means that provide the database meta-data to the Spring application.

@Entity

We use the @Entity annotation to signify that this POJO will be a datastore entity

@Table

We can use the @Table annotation to provide meta-data about the table, like the table name.

@Id

All tables need a primary key, this will be signified by the @Id annotation.

@GeneratedValue

The @GeneratedValue annotation is used to tell the application how the primary key(@Id) should be generated for the database.

In the case of auto generated ids, we never define them, we let the SQL database handle them.

@Column

The @Column annotation tells the application about the SQL table's columns, including the column names and if the values should be unique.

Entity Example

For our example, we will have an SQL database table for website users


@Entity
@Table(name = "db_users")
class DbUser {

  //no-args constructor
  public DbUser(){

  }

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  @Column(name = "username", nullable = false, unique = true)
  private String username;

  @Column(name = "email", nullable = false, unique = true)
  private String email;

  @Column(name = "name", nullable = false)
  private String name;

  //getters
  //setters

}

The primary key is the id field, and is auto-generated. Each user has a username, which must be unique and not null. The same for the email fields. The name field does not need to be unique, but must not be null.

JPA Repository

The repository will be our means of interacting with the SQL database. We give repository interfaces the @Repository annotation, this allows them to be autowired into your services.

The repository will be an interface that inherits from the JpaRepository class. From this parent, we inherit methods such as save().


@Repository
public interface UserRepository extends JpaRepository<PageFlowReportEntity, Long>{

}

The JpaRepository allows us to define method signatures that correspond to SQL statements


@Repository
public interface UserRepository extends JpaRepository<DbUser, Long>{
  
  List<DbUser> findByNameSortById(String name);

}

The findByNameSortById() method fetches all users with a given name sorted by their ids.

Using the Database

Lets write a test to demonstrate how to create and fetch SQL database users.


@Autowired
private final UserRepository userRepository;

@Test
public void saveAndFetchAUserByName(){
  String name = "a name";
  String username = "aUsername";
  String email = "an.email.address@buildbench.net";

  DbUser testUser = new DbUser();
  testUser.setName(name);
  testUser.setUsername(username);
  testUser.setEmail(email);
  //Notice that you don't manually set the id, let the
  //db handle it

  //Save
  userRepository.save(testUser);

  //Fetch
  List<DbUser> usersFetched = userRepository.findByNameSortById("a name");

  //Assert
  assertEquals(1, usersFetched.size());
  assertNotNull(usersFetched.get(0).getId());
  assertEquals(name, usersFetched.get(0).getName());
  assertEquals(username, usersFetched.get(0).getUsername());
  assertEquals(email, usersFetched.get(0).getEmail());
}

Why it Matters

In conclusion, the spring Data JPA is a very convenient and clean way for your Springboot applications to interact with an SQL database. It allows us to cleanly define methods to correspond to SQL statements.

Share this Post