jpa mysql 主键_mysql – 如何使用JPA引用的表的主键更新一...

我有两张表如下,

用户

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

| 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 |

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

下面是两个表的CREATE TABLE查询,

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

现在,我想使用USER_LOCATION的LOCATIONID更新USER中的LOCATION_ID.我如何使用JPA实现它？

我的Java课程：

@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 users = new HashSet();

}

请注意,我正在尝试实现的业务规则是,USER_LOCATION中应该没有基于UNIQUE KEY USER_LOCATION_U1的重复条目.此外,如果同一位置有多个用户,则USER中的LOCATION_ID应更新为该USER_LOCATION.非常感谢.

更新：

我的测试用例,

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;

}

}