Query database related variables in JDO

Question

I need to get some database related variables like gtid_executed, it's convenient to get this value from mysql via query statement like below:

show global variables like "gtid_executed";

the result in mysql:

mysql> show global variables like "gtid_executed";
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| gtid_executed | 2270cb64-37a1-11ec-a47a-0242ac110002:1-190 |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)

I want do this query from JDO likes below:

PersistenceManagerFactory pmf = JDOHelper.getPersistenceManagerFactory("Tutorial");

PersistenceManager pm = pmf.getPersistenceManager();

String queryText = "show global variables like gtid_executed";
Query query = pm.newQuery("javax.jdo.query.SQL", queryText);
Object res = query.execute();

But this exception occured:

Exception in thread "main" javax.jdo.JDOUserException: 
You have specified an SQL statement ("show global variables like gtid_executed") that doesnt start with SELECT. 
This is invalid.

How can I do query like show variables *** in JDO gracefully? Or any other good methods in replace is appreciated!

Answer 1

Finally I found the workaround:

• First method is set datanucleus.query.sql.allowAll as true for persistence-unit properties:

<property name="datanucleus.query.sql.allowAll" value="true"/>

• Second method is use jdbc query:

// use native jdbc for query not begin with select
Connection conn = (Connection) pm.getDataStoreConnection().getNativeConnection();
String queryText = "show global variables like \"gtid_executed\"";
Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(queryText);