insert if not exists in HQL

I am trying to write a query in HQL which can insert a record if it does not exists (with same name) so that there is no duplicate insertion when done from multiple threads.

String hql = "INSERT INTO employee(emp_id, emp_name)"
        + " SELECT '100001' , 'John' " FROM employee
        + " WHERE NOT EXISTS (SELECT 1 from employee WHERE emp_name  = 'John')";

However, the record is not inserted. I suspect this is because the table is empty and the subquery returns 0 records despite the NOT EXISTS clause.

Note – ‘100001’ , ‘John’ & will programmatically be replaced in the actual query string.

Answer

The reason is that you are selecting constant values from an empty table. Thus you are selecting and inserting zero rows. Note that if the table had two rows but neither of them have emp_name = 'John', you would insert two rows. So you should select from a table with exactly one row:

INSERT INTO employee(emp_id, emp_name)
SELECT '100001' , 'John' 
FROM (select 1) as dummy -- derived table with one row
WHERE NOT EXISTS (SELECT 1 from employee WHERE emp_name  = 'John')

Instead of (select 1) as dummy you can also use dual im MySQL.

As I wrote in the comment you can also define the emp_name column as UNIQUE and then just use INSERT IGNORE.