Spring Boot is a widely used framework for building Java-based applications. One of the key aspects of any application is the database it is using. In this blog post, we will discuss how to connect SQLServer, a popular relational database, to a Spring Boot application.



Setting up a SQLServer Database

Before we connect our Spring Boot application to the SQLServer database, we need to set up the database itself. You can either set up a local instance of SQLServer or use a remote instance. In this blog post, we will use a local instance.

To set up a local instance of SQLServer, follow these steps:

  1. Download and install the SQLServer Express edition.
  2. Launch the SQLServer Management Studio and connect to the local instance.
  3. Create a new database by right-clicking on the Databases folder and selecting New Database.
  4. Give the database a name, such as “spring_boot_db”.


Adding the Required Dependencies

Next, we need to add the required dependencies to our Spring Boot project. We will be using the Microsoft JDBC Driver for SQLServer, which can be added to the project by adding the following to the project’s pom.xml file:

<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>8.4.1.jre8</version>
</dependency>


Configuring the SQLServer Data Source

Next, we need to configure the SQLServer data source in our Spring Boot application. This can be done by adding the following to the application.properties file:

spring.datasource.url=jdbc:sqlserver://localhost;databaseName=spring_boot_db
spring.datasource.username=sa
spring.datasource.password=your_password
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

Note that you will need to replace “your_password” with the password for the “sa” user on your SQLServer instance.



Creating a Entity Class

Next, we need to create an entity class that will represent the data in our SQLServer database. This can be done by creating a Java class with the @Entity annotation, like so:

@Entity
@Table(name="employee")
public class Employee {
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Long id;
  
  @Column(name="first_name")
  private String firstName;
  
  @Column(name="last_name")
  private String lastName;
  
  // Getters and setters for the class properties.
}

Note that the @Table annotation is used to specify the name of the table in the database that this entity class will be mapped to.



Creating a Repository Class

Next, we need to create a repository class that will allow us to interact with the SQLServer database. This can be done by creating a Java interface with the @Repository annotation, like so:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
}


Creating a Service Class

Now that we have our entity class and repository class set up, we can create a service class that will use the repository to interact with the SQLServer database. This can be done by creating a Java class with the @Service annotation, like so:

@Service
public class EmployeeService {
  private final EmployeeRepository employeeRepository;
  
  public EmployeeService(EmployeeRepository employeeRepository) {
    this.employeeRepository = employeeRepository;
  }
  
  public List<Employee> findAll() {
    return employeeRepository.findAll();
  }
  
  public Employee findById(Long id) {
    return employeeRepository.findById(id).orElse(null);
  }
  
  public Employee save(Employee employee) {
    return employeeRepository.save(employee);
  }
  
  public void deleteById(Long id) {
    employeeRepository.deleteById(id);
  }
}


Creating a Controller Class

Finally, we need to create a controller class that will expose the functionality of our service class as REST endpoints. This can be done by creating a Java class with the @RestController annotation, like so:

@RestController
@RequestMapping("/employees")
public class EmployeeController {
  private final EmployeeService employeeService;
  
  public EmployeeController(EmployeeService employeeService) {
    this.employeeService = employeeService;
  }
  
  @GetMapping
  public List<Employee> findAll() {
    return employeeService.findAll();
  }
  
  @GetMapping("/{id}")
  public Employee findById(@PathVariable Long id) {
    return employeeService.findById(id);
  }
  
  @PostMapping
  public Employee save(@RequestBody Employee employee) {
    return employeeService.save(employee);
  }
  
  @DeleteMapping("/{id}")
  public void deleteById(@PathVariable Long id) {
    employeeService.deleteById(id);
  }
}


Conclusion

In this blog post, we have discussed how to connect a SQLServer database to a Spring Boot application. We have walked through the steps of setting up the database, adding the required dependencies, configuring the data source, creating entity, repository, service and controller classes, and exposing the functionality as REST endpoints. With these steps in mind, you should now be able to connect your own Spring Boot applications to SQLServer databases.



Leave a Reply