Skip to content
This repository was archived by the owner on Mar 26, 2026. It is now read-only.

Commit cefea55

Browse files
olavloiteolavloite
authored
docs: create Spring Data JDBC sample (#1334)
Adds a sample for using Spring Data JDBC with Cloud Spanner PostgreSQL and shows how to run the same application on both Cloud Spanner and traditional PostgreSQL. This can be used to create a portable application, or to use PostgreSQL as a development/test database, while Cloud Spanner is used for production.
1 parent ff6648f commit cefea55

27 files changed

Lines changed: 2324 additions & 0 deletions

‎.github/workflows/spring-data-jdbc-sample.yaml‎

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,30 @@
1+
# Copyright 2023 Google LLC
2+
#
3+
# Licensed under the Apache License, Version 2.0 (the "License");
4+
# you may not use this file except in compliance with the License.
5+
# You may obtain a copy of the License at
6+
#
7+
# http://www.apache.org/licenses/LICENSE-2.0
8+
#
9+
# Unless required by applicable law or agreed to in writing, software
10+
# distributed under the License is distributed on an "AS IS" BASIS,
11+
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12+
# See the License for the specific language governing permissions and
13+
# limitations under the License.
14+
# Github action job to test core java library features on
15+
# downstream client libraries before they are released.
16+
on:
17+
pull_request:
18+
name: spring-data-jdbc-sample
19+
jobs:
20+
spring-data-jdbc:
21+
runs-on: ubuntu-latest
22+
steps:
23+
- uses: actions/checkout@v3
24+
- uses: actions/setup-java@v3
25+
with:
26+
distribution: temurin
27+
java-version: 17
28+
- name: Run tests
29+
run: mvn test
30+
working-directory: samples/spring-data-jdbc

‎samples/spring-data-jdbc/README.md‎

Lines changed: 95 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,95 @@
1+
# Spring Data JDBC Sample Application with Cloud Spanner PostgreSQL
2+
3+
This sample application shows how to develop portable applications using Spring Data JDBC in
4+
combination with Cloud Spanner PostgreSQL. This application can be configured to run on either a
5+
[Cloud Spanner PostgreSQL](https://cloud.google.com/spanner/docs/postgresql-interface) database or
6+
an open-source PostgreSQL database. The only change that is needed to switch between the two is
7+
changing the active Spring profile that is used by the application.
8+
9+
The application uses the Cloud Spanner JDBC driver to connect to Cloud Spanner PostgreSQL, and it
10+
uses the PostgreSQL JDBC driver to connect to open-source PostgreSQL. Spring Data JDBC works with
11+
both drivers and offers a single consistent API to the application developer, regardless of the
12+
actual database or JDBC driver being used.
13+
14+
This sample shows:
15+
16+
1. How to use Spring Data JDBC with Cloud Spanner PostgreSQL.
17+
2. How to develop a portable application that runs on both Google Cloud Spanner PostgreSQL and
18+
open-source PostgreSQL with the same code base.
19+
3. How to use bit-reversed sequences to automatically generate primary key values for entities.
20+
21+
__NOTE__: This application does __not require PGAdapter__. Instead, it connects to Cloud Spanner
22+
PostgreSQL using the Cloud Spanner JDBC driver.
23+
24+
## Cloud Spanner PostgreSQL
25+
26+
Cloud Spanner PostgreSQL provides language support by expressing Spanner database functionality
27+
through a subset of open-source PostgreSQL language constructs, with extensions added to support
28+
Spanner functionality like interleaved tables and hinting.
29+
30+
The PostgreSQL interface makes the capabilities of Spanner —__fully managed, unlimited scale, strong
31+
consistency, high performance, and up to 99.999% global availability__— accessible using the
32+
PostgreSQL dialect. Unlike other services that manage actual PostgreSQL database instances, Spanner
33+
uses PostgreSQL-compatible syntax to expose its existing scale-out capabilities. This provides
34+
familiarity for developers and portability for applications, but not 100% PostgreSQL compatibility.
35+
The SQL syntax that Spanner supports is semantically equivalent PostgreSQL, meaning schemas
36+
and queries written against the PostgreSQL interface can be easily ported to another PostgreSQL
37+
environment.
38+
39+
This sample showcases this portability with an application that works on both Cloud Spanner PostgreSQL
40+
and open-source PostgreSQL with the same code base.
41+
42+
## Spring Data JDBC
43+
44+
[Spring Data JDBC](https://spring.io/projects/spring-data-jdbc) is part of the larger Spring Data
45+
family. It makes it easy to implement JDBC based repositories. This module deals with enhanced
46+
support for JDBC based data access layers.
47+
48+
Spring Data JDBC aims at being conceptually easy. In order to achieve this it does NOT offer caching,
49+
lazy loading, write behind or many other features of JPA. This makes Spring Data JDBC a simple,
50+
limited, opinionated ORM.
51+
52+
## Sample Application
53+
54+
This sample shows how to create a portable application using Spring Data JDBC and the Cloud Spanner
55+
PostgreSQL dialect. The application works on both Cloud Spanner PostgreSQL and open-source
56+
PostgreSQL. You can switch between the two by changing the active Spring profile:
57+
* Profile `cs` runs the application on Cloud Spanner PostgreSQL.
58+
* Profile `pg` runs the application on open-source PostgreSQL.
59+
60+
The default profile is `cs`. You can change the default profile by modifying the
61+
[application.properties](src/main/resources/application.properties) file.
62+
63+
### Running the Application
64+
65+
1. Choose the database system that you want to use by choosing a profile. The default profile is
66+
`cs`, which runs the application on Cloud Spanner PostgreSQL. Modify the default profile in the
67+
[application.properties](src/main/resources/application.properties) file.
68+
2. Modify either [application-cs.properties](src/main/resources/application-cs.properties) or
69+
[application-pg.properties](src/main/resources/application-pg.properties) to point to an existing
70+
database. If you use Cloud Spanner, the database that the configuration file references must be a
71+
database that uses the PostgreSQL dialect.
72+
3. Run the application with `mvn spring-boot:run`.
73+
74+
### Main Application Components
75+
76+
The main application components are:
77+
* [DatabaseSeeder.java](src/main/java/com/google/cloud/spanner/sample/DatabaseSeeder.java): This
78+
class is responsible for creating the database schema and inserting some initial test data. The
79+
schema is created from the [create_schema.sql](src/main/resources/create_schema.sql) file. The
80+
`DatabaseSeeder` class loads this file into memory and executes it on the active database using
81+
standard JDBC APIs. The class also removes Cloud Spanner-specific extensions to the PostgreSQL
82+
dialect when the application runs on open-source PostgreSQL.
83+
* [JdbcConfiguration.java](src/main/java/com/google/cloud/spanner/sample/JdbcConfiguration.java):
84+
Spring Data JDBC by default detects the database dialect based on the JDBC driver that is used.
85+
This class overrides this default and instructs Spring Data JDBC to also use the PostgreSQL
86+
dialect for Cloud Spanner PostgreSQL.
87+
* [AbstractEntity.java](src/main/java/com/google/cloud/spanner/sample/entities/AbstractEntity.java):
88+
This is the shared base class for all entities in this sample application. It defines a number of
89+
standard attributes, such as the identifier (primary key). The primary key is automatically
90+
generated using a (bit-reversed) sequence. [Bit-reversed sequential values](https://cloud.google.com/spanner/docs/schema-design#bit_reverse_primary_key)
91+
are considered a good choice for primary keys on Cloud Spanner.
92+
* [Application.java](src/main/java/com/google/cloud/spanner/sample/Application.java): The starter
93+
class of the application. It contains a command-line runner that executes a selection of queries
94+
and updates on the database.
95+

‎samples/spring-data-jdbc/pom.xml‎

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<project xmlns="http://maven.apache.org/POM/4.0.0"
3+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4+
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
5+
<modelVersion>4.0.0</modelVersion>
6+
7+
<groupId>org.example</groupId>
8+
<artifactId>cloud-spanner-spring-data-jdbc-example</artifactId>
9+
<version>1.0-SNAPSHOT</version>
10+
<description>
11+
Sample application showing how to use Spring Data JDBC with Cloud Spanner PostgreSQL.
12+
</description>
13+
14+
<properties>
15+
<java.version>17</java.version>
16+
<maven.compiler.source>17</maven.compiler.source>
17+
<maven.compiler.target>17</maven.compiler.target>
18+
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
19+
</properties>
20+
21+
<dependencyManagement>
22+
<dependencies>
23+
<dependency>
24+
<groupId>org.springframework.data</groupId>
25+
<artifactId>spring-data-bom</artifactId>
26+
<version>2023.0.3</version>
27+
<scope>import</scope>
28+
<type>pom</type>
29+
</dependency>
30+
<dependency>
31+
<groupId>com.google.cloud</groupId>
32+
<artifactId>libraries-bom</artifactId>
33+
<version>26.22.0</version>
34+
<scope>import</scope>
35+
<type>pom</type>
36+
</dependency>
37+
</dependencies>
38+
</dependencyManagement>
39+
40+
<dependencies>
41+
<dependency>
42+
<groupId>org.springframework.boot</groupId>
43+
<artifactId>spring-boot-starter-data-jdbc</artifactId>
44+
<version>3.1.3</version>
45+
</dependency>
46+
47+
<!-- Add both the Cloud Spanner and the PostgreSQL JDBC driver. -->
48+
<dependency>
49+
<groupId>com.google.cloud</groupId>
50+
<artifactId>google-cloud-spanner-jdbc</artifactId>
51+
<version>2.12.0</version>
52+
</dependency>
53+
<dependency>
54+
<groupId>org.postgresql</groupId>
55+
<artifactId>postgresql</artifactId>
56+
<version>42.6.0</version>
57+
</dependency>
58+
59+
<dependency>
60+
<groupId>com.google.collections</groupId>
61+
<artifactId>google-collections</artifactId>
62+
<version>1.0</version>
63+
</dependency>
64+
65+
<!-- Test dependencies -->
66+
<dependency>
67+
<groupId>com.google.cloud</groupId>
68+
<artifactId>google-cloud-spanner</artifactId>
69+
<type>test-jar</type>
70+
<scope>test</scope>
71+
</dependency>
72+
<dependency>
73+
<groupId>com.google.api</groupId>
74+
<artifactId>gax-grpc</artifactId>
75+
<classifier>testlib</classifier>
76+
<scope>test</scope>
77+
</dependency>
78+
<dependency>
79+
<groupId>junit</groupId>
80+
<artifactId>junit</artifactId>
81+
<version>4.13.2</version>
82+
<scope>test</scope>
83+
</dependency>
84+
</dependencies>
85+
86+
<build>
87+
<plugins>
88+
<plugin>
89+
<groupId>com.spotify.fmt</groupId>
90+
<artifactId>fmt-maven-plugin</artifactId>
91+
<version>2.20</version>
92+
<executions>
93+
<execution>
94+
<goals>
95+
<goal>format</goal>
96+
</goals>
97+
</execution>
98+
</executions>
99+
</plugin>
100+
</plugins>
101+
</build>
102+
</project>

‎samples/spring-data-jdbc/src/main/java/com/google/cloud/spanner/sample/Application.java‎

Lines changed: 126 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,126 @@
1+
/*
2+
* Copyright 2023 Google LLC
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package com.google.cloud.spanner.sample;
18+
19+
import com.google.cloud.spanner.sample.entities.Album;
20+
import com.google.cloud.spanner.sample.entities.Singer;
21+
import com.google.cloud.spanner.sample.entities.Track;
22+
import com.google.cloud.spanner.sample.repositories.AlbumRepository;
23+
import com.google.cloud.spanner.sample.repositories.SingerRepository;
24+
import com.google.cloud.spanner.sample.repositories.TrackRepository;
25+
import com.google.cloud.spanner.sample.service.SingerService;
26+
import org.slf4j.Logger;
27+
import org.slf4j.LoggerFactory;
28+
import org.springframework.boot.CommandLineRunner;
29+
import org.springframework.boot.SpringApplication;
30+
import org.springframework.boot.autoconfigure.SpringBootApplication;
31+
32+
@SpringBootApplication
33+
public class Application implements CommandLineRunner {
34+
private static final Logger logger = LoggerFactory.getLogger(Application.class);
35+
36+
public static void main(String[] args) {
37+
SpringApplication.run(Application.class, args).close();
38+
}
39+
40+
private final DatabaseSeeder databaseSeeder;
41+
42+
private final SingerService singerService;
43+
44+
private final SingerRepository singerRepository;
45+
46+
private final AlbumRepository albumRepository;
47+
48+
private final TrackRepository trackRepository;
49+
50+
public Application(
51+
SingerService singerService,
52+
DatabaseSeeder databaseSeeder,
53+
SingerRepository singerRepository,
54+
AlbumRepository albumRepository,
55+
TrackRepository trackRepository) {
56+
this.databaseSeeder = databaseSeeder;
57+
this.singerService = singerService;
58+
this.singerRepository = singerRepository;
59+
this.albumRepository = albumRepository;
60+
this.trackRepository = trackRepository;
61+
}
62+
63+
@Override
64+
public void run(String... args) {
65+
66+
// Set the system property 'drop_schema' to true to drop any existing database
67+
// schema when the application is executed.
68+
if (Boolean.parseBoolean(System.getProperty("drop_schema", "false"))) {
69+
logger.info("Dropping existing schema if it exists");
70+
databaseSeeder.dropDatabaseSchemaIfExists();
71+
}
72+
73+
logger.info("Creating database schema if it does not already exist");
74+
databaseSeeder.createDatabaseSchemaIfNotExists();
75+
logger.info("Deleting existing test data");
76+
databaseSeeder.deleteTestData();
77+
logger.info("Inserting fresh test data");
78+
databaseSeeder.insertTestData();
79+
80+
Iterable<Singer> allSingers = singerRepository.findAll();
81+
for (Singer singer : allSingers) {
82+
logger.info(
83+
"Found singer: {} with {} albums",
84+
singer,
85+
albumRepository.countAlbumsBySingerId(singer.getId()));
86+
for (Album album : albumRepository.findAlbumsBySingerId(singer.getId())) {
87+
logger.info("\tAlbum: {}, released at {}", album, album.getReleaseDate());
88+
}
89+
}
90+
91+
// Create a new singer and three albums in a transaction.
92+
Singer insertedSinger =
93+
singerService.createSingerAndAlbums(
94+
new Singer("Amethyst", "Jiang"),
95+
new Album(databaseSeeder.randomTitle()),
96+
new Album(databaseSeeder.randomTitle()),
97+
new Album(databaseSeeder.randomTitle()));
98+
logger.info(
99+
"Inserted singer {} {} {}",
100+
insertedSinger.getId(),
101+
insertedSinger.getFirstName(),
102+
insertedSinger.getLastName());
103+
104+
// Create a new track record and insert it into the database.
105+
Album album = albumRepository.getFirst().orElseThrow();
106+
Track track = new Track(album, 1, databaseSeeder.randomTitle());
107+
track.setSampleRate(3.14d);
108+
// Spring Data JDBC supports the same base CRUD operations on entities as for example
109+
// Spring Data JPA.
110+
trackRepository.save(track);
111+
112+
// List all singers that have a last name starting with an 'J'.
113+
logger.info("All singers with a last name starting with an 'J':");
114+
for (Singer singer : singerRepository.findSingersByLastNameStartingWith("J")) {
115+
logger.info("\t{}", singer.getFullName());
116+
}
117+
118+
// The singerService.listSingersWithLastNameStartingWith(..) method uses a read-only
119+
// transaction. You should prefer read-only transactions to read/write transactions whenever
120+
// possible, as read-only transactions do not take locks.
121+
logger.info("All singers with a last name starting with an 'A', 'B', or 'C'.");
122+
for (Singer singer : singerService.listSingersWithLastNameStartingWith("A", "B", "C")) {
123+
logger.info("\t{}", singer.getFullName());
124+
}
125+
}
126+
}

0 commit comments

Comments
 (0)