Mar 23, 2021
My Journey with DB Connector in Mendix / PostgreSQL
Mendix makes it easier to create and maintain databases with entities, attributes and associations.
Just need to go to the domain Model and create entities there. When creating attributes, there are predefined data types that help to create attributes of the specific data type. Then comes the relationships between two or more tables/entities, no need to worry about those either, there are options to create relations such as Associations, Generalizations or Specializations. There are two types of entities :
1) Persistent entity that stores the data in an application database 2) Non-Persistent entity that stores the data temporarily and only exists in run time memory.
External databases supported by Mendix
Starting with external databases in Mendix, the Database Connector can be used to seamlessly connect to external databases without limiting you in your choice of a database or SQL dialect, enabling you to incorporate your external data directly in your Mendix application.
Usage of Database Connector with PostgreSQL:
Database Connector helps to give direct access to External SQL Databases and supports queries as well as actions like an update, delete, stored procedure calls. “Database Connector” available in Mendix Market Place can be used to run SQL actions.
The list of Databases that Mendix supports is Amazon Redshift, Apache Derby, Firebird, H2 Database Engine, HSQLDB, IBM DB2, IBM Informix, MariaDB, Microsoft SQL/SQL Databases, MySQL, OracleDB, OrientDB, PostgreSQL, Presto and SQLite.
Prerequisites
· Install PostgreSQL dependency from Here
· HikariCP
Configuration:
- Download “Database Connector” from Mendix Market Place into your project.
2. Create default values (Constants) for the configuration of URL, Username and Password. Red colour rectangle shows the constant.
· DB_URL — Inside the Db_Url Constant give default values.
E.g.: “jdbc: postgresql://localhost/Info”.
· Db_Username: Inside the Db_User give a default value for username.
· Db_Password : Inside the Db_Password give a default value for password.
3. Go to Settings under the root directory and in configuration give the URL, Username and Password.
Create entities and attributes the same as created in the PostgreSQL database.
4. Create a Microflow: To create the microflow, add an action to create an entity and select the entity which is created in the domain model. Then drag Execute Query from the Database connector toolbox as shown in the picture.
5. Execute Query : For JDBC URL, Username, password click on edit and select the constants as shown below
For SQL click on edit and write query inside the quotes “Query”.
And at the end set $ReturnValue for the microflow at the end event.
Now go to domain model, right click on entity and create overview pages as shown in picture.
And select entity DB_Data.
Open navigation and add new and give caption and select icon. In on click action select Show a page from dropdown and select created DB_Data_Overview page.
Open PgAdmin(PostrgreSql admin) do all the configurations which should be same as given in the mendix configuration.
From Mendix studio pro compile and run the project and click on the data in the navbar, You’ll see all the data present in the database. As shown in the picture.
Data from mendix database(domain model).
Data from PostgreSQL.
Now Adding new data to the database from Mendix database, click on new from overview page.
The same data will be reflected in both of the databases.
Mendix data after adding new data:
Mendix PgAdmin Data after adding new values:
Query Statements:
- Execute Query: It performs SELECT SQL query on a relational external database. JDBC API is used when the java action attempts to connect with a relational database for which the JDBC drivers exist.
- Execute Statement: Execute Statement works internally the same as Execute action Used for INSERT, UPDATE, DELETE and STORED PROCEDURE OR DDL Statement.
- Execute query & Execute parameterized query mostly used for SELECT SQL command, returns a list of objects of row type.
- Execute Statement & Execute Parameterized statement are used for INSERT, UPDATE & DELETE. Returns integer/long value usually represents the amount of affected rows.
Oracle Databases: For Oracle Database, “Oracle Connector” is available in Mendix Market Place. So this supports oracle specific features like PLSQL.
Dependencies:
According to the type of Database dependencies need to add in the Project Structure (inside the Userlib)
1) HikariCP
2) A High Performance (JDBC connection pool)
Common JDBC Drivers:
Microsoft SQL Server/SQL Database
Read more
From the publisher -
If you enjoyed this article you can find more like it at our medium page or at our own community blog site.
For the makers looking to get started, you can sign up for a Free account, and get instant access to learning with our academy.
Interested in getting more involved with our community? You can join us in our slack community channel or for those who want to be more involved, look into joining one of our meet-ups.
