Open in app

Sign in

Write

Sign in

Spring Boot Database Connection : JDBC vs. JPA & Hibernate

Farhan Tanvir
Code With Farhan
Published in
9 min readJan 3, 2024

This is a complete tutorial of how to use database in Spring Boot . I will discuss two different approaches : Spring JDBC & Spring Data JPA. You will learn how to :

  1. Add database connection in a spring boot application
  2. Run database CRUD operations (SQL) with JDBC & JPA
  3. Difference between JDBC & JPA

JDBC vs JPA

JDBC is a low level standard for working on database. JDBC executes raw SQL queries and return raw data as query result. In JDBC, we need to explicitly map query result into a java class. On the other hand, JPA is an ORM (Object Relational Mapping) which maps java classes to database tables. We can use java methods to run different sql operations without writing any raw sql query. JPA is a high level API for interacting with database.

Hibernate

JPA is an interface and Hibernate is an implementation of JPA. There are other implementations of JPA, for example : EclipseLink, Apache OpenJPA

Configuring Database In Spring Boot

Add the following dependencies to pom.xml file :

  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>
  
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-data-jpa</artifactId>
  </dependency>
  
  <dependency>
   <groupId>org.postgresql</groupId>
   <artifactId>postgresql</artifactId>
   <scope>runtime</scope>
  </dependency>

We have added 3 dependencies for : JDBC, JPA & PostgreSQL.

In the src > main > resources directory, there is a file named “application.properties” . Write the following properties to this file :

# PostgreSQL connection settings
spring.datasource.url=jdbc:postgresql://localhost:5432/database_name
spring.datasource.username=your_database_username
spring.datasource.password=your_database_password
spring.datasource.driver-class-name=org.postgresql.Driver

# HikariCP settings
spring.datasource.hikari.minimumIdle=5
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.maxLifetime=2000000
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.poolName=HikariPoolBooks

# JPA settings
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.PostgreSQLDialect
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true
spring.jpa.properties.hibernate.jdbc.batch_size=15
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.generate_statistics=true
spring.jpa.show-sql=true
spring.oracle.persistence.unit=postgresql

Here, I have created a postgresql database in localhost and used its connection parameters. Use your own database password & connecction parameters.

Now create a table named “stories” and insert some data to work with. Run the following sql query to create a table named “users” :

CREATE TABLE IF NOT EXISTS stories
(
    id         serial PRIMARY KEY,
    title      TEXT,
    body      TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let’s insert a data into stories table by running following query :

INSERT INTO stories(title, body)
VALUES ('Example Title 1', 'Example body 1');

We have inserted a story with a title & body . Let’s insert one more story:

INSERT INTO stories(title, body)
VALUES ('Example Title 2', 'Example body 2');

Using JDBC For SQL Queries

First we need to create a model class for story. Create a new package “model” and add Story.java class :

package com.example.springdbdemo.model;


import jakarta.persistence.*;
import lombok.Data;

import java.sql.Timestamp;

@Data
public class Story {

    private Long id;


    private String title;


    private String body;


    private Timestamp createdAt;

    public Story(Long id, String title, String body, Timestamp createdAt) {
        this.id = id;
        this.title = title;
        this.body = body;
        this.createdAt = createdAt;
    }

    public Story() {
    }
}

We used “@Data” annotation. This annotation will generate all getter & setter methods for the class. We don’t need to write getter & setter methods separately. To use this annotation add the dependency in pom.xml file :

<dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
   <version>1.18.28</version>
   <scope>provided</scope>
</dependency>

StoryRowMapper Class

We need to create a RowMapper for Story class. Because JDBC is a low level client. It will return raw data from any sql query. We need to map the raw query result into a java class. RowMapper will read each row from query result & map it to a java object.

Create a package named “rowmapper” and add the following class :

package com.example.springdbdemo.rowmapper;

import com.example.springdbdemo.model.Story;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;


public class StoryRowMapper implements RowMapper<Story> {


    @Override
    public Story mapRow(ResultSet rs, int rowNum) throws SQLException {
        Story story = new Story();

        story.setId(rs.getLong("ID"));
        story.setTitle(rs.getString("TITLE"));
        story.setBody(rs.getString("BODY"));
        story.setCreatedAt(Timestamp.valueOf( rs.getString("CREATED_AT")));
        return story;
    }
}

The StoryRowMapper class will read each row from query result and create a Story object for each row.

Next we need to create a Controller , Service & Repository for stories.

StoryRepository Class

Create a package named “repository” add the following class :

package com.example.springdbdemo.repository;

import com.example.springdbdemo.model.Story;


public interface StoryRepository {

    public Story findStoryById(Long storyId);

}

We added an interface for the repository. Now implement the repository in same package :

package com.example.springdbdemo.repository;

import com.example.springdbdemo.model.Story;
import com.example.springdbdemo.rowmapper.StoryRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;


@Repository
public class StoryRepositoryImpl implements StoryRepository{


    private final JdbcTemplate jdbcTemplate;
    private final StoryRowMapper storyRowMapper;

    public StoryRepositoryImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.storyRowMapper = new StoryRowMapper();
    }

    @Override
    public Story findStoryById(Long storyId) {
        try {

            Story story = (Story) jdbcTemplate.queryForObject("SELECT *  FROM stories WHERE ID = ?", storyRowMapper, storyId);

            return story;
        }catch (Exception e){
            e.printStackTrace();
        }

        return null;
    }
}

