In this post, we’ll learn how to implement a Spring Boot Rest API using Spring Boot JDBC core classes and PostgreSQL.

· Prerequisites
· Overview
∘ What is JDBC (Java Database Connectivity)?
∘ Core Classes and Interfaces for Spring JDBC
· Getting Started
∘ Define Data Model
∘ JDBC Repositories
∘ Spring Rest APIs Controller
· Test the application
· Conclusion
· References

---

Prerequisites

This is the list of all the prerequisites:

• Spring Boot 3+
• Maven 3.6.3
• Java 21
• PostgreSQL

Overview

What is JDBC (Java Database Connectivity)?

Java Database Connectivity (JDBC) is an application programming interface (API) for the Java programming language which defines how a client may access a database. It is a Java-based data access technology used for Java database connectivity. It is part of the Java Standard Edition platform, from Oracle Corporation. It provides methods to query and update data in a database, and is oriented toward relational databases. A JDBC-to-ODBC bridge enables connections to any ODBC-accessible data source in the Java virtual machine (JVM) host environment.

https://en.wikipedia.org/wiki/Java_Database_Connectivity

Core Classes and Interfaces for Spring JDBC

The core JDBC classes in Spring Boot provide several mechanisms to simplify database access and interaction.

1. JdbcTemplate

JdbcTemplate is the central class in the JDBC core package. It handles the creation and release of resources, which helps you avoid common errors, such as forgetting to close the connection. It performs the basic tasks of the core JDBC workflow (such as statement creation and execution), leaving application code to provide SQL and extract results.

@Autowired
private JdbcTemplate jdbcTemplate;

   
public List<Book> findAll() {
        var query = "SELECT * FROM book";
        return jdbcTemplate.query(query, (res, rowNum) -> Book.builder().
            id(res.getLong("id"))
            .title(res.getString("title"))
            .isbn(res.getString("isbn"))
            .description(res.getString("description"))
            .page(res.getInt("page"))
            .price(res.getBigDecimal("price"))
            .build());
    }

2. NamedParameterJdbcTemplate

The NamedParameterJdbcTemplate class adds support for programming JDBC statements by using named parameters, as opposed to programming JDBC statements using only classic placeholder ( '?') arguments. The NamedParameterJdbcTemplate class wraps a JdbcTemplate and delegates to the wrapped JdbcTemplate to do much of its work.

@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

   
public List<Book> findByTitle(String title) {
    var query = "SELECT * FROM book WHERE title = :title";
    Map<String, Object> params = new HashMap<>();
    params.put("title", title);
    return namedParameterJdbcTemplate.query(query, params, (res, rowNum) -> Book.builder().
            id(res.getLong("id"))
            .title(res.getString("title"))
            .isbn(res.getString("isbn"))
            .description(res.getString("description"))
            .page(res.getInt("page"))
            .price(res.getBigDecimal("price"))
            .build());
}

3. JdbcClient

As of 6.1, the named parameter statements of NamedParameterJdbcTemplate and the positional parameter statements of a regular JdbcTemplate are available through a unified client API with a fluent interaction model.

@Autowired
private final JdbcClient jdbcClient;

public List<Book> findAll() {
   var query = "SELECT * FROM book";
   return jdbcClient.sql(query)
           .query(Book.class)
           .list();
}

4. SimpleJdbcInsert and SimpleJdbcCall

The SimpleJdbcInsert and SimpleJdbcCall classes provide a simplified configuration by taking advantage of database metadata that can be retrieved through the JDBC driver.

A SimpleJdbcInsert is a multi-threaded, reusable object providing easy (batch) insert capabilities for a table. It provides meta-data processing to simplify the code to construct a basic insert statement. All you need to provide is the name of the table and a Map containing the column names and the column values.

A SimpleJdbcCall is a multithreaded, reusable object representing a call to a stored procedure or a stored function. It provides meta-data processing to simplify the code needed to access basic stored procedures/functions.

@Repository
public class BookRepository {

    private final SimpleJdbcInsert simpleJdbcInsert;

