How to use Spring Data JDBC in a web application
Spring data JDBC simplifies the implementation of JDBC based repositories. Spring data JDBC adds enhanced support for traditional JDBC based data access layers. To keep it simple, Spring data JDBC does not offer features like caching, lazy loading, write behind, and many other features of JPA.
In this article, I will show you how to perform CRUD operations with spring data JDBC. Feel free to download the fully working project with the link provided at the end of the project.
Database schema
This example uses the MySQL database. You can use any database of your choice, but make sure to use the relevant database driver. Create the database and customers' table using the below query.
create database springweb;
use springweb;
CREATE TABLE customers (
customer_id BIGINT(20) NOT NULL AUTO_INCREMENT,
customer_name VARCHAR(100) NOT NULL,
address VARCHAR(100) DEFAULT NULL,
no_of_orders int DEFAULT '0'
PRIMARY KEY (customer_id)
)
insert into customers(customer_id,customer_name,address,no_of_orders) values
(1,'Homedepot','Some street, New York',1),
(2,'Lows','2nd street, Toronto',2),
(3,'Bunnings','1st street, Melbourne',3);Spring data JDBC maven dependency
I use Spring 5.3.5 and spring data JDBC 2.2.4. Ensure your spring data JDBC version is compatible with the Spring core version.
<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.web</groupId>
<artifactId>spring</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>war</packaging>
<name>spring</name>
<properties>
<jdk.version>15</jdk.version>
<spring.version>5.3.5</spring.version>
</properties>
<dependencies>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>javax.annotation</groupId>
<artifactId>javax.annotation-api</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-text</artifactId>
<version>1.9</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jdbc</artifactId>
<version>2.2.4</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.5</version>
</dependency>
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>${jdk.version}</source>
<target>${jdk.version}</target>
</configuration>
</plugin>
</plugins>
</build>
</project>
How to create a spring data JDBC data source?
Like any other persistence framework, spring data JDBC requires a data source to establish a database connection. The below code illustrates how to implement the data source as a spring bean.
@Configuration
@EnableJdbcRepositories(basePackages = "com.springdata")
public class SpringDataSource extends AbstractJdbcConfiguration{
@Bean("dataSource")
public DataSource mysqlDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/springweb");
dataSource.setUsername("username");
dataSource.setPassword("password");
return dataSource;
}
@Bean
NamedParameterJdbcOperations namedParameterJdbcOperations(DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
@Bean
public PlatformTransactionManager transactionManager(DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
}Spring data JDBC domain object
The domain object represents database tables. Make sure to name the class the same as the table name. All the class properties should represent the camel case representation of table fields. This way, you don't have to map the object and its properties manually. The spring framework will map them automatically for you.
public class Customers {
@Id
Long customerId;
String customerName;
String address;
Integer noOfOrders;
public Long getCustomerId() {
return customerId;
}
public void setCustomerId(Long customerId) {
this.customerId = customerId;
}
public String getCustomerName() {
return customerName;
}
public void setCustomerName(String customerName) {
this.customerName = customerName;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getNoOfOrders() {
return noOfOrders;
}
public void setNoOfOrders(Integer noOfOrders) {
this.noOfOrders = noOfOrders;
}
}How to create a spring data JDBC repository?
There are several ways to create a spring data JDBC repository. Depending on your requirement, you can extend the Repository, CrudRepository, or PagingAndSortingRepository interface. I will use CrudRepository in this example to show how basic CRUD operations are performed. By implementing the CrudRepository interface, our repository inherits basic CRUD operations, so no explicit declaration is necessary.
public interface CustomersTableQuaries extends CrudRepository
{
} Inherited methods from CrudRepository
Apart from what we have used above, there are several methods ready to use in your repository. Listed below are the methods your spring data repository inherits from CrudRepository. Therefore, no explicit implementation is not required for those methods.
Returns the number of entities available.
Deletes all entities managed by the repository.
Deletes the given entities.
Deletes all instances of the type T with the given IDs.
Returns whether an entity with the given id exists.
Returns all instances of the type T with the given IDs. If some or all ids are not found, no entities are returned for these IDs. Note that the order of elements in the result is not guaranteed.
Saves all given entities.
Customized methods in spring data JDBC repository
The inherited methods from CrudRepository are standard and basic. If you require complex and tailored queries, you can implement them in your repository interface.
public interface CustomersTableQuaries extends CrudRepository
{
@Query("SELECT COUNT(*) FROM customers where no_of_orders > :noOfOrders")
Integer findByFacId(@Param("noOfOrders") Integer noOfOrders);
} How to query spring data JDBC repository?
Our CrudRepository can be used as a standard spring bean. The below example shows how I use my CustomersTableQuaries repository in a Spring MVC controller.
@Controller
public class CustomerController{
@Autowired
private CustomersTableQuaries customersTableQuaries;
@RequestMapping(value = "/create", method = RequestMethod.POST)
public String create(HttpServletRequest request) throws Exception {
Customers customers = new Customers();
customers.setCustomerName(request.getParameter("customerName"));
customers.setAddress(request.getParameter("address"));
customers.setNoOfOrders(Integer.valueOf(request.getParameter("noOfOrders")));
customersTableQuaries.save(customers);
request.setAttribute("customers", customersTableQuaries.findAll());
return "customers";
}
@RequestMapping(value = "/read", method = RequestMethod.GET)
public String read(HttpServletRequest request) throws Exception {
request.setAttribute("customers", customersTableQuaries.findAll());
return "customers";
}
@RequestMapping(value = "/update/{customerId}/{orders}", method = RequestMethod.GET)
public String update(HttpServletRequest request, @PathVariable Integer customerId, @PathVariable Integer orders) throws Exception {
Customers customer = customersTableQuaries.findById(customerId).get();
customer.setNoOfOrders(orders);
customersTableQuaries.save(customer);
request.setAttribute("customers", customersTableQuaries.findAll());
return "customers";
}
@RequestMapping(value = "/delete/{customerId}", method = RequestMethod.GET)
public String delete(HttpServletRequest request, @PathVariable Integer customerId) throws Exception {
customersTableQuaries.deleteById(customerId);
request.setAttribute("customers", customersTableQuaries.findAll());
return "customers";
}
}Related Links
