Question 112986 in Mysql Question posted in
The official MySQL documentation can be found here.

JDBCTemplate : how to fetch values of MySQL variables

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 ?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search