How to connect to the Mx data database using Java

I want to create a report using SQL and Java, how can I connect to the database preferably using the connection settings defined in the modeller. Can I get some code example how to create the connection and to execute a query?
2 answers

No, it is not possible to access the connection settings defined in the Modeler. It is recommended to use OQL instead of SQL. In OQL you can use domain model names. The names of the real tables and columns in the database are subject of change for successive Mendix versions. When it is not necessary, do not use SQL. The way to create connections, depends on the database type.

See the following code to create a connection with SQL Server 2008 and execute a command. Make sure the file sqljdbc4.jar (Microsoft SQL Server JDBC Driver) exists in your class path.

SQLServerDataSource dataSource = new SQLServerDataSource();
dataSource.setServerName(serverName); // name or IP addres of the database server
// dataSource.setPortNumber(portNumber); // only when necessary
// dataSource.setInstanceName(instanceName); // use only when the SQL Server database runs within a specific instance
// or dataSource.setUser(userName);
//    dataSource.setPassword(password);

Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT Name FROM Sales.Customer");

while( {
    // ...

Do not forget do close correctly the connection, also when the execution of the query fails.


Depending on your situation, an easier approach might be writing java code in the javasource or userlib directories of the modeler project, which is invoked by an scheduled microflow. This code can query using the existing XAS database connection using Xpath, OQL or programmatically, and then export the data in the desired format.