DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • How To Build Web Service Using Spring Boot 2.x
  • JobRunr and Spring Data
  • Providing Enum Consistency Between Application and Data
  • Enterprise RIA With Spring 3, Flex 4 and GraniteDS

Trending

  • Streamlining Event Data in Event-Driven Ansible
  • AI Meets Vector Databases: Redefining Data Retrieval in the Age of Intelligence
  • Docker Model Runner: Streamlining AI Deployment for Developers
  • Recurrent Workflows With Cloud Native Dapr Jobs
  1. DZone
  2. Coding
  3. Frameworks
  4. Introduction to Spring Boot and JDBCTemplate: JDBC Template

Introduction to Spring Boot and JDBCTemplate: JDBC Template

This tutorial will cover a way to simplify the code with JDBC using the Spring JDBC Template.

By 
Otavio Santana user avatar
Otavio Santana
DZone Core CORE ·
Ram N user avatar
Ram N
·
Updated Jul. 15, 22 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
57.8K Views

Join the DZone community and get the full member experience.

Join For Free

As with any programming language, Java has several tools to make easy integration between the language and the database. There are several tools such as Hibernate, Eclipse Link, JPA specification, and so on. However, the ORM brings several issues, and sometimes it does not sense to use it and then use it a Java Communication layer or JDBC. This tutorial will cover a way to simplify the code with JDBC using the Spring JDBC Template.

Mapping frameworks such as ORM reduces a lot of boilerplate, reduces the duplicated code, avoids bugs, and doesn’t reinvent the wheel. However, an Object-relational impedance mismatch that brings a set of conceptual and technical difficulties is often encountered when an RDBMS is being served by an application program written in an object-oriented programming language.

A solution might use JDBC, but it increases the complexity to handle data and Java. How can an application reduce this verbosity with JDBC? Spring JDBCTemplate is a powerful mechanism to connect to the database and execute SQL queries. It internally uses JDBC API but eliminates a lot of problems with JDBC API.

Related content by Ram N:

Starting With Spring Initializr

This sample application will use JDBCTemplate with Spring to use two databases: PostgreSQL to run the application and H2 in the test scope. For all Spring applications, you should start with the Spring Initializr. The Initializr offers a fast way to pull in all the dependencies you need for an application and does a lot of the setup for you. This example requires the JDBC API, Spring MVC, PostgreSQL driver, and H2 Database dependencies.

XML
 




x
23


 
1
<dependency> 
2
    <groupId>org.springframework.boot</groupId> 
3
    <artifactId>spring-boot-starter-web</artifactId> 
4
</dependency> 
5
<dependency> 
6
    <groupId>org.springframework.boot</groupId> 
7
    <artifactId>spring-boot-starter-jdbc</artifactId> 
8
</dependency> 
9
<dependency> 
10
    <groupId>org.postgresql</groupId> 
11
    <artifactId>postgresql</artifactId> 
12
    <scope>runtime</scope> 
13
</dependency> 
14
<dependency> 
15
    <groupId>com.h2database</groupId> 
16
    <artifactId>h2</artifactId> 
17
    <scope>test</scope> 
18
</dependency> 
19
<dependency> 
20
    <groupId>org.springframework.boot</groupId> 
21
    <artifactId>spring-boot-starter-test</artifactId> 
22
    <scope>test</scope> 
23
</dependency> 



The next step is the Car entity, with five fields: id, name, city, model, and color. In this project, a POJO as an anemic model is more than enough to handle it. A rich model guarantees the object’s rules and avoids any encapsulation issue, thus, a bulletproof API. However, it only fits in complex projects. Highlighting: 'it depends' is always a good answer when we talk about software architecture.


Java
 




xxxxxxxxxx
1
16


 
1
public class Car {
2

          
3
    private Long id;
4

          
5
    private String name;
6

          
7
    private String city;
8

          
9
    private String model;
10

          
11
    private String color;
12
    //...
13
    public static CarBuilder builder() {
14
        return new CarBuilder();
15
    }
16
}



To have tight integration between Java and ResultSet of JDBC, Spring JDBC has a RowMapper interface. A developer can either create a custom class or use BeanPropertyRowMapper, which reduces the boilerplate; however, the entity should have a public getter and setter; it might get an encapsulation issue besides it provides convenience rather than high performance. For best performance, consider using a custom RowMapper implementation.

