JPA does not return any data from CloudSQL

Question

I have project based on maven for deployment on GoogleAppEngine. I have local MySQL DB (for development) and remote CloudSQL DB (for production)
My code is based on google github
Data inside CloudSQL and MySQL are identical. I have a problem when trying to retrieve data via named query on production - list of size 0 is returned. On local MySQL DB code returns data correctly. I even tried to retrieve data from ClodSQL using standard java.sql.Connection and I was able to retrieve data. No data are returned only when requested from JPA. Named query used:

@NamedQueries({(name = "Device.getDevices", = "SELECT d FROM Device d")

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-device">
    <class>commander.apprecorder.database.pojos.device.Device</class>

    <properties>
        <property name="javax.persistence.jdbc.user" value="root"/>
        <property name="datanucleus.autoCreateSchema" value="true"/>
    </properties>
</persistence-unit>
</persistence>

Content of pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<packaging>war</packaging>
<version>1.0-SNAPSHOT</version>

<groupId>group</groupId>
<artifactId>art</artifactId>

<properties>
    <app.id>appid</app.id>
    <app.version>version</app.version>
    <appengine.version>1.9.30</appengine.version>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <gcloud.plugin.version>2.0.9.74.v20150814</gcloud.plugin.version>

    <objectify.version>5.1.5</objectify.version>
    <guava.version>18.0</guava.version>

    <datanucleus.jpa.version>3.1.1</datanucleus.jpa.version>

    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.showDeprecation>true</maven.compiler.showDeprecation>
    <cloudsql.device.url>
        jdbc:google:mysql://some:instance:name/db?user=xxx
    </cloudsql.device.url>
    <cloudsql.device.url.dev>jdbc:mysql://localhost/db?user=x</cloudsql.device.url.dev>

    <datanucleus-core.version>4.0.0-release</datanucleus-core.version>
    <datanucleus-maven-plugin.version>4.0.0-release</datanucleus-maven-plugin.version>
    <datanucleus-accessplatform-jpa-rdbms.version>4.0.0-release</datanucleus-accessplatform-jpa-rdbms.version>
    <javax.persistence.version>2.1.0</javax.persistence.version>
</properties>

<prerequisites>
    <maven>3.1.0</maven>
</prerequisites>

<dependencies>
    <!-- Compile/runtime dependencies -->
    <dependency>
        <groupId>com.google.appengine</groupId>
        <artifactId>appengine-api-1.0-sdk</artifactId>
        <version>${appengine.version}</version>
    </dependency>
    <dependency>
        <groupId>com.google.appengine.tools</groupId>
        <artifactId>appengine-gcs-client</artifactId>
        <version>0.5</version>
    </dependency>
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>servlet-api</artifactId>
        <version>2.5</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>jstl</groupId>
        <artifactId>jstl</artifactId>
        <version>1.2</version>
    </dependency>

    <dependency>
        <groupId>org.json</groupId>
        <artifactId>json</artifactId>
        <version>20151123</version>
    </dependency>


    <!--JPA start -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.25</version>
    </dependency>
    <dependency>
        <groupId>org.datanucleus</groupId>
        <artifactId>datanucleus-accessplatform-jpa-rdbms</artifactId>
        <version>${datanucleus-accessplatform-jpa-rdbms.version}</version>
        <type>pom</type>
    </dependency>

    <dependency>
        <groupId>org.datanucleus</groupId>
        <artifactId>javax.persistence</artifactId>
        <version>${javax.persistence.version}</version>
    </dependency>
    <!--JPA end -->

    <!-- [START Objectify_Dependencies] -->
    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
        <version>${guava.version}</version>
    </dependency>
    <dependency>
        <groupId>com.googlecode.objectify</groupId>
        <artifactId>objectify</artifactId>
        <version>${objectify.version}</version>
    </dependency>
    <!-- [END Objectify_Dependencies] -->

    <!-- Test Dependencies -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.mockito</groupId>
        <artifactId>mockito-all</artifactId>
        <version>2.0.2-beta</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.google.appengine</groupId>
        <artifactId>appengine-testing</artifactId>
        <version>${appengine.version}</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>com.google.appengine</groupId>
        <artifactId>appengine-api-stubs</artifactId>
        <version>${appengine.version}</version>
        <scope>test</scope>
    </dependency>
</dependencies>

<build>

    <!-- for hot reload of the web application-->
    <outputDirectory>${project.build.directory}/${project.build.finalName}/WEB-INF/classes</outputDirectory>
    <plugins>
        <plugin>
            <groupId>org.codehaus.mojo</groupId>
            <artifactId>versions-maven-plugin</artifactId>
            <version>2.1</version>
            <executions>
                <execution>
                    <phase>compile</phase>
                    <goals>
                        <goal>display-dependency-updates</goal>
                        <goal>display-plugin-updates</goal>
                    </goals>
                </execution>
            </executions>
        </plugin>
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <version>3.2</version>
            <artifactId>maven-compiler-plugin</artifactId>
            <configuration>
                <source>1.7</source>
                <target>1.7</target>
            </configuration>
            <dependencies>
                <dependency>
                    <groupId>org.datanucleus</groupId>
                    <artifactId>datanucleus-core</artifactId>
                    <version>${datanucleus-core.version}</version>
                    <scope>compile</scope>
                </dependency>
            </dependencies>
        </plugin>

        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
            <artifactId>maven-war-plugin</artifactId>
            <version>2.6</version>
            <configuration>
                <archiveClasses>true</archiveClasses>
                <webResources>
                    <!-- in order to interpolate version from pom into appengine-web.xml -->
                    <resource>
                        <directory>${basedir}/src/main/webapp/WEB-INF</directory>
                        <filtering>true</filtering>
                        <targetPath>WEB-INF</targetPath>
                    </resource>
                </webResources>
            </configuration>
        </plugin>

        <plugin>
            <groupId>com.google.appengine</groupId>
            <artifactId>appengine-maven-plugin</artifactId>
            <version>${appengine.version}</version>
            <configuration>
                <enableJarClasses>false</enableJarClasses>
                <version>${app.version}</version>
                <!-- Comment in the below snippet to bind to all IPs instead of just localhost -->
                <!-- address>0.0.0.0</address>
                <port>8080</port -->
                <!-- Comment in the below snippet to enable local debugging with a remote debugger
                     like those included with Eclipse or IntelliJ -->
                <!-- jvmFlags>
                  <jvmFlag>-agentlib:jdwp=transport=dt_socket,address=8000,server=y,suspend=n</jvmFlag>
                </jvmFlags -->
            </configuration>
        </plugin>
        <plugin>
            <groupId>com.google.appengine</groupId>
            <artifactId>gcloud-maven-plugin</artifactId>
            <version>${gcloud.plugin.version}</version>
            <configuration>
                <set_default>true</set_default>
            </configuration>
        </plugin>
        <!--DB start-->
        <plugin>
            <groupId>org.datanucleus</groupId>
            <artifactId>datanucleus-maven-plugin</artifactId>
            <version>${datanucleus-maven-plugin.version}</version>
            <configuration>
                <api>JPA</api>
                <persistenceUnitName>transactions-optional-device</persistenceUnitName>
                <fork>false</fork>
                <!--<persistenceUnitName>Demo</persistenceUnitName>-->
                <!--<log4jConfiguration>${basedir}/log4j.properties</log4jConfiguration>-->
                <verbose>true</verbose>
            </configuration>
            <executions>
                <execution>
                    <phase>process-classes</phase>
                    <goals>
                        <goal>enhance</goal>
                    </goals>
                </execution>
            </executions>
            <dependencies>
                <dependency>
                    <groupId>org.datanucleus</groupId>
                    <artifactId>datanucleus-core</artifactId>
                    <version>${datanucleus-core.version}</version>
                </dependency>
            </dependencies>
        </plugin>
        <!--JPA end-->
    </plugins>
</build>

And servlet for JPA

@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    resp.setContentType("text/plain");

    Map<String, String> properties = new HashMap();
    if (SystemProperty.environment.value() == SystemProperty.Environment.Value.Production) {
        properties.put("javax.persistence.jdbc.driver", "com.mysql.jdbc.GoogleDriver");
        properties.put("javax.persistence.jdbc.url", System.getProperty("cloudsql.device.url"));
    } else {
        properties.put("javax.persistence.jdbc.driver", "com.mysql.jdbc.Driver");
        properties.put("javax.persistence.jdbc.url", System.getProperty("cloudsql.device.url.dev"));
    }

    // List all the rows.

        EntityManagerFactory emf = Persistence.createEntityManagerFactory(
                "transactions-optional-device", properties);
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        resp.getWriter().println("performing query");
        List<Device> result = em.createNamedQuery("Device.getDevices", Device.class).getResultList();
        resp.getWriter().println("Starting to list devices - " + result.size());
        for (Device g : result) {
            resp.getWriter().println(g.getA() + "_" + g.getB());
        }

        em.getTransaction().commit();
        em.close();

}

There is no error inside app engine log. Can you please tell me what is wrong? Thanks :)

