Hello everybody,

Greetings of the day! 

Today, we'll discover how to use the JDBC Template in the Spring Boot REST API to carry out CRUD Operations.

So let's begin.

First, create a basic project structure for your project by visiting the Spring Initializr and adding web, Spring Data JPA, Lombok, and PostgreSQL Driver dependencies.


Unzip the project and import it into your IDE.

Next, let's create a Recipe.java class that we can associate with the recipe table in our database.
package com.learning.dto;

import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
public class Recipe {

    private Long id;

    private String name;

    private String description;

    private String videoURL;
}
Next, create a recipe table in your database. Below is a script to create a recipe table in PostgreSQL.
CREATE TABLE IF NOT EXISTS recipe
(
    id bigint NOT NULL DEFAULT nextval('recipe_id_seq'::regclass),
    description character varying(255) ,
    name character varying(255) ,
    videourl character varying(255) ,
    CONSTRAINT recipe_pkey PRIMARY KEY (id)
);
The key piece of code for each to focus on here is the repository layer code for performing CRUD operations using JDBC templates. So let's just focus on that.

The JdbcTemplate is available out of the box and can be accessed with @Autowired as the bean for the JdbcTemplate is already created by Spring Boot when you add the Spring Data JPA dependency.

@Autowired
private JdbcTemplate jdbcTemplate;

Now suppose you want to insert a record into the recipe table. Then here's a simple query to run:

insert into recipe(name,description,videourl) values('NAME','DESCRIPTION','VIDEO_URL');

Now using Spring Boot and Jdbc Template you can easily execute the above query.

jdbcTemplate.execute(INSERT_RECIPE_QUERY);

Example 
    public void addRecipe(Recipe recipe) {
        String INSERT_RECIPE="insert into " +
                "recipe(name,description,videourl)" +
                "values('NAME','DESCRIPTION','VIDEO_URL')";
        INSERT_RECIPE = INSERT_RECIPE.
                replace("NAME", recipe.getName());
        INSERT_RECIPE = INSERT_RECIPE.
                replace("DESCRIPTION", recipe.getDescription());
        INSERT_RECIPE = INSERT_RECIPE.
                replace("VIDEO_URL", recipe.getVideoURL());
        jdbcTemplate.execute(INSERT_RECIPE);
        LOGGER.info("Recipe stored successfully.");
    }
Now let's run the following query to get all the recipes from the table using JdbcTemplate.


private static final String LIST_RECIPE="Select * from recipe";

    @Override
    public List<Recipe> listRecipes() {
        List<Recipe> recipes = jdbcTemplate.query
                (LIST_RECIPE,
                        new BeanPropertyRowMapper(Recipe.class));
        return recipes;
    }
Let's see how to use JdbcTemplate to get a single recipe record from a table.
private static String GET_RECIPE
            ="Select * from recipe where id=?";

 public Recipe getRecipe(Long id) {
            List<Recipe>recipes  = jdbcTemplate.query
                    (GET_RECIPE,
                            new BeanPropertyRowMapper
                                    (Recipe.class)
                            ,new Object[]{id});
            if(recipes.isEmpty()){
                LOGGER.debug("No Recipe found with {} ",id);
                throw new DataNotFoundException
                        ("No Recipe found with id "+id);
            }
            return recipes.get(0);
    }
The GET_RECIPE query needs to pass the id dynamically. This is done using new Object[]{id} in the code above.

Let's see how to use JdbcTemplate to delete a single recipe record from a table.
Here also we need to pass dynamic id so we will use new Object[]{id} in the JDBC template method.
private static String DELETE_RECIPE=
            "Delete from recipe where id=?";

@Override
public void deleteRecipe(Long id) {
        jdbcTemplate.update(DELETE_RECIPE,new Object[]{id});
        LOGGER.info("Recipe deleted successfully. For Id {} ",id);
}

Let's see how to use JdbcTemplate to count a set of records in a recipe table.

private static final String COUNT_RECIPE=
            "Select count(*) from recipe";

@Override
public Integer countRecipes(){
    Integer count = jdbcTemplate.queryForObject
            (COUNT_RECIPE, Integer.class);
    LOGGER.info("No of records is {} "+count);
    return count;
}
So, we learned how to use JdbcTemplate in Spring Boot to execute multiple queries. This is commonly used when interacting with databases.

Feel free to leave a comment if you encounter any issues while implementing the above solutions. We are happy to support you.

Enjoy your learning!