[JAVA] Spring5 MVC web application development with Visual Studio Code SQL Server connection

Introduction

We will extend the project created in Hello World Creation. For SQL Server, the DB and table created in here will be used.

environment

OS:Windows 10 Pro 64bit DB:SQL Server 2019(Cent OS 8 on Hyper-V) Editor:Visual Studio Code 1.42.1 JDK:AdoptOpenJDK 11.0.6+10 x64 Apache Maven:v3.6.3 Apache Tomcat:v9.0.31

Project creation

I copied the project created in Hello World to "D: \ JAVA \ Project \ sqlSample" and created it.

pom.xml Add the Repository required to access SQL Server.

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>${spring.version}</version>
	<scope>compile</scope>
</dependency>

<dependency>
	<groupId>com.microsoft.sqlserver</groupId>
    	<artifactId>mssql-jdbc</artifactId>
    	<version>8.2.1.jre11</version>
    	<scope>runtime</scope>
</dependency>

<dependency>
	<groupId>com.zaxxer</groupId>
	<artifactId>HikariCP</artifactId>
	<version>3.4.2</version>
	<scope>compile</scope>
</dependency>

The entire pom.xml.

pom.xml


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>com.example</groupId>
	<artifactId>sqlSample1</artifactId>
	<version>1.0-SNAPSHOT</version>
	<packaging>war</packaging>

 
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>11</java.version>
		<spring.version>5.2.4.RELEASE</spring.version>
        <!-- web.Run the build even if you don't have xml-->
        <failOnMissingWebXml>false</failOnMissingWebXml>
	</properties>

	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<source>${java.version}</source>
					<target>${java.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.11</version>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${spring.version}</version>
			<scope>compile</scope>
		</dependency>

		<dependency>
    		<groupId>org.springframework</groupId>
    		<artifactId>spring-context-support</artifactId>
    		<version>${spring.version}</version>
    		<scope>compile</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>${spring.version}</version>
			<scope>compile</scope>
		</dependency>

		<dependency>
    		<groupId>com.microsoft.sqlserver</groupId>
    		<artifactId>mssql-jdbc</artifactId>
    		<version>8.2.1.jre11</version>
    		<scope>runtime</scope>
		</dependency>

		<dependency>
		    <groupId>com.zaxxer</groupId>
		    <artifactId>HikariCP</artifactId>
		    <version>3.4.2</version>
		    <scope>compile</scope>
		</dependency>

		<dependency>
			<groupId>org.thymeleaf</groupId>
			<artifactId>thymeleaf-spring5</artifactId>
			<version>3.0.11.RELEASE</version>
			<scope>compile</scope>
		</dependency>

		<dependency>
			<groupId>org.thymeleaf.extras</groupId>
			<artifactId>thymeleaf-extras-java8time</artifactId>
			<version>3.0.4.RELEASE</version>
			<scope>compile</scope>
		</dependency>

		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>4.0.1</version>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.10</version>
			<scope>provided</scope>
		</dependency>
	</dependencies>
</project>

Model creation

Create the service part that describes the business logic, the persistence part that connects to the DB (issues SQL statements), and the config file.

service department creation

Create a "service" folder in "D: \ JAVA \ Project \ sqlSample \ src \ main \ java \ com \ example". Create a config folder directly under it. Create ServiceConfig.java in the config folder.

D:\JAVA\Project\sqlSample\src\main\java\com\example
└─service
    └─config
        └ServiceConfig.java

ServiceConfig.java


package com.example.service.config;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@ComponentScan(basePackages = "com.example.service")
@EnableTransactionManagement
public class ServiceConfig {
	 @Bean
	    public PlatformTransactionManager transactionManager(DataSource dataSource) {
	        DataSourceTransactionManager transactionManager =
	                new DataSourceTransactionManager(dataSource);
	        return transactionManager;
	    }
}

Creation of persistence department

Create a "persistence" folder in "D: \ JAVA \ Project \ sqlSample \ src \ main \ java \ com \ example". Create a config folder, entity folder, and repository folder directly under it. Create PersistenceConfig.java in the config folder.

D:\JAVA\Project\sqlSample\src\main\java\com\example
└─persistence
    ├─config
    │ └─PersistenceConfig.java
    ├─entity
    └─repository

PersistenceConfig.java describes the settings for connecting to SQL Server.

PersistenceConfig.java


package com.example.persistence.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.zaxxer.hikari.HikariDataSource;

@Configuration
@ComponentScan(basePackages = "com.example.persistence.repository")
@PropertySource("classpath:jdbc.properties")
public class PersistenceConfig {
	@Bean
    public DataSource dataSource(@Value("${jdbc.driverClassName}") String driverClassName,
                                 @Value("${jdbc.url}") String url,
                                 @Value("${jdbc.username}") String username,
                                 @Value("${jdbc.password}") String password) {
		HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setJdbcUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);

        dataSource.setMinimumIdle(10);
        dataSource.setMaximumPoolSize(300);
        dataSource.setConnectionInitSql("SELECT 0");

        return dataSource;
    }

    @Bean
    public NamedParameterJdbcTemplate jdbcTemplate(DataSource dataSource) {
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
        return jdbcTemplate;
    }
}

