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.
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();