Menu
▾
▴
Re: [Springframework-developer] Added BLOB/CLOB support to SqlUpdate and StoredProcedure
|
From: Tom T. <tom...@pr...> - 2004-06-10 10:49:51
|
Hello Thomas,
We have several stored procedures that take BLOBs as parameters, and I've
just reworked them to use the new built-in LOB support, which works
perfectly, thanks!
However, I'm not entirely happy about the fact that the LobHandler is
being passed as an argument to the SqlParameter constructor.
Our classes extend the StoredProcedure class, and I've declared a
"lobHandler" property to be set in the application context, as the same
LobHandler is used by multiple classes, and it also needs a reference to
the NativeJdbcExtractor. Unfortunately, this implicates that the
declaration of the sql parameters, which we've traditionally been doing
in the constructor, now has to be moved to the afterPropertiesSet()
method, as the LobHandler is not yet available in the constructor.
Moreover, care must be taken that the overridden afterPropertiesSet()
first declares the sql parameters and then calls
super.afterPropertiesSet(), which calls compile(). I don't like this.
Of course, the LobHandler could be passed as a constructor argument, but
I don't like that either ;-)
All RdbmsOperations require the sql parameters to be declared in code,
while the sql string can be set from the application context, which is
what we're doing. This makes sense, as you could look at the sql
parameters as defining a "contract", while the sql string is just an
implementation of that contract. The class only cares about the
"contract", any implementation will do. The sql parameters are thus an
essential part of the class, and it's natural to declare them in the
constructor. If the declaration has to be moved to the
afterPropertiesSet() method, I somehow feel the advantage of declaring
the parameters in code is lost...
As LobHandler is currently the only collaborator possibly needed by a
SqlParameter, I've been wondering whether it's appropriate for it to be
part of the SqlParameter definition. It's not part of the "contract", so,
in fact, does not really belong there. Maybe we should make it part of
the SqlLobValue, which would then encapsulate the LOB value itself, and
the logic for dealing with it. The SqlLobValue is normally constructed in
the execute() method, when the LobHandler is already available. I've had
a look at the LOB support implementation, and it appears this would be
trivial to change!
The same idea can probably be applied to a future CustomSqlTypeHandler
for special parameter types. Instead of passing a CustomSqlTypeHandler to
the constructor of SqlParameter, a CustomSqlTypeValue object would be set
as parameter value, containing both the value and its
CustomSqlTypeHandler, which could be a collaborator.
What do you think?
Kind regards,
Tom.
On Fri, 04 Jun 2004 13:57:14 -0400, "Thomas Risberg"
<tho...@tr...> said:
> OK, I'm done adding support for BLOB/CLOB to SqlUpdate and
> StoredProcedure. I have commited these changes and you are welcome to
> take them for a spin. If you notice any problems let me know. I'll
> add some mocked unit tests to our testsuite the next couple of days.
> All tests I have now are live connection ones.
>
> Here is a short example of usage.
>
> I'm reusing the LobHandler that Juergen added recently. It works very
> well and it supports the following input types for LOBs:
>
> BLOB - byte array or java.io.InputStream
> CLOB - String, java.io.InputStream or java.io.Reader
>
> If you are running in an appserver with a connection pool that wraps
> your connection, depending on which LobHandler you use, you might have
> to set a NativeJdbcExtractor. For Oracle 9i and Weblogic 8.1 I used the
> following setup:
>
> LobHandler lobHandler = new OracleLobHandler();
> NativeJdbcExtractor jdbcExtractor = new
> WebLogicNativeJdbcExtractor();
> lobHandler.setNativeJdbcExtractor(jdbcExtractor);
>
> This should of course be set via IoC for a real app.
>
> Now you need to create your SqlUpdate and declare you parameters:
>
> String sql = "insert into docs (doc_id, content) values(?, ?)";
> SqlUpdate su = new SqlUpdate(ds, sql);
> su.declareParameter(new SqlParameter("id", Types.INTEGER));
> su.declareParameter(new SqlParameter("lob", Types.BLOB,
> lobHandler));
> su.compile();
>
> The LOB parameter is declared with a new constructor that takes the
> LobHandler as the third argument. If you don't specify a LobHandler we
> will automatically generate a DefaultLobHandler.
>
> Next we need to create an array with all parameters and call the
> update() method to insert each row. The LOB value must be passed in as
> an SqlLobValue object together with the length (unless you use a byte
> array or String in which case we can easily figure out the length).
> SqlLobValue is a new class that holds the actual LOB value plus the
> LobCreator that the framework internally populates during the update.
>
> Object[] inval = new Object[2];
> inval[0] = new Integer(newId);
> File in = new File("some.doc");
> InputStream is = new FileInputStream(in);
> inval[1] = new SqlLobValue(is, (int) in.length());
> int count = su.update(inval);
> is.close();
>
> That is it. It works the same way for CLOBs and it also works for
> parameters passed in to a StoredProcedure.
>
> While making this change I combined the setXxxxx logic for
> PreparedStatement and CallableStatement to one static method that I
> added to JdbcUtils. If you notice anything odd due to this refactoring,
> please let me know.
>
> Thomas
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by the new InstallShield X.
> From Windows to Linux, servers to mobile, InstallShield X is the one
> installation-authoring solution that does it all. Learn more and
> evaluate today! http://www.installshield.com/Dev2Dev/0504
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
|
