Unable to execute native query on MongoDB

frank1970 · **Joined:** Fri May 01, 2015 1:38 pm **Posts:** 3

Hi all!
I've successfully managed to configure in my persistence.xml the connection towards the MongoDB provider. I'd need to execute a native MongoDB query in an EJB:

Code:

public List<Book>  queryNative(){
   EntityManager em = emf.createEntityManager();
   String query1 = "db.Book.find({author: \"smith\"})";
        Query query = em.createNativeQuery(query1);

        List <Book> list =  query.getResultList();
        return list;         
}

When I execute the above query, an Exception is raised:
Caused by: com.mongodb.util.JSONParseException:
db.Book.find({author: "smith"})
^
at com.mongodb.util.JSONParser.parse(JSON.java:216) [mongo-java-driver-2.12.4.jar:]
at com.mongodb.util.JSONParser.parse(JSON.java:163) [mongo-java-driver-2.12.4.jar:]
at com.mongodb.util.JSON.parse(JSON.java:99) [mongo-java-driver-2.12.4.jar:]
at com.mongodb.util.JSON.parse(JSON.java:79) [mongo-java-driver-2.12.4.jar:]
at org.hibernate.ogm.datastore.mongodb.query.parsing.nativequery.impl.MongoDBQueryDescriptorBuilder.build(MongoDBQueryDescriptorBuilder.java:48) [hibernate-ogm-mongodb-4.1.1.Final.jar:4.1.1.Final]
at org.hibernate.ogm.datastore.mongodb.MongoDBDialect.parseNativeQuery(MongoDBDialect.java:702) [hibernate-ogm-mongodb-4.1.1.Final.jar:4.1.1.Final]
at org.hibernate.ogm.datastore.mongodb.MongoDBDialect.parseNativeQuery(MongoDBDialect.java:123) [hibernate-ogm-mongodb-4.1.1.Final.jar:4.1.1.

The above query however works perfectly in a Mongo shell.

Here is my persistence.xml for my application which is deployed on WildFly 8.2

Code:

<persistence-unit name="mongo-ogm" transaction-type="JTA">
        <provider>org.hibernate.ogm.jpa.HibernateOgmPersistence</provider>
        <class>com.sample.Book</class>
        <properties>
            <property name="hibernate.transaction.jta.platform"
               value="org.hibernate.service.jta.platform.internal.JBossAppServerJtaPlatform" />
            
            <property name="hibernate.ogm.datastore.database" value="sampledb" />
            <property name="hibernate.ogm.datastore.host" value="localhost" /> 
            <property name="hibernate.ogm.datastore.provider"
               value="MONGODB"/> 
  
        </properties>
    </persistence-unit>
</persistence>

Any idea how to sort out this issue?

emmanuel · **Posted:** Mon May 04, 2015 8:29 am

There is nothing mega obvious. A couple of things:

- which version of OGM are you using? Can you try the latest 4.1.3.Final or 4.2.0.Beta1
- which driver version of MongoDB are you using?
- our tests and documentation uses single ticks for both the attributes and values, can you try that?

Code:

"db.WILDE_POEM.count({ 'name' : 'Athanasia' })";

frank1970 · **Joined:** Fri May 01, 2015 1:38 pm **Posts:** 3

Hi Emmanuel,
thanks using the single ticks worked out!
Thanks

emmanuel · **Posted:** Mon May 04, 2015 5:42 pm

I have created https://hibernate.atlassian.net/browse/OGM-807 to clarify the documentation

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

Hi,

The issue actually is that you specified the property key without quotes, i.e. author instead of "author". We require the strict mode (see http://docs.mongodb.org/manual/reference/mongodb-extended-json/) for the CLI syntax.

The only relaxation of this is that single quotes may be used when specifying attribute names/values. So both, "db.Book.find({\"author\": \"smith\"})" and "db.Book.find({'author': 'smith'})" will work, but you need to specify quotes one way or the other.

I've added a clarification to the ref guide.

--Gunnar

nmarques · **Joined:** Thu Aug 06, 2015 5:48 am **Posts:** 3

Hello,

I'm facing the same issue using hibernate-ogm 4.2.0-Final connected to mongoDB (mongo-java-driver 2.13.2)

Code:

String query4 = "db.test.myTest.count()";
Object[] count = (Object[]) em.createNativeQuery(query4).getSingleResult();

Code:

Exception in thread "main" com.mongodb.util.JSONParseException: 
db.test.myTest.count()
^
   at com.mongodb.util.JSONParser.parse(JSON.java:208)
   at com.mongodb.util.JSONParser.parse(JSON.java:155)
   at com.mongodb.util.JSON.parse(JSON.java:93)
   at com.mongodb.util.JSON.parse(JSON.java:74)
   at org.hibernate.ogm.datastore.mongodb.query.parsing.nativequery.impl.MongoDBQueryDescriptorBuilder.build(MongoDBQueryDescriptorBuilder.java:48)
   at org.hibernate.ogm.datastore.mongodb.MongoDBDialect.parseNativeQuery(MongoDBDialect.java:725)
   at org.hibernate.ogm.datastore.mongodb.MongoDBDialect.parseNativeQuery(MongoDBDialect.java:128)
   at org.hibernate.ogm.dialect.impl.ForwardingGridDialect.parseNativeQuery(ForwardingGridDialect.java:191)
   at org.hibernate.ogm.query.impl.NativeNoSqlQueryInterpreter.getCustomQuery(NativeNoSqlQueryInterpreter.java:50)
   at org.hibernate.ogm.query.impl.NativeNoSqlQueryInterpreter.createQueryPlan(NativeNoSqlQueryInterpreter.java:45)
   at org.hibernate.engine.query.spi.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:226)
   at org.hibernate.ogm.hibernatecore.impl.OgmSessionImpl.list(OgmSessionImpl.java:307)
   at org.hibernate.ogm.query.impl.NoSQLQueryImpl.list(NoSQLQueryImpl.java:130)
   at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
   at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:495)

