SpringBoot with MS SQL Server
What is MS SQL Server?
Microsoft SQL Server (MS SQL Server) is a relational database management system (RDBMS) developed by Microsoft. As a database server, it stores and retrieves data as requested by other software applications on the same computer or a remote computer using the client-server model. Microsoft provides APIs to access SQL Server over the internet as a web service.
MS SQL Server is a popular choice for enterprise applications, as it offers a wide range of features and scalability. It is also used in a variety of other industries, including financial services, healthcare, and retail.
Some of the key features of MS SQL Server include:
Support for ANSI SQL, the standard SQL language
T-SQL (Transact-SQL), a proprietary language that extends ANSI SQL
A variety of database engines, including the Enterprise Edition, Standard Edition, and Express Edition
Scalability and performance features, such as clustering and partitioning
Integration with other Microsoft products, such as Microsoft Visual Studio and Microsoft Office
MS SQL Server is a powerful and versatile RDBMS that can be used for various purposes. If you are looking for a database server for your enterprise applications, MS SQL Server is an excellent option.
Benefits of using MS SQL Server
Some of the benefits of using MS SQL Server include:
Performance: MS SQL Server is known for its high performance, even with large databases. It offers a variety of features to improve performance, such as caching, indexing, and partitioning.
Scalability: MS SQL Server can be scaled to meet the needs of even the most demanding applications. It can be deployed on-premises or in the cloud, and it can be scaled horizontally or vertically.
Security: MS SQL Server offers a wide range of security features to protect your data. These features include encryption, auditing, and role-based access control.
Integration: MS SQL Server integrates with other Microsoft products, such as Microsoft Visual Studio and Microsoft Office. This makes it easy to develop and deploy applications that use MS SQL Server.
Support: Microsoft offers a wide range of support options for MS SQL Server, including documentation, training, and technical support.
Cost-effectiveness: MS SQL Server is available in a variety of editions, so you can choose the one that best meets your needs and budget.
Ease of use: MS SQL Server is relatively easy to use, even for users with limited database experience.
Flexibility: MS SQL Server can be used with a variety of programming languages and platforms.
Community support: There is a large and active community of MS SQL Server users and developers who can provide support and advice.
Connecting to MS SQL Server
To connect to MSSQL Server in a Spring Boot application, you need to add the following dependency to your pom.xml file:
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<scope>runtime</scope>
</dependency>You also need to configure the following properties in your application.properties file:
spring.jpa.properties.hibernate.format_sql=true
spring.datasource.url= jdbc:sqlserver://localhost:1433;encrypt=true;trustServerCertificate=true;databaseName=sqlserver_db
spring.datasource.username= sqlserver
spring.datasource.password= admin
spring.jpa.properties.hibernate.dialect= org.hibernate.dialect.SQLServerDialect
spring.jpa.hibernate.ddl-auto= updateStoring data in SQL Server using Spring Boot
Once you have connected your Spring Boot application to an MS SQL Server database, you can start storing data in the database. You can do this by using Spring Data JPA. Spring Data JPA is a framework that makes it easy to access data from a relational database using Java.
To use Spring Data JPA, you need to create a data model. The data model is a Java representation of the tables in your MS SQL Server database. You can create a data model by using the following annotations:
@Entity: This annotation specifies that the class is a JPA entity.
@Table: This annotation specifies the table in the MS SQL Server database that the entity maps to.
@Id: This annotation specifies the primary key of the entity.
We can create a data model as:
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "employee")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "empid")
private Integer id;
@Column(name = "ename")
private String name;
@Column(name = "address")
private String address;
}Once you have created a data model, you can start storing data in the database by using Spring Data JPA repositories. A repository is a Spring Data JPA class that provides access to a specific data model.
To create a repository, you need to extend the JpaRepository interface. The JpaRepository interface provides several methods for accessing data from the database, such as save(), findById(), and findAll().
Creating a CRUD REST API with Spring Boot and MS SQL Server
Once you have stored some data in the MS SQL Server database, you can start creating a CRUD REST API. A CRUD REST API is a REST API that provides methods for creating, reading, updating, and deleting (CRUD) data from a database.
To create a CRUD REST API, you need to create a Spring Boot controller. A Spring Boot controller is a class that maps HTTP requests to methods in your application.
To create a CRUD REST API controller, you need to extend the RestController interface. The RestController interface provides several methods for mapping HTTP requests to methods in your application, such as @GetMapping(), @PostMapping(), and @PutMapping().
For example, the following code shows a Spring Boot controller that exposes CRUD endpoints for a Employee entity:
@RestController
@RequestMapping(value = "/api/v1")
public class EmployeeController {
@Autowired
private EmpoyeeService empoyeeService;
@GetMapping(value = "/employees")
@ResponseStatus(HttpStatus.OK)
public List<EmployeeDTO> employees() {
return empoyeeService.employees();
}
@GetMapping(value = "/employees/{id}")
@ResponseStatus(HttpStatus.OK)
public EmployeeDTO employee(@PathVariable(value = "id") Integer empId) {
return empoyeeService.employee(empId);
}
@PostMapping(value = "/employees")
@ResponseStatus(HttpStatus.CREATED)
public EmployeeDTO save(@RequestBody EmployeeRequest emp) {
return empoyeeService.save(emp);
}
@DeleteMapping(value = "/employees")
@ResponseStatus(HttpStatus.OK)
public String deleteAll() {
return empoyeeService.deleteAll();
}
@DeleteMapping(value = "/employees/{id}")
@ResponseStatus(HttpStatus.OK)
public String delete(@PathVariable(value = "id") Integer empId) {
return empoyeeService.delete(empId);
}
@PutMapping(value = "/employees")
@ResponseStatus(HttpStatus.OK)
public EmployeeDTO update(@RequestBody EmployeeRequest emp) {
return empoyeeService.update(emp);
}
}Once you have created the CRUD REST API controller, you can start testing it using a REST client, such as Postman.
Testing your Spring Boot application with MS SQL Server
Once you have created your Spring Boot application, you can start testing it using a REST client, such as Postman. To test your application, you need to send HTTP requests to the endpoints that you have created.
This application will start a Spring Boot server that can be accessed at http://localhost:8080. The server will provide a REST API that allows you to interact with the MS SQL Server database.
For example, to get all the employees, you would send an HTTP GET request to the /api/v1/employees endpoint. To test the add employee, you would send an HTTP POST request to the /api/v1/save endpoint with the JSON representation of an Employee object in the request body. Similar to that you can test all other endpoints.
Conclusion
In this blog post, I showed you how to connect to MS SQL Server in a Spring Boot application. We also showed you how to use Spring JDBC and Spring Data JPA to interact with MS SQL Server.
Source code reference
The source code for the examples in this blog can be found on GitHub:
https://github.com/sauravkumarshah/springboot-with-sqlserver