Spring Boot2 Web application development with Visual Studio Code SQL Server connection

Introduction

This is the Spring Boot version of the SQL Server connection web application created with here.

environment

OS:Windows 10 Pro 64bit DB:SQL Server 2019(Cent OS 8 on Hyper-V) Editor:Visual Studio Code 1.44.2 JDK:AdoptOpenJDK 11.0.6+10 x64

Creating a template

I created it with the following settings in spring initializr (https://start.spring.io/).

Expand the created template to "D: \ JAVA \ Project".

pom.xml The JDBC of SQL Server is jre8 version, so change it to jre11 version. Add "mssql-jdbc.version" in properties. (It worked without any changes, but just in case)

<properties>
	<java.version>11</java.version>
	<mssql-jdbc.version>8.2.2.jre11</mssql-jdbc.version>
</properties>

application.properties Describe the DB connection information.

application.properties

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.url=jdbc:sqlserver://xxx:1433;databaseName=Training01;QuotedID=NO
spring.datasource.username=xxx
spring.datasource.password=xxx

entity creation

Create an entity that receives data from the DB.

D:\JAVA\Project\bootSample2\src\main\java\com\example\bootSample2\persistence
└─entity
  └─ProductsMaster.java

ProductsMaster.java

package com.example.bootSample2.persistence.entity;

import lombok.Data;

@Data
public class ProductsMaster {
    private String ProductsCode;
    private String ProductsName;
    private Integer UnitPrice;

    public ProductsMaster() {}

    public ProductsMaster(
        String ProductsCode,
        String ProductsName,
        Integer UnitPrice
        ) {
            this.ProductsCode = ProductsCode;
            this.ProductsName = ProductsName;
            this.UnitPrice = UnitPrice;
        }
}

repository creation

Create a repository that connects to the DB and issues SQL statements.

D:\JAVA\Project\bootSample2\src\main\java\com\example\bootSample2\persistence
└─repository
  ├─ProductsMasterRepository.java
  └─ProductsMasterRepositoryImpl.java

ProductsMasterRepository.java

package com.example.bootSample2.persistence.repository;

import java.util.List;

import com.example.bootSample2.persistence.entity.ProductsMaster;

public interface ProductsMasterRepository {
    List<ProductsMaster> productsMasterList();
}

ProductsMasterRepositoryImpl.java

package com.example.bootSample2.persistence.repository;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.bootSample2.persistence.entity.ProductsMaster;

@Repository
public class ProductsMasterRepositoryImpl implements ProductsMasterRepository {
	@Autowired
    NamedParameterJdbcTemplate jdbcTemplate;

	@Override
	public List<ProductsMaster> productsMasterList() {
		String sql = "SELECT * FROM ProductsMaster ORDER BY ProductsCode;";

        List<ProductsMaster> pMList = jdbcTemplate.query(sql,
                (rs, rowNum) -> new ProductsMaster(
                        rs.getString("ProductsCode"),
                        rs.getString("ProductsName"),
                        rs.getInt("UnitPrice")
                        )
                );

        return pMList;
	}
}

service creation

Create a service that will be the business logic.

D:\JAVA\Project\bootSample2\src\main\java\com\example\bootSample2
└─service
  ├─ProductsMasterService.java
  └─ProductsMasterServiceImpl.java

ProductsMasterService.java

package com.example.bootSample2.service;

import java.util.List;

import com.example.bootSample2.persistence.entity.ProductsMaster;

public interface ProductsMasterService {
    List<ProductsMaster> productsMasterList();
}

ProductsMasterServiceImpl.java

package com.example.bootSample2.service;

import java.util.List;

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

import com.example.bootSample2.persistence.entity.ProductsMaster;
import com.example.bootSample2.persistence.repository.ProductsMasterRepository;

@Service
public class ProductsMasterServiceImpl implements ProductsMasterService {
	@Autowired
	ProductsMasterRepository productsMasterRepository;

	@Override
	public List<ProductsMaster> productsMasterList() {
		List<ProductsMaster> pMList = productsMasterRepository.productsMasterList();

        return pMList;
	}
}

form creation

Create a form that receives I / O values in view.

D:\JAVA\Project\bootSample2\src\main\java\com\example\bootSample2\web
└─form
  └─ProductsMasterForm.java

ProductsMasterForm.java

package com.example.bootSample2.web.form;

import java.util.List;

import com.example.bootSample2.persistence.entity.ProductsMaster;

import lombok.Data;

@Data
public class ProductsMasterForm {
	private List<ProductsMaster> pmList;
}

Create controller

D:\JAVA\Project\bootSample2\src\main\java\com\example\bootSample2\web
└─controller
  └─ProductsMasterController.java

ProductsMasterController.java

package com.example.bootSample2.web.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import com.example.bootSample2.service.ProductsMasterService;
import com.example.bootSample2.web.form.ProductsMasterForm;

@Controller
@RequestMapping("/productsMaster")
public class ProductsMasterController {
	@Autowired
	ProductsMasterService productsMasterService;

	@GetMapping("/index")
	public String indexGet(Model model) {
		ProductsMasterForm productsMasterForm = new ProductsMasterForm();
		productsMasterForm.setPmList(productsMasterService.productsMasterList());

		model.addAttribute("productsMasterForm", productsMasterForm);
		return "productsMaster/index";
	}

}

RootController.java

package com.example.bootSample2.web.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class RootController {
    @GetMapping("/")
    public String root() {
    	return "redirect:productsMaster/index";
    }
}

view creation

Create a "productsMaster" folder in "D: \ JAVA \ Project \ bootSample2 \ src \ main \ resources \ templates ". Create index.html in the productsMaster folder.

D:\JAVA\Project\bootSample2\src\main\resources\templates
└─productsMaster
  └─index.html

index.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="http://www.thymeleaf.org">

<head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
	<meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Spring5 MVC sqlSample01</title>
</head>
<body>
    <h1>Hello Spring Products List</h1>
    <table border="1">
		<thead>
			<tr>
				<th>Product code</th>
				<th>product name</th>
				<th>unit price</th>
			</tr>
		</thead>
		<tbody>
			<tr th:each="pm : ${productsMasterForm.pmList}" th:object="${pm}">
		    	<td th:text="*{ProductsCode}"></td>
		    	<td th:text="*{ProductsName}"></td>
		        <td th:text="*{UnitPrice}"></td>
			</tr>
		</tbody>
	</table>
</body>
</html>

Operation check

Press the "F5" key to execute. http://localhost:8080/ Please visit. Make sure you are automatically redirected to [http: // localhost: 8080 / productsMaster / index](http: // localhost: 8080 / productsMaster / index).

It is OK if the following page is displayed.

This sample source

Uploaded to GitHub. https://github.com/t-skri1/SpringSample04

Summary

The contents of the source remain the same, except that the hierarchical structure and the location of the View are different from non-Boot. The other difference is

--DB connection information is described in "application.properties" --The version specification method in pom.xml is different

Will be.

DevTool has been added, so you can hot deploy. This is very convenient.