Java
 




xxxxxxxxxx
1
15


 
1
public class CarRowMapper implements RowMapper<Car> {
2

          
3
    @Override
4
    public Car mapRow(ResultSet resultSet, int rowNum) throws SQLException {
5
        Long id = resultSet.getLong("ID");
6
        String name = resultSet.getString("name");
7
        String city = resultSet.getString("city");
8
        String model = resultSet.getString("model");
9
        String color = resultSet.getString("color");
10
        return Car.builder().id(id).name(name)
11
                .city(city)
12
                .model(model)
13
                .color(color).build();
14
    }
15
}



The entity is ready; let’s talk about the Data Access Object, DAO; every time there is a vast or complex query to handle, there is a discussion about where the SQL query should go. Briefly, when the script is hardcoded, it is clearer what the script is doing, but when the command is tremendous, it becomes challenging to read and understand. Thus, we can move it to read it from properties.


Properties files
 




x


 
1
car.query.find.by.id=SELECT * FROM CAR WHERE ID = :id
2
car.query.delete.by.id=DELETE FROM CAR WHERE ID =:id
3
car.query.update=update CAR set name = :name, city = :city, model= :model, color =:color  where id = :id
4
car.query.find.all=select * from CAR ORDER BY id LIMIT :limit OFFSET :offset



Once the reader is familiar with the query in the code, we’ll explore the query option in a properties file. We’ll have a class to hold this duty, and it will have tight integration with Spring Configuration.

Java
 




xxxxxxxxxx
1
13


 
1
@Component
2
public class CarQueries {
3

          
4
    @Value("${car.query.find.by.id}")
5
    private String findById;
6
    @Value("${car.query.delete.by.id}")
7
    private String deleteById;
8
    @Value("${car.query.update}")
9
    private String update;
10
    @Value("${car.query.find.all}")
11
    private String findAll;
12
    //...
13
}



The CarDAO will do the CRUD operation once the findAll has pagination support. It uses NamedParameterJdbcTemplate to class with a basic set of JDBC operations, allowing named parameters rather than traditional ‘?’ Placeholders. To avoid connection leak, Spring has the Transactional annotation to control the transaction in each method where we defined in our code.

Java
 




xxxxxxxxxx
1
54


 
1
@Repository
2
public class CarDAO {
3

          
4
    private final NamedParameterJdbcTemplate template;
5
    private final CarQueries queries;
6
    private final RowMapper<Car> rowMapper;
7
    private final SimpleJdbcInsert insert;
8

          
9
    @Autowired
10
    public CarDAO(NamedParameterJdbcTemplate template, CarQueries queries) {
11
        this.template = template;
12
        //this.rowMapper = new BeanPropertyRowMapper<>(Car.class);
13
        this.rowMapper = new CarRowMapper();
14
        this.queries = queries;
15
        this.insert = new SimpleJdbcInsert(template.getJdbcTemplate());
16
        this.insert.setTableName("car");
17
        this.insert.usingGeneratedKeyColumns("id");
18
    }
19

          
20
    @Transactional
21
    public Car insert(Car car) {
22
        //Number id = insert.executeAndReturnKey(new BeanPropertySqlParameterSource(car));
23
        Number id = insert.executeAndReturnKey(car.toMap());
24
        return findBy(id.longValue()).orElseThrow(() -> new IllegalStateException(""));
25
    }
26

          
27
    public Optional<Car> findBy(Long id) {
28
        String sql = queries.getFindById();
29
        Map<String, Object> parameters = Collections.singletonMap("id", id);
30
        return template.queryForStream(sql, parameters, rowMapper).findFirst();
31
    }
32

          
33
    @Transactional
34
    public boolean delete(Long id) {
35
        String sql = queries.getDeleteById();
36
        Map<String, Object> paramMap = Collections.singletonMap("id", id);
37
        return template.update(sql, paramMap) == 1;
38
    }
39

          
40
    @Transactional
41
    public boolean update(Car car) {
42
        String sql = queries.getUpdate();
43
        Map<String, Object> paramMap = car.toMap();
44
        return template.update(sql, paramMap) == 1;
45
    }
46

          
47
    public Stream<Car> findAll(Page page) {
48
        String sql = queries.getFindAll();
49
        Map<String, Object> paramMap = new HashMap<>();
50
        paramMap.put("limit", page.getLimit());
51
        paramMap.put("offset", page.getOffset());
52
        return template.queryForStream(sql, paramMap, rowMapper);
53
    }
54
}