Resources department created

Create a "resources" folder in "D: \ JAVA \ Project \ sqlSample \ src \ main". Create jdbc.properties in the resources folder.

D:\JAVA\Project\sqlSample\src\main
└─resources
  └─jdbc.properties

Describes the connection information for connecting to SQL Server.

jdbc.properties


jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://xxx:1433;databaseName=Training01;QuotedID=NO //Specify the SQL Server host name or IP address.
jdbc.username=xxx //Specify the connection user name.
jdbc.password=xxx //Specify the password of the connecting user.

WebAppInitializer.java modified

Add PersistenceConfig.class and ServiceConfig.class to getRootConfigClasses so that Model for DB connection and business logic can be used.

@Override
protected Class<?>[] getRootConfigClasses() {
    return new Class<?>[] {PersistenceConfig.class, ServiceConfig.class};
}

The entire WebAppInitializer.java.

WebAppInitializer.java


package com.example.web.config;

import java.nio.charset.StandardCharsets;

import javax.servlet.Filter;

import com.example.persistence.config.PersistenceConfig;
import com.example.service.config.ServiceConfig;

import org.springframework.web.filter.CharacterEncodingFilter;
import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class WebAppInitializer extends AbstractAnnotationConfigDispatcherServletInitializer {
	/**
     *Specifies Java Config classes for non-Spring MVC, such as business logic.
     */
    @Override
    protected Class<?>[] getRootConfigClasses() {
        return new Class<?>[] {PersistenceConfig.class, ServiceConfig.class};
    }

    /**
     *Specifies the Java Config class for Spring MVC.
     */
    @Override
    protected Class<?>[] getServletConfigClasses() {
        return new Class<?>[] {MvcConfig.class};
    }

    /**
     *Specifies the URL pattern for the DispatcherServlet.
     * "/"By specifying, DispatcherServlet receives all requests.
     */
    @Override
    protected String[] getServletMappings() {
        return new String[]{"/"};
    }

    /**
     *Specify the Servlet filter.
     *If there are multiple filters, they will be executed in the order specified in the array.
     */
    @Override
    protected Filter[] getServletFilters() {
        return new Filter[]{
                new CharacterEncodingFilter(StandardCharsets.UTF_8.name(), true)};
    }
}

This is the preparation.

entity creation

Create an entity that receives data from the DB.

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

ProductsMaster.java


package com.example.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\sqlSample\src\main\java\com\example\persistence
└─repository
  ├─ProductsMasterRepository.java
  └─ProductsMasterRepositoryImpl.java

ProductsMasterRepository.java


package com.example.persistence.repository;

import java.util.List;

import com.example.persistence.entity.ProductsMaster;

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

ProductsMasterRepositoryImpl.java


