英文:
How to write Joins using CriteriaUpdate?
问题
我想要请求，请帮助我找到在其他表中更新外键值的解决方案。
例如：-
如果我们采用设备表
列为：- 设备名称 设备编号 显示名称 用户编号 房间编号 是否已删除
其他表是房间表
列为：- 房间名称 房间编号 房间类型 是否已删除
在这里，它看起来像是一对多的关系。即一个房间会包含多个设备。
在上面的两个表中，房间编号是房间表中的主键，而房间编号是设备表中的外键。
因此，这里我们想要的是更新设备表中的房间编号。
我尝试过了，但是我无法找到这个问题的解决方案，我请求你们帮助我解决这个问题。
数据库表将会是这样的
房间表（room_table）
房间编号 房间名称 房间类型
1 ROOM1 ROOM_TYPE1
2 ROOM2 ROOM_TYPE2
类似设备表（device_table）的设备表
设备编号 设备名称 显示名称 房间编号
1 DEVICE1 DISPLAY1 1
2 DEVICE2 DISPLAY2 2
我希望你们能理解上面的表。
所以，现在来谈谈我需要的任务：-
我想要更新设备表中的房间编号，即对于DEVICE2，我们需要从房间编号1更新到房间编号2，如下所示。
类似设备表（device_table）的设备表
设备编号 设备名称 显示名称 房间编号
1 DEVICE1 DISPLAY1 1
2 DEVICE2 DISPLAY2 2
为此，我编写了以下Java代码：-
@Entity
@Table(name = "device_table")
public class Device {
@Id
@Column(name = "device_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int deviceId;
@Column(name = "device_name")
private String deviceName;
@Column(name = "display_name")
private String dispalyName;
@ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@JoinColumn(name = "ROOM_ID")
private Room roomList;
// setters & getters
}
@Entity
@Table(name = "room_table")
public class Room {
@Id
@Column(name = "room_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int roomId;
@Column(name = "room_name")
private String roomName;
@Column(name = "room_type")
private String roomType;
@OneToMany(mappedBy = "roomList")
private Collection<Device> deviceList = new ArrayList<>();
// setters & getters
}
用于更新的代码如下。
public class SaveDataClientTest {
public static void main(String[] args) {
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
session.beginTransaction();
CriteriaBuilder criteriaBuilder = session.getCriteriaBuilder();
CriteriaUpdate<Device> deviceUpdate = criteriaBuilder.createCriteriaUpdate(Device.class);
Root<Device> deviceUpdateRoot = deviceUpdate.from(Device.class);
Subquery<Device> deviceSubquery = deviceUpdate.subquery(Device.class);
Root<Device> roomRoot = deviceSubquery.from(Device.class);
Join<Device, Room> join = roomRoot.join("roomList", JoinType.LEFT);
deviceUpdate.set(deviceUpdateRoot.get("dispalyName"), "DISPLAY12345");
deviceUpdate.set(deviceUpdateRoot.get("roomList").get("roomId"), 2);
deviceUpdate.where(criteriaBuilder.equal(deviceUpdateRoot.get("deviceName"), "DEVICE2"));
int returnValue = session.createQuery(deviceUpdate).executeUpdate();
System.out.println(returnValue);
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
}
}
}
我遇到了错误
Oct 01, 2020 8:00:03 PM org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
INFO: HHH000397: Using ASTQueryTranslatorFactory
Oct 01, 2020 8:00:03 PM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: Invalid path: 'generatedAlias1.roomId'
...
Invalid path: 'generatedAlias1.roomId'
...
请帮助我解决这个问题。谢谢。
英文:
I would like to request please help me to find out solution for updating the foreign key value in other table.
for example:-
If we taken Device Table
columns are :- devicename
deviceId
displayname
userId
roomId
isDeleted
other table is Rooms Table
columns are :- roomName
roomId
roomType
isDeleted
Hear it is looking like one-to-many relationship.
i.e., one room will contain many devices.
In the above two tables roomId is primary key in Rooms Table and roomId is forgin key in Device Table.
So, Hear what we want is we have to update the roomId in DeviceTable.
I tried but, I cant able to find the solution for this, I request you guys' please help me for this solution.
the database tables will be like
Room Table(room_table)
room_id room_name room_type
1 ROOM1 ROOM_TYPE1
2 ROOM2 ROOM_TYPE2
Device Table like(device_table)
device_id device_name display_name room_id
1 DEVICE1 DISPLAY1 1
2 DEVICE2 DISPLAY2 2
I hope you guys understand the above tables.
So, now Coming to my required task is:-
I want to update roomId in device table i.e., for DEVICE2 we have to update from roomId 1 to roomId 2 AS SHOWN IN BELOW.
Device Table like(device_table)
device_id device_name display_name room_id
1 DEVICE1 DISPLAY1 1
2 DEVICE2 DISPLAY2 2
for this I wrote java code like:-
@Entity
@Table(name = "device_table")
public class Device {
@Id
@Column(name = "device_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int deviceId;
@Column(name = "device_name")
private String deviceName;
@Column(name = "display_name")
private String dispalyName;
@ManyToOne(cascade=CascadeType.ALL,fetch=FetchType.EAGER)
//private List<Room> roomList = new ArrayList<>();
@JoinColumn(name = "ROOM_ID")
private Room roomList;
//setters & getters
}
@Entity
@Table(name = "room_table")
public class Room {
@Id
@Column(name = "room_id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int roomId;
@Column(name = "room_name")
private String roomName;
@Column(name = "room_type")
private String roomType;
@OneToMany(mappedBy="roomList")
private Collection<Device> deviceList = new ArrayList<>();
//setters & getters
}
For updating I tried code like below.
public class SaveDataClientTest {
public static void main(String[] args) {
try (Session session = HibernateUtil.getSessionFactory().openSession()) {
session.beginTransaction();
CriteriaBuilder userBuilder = session.getCriteriaBuilder();
CriteriaUpdate<Device> deviceUpdate = userBuilder.createCriteriaUpdate(Device.class);
Root<Device> deviceUpdateRoot = deviceUpdate.from(Device.class);
Subquery<Device> deviceSubquery = deviceUpdate.subquery(Device.class);
Root<Device> roomRoot = deviceSubquery.from(Device.class);
Join<Device, Room> join = roomRoot.join("roomList", JoinType.LEFT);
deviceUpdate.set(deviceUpdateRoot.get("dispalyName"), "DISPLAY12345");
deviceUpdate.set(join.get("roomId"), 2);
deviceUpdate.where(userBuilder.equal(deviceUpdateRoot.get("deviceName"), "DEVICE2"));
int returnValue = session.createQuery(deviceUpdate).executeUpdate();
System.out.println(returnValue);
session.getTransaction().commit();
} catch (HibernateException e) {
e.printStackTrace();
}
}
}
I am getting error like
Oct 01, 2020 8:00:03 PM org.hibernate.hql.internal.QueryTranslatorFactoryInitiator initiateService
INFO: HHH000397: Using ASTQueryTranslatorFactory
Oct 01, 2020 8:00:03 PM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: Invalid path: 'generatedAlias1.roomId'
Oct 01, 2020 8:00:03 PM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: Invalid path: 'generatedAlias1.roomId'
Invalid path: 'generatedAlias1.roomId'
at org.hibernate.hql.internal.ast.util.LiteralProcessor.lookupConstant(LiteralProcessor.java:111)
at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:214)
at org.hibernate.hql.internal.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:1038)
at org.hibernate.hql.internal.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:1026)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.assignment(HqlSqlBaseWalker.java:1054)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.setClause(HqlSqlBaseWalker.java:765)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.updateStatement(HqlSqlBaseWalker.java:381)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:269)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:266)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3324)
at org.hibernate.query.criteria.internal.AbstractManipulationCriteriaQuery$1.buildCompiledQuery(AbstractManipulationCriteriaQuery.java:112)
at org.hibernate.query.criteria.internal.compile.CriteriaCompiler.compile(CriteriaCompiler.java:127)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3628)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:203)
at com.infotech.client.SaveDataClientTest.main(SaveDataClientTest.java:54)
Exception in thread "main" java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.roomId' [update com.infotech.entities.Device as generatedAlias0 set generatedAlias0.dispalyName = :param0, generatedAlias1.roomId = 2 where generatedAlias0.deviceName=:param1]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:133)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:164)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:670)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3324)
at org.hibernate.query.criteria.internal.AbstractManipulationCriteriaQuery$1.buildCompiledQuery(AbstractManipulationCriteriaQuery.java:112)
at org.hibernate.query.criteria.internal.compile.CriteriaCompiler.compile(CriteriaCompiler.java:127)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:3628)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:203)
at com.infotech.client.SaveDataClientTest.main(SaveDataClientTest.java:54)
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.roomId' [update com.infotech.entities.Device as generatedAlias0 set generatedAlias0.dispalyName = :param0, generatedAlias1.roomId = 2 where generatedAlias0.deviceName=:param1]
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:272)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662)
... 6 more
will you please help me to solve this issue.
Thanks.
答案1
得分: 1
你尝试过以下内容吗？
CriteriaUpdate<Device> deviceUpdate = userBuilder.createCriteriaUpdate(Device.class);
Root<Device> deviceUpdateRoot = deviceUpdate.from(Device.class);
deviceUpdate.set(deviceUpdateRoot.get("dispalyName"), "DISPLAY12345");
deviceUpdate.set(deviceUpdateRoot.get("roomList").get("roomId"), 2);
deviceUpdate.where(userBuilder.equal(deviceUpdateRoot.get("deviceName"), "DEVICE2"));
int returnValue = session.createQuery(deviceUpdate).executeUpdate();
或者这个？
CriteriaUpdate<Device> deviceUpdate = userBuilder.createCriteriaUpdate(Device.class);
Root<Device> deviceUpdateRoot = deviceUpdate.from(Device.class);
deviceUpdate.set(deviceUpdateRoot.get("dispalyName"), "DISPLAY12345");
deviceUpdate.set(deviceUpdateRoot.get("roomList"), session.getReference(Room.class, 2));
deviceUpdate.where(userBuilder.equal(deviceUpdateRoot.get("deviceName"), "DEVICE2"));
int returnValue = session.createQuery(deviceUpdate).executeUpdate();
英文:
Have you tried the following?
CriteriaUpdate<Device> deviceUpdate = userBuilder.createCriteriaUpdate(Device.class);
Root<Device> deviceUpdateRoot = deviceUpdate.from(Device.class);
deviceUpdate.set(deviceUpdateRoot.get("dispalyName"), "DISPLAY12345");
deviceUpdate.set(deviceUpdateRoot.get("roomList").get("roomId"), 2);
deviceUpdate.where(userBuilder.equal(deviceUpdateRoot.get("deviceName"), "DEVICE2"));
int returnValue = session.createQuery(deviceUpdate).executeUpdate();
or this?
CriteriaUpdate<Device> deviceUpdate = userBuilder.createCriteriaUpdate(Device.class);
Root<Device> deviceUpdateRoot = deviceUpdate.from(Device.class);
deviceUpdate.set(deviceUpdateRoot.get("dispalyName"), "DISPLAY12345");
deviceUpdate.set(deviceUpdateRoot.get("roomList"), session.getReference(Room.class, 2));
deviceUpdate.where(userBuilder.equal(deviceUpdateRoot.get("deviceName"), "DEVICE2"));
int returnValue = session.createQuery(deviceUpdate).executeUpdate();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论