[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
- PC: macOS Catalina 10.15.4
- IDE: Eclipse 2020-03(4.15.0)
- SQL: PostgreSQL 12.2 --SQL Tools: DBeaver 7.0.3.202004191823
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
- There are names such as model class and DTO (Data Transger Object).
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
src/main/resource
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