如何在Google Apps Script中通过JDBC检索MySQL指定列的目标字符串

Got it, let's walk through how to implement this search functionality using Google Apps Script's JDBC service. I’ve tackled similar tasks before, so here’s a practical, step-by-step solution that should get you exactly what you need:

First, you’ll need to set up a connection to your MySQL database. Make sure your database server allows incoming connections from Google’s IP ranges (you can find these in Google’s internal docs if you’re using a remote host) or use Cloud SQL with the appropriate permissions if that’s your setup.

Here’s how to create the connection:

function getMySQLConnection() {
  const host = "your-database-host"; // e.g., "localhost" or a remote IP/domain
  const port = 3306; // default MySQL port
  const dbName = "your-database-name";
  const username = "your-db-username";
  const password = "your-db-password";

  const connectionString = `jdbc:mysql://${host}:${port}/${dbName}?useSSL=true`; // add useSSL if required by your server
  return Jdbc.getConnection(connectionString, username, password);
}

To avoid SQL injection (super important!) and search across the three columns, we’ll use a parameterized query with LIKE clauses. This lets us safely pass in our search string without risking malicious input.

The query will look for matches in name, description, or friends where the column contains your target string (using % as a wildcard for partial matches).

Now, let’s put it all together into a function that takes your search string, runs the query, and returns an array of matching rows. We’ll use try/catch/finally to ensure we clean up database resources properly:

function searchPersonTable(searchString) {
  let conn = null;
  let stmt = null;
  let rs = null;
  const results = [];

  try {
    // Get connection
    conn = getMySQLConnection();

    // Prepare parameterized query
    const query = `
      SELECT name, age, description, friends 
      FROM person 
      WHERE name LIKE ? 
         OR description LIKE ? 
         OR friends LIKE ?
    `;
    stmt = conn.prepareStatement(query);

    // Wrap search string with wildcards for partial matches
    const searchPattern = `%${searchString}%`;
    // Set parameters for each LIKE clause
    stmt.setString(1, searchPattern);
    stmt.setString(2, searchPattern);
    stmt.setString(3, searchPattern);

    // Execute query and get results
    rs = stmt.executeQuery();

    // Iterate through result set and build output array
    const cols = rs.getMetaData().getColumnCount();
    while (rs.next()) {
      const row = [];
      for (let i = 1; i <= cols; i++) {
        row.push(rs.getString(i));
      }
      results.push(row);
    }

  } catch (e) {
    console.error("Error searching person table:", e);
    throw e; // Re-throw if you want to handle errors upstream
  } finally {
    // Clean up resources to avoid leaks
    if (rs) rs.close();
    if (stmt) stmt.close();
    if (conn) conn.close();
  }

  return results;
}

• Return Object Instead of Array: If you prefer objects (easier to read with column names) instead of a 2D array, modify the row-building part:

  const row = {
    name: rs.getString("name"),
    age: rs.getInt("age"),
    description: rs.getString("description"),
    friends: rs.getString("friends")
  };
  results.push(row);
  

• Case Insensitivity: If you want the search to be case-insensitive, add COLLATE utf8mb4_general_ci to your query (adjust collation to match your table’s setup) or use LOWER() on both the column and search string:

  WHERE LOWER(name) LIKE LOWER(?) 
     OR LOWER(description) LIKE LOWER(?) 
     OR LOWER(friends) LIKE LOWER(?)
  

• Performance: For large tables, consider adding full-text indexes on the name, description, and friends columns—this will make your searches much faster than using LIKE with wildcards.

内容的提问来源于stack exchange，提问作者JSmith