The user interface leaves a lot to be desired, but my Google Maps mashup went live today. If you want to see where I’ve taught courses over the past three years, you can go here and see.
All of the data is stored in a very simple database consisting of three tables: Courses, Locations, and Clients. Each of those tables has a single integer primary key and columns to represent the details. There is a many-to-one relationship between clients and courses and between locations and courses, too
I didn’t write any SQL to populate the tables or access the data. Instead, I used the Java 5 annotations and the Java Persistence API to annotate my entities.
Here’s the Client class:
@Entity
@Table(name="clients")
public class Client {
@GeneratedValue
@Id private int id;
private String name;
@OneToMany(mappedBy="client")
private Set<Course> courses = new HashSet<Course>();
// ... all the required gets and sets ...
}
and the Location:
@Entity
@Table(name="locations")
public class Location {
@Id
@GeneratedValue
private int id;
private String city;
private String state;
private double latitude;
private double longitude;
@OneToMany(mappedBy="location")
private Set<Course> courses = new HashSet<Course>();
// ... gets and sets, toString, equals, etc. ...
}
and finally the Courseclass itself:
@Entity
@Table(name="courses")
public class Course {
@GeneratedValue
@Id private int id;
private String title;
@Column(name="start_date")
@Temporal(TemporalType.DATE)
private Date startDate;
@Column(name="end_date")
@Temporal(TemporalType.DATE)
private Date endDate;
@ManyToOne(cascade=CascadeType.PERSIST,fetch=FetchType.EAGER)
private Client client;
@ManyToOne(cascade=CascadeType.PERSIST,fetch=FetchType.EAGER)
private Location location;
// ... gets and sets, etc. ...
}
Then I configured my persistence.xml file (required for JPA) to use Hibernate as my persistence provider.
<?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="TrainingPU" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<properties>
<property name="hibernate.connection.driver_class"
value="com.mysql.jdbc.Driver"/>
<property name="hibernate.connection.url"
value="jdbc:mysql://localhost/training"/>
<property name="hibernate.connection.username" value="..."/>
<property name="hibernate.connection.password" value="..."/>
<property name="hibernate.show_sql" value="false"/>
</properties>
</persistence-unit>
</persistence>
No doubt I’ll eventually modify that to use a JNDI lookup for a datasource, but this was an easy way to get started.
Access to the entities requires an EntityManager, which in turn requires an EntityManagerFactory. I wrote a CourseDAO interface and a JpaCourseDAO implementation class to act as the persistence layer. The interface is simple:
public interface CourseDAO {
void saveCourse(Course course);
List<Course> getAllCourses();
List<Course> getCoursesByClient(String clientName);
List<Course> getCoursesByLocation(String city, String state);
List<Course> getCoursesByYear(int year);
List<Client> getAllClients();
Client getClientByName(String name);
void saveClient(Client client);
List<Location> getAllLocations();
Location getLocationByCityAndState(String city, String state);
void saveLocation(Location loc);
}
The implementation class, JpaCourseDAO, has an attribute of type EntityManager, along with a set method that is used by Spring 2.0. Here’s that class:
@Repository
public class JpaCourseDAO implements CourseDAO {
private EntityManager em;
@PersistenceContext
public void setEntityManager(EntityManager em) {
this.em = em;
}
public List<Course> getAllCourses() {
return em.createQuery("select c from Course c").getResultList();
}
public List<Course> getCoursesByClient(String clientName) {
return em.createQuery(
"select c from Course c where c.client.name=?1")
.setParameter(1, clientName)
.getResultList();
}
public List<Course> getCoursesByLocation(String city, String state) {
return em.createQuery(
"select c from Course c where c.location.city=?1 and c.location.state=?2")
.setParameter(1, city)
.setParameter(2, state)
.getResultList();
}
public List<Course> getCoursesByYear(int year) {
Calendar cal = Calendar.getInstance();
cal.set(Calendar.MONTH,Calendar.JANUARY);
cal.set(Calendar.DAY_OF_MONTH, 1);
cal.set(Calendar.YEAR, year);
Date jan1 = cal.getTime();
cal.set(Calendar.MONTH, Calendar.DECEMBER);
cal.set(Calendar.DAY_OF_MONTH, 31);
Date dec31 = cal.getTime();
return em.createQuery(
"select c from Course c where c.startDate between ?1 and ?2")
.setParameter(1, jan1, TemporalType.DATE)
.setParameter(2, dec31, TemporalType.DATE)
.getResultList();
}
public void saveCourse(Course course) {
em.persist(course);
}
public List<Client> getAllClients() {
return em.createQuery("select c from Client c").getResultList();
}
public Client getClientByName(String name) {
List<Client> matches = em.createQuery(
"select c from Client c where c.name=?1")
.setParameter(1, name)
.getResultList();
return matches.size() > 0 ? matches.get(0) : null;
}
public void saveClient(Client client) {
em.persist(client);
}
public List<Location> getAllLocations() {
return em.createQuery("select loc from Location loc").getResultList();
}
public Location getLocationByCityAndState(String city, String state) {
List<Location> matches = em.createQuery(
"select loc from Location loc where loc.city=?1 and loc.state=?2")
.setParameter(1, city)
.setParameter(2, state)
.getResultList();
return matches.size() > 0 ? matches.get(0) : null;
}
public void saveLocation(Location loc) {
em.persist(loc);
}
}
Most of it is very straightforward JPA, but I did have to look up how to deal with the dates and times. Java has always had a dicey relationship with temporal data, but at least this works.
As recommended, the DAO class doesn’t have anything in it about transactions. Instead, I have an interface called CourseService and a class called CourseServiceImpl that sit in front of the DAO:
public interface CourseService {
void addCourse(Course course);
List<Course> getCourses();
List<Course> getCoursesByYear(int year);
List<Course> getCoursesByClient(String clientName);
List<Client> getClients();
List<Location> getLocations();
}
and
@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
public class CourseServiceImpl implements CourseService {
private CourseDAO dao;
public void setCourseDAO(CourseDAO dao) {
this.dao = dao;
}
@Transactional(propagation = Propagation.REQUIRED)
public void addCourse(Course course) {
Client client = course.getClient();
Location loc = course.getLocation();
Client existingClient =
dao.getClientByName(client.getName());
if (existingClient != null) {
existingClient.getCourses().add(course);
course.setClient(existingClient);
} else {
client.getCourses().add(course);
dao.saveClient(client);
}
Location existingLoc =
dao.getLocationByCityAndState(loc.getCity(), loc.getState());
if (existingLoc != null) {
existingLoc.getCourses().add(course);
course.setLocation(existingLoc);
} else {
loc.getCourses().add(course);
dao.saveLocation(loc);
}
dao.saveCourse(course);
}
public List<Course> getCourses() {
return dao.getAllCourses();
}
public List<Course> getCoursesByClient(String clientName) {
return dao.getCoursesByClient(clientName);
}
public List<Course> getCoursesByYear(int year) {
return dao.getCoursesByYear(year);
}
public List<Client> getClients() {
return dao.getAllClients();
}
public List<Location> getLocations() {
return dao.getAllLocations();
}
}
Again, the idea is that the service has an attribute of type CourseDAO, which is injected by Spring. By using Spring’s transactional annotations, I was able to make all the read methods use the “supports” type, while leaving room for a “required” transaction on the addCourse method.
To fit all the pieces together, here’s the Spring configuration file, which I called applicationContext.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-2.0.xsd"
xmlns:tx="http://www.springframework.org/schema/tx">
<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalEntityManagerFactoryBean">
<property name="persistenceUnitName" value="TrainingPU" />
</bean>
<bean id="transactionManager"
class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>
<bean id="courseDao" class="com.kousenit.dao.JpaCourseDAO" />
<bean id="courseService"
class="com.kousenit.service.CourseServiceImpl">
<property name="courseDAO" ref="courseDao" />
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
<bean
class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor"
/>
<bean
class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />
</beans>
Craig Walls might find a lot of that file familiar, seeing how I adapted it directly from the examples in his most excellent Spring in Action book.
Actually, now that I mention it, Mr. Walls will probably find the whole set-up pretty familiar. My whole persistence layer is based on what I learned from his book. 🙂
To populate the tables, I used the persistence layer as a regular Java SE application rather that working with anything web related yet. To populate the locations table, I also needed latitude and longitude information for each city.
Of course, first I needed a list of all the courses and their information. Although what I needed is in my QuickBooks data, that’s not exactly an easy API to work with (even assuming such an API exists and is available). I debated various approaches, but in the end I just entered the data for each course into a spreadsheet and then saved it as CSV (comma-separated values) file. I know Groovy can access spreadsheet data directly, but it figured it was just easier to go directly to text.
The “hard” (okay, not so hard, but rather detailed) part, was reading and parsing the text data:
def readData() {
def df = new SimpleDateFormat('MM/dd/yyyy')
def dataFile = new File('Course_locations.csv')
def lines = dataFile.readLines()
for (line in lines) {
def elements = line.trim().split(',')
if (elements.size() == 8 && !elements[0].startsWith("#")) {
def loc = new Location()
loc.city = elements[5]
loc.state = elements[6]
if (loc.latitude == 0 && loc.longitude == 0) {
def query = "q=${loc.city},+${loc.state}&output=csv&key=${key}"
def url_string = base + '?q=' +
',+' + URLEncoder.encode(loc.city,"UTF-8") +
',+' + loc.state + "&output=csv&key=${key}"
def results = new URL(url_string).text.split(',')
loc.latitude = results[-2].toDouble();
loc.longitude = results[-1].toDouble();
}
def client = new Client()
client.name = elements[3]
def course = new Course()
course.title = elements[4]
course.startDate = df.parse(elements[1])
course.endDate = df.parse(elements[2])
course.rate = elements[7].toDouble();
course.client = client
course.location = loc
courses << course
}
}
}
The cool parts are (1) how I can use my regular Java classes, Location, Client, and Course, right in the Groovy script, and (2) the way I can build a URL string to access the geocoder at Google. The Java code to do the same thing would be a lot longer and much, much uglier.
The Groovy code to save the resulting objects is almost trivial, once Spring is loaded:
def ctx = new FileSystemXmlApplicationContext('src/applicationContext.xml')
def dao = ctx.getBean('courseService')
def saveCourses() {
courses.each { course ->
dao.addCourse(course)
}
}
I played around with various business methods, figuring out how many training days I did in each year, looking a revenue, etc, but sooner or later I had to get back to Google Maps. After all, that was supposed the reason I started this whole exercise.
That’s when I hit a very fundamental problem. Like a good object-oriented programmer, I’d gone to all this trouble to retrieve my data as collections of Java objects. That’s fine if my user interface is in JavaServer Pages, or even Groovy. But JavaScript can’t read Java objects.
Nuts.
This isn’t a new problem, of course. I think the Ajax in Action book even has a discussion about it, to some degree. But this was the first time I really had to deal with it.
I think the potential solutions are:
1. Java wins. In other words, use my server-side Java to generate JavaScript as though it was just any other text, plugging in values from my objects as necessary. It’s kludgy, but doable.
2. HTML wins. Write the server-side Java code to return formatted strings, which the JavaScript can just read like any other text.
3. JavaScript wins. Find a way to convert my Java objects into JavaScript objects. To my understanding, that means JSON. I was tempted to implement a “toJSONString” method in all my entities, but haven’t yet. I still may go that way eventually.
4. Nobody wins. That means XML, of course. As the web services world has already recognized, every programming language can read and write formatted text. Even better, every language these days has a set of libraries for dealing with XML. Java’s approach has to be one of the worst, leading to reams of highly fragile code, but it can be done.
In the end, for this first iteration, I chose option (1). My application is a simple JSP, using JSTL tags to loop over the data and generate the JavaScript.
To make the data available I first wrote a context listener that loaded all the course data into application scope when the web application is first loaded:
public class CourseLoader implements ServletContextListener {
private ApplicationContext ctx =
new ClassPathXmlApplicationContext("applicationContext.xml");
private CourseService dao = (CourseService) ctx.getBean("courseService");
public void contextDestroyed(ServletContextEvent sce) {
ServletContext sc = sce.getServletContext();
sc.removeAttribute("locations");
sc.removeAttribute("courses");
}
public void contextInitialized(ServletContextEvent sce) {
ServletContext sc = sce.getServletContext();
sc.setAttribute("locations",dao.getLocations());
sc.setAttribute("courses", dao.getCourses());
}
}
Then I wrote the JSP that does the actual mapping work. It has a <div> to hold the map, as Google Maps requires. Here’s the JSP that generates the JavaScript code. Near the top I have a taglib directive to bring in the core JSTL library:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
Then I mix JavaScript code into a forEach loop to put markers on the map:
<script type="text/javascript" src="http://www.google.com/jsapi?key=..."></script>
<script type="text/javascript">
google.load("maps", "2.x");
google.load("search","1");
// Call this function when the page has been loaded
function initialize() {
var map = new google.maps.Map2(document.getElementById("map"));
var home = new google.maps.LatLng(41.612958, -72.452903);
map.setCenter(home, 4);
var marker;
var html;
var polyline;
var location;
var polylineOptions = {geodesic:true};
<c:forEach var="course" items="${courses}">
location = new google.maps.LatLng(
${course.location.latitude},${course.location.longitude});
marker = new google.maps.Marker(location);
google.maps.Event.addListener(marker, "click", function() {
map.openInfoWindowHtml(
new google.maps.LatLng(${course.location.latitude},${course.location.longitude}),
"<b>${course.title}</b><br />" +
"<em>${course.location.city}, ${course.location.state}</em><br />" +
"${course.startDate} -- ${course.endDate}"
);
});
map.addOverlay(marker);
polyline = new google.maps.Polyline(new Array(home,location),
"#ff0000",2,0.5,polylineOptions);
map.addOverlay(polyline);
</c:forEach>
map.addControl(new google.maps.LargeMapControl());
map.addControl(new google.maps.MapTypeControl());
map.addControl(new google.maps.OverviewMapControl());
}
google.setOnLoadCallback(initialize);
</script>
and there you have it. If you go to my page and do a view source, you can see the tons of repeated code generated by the JSTL foreach loop, <c:forEach>. It’s certainly not the most elegant way to go, but it seems to do the job.
My next plan is to do option (4) and use XML as the common language. To avoid all the horrid Java code required, though, I’m going to use a Groovy MarkUp builder. As a sample, the code will look something like this little experiment, which translates all the locations into XML:
def locationsToXML() {
def buffer = new java.io.StringWriter()
def builder = new groovy.xml.MarkupBuilder(buffer)
def locsXML = builder.locations {
dao.locations.each { loc ->
builder.location {
city loc.city
state loc.state
latitude loc.latitude
longitude loc.longitude
}
}
}
return buffer.getBuffer().toString()
}
Now that’s how easy it ought to be to convert to XML. Rather than finish all that, though, I figured I might as well get the application up and running, so feel free to have a look and let me know what you think.
I definitely have to give a shout-out to MyEclipse, though. Their editor helped me build the initial JPA and Spring configuration files and added the libraries I needed. The fact that the asm jar files in Spring 2.0 and Hibernate 3.2 conflict isn’t really their fault.
Actually, that’s a post for another day.
Leave a Reply