In this example, we’ll take a look at the Spring Jdbc jdbcTemplate and how can we make the query to database and do the read, write and conditional search operations. Spring Jdbc removes a lot of boiler plate code and let you focus on writing only the business logic, which make it to make more powerful. Spring Jdbc is good when you want to batch operations etc.
pom.xml — It has all dependencies needed for the application.
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.6.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>spring-jdbc</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-jdbc</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>11</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>Person.java — Its a model class
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Data
public class Person {
private Long id;
private String firstName;
private String lastName;
private String phone;
private String email;
private String address;
}PersonDao.java — Defines Contracts for the number of methods.
import com.example.springjdbc.model.Person;
import java.util.List;
public interface PersonDao {
public void save(Person person);
public Person load(long id);
public void delete(long id);
public void update(Person person);
public void updateAddress(long id, String newAddress);
public List<Person> loadAll();
public List<Person> findPersonsByLastName(String name);
public Long getPersonCount();
}PersonDaoImpl.java — Defines the implementation class for the contracts defined.
import com.example.springjdbc.model.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
@Service
public class PersonDaoImpl implements PersonDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public void save(Person person) {
String sql = "INSERT INTO test.person (first_name, last_name, phone, email, address)" +
"VALUES(?,?,?,?)";
jdbcTemplate.update(sql, person.getFirstName(),
person.getLastName(),
person.getPhone(),
person.getEmail(),
person.getAddress());
}
@Override
public Person load(long id) {
String sql = "select * from Person where id =?";
List<Person> persons = jdbcTemplate.query(sql, (resultSet, i) -> toPerson(resultSet), new Object[]{id});
if (persons.size() == 1) {
return persons.get(0);
}
return null;
}
@Override
public void delete(long id) {
String sql = "delete from person where id = ?";
jdbcTemplate.update(sql, id);
}
@Override
public void update(Person person) {
throw new UnsupportedOperationException();
}
@Override
public void updateAddress(long id, String newAddress) {
String sql = "update person set address = ? where id = ?";
jdbcTemplate.update(sql, newAddress, id);
}
@Override
public List<Person> loadAll() {
String sql = "select * from person";
return jdbcTemplate.query(sql, (resultSet, i) -> toPerson(resultSet));
}
@Override
public List<Person> findPersonsByLastName(String name) {
String sql = "select * from person where last_name = ?";
return jdbcTemplate.query(sql, new RowMapper<Person>() {
@Override
public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
return toPerson(rs);
}
}, new Object[]{name});
}
@Override
public Long getPersonCount() {
String sql = "select count(*) from person";
return jdbcTemplate.queryForObject(sql, Long.class);
}
private Person toPerson(ResultSet resultSet) throws SQLException {
Person person = new Person();
person.setId(resultSet.getLong("id"));
person.setFirstName(resultSet.getString("first_name"));
person.setLastName(resultSet.getString("last_name"));
person.setPhone(resultSet.getString("phone"));
person.setEmail(resultSet.getString("email"));
person.setAddress(resultSet.getString("address"));
return person;
}
}MainApp.java
@SpringBootApplication
public class SpringJdbcApplication implements CommandLineRunner {
public static void main(String[] args) {
SpringApplication.run(SpringJdbcApplication.class, args);
}
@Autowired
private PersonDao personDao;
@Override
public void run(String... args) throws Exception {
System.out.println("Find By last_name");
List<Person> persons = personDao.findPersonsByLastName("Ashtikar");
System.out.println(persons);
System.out.println("-------------");
System.out.println("\n Load All Persons");
List<Person> personList = personDao.loadAll();
System.out.println(personList);
System.out.println("-------------");
Long personCount = personDao.getPersonCount();
System.out.println("Persons count = "+ personCount);
System.out.println("-------------");
Person person = personDao.load(1l);
System.out.println("Load By Id : "+ person);
System.out.println("----------------");
personDao.updateAddress(1l, "Orange Circle");
System.out.println("-------------Update Done Successfully...");
}
}application.yml
spring:
datasource:
username: root
password: Password
url: jdbc:mysql://localhost:3306/test
driver-class-name: com.mysql.cj.jdbc.Driver
jpa:
show-sql: true
hibernate:
ddl-auto: update
properties.hibernate.dialect: org.hibernate.dialect.MySQL5DialectCreateSql.sql
CREATE TABLE `person` (
`id` bigint NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`phone` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;insert.sql
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(1, 'Thomas', 'Maggard', '910-984-4490', 'ThomasJMaggard@rhyta.com', 'Orange Circle');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(2, 'Allan', 'Glass', '701-886-8262', 'AllanCGlass@teleworm.us', '177 Findley Avenue Neche, ND 58265');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(3, 'Katherine', 'Benefield', '323-589-9055', 'KatherineRBenefield@armyspy.com
', '2905 Evergreen Lane Huntington Park, CA 90255
');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(4, 'Rachal', 'Wood', '732-993-1707', 'RachalRWood@jourrapide.com', '4823 Mahlon Street New Brunswick, NJ 08901');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(5, 'Ben', 'Ramos', '781-216-9080', 'BenMRamos@jourrapide.com', '410 Wescam Court Boston, MA 02110');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(6, 'Neha', 'Parate', '111-345-0980', 'neha.parate@gmail.com', '1149 George Avenue Evergreen, AL 36401');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(7, 'Parag', 'Rane', '309-690-7156', 'parag.rane@gmail.com', '2241 Garfield Road Peoria, IL 61614');
INSERT INTO test.person
(id, first_name, last_name, phone, email, address)
VALUES(8, 'Aravind', 'Ashtikar', '404-836-0110', 'aravind.ashtikar@springone.com', '50 Stroop Hill Road Atlanta, GA 30303');
