Is there any way to run 'SET foreign_key_checks = 0 ' using JDBC?

Issue

I am using Java 11 JDBC and MySQL Connector/J 8 jar. All other CRUDs are running OK, but when I am trying to run SET foreign_key_checks = 0 or SET foreign_key_checks = 1, it shows syntax error with SQLState: 42000 and VendorError: 1064. So if there is any way to run such queries using JDBC?

try {
    Statement checks = connection.createStatement();
    checks.execute("set foreign_key_checks=0");
    checks.close();

    String tableName = json.getString("table");
    RowOperation rowOperation = RowOperation.valueOf(json.getString("activity"));
    JSONArray rows = json.getJSONArray("rows");

    for (Object obj : rows) {
        JSONObject row = (JSONObject) obj;
        List<String> keys = new ArrayList<>(row.keySet());
        StringBuilder sb = new StringBuilder();
        String columns = keys.stream().collect(Collectors.joining(",")) + ",rowOperation,rowCreatedOn";
        String questionMakrs = keys.stream().map(x -> "?").collect(Collectors.joining(",")) + ",?,?";
        String query = "insert into " + tableName + " (" + columns + ") values (" + questionMakrs + "); SET FOREIGN_KEY_CHECKS=0";
        PreparedStatement stmt = connection.prepareStatement(query);
        int i = 0;
        for (i = 0; i < keys.size(); i++) {
            String key = keys.get(i);
            stmt.setString(i + 1, String.valueOf(row.get(key)));
        }
        stmt.setString(i + 1, rowOperation.name());
        i++;
        stmt.setTimestamp(i + 1, Timestamp.valueOf(LocalDateTime.now()));
        stmt.execute();
        stmt.close();
    }

} finally {
    Statement checks = connection.createStatement();
    checks.execute("set foreign_key_checks=1");
    checks.close();
}

Solution

You are trying to execute two statements, an INSERT followed by a SET, separated by a semicolon, in a single call to prepareStatement().

https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html

SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

You must execute the SET statement in a separate statement.

Answered By – Bill Karwin

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0