Hazelcast Jet with Named Jdbc Query or ResultSet?

Question

I am new to Hazelcast Jet and I am using Spring JdbcTemplate to execute my query, which uses named parameters in the query, but I am not sure how to use it with Hazelcast Jet.

E.g. Hazelcast works in following way:

Pipeline p = Pipeline.create();
p.readFrom(Sources.jdbc(
    () -> DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql"),
    (con, parallelism, index) -> {
        PreparedStatement stmt = con.prepareStatement(
              "SELECT * FROM person WHERE MOD(id, ?) = ?)");
        stmt.setInt(1, parallelism);
        stmt.setInt(2, index);
        return stmt.executeQuery();
    },
    resultSet -> new Person(resultSet.getInt(1), resultSet.getString(2))
)).writeTo(Sinks.logger());

But instead of ? I want to use named query like SELECT * FROM person WHERE MOD(id, :id) = :id). Does Hazelcast support named query or Spring JdbcTemplate?

Also in Sources, can we pass ResultSet directly as a source? There are many sources but I didn't find any for "ResultSet".

E.g.

p.readFrom(Sources.resultSet(<Resultset Object>) //something like this

Please help me out on this, if possible.

Can you please clarify what do you mean by: "[...] can we pass Resultset directly as Sources?" ? What are you trying to achieve? — Jaromir Hamala, May 10 '21 at 07:04
In my application I am using Spring JdbcTemplate which is returning me Resultset and I want to do some filtration, aggregation and grouping on it using hazelcast jet pipeline, so how can I achieve it as Hazelcast jet directly do not support Resultset as source or Named Parameters. — user3458271, May 10 '21 at 09:51
There is no API for this. The main reason: The entire result set would have to be materialized in the memory of the instance which is submitting the Pipeline job. Normally ResultSet is connected to the underlying database. You can create a disconnected representation: It's called RowSet. But you would have to materialize the entire dataset in memory. This might work for toy-examples, but it's not really practical for any meaningful data sizes. — Jaromir Hamala, May 10 '21 at 14:28
What is your motivation for using the JdbcTemplate? Do you want/need to use the named parameters? Or a connection pool managed by Spring? We are currently looking how to improve our Spring integration and your example could help use to understand the problem better. — Jaromir Hamala, May 10 '21 at 14:31
Yes our connection pool is managed by spring, our whole application build on JDBCTemaple as db connection, queries and parameters everything is dynamic which will be written by user (So user do not have to write parameters again or keep count of it), because queries can be quite big of 200, 300 or more lines also, it is like creating dynamic datasets (BI report), I can achieve List (which can be used with Hazelcast Sources) from JDBCTemplate but it will also be stored in memory which not a good idea. — user3458271, May 10 '21 at 15:15
Can't we achieve it using create custom source or extending any interface and writing our own logic which can be used with Pipeline? — user3458271, May 24 '21 at 06:00

score 1 · Answer 1 · answered May 10 '21 at 07:04

I assume that by "Named JDBC Query" you meant "Named parameters".

It's is not a standard feature of JDBC API, it's a convenience provided by the Spring Framework. Hazelcast Jet does not support it. We are currently looking at how to provide better integration between Hazelcast Jet and Spring and this is one of the things on our radar.

score 1 · Answer 2 · answered May 12 '21 at 10:29

I'll answer the other part of the question: it's not technically possible to create a ResultSet source. The query must be executed on the cluster, you cannot execute a query on the client and send it to the cluster to fetch the rows. That's why the JDBC source asks you for a SQL statement and for a lambda to create the connection, but the connection will be created in the cluster and used to execute the query.

Hazelcast Jet with Named Jdbc Query or ResultSet?

2 Answers2