-
1. Re: How do you call a Stored Procedure?
tom_goring Apr 10, 2008 11:18 PM (in response to alringer)no seam support for stored proc's.
you could use some spring lib's and use the spring-seam support.
If it's just one you maybe better just calling the SP with some JDBC in your session bean.
-
2. Re: How do you call a Stored Procedure?
pmuir Apr 14, 2008 12:23 AM (in response to alringer)Nothing particularly to do with Seam - this is really up to whatever persistence layer you use e.g. JPA.
-
3. Re: How do you call a Stored Procedure?
stells Nov 21, 2008 4:16 PM (in response to alringer)Hello, Marc!
I face the same problem right now. (I must to use legacy stored proc's while I need JPA also).
What solution have you implemented?
Thank you in advance!
Michail
-
4. Re: How do you call a Stored Procedure?
alringer Nov 21, 2008 6:07 PM (in response to alringer)I just implemented a DatabaseUtility class. Here is an example. of one of the stored procs being called...
public static BigDecimal calcDistance(String zipcode, BigDecimal lat, BigDecimal lon) throws SQLException
{
BigDecimal result = null;
DataSource datasource = null;
try
{
InitialContext context = new InitialContext();
datasource = (DataSource)context.lookup("java:/MyDatasource");
}
catch(NamingException e)
{
assert false : "Unable to create directory context!";
}
Connection connection = datasource.getConnection();
// Call a procedure with one n-IN/1-OUT parameters
CallableStatement statement = connection.prepareCall("{call CalcDistance(?, ?, ?, ?)}");
try
{
statement.setString(1, zipcode);
statement.setBigDecimal(2, lat);
statement.setBigDecimal(3, lon);
statement.registerOutParameter(4, Types.DOUBLE);
statement.execute();
result = statement.getBigDecimal(4);
}
catch (SQLException e)
{
log.error("sql error " + e);
}
finally
{
if(statement != null)
{
statement.close();
}
if(connection != null)
{
connection.close();
}
}
return result;
} -
5. Re: How do you call a Stored Procedure?
luxspes Nov 21, 2008 7:41 PM (in response to alringer)I had posted here before, but my post got deleted (did I say something wrong?):
Where I work, we have 2 projects with the same situation, one of them uses Seam+Spring+Ibatis+POJOasDTO and the other Seam+Spring+Spring-Jdbc+MapasDTO.
I have found that Seam+Spring+Ibatis works find with big projects, but with small project it becomes cumbersome to maintain the daos and the xml files, for those it si better to use Seam+Spring+Spring-Jdbc+MapasDTO. (Of course it is alwasy better to use Seam+JPA, but sometimes that is not an option)
I do not think it is a good idea to use Seam+PlainJDBC, it is just to easy to make mistakes, and you end up wrinting an insane amount of code even for very simple things (like in your previous example)
What we do is that we configure the datasource with JNDI, link to it using Spring and then configure a JdbcTemplate bean linked to that datasource, then we inject it to our Seam component:
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; @Name("seamComponent"); public SeamComponent{ @In("#{jdbcTemplate}") private transient SimpleJdbcTemplate jdbcTemplate; }
Spring works really good as a glue between Seam and Spring-Jdbc or Ibatis.
Regards,
-
6. Re: How do you call a Stored Procedure?
luxspes Nov 21, 2008 7:52 PM (in response to alringer)With Seam+Spring+Spring-Jdbc your example would look like this:
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate; @Name("seamComponent"); public SeamComponent{ @In("#{jdbcTemplate}") public static BigDecimal calcDistance(String zipcode, BigDecimal lat, BigDecimal lon) throws { ConnectionCallbak connectionCallBack = new ConnectionCallback() { @Override public Object doInConnection(Connection conexion_db) throws SQLException, DataAccessException { CallableStatement statement = connection.prepareCall("{call CalcDistance(?, ?, ?, ?)}"); statement.setString(1, zipcode); statement.setBigDecimal(2, lat); statement.setBigDecimal(3, lon); statement.registerOutParameter(4, Types.DOUBLE); statement.execute(); result = statement.getBigDecimal(4); return result; } }); BigDecimal result = (BigDecimal) template.execute(connectionCallBack); return result; }
As you can see the code reduces significantly, and that is not the only advantage: now the Connection is automatically handled by Spring so you do not have to waste time writing try{}catch{}finally{} nor risk forgetting or not writing them if you are in a hurry.
Regards,
-
7. Re: How do you call a Stored Procedure?
luxspes Nov 21, 2008 7:56 PM (in response to alringer)Ha! Sorry, forgot to declare the variable in my previous post:
This:
@Name("seamComponent"); public SeamComponent{ @In("#{jdbcTemplate}") public static BigDecimal calcDistance(String zipcode, BigDecimal lat, BigDecimal lon) throws {
Should be this:
@Name("seamComponent"); public SeamComponent{ @In("#{jdbcTemplate}") private transient SimpleJdbcTemplate jdbcTemplate; public static BigDecimal calcDistance(String zipcode, BigDecimal lat, BigDecimal lon) {
Oh, and the throws in calcDistance is no longer necessary, Spring also handles checked exceptions for you.
I think Seam and Spring make a really good combination.
Regards,
-
8. Re: How do you call a Stored Procedure?
gonorrhea Jun 18, 2009 8:05 AM (in response to alringer)
Pete Muir wrote on Apr 14, 2008 00:23:
Nothing particularly to do with Seam - this is really up to whatever persistence layer you use e.g. JPA.I disagree. See my recent thread: My Link
Seam needs to provide a JDBC API just like Spring does...
Francisco's Seam/Spring integration example is a good idea but we Seam developers should not have to rely on Spring API for something as basic as JDBC...
Is there official reference documentation (or in a Seam book) on how to do the Seam/Spring integration regarding JdbcTemplate usage?
-
9. Re: How do you call a Stored Procedure?
luxspes Jun 18, 2009 4:02 PM (in response to alringer)
Arbi Sookazian wrote on Jun 18, 2009 08:05:
Pete Muir wrote on Apr 14, 2008 00:23:
Nothing particularly to do with Seam - this is really up to whatever persistence layer you use e.g. JPA.
I disagree. See my recent thread: My Link
Seam needs to provide a JDBC API just like Spring does...Feel free to reinvent the wheel, but I see no point in doing so...
Francisco's Seam/Spring integration example is a good idea but we Seam developers should not have to rely on Spring API for something as basic as JDBC...
Is there official reference documentation (or in a Seam book) on how to do the Seam/Spring integration regarding JdbcTemplate usage?There aint much to say, Dan Allen book covers how to create a datasource in Spring that is used by a Seam application, after that all you have to do is connect your jdbcTemplate with your dataSource
-
10. Re: How do you call a Stored Procedure?
vrxyz Feb 8, 2010 1:45 PM (in response to alringer)Hi,
Does that mean I have to download Spring framework and configure everything there? All I just have to do is inject it to Seam?
Just to make sure I'm following. Thank you :)