Google Appengine Cloud SQL issue with Spring Hibernate

Question

I have a working Spring Hibernate Application running on Amazon EC2 with MySQL. I am thinking of porting my application to Google App Engine as Google now support MySQL with Google Could SQL.

So configure my existing application to Google App Engine Web Application, then compile the code without any error. I have not changed anything to my existing application and it compiled and also created the required tables and the server started successfully.

However, when running application that access the database via hibernate, I am getting the following error.

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Cannot open connection
at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:596)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:371)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:335)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:105)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy30.findById(Unknown Source)
at com.openentry.catgen.services.impl.WebsiteServiceImpl.getMasterDomain(WebsiteServiceImpl.java:99)

I am using annotations for my entity classes.

Is there anything I need changing for this?

below is my applicationContext.xml

<bean id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource"
    p:driverClassName="${jdbc.driverClassName}" p:url="${jdbc.url}"
    p:username="${jdbc.username}" p:password="${jdbc.password}" />

<bean id="sessionFactory"
    class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
    p:dataSource-ref="dataSource" p:configurationClass="org.hibernate.cfg.AnnotationConfiguration"
    p:packagesToScan="com.package.app.entities">
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${hibernate.dialect}</prop>
            <prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
            <prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
            <prop key="hibernate.generate_statistics">${hibernate.generate_statistics}</prop>
            <prop key="hibernate.connection.useUnicode">${hibernate.connection.useUnicode}</prop>
            <prop key="hibernate.connection.characterEncoding">${hibernate.connection.characterEncoding}</prop>
            <prop key="hibernate.connection.charSet">${hibernate.connection.charSet}</prop>
            <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
        </props>
    </property>

</bean>

<tx:annotation-driven />

<bean id="transactionManager"
    class="org.springframework.orm.hibernate3.HibernateTransactionManager"
    p:sessionFactory-ref="sessionFactory" />

<bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
    <constructor-arg>
        <ref bean="sessionFactory" />
    </constructor-arg>
</bean>

Answer 1

To run Google Cloud SQL on Eclipse localhost and on appengine instance, you must checklist the following points.

1. Login to your eclipse with the same google account on which your Cloud SQL instance is created.
2. Right Click on project properties go to Google>Appengine check the Enable Google Cloud SQL instance

3. Choose MySQL instance for localhost and configure its values.

   Hostname : localhost
   Database Name : yourdatabasename
   Port No : 3306
   Username : yourMySQLUserName
   Password : yourPassword
   Path to MySQL JDBC Jar : Path where your mysql-connector-java-x.x.xx.jar   // I usually put this jar on the WEB-INF/lib
   

4. Configure the Google Cloud SQL Instance as well:

   Instance Name : something:something     // This you will get under Google Cloud SQL tab under Google API Console
   Database Name : yourdatabasename
   Username : yourMySQLUserName
   Password : yourPassword
   

5. Just copy your mysql-connector-java-x.x.xx.jar file and paste it under this location of your Appengine SDK in Eclipse

   // This path is shown for Eclipse
   
   D:\MyEclipse\plugins\com.google.appengine.eclipse.sdkbundle_1.7.2.1\appengine-java-sdk-1.7.2.1\lib\impl\
   

6. The Driver ClassName and Database Access URL should be changed to following values.

   AppengineDriver Class Name : com.google.appengine.api.rdbms.AppEngineDriver
   Database Access URL : jdbc:google:rdbms://instance_name/database_name
   e.g. jdbc:google:rdbms://XXXXXX:xxxxx/XXX_databasename
   user : username   // Your Database User by default its root
   password : password  // Your Database Password by default its blank in GAE Cloud SQL
   

7. By following all the things you can easily configure Google Cloud SQL in Eclipse.

Answer 2

I believe it is a connection problem.Either you write database configuration wrong or the connection between the database and application has data loss.