Datasource with native queries


I need to create a datasource that executes native queries. The only solution I see is to use an entityManager in the midleware and call it from the DataSource that resides in the Web module. To establish the communication between the web module and the midleware, the documentation indicates that it is done with the cuba.connectionUrlList property, but I can not establish the communication. Does anyone have any idea how I could get it?

I would really appreciate it if you could help me

Hi Ivan,

You don’t have to tinker with communication between tiers - it all works out-of-the-box. Follow these steps:

  • Create a middleware service and a method that loads data and returns it in some form. In my example, a list of maps representing table rows is returned:

    public class CustomerServiceBean implements CustomerService {
        private Persistence persistence;
        public List<Map<String, Object>> loadCustomers() {
            QueryRunner queryRunner = new QueryRunner(persistence.getDataSource());
            try {
                List<Map<String, Object>> rows = queryRunner.query(
                        "select name, email from demo_customer", new MapListHandler());
                return rows;
            } catch (SQLException e) {
                throw new RuntimeException(e);
  • Create a custom datasource on the web tier:

    public class CustomersDatasource extends CustomValueCollectionDatasource {
        protected Collection<KeyValueEntity> getEntities(Map<String, Object> params) {
            CustomerService customerService = AppBeans.get(CustomerService.class);
            List<Map<String, Object>> rows = customerService.loadCustomers();
                    .map(row -> {
                        KeyValueEntity entity = new KeyValueEntity();
                        entity.setValue("name", row.get("name"));
                        entity.setValue("email", row.get("email"));
                        return entity;
  • In your screen XML, define the datasource and its properties:

        <valueCollectionDatasource id="customDs"
                <property datatype="string"
                <property datatype="string"

    Use this datasource in a table.

The whole project is attached. (81.3 KB)


1 Like