and in the LOG of your JPA provider ? what SQL is invoked? what else does the LOG say? — Neil Stockton, Jan 06 '16 at 17:45
You have totally inconsistent versions of jars there ... datanucleus-api-jpa v3.1.1 with datanucleus-core v4.0.0! And why you are using an old version there I've no real idea. The latest version I see are 4.1.7 of datanucleus-accessplatform-jpa-rdbms — Neil Stockton, Jan 06 '16 at 18:48
I have used that Google project you mentioned and all I needed to do was change the datanucleus-accessplatform-jpa-rdbms dependency to use a recent version ... in my case I used 4.1.5 but no reason to think that 4.1.7 will be any different. No need for any "datanucleus-core", "datanucleus-api-jpa" dependencies at all, so no idea why you have those. — , Jan 07 '16 at 09:57

score -1 · Answer 1 · answered Jan 06 '16 at 18:21

-1

I set log level to ALL and found out that Datanucleus was trying to access datastore. It created new table inside datastore and retrieved its content - no data.
I have no idea why it was trying to access datastore and was not using CloudSQL, but nevermind...
I replaced DataNucles for Hibernate by tutorial inside google github and it worked on first time
Migration was easy, only persistance.xml and pom.xml had to be modified.

answered Jan 06 '16 at 18:21

Ajvo

148
8

1

This doesn't answer the "question", or attempt to understand why you get a problem, simply bypassing it. Also it would be trying to access the datastore because your configuration told it to do so ... you have a persistence property defined to do just that in your persistence.xml so if you don't want it to create a table then why ask it to? – Neil Stockton Jan 06 '16 at 18:41

JPA does not return any data from CloudSQL

1 Answers1