Thursday, April 25, 2024
No menu items!
HomeCloud ComputingLightweight Application Development with Serverless Cloud Functions (Java) and Cloud SQL (SQL...
Cloud Computing

Lightweight Application Development with Serverless Cloud Functions (Java) and Cloud SQL (SQL Server) in 2 minutes

By mullaned2002
0
550

Introduction

In this post, you’ll learn to build a Java based Cloud Function that will connect to a Cloud SQL for SQL Server database using the Cloud SQL Connector for Java. This solution will help you learn to build event driven lightweight solutions for any stand-alone functionality with Cloud SQL database that respond to Cloud events without needing to manage a server or runtime environment. 

What is Cloud SQL?

Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on Google Cloud Platform. It has 99.95% availability and supports up to 64 TB of storage available, with the ability to automatically increase storage size as needed. For a list of detailed features, refer to the documentation here.

What is Cloud Functions?

Cloud Functions is a lightweight serverless compute solution for developers to create single-purpose, stand-alone functions that respond to Cloud events without needing to manage a server or runtime environment.

What you’ll build

You’ll write a Cloud Function in Java. The function:

Creates a Cloud SQL for SQL Server instanceConnects to a Cloud SQL for SQL Server Database instance using a Cloud SQL Connector methodCreates a table in the database

What you’ll learn

How to create a Cloud SQL for SQL Server instanceHow to access the Cloud Functions web UI in the Google Cloud ConsoleHow to create a Cloud FunctionHow to test a Cloud FunctionHow to connect to a Cloud SQL database instance (SQL Server) using JavaHow to run DDL operations on a Cloud SQL database using Java and Cloud SQL Connector method

Requirements

A browser, such as Chrome or FirefoxA Google Cloud Platform project that contains your Cloud SQL instanceThe next section has the list of steps to create a Cloud SQL for SQL Server instance 

1. Create the Cloud SQL – SQL Server instance

From Google Cloud console, go to Cloud SQL Select SQL Server, and choose the SQL Server 2019 Standard as the Database version Choose from Development instance with default compute size and memory (you can change it later if needed) and make sure you leave the Public IP in the Networking section enabled

Go to Databases on the left side menu once the instance is created and create a database

Go to Users on the left side menu and configure a new user account (with a username and password) for the instance. You can alternatively use the default user as well. 

2. Prepare code and create Cloud Function

Prepare Code

The Cloud Function code for connecting to a Cloud SQL database is available below. Some variable values depend on your Cloud SQL database configuration, and depend on your own database information. The function connects to SQL Server database using  Cloud SQL Connector JDBC for SQL Server and creates a table in the database.

The Cloud Functions UI in the Cloud Console includes a text editor. You can copy/paste and edit the code there, or edit the code locally first, and then copy/paste it into the UI. 

/pom.xml

You can find the pom.xml file in the repository: https://github.com/AbiramiSukumaran/CloudFunctions_CloudSQL/blob/main/pom.xml

The Java class for implementing the functionality:
https://github.com/AbiramiSukumaran/CloudFunctions_CloudSQL/blob/main/src/main/java/com/example/Example.java

/src/main/java/com/example/Example.java

code_block[StructValue([(u’code’, u’package com.example;rn rnimport com.google.cloud.functions.HttpFunction;rnimport com.google.cloud.functions.HttpRequest;rnimport com.google.cloud.functions.HttpResponse;rnimport java.io.BufferedWriter;rnimport com.zaxxer.hikari.HikariConfig;rnimport com.zaxxer.hikari.HikariDataSource;rnimport edu.umd.cs.findbugs.annotations.SuppressFBWarnings;rnimport java.sql.Connection;rnimport java.sql.PreparedStatement;rnimport java.sql.SQLException;rnimport javax.servlet.ServletContext;rnimport javax.servlet.ServletContextEvent;rnimport javax.servlet.ServletContextListener;rnimport javax.servlet.annotation.WebListener;rnimport javax.sql.DataSource;rnimport java.sql.PreparedStatement;rnimport java.sql.ResultSet;rnimport java.util.ArrayList;rnimport java.util.List;rnimport java.util.Properties;rnimport java.util.UUID;rnimport java.util.concurrent.TimeUnit;rn rnpublic class Example implements HttpFunction {rn@Overridern public void service(HttpRequest request, HttpResponse response) throws Exception {rn createConnectionPool();rn BufferedWriter writer = response.getWriter();rn writer.write(“Hello. I have successfully completed your work – created table in SQL Server!”);rn }rn /* Saving credentials in environment variables is convenient, but not secure – consider a more secure solution such as https://cloud.google.com/kms/ to help keep secrets safe. */rn private static final String INSTANCE_CONNECTION_NAME =”YOUR_CONNECTION”;rn private static final String DB_USER = “YOUR_USERNAME”;rn private static final String DB_PASS = “YOUR_PASSWORD”;rn private static final String DB_NAME = “testdatabase”;rn private HikariDataSource connectionPool;rn private String tableName;rn rn public void createConnectionPool() throws SQLException {rn HikariConfig config = new HikariConfig();rn configrn .setDataSourceClassName(“com.microsoft.sqlserver.jdbc.SQLServerDataSource”);rn config.setUsername(DB_USER);rn config.setPassword(DB_PASS); rn config.addDataSourceProperty(“databaseName”, DB_NAME);rn config.addDataSourceProperty(“socketFactoryClass”,rn “com.google.cloud.sql.sqlserver.SocketFactory”);rn config.addDataSourceProperty(“socketFactoryConstructorArg”, INSTANCE_CONNECTION_NAME);rn config.addDataSourceProperty(“encrypt”, “true”);rn config.addDataSourceProperty(“trustServerCertificate”,”true”);rn config.setMaximumPoolSize(5); rn config.setMinimumIdle(5);rn config.setConnectionTimeout(10000); // 10 secondsrn config.setIdleTimeout(600000); // 10 minutes rn config.setMaxLifetime(1800000); // 30 minutes rn DataSource pool = new HikariDataSource(config); rn this.connectionPool = new HikariDataSource(config);rn this.tableName = String.format(“books_%s”, UUID.randomUUID().toString().replace(“-“, “”));rn // Create tablern try (Connection conn = connectionPool.getConnection()) {rn String stmt = String.format(“CREATE TABLE %s (“, this.tableName)rn + ” ID CHAR(20) NOT NULL,”rn + ” TITLE TEXT NOT NULL”rn + “);”;rn try (PreparedStatement createTableStatement = conn.prepareStatement(stmt)) {rn createTableStatement.execute();rn }rn }rn }rn}’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e60a6c2d850>)])]

3. Create Cloud Function

Create the function

1. In a browser, go to the Google Cloud Platform Console UI

2. Select Cloud Functions from the Navigation menu

3. Click CREATE FUNCTION on the button bar

4. Enter a name for the function

5. Select the HTTP trigger. (Make a note of the URL displayed beneath the trigger item. It will be in this format: https://REGION-PROJECT_ID.cloudfunctions.net/FUNCTION_NAME)

6. Under Authentication, select Allow unauthenticated invocations to make the function public in this example, to make it accessible to test from the browser by clicking the URL in Trigger tab of the Functions console

7. Expand the Runtime, Build and Connections Settings In Runtime service account, select a service account that has the Cloud SQL Client role

8. Click the NEXT button

9. Select Runtime : Java 11 for the runtime option

10. Select Inline editor for the source code option

11. Select the source code editor windows, delete the existing content for both pom.xml and Example.java, and replace them with your edited versions of the code above

12. Enter “ CF-CloudSQL” as the name of the Entry point

13. Click Deploy and wait while the function is created. The spinner stops spinning and a green check appears on the subsequent page when the function is ready to use

14. Add permission for “allUsers” principal to “Cloud Functions Invoker” role

4. Test the function

1. Click on the name of the function you created in the above steps

2. Select the TESTING link in the middle of the page

3. Select TEST THE FUNCTION

4. The result should appear. If the test fails, you’ll see a stack trace to help with debugging

5. In a browser, go to the URL that you saved earlier, when you created the function. If you forgot to save the URL, you can get it from the TRIGGER link

6. The ok result should appear in the browser as well

7. You can check for details in the “DETAILS” tab

8. You can check for log and console information in the “LOGS” tab

5. Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this post, follow these steps.

Delete the Cloud SQL instance

1. Go to the Cloud SQL Instances page in the Google Cloud Console

2. Select the instance you created to open the Instance details page

3. In the icon bar at the top of the page, click Delete

4. In the Delete instance window, type the name of your instance, then click Delete to delete the instance. You cannot reuse an instance name for about 7 days after an instance is deleted

Delete the Cloud Function

1. Go to the Cloud Functions page in the Google Cloud Console

2. Select the three dots under Actions for your function and choose Delete

3. Confirm deletion by clicking the DELETE button

6. Congratulations

Congratulations, you’ve successfully created Cloud SQL – SQL Server instance and built a Cloud Function that works with Cloud SQL.

Specifically, you’ve created a Java Cloud Function that connects to and creates a table in a Cloud SQLfor SQL Server database instance using Cloud SQL connector method. This kind of function helps you build stand alone compute solutions for event-driven cloud use cases using Cloud SQL data that is handled by other enterprise applications. Now go ahead and extend this learning to implement an update triggered calculation in an existing table assuming that the data in the table is getting transactionally updated from multiple sources.

7. Before you go…

Check out some of these codelabs and resourceful reads.

Connecting to Cloud SQL: Compute Engine, Private IP and Cloud SQL ProxyIntroduction to Cloud SQL Insightshttps://codelabs.developers.google.com/codelabs/connecting-to-cloud-sql-with-cloud-functions#0Cloud Functions documentationCloud SQL documentation

https://cloud.google.com/sql/docs/sqlserver/connect-connectors

Related Article

The definitive guide to databases on Google Cloud: Part 1 – Data modeling basics

In this blog we discuss the business attributes, technical aspects, design questions, considerations to keep in mind while “Designing the…

Read Article

Related Article

A Guide to Databases on Google Cloud part 3 – Cloud Spanner! & CRUD it with Spring Boot on Cloud Run

In this “A Guide to Databases on Google Cloud part 3 – Cloud Spanner and CRUD it with Spring Boot on Cloud Run” blog we are going to expe…

Read Article

Related Article

Databases on Google Cloud Part 4: Query, Index, CRUD and Crush your Java app with Firestore APIs

In this “A Guide to Databases on Google Cloud part 2 – Options at a glance”, We will look into setting up Firestore, creating complex que…

Read Article

Cloud BlogRead More

Previous articleUniversity IT professionals compete in first-ever Cloud Hero Cup
Next articleHow to Calculate Precision, Recall, F1, and More for Deep Learning Models
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Load more

Recent Comments

EDITOR PICKS

POPULAR POSTS

POPULAR CATEGORY