In this tutorials I am going to show you how to work with Spring Boot JdbcTemplate using MySql Database.
Spring Boot JdbcTemplate :
Technologies :
- Spring Boot-1.5.10
- Spring-Boot-Starter-jdbc
- Java 8
- MySql 5.5
Project Structure :
Spring Boot JdbcTemplate Example :
As part of this tutorials, I am going to implement a complete CRUD operations using Spring Boot JdbcTemplate.
Recommended : Spring JDBCTemplate Example
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.onlinetutorialspoint</groupId>
<artifactId>SpringBoot-JDBC-MySQL</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>SpringBoot-JDBC-MySQL</name>
<description>Demo project for Spring Boot JdbcTemplate</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.10.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-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Defining all necessary data source, authentication credentials.
application.properties
# Database
server.port=8080
spring.datasource.driver-class-name: com.mysql.jdbc.Driver
spring.datasource.url: jdbc:mysql://localhost:3306/otp
spring.datasource.username: root
spring.datasource.password: 123456Database Preparation :
Create your mysql database (otp) and create below item table under otp database.
item Table
CREATE TABLE `item` (
`id` INT(11) NULL DEFAULT NULL,
`name` VARCHAR(50) NULL DEFAULT NULL,
`category` VARCHAR(50) NULL DEFAULT NULL
)
ENGINE=InnoDB;
Created an item table, which represents all items and I am going to do all CRUD operations on this table.
Create Item Model to represent the above table.
Item.java
package com.onlinetutorialspoint.model;
public class Item {
private int id;
private String name;
private String category;
public Item() {
}
public Item(int id, String name, String category) {
this.id = id;
this.name = name;
this.category = category;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getCategory() {
return category;
}
public void setCategory(String category) {
this.category = category;
}
}
Creating Item Repository :
This is the key class of our example, under which all CRUD operations are happening.
ItemRepository.java
package com.onlinetutorialspoint.repo;
import com.onlinetutorialspoint.model.Item;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
public class ItemRepository {
@Autowired
JdbcTemplate template;
/*Getting all Items from table*/
public List<Item> getAllItems(){
List<Item> items = template.query("select id, name,category from item",(result,rowNum)->new Item(result.getInt("id"),
result.getString("name"),result.getString("category")));
return items;
}
/*Getting a specific item by item id from table*/
public Item getItem(int itemId){
String query = "SELECT * FROM ITEM WHERE ID=?";
Item item = template.queryForObject(query,new Object[]{itemId},new BeanPropertyRowMapper<>(Item.class));
return item;
}
/*Adding an item into database table*/
public int addItem(int id,String name,String category){
String query = "INSERT INTO ITEM VALUES(?,?,?)";
return template.update(query,id,name,category);
}
/*delete an item from database*/
public int deleteItem(int id){
String query = "DELETE FROM ITEM WHERE ID =?";
return template.update(query,id);
}
}
Create RestController to provide an endpoint to access from outside.
ItemController.java
package com.onlinetutorialspoint.controller;
import com.onlinetutorialspoint.model.Item;
import com.onlinetutorialspoint.repo.ItemRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
public class ItemController {
@Autowired
ItemRepository itemRepo;
@RequestMapping("/getAllItems")
@ResponseBody
public List<Item> getAllItems(){
return itemRepo.getAllItems();
}
@RequestMapping("/getItem")
@ResponseBody
public Item getItem(@RequestParam("itemId") int itemId){
return itemRepo.getItem(itemId);
}
@RequestMapping("/addItem")
@ResponseBody
public String addItem(@RequestParam("id") int id,@RequestParam("name") String name,
@RequestParam("category") String category){
if(itemRepo.addItem(id,name,category) >= 1){
return "Item Added Successfully";
}else{
return "Something went wrong !";
}
}
@RequestMapping("/deteteItem")
@ResponseBody
public String deteteItem(@RequestParam("itemId") int itemId){
if(itemRepo.deleteItem(itemId) >= 1){
return "Item Deleted Successfully";
}else{
return "Something went wrong !";
}
}
}
Application.java
Application.java
package com.onlinetutorialspoint;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
Run it !
mvn clean install
mvn spring-boot:run
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v1.5.10.RELEASE)
2018-02-21 07:08:32.553 INFO 5416 --- [ main] com.onlinetutorialspoint.Application : Starting Application on DESKTOP-RN4SMHT with PID 5416 (E:\work\SpringBoot-JDBC-MyS
QL\target\classes started by Lenovo in E:\work\SpringBoot-JDBC-MySQL)
2018-02-21 07:08:32.559 INFO 5416 --- [ main] com.onlinetutorialspoint.Application : No active profile set, falling back to default profiles: default
2018-02-21 07:08:32.644 INFO 5416 --- [ main] ationConfigEmbeddedWebApplicationContext : Refreshing org.springframework.boot.context.embedded.AnnotationConfigEmbeddedWebAp
plicationContext@1ac73b13: startup date [Wed Feb 21 07:08:32 IST 2018]; root of context hierarchy
2018-02-21 07:08:35.791 INFO 5416 --- [ main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat initialized with port(s): 8080 (http)
2018-02-21 07:08:35.818 INFO 5416 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2018-02-21 07:08:35.820 INFO 5416 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet Engine: Apache Tomcat/8.5.27
2018-02-21 07:08:36.100 INFO 5416 --- [ost-startStop-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2018-02-21 07:08:36.101 INFO 5416 --- [ost-startStop-1] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 3463 ms
2018-02-21 07:08:36.440 INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.ServletRegistrationBean : Mapping servlet: 'dispatcherServlet' to [/]
2018-02-21 07:08:36.450 INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'characterEncodingFilter' to: [/*]
2018-02-21 07:08:36.451 INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'hiddenHttpMethodFilter' to: [/*]
2018-02-21 07:08:36.452 INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'httpPutFormContentFilter' to: [/*]
2018-02-21 07:08:36.453 INFO 5416 --- [ost-startStop-1] o.s.b.w.servlet.FilterRegistrationBean : Mapping filter: 'requestContextFilter' to: [/*]
2018-02-21 07:08:37.674 INFO 5416 --- [ main] s.w.s.m.m.a.RequestMappingHandlerAdapter : Looking for @ControllerAdvice: org.springframework.boot.context.embedded.Annotatio
nConfigEmbeddedWebApplicationContext@1ac73b13: startup date [Wed Feb 21 07:08:32 IST 2018]; root of context hierarchy
2018-02-21 07:08:37.814 INFO 5416 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/getItem]}" onto public com.onlinetutorialspoint.model.Item com.onlinetu
torialspoint.controller.ItemController.getItem(int)
2018-02-21 07:08:37.816 INFO 5416 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/getAllItems]}" onto public java.util.List<com.onlinetutorialspoint.mode
l.Item> com.onlinetutorialspoint.controller.ItemController.getAllItems()
2018-02-21 07:08:37.817 INFO 5416 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/addItem]}" onto public java.lang.String com.onlinetutorialspoint.contro
ller.ItemController.addItem(int,java.lang.String,java.lang.String)
2018-02-21 07:08:37.818 INFO 5416 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/deteteItem]}" onto public java.lang.String com.onlinetutorialspoint.con
troller.ItemController.deteteItem(int)
2018-02-21 07:08:37.824 INFO 5416 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity<java.util.
Map<java.lang.String, java.lang.Object>> org.springframework.boot.autoconfigure.web.BasicErrorController.error(javax.servlet.http.HttpServletRequest)
2018-02-21 07:08:37.826 INFO 5416 --- [ main] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servl
et.ModelAndView org.springframework.boot.autoconfigure.web.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
2018-02-21 07:08:37.898 INFO 5416 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.
servlet.resource.ResourceHttpRequestHandler]
2018-02-21 07:08:37.899 INFO 5416 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.
resource.ResourceHttpRequestHandler]
2018-02-21 07:08:37.988 INFO 5416 --- [ main] o.s.w.s.handler.SimpleUrlHandlerMapping : Mapped URL path [/**/favicon.ico] onto handler of type [class org.springframework.
web.servlet.resource.ResourceHttpRequestHandler]
2018-02-21 07:08:38.393 INFO 5416 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Registering beans for JMX exposure on startup
2018-02-21 07:08:38.520 INFO 5416 --- [ main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2018-02-21 07:08:38.528 INFO 5416 --- [ main] com.onlinetutorialspoint.Application : Started Application in 6.638 seconds (JVM running for 16.176)
Access the Application :
Getting All Items :
http://localhost:8080/getAllItems
Add an Item :
http://localhost:8080/addItem?id=4&name=Refrigerator&category=Refrigerator
Getting All Items After adding :
http://localhost:8080/getAllItems
Delete an Item from the list :
References :
Happy Learning 🙂
