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 /)".
Click sqlSample-1.0-SNAPSHOT.
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