    public BookRepository(DataSource dataSource) {
        this.simpleJdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("book")
                .usingGeneratedKeyColumns("id");
    }

    public Long insertBook(Book book) {
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("title", book.title());
        parameters.addValue("isbn", book.isbn());
        parameters.addValue("description", book.description());
        parameters.addValue("page", book.page());
        parameters.addValue("price", book.price());

        // Execute the insert and return the generated key
        return simpleJdbcInsert.executeAndReturnKey(parameters).longValue();
    }
}

@Autowired
private SimpleJdbcCall simpleJdbcCall;

public Book getBookById(Long bookId) {
        Map<String, Object> parameters = new HashMap<>();
        parameters.put("bookId", bookId);
        
        simpleJdbcCall.withProcedureName("getBookById");
        Map<String, Object> result = simpleJdbcCall.execute(parameters);
        return ((List<Book>) result.get("result")).get(0);
}

Getting Started

We will create a simple Spring project from start.spring.io, with the following dependencies: Spring Web, Lombok, PostgreSQL Driver, JDBC API, and Validation

Define Data Model

Let’s start by creating a class that represents our data models.

We have used Java records to model Book, Author, and Publisher domain objects.

JDBC Repositories

We will implement the repositories for this story using JdbcTemplate, NamedParameterJdbcTemplate, and JdbcClient. Before that, we created a basic JDBC Repository interface which the repository classes will implement.

public interface JdbcRepository<T> {

    T save(T table);

    T update(T table, Long id);

    List<T> findAll();

    Optional<T> findById(Long id);

    int deleteById(Long id);
}

The DataSource should always be configured as a bean in the Spring IoC container.

• BookRepository

This class implements all methods of JdbcRepository using JdbcTemplate as follows:

@RequiredArgsConstructor
@Transactional
@Repository
public class BookRepository implements JdbcRepository<Book> {

    private final JdbcTemplate jdbcTemplate;

    private final RowMapper<Book> rowMapper = (res, rowNum) -> Book.builder().
            id(res.getLong("id"))
            .title(res.getString("title"))
            .isbn(res.getString("isbn"))
            .description(res.getString("description"))
            .page(res.getInt("page"))
            .price(res.getBigDecimal("price"))
            .authorId(res.getLong("authorId"))
            .build();


    @Override
    public Book save(Book book) {
        var query = "INSERT INTO book(id, title, isbn, description, page, price, authorId) values(?,?,?,?,?,?,?)";
        int inserted = jdbcTemplate.update(query, book.id(), book.title(), book.isbn(), book.description(), book.page(), book.price(), book.authorId());
        if (inserted == 1) {
            return book;
        }
        throw new RuntimeException("Save failed");
    }

    @Override
    public Book update(Book book, Long id) {
        var query = "UPDATE book set title = ?, isbn = ?, description = ?, page = ?, price = ? where id = ?";
        int updated = jdbcTemplate.update(query, book.title(), book.isbn(), book.description(), book.page(), book.price(), book.id());
        if (updated == 1) {
            return book;
        }
        throw new RuntimeException("Update failed");
    }

    @Override
    public List<Book> findAll() {
        var query = "SELECT id, title, isbn, description, page, price, authorId FROM book";
        return jdbcTemplate.query(query, rowMapper);
    }

    @Override
    public Optional<Book> findById(Long id) {
        var query = "SELECT id, title, isbn, description, page, price, authorId FROM book where id = ?";
        try {
            Book book = jdbcTemplate.queryForObject(query, rowMapper, id);
            return Optional.ofNullable(book);
        } catch (DataAccessException e) {
            return Optional.empty();
        }
    }

    @Override
    public int deleteById(Long id) {
        var query = "DELETE from book where id = ?";
        return jdbcTemplate.update(query, id);
    }

}

The RowMapper lambda expression maps rows of a ResultSet to Java objects.

• PublisherRepository

This class used the NamedParameterJdbcTemplate

