Skip to content

Commit 9797203

Browse files
olavloiteolavloite
authored
docs: add sample for Spring Data MyBatis (googleapis#1352)
* docs: add sample for Spring Data MyBatis Adds a sample application for using Spring Data and MyBatis with the Cloud Spanner JDBC driver and a PostgreSQL-dialect database. * chore: update sample database name
1 parent a799596 commit 9797203

27 files changed

Lines changed: 2495 additions & 0 deletions

‎java-spanner-jdbc/samples/spring-data-mybatis/README.md‎

Lines changed: 93 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,93 @@
1+
# Spring Data MyBatis Sample Application with Cloud Spanner PostgreSQL
2+
3+
This sample application shows how to develop portable applications using Spring Data MyBatis 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 MyBatis 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 MyBatis 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+
## MyBatis Spring
43+
[MyBatis Spring](http://mybatis.org/spring/) integrates MyBatis with the popular Java Spring
44+
framework. This allows MyBatis to participate in Spring transactions and to automatically inject
45+
MyBatis mappers into other beans.
46+
47+
## Sample Application
48+
49+
This sample shows how to create a portable application using Spring Data MyBatis and the Cloud Spanner
50+
PostgreSQL dialect. The application works on both Cloud Spanner PostgreSQL and open-source
51+
PostgreSQL. You can switch between the two by changing the active Spring profile:
52+
* Profile `cs` runs the application on Cloud Spanner PostgreSQL.
53+
* Profile `pg` runs the application on open-source PostgreSQL.
54+
55+
The default profile is `cs`. You can change the default profile by modifying the
56+
[application.properties](src/main/resources/application.properties) file.
57+
58+
### Running the Application
59+
60+
1. Choose the database system that you want to use by choosing a profile. The default profile is
61+
`cs`, which runs the application on Cloud Spanner PostgreSQL. Modify the default profile in the
62+
[application.properties](src/main/resources/application.properties) file.
63+
2. Modify either [application-cs.properties](src/main/resources/application-cs.properties) or
64+
[application-pg.properties](src/main/resources/application-pg.properties) to point to an existing
65+
database. If you use Cloud Spanner, the database that the configuration file references must be a
66+
database that uses the PostgreSQL dialect.
67+
3. Run the application with `mvn spring-boot:run`.
68+
69+
### Main Application Components
70+
71+
The main application components are:
72+
* [DatabaseSeeder.java](src/main/java/com/google/cloud/spanner/sample/DatabaseSeeder.java): This
73+
class is responsible for creating the database schema and inserting some initial test data. The
74+
schema is created from the [create_schema.sql](src/main/resources/create_schema.sql) file. The
75+
`DatabaseSeeder` class loads this file into memory and executes it on the active database using
76+
standard JDBC APIs. The class also removes Cloud Spanner-specific extensions to the PostgreSQL
77+
dialect when the application runs on open-source PostgreSQL.
78+
* [JdbcConfiguration.java](src/main/java/com/google/cloud/spanner/sample/JdbcConfiguration.java):
79+
This utility class is used to determine whether the application is running on Cloud Spanner
80+
PostgreSQL or open-source PostgreSQL. This can be used if you have specific features that should
81+
only be executed on one of the two systems.
82+
* [AbstractEntity.java](src/main/java/com/google/cloud/spanner/sample/entities/AbstractEntity.java):
83+
This is the shared base class for all entities in this sample application. It defines a number of
84+
standard attributes, such as the identifier (primary key). The primary key is automatically
85+
generated using a (bit-reversed) sequence. [Bit-reversed sequential values](https://cloud.google.com/spanner/docs/schema-design#bit_reverse_primary_key)
86+
are considered a good choice for primary keys on Cloud Spanner.
87+
* [Application.java](src/main/java/com/google/cloud/spanner/sample/Application.java): The starter
88+
class of the application. It contains a command-line runner that executes a selection of queries
89+
and updates on the database.
90+
* [SingerService](src/main/java/com/google/cloud/spanner/sample/service/SingerService.java) and
91+
[AlbumService](src/main/java/com/google/cloud/spanner/sample/service/SingerService.java) are
92+
standard Spring service beans that contain business logic that can be executed as transactions.
93+
This includes both read/write and read-only transactions.

‎java-spanner-jdbc/samples/spring-data-mybatis/pom.xml‎

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

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

Lines changed: 133 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,133 @@
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.mappers.AlbumMapper;
23+
import com.google.cloud.spanner.sample.mappers.SingerMapper;
24+
import com.google.cloud.spanner.sample.service.AlbumService;
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 AlbumService albumService;
45+
46+
private final SingerMapper singerMapper;
47+
48+
private final AlbumMapper albumMapper;
49+
50+
public Application(
51+
SingerService singerService,
52+
AlbumService albumService,
53+
DatabaseSeeder databaseSeeder,
54+
SingerMapper singerMapper,
55+
AlbumMapper albumMapper) {
56+
this.databaseSeeder = databaseSeeder;
57+
this.singerService = singerService;
58+
this.albumService = albumService;
59+
this.singerMapper = singerMapper;
60+
this.albumMapper = albumMapper;
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 = singerMapper.findAll();
81+
for (Singer singer : allSingers) {
82+
logger.info(
83+
"Found singer: {} with {} albums",
84+
singer,
85+
albumMapper.countAlbumsBySingerId(singer.getId()));
86+
for (Album album : albumMapper.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 Album and some Tracks in a read/write transaction.
105+
// Track is an interleaved table.
106+
Album album = new Album(DatabaseSeeder.randomTitle());
107+
album.setSingerId(insertedSinger.getId());
108+
albumService.createAlbumAndTracks(
109+
album,
110+
new Track(album, 1, DatabaseSeeder.randomTitle(), 3.14d),
111+
new Track(album, 2, DatabaseSeeder.randomTitle(), 3.14d),
112+
new Track(album, 3, DatabaseSeeder.randomTitle(), 3.14d),
113+
new Track(album, 4, DatabaseSeeder.randomTitle(), 3.14d),
114+
new Track(album, 5, DatabaseSeeder.randomTitle(), 3.14d),
115+
new Track(album, 6, DatabaseSeeder.randomTitle(), 3.14d),
116+
new Track(album, 7, DatabaseSeeder.randomTitle(), 3.14d));
117+
logger.info("Inserted album {}", album.getTitle());
118+
119+
// List all singers that have a last name starting with an 'J'.
120+
logger.info("All singers with a last name starting with an 'J':");
121+
for (Singer singer : singerMapper.findSingersByLastNameStartingWith("J")) {
122+
logger.info("\t{}", singer.getFullName());
123+
}
124+
125+
// The singerService.listSingersWithLastNameStartingWith(..) method uses a read-only
126+
// transaction. You should prefer read-only transactions to read/write transactions whenever
127+
// possible, as read-only transactions do not take locks.
128+
logger.info("All singers with a last name starting with an 'A', 'B', or 'C'.");
129+
for (Singer singer : singerService.listSingersWithLastNameStartingWith("A", "B", "C")) {
130+
logger.info("\t{}", singer.getFullName());
131+
}
132+
}
133+
}

0 commit comments

Comments
 (0)