Community
Participate
Working Groups
Build Identifier: 2.2.0M5 SELECT m FROM Main m WHERE SUBSTRING(m.string,1,9) = SUBSTRING(:value,1,9) gets transformed to SELECT ID, STRING FROM MAIN WHERE (SUBSTR(STRING, ?, ?) = SUBSTR(?, ?, ?)) bind => [1, 9, some value, 1, 9] instead of the expected SELECT ID, STRING FROM MAIN WHERE (SUBSTR(STRING, 1, 9) = SUBSTR(?, 1, 9)) bind => [some value] which although correct in the sense that the expected data is returned it means that oracle will not use a function index we have created on the string column. What is the reasoning behind using bind vars in this case ? Reproducible: Always Steps to Reproduce: 1 - see file that will be shortly uploaded. 2 - run Main.main() and look at console output. 3 - if the code in Setup.getProperties is changed you can see this only happens if targetDatabase = "ORACLE" but not for HSQL and Derby 4 - fill in the url for the oracle db so it runs against real oracle db and not hsql and you can see it happens then as well
Created attachment 185050 [details] to reproduce - Main.main()
Created attachment 185053 [details] run Main.main() to reproduce prev attachment was set to run against derby - this using oracle
Binding of Literals is controlled in the DatabasePlatform. (shouldBindLiterals property on DatabasePlatform) If you do not want literals to be bound, you should be able to change that property to false in a session customizer. Something like: session.getPlatform().setShouldBindLiterals(false)
Thanks Tom, ....BUT.... we are injectingin the an entityManager @PersistenceContext EnyityManage em; and the only way we can set this would be by calling the following reflectively as we dont want any vendor specific code hanging around : em.getDelegate().getSession.getPlatform().setShouldBindLiterals(true); is there any property we can set in orm.xml or persistence.xml to cover this ?
This will work without introducing a dependency on EclipseLink into your code. Although a SessionCustomizer is an EclipseLink-specific construct, it is configured as a property in your persistence.xml and, as a result, it will not be used by other providers. See "eclipselink.session.customizer" under: "http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#How_to_Use_the_Persistence_Unit_Properties_for_Customization_and_Validation
good stuff, thanks for the help.
MMmm, it nearly works.... I implemented the customization class as described and when I set a modification breakpoint on the shouldBindLiterals variable in the DatabasePlatform class I see it setting it to false as expected when called from the csutomizer, on a DatabasePlatform with instance id = x, however when the 1st query in our application is executed it creates a new instance of DatabasePlatform that never gets sent to the customization class and shouldBindLiterals variable never gets set to false, that same new instance Y is then used from that point forwards and the customized session is never used ? The stacktrace for when the new instance is created is : Thread [pool-1-thread-18] (Suspended (modification of field shouldBindLiterals in DatabasePlatform)) Oracle10Platform(DatabasePlatform).<init>() line: 204 Oracle10Platform(DatabasePlatform).<init>() line: 34 Oracle10Platform(OraclePlatform).<init>() line: 62 Oracle10Platform(Oracle8Platform).<init>() line: 42 Oracle10Platform(Oracle9Platform).<init>() line: 113 Oracle10Platform.<init>() line: 30 NativeConstructorAccessorImpl.newInstance0(Constructor, Object[]) line: not available [native method] NativeConstructorAccessorImpl.newInstance(Object[]) line: 39 DelegatingConstructorAccessorImpl.newInstance(Object[]) line: 27 Constructor<T>.newInstance(Object...) line: 513 Class<T>.newInstance0() line: 355 Class<T>.newInstance() line: 308 PrivilegedAccessHelper.newInstanceFromClass(Class) line: 384 DatabaseLogin(DatasourceLogin).setPlatformClassName(String) line: 495 ServerSession(DatabaseSessionImpl).loginAndDetectDatasource() line: 585 EntityManagerFactoryProvider.login(ServerSession, Map) line: 228 EntityManagerSetupImpl.deploy(ClassLoader, Map) line: 392 EntityManagerFactoryImpl.getServerSession() line: 164 EntityManagerFactoryImpl.createEntityManagerImpl(Map) line: 221 EntityManagerFactoryImpl.createEntityManager(Map) line: 216 EntityManagerWrapper._getDelegate() line: 181 EntityManagerWrapper.createNamedQuery(String) line: 380 TimerSchedulerEJB.findAllActiveSchedules() line: 804 TimerSchedulerEJB.refresh(boolean) line: 729 NativeMethodAccessorImpl.invoke0(Method, Object, Object[]) line: not available [native method] NativeMethodAccessorImpl.invoke(Object, Object[]) line: 39 DelegatingMethodAccessorImpl.invoke(Object, Object[]) line: 25 Method.invoke(Object, Object...) line: 597 EJBSecurityManager.runMethod(Method, Object, Object[]) line: 1011 SecurityUtil.invoke(Method, Invocation, Object, Object[], Container, SecurityManager) line: 175 StatelessSessionContainer(BaseContainer).invokeTargetBeanMethod(Method, Invocation, Object, Object[], SecurityManager) line: 2929 StatelessSessionContainer(BaseContainer).intercept(Invocation) line: 4020 EJBLocalObjectInvocationHandler.invoke(Class, Method, Object[]) line: 197 EJBLocalObjectInvocationHandlerDelegate.invoke(Object, Method, Object[]) line: 83 $Proxy76.refresh(boolean) line: not available TimerSchedulerInitialiser.contextInitialized(ServletContextEvent) line: 95 WebModule(StandardContext).listenerStart() line: 4655 WebModule(StandardContext).start() line: 5364 WebModule.start() line: 345 LifecycleStarter.doRun() line: 58 LifecycleStarter(RunnableBase<T>).runSync() line: 304 LifecycleStarter(RunnableBase<T>).run() line: 341 Executors$RunnableAdapter<T>.call() line: 441 FutureTask$Sync.innerRun() line: 303 FutureTask<V>.run() line: 138 ThreadPoolExecutor$Worker.runTask(Runnable) line: 886 ThreadPoolExecutor$Worker.run() line: 908 Thread.run() line: 619
ok... our DB platform detection is getting in the way. Writing your customize() method like this should help: public void customize(Session session) { session.getEventManager().addListener(new SessionEventAdapter() { public void postLogin(SessionEvent event) { event.getSession().getPlatform().setShouldBindLiterals(true); } }); }
of course... use false for the argument above :) event.getSession().getPlatform().setShouldBindLiterals(false);
that's better! all good now, thanks
The Eclipselink project has moved to Github: https://github.com/eclipse-ee4j/eclipselink
