CodeTreat

Full Stack Developer Tutorials

  • About
Spring Boot, Data JPA, Hibernate with MS SQL Server RESTful CRUD app – Part 1

Spring Boot, Data JPA, Hibernate with MS SQL Server RESTful CRUD app – Part 1

Building a full-fledged REST API is very easy using Spring Boot since it has reduced the need to write lot of boilerplate code to configure lots of items in the application. Through the use of annotations, developing RESTful APIs have become easier than ever. In this tutorial we will build a REST API to perform CRUD operations with the help of Spring Boot, Spring DATA JPA, Hibernate and MS SQL.

Creating your project


Visit https://start.spring.io and download the project after selecting Web and JPA in the dependencies field. For the remaining fields, provide the following details.

  1. Group : Mention the package name of the application. In this tutorial we have mentioned the package name as com.codetreat.
  2. Artifact : Mention the artifact name which will be used to identify the module of your application. Here, we have mentioned the artifact name as sample.
  3. Dependencies : In the Search for Dependencies field, search and select Web  and JPA as shown below.

Spring Boot JPA Web

You may leave the remaining fields to its default values and click Generate Project to download the bootstrapped application.

Spring Boot Project Structure


After downloading and importing the project to the IDE, the project structure will look as shown below. The project dependencies will be downloaded in the background.

SpriSpring Data JPA Project Structure

Following the Separation of Concerns principle, let us create the following packages in our application in-order to separate it into various layers.

  • com.codetreat.demo.controller – Controller will contain the code to handle the routing of user requests to a proper service.
  • com.codetreat.demo.service – Service will contain the business logic of the application.
  • com.codetreat.demo.repository – Repository will contain the code to access the data from the database.
  • com.codetreat.demo.entity – Entity will contain the domain model of the tables present in the database.

The updated project structure should look as shown below. Note that we have created classes and an interface inside the packages.

Spring Data JPA Project Structure

Let us look in detail about each class and interface that has been created inside the packages.

Entity


In this sample, we will create a table which has three columns namely id, name and age. Let us create the entity model with the name SampleEntity.java as shown below.

package com.codetreat.sample.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "Sample")
public class SampleEntity {

	@Id
	@Column(name = "id")
	Integer Id;

	@Column(name = "name")
	String name;

	@Column(name = "age")
	Integer age;

	public Integer getId() {
		return Id;
	}

	public void setId(Integer id) {
		Id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}

}
  • @Entity – The @Entity annotation is used to denote that the specific POJO (Plain Old Java Object) class is a domain model which represents the table and its fields from the database.
  • @Table – This annotation is used to specify the table name which is mapped to the domain model. The name field will contain the table name as created in the database.
  • @Id – The @Id annotation is used to specify the primary column of the domain model. Each entity must have the @Id field specified without fail. Failing to do so will throw errors. In our example, we have specified the database column id  as our primary key.
  • @Column – The @Column annotation is used to specify the mapped field names to the column names of the database in a similar way the @Table annotation is used.

Note that all these annotations are from the package javax.persistence which is the Java Persistence API (JPA). Finally the getters and setters must be generated for all the fields to perform data operations for the specified fields.

Repository


The repository is used to perform data manipulations. It is the DAO (Data Access Object) layer that connects to the database to fetch and write data to and fro between the database and the service layer. Let use create an Interface SampleRepository.java inside the package com.codetreat.demo.repository and write the code as shown below.

package com.codetreat.sample.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.codetreat.sample.entity.SampleEntity;

@Repository
public interface SampleRepository extends CrudRepository<SampleEntity, Long> {

	@Query("FROM SampleEntity")
	public List<SampleEntity> getAll();
}

Note that we have created an Interface and not a class. This is because we have to extend the CrudRepository which is the Interface that contains the methods to perform CRUD operations. This Interface is from the Spring Data sub-project which focuses on simplifying the data operations through JPA and Hibernate.

  • @Repository – This annotation denotes that the specific class must be considered as a repository by the Spring application context. A repository class will contain the methods to perform data operations.
  • @Query – The @Query annotation is used to specify the SQL query that will be used to perform data operations on the specified table. The DML (Data Manipulation Language) commands will be specified in the parameter section of the annotation and will be enclosed in double quotes. In the above example, we have specified the query to fetch all records from the SampleEntity entity. Note that we are using the HQL (Hibernate Query Language) format for the queries. So, there is no need to write the entire query as “SELECT * FROM Sample”. Instead, specifying the HQL query “FROM SampleEntity” will be enough. Moreover, we will specify the Entity Name and not the database table name. In this sample, we have imported the SampleEntity from the com.codetreat.demo.entity package.

The getAll() method does not contain body since this is an Interface. The return type of the method signature is mentioned as a generic List of type SampleEntity.

Service


The service layer will contain the business logic of the application. In this tutorial we are just going to fetch the data from database without performing any data manipulations in the service layer. Create the class SampleService.java inside the package com.codetreat.demo.service as shown below.

package com.codetreat.sample.service;

import java.util.List;

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

import com.codetreat.sample.entity.SampleEntity;
import com.codetreat.sample.repository.SampleRepository;

