Search code examples
javamysqlhibernategoogle-app-enginegoogle-cloud-sql

How to configure Java Hibernate for Google Cloud SQL?

I am using Hibernate + Java + Jersey + MYSql and it's working fine on local machine. However I am unable to create a connection to MySql on Google Cloud.

I think MYSql configuration and instance creation is fine on Google Cloud, but I am unable to pass all configuration through hibernate properties. See here what Google's sample code:

// The configuration object specifies behaviors for the connection pool.
HikariConfig config = new HikariConfig();

// Configure which instance and what database user to connect with.
config.setJdbcUrl(String.format("jdbc:mysql:///%s", DB_NAME));
config.setUsername(DB_USER); // e.g. "root", "postgres"
config.setPassword(DB_PASS); // e.g. "my-password"

// For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections.
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.
config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_CONNECTION_NAME);
config.addDataSourceProperty("useSSL", "false");

// ... Specify additional connection properties here.
// ...

// Initialize the connection pool using the configuration object.
DataSource pool = new HikariDataSource(config);

Now I dont know how to pass cloudSqlInstance and socketFactory in Hibernate. Here what I tried to pass these parameters but it's not working:

hibernate.connection.driver_class = com.mysql.jdbc.Driver
hibernate.connection.url = jdbc:mysql://google/pashumandi_db?cloudSqlInstance=pashuserver:asia-south1:mysql-instance&socketFactory=com.google.cloud.sql.mysql.SocketFactory
hibernate.connection.username = abc_user
hibernate.connection.password = 12345678
hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

Could you please let me know what is correct hibernate configuration to connect with MySql on Google Cloud? Thanks.

Solution

  • Find the INSTANCE_CONNECTION_NAME for the instance on the Instance details page on Google Cloud Console. It uses the format PROJECT_ID:REGION:INSTANCE_ID, and is used to identify the Cloud SQL instance you are connecting to.

    To enable a local TCP port, add the following to your project's app.yaml file: 

    runtime: java
env: flex
beta_settings:
  cloud_sql_instances: <INSTANCE_CONNECTION_NAME>=tcp:<PORT>

    Then here is my hibernate.properties file:

    hibernate.connection.driver_class = com.mysql.jdbc.Driver
hibernate.connection.url = jdbc:mysql://172.17.0.1:PORT_NUMBER_IN_YAML_FILE
hibernate.connection.username = DB_USERNAME
hibernate.connection.password = DB_PASSWORD
hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
hibernate.default_schema = DATABASE_NAME
hibernate.show_sql=true
hibernate.hbm2ddl.auto=create

    For more details you can visit: https://cloud.google.com/sql/docs/mysql/connect-app-engine