Datasource with native queries

Support
#1

Hello,

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

#2

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:

    @Service(CustomerService.NAME)
public class CustomerServiceBean implements CustomerService {

    @Inject
    private Persistence persistence;

    @Override
    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 {

    @Override
    protected Collection<KeyValueEntity> getEntities(Map<String, Object> params) {
        CustomerService customerService = AppBeans.get(CustomerService.class);
        List<Map<String, Object>> rows = customerService.loadCustomers();
        return rows.stream()
                .map(row -> {
                    KeyValueEntity entity = new KeyValueEntity();
                    entity.setValue("name", row.get("name"));
                    entity.setValue("email", row.get("email"));
                    return entity;
                })
                .collect(Collectors.toList());
    }
}

  • In your screen XML, define the datasource and its properties:

    <dsContext>
    <valueCollectionDatasource id="customDs"
                               datasourceClass="com.company.demo.web.customer.CustomersDatasource">
        <properties>
            <property datatype="string"
                      name="name"/>
            <property datatype="string"
                      name="email"/>
        </properties>
    </valueCollectionDatasource>
</dsContext>

    Use this datasource in a table.

The whole project is attached.
demo-sql.zip (81.3 KB)

Regards,
Konstantin

1 Like