ylrz_saas_pharmacy/java/scripts/ApplyLobsterInstanceColumns.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/** Add missing lobster_workflow_instance columns for validation. */
public class ApplyLobsterInstanceColumns {
    private static final String URL = "jdbc:mysql://cq-cdb-8fjmemkb.sql.tencentcdb.com:27220/ylrz_saas"
            + "?useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true";
    private static final String USER = "root";
    private static final String PWD = "Ylrz_1q2w3e4r5t6y";

    public static void main(String[] args) throws Exception {
        String[][] cols = {
                {"binding_id", "ALTER TABLE lobster_workflow_instance ADD COLUMN binding_id bigint DEFAULT NULL AFTER workflow_id"},
                {"dept_id", "ALTER TABLE lobster_workflow_instance ADD COLUMN dept_id bigint DEFAULT NULL AFTER channel_type"},
                {"company_user_id", "ALTER TABLE lobster_workflow_instance ADD COLUMN company_user_id bigint DEFAULT NULL AFTER dept_id"},
                {"qw_user_id", "ALTER TABLE lobster_workflow_instance ADD COLUMN qw_user_id bigint DEFAULT NULL AFTER company_user_id"},
                {"wx_account_id", "ALTER TABLE lobster_workflow_instance ADD COLUMN wx_account_id bigint DEFAULT NULL AFTER qw_user_id"},
        };
        try (Connection c = DriverManager.getConnection(URL, USER, PWD);
             Statement st = c.createStatement()) {
            for (String[] col : cols) {
                if (columnExists(st, col[0])) {
                    System.out.println("skip " + col[0]);
                } else {
                    st.execute(col[1]);
                    System.out.println("added " + col[0]);
                }
            }
        }
    }

    private static boolean columnExists(Statement st, String col) throws Exception {
        try (ResultSet rs = st.executeQuery(
                "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='ylrz_saas'"
                        + " AND TABLE_NAME='lobster_workflow_instance' AND COLUMN_NAME='" + col + "'")) {
            rs.next();
            return rs.getInt(1) > 0;
        }
    }
}