[With sample code] Basics of Spring JDBC to learn with Blog app

Even if I imitated the sample code of the book and wrote the code, I did not know whether I understood JDBC, so I actually made a super simple blog application using Spring JDBC. (I don't implement security or transactions. It's just a confirmation of CRUD operations)

We publish the repository on GitHub. Click here for Spring-BlogApp repository

Development environment

All tools are free to use. If you can work with SQL in the terminal, you don't need SQL tools.

1. About required classes

A. What is a domain class?

Objects passed between repository and service classes

B. What is a repository class?

Perform CRUD operations on DB and return the result = Execute sql statement from repository class to get search results

C. What is a service class?

Call the repository class that performs DB operations

D. What is a controller class?

Receive front-side operation and give instructions to the appropriate service class

2. Project hierarchy

src/main/java java.png

src/main/resource resource.png

3. HTML and each form

I will omit it here. Check out the GitHub repository.

4. Creating a domain class

Create it in com.example.demo.domain.model with the name Blog.java.

Blog.java


package com.example.demo.domain.model;

import java.io.Serial;
import java.util.Date;

import lombok.Data;

@Data
public class Blog {

  private Integer articleId;
  private String articleTitle;
  private String articleBody;
  private Date createdDate;
  private Date updatedDate;
}

5. Create repository class

A. Creating an interface

Spring JDBC has JdbcTemplate and NamedParameterJdbcTemplate, assuming that they are used properly. Create an interface that can be overwritten and reused.

Create it in com.example.demo.domain.model.repository with the name BlgDao.java.

java.BlogDao.java


package com.example.demo.domain.model.repository;

import java.io.Serial;
import java.util.List;

import org.springframework.dao.DataAccessException;

import com.example.demo.domain.model.Blog;

public interface BlogDao {
  //By creating an interface, you can override it and reuse it.
  //Dao stands for Data Access Object

  //Get the number of blog tables
  public int count() throws DataAccessException;

  //Get multiple data in Blog table
  public List<Blog> selectMany() throws DataAccessException;

  //Get 1 blog table data
  public Blog selectOne(Integer articleId) throws DataAccessException;

  //Insert 1 data into Blog table
  public int insertOne(Blog blog) throws DataAccessException;

  //Update 1 blog table data
  public int updateOne(Blog blog) throws DataAccessException;

  //Delete 1 data in Blog table
  public int deleteOne(Integer articleId) throws DataAccessException;
}

B. Repository implementation

Write the process to overwrite the interface and actually perform the DB operation. Create it in com.example.demo.domain.model.repository.jdbc with the name BlogDaoJdbcImpl.

BlogDaoJdbcImpl.java


package com.example.demo.domain.model.repository.jdbc;

import java.io.Serial;
import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.demo.domain.model.Blog;
import com.example.demo.domain.model.repository.BlogDao;

@Repository
public class BlogDaoJdbcImpl implements BlogDao {

  @Autowired
  JdbcTemplate jdbc;

  //Get the number of blog tables
  @Override
  public int count() throws DataAccessException {

    int count = jdbc.queryForObject(
      "SELECT COUNT(*) FROM article",
      Integer.class
    );

    return count;
  }

  //Get all data in Blog table
  @Override
  public List<Blog> selectMany() throws DataAccessException {

    List<Map<String, Object>> getList = jdbc.queryForList(
      "SELECT * FROM article ORDER BY article_id DESC"
    );

    //Variable for returning results
    List<Blog>  blogList = new ArrayList<>();

    //Store the acquired data in List
    for (Map<String, Object> map : getList) {

      //Create Blog instance
      Blog blog = new Blog();
 
      //Store the acquired data in the Blog instance
      blog.setArticleId((Integer)map.get("article_id"));
      blog.setArticleTitle((String)map.get("article_title"));
      blog.setArticleBody((String)map.get("article_body"));
      blog.setCreatedDate((Date)map.get("created_date"));
      blog.setUpdatedDate((Date)map.get("updated_date"));

      //Store in Return List
      blogList.add(blog);
    }

    return blogList;
  }

  //Get 1 blog table data
  @Override
  public Blog selectOne(Integer articleId) throws DataAccessException {

    Map<String, Object> map = jdbc.queryForMap(
      "SELECT * FROM article WHERE article_id = ?", articleId
    );

    //Create a Blog instance
    Blog blog = new Blog();

    blog.setArticleId((Integer)map.get("article_id"));
    blog.setArticleTitle((String)map.get("article_title"));
    blog.setArticleBody((String)map.get("article_Body"));
    blog.setCreatedDate((Date)map.get("created_date"));
    blog.setUpdatedDate((Date)map.get("update_date"));

    return blog;
  }