@RequiredArgsConstructor
@Transactional
@Repository
public class PublisherRepository implements JdbcRepository<Publisher> {

    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private final RowMapper<Publisher> rowMapper = (res, rowNum) -> Publisher.builder().
            id(res.getLong("id"))
            .name(res.getString("name"))
            .email(res.getString("email"))
            .build();

    @Override
    public Publisher save(Publisher publisher) {
        var query = "INSERT INTO publisher(id, name, email) values(:id, :name, :email)";
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("id", publisher.id());
        parameters.addValue("name", publisher.name());
        parameters.addValue("email", publisher.email());
        int inserted = namedParameterJdbcTemplate.update(query, parameters);

        if (inserted == 1) {
            return publisher;
        }
        throw new RuntimeException("Save failed");
    }

    @Override
    public Publisher update(Publisher publisher, Long id) {
        var query = "UPDATE publisher set name = :name, email = :email where id = :id";
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("name", publisher.name());
        parameters.addValue("email", publisher.email());
        parameters.addValue("id", id);
        int updated = namedParameterJdbcTemplate.update(query, parameters);
        if (updated == 1) {
            return publisher;
        }
        throw new RuntimeException("Update failed");
    }

    @Override
    public List<Publisher> findAll() {
        var query = "SELECT id,name,email FROM publisher";
        return namedParameterJdbcTemplate.query(query, rowMapper);
    }

    @Override
    public Optional<Publisher> findById(Long id) {
        var query = "SELECT id,name,email FROM publisher where id = :id";
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("id", id);
        return Optional.ofNullable(namedParameterJdbcTemplate.queryForObject(query, parameters, rowMapper));
    }

    @Override
    public int deleteById(Long id) {
        var query = "DELETE from publisher where id = :id";
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("id", id);
        return namedParameterJdbcTemplate.update(query, parameters);
    }
}

• AuthorRepository

We’ll use JdbcClient with the AuthorRepository.

@RequiredArgsConstructor
@Transactional
@Repository
public class AuthorRepository implements JdbcRepository<Author> {

    private final JdbcClient jdbcClient;

    @Override
    public Author save(Author author) {
        var query = "INSERT INTO author(id, firstname, lastname) values(?,?,?)";
        int inserted = jdbcClient.sql(query)
                .params(author.id(), author.firstname(), author.lastname())
                .update();
        if (inserted == 1) {
            return author;
        }
      throw new RuntimeException("Save failed");
    }

    @Override
    public Author update(Author author, Long id) {
        var query = "UPDATE author set firstname = ?, lastname = ? where id = ?";
        int updated = jdbcClient.sql(query)
                .params(author.firstname(), author.lastname(), id)
                .update();
        if (updated == 1) {
            return author;
        }
        throw new RuntimeException("Update failed");
    }

    @Override
    public List<Author> findAll() {
        var query = "SELECT id,firstname,lastname FROM author";
        return jdbcClient.sql(query)
                .query(Author.class)
                .list();
    }

    @Override
    public Optional<Author> findById(Long id) {
        var query = "SELECT id, firstname, lastname FROM author where id = :id";
        return jdbcClient.sql(query)
                .param("id", id)
                .query(Author.class)
                .optional();
    }

    @Override
    public int deleteById(Long id) {
        var query = "DELETE from author where id = :id";
        return jdbcClient.sql(query)
                .param("id", id)
                .update();
    }
}

With JdbcClient we can use both positional parameters or named parameters.

Spring Rest APIs Controller

After creating the service classes for each repository above, we create the controllers that provide APIs to create, retrieve, update, delete, and search for book, author, and publisher models.

• BookController

@RestController
@RequestMapping("/api/book")
public class BookController {

    private final Logger log = LoggerFactory.getLogger(BookController.class);

    private final BookService entityService;

    public BookController(BookService entityService) {
        this.entityService = entityService;
    }

    /**
     * {@code POST  /book} : Create a new book.
     *
     * @param book the book to create.
     * @return the {@link ResponseEntity} with status {@code 201 (Created)} and with body the new book.
     */
    @PostMapping()
    public ResponseEntity<Book> createBook(@RequestBody @Valid Book book) {
        log.debug("REST request to save Book : {}", book);
        return new ResponseEntity<>(entityService.create(book), HttpStatus.CREATED);
    }

