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

Question

I have two tables:

USER

+--------+---------------+------------+--------+-----------+------------+--------------+----------------+----------------+-------------+ | USERID | EMAIL | FIRST_NAME | HONORS | LAST_NAME | LOGIN_TYPE | PHONE_NUMBER | PROFILE_PIC | RECENT_CONV_ID | LOCATION_ID | +--------+---------------+------------+--------+-----------+------------+--------------+----------------+----------------+-------------+ | 1 | [e-mail] | 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 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 LOCATIONID USER_LOCATION. How do I get JPA to work?

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 that the business rule I'm trying to implement should not have duplicate entries in USER_LOCATION based on UNIQUE KEY USER_LOCATION_U1. Moreover, if more than one user is in the same location, the LOCATION_ID in USER must be updated to this USER_LOCATION . Thank you very much.

UPDATE: My test file,

 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(" [e-mail] "); 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(" [e-mail] "); 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; } } 

Answer 1

In this case

• Locate the UserLocation object using entityManager.find() or a query with the name .
• Since the user and UserLocation have a bidrectional relationship, and I want to add a new user to an existing location , userLocation.getUsers.add(newUser);

What is 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 gist for a detailed implementation.

UPDATE: Run the test several times to add more users to your existing location.

Answer 2

You must remove CascadeType.PERSIST from this relationship

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

Cascade persist means that whenever you save a new instance of a user, it tries to save a new UserLocation. This is clearly not what you want regarding ManyToOne.

You must create UserLocation instances before creating User objects, and then reuse the same user locations in user instances.