Establishing a JDBC Connection

To interact with the database, initialize a connection object using the JDBC driver manager. Ensure the connection string specifies the correct host, port, and schema.

String connectionString = "jdbc:mysql://localhost:3306/inventory_db";
String dbUser = "app_user";
String dbPass = "secure_pass";

try (Connection dbLink = DriverManager.getConnection(connectionString, dbUser, dbPass)) {
    // Database operations are performed here
} catch (SQLException ex) {
    System.err.println("Connection error: " + ex.getMessage());
}

Executing SQL Queries for JSON Columns

Use a PreparedStatement to execute the query safely. When selecting a column defined as JSON in MySQL, retrieve it as a string using the getString method of the ResultSet.

String fetchQuery = "SELECT product_id, metadata FROM products WHERE status = ?";

try (PreparedStatement pStmt = dbLink.prepareStatement(fetchQuery)) {
    pStmt.setString(1, "active");
    ResultSet rs = pStmt.executeQuery();
    
    while (rs.next()) {
        String String = rs.getString("metadata");
        // Further processing of String is handled below
    }
} catch (SQLException ex) {
    ex.printStackTrace();
}

Parsing JSON Strings in Java

Once the raw string is extracted, convert it into a manipulatable object. The following example demonstrates parsing the string into a JSONObject using the org. library.

import org..JSONObject;
import org..JSONException;

try {
    JSONObject parsedData = new JSONObject(String);
    String value = parsedData.getString("key_name");
    System.out.println("Retrieved value: " + value);
} catch (JSONException e) {
    System.err.println("JSON parsing failed: " + e.getMessage());
}