Getting Started with Spring Boot, Part 13 Database Queries with the JDBC Template
Simple database queries via JPA, repositories and derived and custom queries are often the preferred method in Spring. However, there are exceptions, for example the maintenance of existing projects in which extensive SQL code is already available.
Companies on the topic
With the JdbcTemplate, Spring provides a class that executes SQL statements.
(Image: Spring.io)
In the cases mentioned at the beginning, it is often easier to use the “Java Database Connectivity”interface, better known under the abbreviation JDBC. The API dates back to the early days of Java and is considered rather cumbersome. For a simple query, a whole series of steps must be programmed:
- Registering a Driver
- Opening a Connection using Driver Manager
- Creating a Statement
- Iterate over the result set
- Closing the Connection
Fortunately, Spring makes this hassle easier through the JdbcTemplate. This is a class that executes SQL statements and can transform the results into objects with the support of various helper classes, for example.
The class also takes care of opening and closing connections and generating statements. It also transforms the SQLException into a runtime exception that works better with Spring.
Making preparations
The basis here is a project created with the Spring Initializr, to which the starter dependencies H2 Database, JDBC API, Spring Web and-for convenience Lombok have been added:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency><dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency><dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency><dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
The data object used here is again the simple class Person, which has already been used in other posts in the series. The getters and setters are created with the Lombok annotation @Data. Also from Lombok comes the annotation @Builder, which allows people to be created efficiently with the builder pattern:
@Data
@Builder
public class Person {
private Long id;
private String firstname;
private String lastname;
}
The associated database schema for the H2 database is stored in the schema file.sql stored in src / main / resources. Spring Boot reads the file and creates the table automatically :
CREATE TABLE person (
id INTEGER NOT NULL AUTO_INCREMENT,
firstname VARCHAR(128) NOT NULL,
lastname VARCHAR(128) NOT NULL,
PRIMARY KEY (id)
);
Also under src / main / resources is the configuration file application.properties. In it, the web console of the H2-DB is activated and switched to in-memory operation:
spring.h2.console.enabled=true
spring.datasource.url=jdbc:h2:mem:testdb
The Person table has been created, but does not yet contain any data.
(Picture: Koller)
After that, the console of the database is at the URL http://localhost:8080/h2-console / available. The correct JDBC URL is jdbc:h2: mem: testdb, no password required. As the screenshot shows, the Person table has been created. However, it does not yet contain any data. Test data can either be entered manually using the H2 console or in the data file.store sql in src / main / resources:
INSERT INTO person VALUES (1, 'Max', 'Mustermann');
INSERT INTO person VALUES (2, 'Ute', 'Musterfrau');
After restarting the application, they will be visible in the console.
From dataset to object: RowMapper
JdbcTemplate offers several ways to process the requested data. A Person object is to be created here. This is done using a RowMapper. The interface contains the method mapRow (ResultSet rs, int rowNum), in which the repopulation of the data from the ResultSet into a Person object is implemented:
public class PersonRowMapper implements RowMapper<Person> { @Override
public Person mapRow(ResultSet rs, int rowNum) throws SQLException {
Person person = Person.builder()
.id(rs.getLong("ID"))
.firstname(rs.getString("firstname"))
.lastname(rs.getString("lastname"))
.build();
return person;
}
}
JdbcTemplate
So everything is together to execute the database query with JdbcTemplate. Here, for reasons of space, this is done directly in a Controller class into which a JdbcTemplate instance is injected:
@Controller
public class JDBCTemplateController { @Autowired
JdbcTemplate jdbcTemplate; @GetMapping("/{id}")
@ResponseBody
public String home(@PathVariable Long id) { Person person = jdbcTemplate.queryForObject("SELECT * FROM Person WHERE Id = ?", new PersonRowMapper(), new Object[] {id});
return person.toString();
}
}
The requested person with the id=1 is displayed in the browser.
(Picture: Koller)
The mapped URL / is given the person id as part of the URL. The call JdbcTemplate.queryForObject () then brings everything together: the Query, the RowMapper, and the passed Id, which is used as the query parameter instead of the question mark. The return of the method, i.e. the Person object obtained, is simply written to the body of the HTTP response here.
A call from http://localhost:8080/1 in the browser therefore displays the data of Max Mustermann. In practice, one would pass the Person object to a Thymeleaf view, for example, and visualize it there with HTML and CSS. For this post, however, this leads too far.
The small example illustrates the performance of JdbcTemplate. The boilerplate code for opening the database connection, creating the statement and handling the IOException is completely omitted. The database access is reduced to a single statement and now shows well what it is actually about.
But JdbcTemplate can do a lot more. For processing with the RowMapper there are alternatives and of course you can also add, update or delete records. More on this in the next part of the series.
(ID:47395355)
