I am facing two issues here :
Issue 1) I am trying to get the COMPANY_ID using the selectCompanyID variable as shown below. I am storing the value returned into getCompanyID variable.When I tried to print it, as shown in the console output below, it's always printing number 1. However, when I tried to run the actual SQL in the
Oracle SQL developer SQL Worksheeet, I got different number. Because of this I could insert wrong ID in the following INSERT statement where it's
getting used.
Issue 2) As shown in the SQL, I am also getting ORA-00984: column not allowed here error. Noticed, people encountering similar error in the following post :
"column not allowed here" error in INSERT statement
But here I don't have straightforward SQL INSERT statement as the one mentioned in the above post with missing quotes.
public boolean insertEmployeeDetails(Employee employee)
{
logger.debug("Starting EmployeeDaoImpl.insert() .....");
Session session = null;
Transaction tx = null;
boolean status = true;
try {
session = sessionFactory.openSession();
tx = session.beginTransaction();
//The following SELECT query returns a number when I ran it in the Oracle SQL developer SQL worksheet
String selectCompanyID = "SELECT"
+ " VALUE_EMP_ID"
+" FROM "
+ " COMPANY_DATA"
+" WHERE"
+ " testing_id = 1234"
+" AND "
+ " company_employee_id = 3345";
int getCompanyID = session.createSQLQuery(selectCompanyID)
.executeUpdate();
System.out.println("GetCompanyID Test below");
System.out.println(getCompanyID);
String hqlInsert = "INSERT INTO Employee (NAME, IS_CORRECT,IS_WRONG,COMPANY_ID, TRANSACTION_ID,DEFINITION) VALUES"
+ "( SELECT value_emp_id FROM COMPANY_DATA WHERE testing_id = 1234 AND"
+ " company_employee_id = 3345))";
String hqlInsert = "INSERT INTO Employee (NAME,IS_CORRECT,IS_WRONG,COMPANY_ID,TRANSACTION_ID,DEFINITION) VALUES "
+ "("
+employee.getName()+","
+employee.getIsCorrect()+","
+employee.getIsWrong()+","
+getCompanyID+","
+employee.getTransactionId()+","
+employee.getDefinition()+")";
System.out.println("October 3 Checking for hqlInsert");
System.out.println(hqlInsert);
int createdEntities = session.createSQLQuery( hqlInsert )
.executeUpdate();
session.persist(employee);
tx.commit();
System.out.println("October 3 BELOW Checking for hqlInsert");
System.out.println(hqlInsert);
System.out.println("Checking for CreatedEntities");
System.out.println(createdEntities);
} catch(Exception ex) {
tx.rollback();
ex.printStackTrace();
status = false;
} finally {
session.close();
}
logger.debug("Completed EmployeeDaoImpl.insert() .....");
return status;
}
Console :
Hibernate:
SELECT
VALUE_EMP_ID
FROM
COMPANY_DATA
WHERE
testing_id = 1234
AND company_employee_id = 3345
GetCompanyID Test below
1
October 3 Checking for hqlInsert
INSERT INTO Employee (NAME,IS_CORRECT,IS_WRONG,COMPANY_ID,TRANSACTION_ID,DEFINITION) VALUES (Terminology,0,0,1,0,definitionTest)
Hibernate:
INSERT
INTO
Employee
(NAME,IS_CORRECT,IS_WRONG,COMPANY_ID,TRANSACTION_ID,DEFINITION)
VALUES
(Terminology,0,0,1,0,definitionTest)
2017-10-03 11:32:43.753 WARN 5392 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 984, SQLState: 42000
2017-10-03 11:32:43.753 ERROR 5392 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-00984: column not allowed here
Here is my Entity Class Employee.java is as follows:
package abc.def.mypackage.orm
@Entity
@Table(name = "EMPLOYEE")
public class Employee {
public int getEmployeeId() {
return employeeId;
}
public void setEmployeeId(int employeeId) {
this.employeeId = employeeId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getIsCorrect() {
return isCorrect;
}
public void setIsCorrect(int isCorrect) {
this.isCorrect = isCorrect;
}
public int getIsWrong() {
return isWrong;
}
public void setIsWrong(int isWrong) {
this.isWrong = isWrong;
}
public int getCompanyId() {
return companyId;
}
public void setCompanyId(int companyId) {
this.companyId = companyId;
}
public Integer getTransactionId() {
return transactionId;
}
public void setTransactionId(Integer transactionId) {
this.transactionId = transactionId;
}
public String getDefinition() {
return definition;
}
public void setDefinition(String definition) {
this.definition = definition;
}
@Id
@Column(name = "EMPLOYEE_ID")
@GeneratedValue(strategy = GenerationType.AUTO, generator = "seqgen")
@SequenceGenerator(name = "seqgen", sequenceName = "EMPLOYEE_AUTOINC_SEQ")
private int employeeId;
@Column(name = "NAME")
private String name;
@Column(name = "DEFINITION")
private String definition;
@Column(name = "IS_CORRECT")
private int isCorrect;
@Column(name = "IS_WRONG")
private int isWrong;
@Column(name = "COMPANY_ID")
private int companyId;
@Column(name = "TRANSACTION_ID", nullable = true)
private Integer transactionId;
}