Any idea of what's wrong here ? I believe that currently, nativeQuery only support find query syntax liek {$query:{...}, ...} but i cannot query with CLI syntax.
Is there any other way to have a count() query ?

Thanks in advance

nmarques · **Joined:** Thu Aug 06, 2015 5:48 am **Posts:** 3

In addition to my previous message:

My entites are defined like

Code:

@Entity
@Table(schema = "test", name = "myTest")
public class MyTest{ ... }

In mongoDB, the table created is "test.myTest". So when i do nativeQuery

Code:

db.test.myTest.count()

it fails.

If i just not use the schema definition the query

Code:

db.myTest.count()

runs successfully. Is there any limitations or it is just a bug ?

nmarques · **Joined:** Thu Aug 06, 2015 5:48 am **Posts:** 3

Ok, i got it:

This is a limitation from the org.hibernate.ogm.datastore.mongodb.query.parsing.nativequery.impl.NativeQueryParser which define the pattern as :

Quote:

public Rule Query() {
return Sequence( FirstOf( FindQuery(), CriteriaOnlyFindQuery() ), EOI, push( builder ) );
}

public Rule FindQuery() {
return Sequence( Db(), Separator(), Collection(), Separator(), Operation() );
}

@SuppressSubnodes
public Rule Collection() {
return Sequence( OneOrMore( TestNot( Separator() ), ANY ), builder.setCollection( match() ) );
}

So collection names must not have a dot inisde, so we don't have possibility to use schema...

Hope, that it will be fixed in the future ? ;)

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

Hi,

Sorry for the late reply. Using "schema()" is not used in conjunction with MongoDB (We could try and map it to database names possibly, but we did not investigate this path yet). Encoding the schema name in the collection name seems just to be a coincidential effect of this not being supported.

That being said we probably should support dots in collection names. I've filed https://hibernate.atlassian.net/browse/OGM-900 for this. Would you be interested in sending in a pull request for fixing this?

Thanks,

--Gunnar