    /**
     * {@code PUT  /book/{id}} : Updates an existing book.
     *
     * @param book the book to update.
     * @param id the id of the book to update.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the updated book,
     * or with status {@code 400 (Bad Request)} if the book is not valid,
     * or with status {@code 500 (Internal Server Error)} if the book couldn't be updated.
     */
    @PutMapping(value = "/{id}")
    public ResponseEntity<Book> updateBook(@Valid @RequestBody Book book, @PathVariable("id") Long id) {
        log.debug("REST request to update Book : {}", book);
        Book result = entityService.update(book, id);
        return ResponseEntity.ok().body(result);
    }

    /**
     * {@code GET  /book} : get all the books.
     *
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and the list of book in body.
     */

    @GetMapping()
    public ResponseEntity<List<Book>> getAllBook() {
        log.debug("REST request to get all books");
        List<Book> lst = entityService.getAll();

        return new ResponseEntity<>(lst, HttpStatus.OK);
    }

    /**
     * {@code GET  /book/:id} : get the "id" book.
     *
     * @param id the id of the book to retrieve.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the book, or with status {@code 404 (Not Found)}.
     */
    @GetMapping(value = "/{id}")
    public ResponseEntity<Book> getOneBook(@PathVariable("id") Long id) {
        log.debug("REST request to get Book : {}", id);
        Book e = entityService.getOne(id);

        return new ResponseEntity<>(e, HttpStatus.OK);
    }

    /**
     * {@code DELETE  /book/:id} : delete the "id" book.
     *
     * @param id the id of the book to delete.
     * @return the {@link ResponseEntity} with status {@code 204 (NO_CONTENT)}.
     */
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteBook(@PathVariable("id") Long id) {
        log.debug("REST request to delete Book : {}", id);
        entityService.delete(id);
        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }
}

• PublisherController

@RestController
@RequestMapping("/api/publisher")
public class PublisherController {

    private final Logger log = LoggerFactory.getLogger(PublisherController.class);

    private final PublisherService entityService;

  public PublisherController(PublisherService entityService) {
  this.entityService = entityService;
 }

    /**
     * {@code POST  /publisher} : Create a new publisher.
     *
     * @param publisher the publisher to create.
     * @return the {@link ResponseEntity} with status {@code 201 (Created)} and with body the new publisher.
     */
 @PostMapping()
 public ResponseEntity<Publisher> createPublisher(@RequestBody @Valid Publisher publisher) {
         log.debug("REST request to save Publisher : {}", publisher);
         return new ResponseEntity<>(entityService.create(publisher), HttpStatus.CREATED);
    }

   /**
     * {@code PUT  /publisher/{id}} : Updates an existing publisher.
     *
     * @param publisher the publisher to update.
     * @param id the id of the publisher to update.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the updated publisher,
     * or with status {@code 400 (Bad Request)} if the publisher is not valid,
     * or with status {@code 500 (Internal Server Error)} if the publisher couldn't be updated.
     */
    @PutMapping(value = "/{id}")
    public ResponseEntity<Publisher> updatePublisher(@Valid @RequestBody Publisher publisher, @PathVariable("id") Long id) {
        log.debug("REST request to update Publisher : {}", publisher);
        Publisher result = entityService.update(publisher, id);
        return ResponseEntity.ok().body(result);
    }

    /**
     * {@code GET  /publisher} : get all the publishers.
     *
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and the list of publisher in body.
     */

    @GetMapping()
    public ResponseEntity<List<Publisher>> getAllPublisher() {
     log.debug("REST request to get all publishers");
        List<Publisher> lst = entityService.getAll();

        return new ResponseEntity<>(lst,HttpStatus.OK);
    }

