Spring JDBC
Spring can perform JDBC operations by having connectivity with any one of jars of RDBMS like MySQL, Oracle, or SQL Server, etc., For example, if we are connecting with MySQL, then we need to connect “mysql-connector-java”. Let us see how a pom.xml file of a maven project looks like.
XML
<?xml version="1.0" encoding="UTF-8"?> xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 <modelVersion>4.0.0</modelVersion> <groupId>com.gfg.common</groupId> <artifactId>SpringJDBCExample</artifactId> <packaging>jar</packaging> <version>1.0-SNAPSHOT</version> <name>SpringJDBCExample</name> <dependencies> <!-- Spring framework --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring</artifactId> <version>2.5.6</version> <!-- You can change corresponding version here --> </dependency> <!-- MySQL database driver --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.9</version> <!-- You can change corresponding version here --> </dependency> </dependencies> </project> |
Model Class:
We need a MODEL class to start
Java
public class <ModelClass>{ int column1; String column2; int column3; // .... // corresponding getter and setter // methods for each and every column } |
DAO Pattern:
Java
public interface <SampleDAO>{ // To insert the records public void insert(ModelClass object); // To get the records public ModelClass findByXXX(int xxx);} |
Implementation of DAO Interface:
Java
import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import javax.sql.DataSource;// Provide necessary interface imports// Provide necessary ModelClass importspublic class JdbcSampleDAO implements SampleDAO { private DataSource dataSource; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } public void insert(ModelClass object) { // Write the necessary statements like providing SQL // insert statement // and execute them for making record insertion } public ModelClass findByXXX(int XXX) { // Write the necessary statements like providing SQL // select statement // and execute them for making record display }} |
Spring Data JDBC
It belongs to the Spring Data family. Basically, it provides abstractions for the JDBC-based Data Access Layer. It provides easy to use Object Relational Mapping (ORM) framework to work with databases. It can support entity objects and repositories. Because of this, a lot of complexities are reduced. The data access layer is simple. Hence JPA features like Lazy Loading, caching of entities, etc. are omitted. Because of this JDBC operations on the entities are taken care of well. Let us see what are the dependencies needed for Spring Data JDBC in the Spring Boot maven project.
XML
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jdbc</artifactId></dependency> |
Let us see what are the dependencies needed for Spring Data JDBC in the Spring maven project.
XML
<dependency> <groupId>org.springframework.data</groupId> <artifactId>spring-data-jdbc</artifactId> <!-- Specify the required version here --> <version>{version}</version></dependency> |
POJO Class:
For making the entity, we need to use a POJO(Plain old java object). Spring Data JDBC can map our POJO to a database table. The following key points need to be observed
- Created POJO needs to match with the database table. If not, it uses @Table annotation and this helps to refer to the actual table name.
- @Id annotation is required to identify the primary key
- It is a good practice that all the persistence fields in the POJO and database table columns need to match. Otherwise, @Column annotation is helpful to provide column names.
Sample POJO class
Java
import lombok.Data;import org.springframework.data.annotation.Id;import org.springframework.data.relational.core.mapping.Table;// Required for generating default getter and setter@Data// If POJO class name and db table name differs@Table("<mention the name of db table>")public class <POJOClass> { // For representing Primary key @Id private Long id; private String <columnName1>; private String <columnName2>; private Integer <columnName3>;} |
For a POJO class, it is not mandatory to have
- A parameterized constructor.
- Access methods.
Repository Interface:
Repository Interface along with Query methods and @Query annotations are supported. Created repositories can be a sub-interface of CrudRepository.
Java
import org.springframework.data.repository.CrudRepository;import org.springframework.stereotype.Repository;import java.util.List;@Repositorypublic interface <SampleRepository> extends CrudRepository<<POJOClass>, Long> { // XXX can be firstName, lastName, age, gender etc., List<POJOClass> findByXXX(String XXX);} |
The named query can be constructed as
Java
@Query("select * from <tableName> where condition = :<param>")List<POJOClass> findByXXX(@Param("param") String XXX); |
Difference Between Spring JDBC and Spring Data JDBC
|
Spring JDBC |
Spring Data JDBC |
|---|---|
| Spring JDBC is a Model class. | Spring Data JDBC is a POJO class. |
| Getter and setters are mandatory. | Getter and setters are not mandatory. |
| The parameterized constructor will be helpful. | The parameterized constructor may not be helpful. |
| There is no specific annotation is required. The only thing is we should have equal attributes match with the DB table and each attribute should have a getter and setter. | @Table, @ID, and @Column annotations are helpful to mention for direct connection with the database. |
| Data Access Layer is specified with the interface and its implementation. | Data Access Layer is simple and it omits lazy loading, cache implementation, etc., which is there in JPA(Java Persistence API). |