In the implementation class, we are injecting an JdbcTemplate object and we are creating a new instance of StoryRowMapper class.

We implemented the findStoryById() method.

Story story = (Story) jdbcTemplate.queryForObject("SELECT *  FROM stories WHERE ID = ?", storyRowMapper, storyId);

This line runs a raw sql query. We passed the row mapper object in method and also passed the storyId as parameter which will replace “?” in the sql query.

StoryService Class

Create a package named “service” and add the following class:

package com.example.springdbdemo.service;

import com.example.springdbdemo.model.Story;
import com.example.springdbdemo.repository.StoryRepository;
import org.springframework.stereotype.Service;

@Service
public class StoryService {

    private final StoryRepository storyRepository;

    public StoryService(StoryRepository storyRepository) {
        this.storyRepository = storyRepository;
    }

    public Story findById(Long storyId) {
        return this.storyRepository.findStoryById(storyId);
    }
}

The service class uses StoryRepository to fetch data from db.

StoryController Class

Finally, we will create a controller class story. Create a package named “controller” and add the following class:

package com.example.springdbdemo.controller;

import com.example.springdbdemo.model.Story;
import com.example.springdbdemo.service.StoryService;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;


@Controller
@RequestMapping("/rest/story")
public class StoryController {

    private final StoryService storyService;

    public StoryController(StoryService storyService) {
        this.storyService = storyService;
    }


    @ResponseBody
    @RequestMapping(value = "/get/{id}",method = RequestMethod.GET)
    public ResponseEntity<Story> findStoryById(@PathVariable("id")Long id, HttpServletRequest request, HttpServletResponse response) throws Exception{

        try {
            Story story = storyService.findById(id);

            if(story != null) {
                return ResponseEntity.ok(story);
            }

            throw new Exception("Data not found");

        } catch (Exception e){

            e.printStackTrace();
            throw e;
        }

    }
}

Now, run the code & send a get request in postman to : http://localhost:8080/rest/story/get/1

Response :

{
    "id": 1,
    "title": "Example Title 1",
    "body": "Example body 1",
    "createdAt": "2024-01-02T22:00:34.079+00:00"
}

Add two more method in repository class for creating new story & fetching all stories from db.

Update StoryRepository & StoryRepositoryImpl classes :

public interface StoryRepository {

    public Story findStoryById(Long storyId);

    public List<Story> getAllStories();

    public boolean createStory(Story story);

}


@Repository
public class StoryRepositoryImpl implements StoryRepository{


    ...

