Menu
▾
▴
Re: [Springframework-developer] Added BLOB/CLOB support to SqlUpdate and StoredProcedure
|
From: <tho...@tr...> - 2004-06-10 20:16:21
|
I have made the changes to the SqlParameters and SqlLobValue - the LobHandler
reference has been moved from SqlParameters to SqlLobValue.
Here is an example of how you would use it:
LobHandler lobHandler = new OracleLobHandler();
NativeJdbcExtractor jdbcExtractor = new WebLogicNativeJdbcExtractor();
lobHandler.setNativeJdbcExtractor(jdbcExtractor);
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));
su.compile();
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(), lobHandler);
int count = su.update(inval);
is.close();
If you don't provide a LobHandler for the SqlLobValue, then a DefaultLobHandler
will automatically be created.
Let me know if there are some other issues.
Thomas
Quoting Thomas Risberg <tho...@tr...>:
> Tom,
>
> I would have to say that I agree with all your points. I'll look into
> changing this as soon as I can.
>
> Thanks for ponting this out. As you said, there really is no need to
> have the LobHandler available when the parameters are declared. As long
> as we have it when we process the SqlLobValue, we will be fine.
>
> Thomas
>
> Tom Turelinckx wrote:
>
> >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
> >>
> >>
> >
> >
> >-------------------------------------------------------
> >This SF.Net email is sponsored by: GNOME Foundation
> >Hackers Unite! GUADEC: The world's #1 Open Source Desktop Event.
> >GNOME Users and Developers European Conference, 28-30th June in Norway
> >http://2004/guadec.org
> >_______________________________________________
> >Springframework-developer mailing list
> >Spr...@li...
> >https://lists.sourceforge.net/lists/listinfo/springframework-developer
> >
> >
> >
> >
> >
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by: GNOME Foundation
> Hackers Unite! GUADEC: The world's #1 Open Source Desktop Event.
> GNOME Users and Developers European Conference, 28-30th June in Norway
> http://2004/guadec.org
> _______________________________________________
> Springframework-developer mailing list
> Spr...@li...
> https://lists.sourceforge.net/lists/listinfo/springframework-developer
>
|