  //Insert 1 data into Blog table
  @Override
  public int insertOne(Blog blog) throws DataAccessException {

    int rowNumber = jdbc.update(
      "INSERT INTO article("
      + " article_title,"
      + " article_body,"
      + " created_date"
      + ") VALUES (?, ?, ?)"
      , blog.getArticleTitle()
      , blog.getArticleBody()
      , blog.getCreatedDate()
    );

    return rowNumber;
  }

  //Update 1 blog table data
  @Override
  public int updateOne(Blog blog) throws DataAccessException {

    int rowNumber = jdbc.update(
      "UPDATE Blog SET"
      + " article_title = ?,"
      + " article_body = ?,"
      + " updated_date = ?"
      + " WHERE article_id = ?"
      , blog.getArticleTitle()
      , blog.getArticleBody()
      , blog.getUpdatedDate()
      , blog.getArticleId()
    );

    return rowNumber;
  }

  //Delete 1 data in Blog table
  @Override
  public int deleteOne(Integer articleId) throws DataAccessException {

    int rowNumber = jdbc.update(
      "DELETE FROM article WHERE article_id = ?", articleId
    );

    return rowNumber;
 }
}

6. Creating a service class

Create it in com.example.demo.domain.service with the name BlogService.

BlogService.java


package com.example.demo.domain.service;

import java.io.Serial;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.example.demo.domain.model.Blog;
import com.example.demo.domain.model.repository.BlogDao;

@Service
public class BlogService {

  @Autowired
  BlogDao dao;

  //Get the number of Blog table
  public int count() {
    return dao.count();
  }

  //Get all data in Blog table
  public List<Blog> selectMany() {
    return dao.selectMany();
  }

  //Get 1 blog table data
  public Blog selectOne(Integer articleId) {
    return dao.selectOne(articleId);
  }

  //Insert 1 data into Blog table
  public boolean insertOne(Blog blog) {
  
    int rowNumber = dao.insertOne(blog);

    //Variable for returning results
    boolean result = false;

    if (rowNumber > 0) {
      result = true;
    }

    return result;
  }

  //Update 1 blog table data
  public boolean updateOne(Blog blog) {

    int rowNumber = dao.updateOne(blog);

    //Variable for returning results
    boolean result = false;

    if (rowNumber > 0) {
      result = true;
    }

    return result;
  }

  //Delete 1 data in Blog table
  public boolean deleteOne(Integer articleId) {
    
    int rowNumber = dao.deleteOne(articleId);

    //Variable for returning results
    boolean result = false;

    if (rowNumber > 0) {
      result = true;
    }

    return result;
  }
}

7. Creating a controller class

HomeController.java


package com.example.demo.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;

import com.example.demo.domain.model.ArticleForm;
import com.example.demo.domain.model.Blog;
import com.example.demo.domain.model.GetDate;
import com.example.demo.domain.service.BlogService;

@Controller
public class HomeControler {

  @Autowired
  BlogService blogService;

  @GetMapping("/")
  public String getHome(Model model) {

    //Register the character string to display the article list in the article content part
    model.addAttribute("main" , "topContent :: main_content");

    //Get all the data in the Blog table
    List<Blog> blogList = blogService.selectMany();

    //model Register a blog post
    model.addAttribute("blogList", blogList);

    return "top";
  }

  @GetMapping("/post")
  public String getForm(@ModelAttribute ArticleForm form, Model model) {

    //Register the character string to display the form in the content part
    model.addAttribute("main" , "form/postFormContent :: main_content");

    return "top";
  }

  @PostMapping("/post")
  public String postForm(@ModelAttribute @Validated ArticleForm form,
    BindingResult bindingResult,
    Model model) {

    //If you get stuck in the input check, return to the new registration screen
    if (bindingResult.hasErrors()) {

      //Call the method for GET request and return to the new registration screen
      return getForm(form, model);
    }

    //Check the contents of the form on the console
    System.out.println(form);

    //Variable for insert
    Blog blog = new Blog();

    // created_Instance for date
    GetDate getDate = new GetDate();

    blog.setArticleTitle(form.getArticleTitle());
    blog.setArticleBody(form.getArticleBody());
    blog.setCreatedDate(getDate.getDate());

    //registration process
    boolean result = blogService.insertOne(blog);

    //Judgment of user registration result
    if(result == true) {
      System.out.println("inset success");
    }else {
      System.out.println("insert failure");
    }

    //Redirect to top page
    return "redirect:/";
  }

  @GetMapping("/detail/{id}")
  public String postDetail(@PathVariable("id")Integer articleId, Model model) {

    //Register the character string to display the article content in the content part
    model.addAttribute("main" , "detail :: main_content");

    Blog blog = blogService.selectOne(articleId);

    //Register blog data
    model.addAttribute("blog", blog);

    return "top";
  }
	
