How to connect to cloud sql when using app engine instance in java 21 runtime?

Question

I have a java ee 8 application which I have migrated to a jakarta 10 application. I use google app engine standard server. I recently migrated it to java 17 runtime and it was connecting to the cloud sql database and everything was working well. Here is my pom.xml file

   <?xml version="1.0" encoding="UTF-8"?>
   <project xmlns="http://maven.apache.org/POM/4.0.0"   
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.figjaminc</groupId>
<artifactId>XXXXX</artifactId>
<version>1.0</version>
<packaging>war</packaging>

<name>XXXX</name>

<properties>
    <endorsed.dir>${project.build.directory}/endorsed</endorsed.dir>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>

<dependencyManagement>
    <dependencies>
        <dependency>
            <groupId>com.google.cloud</groupId>
            <artifactId>libraries-bom</artifactId>
            <version>26.30.0</version>
            <type>pom</type>
            <scope>import</scope>
        </dependency>
    </dependencies>
</dependencyManagement>
<dependencies>

    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>org.eclipse.persistence.core</artifactId>
        <version>4.0.0</version>
    </dependency>

    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>eclipselink</artifactId>
        <version>4.0.0</version>
    </dependency>

    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>org.eclipse.persistence.jpa</artifactId>
        <version>4.0.0</version>
    </dependency>

    <dependency>
        <groupId>org.eclipse.persistence</groupId>
        <artifactId>org.eclipse.persistence.json</artifactId>
        <version>4.0.0</version>
    </dependency>

    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-core</artifactId>
        <version>2.16.1</version>
    </dependency>

    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.16.1</version>
    </dependency>


    <dependency>
        <groupId>com.google.appengine</groupId>
        <artifactId>appengine-api-1.0-sdk</artifactId>
        <version>2.0.12</version>
        <type>jar</type>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
        <type>jar</type>
    </dependency>
    <dependency>
        <groupId>com.sendgrid</groupId>
        <artifactId>java-http-client</artifactId>
        <version>4.3.6</version>
        <type>jar</type>
    </dependency>
    <!-- https://mvnrepository.com/artifact/jakarta.platform/jakarta.jakartaee-api -->
    <dependency>
        <groupId>jakarta.platform</groupId>
        <artifactId>jakarta.jakartaee-api</artifactId>
        <version>10.0.0</version>
    </dependency>

    <dependency>
        <groupId>org.glassfish.jersey.containers</groupId>
        <artifactId>jersey-container-servlet</artifactId>
        <version>3.1.5</version> <!-- Use the latest version available -->
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.glassfish.jersey.media/jersey-media-json-jackson -->
    <dependency>
        <groupId>org.glassfish.jersey.media</groupId>
        <artifactId>jersey-media-json-jackson</artifactId>
        <version>3.1.5</version>
    </dependency>

    <dependency>
        <groupId>org.glassfish.jersey.inject</groupId>
        <artifactId>jersey-hk2</artifactId>
        <version>3.1.5</version> <!-- Use the latest version available -->
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.3.0</version>
    </dependency>

    <dependency>
        <groupId>org.json</groupId>
        <artifactId>json</artifactId>
        <version>20231013</version>
    </dependency>


    <dependency> 
        <groupId>com.google.code.gson</groupId>
        <artifactId>gson</artifactId>
        <version>2.8.5</version>
    </dependency>
    
        
    <dependency>
        <groupId>com.google.cloud</groupId>
        <artifactId>google-cloud-core</artifactId>
    </dependency>
    
    <!-- https://mvnrepository.com/artifact/com.sendgrid/sendgrid-java -->
    <dependency>
        <groupId>com.sendgrid</groupId>
        <artifactId>sendgrid-java</artifactId>
        <version>4.7.2</version>
        <type>jar</type>
    </dependency>
    
    <dependency>
        <groupId>com.google.cloud</groupId>
        <artifactId>google-cloud-storage</artifactId>
    </dependency>

    <dependency>
        <groupId>com.google.cloud</groupId>
        <artifactId>google-cloud-tasks</artifactId>
    </dependency>


    <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-csv -->
    <dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-csv</artifactId>
        <version>1.9.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.google.cloud.sql/mysql-socket-factory-connector-j-8 -->
    <dependency>
        <groupId>com.google.cloud.sql</groupId>
        <artifactId>mysql-socket-factory-connector-j-8</artifactId>
        <version>1.17.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.30</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/jakarta.servlet.jsp.jstl/jakarta.servlet.jsp.jstl-api -->
    <!-- https://mvnrepository.com/artifact/javax.servlet/jstl -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>jstl</artifactId>
        <version>1.2</version>
    </dependency>





