I just about tried everything, but I am not receiving any results when running a simple SQL query running on the following target platform: Google AppEngine + Google Cloud SQL datastore using DataNucleus (all on Google Cloud Platform). I have deployed a basic Java Servlet and set up a simple MySql table. Here are the details. What is most frustrating is that I don't know how to get more information to progress. So your help is much appreciated. So again, there is no error, just no results in the result set.
The Database
Of course, I am 100% sure there is data in the table. My database (MySql) has the following table in the schema 'vicparkhoney':
CREATE TABLE `mytable` ( `name` varchar(255) DEFAULT NULL);
The following query, directly entered in MySql workbench gives two results:
SELECT * FROM vicparkhoney.mytable;
results:
name
===========
Name1
Name2
(even after several reconnects for those wondering if I had committed the inserts...)
And this will be no surprise to you, the following query returns one result:
SELECT * FROM vicparkhoney.mytable n WHERE n.name = 'Name1';
name
===========
Name1
The Java Code
I don't get this same result when running the JPA query through DataNucleus. This is my Servlet code:
EntityManagerFactory emf = Persistence.createEntityManagerFactory("transactions-optional");
EntityManager em = emf.createEntityManager();
Query q = em.createQuery("select n from MyTable n where n.name = 'Name1'");
List results = q.getResultList();
Iterator iter = results.iterator();
MyTable n = null;
while (iter.hasNext()) {
n = (MyTable)iter.next();
}
em.close();
This is the MyTable code for the Entity class:
package com.vicparkhoney.backend.bean;
import javax.persistence.*;
@Entity
@Table( name = "mytable" )
public class MyTable {
@Basic
@Id
private String name;
/**
* @return the name
*/
public String getName() {
return name;
}
/**
* @param name the name to set
*/
public void setName(String name) {
this.name = name;
}
}
The JPA config
This is my persistence.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
version="1.0">
<persistence-unit name="transactions-optional">
<provider>org.datanucleus.api.jpa.PersistenceProviderImpl</provider>
<class>com.vicparkhoney.backend.bean.MyTable</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
<property name="datanucleus.mapping.Schema" value="vicparkhoney"/>
<property name="datanucleus.NontransactionalRead" value="true"/>
<property name="datanucleus.NontransactionalWrite" value="true"/>
<property name="datanucleus.ConnectionURL" value="appengine"/>
</properties>
</persistence-unit>
</persistence>
The Log
I am wondering if there is a way to get more info in the logs as to what the exact SQL is that DataNucleus is running. I used:
DataNucleus.level=FINEST
DataNucleus.Datastore.Native=FINEST
The logs:
20:08:55.937
org.datanucleus.query.JPQLSingleStringParser <init>: JPQL Single-String with "select n from MyTable n where n.name = 'Name1'"
20:08:56.141
org.datanucleus.store.query.AbstractJPQLQuery compileInternal: JPQL Query : Compiling "SELECT n FROM MyTable n WHERE n.name = 'Name1'"
20:08:56.339
org.datanucleus.store.query.AbstractJPQLQuery compileInternal: JPQL Query : Compile Time = 198 ms
20:08:56.340
org.datanucleus.store.query.AbstractJPQLQuery compileInternal: QueryCompilation:
[from:ClassExpression(alias=n)]
[filter:DyadicExpression{PrimaryExpression{n.name} = Literal{Name1}}]
[symbols: n type=com.vicparkhoney.backend.bean.MyTable]
20:08:56.342
com.google.appengine.datanucleus.query.JPQLQuery performExecute: JPQL Query : Executing "SELECT n FROM MyTable n WHERE n.name = 'Name1'" ...
20:08:56.342
com.google.appengine.datanucleus.MetaDataValidator validate: Performing appengine-specific metadata validation for com.vicparkhoney.backend.bean.MyTable
20:08:56.344
com.google.appengine.datanucleus.MetaDataValidator validate: Finished performing appengine-specific metadata validation for com.vicparkhoney.backend.bean.MyTable
20:08:56.628
org.datanucleus.store.StoreDataManager registerStoreData: Managing Persistence of Class : com.vicparkhoney.backend.bean.MyTable [Table : com.vicparkhoney.backend.bean.MyTable, InheritanceStrategy : new-table]
20:08:57.342
com.google.appengine.datanucleus.query.JPQLQuery performExecute: Query compiled as : Kind=com.vicparkhoney.backend.bean.MyTable Filter : __key__=mytable("Name1") [QUERY-TYPE=NORMAL]
20:08:57.439
com.google.appengine.datanucleus.query.DatastoreQuery performExecute: Executing query in datastore for SELECT n FROM MyTable n WHERE n.name = 'Name1'
20:08:58.930
com.google.appengine.datanucleus.DatastoreConnectionFactoryImpl$DatastoreManagedConnection <init>: Created ManagedConnection using DatastoreService = com.google.appengine.api.datastore.DatastoreServiceImpl@9355b6
20:08:58.937
org.datanucleus.store.connection.ConnectionManagerImpl allocateConnection: Connection added to the pool : com.google.appengine.datanucleus.DatastoreConnectionFactoryImpl$DatastoreManagedConnection@56c36c for key=org.datanucleus.ObjectManagerImpl@15f65a1 in factory=ConnectionFactory:nontx[com.google.appengine.datanucleus.DatastoreConnectionFactoryImpl@3817d7]
20:08:58.938
com.google.appengine.datanucleus.query.JPQLQuery performExecute: JPQL Query : Execution Time = 2596 ms
20:08:59.150
org.datanucleus.store.connection.ConnectionManagerImpl closeAllConnections: Connection found in the pool : com.google.appengine.datanucleus.DatastoreConnectionFactoryImpl$DatastoreManagedConnection@56c36c for key=org.datanucleus.ObjectManagerImpl@15f65a1 in factory=ConnectionFactory:nontx[com.google.appengine.datanucleus.DatastoreConnectionFactoryImpl@3817d7] but owner object closing so closing connection
20:08:59.220
org.datanucleus.store.connection.ConnectionManagerImpl$1 managedConnectionPostClose: Connection removed from the pool : com.google.appengine.datanucleus.DatastoreConnectionFactoryImpl$DatastoreManagedConnection@56c36c for key=org.datanucleus.ObjectManagerImpl@15f65a1 in factory=ConnectionFactory:nontx[com.google.appengine.datanucleus.DatastoreConnectionFactoryImpl@3817d7]
20:08:59.220
org.datanucleus.ObjectManagerImpl disconnectObjectProvidersFromCache: Level 1 Cache cleared
20:08:59.223
org.datanucleus.ObjectManagerImpl close: Object Manager "org.datanucleus.ObjectManagerImpl@15f65a1" closed