  @GetMapping("/update/{id}")
  public String getUpdate(@ModelAttribute ArticleForm form,
    @PathVariable("id")Integer articleId,
    Model model) {
 
    //Register the character string to display the form in the content part
    model.addAttribute("main" , "form/updateFormContent :: main_content");

    //1 acquisition process
    Blog blog = blogService.selectOne(articleId);

    form.setArticleId(blog.getArticleId());
    form.setArticleTitle(blog.getArticleTitle());
    form.setArticleBody(blog.getArticleBody());

    model.addAttribute("articleForm", form);

    return "top";
  }

  //params specifies the character string of the name tag of the submit button
  //To distinguish if the page has multiple buttons
  @PostMapping(value = "/update", params = "update")
  public String postUpdate(@ModelAttribute @Validated ArticleForm form,
    BindingResult bindingResult,
    Model model) {

    //If you get stuck in the input check, return to the newly registered image return
    if (bindingResult.hasErrors()) {

      //Call the method for GET request and return to the new registration screen
      return getForm(form, model);
    }

    //Check the contents of the form on the console
    System.out.println(form);

    //Variable for insert
    Blog blog = new Blog();

    // created_Instance for date
    GetDate getDate = new GetDate();

    blog.setArticleId(form.getArticleId());
    blog.setArticleTitle(form.getArticleTitle());
    blog.setArticleBody(form.getArticleBody());
    blog.setUpdatedDate(getDate.getDate());

    //Update process
    boolean result = blogService.updateOne(blog);

    //Judgment of registration result
    if(result == true) {
      System.out.println("insert success");
    }else {
      System.out.println("insert failure");
    }

    //Redirect to top page
    return "redirect:/";
  }

  //params specifies the character string of the name tag of the submit button
  //To distinguish if the page has multiple buttons
  @PostMapping(value = "/update", params = "delete")
  public String postDelete(@ModelAttribute ArticleForm form, Model model) {

     //Delete execution
    boolean result = blogService.deleteOne(form.getArticleId());

    if (result == true) {
      System.out.println("delete success");
    }else {
      System.out.println("delete failure");
    }

    //Redirect to top page
    return "redirect:/";
  }
}

8. Explanation

When using redirect:, it is ** when moving pages **.

@PostMapping(value = "/update", params = "delete")
public String hogehoge(...) {
  ...
  
  //Go to the top page from update
  return "redirect:/";

  // top.I get an error when I use html
  return "top";
}

When using return getForm ()

//When an error occurs in validation
//Used to keep the values registered in form and model and display the original page
if (bindingResult.hasErrors()) {

  //Return to form page
  return getForm(form, model);
}

If you don't have params, you don't have to enter value.

@PostMapping("/post")
@PostMapping(value = "/update", params = "delete")

9. Page path

/ post => Registration form page / update / {id} => Update form page / detail / {id} => Article page

10. App screenshot

Please note that all the pictures are cats

top page

トップページ

input form

/ post and/ update / {id} Add your browser's address bar / post to the new input form.

入力フォーム

Article page

/detail/{id} 記事ページ

Task

Create a ranking based on the image file uploader and the number of accesses and display it on the side.

Supplement

What is JDBC

Java Database Connectivity (JDBC) is an API for connecting Java to relational databases. Source: Wikipedia

What is Spring JDBC

In JDBC, you have to write DB connection and close processing, but with Spring JDBC, you can write it in simple code. It also matches the DB's unique error code to Spring and throws an appropriate exception.

Recommended Posts

[With sample code] Basics of Spring JDBC to learn with Blog app
Sample code to unit test a Spring Boot controller with MockMvc
[Java] Explanation of Strategy pattern (with sample code)
Settings for connecting to MySQL with Spring Boot + Spring JDBC
Compatibility of Spring JDBC and MyBatis with Spring Data JDBC (provisional)
Create Restapi with Spring Boot ((1) Until Run of App)
How to boot by environment with Spring Boot of Maven
Port C code with a lot of typecasts to Swift
A story packed with the basics of Spring Boot (solved)
Sample code to parse date and time with Java SimpleDateFormat
Learn how to customize the Navigation Bar from the sample app
Use Spring JDBC with Spring Boot
The basics of the process of making a call with an Android app
I tried connecting to MySQL using JDBC Template with Spring MVC
How to realize huge file upload with Rest Template of Spring
Java to learn with ramen [Part 1]
Create an app with Spring Boot 2
How to use Spring Data JDBC
Spring Boot programming with VS Code
Create an app with Spring Boot
To be aware of easy-to-read code
Spring5 MVC Web App Development with Visual Studio Code Hello World Creation
Sample code to get key JDBC type values in Java + H2 Database
How to read Body of Request multiple times with Spring Boot + Spring Security
I want to display images with REST Controller of Java and Spring!
How to access Socket directly with the TCP function of Spring Integration
Learn RSpec with Everyday Rails. Until you bundle install the sample app.
Minimum configuration sample to automatically release Lambda by Java with Code pipeline
[swift5] How to change the color of TabBar or the color of item of TabBar with code
Introduction to Java development environment & Spring Boot application created with VS Code