42 lines
18 KiB
HTML
42 lines
18 KiB
HTML
<html><head>
|
|
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
|
|
<title>155. Spring JDBC</title><link rel="stylesheet" type="text/css" href="css/manual-multipage.css"><meta name="generator" content="DocBook XSL Stylesheets V1.79.1"><link rel="home" href="multi_spring-cloud.html" title="Spring Cloud"><link rel="up" href="multi_spring-cloud-gcp-reference.html" title="Part XVIII. Spring Cloud GCP"><link rel="prev" href="multi__spring_resources.html" title="154. Spring Resources"><link rel="next" href="multi__spring_integration.html" title="156. Spring Integration"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">155. Spring JDBC</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="multi__spring_resources.html">Prev</a> </td><th width="60%" align="center">Part XVIII. Spring Cloud GCP</th><td width="20%" align="right"> <a accesskey="n" href="multi__spring_integration.html">Next</a></td></tr></table><hr></div><div class="chapter"><div class="titlepage"><div><div><h2 class="title"><a name="_spring_jdbc" href="#_spring_jdbc"></a>155. Spring JDBC</h2></div></div></div><p>Spring Cloud GCP adds integrations with
|
|
<a class="link" href="https://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html" target="_top">Spring JDBC</a> so you can run your MySQL or PostgreSQL databases in Google Cloud SQL using Spring JDBC, or other libraries that depend on it like Spring Data JPA.</p><p>The Cloud SQL support is provided by Spring Cloud GCP in the form of two Spring Boot starters, one for MySQL and another one for PostgreSQL.
|
|
The role of the starters is to read configuration from properties and assume default settings so that user experience connecting to MySQL and PostgreSQL is as simple as possible.</p><p>Maven coordinates, using Spring Cloud GCP BOM:</p><pre class="programlisting"><span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><dependency></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><groupId></span>org.springframework.cloud<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></groupId></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><artifactId></span>spring-cloud-gcp-starter-sql-mysql<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></artifactId></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></dependency></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><dependency></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><groupId></span>org.springframework.cloud<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></groupId></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><artifactId></span>spring-cloud-gcp-starter-sql-postgresql<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></artifactId></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></dependency></span></pre><p>Gradle coordinates:</p><pre class="screen">dependencies {
|
|
compile group: 'org.springframework.cloud', name: 'spring-cloud-gcp-starter-sql-mysql'
|
|
compile group: 'org.springframework.cloud', name: 'spring-cloud-gcp-starter-sql-postgresql'
|
|
}</pre><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="_prerequisites" href="#_prerequisites"></a>155.1 Prerequisites</h2></div></div></div><p>In order to use the Spring Boot Starters for Google Cloud SQL, the Google Cloud SQL API must be enabled in your GCP project.</p><p>To do that, go to the <a class="link" href="https://console.cloud.google.com/apis/library" target="_top">API library page</a> of the Google Cloud Console, search for "Cloud SQL API", click the first result and enable the API.</p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Note"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Note]" src="images/note.png"></td><th align="left">Note</th></tr><tr><td align="left" valign="top"><p>There are several similar "Cloud SQL" results.
|
|
You must access the "Google Cloud SQL API" one and enable the API from there.</p></td></tr></table></div></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="_spring_boot_starter_for_google_cloud_sql" href="#_spring_boot_starter_for_google_cloud_sql"></a>155.2 Spring Boot Starter for Google Cloud SQL</h2></div></div></div><p>The Spring Boot Starters for Google Cloud SQL provide an auto-configured <a class="link" href="https://docs.oracle.com/javase/7/docs/api/javax/sql/DataSource.html" target="_top"><code class="literal">DataSource</code></a> object.
|
|
Coupled with Spring JDBC, it provides a
|
|
<a class="link" href="https://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html#jdbc-JdbcTemplate" target="_top"><code class="literal">JdbcTemplate</code></a> object bean that allows for operations such as querying and modifying a database.</p><pre class="programlisting"><span xmlns:d="http://docbook.org/ns/docbook" class="hl-keyword">public</span> List<Map<String, Object>> listUsers() {
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-keyword">return</span> jdbcTemplate.queryForList(<span xmlns:d="http://docbook.org/ns/docbook" class="hl-string">"SELECT * FROM user;"</span>);
|
|
}</pre><p>You can rely on
|
|
<a class="link" href="https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html#boot-features-connect-to-production-database" target="_top">Spring Boot data source auto-configuration</a> to configure a <code class="literal">DataSource</code> bean.
|
|
In other words, properties like the SQL username, <code class="literal">spring.datasource.username</code>, and password, <code class="literal">spring.datasource.password</code> can be used.
|
|
There is also some configuration specific to Google Cloud SQL:</p><div class="informaltable"><table class="informaltable" style="border-collapse: collapse;border-top: 1px solid ; border-bottom: 1px solid ; "><colgroup><col class="col_1"><col class="col_2"><col class="col_3"></colgroup><tbody><tr><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p>Property name</p></td><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p>Description</p></td><td style="border-bottom: 1px solid ; " align="left" valign="top"><p>Default value</p></td></tr><tr><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p><code class="literal">spring.cloud.gcp.sql.enabled</code></p></td><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p>Enables or disables Cloud SQL auto configuration</p></td><td style="border-bottom: 1px solid ; " align="left" valign="top"><p><code class="literal">true</code></p></td></tr><tr><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p><code class="literal">spring.cloud.gcp.sql.database-name</code></p></td><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p>Name of the database to connect to.</p></td><td style="border-bottom: 1px solid ; " align="left" valign="top"> </td></tr><tr><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p><code class="literal">spring.cloud.gcp.sql.instance-connection-name</code></p></td><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p>A string containing a Google Cloud SQL instance’s project ID, region and name, each separated by a colon.
|
|
For example, <code class="literal">my-project-id:my-region:my-instance-name</code>.</p></td><td style="border-bottom: 1px solid ; " align="left" valign="top"> </td></tr><tr><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p><code class="literal">spring.cloud.gcp.sql.credentials.location</code></p></td><td style="border-right: 1px solid ; border-bottom: 1px solid ; " align="left" valign="top"><p>File system path to the Google OAuth2 credentials private key file.
|
|
Used to authenticate and authorize new connections to a Google Cloud SQL instance.</p></td><td style="border-bottom: 1px solid ; " align="left" valign="top"><p>Default credentials provided by the Spring GCP Boot starter</p></td></tr><tr><td style="border-right: 1px solid ; " align="left" valign="top"><p><code class="literal">spring.cloud.gcp.sql.credentials.encoded-key</code></p></td><td style="border-right: 1px solid ; " align="left" valign="top"><p>Base64-encoded contents of OAuth2 account private key in JSON format.
|
|
Used to authenticate and authorize new connections to a Google Cloud SQL instance.</p></td><td style="" align="left" valign="top"><p>Default credentials provided by the Spring GCP Boot starter</p></td></tr></tbody></table></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Note"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Note]" src="images/note.png"></td><th align="left">Note</th></tr><tr><td align="left" valign="top"><p>If you provide your own <code class="literal">spring.datasource.url</code>, it will be ignored, unless you disable Cloud SQL auto configuration with <code class="literal">spring.cloud.gcp.sql.enabled=false</code>.</p></td></tr></table></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a name="_datasource_creation_flow" href="#_datasource_creation_flow"></a>155.2.1 <code class="literal">DataSource</code> creation flow</h3></div></div></div><p>Based on the previous properties, the Spring Boot starter for Google Cloud SQL creates a <code class="literal">CloudSqlJdbcInfoProvider</code> object which is used to obtain an instance’s JDBC URL and driver class name.
|
|
If you provide your own <code class="literal">CloudSqlJdbcInfoProvider</code> bean, it is used instead and the properties related to building the JDBC URL or driver class are ignored.</p><p>The <code class="literal">DataSourceProperties</code> object provided by Spring Boot Autoconfigure is mutated in order to use the JDBC URL and driver class names provided by <code class="literal">CloudSqlJdbcInfoProvider</code>, unless those values were provided in the properties.
|
|
It is in the <code class="literal">DataSourceProperties</code> mutation step that the credentials factory is registered in a system property to be <code class="literal">SqlCredentialFactory</code>.</p><p><code class="literal">DataSource</code> creation is delegated to
|
|
<a class="link" href="https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html" target="_top">Spring Boot</a>.
|
|
You can select the type of connection pool (e.g., Tomcat, HikariCP, etc.) by <a class="link" href="https://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html#boot-features-connect-to-production-database" target="_top">adding their dependency to the classpath</a>.</p><p>Using the created <code class="literal">DataSource</code> in conjunction with Spring JDBC provides you with a fully configured and operational <code class="literal">JdbcTemplate</code> object that you can use to interact with your SQL database.
|
|
You can connect to your database with as little as a database and instance names.</p></div><div class="section"><div class="titlepage"><div><div><h3 class="title"><a name="_troubleshooting_tips" href="#_troubleshooting_tips"></a>155.2.2 Troubleshooting tips</h3></div></div></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a name="connection-issues" href="#connection-issues"></a>Connection issues</h4></div></div></div><p>If you’re not able to connect to a database and see an endless loop of <code class="literal">Connecting to Cloud SQL instance […​] on IP […​]</code>, it’s likely that exceptions are being thrown and logged at a level lower than your logger’s level.
|
|
This may be the case with HikariCP, if your logger is set to INFO or higher level.</p><p>To see what’s going on in the background, you should add a <code class="literal">logback.xml</code> file to your application resources folder, that looks like this:</p><pre class="programlisting"><span class="hl-directive" style="color: maroon"><?xml version="1.0" encoding="UTF-8"?></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><configuration></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><include</span> <span xmlns:d="http://docbook.org/ns/docbook" class="hl-attribute">resource</span>=<span xmlns:d="http://docbook.org/ns/docbook" class="hl-value">"org/springframework/boot/logging/logback/base.xml"</span><span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag">/></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><logger</span> <span xmlns:d="http://docbook.org/ns/docbook" class="hl-attribute">name</span>=<span xmlns:d="http://docbook.org/ns/docbook" class="hl-value">"com.zaxxer.hikari.pool"</span> <span xmlns:d="http://docbook.org/ns/docbook" class="hl-attribute">level</span>=<span xmlns:d="http://docbook.org/ns/docbook" class="hl-value">"DEBUG"</span><span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag">/></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></configuration></span></pre></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a name="_errors_like_c_g_cloud_sql_core_sslsocketfactory_re_throwing_cached_exception_due_to_attempt_to_refresh_instance_information_too_soon_after_error" href="#_errors_like_c_g_cloud_sql_core_sslsocketfactory_re_throwing_cached_exception_due_to_attempt_to_refresh_instance_information_too_soon_after_error"></a>Errors like <code class="literal">c.g.cloud.sql.core.SslSocketFactory : Re-throwing cached exception due to attempt to refresh instance information too soon after error</code></h4></div></div></div><p>If you see a lot of errors like this in a loop and can’t connect to your database, this is usually a symptom that something isn’t right with the permissions of your credentials or the Google Cloud SQL API is not enabled.
|
|
Verify that the Google Cloud SQL API is enabled in the Cloud Console and that your service account has the <a class="link" href="https://cloud.google.com/sql/docs/mysql/project-access-control#roles" target="_top">necessary IAM roles</a>.</p><p>To find out what’s causing the issue, you can enable DEBUG logging level as mentioned <a class="link" href="multi__spring_jdbc.html#connection-issues" title="Connection issues">above</a>.</p></div><div class="section"><div class="titlepage"><div><div><h4 class="title"><a name="_postgresql_java_net_socketexception_already_connected_issue" href="#_postgresql_java_net_socketexception_already_connected_issue"></a>PostgreSQL: <code class="literal">java.net.SocketException: already connected</code> issue</h4></div></div></div><p>We found this exception to be common if your Maven project’s parent is <code class="literal">spring-boot</code> version <code class="literal">1.5.x</code>, or in any other circumstance that would cause the version of the <code class="literal">org.postgresql:postgresql</code> dependency to be an older one (e.g., <code class="literal">9.4.1212.jre7</code>).</p><p>To fix this, re-declare the dependency in its correct version.
|
|
For example, in Maven:</p><pre class="programlisting"><span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><dependency></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><groupId></span>org.postgresql<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></groupId></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><artifactId></span>postgresql<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></artifactId></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"><version></span>42.1.1<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></version></span>
|
|
<span xmlns:d="http://docbook.org/ns/docbook" class="hl-tag"></dependency></span></pre></div></div></div><div class="section"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="_samples_3" href="#_samples_3"></a>155.3 Samples</h2></div></div></div><p>Available sample applications and codelabs:</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><a class="link" href="https://github.com/spring-cloud/spring-cloud-gcp/tree/master/spring-cloud-gcp-samples/spring-cloud-gcp-sql-mysql-sample" target="_top">Spring Cloud GCP MySQL</a></li><li class="listitem"><a class="link" href="https://github.com/spring-cloud/spring-cloud-gcp/tree/master/spring-cloud-gcp-samples/spring-cloud-gcp-sql-postgres-sample" target="_top">Spring Cloud GCP PostgreSQL</a></li><li class="listitem"><a class="link" href="https://github.com/spring-cloud/spring-cloud-gcp/tree/master/spring-cloud-gcp-samples/spring-cloud-gcp-data-jpa-sample" target="_top">Spring Data JPA with Spring Cloud GCP SQL</a></li><li class="listitem">Codelab: <a class="link" href="https://codelabs.developers.google.com/codelabs/cloud-spring-petclinic-cloudsql/index.html" target="_top">Spring Pet Clinic using Cloud SQL</a></li></ul></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="multi__spring_resources.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="multi_spring-cloud-gcp-reference.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="multi__spring_integration.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">154. Spring Resources </td><td width="20%" align="center"><a accesskey="h" href="multi_spring-cloud.html">Home</a></td><td width="40%" align="right" valign="top"> 156. Spring Integration</td></tr></table></div></body></html> |