We will extend the project created in Hello World Creation. For SQL Server, the DB and table created in here will be used.
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
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>
Create the service part that describes the business logic, the persistence part that connects to the DB (issues SQL statements), and the config file.
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;
}
}
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;
}
}
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.
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.
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;
}
}
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;
}
}
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;
}
}
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 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";
}
}
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>
You can compile and create a war file with the following command.
mvn package
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.
Uploaded to GitHub. https://github.com/t-skri1/SpringSample02
It's simple, but it's now a web app. Next time, we will incorporate Spring Security.
Recommended Posts