SQLJ template: Java 5 support

[Amit Gadkari]

Is SQLJ template project active.
If yes, then following enchancement needs to be done
a) Support for Java 5
b) parameters or arugments to query methods of sqlTemplate for passing parameters. (currently we have define all parameters as final)
c) General code for iterator and context, so that each SQL DAO need not define them.

[Mario Briggs]

Hi Amit,

like any opensource project, it is active as long as there are users using it and have a need for it. I would be interested in hearing your usage.

thanks
Mario

[amitashokgadkari]

Hi Amit,

like any opensource project, it is active as long as there are users using it and have a need for it. I would be interested in hearing your usage.

thanks
Mario

We are using lastest Spring 3.0 and using JBBC template.
We use sqlj extensively.
Currently we are evaluting spring SQLJ template.
Following are our observation
a) Generic support for SQLJ template (return List of specific object)
b) parameter passing
c) SQLJ template should be made generalized so that each Data access object need not define iterators and context.

[Mario Briggs]

I agree about a, not sure about b & c. Which Database are you using it with. Oracle or DB2? Honestly, i have never seen much usage of this or folks are using it without me knowing about this. If you mention your database, maybe i can let you know what's the focus with those databases

[amitashokgadkari]

We are using DB2 database.
Can we have some timeline for upgarding of SQLJTemplate with generic support.
I was to trying to do changes locally, but Object return parameter is used for returning List and also Object. Hence need to do more analysis while implementing support for generics


public <T> List<T> query(SqljQueryForRowsCallback callback, IterRowMapper<T> iterRowMapper) throws DataAccessException {
return (List<T>) query(callback, new IterRowMapperResultSetExtractor<T>(iterRowMapper)) ;
}

[Mario Briggs]

Since you are on DB2, I am assuming you want to reap the benefits of DB2 Static SQL. IF it is something else, let me know.

For DB2, the new push for this is pureQuery - http://www-01.ibm.com/software/data/...l?S_CMP=wspace
So you can use the pureQuery Method-Style/AnnotatedStyle/DAO and get all the same benefits you get with SQLJ, plus it is not cumbersome like SQLJ really is. It is all pure Java unlike SQLJ.

You can use pureQuery with Spring too, there are templates available. https://jira.springframework.org/browse/MOD-415
and see this devWork article - http://www.ibm.com/developerworks/da...ing/index.html

The Spring-PureQuery Templates already uses Generics and given pureQuery is simpler, you dont have things like SQLJContext/SQLJIterators. For the same reason none of the parameters are final on the query methods. So honestly all your upgrade requirements are already there on SpringpureQuery templates. I believe there is a version greater than 1.2 available too, which Matt Anderson should be willing to share/upload.

P.S. the final on parameters & iterator/Context issues are necessitated in SQLJTemplate becuase of SQLJ semantics. I can discuss if you want, but i am thinking you would plonk for pureQuery, so i will skip that, but let me know if you want to discuss.

[Amit Gadkari]

Thanks for your suggestions.

Yes, we will try to use pureQuery.

Many applications in my organization are already using SQLJ
Hence we need to enhanced SQLJ template.

I will modify source code for SQLJTemplate for adding generics and upload source code as an attachment with this message.

Once changes are done, can you please verfiy those changes and upload in repository.

If I require your help can we discuss and upgrade this template for generics support ?

[Mario Briggs]

For these existing SQLJ applications were u already using SQLJTemplate? or you are moving these existing applications to Spring ?

I cannot commit that i can spend time on this since as i mentioned all what you want is available on the pureQuery template. But if you are contributing, you will need to add test cases for those and have the existing test cases passing.

[amitashokgadkari]

I have uploaded modified source code for generic support for SQLJ template
I have tested this by writing sample application.

I wanted to know procedure for running junit test cases.

When I tried running sqlj test case on old code and new code I am getting error while executing sqljtemplate code

java.lang.AssertionError:
Unexpected method call prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = 3", 1003, 1007):
prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = 3"): expected: 1, actual: 0
isClosed(): expected: 1, actual: 0
at org.easymock.internal.MockInvocationHandler.invoke (MockInvocationHandler.java:43)
at org.easymock.internal.ObjectMethodsFilter.invoke(O bjectMethodsFilter.java:72)
at $Proxy0.prepareStatement(Unknown Source)
at sqlj.runtime.profile.ref.JDBCProfile.prepareStatem ent(JDBCProfile.java:303)
at sqlj.runtime.profile.ref.JDBCProfile.getStatement( JDBCProfile.java:203)
at sqlj.runtime.profile.ref.JDBCProfile.getStatement( JDBCProfile.java:239)
at sqlj.runtime.profile.ref.ProfileWrapper.getStateme nt(ProfileWrapper.java:113)
at sqlj.runtime.profile.ref.IterConvertProfile.getSta tement(IterConvertProfile.java:79)
at sqlj.runtime.profile.ref.ProfileWrapper.getStateme nt(ProfileWrapper.java:113)
at sqlj.runtime.profile.ref.UntypedSelectProfile.getS tatement(UntypedSelectProfile.java:56)
at sqlj.runtime.profile.ref.ProfileWrapper.getStateme nt(ProfileWrapper.java:113)
at sqlj.runtime.profile.ref.ParamRegProfile.getStatem ent(ParamRegProfile.java:61)
at sqlj.runtime.profile.ref.PositionedProfile.getStat ement(PositionedProfile.java:128)
at sqlj.runtime.profile.ref.TransactionControlProfile .getStatement(TransactionControlProfile.java:147)
at sqlj.runtime.ExecutionContext$StatementStack.setSt atement(ExecutionContext.java:1172)
at sqlj.runtime.ExecutionContext.registerStatement(Ex ecutionContext.java:695)
at org.springextensions.sqlj.core.SqljTemplateQueryTe sts$7.sqljSelectIntoClause(SqljTemplateQueryTests. java:801)
at org.springextensions.sqlj.core.SqljTemplate$1Query ForObjectConnectionCallback.doInConnection(SqljTem plate.java:501)
at org.springextensions.sqlj.core.SqljTemplate.execut eConnectionCallback(SqljTemplate.java:408)
at org.springextensions.sqlj.core.SqljTemplate.queryF orObject(SqljTemplate.java:504)
at org.springextensions.sqlj.core.SqljTemplate.queryF orInt(SqljTemplate.java:513)
at org.springextensions.sqlj.core.SqljTemplateQueryTe sts.testQueryForInt(SqljTemplateQueryTests.sqlj:47 4)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Native MethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(De legatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:618)
at junit.framework.TestCase.runTest(TestCase.java:164 )
at junit.framework.TestCase.runBare(TestCase.java:130 )
at junit.framework.TestResult$1.protect(TestResult.ja va:106)
at junit.framework.TestResult.runProtected(TestResult .java:124)
at junit.framework.TestResult.run(TestResult.java:109 )
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:2 30)
at junit.framework.TestSuite.run(TestSuite.java:225)
at org.eclipse.jdt.internal.junit.runner.junit3.JUnit 3TestReference.run(JUnit3TestReference.java:130)
at org.eclipse.jdt.internal.junit.runner.TestExecutio n.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRu nner.main(RemoteTestRunner.java:196)

[Mario Briggs]

thanks for opening an issue on the issue tracker. I added a comment there.

https://jira.springframework.org/browse/SESQLJ-1