Hello Vasily, > The extra roundtrip for preparation may not actually eventuate; JDBC driver > may and should cache prepared statements in associated connection. > I checked it with jtds and there was no repeated preparation, so that's not > an issue really. > Prepared statement is probably only valid in the context of the connection > where it was created anyways.
I wasn't aware of that! So internally, the jtds JDBC connection maintains a map of SQL strings / open cursors. I wonder whether that's the case for Oracle, too... > As to the way to bind/provide new parameter values - it would be ideal to > have explicit OO way to handle that, maybe using a class to represent all > parameters. > Why not have all parameters in a map by name while maintaining their > position as before > > in some sort of Query class Yes, Query already exposes a List<Object> getBindValues() method: https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/Query.java That is an immutable copy of the bind values, though > <T> Parameter<T> parameter(String name, T value); // create or set value > <T> Parameter<T> getParameter(String name); // get existing jOOQ has no named parameters. You'd have to access them by index > and then in Parameter<T> > T getValue(); > void setValue(T newValue); > int getIndex(); > > Then user will set new value with > query.getParameter("name").setValue(newValue); I can see where you're going. It is actually quite a task to update every query parts' bind values, and I wonder whether this is so useful in general. To better understand your use case, could you provide me with an example, where it is really more convenient to get/set bind values by index rather than re-constructing the query? I'm asking because the Factory (and all objects created from it) aren't thread-safe. So I don't see the use-case right now, where re-using query instances is helpful... An alternative solution, if this is only to be used once or twice: // -------------------------------------------------- // Create the dummy query once. // This will bind 4 values: "Y", 1, 2, 3 Select<?> select = create.selectFrom(ABC).where(X.equal('Y')).and(Z.in(1, 2, 3)); // Instead of executing it, get its SQL: String sql = select.getSQL(); // And run it either with JDBC, Spring Templates, or jOOQ: Result<?> result1 = create.fetch(sql, "X", 1, 2, 3); Result<?> result2 = create.fetch(sql, "Y", 4, 5, 6); Result<?> result3 = create.fetch(sql, "Z", 7, 8, 9); // This also works with INSERT / UPDATE / DELETE int result4 = create.query(sql, "A", "B", 1, 2); int result5 = create.query(sql, "C", "D", 3, 4); // -------------------------------------------------- Cheers Lukas
