Unable to bind query parameter for native query using mongod

svirid · **Joined:** Mon Aug 25, 2014 9:42 am **Posts:** 10

Hi guys,

trying to use a native mongodb queries with hibernate OGM. Works perfectly the issues is I cant bind parameters.

This works:

Code:

public SocialConnection getConnectionByUserId(SocialProvider provider, String userId) {      
      Query query = entityManager.createNativeQuery("{ $query : { provider : 'FACEBOOK'} }", SocialConnection.class);
      query.setMaxResults(1);      
      return (SocialConnection) query.getSingleResult();
 }

Trying to bind parameter, does not work, anything I am doing wrong ?

Code:

public SocialConnection getConnectionByUserId(SocialProvider provider, String userId) {      
      Query query = entityManager.createNativeQuery("{ $query : { provider: :provider } }", SocialConnection.class);
      query.setParameter("provider ", provider);
      query.setMaxResults(1);      
      return (SocialConnection) query.getSingleResult();
  }

Getting Exceptiomn

Code:

java.lang.IllegalArgumentException: Parameter with that name [provider] did not exist
   at org.hibernate.jpa.spi.BaseQueryImpl.findParameterRegistration(BaseQueryImpl.java:505)
   at org.hibernate.jpa.spi.BaseQueryImpl.setParameter(BaseQueryImpl.java:631)
   at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:180)
   at org.hibernate.jpa.spi.AbstractQueryImpl.setParameter(AbstractQueryImpl.java:49)
   at com.dao.social.SocialConnectionDao.getConnectionByUserId(SocialConnectionDao.java:37)
   at com.service.social.SocialConnectionServiceImpl.getConnectionByUserId(SocialConnectionServiceImpl.java:39)
   at com.service.social.SocialConnectionServiceImpl$$FastClassBySpringCGLIB$$1d9a6b18.invoke(<generated>)
   at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
   at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:708)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
   at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
   at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
   at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:644)
   at com.service.social.SocialConnectionServiceImpl$$EnhancerBySpringCGLIB$$b586c0a5.getConnectionByUserId(<generated>)
   at test.com.TestSocialConnection.listSocialConnections(TestSocialConnection.java:60)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:601)
   at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
   at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
   at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
   at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
   at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
   at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
   at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
   at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
   at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:233)
   at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:87)
   at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
   at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
   at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
   at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
   at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
   at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
   at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
   at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
   at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:176)
   at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
   at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

DavideD · **Joined:** Fri Sep 09, 2011 3:18 am **Posts:** 295

Hi,
at the moment, OGM supports parameters for native queries only if the underlying query language support them.

MongoDB query language does not support parameters so you cannot use them.

Cheers,
Davide

svirid · **Joined:** Mon Aug 25, 2014 9:42 am **Posts:** 10

davided80 wrote:

Hi,
at the moment, OGM supports parameters for native queries only if the underlying query language support them.

MongoDB query language does not support parameters so you cannot use them.

Cheers,
Davide

Yes mongodb does not support them, I just thought u guys have it as dont like writing queries like that.

"select * from xxx where test = '" + test "'";

DavideD · **Joined:** Fri Sep 09, 2011 3:18 am **Posts:** 295

Quote:

Yes mongodb does not support them, I just thought u guys have it as dont like writing queries like that.

"select * from xxx where test = '" + test "'";

From the comment section of the issue (https://hibernate.atlassian.net/browse/OGM-480):

Quote:

We must not support parameters for QL that do not offer it or we would face the security risk of (S)QL injection. The only safe solution is to have a proper parser for that QL or a safe way to escape any input according to the QL rules.

This does not mean that we won't work on it, but at the moment we decided to focus on other aspects.

It goes without saying that you are more than welcome to help if you like.

If you have other questions, don't hesitate to ask and thanks for the feedback.

svirid · **Joined:** Mon Aug 25, 2014 9:42 am **Posts:** 10

davided80 wrote:

Quote:

Yes mongodb does not support them, I just thought u guys have it as dont like writing queries like that.

"select * from xxx where test = '" + test "'";

From the comment section of the issue (https://hibernate.atlassian.net/browse/OGM-480):

Quote:

We must not support parameters for QL that do not offer it or we would face the security risk of (S)QL injection. The only safe solution is to have a proper parser for that QL or a safe way to escape any input according to the QL rules.

This does not mean that we won't work on it, but at the moment we decided to focus on other aspects.

It goes without saying that you are more than welcome to help if you like.

If you have other questions, don't hesitate to ask and thanks for the feedback.

thank you appreciate the info.

svirid · **Joined:** Mon Aug 25, 2014 9:42 am **Posts:** 10

Quick question is there a way for me to read a query before running it if I use @NamedNativeQueries syntax.

For example I have query in entity:

Code:

@Entity
@NamedNativeQueries(value = {
   @NamedNativeQuery(name = "social.getConnectionUserById2", query = "{$query: { provider : 'FACEBOOK', providerUserId: ':providerUserId'} }", resultSetMapping = "myres")
})
@SqlResultSetMappings(value = {
   @SqlResultSetMapping(name="myres", entities = @EntityResult(entityClass = SocialConnection.class))
})

@Table(name = "social_connection" )
public class ...........

Now in DAO I am running it:

Code:

TypedQuery<SocialConnection> namedQuery = entityManager.createNamedQuery("social.getConnectionUserById2", SocialConnection.class);
namedQuery.getSingleResult();

This is all working fine, however what I want is before getSingleResult, I want to be able to parse query string and modify it. Lets say in query I have ':providerId' so I want replace it with my own parameter.

Code:

TypedQuery<SocialConnection> namedQuery = entityManager.createNamedQuery("social.getConnectionUserById2", SocialConnection.class);

String queryStr = namedQuery.getQueryString();
queryStr .....replacing with regex all the parameters

namedQuery.setQueryString(queryStr );
namedQuery.getSingleResult();

is that possible ?

Gunnar · **Joined:** Sat Jan 24, 2009 12:46 pm **Posts:** 388

Hi,

It feels a bit hackish, but you could do it by unwrapping the entity manager into a session and invoke getQueryString():

Code:

String queryString = em.unwrap( Session.class ).getNamedQuery( "social.getConnectionUserById2" ).getQueryString();
queryString = queryString.replace( ":providerUserId", 123 );
Query query = em.createNativeQuery( queryString, "myres" );

Note that this hack requires you to pass the parameter name (:myParam) within a String literal (':myParam'), as otherwise the query will not be valid. Technically it's no longer a query parameter that way, though.

I guess we really need to add support for parameters in native MongoDB queries somehow. If you want to help out with this, that'd be awesome!

--Gunnar

svirid · **Joined:** Mon Aug 25, 2014 9:42 am **Posts:** 10

Gunnar wrote:

Hi,

It feels a bit hackish, but you could do it by unwrapping the entity manager into a session and invoke getQueryString():

Code:

String queryString = em.unwrap( Session.class ).getNamedQuery( "social.getConnectionUserById2" ).getQueryString();
queryString = queryString.replace( ":providerUserId", 123 );
Query query = em.createNativeQuery( queryString, "myres" );

Note that this hack requires you to pass the parameter name (:myParam) within a String literal (':myParam'), as otherwise the query will not be valid. Technically it's no longer a query parameter that way, though.

I guess we really need to add support for parameters in native MongoDB queries somehow. If you want to help out with this, that'd be awesome!

--Gunnar

wonderful. this is great that I can actually do that. Thank you. I will see if I can contribute.