11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
Spring Boot + SQL Server example: CRUD
Operations Rest API
For more detail, please visit:
Spring Boot CRUD Operations example with SQL Server
1. Run a SQL Server docker container
It is mandatory to run Docker Desktop before pulling and running the SSMS docker container
We open a command prompt window and run the following command
docker run ^
-e "ACCEPT_EULA=Y" ^
-e "MSSQL_SA_PASSWORD=Luiscoco123456" ^
-p 1433:1433 ^
-d [Link]/mssql/server:2022-latest
2. Connect to the SQL Server container from SSMS
Download and install SQL Server Management Studio (SSMS) from this site:
[Link]
[Link] 1/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
3. Source Code explanation
3.1. Project folders and files structure
3.2. Project dependencies
[Link] 2/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
These are the project dependencies:
spring-boot-starter-actuator
spring-boot-starter-data-jpa
spring-boot-starter-web
mssql-jdbc
springdoc-openapi-starter-webmvc-ui
spring-boot-starter-test
[Link]
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="[Link] xmlns:xsi="[Link]
xsi:schemaLocation="[Link] [Link]
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>[Link]</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>3.1.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>[Link]</groupId>
<artifactId>spring-boot-sql-server</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-boot-sql-server</name>
<description>Spring Boot + SQL Server (MSSQL) example with JPA</description>
<properties>
<[Link]>21</[Link]>
</properties>
<dependencies>
<dependency>
<groupId>[Link]</groupId>
<artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
<groupId>[Link]</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>[Link]</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>[Link]</groupId>
<artifactId>mssql-jdbc</artifactId>
<scope>runtime</scope>
[Link] 3/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
</dependency>
<dependency>
<groupId>[Link]</groupId>
<artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
<version>2.0.3</version>
</dependency>
<dependency>
<groupId>[Link]</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>[Link]</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
3.3. Application Main entry point
[Link]
package [Link];
import [Link];
import [Link];
@SpringBootApplication
public class SpringBootSqlServerApplication {
public static void main(String[] args) {
[Link]([Link], args);
}
3.4. Data Model
[Link]
[Link] 4/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
package [Link];
import [Link].*;
@Entity
@Table(name = "tutorials")
public class Tutorial {
@Id
@GeneratedValue(strategy = [Link])
private long id;
@Column(name = "title")
private String title;
@Column(name = "description")
private String description;
@Column(name = "published")
private boolean published;
public Tutorial() {
public Tutorial(String title, String description, boolean published) {
[Link] = title;
[Link] = description;
[Link] = published;
}
public long getId() {
return id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
[Link] = title;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
[Link] = description;
}
public boolean isPublished() {
[Link] 5/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
return published;
}
public void setPublished(boolean isPublished) {
[Link] = isPublished;
}
@Override
public String toString() {
return "Tutorial [id=" + id + ", title=" + title + ", desc=" + description + ", published=
}
3.5. Repository
[Link]
package [Link];
import [Link];
import [Link];
import [Link];
public interface TutorialRepository extends JpaRepository<Tutorial, Long> {
List<Tutorial> findByPublished(boolean published);
List<Tutorial> findByTitleContaining(String title);
}
3.6. Controller
We also configure the Swagger Open API docs for each action inside the controller
TutorialController
package [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
[Link] 6/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
// @CrossOrigin(origins = "[Link]
@RestController
@RequestMapping("/api")
@Tag(name = "Tutorial", description = "The Tutorial API")
public class TutorialController {
@Autowired
TutorialRepository tutorialRepository;
@Operation(summary = "Test the API", description = "Test endpoint to verify the API is wor
@ApiResponse(description = "Success", responseCode = "200", content = @Content(mediaTy
@GetMapping("/test")
public ResponseEntity<String> test() {
return [Link]("Test endpoint response");
}
@Operation(summary = "Get all tutorials", description = "Retrieve all tutorials or filter
@ApiResponse(description = "Successful Operation", responseCode = "200", content = @Co
@ApiResponse(description = "Not Found", responseCode = "404") })
@GetMapping("/tutorials")
public ResponseEntity<List<Tutorial>> getAllTutorials(@RequestParam(required = false) Stri
try {
List<Tutorial> tutorials = new ArrayList<Tutorial>();
if (title == null) {
[Link]().forEach(tutorials::add);
} else {
[Link](title).forEach(tutorials::add);
}
if ([Link]()) {
// Add logging here
[Link]("No tutorials found");
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
}
[Link] 7/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
// Additional logging here if needed
[Link]("Found tutorials: " + [Link]());
return new ResponseEntity<>(tutorials, [Link]);
} catch (Exception e) {
// Log the exception details here
[Link]();
return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
}
}
@Operation(summary = "Get a tutorial by ID", description = "Retrieve a single tutorial by
@ApiResponse(description = "Found", responseCode = "200", content = @Content(mediaType
@ApiResponse(description = "Not Found", responseCode = "404") })
@GetMapping("/tutorials/{id}")
public ResponseEntity<Tutorial> getTutorialById(@PathVariable("id") long id) {
Optional<Tutorial> tutorialData = [Link](id);
if ([Link]()) {
return new ResponseEntity<>([Link](), [Link]);
} else {
return new ResponseEntity<>(HttpStatus.NOT_FOUND);
}
}
@Operation(summary = "Create a new tutorial", description = "Add a new tutorial to the dat
@ApiResponse(description = "Created", responseCode = "201", content = @Content(mediaTy
@ApiResponse(description = "Internal Server Error", responseCode = "500") })
@PostMapping("/tutorials")
public ResponseEntity<Tutorial> createTutorial(@RequestBody Tutorial tutorial) {
try {
Tutorial _tutorial = tutorialRepository
.save(new Tutorial([Link](), [Link](), false))
return new ResponseEntity<>(_tutorial, [Link]);
} catch (Exception e) {
return new ResponseEntity<>(null, HttpStatus.INTERNAL_SERVER_ERROR);
}
}
@Operation(summary = "Update a tutorial", description = "Update an existing tutorial by ID
@ApiResponse(description = "Successful update", responseCode = "200", content = @Conte
@ApiResponse(description = "Not found", responseCode = "404")
})
@PutMapping("/tutorials/{id}")
public ResponseEntity<Tutorial> updateTutorial(@PathVariable("id") long id, @RequestBody T
Optional<Tutorial> tutorialData = [Link](id);
if ([Link]()) {
Tutorial _tutorial = [Link]();
_tutorial.setTitle([Link]());
_tutorial.setDescription([Link]());
_tutorial.setPublished([Link]());
return new ResponseEntity<>([Link](_tutorial), [Link]);
} else {
[Link] 8/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
return new ResponseEntity<>(HttpStatus.NOT_FOUND);
}
}
@Operation(summary = "Delete a tutorial", description = "Delete a tutorial by ID", respons
@ApiResponse(description = "Successful deletion", responseCode = "204"),
@ApiResponse(description = "Internal server error", responseCode = "500")
})
@DeleteMapping("/tutorials/{id}")
public ResponseEntity<HttpStatus> deleteTutorial(@PathVariable("id") long id) {
try {
[Link](id);
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
} catch (Exception e) {
return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
}
}
@Operation(summary = "Delete all tutorials", description = "Delete all tutorials from the
@ApiResponse(description = "Successful deletion", responseCode = "204"),
@ApiResponse(description = "Internal server error", responseCode = "500")
})
@DeleteMapping("/tutorials")
public ResponseEntity<HttpStatus> deleteAllTutorials() {
try {
[Link]();
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
} catch (Exception e) {
return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
}
@GetMapping("/tutorials/published")
public ResponseEntity<List<Tutorial>> findByPublished() {
try {
List<Tutorial> tutorials = [Link](true);
if ([Link]()) {
return new ResponseEntity<>(HttpStatus.NO_CONTENT);
}
return new ResponseEntity<>(tutorials, [Link]);
} catch (Exception e) {
return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
}
}
3.7. [Link]
[Link] 9/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
We configure the database connection string and Swagger options:
[Link]= jdbc:sqlserver://localhost:1433;encrypt=true;trustServerCertificate=tru
[Link]= sa
[Link]= Luiscoco123456
[Link]= [Link]
[Link]-auto= update
#[Link]=false
#[Link]=false
#[Link]-to-scan=[Link]
[Link]=/bezkoder-documentation
[Link]=/bezkoder-api-docs
#[Link]=method
#[Link]=alpha
[Link]=true
[Link]=true
[Link]-url=[Link]
[Link]-url=[Link]
3.8. Swagger configuration
[Link]
package [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
import [Link];
@Configuration
public class OpenAPIConfig {
@Value("${[Link]-url}")
private String devUrl;
@Value("${[Link]-url}")
private String prodUrl;
[Link] 10/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
@Bean
public OpenAPI myOpenAPI() {
Server devServer = new Server();
[Link](devUrl);
[Link]("Server URL in Development environment");
Server prodServer = new Server();
[Link](prodUrl);
[Link]("Server URL in Production environment");
Contact contact = new Contact();
[Link]("bezkoder@[Link]");
[Link]("BezKoder");
[Link]("[Link]
License mitLicense = new License().name("MIT License").url("[Link]
Info info = new Info()
.title("Tutorial Management API")
.version("1.0")
.contact(contact)
.description("This API exposes endpoints to manage tutorials.").termsOfService("https:
.license(mitLicense);
return new OpenAPI().info(info).servers([Link](devServer, prodServer));
}
}
4. Run Spring Boot application
We run the application in VSCode with the following command
mvn spring-boot:run
These are the application endpoints defined in the Controller
Methods Urls Actions
POST /api/tutorials create new Tutorial
GET /api/tutorials retrieve all Tutorials
GET /api/tutorials/:id retrieve a Tutorial by :id
PUT /api/tutorials/:id update a Tutorial by :id
DELETE /api/tutorials/:id delete a Tutorial by :id
DELETE /api/tutorials delete all Tutorials
[Link] 11/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
GET /api/tutorials/published find all published Tutorials
GET /api/tutorials?title=[keyword] find all Tutorials which title contains keyword
We navigate to the Swagger OpenAPI documentation: [Link]
We can sent a GET request to see all the tutorials stored in the SQL Sever database
[Link] 12/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
We confirm with the information obtained from the database
[Link] 13/14
11/4/24, 8:39 Spring Boot + SQL Server example: CRUD Operations Rest API
[Link] 14/14