package com.example.persistence.repository;

import java.util.List;

import com.example.persistence.entity.ProductsMaster;

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

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

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

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

        return pMList;
	}
}

service creation

Create a service that will be the business logic. There is no need to create it in this content, but I will create it as an example.

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

ProductsMasterService.java


package com.example.service;

import java.util.List;

import com.example.persistence.entity.ProductsMaster;

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

ProductsMasterServiceImpl.java


package com.example.service;

import java.util.List;

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

import com.example.persistence.entity.ProductsMaster;
import com.example.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" folder in "D: \ JAVA \ Project \ sqlSample \ src \ main \ java \ com \ example \ web". Create ProductsMasterForm.jave in the form folder.

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

ProductsMasterForm.java


package com.example.web.form;

import java.util.List;

import com.example.persistence.entity.ProductsMaster;

import lombok.Data;

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

Create controller

Create ProductsMasterController.java in "D: \ JAVA \ Project \ sqlSample \ src \ main \ java \ com \ example \ web \ controller".

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

ProductsMasterController.java


package com.example.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.service.ProductsMasterService;
import com.example.web.form.ProductsMasterForm;

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

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

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

}

view creation

Create a "productsMaster" folder in "D: \ JAVA \ Project \ sqlSample \ src \ main \ webapp \ WEB-INF \ templates". Create index.html in the productsMaster folder.

D:\JAVA\Project\sqlSample\src\main\webapp\WEB-INF\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>

Compile / Package

You can compile and create a war file with the following command.

mvn package

Operation check

Command palette

Tomcat: Add Tomcat Server

The Tomcat folder selection dialog is displayed. Select the Tomcat folder (D: \ JAVA \ Tomcat \ apache-tomcat-9.0.31).

Command palette

Tomcat: Run on Tomcat Server

A war file selection dialog will be displayed. Select the war file (D: \ JAVA \ Project \ SpringSample01 \ target \ sqlSample1-1.0-SNAPSHOT.war) created by "mvn package".

Please access "[http: // localhost: 8080 /](http: // localhost: 8080 /)". tomcat4.jpg

Click sqlSample-1.0-SNAPSHOT.

sqlSample1.jpg

When modifying the code → checking the operation, the war file placed in Tomact was not reflected properly unless it was deleted each time. I wish I could do a hot deploy, I couldn't find it.

This sample source

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

Summary

It's simple, but it's now a web app. Next time, we will incorporate Spring Security.

Recommended Posts

Spring5 MVC web application development with Visual Studio Code SQL Server connection
Spring Boot2 Web application development with Visual Studio Code SQL Server connection
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]
Spring5 MVC Web App Development with Visual Studio Code Hello World Creation
Spring Boot2 Web application 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
Start web application development with Spring Boot
Build Java program development environment with Visual Studio Code
Java web application development environment construction with VS Code (struts2)
Use PlantUML with Visual Studio Code
Run WEB application with Spring Boot + Thymeleaf
A record of setting up a Java development environment with Visual Studio Code
Introduction to Java development environment & Spring Boot application created with VS Code
Experience .NET 5 with Docker and Visual Studio Code
Create a web api server with spring boot
Getting started with Java programs using Visual Studio Code
Why can I develop Java with Visual Studio Code?
Roughly the flow of web application development with Rails.
Web application development memo with MVN, Tomcat, JSP / Servlet with VScode
The first WEB application with Spring Boot-Making a Pomodoro timer-
To receive an empty request with Spring Web MVC @RequestBody
Spring Boot application that specifies DB connection settings with parameters
Java Config with Spring MVC
[Spring Boot] Web application creation
Web application development article summary
Java in Visual Studio Code
Web application built with docker (1)
Rails web server and application server
Comparison of WEB application development with Rails and Java Servlet + JSP
How to use PlantUML with Visual Studio Code (created on October 30, 2020)
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
[Probably the easiest] WEB application development with Apache Tomcat + Java Servlet
What I learned from doing Java work with Visual Studio Code
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