</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>com.google.cloud.tools</groupId>
            <artifactId>appengine-maven-plugin</artifactId>
            <version>2.2.0</version>
            <configuration>                    
                <promote>false</promote>
                 

                <projectId>XXXXX</projectId>
                <version>9</version>
         <!--                                     
               <projectId>XXXXX</projectId>
                <version>9</version>                            
                               -->              

               

            </configuration>
        </plugin>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-compiler-plugin</artifactId>
            <version>3.8.1</version>
            <configuration>
                <source>17</source>
                <target>17</target>
                <compilerArguments>
                    <endorseddirs>${endorsed.dir}</endorseddirs>
                </compilerArguments>
            </configuration>
        </plugin>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-war-plugin</artifactId>
            <version>3.3.2</version>
            <configuration>
                <failOnMissingWebXml>false</failOnMissingWebXml>
            </configuration>
        </plugin>

    </plugins>
</build>

this is how i set up my appengine-web.xml

    <?xml version="1.0" encoding="utf-8"?>
    <appengine-web-app xmlns="http://appengine.google.com/ns/1.0">
<runtime>java21</runtime>
<app-engine-apis>true</app-engine-apis>
<sessions-enabled>true</sessions-enabled>
<staging>
    <enable-jar-classes>true</enable-jar-classes>
</staging>
<instance-class>F2</instance-class>

I can creating an entityManagerFactory when my application starts up here

         package com.figjaminc.startup;

      import com.google.cloud.ServiceOptions;
      import jakarta.persistence.Persistence;
      import jakarta.servlet.ServletContextEvent;
      import jakarta.servlet.ServletContextListener;
      import java.util.HashMap;
      import java.util.Map;
      import java.util.logging.Logger;

     public class WebAppStartStop implements ServletContextListener {

private static final String class_name = "WebAppStartStop";

private static final Logger LOGGER = Logger.getLogger(WebAppStartStop.class.getName());



@Override
public void contextInitialized(ServletContextEvent servletContextEvent) {

    String startupReport = "";

    startupReport += initializeEntityManager();
    LOGGER.info("WebAppStartStop startup_report:" + startupReport);
}

private String initializeEntityManager() {
    String projectId = ServiceOptions.getDefaultProjectId();

    Map<String, String> persistenceMap = new HashMap<String, String>();

    //set sandbox credentials
    if (projectId.equals("xxx-sandbox")) {
        persistenceMap.put("jakarta.persistence.jdbc.url", "jdbc:mysql://google/xxxdb?cloudSqlInstance=xxx:us-central1:xxxdb3&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false");
        persistenceMap.put("jakarta.persistence.jdbc.user", "xxxuser");
        persistenceMap.put("jakarta.persistence.jdbc.driver", "com.mysql.cj.jdbc.Driver");

    }//if
    else if (projectId.equals("xxx")) {//set live credentials
        persistenceMap.put("jakarta.persistence.jdbc.url", "jdbc:mysql://google/xxxlivedb?cloudSqlInstance=xxx:us-central1:xxxdblive&socketFactory=com.google.cloud.sql.mysql.SocketFactory&useSSL=false");//changed for new demo
        persistenceMap.put("jakarta.persistence.jdbc.user", "xxxliveuser");
        persistenceMap.put("jakarta.persistence.jdbc.driver", "com.mysql.jdbc.Driver");
    }//else
    else {
        persistenceMap.put("jakarta.persistence.jdbc.url", "jdbc:mysql://localhost:3306/xxxdb?zeroDateTimeBehavior=CONVERT_TO_NULL");
        persistenceMap.put("jakarta.persistence.jdbc.user", System.getenv("db_username"));
        persistenceMap.put("jakarta.persistence.jdbc.password", System.getenv("db_password"));
        persistenceMap.put("jakarta.persistence.jdbc.driver", "com.mysql.cj.jdbc.Driver");
    }//else

    CommonDbMethods.factory = Persistence.createEntityManagerFactory("com.xxx_xxx_war_1.0PU", persistenceMap);
    return "Entity Manager initialized";
}

@Override
public void contextDestroyed(ServletContextEvent servletContextEvent) {

    LOGGER.info("Shutting Down");
}
   }

• A scenario I tried which works is removing the app-engine-apis from appengine-web.xml and my application connects. but I need that becauses sessions are not working without it.
• With the current configuration that I set up the application does not connect to the database.

Answer 1

UPDATE:

1. Ensure app-engine-apis is set to true in appengine-web.xml.

2. Verify your Cloud SQL instance has a public IP (likely default).

3. Double-check connection details (URL, username, password) in your code.

4. Keep the mysql-socket-factory-connector-j-8 dependency in pom.xml.

If connection fails, check App Engine logs and try disabling app-engine-apis temporarily to isolate conflicts.

• Connecting from App Engine standard environment

  • The various connection options (including Cloud SQL Proxy) that you can use depending on your App Engine configuration (standard or flexible) and Cloud SQL instance setup (public IP or private IP) are covered in this guide.

• Java runtime environment

  • The Java runtime environment for the App Engine standard is described on this page, along with the supported Java versions and restrictions. Although it doesn't cover Cloud SQL connection specifically, it gives you background information on the environment you're working in.

While the App Engine standard with public IP Cloud SQL may not absolutely require the Cloud SQL Proxy, these documents provide a better understanding of the connection options and factors to take into account for various scenarios.