[JAVA] 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/).

2020-05-05 (1).png

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. springbootsql1.jpg

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.

Recommended Posts

Spring Boot2 Web application development with Visual Studio Code SQL Server connection
Spring5 MVC web application development with Visual Studio Code SQL Server connection
Spring Boot2 Web application development with Visual Studio Code Hello World creation
Spring5 MVC Web application development with Visual Studio Code Spring Security usage 1/3 [Preparation]
Spring5 MVC Web application development with Visual Studio Code Maven template creation
Spring5 MVC Web application development with Visual Studio Code Spring Security usage 2/3 [Page creation 1/2]
Spring5 MVC Web application development with Visual Studio Code Spring Security usage 3/3 [Page creation 2/2]
Start web application development with Spring Boot
Spring5 MVC Web App Development with Visual Studio Code Hello World Creation
Spring5 MVC Web application development with Visual Studio Code Environment construction (Installation of JDK11, Maven, Tomcat, Visual Studio Code)
Build WebAPP development environment with Java + Spring with Visual Studio Code
Run WEB application with Spring Boot + Thymeleaf
Create a web api server with spring boot
Introduction to Java development environment & Spring Boot application created with VS Code
Build Java program development environment with Visual Studio Code
[Spring Boot] Web application creation
Java web application development environment construction with VS Code (struts2)
Spring Boot application that specifies DB connection settings with parameters
Use PlantUML with Visual Studio Code
Spring Boot application development in Eclipse
Spring Boot application code review points
Hot deploy with Spring Boot development
Spring Boot programming with VS Code
Inquiry application creation with Spring Boot
Implement a simple Web REST API server with Spring Boot + MySQL
Let's make a book management web application with Spring Boot part1
Let's make a book management web application with Spring Boot part3
Let's make a book management web application with Spring Boot part2
Processing at application startup with Spring Boot
Try using Spring Boot with VS Code
Launch Nginx + Spring Boot application with docker-compose
A record of setting up a Java development environment with Visual Studio Code
Deploy the WEB application by Spring Boot to Tomcat server as WAR
Coexistence of Flyway in the embedded database (h2) of the development environment and the release database (SQL Server) with Spring Boot
[Spring Boot] Precautions when developing a web application with Spring Boot and placing war on an independent Tomcat server
Experience .NET 5 with Docker and Visual Studio Code
Configure Spring Boot application with maven multi module
I tried to clone a web application full of bugs with Spring Boot
Create Spring Boot environment with Windows + VS Code
Create a Spring Boot development environment with docker
Create Spring Cloud Config Server with security with Spring Boot 2.0
From creating a Spring Boot project to running an application with VS Code
What I was addicted to when developing a Spring Boot application with VS Code
A story that stumbled when deploying a web application created with Spring Boot to EC2
Getting started with Java programs using Visual Studio Code
Try using OpenID Connect with Keycloak (Spring Boot application)
Why can I develop Java with Visual Studio Code?
Until you start development with Spring Boot in eclipse 1
Roughly the flow of web application development with Rails.
Until you start development with Spring Boot in eclipse 2
Spring Boot 2.3 Application Availability
Download with Spring Boot
Web application development memo with MVN, Tomcat, JSP / Servlet with VScode
Implement REST API with Spring Boot and JPA (Application Layer)
The first WEB application with Spring Boot-Making a Pomodoro timer-
Extract SQL to property file with jdbcTemplate of spring boot
Sample web application that handles multiple databases in Spring Boot 1.5
Try hitting the zip code search API with Spring Boot
Introducing Spring Boot2, a Java framework for web development (for beginners)
Domain Driven Development with Java and Spring Boot ~ Layers and Modules ~
[Java] Sample project for developing web applications with Spring Boot