    /**
     * {@code GET  /publisher/:id} : get the "id" publisher.
     *
     * @param id the id of the publisher to retrieve.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the publisher, or with status {@code 404 (Not Found)}.
     */
    @GetMapping(value = "/{id}")
    public ResponseEntity<Publisher> getOnePublisher(@PathVariable("id") Long id) {
        log.debug("REST request to get Publisher : {}", id);
        Publisher e = entityService.getOne(id);

        return new ResponseEntity<>(e, HttpStatus.OK);
    }

  /**
     * {@code DELETE  /publisher/:id} : delete the "id" publisher.
     *
     * @param id the id of the publisher to delete.
     * @return the {@link ResponseEntity} with status {@code 204 (NO_CONTENT)}.
     */
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deletePublisher(@PathVariable("id") Long id) {
        log.debug("REST request to delete Publisher : {}", id);
        entityService.delete(id);
        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }

}

• AuthorController

@RestController
@RequestMapping("/api/author")
public class AuthorController {

    private final Logger log = LoggerFactory.getLogger(AuthorController.class);
 
    private final AuthorService entityService;

  public AuthorController (AuthorService entityService) {
  this.entityService = entityService;
 }

    /**
     * {@code POST  /author} : Create a new author.
     *
     * @param author the author to create.
     * @return the {@link ResponseEntity} with status {@code 201 (Created)} and with body the new author.
     */
 @PostMapping()
 public ResponseEntity<Author> createAuthor(@RequestBody @Valid Author author) {
         log.debug("REST request to save Author : {}", author);
         return new ResponseEntity<>(entityService.create(author), HttpStatus.CREATED);
    }

   /**
     * {@code PUT  /author/{id}} : Updates an existing author.
     *
     * @param author the author to update.
     * @param id the id of the author to update.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the updated author,
     * or with status {@code 400 (Bad Request)} if the author is not valid,
     * or with status {@code 500 (Internal Server Error)} if the author couldn't be updated.
     */
    @PutMapping(value = "/{id}")
    public ResponseEntity<Author> updateAuthor(@Valid @RequestBody Author author, @PathVariable("id") Long id) {
        log.debug("REST request to update Author : {}", author);
        Author result = entityService.update(author, id);
        return ResponseEntity.ok().body(result);
    }

    /**
     * {@code GET  /author} : get all the authors.
     *
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and the list of author in body.
     */

    @GetMapping()
    public ResponseEntity<List<Author>> getAllAuthor() {
     log.debug("REST request to get all authors");
        List<Author> lst = entityService.getAll();

        return new ResponseEntity<>(lst,HttpStatus.OK);
    }

    /**
     * {@code GET  /author/:id} : get the "id" author.
     *
     * @param id the id of the author to retrieve.
     * @return the {@link ResponseEntity} with status {@code 200 (OK)} and with body the author, or with status {@code 404 (Not Found)}.
     */
    @GetMapping(value = "/{id}")
    public ResponseEntity<Author> getOneAuthor(@PathVariable("id") Long id) {
        log.debug("REST request to get Author : {}", id);
        Author e = entityService.getOne(id);

        return new ResponseEntity<>(e, HttpStatus.OK);
    }

  /**
     * {@code DELETE  /author/:id} : delete the "id" author.
     *
     * @param id the id of the author to delete.
     * @return the {@link ResponseEntity} with status {@code 204 (NO_CONTENT)}.
     */
    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteAuthor(@PathVariable("id") Long id) {
        log.debug("REST request to delete Author : {}", id);
        entityService.delete(id);
        return new ResponseEntity<>(HttpStatus.NO_CONTENT);
    }

}

Test the application

Now we can run our application and let’s do some tests per endpoint.

Conclusion

Well done !!. This post covered a Spring Boot REST API implementation using JDBC Core.

JDBC is a powerful API that provides a standard way for Java applications to interact with databases.

The complete source code is available on GitHub.

Support me through GitHub Sponsors.

Thank you for Reading !! See you in the next story.

References

• https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/package-summary.html
• https://docs.spring.io/spring-framework/reference/data-access/jdbc/core.html

👉 Link to Medium blog