How to handle JPA Many-to-One Relation?

Question

Hi,

I am designing an application for collecting weather data. I have 2 POJO objects "Location" and "Record". Location contains information about latitude and longitude and the current weather conditions, and Record contains all the weather information over time for a specific location thus having a Many-to-one relation with Location. The definition of the classes I have is as follows:

Location.java

@Entity
@Table(name = "location")
@NamedQueries( {
 @NamedQuery(name = "findLocations", query = "SELECT e FROM Location e ORDER BY e.longitude, e.latitude"),
 @NamedQuery(name = "findLocationByLatLong", query = "SELECT e from Location e WHERE e.latitude = :latitude AND e.longitude = :longitude"),
 @NamedQuery(name = "findLocationById", query = "SELECT e from Location e WHERE e.id = :id"),
 @NamedQuery(name = "deleteLocationById", query= "DELETE Location e WHERE e.id = :id"),
 @NamedQuery(name = "updateLocation", query = "UPDATE Location e SET e.lastModifiedDate = :lastModifiedDate WHERE e.id = :id")})

public class Location implements Serializable {

 /**
  * 
  */
 private static final long serialVersionUID = 1L;

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 protected Long id;

 @Column(name="latitude", nullable=false)
 protected String latitude;

 @Column(name="longitude", nullable=false)
 protected String longitude;

 @Column(name="lastModifiedDate")
 @Temporal(TemporalType.TIMESTAMP)
 private Date lastModifiedDate;

 @Column(name="windDirection")
 private float windDirection;

 @Column(name="windSpeed")
 private float windSpeed;

 @Column(name="temperature")
 private float temperature;
}

And Record.java

@Entity
@Table(name = "weatherdata")
@NamedQueries( {
  @NamedQuery(name = "findWeatherRecordById", query = "SELECT e from Record e WHERE e.id = :id"),
  @NamedQuery(name = "findWeatherRecords", query = "SELECT e from Record e WHERE e.parent = :parent") })
public class Record implements Serializable{

 /**
  * 
  */
 private static final long serialVersionUID = 1L;

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 @Column(name="id")
 protected Long id;

 @Column(name="mTime")
 @Temporal(TemporalType.TIMESTAMP)
 private Date mtime;

 @Column(name="windDirection")
 private float windDirection;

 @Column(name="windSpeed")
 private float windSpeed;

 @Column(name="temperature")
 private float temperature;

 @ManyToOne(cascade = { CascadeType.ALL }, targetEntity=Location.class, fetch = FetchType.EAGER)
 @JoinColumn(name="locationId")
 protected Location parent;
}

And my incoming data is in the form of:

latitude,longitude,date,winddirection,windspeed,temperature
36.9822,-122.0153,20100907000000.00,158,2.68,20.57
38.1838,-120.54,20100907000000.00,248,0,26.68
38.3495,-121.9688,20100907000000.00,149,0.45,33.9
38.41935,-121.36029,20100907000000.00,322,0.9,33.9
37.91617,-122.286,20100907000000.00,224,0,24.46
38.587,-121.3162,20100907000000.00,315,0,34.46
36.8717,-121.6555,20100907000000.00,294,3.13,18.34

Now whenever I get a record, I want to insert it in Record table. And as I have a foreign key to Location, I will also add the locationId of Location table. Another thing, Location table is not prepopulated. So whenever a new record comes I first insert it in Location table, and then populate the Record table with the foreign key. And I dont want duplication location entries in Location table. Location table will also contain the most latest temperature, windspeed and winddirection data as you can see.

I am using the following code to accomplish that:

Location loc = handler.getLocation(line);   
//loc.setTemperature(0);

Location dbLoc = null;

try {
    Query q = eManager.createNamedQuery("findLocationByLatLong");
    q.setParameter("latitude", loc.getLatitude());
    q.setParameter("longitude", loc.getLongitude());
    dbLoc = (Location) q.getSingleResult();                     
} catch (Exception e) {
    System.out.println("Location not found! Creating new location");
    Logger.getLogger("WeatherRecorderBean.class").log(Level.WARNING, e.getMessage());
}


Record r = handler.getRecord(line);

if(dbLoc!=null) {
    r.setParent(dbLoc);

    dbLoc.setLastModifiedDate(r.getMtime());//I am doing this so as to know what time the weather change entry is about
    dbLoc.setWindDirection(r.getWindDirection());
    dbLoc.setWindSpeed(r.getWindSpeed());
    dbLoc.setTemperature(r.getTemperature());

    eManager.merge(r);                      
}
else {
    dbLoc = new Location();
    dbLoc.setLatitude(loc.getLatitude());
    dbLoc.setLongitude(loc.getLongitude());
    //eManager.persist(dbLoc);

    r.setParent(dbLoc);                     

    dbLoc.setLastModifiedDate(r.getMtime());
    dbLoc.setWindDirection(r.getWindDirection());
    dbLoc.setWindSpeed(r.getWindSpeed());
    dbLoc.setTemperature(r.getTemperature());

    eManager.merge(r);
    //eManager.merge(dbLoc);

}

But by doing this, what is happening is Locations are being duplicated. Meaning I have multiple entries for same longitude and latitude but with different temperature, windspeed data in the Location table. What I want to accomplish is have a single entry for one latitude and longitude and update the windspeed, temperature and winddirection fields with the latest data.

Please HELP!

Answer 1

You're cascading ALL operations from Record to Location so when you merge a new Record and its new parent Location, there is no need to merge a transient Location again (or you'll get duplicate lines).

I've put some comments in your code below (I didn't fix everything, there are IMO more problems but the suggested changes should at least remove the creation of duplicates Location entries):

if(dbLoc==null) {
  dbLoc = new Location();
  dbLoc.setLatitude(loc.getLatitude());
  dbLoc.setLongitude(loc.getLongitude());
  r.setParent(dbLoc);

  // add changes on the dbLoc here

  eManager.merge(r);  // here you're merging r and the dbLoc 

  //loc.setLastModifiedDate(r.getMtime()); // why are you modifying the line from the file?
  //loc.setWindDirection(r.getWindDirection());
  //loc.setWindSpeed(r.getWindSpeed());
  //loc.setTemperature(r.getTemperature());
  //eManager.persist(loc);    
  //System.out.println("Location id : "+loc.getId());

  //eManager.merge(dbLoc); // here you're merging a transient dbLoc again

}

Answer 2

Solved :-)

public Location saveLocation(Location loc) {
    eManager.clear();
    eManager.setFlushMode(FlushModeType.COMMIT);
    //eManager.setFlushMode(FlushModeType.COMMIT);
    Query q = eManager.createNamedQuery("findLocationByLatLong");
    q.setParameter("latitude", loc.getLatitude());
    q.setParameter("longitude", loc.getLongitude());
    try {
        Location dummy = (Location) q.getSingleResult();
        eManager.clear();
        // eManager.flush();
        return dummy;
    } catch (NoResultException ex) {
        Logger.getLogger("WeatherRecorderBean.class").log(Level.WARNING,
                ex.getMessage());
        eManager.clear();
        eManager.merge(loc);
        eManager.flush();
        return loc;
    } catch (Exception ex) {
        Logger.getLogger("WeatherRecorderBean.class").log(Level.WARNING, "Should never get here! "+ex.getMessage());
        return null;
    }
}

I create a new function for saving Location. I also had a few synchronization problems. I had this function in a MDB onMessage() function. So before one onMessage() was finished another one started hence creating duplicate entries!

Hope this helps somebody in future!