Let’s try to tie everything we’ve learned about databases so far together, into a Spring Boot application.
Note: spring itself provides plenty of guides. For relational databases, see https://spring.io/guides/gs/relational-data-access
Before you can run a single query from Java, three things need to be in place: the right dependencies on the classpath, a datasource configuration that tells Spring Boot where the database is, and a live PostgreSQL database to connect to.
Add the following dependencies to your pom.xml:
**<!-- Spring JDBC — includes JdbcTemplate -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- PostgreSQL JDBC driver -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>**
spring-boot-starter-jdbc brings in JdbcTemplate and Spring's transaction management. The PostgreSQL driver is the JDBC driver that knows how to speak PostgreSQL's wire protocol — without it, Java has no way to actually talk to the database.
Spring Boot needs to know where your database is, what it is called, and how to authenticate. You provide this in src/main/resources/application.properties:
spring.datasource.url=jdbc:postgresql://localhost:5432/postify
spring.datasource.username=postgres
spring.datasource.password=yourpassword
spring.datasource.driver-class-name=org.postgresql.Driver
<aside> ⚠️
For this exercise storing your (plaintext) password in the properties file is ok. However, it is often prefered to use environment variables instead:
spring.datasource.password=${YOUR_OWN_DEFINED_ENV_VAR}
</aside>
As soon as Spring Boot starts, it finds a datasource URL, username, and password, and automatically creates a DataSource bean and a connection pool. This connection pool maintains a set of open database connections and hands them out to your application code on demand. You never manage individual connections yourself. You do not open them, close them, or return them to the pool. Spring handles all of that.
The simplest way to confirm everything is wired up correctly is to add a health check. Spring Boot Actuator does this automatically if you add the dependency, but you can also verify manually by injecting JdbcTemplate and running a trivial query at startup:
@Component
public class DatabaseConnectionCheck {
private final JdbcTemplate jdbcTemplate;
public DatabaseConnectionCheck(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@EventListener(ApplicationReadyEvent.class)
public void checkConnection() {
String result = this.jdbcTemplate.queryForObject("SELECT 'connected'", String.class);
System.out.println("Database status: " + result);
}
}
When a Spring Boot application starts up, it reads your code and builds an application context: a container that constructs and manages the objects your application needs. Any class you annotate with @Component, @Service, @Repository, or @RestController gets instantiated by Spring and registered in this container as a bean. This is Inversion of Control (IoC): instead of your code deciding when and how to create objects with new, you hand that responsibility to the framework.
The practical benefit of IoC is Dependency Injection (DI): when Spring creates a bean and sees that its constructor requires another object — say, a JdbcTemplate — it looks that object up in the application context and passes it in automatically. Your DatabaseConnectionCheck never calls new JdbcTemplate(...) itself; it simply declares that it needs one, and Spring handles the rest. This means your classes are loosely coupled. A DatabaseConnectionCheck does not need to know how a JdbcTemplate is built or configured, only what it does. This makes the code easier to test, swap out, and maintain.
A good overview of what IoC is, and how Spring Boot uses it, can be found in their docs: https://docs.spring.io/spring-framework/reference/core/beans/introduction.html. Specifically, the Java annotation style configuration https://docs.spring.io/spring-framework/reference/core/beans/java.html is relevant.
Note that DI and IoC are general-purpose patterns for wiring together objects that depend on each other some way. Database connections are just one example, but the ideas can be reused much more, including outside of Spring Boot applications, and other programming languages!
The jdbcTemplate is Spring’s main tool for executing SQL. It wraps the raw JDBC API you studied last week and handles the parts that are tedious and error-prone to write by hand: opening connections, creating statements, iterating result sets, and closing everything afterwards. You write the SQL; JdbcTemplate handles the plumbing.
The conventional place for database queries in a Spring Boot application is a repository class: a @Repository-annotated component whose sole responsibility is talking to the database. Here is one for streams:
@Repository
public class StreamRepository {
private final JdbcTemplate jdbcTemplate;
public StreamRepository(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
Spring sees @Repository, creates the bean, injects jdbcTemplate, and makes StreamRepository available for injection elsewhere. All the query methods you write live in this class.
The jdbcTemplate exposes an API for interacting with the database. If you want a single row from your database, you can use the function queryForObject :
public int countStreamsForUser(int userId) {
return jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM streams WHERE user_id = ?",
Integer.class, // Cast the result to an integer
userId // Pass a variable value to the prepared statement
);
}
The ? is a parameter placeholder for the prepared statement. The value userId is passed as a separate argument, and this is how SQL injection is prevented.
The second argument Integer.class tells JdbcTemplate what Java type to map the single result column to. This works for String.class, Long.class, Boolean.class, and other simple types.
<aside> ⚠️
This method expects a single row being returned. If no rows are returned, it throws a EmptyResultDataAccessException . If multiple rows are returned, it throws a IncorrectResultSizeDataAccessException
</aside>
Use query when your query returns multiple rows. It requires a RowMapper to turn each row into a Java object. This RowMapper tells each row how to be transformed into a Java data type. Here’s an example for a list of track id’s:
public List<Integer> findTrackIdsStreamedByUser(int userId) {
return jdbcTemplate.query(
"SELECT track_id FROM streams WHERE user_id = ?",
(rs, rowNum) -> rs.getInt("track_id"), // This is a lambda expression
userId
);
}
<aside> 🤓
The second argument passed to query is a lambda expression. This is an easy way to define functions on the fly. See also https://docs.oracle.com/javase/tutorial/java/javaOO/lambdaexpressions.html
</aside>
The second argument is what constructs the return statement: for each row in the result, you can define the returned statement using the result set (rs) and the current row number (rowNum). Calling rs.getInt already knows the current row number. A list of Integers is returned as a result.
If no rows are found, query returns an empty list; it does not throw an exception.
You can also use more complicated row mappers, returning more complex data structures. First, define a model class for streams:
public class Stream {
private int streamId;
private int userId;
private int trackId;
private OffsetDateTime streamedAt;
// constructors, getters, setters
}
Then, use it in your query:
public List<Stream> findByUser(int userId) {
return jdbcTemplate.query(
"SELECT stream_id, user_id, track_id, streamed_at " +
"FROM streams WHERE user_id = ?",
(rs, rowNum) -> {
Stream stream = new Stream();
stream.setStreamId(rs.getInt("stream_id"));
stream.setUserId(rs.getInt("user_id"));
stream.setTrackId(rs.getInt("track_id"));
stream.setStreamedAt(rs.getObject("streamed_at", OffsetDateTime.class));
return stream;
},
userId
);
}
This now returns a List of Streams instead of simpler integers, and contains more complicated classes such as the OffsetDateTime class. Here it’s defined as a lambda expression, but you can also define them separately as RowMappers:
public class StreamRowMapper implements RowMapper<Stream> {
@Override
public Stream mapRow(ResultSet rs, int rowNum) throws SQLException {
Stream stream = new Stream();
stream.setStreamId(rs.getInt("stream_id"));
stream.setUserId(rs.getInt("user_id"));
stream.setTrackId(rs.getInt("track_id"));
stream.setStreamedAt(rs.getObject("streamed_at", OffsetDateTime.class));
return stream;
}
}
Which can be used like this:
private final RowMapper<Stream> streamMapper = new StreamRowMapper();
public List<Stream> findByUser(int userId) {
return jdbcTemplate.query(
"SELECT stream_id, user_id, track_id, streamed_at " +
"FROM streams WHERE user_id = ?",
streamMapper,
userId
);
}
public List<Stream> findByTrack(int trackId) {
return jdbcTemplate.query(
"SELECT stream_id, user_id, track_id, streamed_at " +
"FROM streams WHERE track_id = ?",
streamMapper,
trackId
);
}
For any statement that modifies data (INSERT, UPDATE, or DELETE ) JdbcTemplate provides the update method. Despite the name, it handles all three statement types.
updateupdate executes a SQL statement and returns the number of rows affected:
public int deleteStream(int streamId) {
return jdbcTemplate.update(
"DELETE FROM streams WHERE stream_id = ?",
streamId
);
}
In the case of a DELETE statement like in this example, the return value is useful for verifying the operation had the expected effect. If you delete by primary key and get back 0, the row did not exist.
For an INSERT, it behaves slightly differently:
public int addStream(int userId, int trackId) {
return jdbcTemplate.update(
"INSERT INTO streams (user_id, track_id, streamed_at) VALUES (?, ?, NOW())",
userId, trackId
);
}
This returns 1 on success, and 0 on failure. Note the use of 2 variables in the prepared statements here; the arguments are substituted in order.
Finally for UPDATEs:
public int endSubscription(int userId) {
return jdbcTemplate.update(
"UPDATE subscriptions SET ended_at = NOW() " +
"WHERE user_id = ? AND ended_at IS NULL",
userId
);
}
The return value here is meaningful: if the number of affected rows is 0, the user had no active subscription to end, which your application might want to treat as an error.
When you insert a row into a table with a SERIAL primary key, you often need the generated ID back, for example to insert related rows immediately after. The basic update method does not return it, so you need a slightly different approach using KeyHolder:
public int addUserAndReturnId(String userName, String email, String country) {
KeyHolder keyHolder = new GeneratedKeyHolder(); // Create an object that can hold the keys to be generated
jdbcTemplate.update(connection -> { // Call the `update` function via its lambda interface
PreparedStatement ps = connection.prepareStatement( // We must alter the statement to ensure the generated keys are returned
"INSERT INTO users (user_name, user_email, user_country) VALUES (?, ?, ?)",
Statement.RETURN_GENERATED_KEYS
);
ps.setString(1, userName);
ps.setString(2, email);
ps.setString(3, country);
return ps;
}, keyHolder); // Pass the keyholder to the jdbcTemplate can populate it after the database has generated the keys
return keyHolder.getKey().intValue();
}
Statement.RETURN_GENERATED_KEYS tells JDBC to capture the generated primary key. keyHolder.getKey() retrieves it after the insert completes.
In addition to the mentioned methods above, jdbcTemplate exposes the following methods to execute SQL on the database. For a full overview, see https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/JdbcTemplate.html.
| Read/write? | Method | Purpose |
|---|---|---|
| r | queryForObject |
Read a single row |
| r | query |
Read multiple rows |
| r+w | execute |
Perform any SQL. Most often only used for DDL statements. |
| w | update |
Single insert, update, or delete operation |
| w | batchUpdate |
Multiple insert, update, or delete operations |
In the transactions chapter you wrote BEGIN, COMMIT, and ROLLBACK manually in SQL. In Spring Boot you rarely do that. Instead, you annotate a method with @Transactional and then Spring handles the transaction lifecycle for you.
When you call a @Transactional method, Spring intercepts the call before it reaches your code, opens a transaction on the database connection, and then either commits or rolls back when the method returns (depending on whether it completed normally or threw an exception).
Your code never calls BEGIN or COMMIT. It just executes SQL and Spring wraps the boundary around it invisibly. An example:
@Service
public class SubscriptionService {
private final JdbcTemplate jdbcTemplate;
public SubscriptionService(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
@Transactional
public void upgradeSubscription(int userId, String newTier) {
// Step 1: close current subscription
jdbcTemplate.update(
"UPDATE subscriptions SET ended_at = NOW() " +
"WHERE user_id = ? AND ended_at IS NULL",
userId
);
// Step 2: open new subscription
jdbcTemplate.update(
"INSERT INTO subscriptions (user_id, plan_tier, started_at) " +
"VALUES (?, ?, NOW())",
userId, newTier
);
}
}
You can annotate single methods with @Transactional, or an entire class! Method annotations override class annotations.