How to update foreign key in one table with the primary key of the table that it references using JPA?

Question

I've two tables as following,

USER

+--------+---------------+------------+--------+-----------+------------+--------------+----------------+----------------+-------------+
| USERID | EMAIL         | FIRST_NAME | HONORS | LAST_NAME | LOGIN_TYPE | PHONE_NUMBER | PROFILE_PIC    | RECENT_CONV_ID | LOCATION_ID |
+--------+---------------+------------+--------+-----------+------------+--------------+----------------+----------------+-------------+
|      1 | asf@gmail.com | ghj        |      0 | ert       |          0 | 9879878      | http://vvv.com |           NULL |        NULL |

+--------+---------------+------------+--------+-----------+------------+--------------+----------------+----------------+-------------+

USER_LOCATION

+------------+-------+---------+----------+------------+-----------+-------+
| LOCATIONID | CITY  | COUNTRY | LATITUDE | LOCAL_ADDR | LONGITUDE | STATE |
+------------+-------+---------+----------+------------+-----------+-------+
|          1 | xyz   | mm      |       10 | asfdasf    |        10 | qqq   |
+------------+-------+---------+----------+------------+-----------+-------+

Below are the CREATE TABLE queries for both the tables,

CREATE TABLE `USER` (
`USERID` bigint(20) NOT NULL AUTO_INCREMENT,
`EMAIL` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`FIRST_NAME` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`HONORS` bigint(20) NOT NULL,
`LAST_NAME` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`LOGIN_TYPE` int(11) NOT NULL,
`PHONE_NUMBER` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`PROFILE_PIC` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
`RECENT_CONV_ID` bigint(20) DEFAULT NULL,
`LOCATION_ID` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`USERID`),
 KEY `USER_N50` (`RECENT_CONV_ID`),
 KEY `USER_N49` (`LOCATION_ID`),
 CONSTRAINT `USER_FK1` FOREIGN KEY (`RECENT_CONV_ID`) REFERENCES `RECENT_CONVERSATION` (`ID`),
 CONSTRAINT `USER_FK2` FOREIGN KEY (`LOCATION_ID`) REFERENCES `USER_LOCATION` (`LOCATIONID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |


CREATE TABLE `USER_LOCATION` (
`LOCATIONID` bigint(20) NOT NULL AUTO_INCREMENT,
`CITY` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`COUNTRY` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`LATITUDE` double DEFAULT NULL,
`LOCAL_ADDR` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`LONGITUDE` double DEFAULT NULL,
`STATE` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
 PRIMARY KEY (`LOCATIONID`),
 UNIQUE KEY `USER_LOCATION_U1` (`LOCAL_ADDR`,`CITY`,`STATE`,`COUNTRY`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

Now, I want to update LOCATION_ID in USER with the LOCATIONID of USER_LOCATION. How do I achieve it use JPA?

My Java classes :

@Entity(name="USER")
public class User {

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private long userId;

@Column(name="PHONE_NUMBER", nullable=false)
private String phoneNumber;

@Column(name="FIRST_NAME", nullable=false)
private String firstName;

@Column(name="LAST_NAME", nullable=false)
private String lastName;

@Column(name="EMAIL", nullable=false)
private String email;

@Column(name="PROFILE_PIC", nullable=false)
private String profilepic;

@Column(name="LOGIN_TYPE", nullable=false)
private int loginType;

@Column(name="HONORS", nullable=false)
private long honors;

@ManyToOne(cascade={CascadeType.PERSIST})
@JoinColumn(name="LOCATION_ID")
private UserLocation userLocation;

@OneToMany(cascade=CascadeType.PERSIST)
@JoinColumn(name="RECENT_CONV_ID")
private RecentConversation recentConversation;
 }

@Entity(name="USER_LOCATION")
@Table(name="USER_LOCATION", uniqueConstraints=@UniqueConstraint(columnNames={"LOCAL_ADDR", "CITY", "STATE", "COUNTRY"}))
@NamedQuery(name="addUserLocation", query="SELECT l FROM USER_LOCATION l " +
                                        "WHERE l.local_addr = :lo_addr AND " +
                                        "l.city = :city AND " +
                                        "l.state = :state AND " +
                                        "l.country = :country")
public class UserLocation {
@Id
@GeneratedValue
private long locationId;

@Column(name="LATITUDE")
private Double latitude;

@Column(name="LONGITUDE")
private Double longitude;

@Column(name="LOCAL_ADDR")
private String local_addr;

@Column(name="CITY")
private String city;

@Column(name="STATE")
private String state;

@Column(name="COUNTRY")
private String country;

@OneToMany(mappedBy="userLocation")
private Collection<User> users = new HashSet<User>();
}

Please note, the business rule that I'm trying to implement is, there should be no duplicate entries in the USER_LOCATION, based on UNIQUE KEY USER_LOCATION_U1. Moreover, if there a more than one users at the same location, LOCATION_ID in USER should be updated to that USER_LOCATION. Thanks a lot.

UPDATE: My testcase,

public class UserTest extends TestCase{

EntityManager em;

public void testUsersFromLocation() {

    EntityManagerFactory emf = Persistence.createEntityManagerFactory("TalkExchange");
    em = emf.createEntityManager();

    User user = createNewUser();

    em.getTransaction().begin();
//      em.persist(user.getUserLocation());
    em.merge(user);
    em.flush();
    em.detach(user.getUserLocation());
    em.contains(user.getUserLocation());
    em.contains(user);
    em.getTransaction().commit();

    getUsersAtLocation();
}

    public User createNewUser() {
    User user = new User();
    user.setEmail("asf@gmail.com");
    user.setFirstName("fgfg");
    user.setLastName("uiu");
    user.setLoginType(0);
    user.setPhoneNumber("7777");
    user.setProfilepic("http://vvv.com");
    user.setUserId(234);
    UserLocation userLocation = createUserLocation();
    user.setUserLocation(userLocation);
//      UserLocation userLocation = getExistingUserLocation(); 
//      user.setUserLocation(userLocation);
    userLocation.getUsers().add(user);
    return user;
}

    public User createNewUser() {
    User user = new User();
    user.setEmail("asf@gmail.com");
    user.setFirstName("fgfg");
    user.setLastName("uiu");
    user.setLoginType(0);
    user.setPhoneNumber("7777");
    user.setProfilepic("http://vvv.com");
    user.setUserId(234);
    UserLocation userLocation = createUserLocation();
    user.setUserLocation(userLocation);
//      UserLocation userLocation = getExistingUserLocation(); 
//      user.setUserLocation(userLocation);
    userLocation.getUsers().add(user);
    return user;
}

    public UserLocation createUserLocation() {
    UserLocation userLocation = new UserLocation();
    userLocation.setCity("wrwer");
    userLocation.setCountry("MM");
    userLocation.setLatitude(new Double(10));
    userLocation.setLongitude(new Double(10));
    userLocation.setLocal_addr("dfdfd");
    userLocation.setState("kjlkj");

//      create a query to find out whether the above UserLocation exists in the database.
//      if(exists)
//          use the existing location
//      else
//          use add the new location
    addLocationRule(userLocation);
    return userLocation;
}
}

score 1 · Answer 1 · answered May 16 '13 at 13:24

1

You should remove the CascadeType.PERSIST from this relation

@ManyToOne(cascade={CascadeType.PERSIST})
@JoinColumn(name="LOCATION_ID")
private UserLocation userLocation;

Cascade persist means that whenever you save a new instance of User it will try to save a new UserLocation. This is obviously not what you want in a ManyToOne relation.

You should create UserLocation instances before you create the User objects and then reuse the same user locations in the user instances.

answered May 16 '13 at 13:24

Doron Manor

576
4
5

If the related UserLocation is detached when the User is persisted then it won't create any new UserLocation object (even when CascadeType.PERSIST is set) and just refer to the existing one – DataNucleus May 16 '13 at 13:36
Thanks Doron, DataNucleus. Doron, I get your point. Commented it. Still the transaction rolled back due to @UniqueConstraint on UserLocation. Now, please correct me if I am wrong, when I persist the user object, it tries to persist userLocation object too. So, is CascadeType.PERSIST enabled by default on persist() method? – Harshal Kshatriya May 16 '13 at 14:48
DataNucleus, how do I 'refer' a new User object to an existing userLocation object? Please bear with me. – Harshal Kshatriya May 16 '13 at 14:49
you "find" the object in a txn and set the reference in your User class to it before persisting the User. Nothing specific to java persistence in that, its Java ... "refer" equates to use a reference to – DataNucleus May 17 '13 at 10:38
Thanks DataNucleus for always being there. I realized I didn't have my concepts clear. I've posted a detailed answer. – Harshal Kshatriya May 18 '13 at 05:31

Harshal Kshatriya · Accepted Answer · 2013-05-18T05:45:23.090

In this case,

Find the UserLocation object using entityManager.find() or a named query.
Since User and UserLocation have a bidrectional relationship, and I want to add a new user to the existing location, userLocation.getUsers.add(newUser);

That's it. It will add a new user to the USER table, with a foreign key, pointing to the existing USER_LOCATION row in the USER_LOCATION table.

Please check the gist for detailed implementation.

UPDATE: Please run the test more than once, in order to add more users to an existing location.

How to update foreign key in one table with the primary key of the table that it references using JPA?

2 Answers2