Google App Engine, GWT, Spring 3, JPA and Google Spreadsheet
Posted by ice09 on June 7, 2009
If you are interested in Spring 3 on the GAE, there is a newer post about just this topic (and some REST!).
The previous post describes the main steps, this post just extends two classes and adds a “sync function”, which allows for syncronizing with data im a Google Spreadsheet.
The github project is here.
Caveats
- There is a known problem with cookie handling, therefore the static block is used.
- The spreadsheet document must be named tabledata.
- The sample is quite stupid, to test the sync function, the usename sync must be used.
GreetingsServiceImpl.java
package com.commons.ssheet.server; import java.io.IOException; import java.net.URL; import java.util.ArrayList; import java.util.Collection; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityTransaction; import javax.persistence.PersistenceException; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.orm.jpa.JpaCallback; import org.springframework.orm.jpa.JpaTemplate; import org.springframework.stereotype.Component; import com.commons.ssheet.client.GreetingService; import com.google.gdata.client.http.GoogleGDataRequest; import com.google.gdata.client.spreadsheet.SpreadsheetService; import com.google.gdata.data.spreadsheet.CellEntry; import com.google.gdata.data.spreadsheet.CellFeed; import com.google.gdata.data.spreadsheet.SpreadsheetEntry; import com.google.gdata.data.spreadsheet.SpreadsheetFeed; import com.google.gdata.data.spreadsheet.WorksheetEntry; import com.google.gdata.util.ServiceException; import com.google.gwt.user.server.rpc.RemoteServiceServlet; @SuppressWarnings("serial") @Component public class GreetingServiceImpl extends RemoteServiceServlet implements GreetingService { static { System.setProperty(GoogleGDataRequest.DISABLE_COOKIE_HANDLER_PROPERTY, "true"); } private boolean first; @Autowired private EntityManager entityManager; @Autowired private SpreadsheetService spreadsheetService; @Autowired private URL metafeedUrl; private String username = "YOURUSERNAME"; private String password = "YOURPASSWORD"; public JpaTemplate getTemplate() { return new JpaTemplate(entityManager); } public String greetServer(String input) { try { return getData(input); } catch (IOException e) { e.printStackTrace(); } catch (ServiceException e) { e.printStackTrace(); } return null; } private String getData(String input) throws IOException, ServiceException { String all = ""; if (input.equals("sync")) { first = false; } for (Customer customer : getCustomers()) { if (!first) { removeCustomer(customer); } } first = true; persistCustomer(createCustomer(input)); if (input.equals("sync")) { for (Customer customer : syncWithSpredsheet()) { persistCustomer(customer); } } for (Customer customer : getCustomers()) { all += "id: " + customer.getId() + " - firstname: " + customer.getFirstName() + " - name:" + customer.getLastName() + "<br>"; } return all; } private void removeCustomer(Customer customer) { EntityTransaction trx = entityManager.getTransaction(); trx.begin(); entityManager.remove(customer); trx.commit(); } private Customer createCustomer(String input) { Customer newCustomer = new Customer(); newCustomer.setFirstName(input + System.currentTimeMillis()); newCustomer.setLastName(input + System.currentTimeMillis()); return newCustomer; } private void persistCustomer(Customer customer) { EntityTransaction trx = entityManager.getTransaction(); trx.begin(); entityManager.persist(customer); trx.commit(); } private Collection<Customer> getCustomers() { return getTemplate().execute(new JpaCallback<Collection<Customer>>() { @Override public Collection<Customer> doInJpa(EntityManager arg0) throws PersistenceException { return (Collection<Customer>) entityManager.createQuery("SELECT cust FROM com.commons.ssheet.server.Customer cust").getResultList(); } }); } private List<Customer> syncWithSpredsheet() throws IOException, ServiceException { spreadsheetService.setUserCredentials(username, password); List<Customer> result = new ArrayList<Customer>(); SpreadsheetFeed feed = (SpreadsheetFeed) spreadsheetService.getFeed(metafeedUrl, SpreadsheetFeed.class); List<SpreadsheetEntry> spreadsheets = feed.getEntries(); for (int i = 0; i < spreadsheets.size(); i++) { SpreadsheetEntry entry = spreadsheets.get(i); if (entry.getTitle().getPlainText().trim().equals("tabledata")) { List<WorksheetEntry> worksheets = entry.getWorksheets(); for (int j = 0; j < worksheets.size(); j++) { WorksheetEntry worksheet = worksheets.get(j); URL cellFeedUrl = worksheet.getCellFeedUrl(); CellFeed cfeed = spreadsheetService.getFeed(cellFeedUrl, CellFeed.class); Customer customer = new Customer(); boolean wasAdded = true; for (CellEntry cell : cfeed.getEntries()) { String value = cell.getCell().getInputValue(); if (cell.getTitle().getPlainText().startsWith("A")) { if (!wasAdded) { result.add(customer); customer = new Customer(); } customer.setFirstName(value); } else if (cell.getTitle().getPlainText().startsWith("B")) { customer.setLastName(value); wasAdded = false; } } result.add(customer); } } } return result; } }
dispatcher-servlet.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <context:component-scan base-package="com.commons" /> <bean id="spreadsheetService" class="com.google.gdata.client.spreadsheet.SpreadsheetService"> <constructor-arg><value>appname</value></constructor-arg> </bean> <bean id="metafeedUrl" class="java.net.URL"> <constructor-arg><value>http://spreadsheets.google.com/feeds/spreadsheets/private/full</value></constructor-arg> </bean> <bean id="entityManager" factory-bean="EMF" factory-method="entityManager" /> <bean class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping"> <property name="mappings"> <value> /googlespreadsheet/greet=gwtController </value> </property> </bean> <bean name="gwtController" class="com.commons.ssheet.server.GWTController"> <property name="remoteService" ref="greetingServiceImpl"/> </bean> </beans>
Jesper said
Hi!
Impressive! I have tried to follow your example…
When run it I get the following error:
31-Jul-2009 11:32:39 com.google.apphosting.utils.jetty.JettyLogger warn
WARNING: Nested in javax.servlet.ServletException: init:
java.lang.NoClassDefFoundError: com/google/gdata/util/ServiceException
at java.lang.Class.getDeclaredConstructors0(Native Method)
…
…
Caused by: java.lang.ClassNotFoundException: com.google.gdata.util.ServiceException
at java.net.URLClassLoader$1.run(URLClassLoader.java:200)
at java.security.AccessController.doPrivileged(Native Method)
…
…
What have i missed?
Thanks,
J
ice09 said
Hi,
sorry, did not get the notification about the comment… Will look into it.
Best,
Alex
ice09 said
Hi Jesper,
did you try to download and use the github download?
It sounds like the google jars are missing, you can get them here:
http://code.google.com/p/gdata-java-client/downloads/list
Best,
Alex
Google Chart API with Gaelyk on the Google App Engine « Ice09 said
[…] Access class already exists (cmp. this post) […]
Nathan and his Open Ideals » links for 2009-12-11 said
[…] Google App Engine, GWT, Spring 3, JPA and Google Spreadsheet « ICE09 . playing with java, scala and… If you are interested in Spring 3 on the GAE, there is a newer post about just this topic (and some REST!). […]