The code is ready; let’s test it. Yeap, a TDD technique, has a philosophy to start with the test and then create the code. But it is not the article’s goal. In the test scope, we’ll generate a DBMS in memory with H2. Spring has several features to allow us to test smoothly. Thanks to Spring, we can use H2 in the test without impact the driver that will operate on production.

Java
 




xxxxxxxxxx
1
86


 
1
@ExtendWith(SpringExtension.class)
2
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.MOCK)
3
class CarDAOTest {
4

          
5
    @Autowired
6
    private CarDAO carDAO;
7

          
8
    @Autowired
9
    private JdbcTemplate template;
10

          
11
    @Test
12
    public void shouldFindById() {
13
        Assertions.assertNotNull(carDAO);
14
        Optional<Car> car = carDAO.findBy(1L);
15
        Assertions.assertNotNull(car);
16
    }
17

          
18
    @Test
19
    public void shouldInsertCar() {
20
        Car car = Car.builder()
21
                .city("Salvador")
22
                .color("Red")
23
                .name("Fiat")
24
                .model("Model")
25
                .build();
26
        Car insert = carDAO.insert(car);
27
        Assertions.assertNotNull(insert);
28
        Assertions.assertNotNull(insert.getId());
29
    }
30

          
31
    @Test
32
    public void shouldDelete() {
33
        Car car = Car.builder()
34
                .city("Salvador")
35
                .color("Red")
36
                .name("Fiat")
37
                .model("Model")
38
                .build();
39
        Car insert = carDAO.insert(car);
40
        carDAO.delete(insert.getId());
41
        Optional<Car> empty = carDAO.findBy(insert.getId());
42
        Assertions.assertTrue(empty.isEmpty());
43
    }
44

          
45
    @Test
46
    public void shouldUpdate() {
47
        Car car = Car.builder()
48
                .city("Salvador")
49
                .color("Red")
50
                .name("Fiat")
51
                .model("Model")
52
                .build();
53
        Car insert = carDAO.insert(car);
54

          
55
        insert.update(Car.builder()
56
                .city("Salvador")
57
                .color("Red")
58
                .name("Fiat")
59
                .model("Update")
60
                .build());
61
        carDAO.update(insert);
62
    }
63

          
64
    @Test
65
    public void shouldFindAll() {
66
        template.execute("DELETE FROM CAR");
67
        List<Car> cars = new ArrayList<>();
68
        for (int index = 0; index < 10; index++) {
69
            Car car = Car.builder()
70
                    .city("Salvador")
71
                    .color("Red")
72
                    .name("Fiat " + index)
73
                    .model("Model")
74
                    .build();
75
            cars.add(carDAO.insert(car));
76
        }
77
        Page page = Page.of(1, 2);
78
        List<Car> result = carDAO.findAll(page).collect(Collectors.toList());
79
        Assertions.assertEquals(2, result.size());
80
        MatcherAssert.assertThat(result, Matchers.contains(cars.get(0), cars.get(1)));
81
        Page nextPage = page.next();
82
        result = carDAO.findAll(nextPage).collect(Collectors.toList());
83
        Assertions.assertEquals(2, result.size());
84
        MatcherAssert.assertThat(result, Matchers.contains(cars.get(2), cars.get(3)));
85
    }
86
}



Conclusion

In this tutorial, we introduced Spring JDBC and its operation; we talked a little about the mapping trade-offs and the place to store the queries in addition to the testing and configuration resources. Spring brings several features that increase developer productivity. In the second part, we will talk a little about Spring MVC and its works with the database.

Code: https://github.com/xgeekshq/spring-boot-jdbc-template-sample

Spring Framework Database Spring Boot Template application Java (programming language)

Opinions expressed by DZone contributors are their own.

Related

  • How To Build Web Service Using Spring Boot 2.x
  • JobRunr and Spring Data
  • Providing Enum Consistency Between Application and Data
  • Enterprise RIA With Spring 3, Flex 4 and GraniteDS

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!