En esta guía se amplía el fragmento de código utilizado en Gestión de datos enviados por los usuarios almacenando y recuperando datos con Google Cloud SQL.
Cloud SQL es una opción de almacenamiento disponible en App Engine que se puede integrar fácilmente en las aplicaciones y almacenar datos de texto relacionales. Compara Cloud SQL, Cloud Datastore y Cloud Storage y elige el que mejor se adapte a los requisitos de tu aplicación.
Este ejemplo se basa en una serie de guías y muestra cómo almacenar, actualizar y eliminar datos de entradas de blog en Cloud SQL.
Antes de empezar
Configura tu entorno de desarrollo y crea tu proyecto de App Engine.
Crear una instancia de Cloud SQL y conectarse a la base de datos
Deberás crear una instancia de Cloud SQL y configurar una conexión a ella desde tu aplicación de App Engine. Para obtener instrucciones sobre cómo conectarte a Cloud SQL, consulta el artículo Conectarse a App Engine.
Crear tablas
Debes crear un objeto Connection en el método init() del servlet para gestionar la conexión a la instancia de Cloud SQL:
Connection conn; // Cloud SQL connection
// Cloud SQL table creation commands
final String createContentTableSql =
"CREATE TABLE IF NOT EXISTS posts ( post_id INT NOT NULL "
+ "AUTO_INCREMENT, author_id INT NOT NULL, timestamp DATETIME NOT NULL, "
+ "title VARCHAR(256) NOT NULL, "
+ "body VARCHAR(1337) NOT NULL, PRIMARY KEY (post_id) )";
final String createUserTableSql =
"CREATE TABLE IF NOT EXISTS users ( user_id INT NOT NULL "
+ "AUTO_INCREMENT, user_fullname VARCHAR(64) NOT NULL, "
+ "PRIMARY KEY (user_id) )";
@Override
public void init() throws ServletException {
try {
String url = System.getProperty("cloudsql");
try {
conn = DriverManager.getConnection(url);
// Create the tables so that the SELECT query doesn't throw an exception
// if the user visits the page before any posts have been added
conn.createStatement().executeUpdate(createContentTableSql); // create content table
conn.createStatement().executeUpdate(createUserTableSql); // create user table
// Create a test user
conn.createStatement().executeUpdate(createTestUserSql);
} catch (SQLException e) {
throw new ServletException("Unable to connect to SQL server", e);
}
} finally {
// Nothing really to do here.
}
}
El método init() configura una conexión a Cloud SQL y, a continuación, crea las tablas content y user si no existen. Después de usar el método init(), la aplicación estará lista para ofrecer y almacenar datos nuevos.
En el fragmento, las instrucciones SQL de creación de tablas se almacenan en String
variables, que se ejecutan en el servlet init() mediante la llamada al método executeUpdate. Ten en cuenta que no se crearán esas tablas si ya existen.
Las dos tablas creadas en el fragmento se llaman posts y users. posts
contiene los detalles de cada entrada del blog, mientras que users incluye información sobre el
autor, como se muestra a continuación:
Tabla: posts
| Campo | Tipo |
|---|---|
| post_id | INT (incremento automático, clave principal) |
| author_id | INT |
| timestamp | DATETIME |
| title | VARCHAR (256) |
| cuerpo | VARCHAR (1337) |
Tabla: usuarios
| Campo | Tipo |
|---|---|
| user_id | INT (incremento automático, clave principal) |
| user_fullname | VARCHAR (64) |
Obtener los datos iniciales que se mostrarán en un formulario
Un caso práctico habitual es rellenar previamente un formulario con datos almacenados en la base de datos para que los usuarios puedan seleccionarlos. Por ejemplo:
Connection conn;
final String getUserId = "SELECT user_id, user_fullname FROM users";
Map<Integer, String> users = new HashMap<Integer, String>();
@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// Find the user ID from the full name
try (ResultSet rs = conn.prepareStatement(getUserId).executeQuery()) {
while (rs.next()) {
users.put(rs.getInt("user_id"), rs.getString("user_fullname"));
}
req.setAttribute("users", users);
req.getRequestDispatcher("/form.jsp").forward(req, resp);
} catch (SQLException e) {
throw new ServletException("SQL error", e);
}
}
En el fragmento de código anterior, el servlet consulta la base de datos de Cloud SQL para obtener una lista de IDs de usuario y nombres de autores. Se almacenan como tuplas (id, full
name) en un mapa hash. A continuación, el servlet reenvía el usuario y el mapa hash a /form.jsp, que procesa el mapa hash de los nombres de los autores, tal como se muestra en la siguiente sección.
Admitir interacciones con bases de datos en un formulario
El siguiente fragmento usa JavaServer Pages (JSP) para mostrar al usuario los datos iniciales del mapa de hash del nombre del autor que se ha transferido desde el servlet y usa esos datos en una lista de selección. El formulario también permite al usuario crear y actualizar datos.
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/functions" prefix="fn"%>
<div>
<c:choose>
<c:when test="${id == null}">
<h2>Create a new blog post</h2>
<form method="POST" action="/create">
</c:when>
<c:otherwise>
<h2><c:out value="${pagetitle}" /></h2>
<form method="POST" action="/update">
<input type="hidden" name="blogContent_id" value="${id}">
</c:otherwise>
</c:choose>
<div>
<label for="title">Title</label>
<input type="text" name="blogContent_title" id="title" size="40" value="${title}" />
</div>
<div>
<label for="author">Author</label>
<select name="blogContent_id">
<c:forEach items="${users}" var="user">
<option value="${user.key}">${user.value}</option>
</c:forEach>
</select>
<input type="text" name="blogContent_author" id="author" size="40" value="${author}" />
</div>
<div>
<label for="description">Post content</label>
<textarea name="blogContent_description" id="description" rows="10" cols="50">${body}</textarea>
</div>
<button type="submit">Save</button>
</form>
</div>
En el fragmento de código anterior, el formulario se rellena cuando se carga la página con el mapa hash de los nombres de los autores que se han transferido desde el servlet. El formulario usa la biblioteca de etiquetas estándar de JavaServer Pages (JSTL) when y las operaciones otherwise proporcionan la lógica if..else y los bucles forEach a través del mapa hash transferido desde el servlet.
La página JSP del fragmento de código anterior contiene un formulario para crear entradas de blog y actualizar las que ya existen. Ten en cuenta que el formulario puede enviar los datos a los controladores /create o /update, según si el usuario está creando o actualizando una entrada de blog.
Para obtener más información sobre cómo usar formularios, consulta Gestionar datos POST.
Almacenar registros
En el siguiente fragmento se muestra cómo crear un registro a partir de los datos que el usuario ha proporcionado en el formulario y almacenarlo en la base de datos. En el ejemplo se muestra una instrucción SQL INSERT
creada a partir de los datos enviados en el formulario de creación de la entrada de blog
descrito en la sección anterior:
// Post creation query
final String createPostSql =
"INSERT INTO posts (author_id, timestamp, title, body) VALUES (?, ?, ?, ?)";
@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// Create a map of the httpParameters that we want and run it through jSoup
Map<String, String> blogContent =
req.getParameterMap()
.entrySet()
.stream()
.filter(a -> a.getKey().startsWith("blogContent_"))
.collect(
Collectors.toMap(
p -> p.getKey(), p -> Jsoup.clean(p.getValue()[0], Whitelist.basic())));
// Build the SQL command to insert the blog post into the database
try (PreparedStatement statementCreatePost = conn.prepareStatement(createPostSql)) {
// set the author to the user ID from the user table
statementCreatePost.setInt(1, Integer.parseInt(blogContent.get("blogContent_id")));
statementCreatePost.setTimestamp(2, new Timestamp(new Date().getTime()));
statementCreatePost.setString(3, blogContent.get("blogContent_title"));
statementCreatePost.setString(4, blogContent.get("blogContent_description"));
statementCreatePost.executeUpdate();
conn.close(); // close the connection to the Cloud SQL server
// Send the user to the confirmation page with personalised confirmation text
String confirmation = "Post with title " + blogContent.get("blogContent_title") + " created.";
req.setAttribute("confirmation", confirmation);
req.getRequestDispatcher("/confirm.jsp").forward(req, resp);
} catch (SQLException e) {
throw new ServletException("SQL error when creating post", e);
}
}
El fragmento de código toma la entrada del usuario y la ejecuta a través de jSoup para sanearla. Usar jSoup y PreparedStatement
mitigar la posibilidad de ataques de XSS y de inyección de SQL.
La variable createPostSql contiene la consulta INSERT con ? como marcadores de posición de los valores que se asignarán mediante el método PreparedStatement.set().
Ten en cuenta el orden de los campos de la tabla, ya que se hace referencia a ellos en los métodos PreparedStatement. Por ejemplo, author_id es un campo de tipo INT, por lo que debe usarse setInt() para definir author_id.
Recuperar registros
En el siguiente fragmento se muestra el método doGet() de un servlet que obtiene las filas de la tabla de entradas de blog y las imprime.
// Preformatted HTML
String headers =
"<!DOCTYPE html><meta charset=\"utf-8\"><h1>Welcome to the App Engine Blog</h1><h3><a href=\"blogpost\">Add a new post</a></h3>";
String blogPostDisplayFormat =
"<h2> %s </h2> Posted at: %s by %s [<a href=\"/update?id=%s\">update</a>] | [<a href=\"/delete?id=%s\">delete</a>]<br><br> %s <br><br>";
@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// Retrieve blog posts from Cloud SQL database and display them
PrintWriter out = resp.getWriter();
out.println(headers); // Print HTML headers
try (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) {
Map<Integer, Map<String, String>> storedPosts = new HashMap<>();
while (rs.next()) {
Map<String, String> blogPostContents = new HashMap<>();
// Store the particulars for a blog in a map
blogPostContents.put("author", rs.getString("users.user_fullname"));
blogPostContents.put("title", rs.getString("posts.title"));
blogPostContents.put("body", rs.getString("posts.body"));
blogPostContents.put("publishTime", rs.getString("posts.timestamp"));
// Store the post in a map with key of the postId
storedPosts.put(rs.getInt("posts.post_id"), blogPostContents);
}
// Iterate the map and display each record's contents on screen
storedPosts.forEach(
(k, v) -> {
// Encode the ID into a websafe string
String encodedID = Base64.getUrlEncoder().encodeToString(String.valueOf(k).getBytes());
// Build up string with values from Cloud SQL
String recordOutput =
String.format(blogPostDisplayFormat, v.get("title"), v.get("publishTime"),
v.get("author"), encodedID, encodedID, v.get("body"));
out.println(recordOutput); // print out the HTML
});
} catch (SQLException e) {
throw new ServletException("SQL error", e);
}
}
Los resultados de la instrucción SELECT se colocan en un ResultSet, que se
itera mediante el método ResultSet.get(). Ten en cuenta el método ResultSet.get()
getString que corresponde al esquema de tabla definido anteriormente.
En este ejemplo, cada publicación tiene un enlace [Update] y un enlace [Delete], que se usan para iniciar actualizaciones y eliminaciones de publicaciones, respectivamente. Para ofuscar el ID de la publicación, el identificador se codifica en Base64.
Actualizar registros
En el siguiente fragmento se muestra cómo actualizar un registro:
final String updateSql = "UPDATE posts SET title = ?, body = ? WHERE post_id = ?";
@Override
public void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// Create a map of the httpParameters that we want and run it through jSoup
Map<String, String> blogContent =
req.getParameterMap()
.entrySet()
.stream()
.filter(a -> a.getKey().startsWith("blogContent_"))
.collect(
Collectors.toMap(
p -> p.getKey(), p -> Jsoup.clean(p.getValue()[0], Whitelist.basic())));
// Build up the PreparedStatement
try (PreparedStatement statementUpdatePost = conn.prepareStatement(updateSql)) {
statementUpdatePost.setString(1, blogContent.get("blogContent_title"));
statementUpdatePost.setString(2, blogContent.get("blogContent_description"));
statementUpdatePost.setString(3, blogContent.get("blogContent_id"));
statementUpdatePost.executeUpdate(); // Execute update query
conn.close();
// Confirmation string
final String confirmation = "Blog post " + blogContent.get("blogContent_id") + " has been updated";
req.setAttribute("confirmation", confirmation);
req.getRequestDispatcher("/confirm.jsp").forward(req, resp);
} catch (SQLException e) {
throw new ServletException("SQL error", e);
}
}
En este fragmento, cuando el usuario hace clic en el enlace [Update] (Actualizar) de una entrada de blog, se muestra el formulario JSP que se usa para crear una entrada nueva, pero ahora se rellena automáticamente con el título y el contenido de la entrada. El nombre del autor no se muestra en la muestra porque no va a cambiar.
Actualizar una publicación es similar a crearla, excepto que se usa la consulta SQL UPDATE
en lugar de INSERT.
Después de ejecutar executeUpdate(), se redirige al usuario a una página de confirmación
en el fragmento.
Eliminar registros
Para eliminar una fila (en este ejemplo, una entrada de blog), es necesario eliminar una fila de la tabla de destino, que en este caso es la tabla content. Cada registro se identifica por su ID, que es el valor post_id del código de ejemplo. Usa este ID como filtro en la consulta DELETE:
Después de ejecutar executeUpdate(), se redirige al usuario a una página de confirmación.
final String deleteSql = "DELETE FROM posts WHERE post_id = ?";
@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
Map<String, String[]> userData = req.getParameterMap();
String[] postId = userData.get("id");
String decodedId = new String(Base64.getUrlDecoder().decode(postId[0])); // Decode the websafe ID
try (PreparedStatement statementDeletePost = conn.prepareStatement(deleteSql)) {
statementDeletePost.setString(1, postId[0]);
statementDeletePost.executeUpdate();
final String confirmation = "Post ID " + postId[0] + " has been deleted.";
req.setAttribute("confirmation", confirmation);
req.getRequestDispatcher("/confirm.jsp").forward(req, resp);
} catch (SQLException e) {
throw new ServletException("SQL error", e);
}
}
Después de decodificar el ID de la publicación, el fragmento eliminará una sola publicación de la tabla posts.
Desplegar en App Engine
Puedes desplegar tu aplicación en App Engine con Maven.
Ve al directorio raíz de tu proyecto y escribe lo siguiente:
mvn package appengine:deploy -Dapp.deploy.projectId=PROJECT_ID
Sustituye PROJECT_ID por el ID de tu Google Cloud proyecto. Si tu archivo pom.xml ya especifica tu ID de proyecto, no es necesario que incluyas la propiedad -Dapp.deploy.projectId en el comando que ejecutes.
Una vez que Maven haya desplegado tu aplicación, se abrirá automáticamente una pestaña del navegador web con tu nueva aplicación. Para ello, escribe lo siguiente:
gcloud app browse
Siguientes pasos
Cloud SQL es útil para almacenar datos basados en texto. Sin embargo, si quieres almacenar contenido multimedia enriquecido, como imágenes, te recomendamos que uses Cloud Storage.
A continuación, consulta cómo usar las colas de tareas para realizar tareas asíncronas. Para ello, sigue un ejemplo de uso de la API Images para cambiar el tamaño de las imágenes subidas en esta guía.