@Service
public class SampleService {

	@Autowired
	SampleRepository sampleRepo;

        public List<SampleEntity> getAll() {
		return sampleRepo.getAll();
	}
}
  • @Service – This annotation is used to denote the application context that this class should be used as a service class which contains the methods of business logic of the application.
  • @Autowired – This annotation is used to wire the dependencies of the class. This is where the Dependency Injection concept is utilized. The Spring container (Application Context) will automatically manage the instantiation and object creation process for us. We denote the Spring container to handle it through this annotation. By this way, we are avoiding the need to instantiate the object manually which makes the application into a loosely coupled one.

We have imported the SampleEntity.java model and created a method named getAll() in which we are calling the getAll() method from our repository SampleRepository.java. This will return all the records from the table in a List which has the generic type as SampleEntity.

Controller


Create the class SampleController.java inside the package com.codetreat.demo.controller.

package com.codetreat.sample.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.codetreat.sample.entity.SampleEntity;
import com.codetreat.sample.service.SampleService;

@RestController
public class SampleController {

	@Autowired
	SampleService sampleService;

	@RequestMapping(value = "/sample")
        public List<SampleEntity> sample() {
		return sampleService.getAll();
	}
}

Let us look at the explanation of various sections of the above class.

  • @RestController – This annotation is combination of @Controller and @ResponseBody annotations. The @Controller annotation is used to denote that the specific class should be used as a controller which contains the methods of of routing logic to handle the user requests. The @ResponseBody annotation is used to mention that a http response must be returned by the methods defined in the class that has the @Controller annotation.Spring Boot makes it easy by combining both of these functionalities into a single annotation, so that we do not have to annotate each method of a @Controller class with the @ResponseBody annotation. Moreover, the return type of the response is by default configured as JSON which is the suitable mode of response for a REST API.
  • @Autowired – As already specified, we are wiring the SampleService.java and letting the application context to manage th object instantiation process instead of doing it manually to avoid a tightly coupled design.
  • @RequestMapping – This annotation is used to define the URI that will be used to initiate the request to be performed. In this sample, we have mentioned the URI as “/sample”, which will be triggered from the browser as “http://localhost:8080/sample“ if you are running the app from localhost.

Dependency Management (POM.xml)


The POM.xml file contains the project dependencies configuration which is managed by Maven. To configure the MS SQL Server database to work properly, we need to specify the SQL Server driver in the dependency configuration. The default POM.xml file looks as shown below.

<?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.codetreat</groupId>
	<artifactId>sample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>

	<name>sample</name>
	<description>Demo project for Spring Boot</description>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.2.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>com.microsoft.sqlserver</groupId>
			<artifactId>mssql-jdbc</artifactId>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>


</project>

As you can see, we have added the MS SQL SERVER driver dependency by adding the following code inside the <dependencies> element in the POM.xml file

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

The mssql-jdbc-x.x.x.jre8.jar will be downloaded under the Maven Dependencies section of the project after adding the above code in the dependency configuration.

Application configuration


Finally, the most important part of the application which contains all configuration details of the entire application is the application.properties file which resides under the path src/main/resources. The driver configuration must be defined in the properties file for the application to connect with the database and perform data manipulations.

spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

spring.datasource.url=jdbc:sqlserver://PC357984;databaseName=sampleDB
spring.datasource.username=sa
spring.datasource.password=password-1
  • spring.datasource.driver-class-name – In this property, we will specify the path of the SQL Driver class which is com.microsoft.sqlserver.jdbc.SQLServerDriver
  • spring.datasource.url – The database host-name must be specified in this property. In the sample, the database name is sampleDB and the host name (PC name) is PC357984. The URL must be specified in the proper format as shown above.
  • spring.datasource.username – The username of the database server.
  • spring.datasource.password – The password of the database server.

All necessary files have been created. It is time to run the application. Right click on the project and select Run As and click on Spring Boot App as shown below.

Launching Spring Boot Applicatio

Alternatively, you can access the Run As menu from the green colored play button in the toolbar as highlighter in the above image. The application should start and execute without any issues. Confirm that the application has started successfully, by checking the console for the following text.

...
18:02:57.972  INFO 11768 --- [main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
18:02:57.972  INFO 11768 --- [main] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
18:02:58.310  INFO 11768 --- [main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
18:02:58.311  INFO 11768 --- [main] o.s.j.e.a.AnnotationMBeanExporter        : Bean with name 'dataSource' has been autodetected for JMX exposure
18:02:58.315  INFO 11768 --- [main] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]
18:02:58.467  INFO 11768 --- [main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
18:02:58.471  INFO 11768 --- [main] com.codetreat.sample.SampleApplication   : Started SampleApplication in 10.615 seconds (JVM running for 11.422)

Open the web browser and hit the URI http://localhost:8080/sample. Voila!! You should be able to view the data from the DB in proper JSON format. In the next tutorial, we will perform CRUD operations which involves Create, Update and Delete in addition to the Read functionality mentioned in this tutorial.

Share this:

  • Facebook
  • Twitter
  • WhatsApp
  • Tumblr
  • Google
  • Pinterest