Google SQL Not Working On Google App Engine

Question

I wish to deploy an app to Google App Engine and allow user to access it using POST from the web. As I know that com.mysql.jdbc.GoogleDriver only work during the app is deployed at GAE. So, I try it every time after deploy to the GAE. Unfortunately, I always receive the error part of reply. I think this is the problem with my connection with the Google Cloud SQL.

<?xml version="1.0" encoding="utf-8"?>
<appengine-web-app xmlns="http://appengine.google.com/ns/1.0">

<!--
Allows App Engine to send multiple requests to one instance in parallel:
-->
<threadsafe>true</threadsafe>
<use-google-connector-j>true</use-google-connector-j>
<system-properties>
<property name="ae-cloudsql.cloudsql-database-url" value="jdbc:google:mysql://${INSTANCE_CONNECTION_NAME}/${database}?user=${user}&amp;password=${password}" />
</system-properties>

<!--
HTTP Sessions are disabled by default. To enable HTTP sessions specify:

  <sessions-enabled>true</sessions-enabled>

It's possible to reduce request latency by configuring your application to
asynchronously write HTTP session data to the datastore:

  <async-session-persistence enabled="true" />

With this feature enabled, there is a very small chance your app will see
stale session data. For details, see
https://cloud.google.com/appengine/docs/java/config/appconfig#Java_appengine_web_xml_Enabling_sessions
-->

</appengine-web-app>

Web.XML

@Consumes(MediaType.APPLICATION_JSON)
@Produces(MediaType.APPLICATION_JSON)
@POST
@Path("/v3")
public Response getSAppData(AppDataRequest adr) {
    Response data = ads.getSAppData(adr.getId(), adr.getEmail(), adr.getPassword());
    return data;
}

POST Code

public class AppDataService {
Map<AppDataRequest, Data> DataHM = new HashMap<>();

static final String JDBC_DRIVER = "com.mysql.jdbc.GoogleDriver";
static final String DB_URL = "jdbc:google:mysql://****:****/****?user=root";

static final String USER = "****";
static final String PASS = "****";

public AppDataService(){
    Connection conn = null;
    Statement stat = null;
    try{
        Class.forName("com.mysql.jdbc.GoogleDriver");
        conn = DriverManager.getConnection(DB_URL);
        stat = conn.createStatement();
        String sql = "SELECT * FROM testdata";
        ResultSet resu = stat.executeQuery(sql);
        while(resu.next()){
            int id = resu.getInt("app_id");
            String email = resu.getString("email");
            String password = resu.getString("password");
            String token = resu.getString("token");
            DataHM.put(new AppDataRequest(id, email, password), new Data(token));
        }
        resu.close();
        stat.close();
        conn.close();
    }
    catch(SQLException se){
        se.printStackTrace();
    }
    catch(Exception e){
        e.printStackTrace();
    }
    finally{
        try{
            if(stat!=null){
                stat.close();
            }
        }
        catch (SQLException se2){
            se2.printStackTrace();
        }
        try{
            if(conn!=null){
                conn.close();
            }
        }
        catch(SQLException se3){
            se3.printStackTrace();
        }
    }               
}

public Response getSAppData(int id, String email, String password){
    Map<String, AppData> AppDataHM = new HashMap<>(); 
    Map<String, Data> DataHM1 = new HashMap<>();
    Map<String, List<String>> DataHM2 = new HashMap<>();
    HashMap<Object, Object> ADHMDHM = new HashMap<>();

    List<String> message = new ArrayList<>();
    Data data = DataHM.get(new AppDataRequest (id, email, password));
    List<String> data2 = new ArrayList<>();

    if(data != null){
        message.add("");
        AppDataHM.put("AppData", new AppData("success", message));
        DataHM1.put("Data", data);
        ADHMDHM.putAll(AppDataHM);
        ADHMDHM.putAll(DataHM1);
        String ADHMDHM1 = new Gson().toJson(ADHMDHM);
        return Response.status(200).entity(ADHMDHM1).build();
    }
    else{
        message.add("Your login information is invalid. Please try with the correct information");
        AppDataHM.put("AppData", new AppData("error", message));
        DataHM2.put("Data", data2);
        ADHMDHM.putAll(AppDataHM);
        ADHMDHM.putAll(DataHM2);
        String ADHMDHM2 = new Gson().toJson(ADHMDHM);
        return Response.status(200).entity(ADHMDHM2).build();
        }   
    }
}

Code for accessing Google SQL

Jar Files

GAE Return Using POSTman

Thanks in advance for all users that help to answer this question.

Maybe you need to use `DriverManager.getConnection(String url, String user, String password)` instead. You are currently only supplying a user name in the URL (which is slightly weird), and no password. — Mark Rotteveel, Apr 26 '17 at 10:12
Hi. @MarkRotteveel Before I deploy my app to Google App Engine, `DriverManager.getConnection(String url, String user, String password)` will be working, but it will not be working after deploy and you can refer [here](http://stackoverflow.com/a/20173117/7772730) for more info. I supplying only a user name because I do not set password and just using the root account. — Reuben Tan, Apr 27 '17 at 00:44

score 1 · Accepted Answer · answered Apr 27 '17 at 03:11

Reuben. I feel that your code not working because you are using Instance ID instead of Instance Connection Name for your Google Cloud SQL URL. Your Web.XML indicate that you shoud be using ${INSTANCE_CONNECTION_NAME} from <property name="ae-cloudsql.cloudsql-database-url" value="jdbc:google:mysql://${INSTANCE_CONNECTION_NAME}/${database}?user=${user}&password=${password}" />

You can check on this Setting up the Cloud SQL instance.

Google SQL Not Working On Google App Engine

1 Answers1