使用Cloud SQL示例代码的Cloud Endpoints

I am looking to use Cloud Endpoints to create an API on the Google App Engine but all the tutorials I see are for how to interact with the Cloud Datastore. I am looking to use Cloud SQL though.

我希望使用Cloud Endpoints在Google App Engine上创建API，但我看到的所有教程都是关于如何与Cloud Datastore进行交互。我希望使用Cloud SQL。

Does anyone have a Cloud Endpoints class sample code (in Java) that shows how to connect to Cloud SQL and execute some simple INSERT and SELECT statements? e.g.

有没有人有一个Cloud Endpoints类示例代码（在Java中），它显示了如何连接到Cloud SQL并执行一些简单的INSERT和SELECT语句？例如

INSERT message INTO messages
SELECT * FROM messages

1 个解决方案

#1

Here is an example Cloud Endpoint class that updates Cloud SQL. The example assumes the end point is authenticated

以下是更新Cloud SQL的示例Cloud Endpoint类。该示例假定端点已通过身份验证

import java.io.IOException;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.google.api.server.spi.ServiceException;
import com.google.api.server.spi.config.Api;
import com.google.api.server.spi.config.ApiMethod;
import com.google.api.server.spi.config.Named;
import com.google.api.server.spi.response.ForbiddenException;
import com.google.api.server.spi.response.InternalServerErrorException;
import com.google.appengine.api.oauth.OAuthRequestException;
import com.google.appengine.api.users.User;

@Api(
    name = "myendpoint",
    version = "v1",
    clientIds = {"ddddd.apps.googleusercontent.com"},
    audiences = {"xxxx"}
)
public class CloudSqlEndpoint {

    private static final Logger log = Logger.getLogger(CloudSqlEndpoint.class.getName());

    private static final String INSERT_ORDER_SQL = "INSERT INTO order (amount, user, status, address) VALUES( ? , ? , ? , ? )"; 

/**
 * Insert a row into cloud sql
 * @param order
 * @param user
 * @return
 */
@ApiMethod(name = "order.add")
public Order addOrder(Order order, User user) throws OAuthRequestException, 
    IOException, ServiceException {

    this.validateUser(user);

    log.info("adding order to cloud sql: " + order);

    try {
         String url = null;
         if (SystemProperty.environment.value() ==
              SystemProperty.Environment.Value.Production) {
            // Load the class that provides the new "jdbc:google:mysql://" prefix.
            Class.forName("com.mysql.jdbc.GoogleDriver");
            url = "jdbc:google:mysql://your-project-id:your-instance-name/guestbook?user=root";
        } else {
            // Local MySQL instance to use during development.
            Class.forName("com.mysql.jdbc.Driver");
            url = "jdbc:mysql://127.0.0.1:3306/guestbook?user=root";

            // Alternatively, connect to a Google Cloud SQL instance using:
            // jdbc:mysql://ip-address-of-google-cloud-sql-instance:3306/guestbook?user=root
        }
        try(Connection conn = DriverManager.getConnection(url)) {

            PreparedStatement stmt = conn.prepareStatement(INSERT_ORDER_SQL);
            stmt.setString(1, order.getAmount());
            stmt.setString(2, order.getUser());
            stmt.setString(3, "Open");
            stmt.setString(4, order.getAddress());

            stmt.executeUpdate();

        }

    } catch(Exception e) {

        log.log(Level.SEVERE, "Failed to create order", e);
        throw new InternalServerErrorException(e);
    }

    return order;
}

/**
 * Validate the current user 
 * @param user
 */
private void validateUser(User user) throws OAuthRequestException, ServiceException {

    // validate the users domain
    if (user == null) {
        throw new OAuthRequestException("Invalid user.");

    } else {
        String email = user.getEmail().toLowerCase();

        // any other validation
    }

}

}