    @Override
    public List<Story> getAllStories() {
        try {

            List<Story> storyList =  jdbcTemplate.query("SELECT *  FROM stories", storyRowMapper);

            return storyList;
        }catch (Exception e){
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public boolean createStory(Story story) {
        try {

            jdbcTemplate.update("INSERT INTO stories(title, body) VALUES (?,?)",story.getTitle(),story.getBody());

            return true;
        }catch (Exception e){
            e.printStackTrace();
        }

        return false;
    }
}

Update the service & controller classes :


@Service
public class StoryService {

    ...

    public List<Story> getAllStories() {
        return storyRepository.getAllStories();
    }

    public boolean createStory(Story story) {
        return storyRepository.createStory(story);
    }
}
@Controller
@RequestMapping("/rest/story")
public class StoryController {

    
    @ResponseBody
    @RequestMapping(value = "/get-all",method = RequestMethod.GET)
    public ResponseEntity<List<Story>> getAllStories(HttpServletRequest request, HttpServletResponse response) throws Exception{

        try {
            List<Story> storyList = storyService.getAllStories();

            if(storyList != null) {
                return ResponseEntity.ok(storyList);
            }

            throw new Exception("Data not found");

        } catch (Exception e){

            e.printStackTrace();
            throw e;
        }

    }

    @ResponseBody
    @RequestMapping(value = "/create",method = RequestMethod.POST)
    public ResponseEntity<String> createStory(HttpServletRequest request, HttpServletResponse response, @RequestBody Story story) throws Exception{

        try {
            boolean created = storyService.createStory(story);

            if(created) {
                return ResponseEntity.ok("Created new story");
            }

            throw new Exception("Failed creating story");

        } catch (Exception e){

            e.printStackTrace();
            throw e;
        }

    }
}

Lets test the create API. Send a post request :

http://localhost:8080/rest/story/create

{
    "title": "Example title 3",
    "body": "Example body 3"
}

Now, fetch all stories :

http://localhost:8080/rest/story/get-all

Response :

[
    {
        "id": 1,
        "title": "Example Title 1",
        "body": "Example body 1",
        "createdAt": "2024-01-02T22:00:34.079+00:00"
    },
    {
        "id": 2,
        "title": "Example Title 2",
        "body": "Example body 2",
        "createdAt": "2024-01-02T22:00:58.029+00:00"
    },
    {
        "id": 3,
        "title": "Example title 3",
        "body": "Example body 3",
        "createdAt": "2024-01-03T08:32:19.646+00:00"
    }
]

Using JPA With Hibernate For SQL Queries

JPA is an ORM (Object Relational Mapper). To use JPA, we need to map java class to database table. First update the Story.java class :

package com.example.springdbdemo.model;


import jakarta.persistence.*;
import lombok.Data;

import java.sql.Timestamp;

@Data
@Entity
@Table(name = "STORIES")
public class Story {

    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;


    @Column(name = "TITLE")
    private String title;


    @Column(name = "BODY")
    private String body;

    @Column(name = "CREATED_AT", nullable = false, updatable = false, insertable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
    private Timestamp createdAt;

    public Story(Long id, String title, String body, Timestamp createdAt) {
        this.id = id;
        this.title = title;
        this.body = body;
        this.createdAt = createdAt;
    }

    public Story() {
    }
}

Let us go through this code step by step.

@Entity
@Table(name = "STORIES")
public class Story {
  ...
}

This two annotations tell spring boot to map the Story.java class to the “STORIES” table in the database.

The “@Column” annotation is used to map database table columns with java class properties.

@Column(name = "TITLE")
private String title;

The @Column(name = “TITLE”) annotation tells spring boot to map the “TITLE” column in table to the “title” property of Story.java class.

The “@ID” annotation tells that this field is a primary key.

Now we have mapped our java class with database table, we can write queries with JPA. There are two ways we can write sql queries in JPA : using EntityManager or using JpaRepository interface

EntityManager

Update the findStoryById() method in StoryRepositoryImpl class :

package com.example.springdbdemo.repository;

import com.example.springdbdemo.model.Story;
import com.example.springdbdemo.rowmapper.StoryRowMapper;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;


@Repository
public class StoryRepositoryImpl implements StoryRepository{

    @PersistenceContext
    private EntityManager entityManager;

    ...

    @Override
    public Story findStoryById(Long storyId) {
        try {

            Story story = (Story) entityManager.createNativeQuery("SELECT *  FROM stories WHERE ID =:storyId",Story.class)
                    .setParameter("storyId",storyId)
                    .getSingleResult();

            System.out.println("fetched data from db");
            return story;
        }catch (Exception e){
            e.printStackTrace();
        }

        return null;
    }

    ...
}

We are running native sql queries in entity manager. Notice, we no longer need a row mapper. Because we have already mapped the Story class with database table.

Now update the getAllStories() & createStories() method :

package com.example.springdbdemo.repository;

import com.example.springdbdemo.model.Story;
import com.example.springdbdemo.rowmapper.StoryRowMapper;
import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.support.TransactionTemplate;

import java.util.List;


@Repository
public class StoryRepositoryImpl implements StoryRepository{

    @PersistenceContext
    private EntityManager entityManager;

    private final TransactionTemplate transactionTemplate;

    public StoryRepositoryImpl(TransactionTemplate transactionTemplate) {

        this.transactionTemplate = transactionTemplate;

    }

    @Override
    public List<Story> getAllStories() {
        try {

            List<Story> storyList = entityManager.createNativeQuery("SELECT *  FROM stories",Story.class).getResultList();

            return storyList;
        }catch (Exception e){
            e.printStackTrace();
        }

        return null;
    }

    @Override
    public boolean createStory(Story story) {
        try {

            transactionTemplate.execute(transactionStatus ->{
                entityManager.createNativeQuery("INSERT INTO stories(title, body) VALUES (:title, :body)")
                        .setParameter("title",story.getTitle())
                        .setParameter("body",story.getBody())
                        .executeUpdate();
                transactionStatus.flush();
                return null;
            });

            return true;

        }catch (Exception e){
            e.printStackTrace();
        }

        return false;
    }
}

Notice, for the insert query , we wrap the query execution with transactionTemplate.execute() method. Because for any update/delete query we need to use transaction.

Using JpaRepository

Using JpaRepository interface will let us do sql operations with out writing native sql queries. First, we need to refactore our code.

Rename StoryRepository & StoryRepositoryImpl to StoryCustomRepository & StoryCustomRepositoryImpl

public interface StoryCustomRepository {

    public Story findStoryById(Long storyId);

    public List<Story> getAllStories();

    public boolean createStory(Story story);

}
package com.example.springdbdemo.repository;


public class StoryCustomRepositoryImpl implements StoryCustomRepository {

    ...
}

We also removed the “@Repository” annotation.

Now create a new interface named StoryRepository.java :

package com.example.springdbdemo.repository;

import com.example.springdbdemo.model.Story;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface StoryRepository extends JpaRepository<Story,Long>, StoryCustomRepository {

}

Our new StoryRepository interface extends both JpaRepository & StoryCustomRepository. This way we can use both JpaRepository methods & entity manager methods. Or we can only extend JpaRepository if we don’t want to use entity manager native queries. Now we can update StoryService to use JpaRepository method :

package com.example.springdbdemo.service;

...
import com.example.springdbdemo.repository.StoryRepository;


@Service
public class StoryService {

    private final StoryRepository storyRepository;

    public StoryService(StoryRepository storyRepository) {
        this.storyRepository = storyRepository;
    }

    public Story findById(Long storyId) {
        return this.storyRepository.findById(storyId).orElse(null);
    }

    ...
}

We updated the findById() method to use JpaRepository’s built in findById() method. Let us update the other two methods in service :

package com.example.springdbdemo.service;

import com.example.springdbdemo.model.Story;
import com.example.springdbdemo.repository.StoryRepository;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StoryService {

    private final StoryRepository storyRepository;

    public StoryService(StoryRepository storyRepository) {
        this.storyRepository = storyRepository;
    }

    public Story findById(Long storyId) {
        return this.storyRepository.findById(storyId).orElse(null);
    }

    public List<Story> getAllStories() {
        return storyRepository.findAll();
    }

    public boolean createStory(Story story) {
        Story newStory = storyRepository.saveAndFlush(story);
        if(newStory != null) {
            return true;
        }
        return false;
    }
}

We are now using methods from JpaRepository. We can also call entitymanager methods with StoryRepository if we want to run native queries.

Using “@Query” Annotation

We can write native sql query in JpaRepository with “@Query” annotation. Let us add a new query to search stories by title. Update the StoryRepository class :

@Repository
public interface StoryRepository extends JpaRepository<Story,Long>, StoryCustomRepository {

    @Query("select s from Story s where s.title like %:text%")
    List<Story> searchStoryByTitle(String text);

}

We wrote a sql like query which search stories which title contains some text. Notice this is not a raw sql query. Instead of using table name, we are using java class name that map the database table.

“select s from Story s”

Here “Story” is the name of the java class , not the name of database table and “s” is an object of the Story class. And unlike raw sql query, we didn’t use “select * from …” to get all columns. Instead, we used the java object. Also in the where clause, we wrote “s.title” , that is a property of Story class.

In “@Query” annotation, we write sql like query where instead of using database table names and columns we write java class names and class properties.

Now update the StoryService & StoryController class for the new repository method :

@Service
public class StoryService {

    ...

    public List<Story> searchStoryByTitle(String text) {
        List<Story> storyList = storyRepository.searchStoryByTitle(text);
        return storyList;
    }
}
@Controller
@RequestMapping("/rest/story")
public class StoryController {

    ...

    @ResponseBody
    @RequestMapping(value = "/search-by-title",method = RequestMethod.POST)
    public ResponseEntity<List<Story>> searchStoryByTitle(HttpServletRequest request, HttpServletResponse response, @RequestBody StorySearchReq storySearchReq) throws Exception{

        try {
            List<Story> storyList = storyService.searchStoryByTitle(storySearchReq.getText());

            if(storyList != null) {
                return ResponseEntity.ok(storyList);
            }

            throw new Exception("Data not found");

        } catch (Exception e){

            e.printStackTrace();
            throw e;
        }

    }
}

Add a new model class for request body for our new controller. Inside model package add StorySearchReq.java class :

package com.example.springdbdemo.model;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class StorySearchReq {
    public String text;
}

Now send a post request to :

http://localhost:8080/rest/story/search-by-title

{
    "text":"2"
}
Spring Boot
Spring
Java
Database
Spring Framework

--

--

Farhan Tanvir
Code With Farhan

Written by Farhan Tanvir

200 Followers
Editor for

Software Engineer

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams