Integrate Spanner with Spring Data JPA (PostgreSQL dialect)

Spring Data JPA, part of the larger Spring Data family, makes it easier to implement JPA based repositories. Spring Data JPA supports PostgreSQL and a wide range of other database systems. It adds an abstraction layer between your application and your database that makes your application easier to port from one database system to another.

Set up Spring Data JPA for Spanner PostgreSQL-dialect databases

You can integrate Spanner PostgreSQL-dialect databases with Spring Data JPA using the standard PostgreSQL Hibernate dialect and PGAdapter.

To see an example, refer to the full working sample application on GitHub.

Dependencies

In your project, add Apache Maven dependencies for Spring Data JPA, the PostgreSQL JDBC driver, and PGAdapter.

<dependencies> <!--SpringDataJPA--> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <!--AddthePostgreSQLJDBCdriver--> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> </dependency> <!--AddPGAdapterasadependency,sowecanstartitin-process--> <dependency> <groupId>com.google.cloud</groupId> <artifactId>google-cloud-spanner-pgadapter</artifactId> </dependency> </dependencies>

Start PGAdapter in-process

Add the following method to your application to start PGAdapter directly from your Java application. PGAdapter runs in the same JVM as your application, and the application connects to PGAdapter on localhost:port.

/** Starts PGAdapter in-process and returns a reference to the server. */staticProxyServerstartPGAdapter(){// Start PGAdapter using the default credentials of the runtime environment on port 9432.OptionsMetadataoptions=OptionsMetadata.newBuilder().setPort(9432).build();ProxyServerserver=newProxyServer(options);server.startServer();server.awaitRunning();returnserver;}

Configuration

Configure application.properties to use the PostgreSQL Hibernate Dialect and the PostgreSQL JDBC Driver. Configure the PostgreSQL JDBC Driver to connect to a PostgreSQL-dialect database through PGAdapter.

#TheexampleusesthestandardPostgreSQLHibernatedialect. spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect #Definingthesepropertiesheremakesitabiteasiertobuildtheconnectionstring. #ChangethesetomatchyourCloudSpannerPostgreSQL-dialectdatabase. spanner.project=my-project spanner.instance=my-instance spanner.database=my-database #ThissettingensuresthatPGAdapterautomaticallycommitsthecurrenttransactionifitencounters #aDDLstatementinaread/writetransaction,andthenexecutestheDDLstatementsasasingleDDL #batch. spanner.ddl_transaction_mode=options=-c%20spanner.ddl_transaction_mode=AutocommitExplicitTransaction #ThisistheconnectionstringtoPGAdapterrunningin-process. spring.datasource.url=jdbc:postgresql://localhost:9432/projects%2F${spanner.project}%2Finstances%2F${spanner.instance}%2Fdatabases%2F${spanner.database}?${spanner.ddl_transaction_mode} #YoucandisplaySQLstatementsandstatsfordebuggingifneeded. spring.jpa.properties.hibernate.show_sql=true spring.jpa.properties.hibernate.format_sql=true #EnableJDBCbatching. spring.jpa.properties.hibernate.jdbc.batch_size=100 spring.jpa.properties.hibernate.order_inserts=true

Full Sample Application

A working sample application is available on GitHub.

What's next