I want to get the value of a MySQL variable (example: max_allowed_packet) via jdbcTemplate. is this possible? if so, how ?

In SQL, I can do SHOW VARIABLES LIKE 'max_allowed_packet'; but how to do this via JDBCTemplate ?

CodePudding user response：

Here is a solution

public List<Variable> findAllVariables() {
    List<Variable> result = jdbcTemplate.query("SHOW GLOBAL VARIABLES", new VariableRowMapper());
    //about 630 variables
    return result;
}

Variable class:

public class Variable {
    private String name;
    private String value;
    //getters and setters
}

VariableRowMapper class:

public class VariableRowMapper implements RowMapper<Variable> {
    @Override
    public Variable mapRow(ResultSet resultSet, int rowNum) throws SQLException {
        String name = resultSet.getString("Variable_Name");
        String value = resultSet.getString("Value");
        return new Variable(name, value);
    }
}

hope it helps.

CodePudding user response：

I was particularly interested in getting the max_allowed_packet variable from the database. This below snippet does the trick.

    private int fetchMaxAllowedPacketsFromDB(JdbcTemplate jdbcTemplate) {
        final String sql = "SELECT @@GLOBAL.max_allowed_packet";
        Integer maxAllowedPacketsFromDB = jdbcTemplate.queryForObject(sql, Integer.class);
        log.info("@@GLOBAL.max_allowed_packet : {}", maxAllowedPacketsFromDB);
        return maxAllowedPacketsFromDB;
    }

1. You can look at @Ali4j 's answer for a more generic/multi-variable requirement.
2. Or, You can refactor the snippet above to pass in a variable as argument, if you don't need the extra work of RowMappers