2

I have a table user_services

    Column     |  Type   | 
---------------+---------+
 user_id       | integer | 
 subservice_id | integer |

I have a SortedSet of subservice_ids in Java that I want to insert into my table each with the same user_id. Can this be done in a single INSERT-statement using native Hibernate SQL?

What if I do something like:

String values = StringUtils.join(mySubServiceIds, ',');

Query query = createSQLQuery("INSERT INTO user_subservices " +
"(user_id, subservice_id) " +
"SELECT 1 id, x " +
"FROM unnest(ARRAY["+ values + "]) x");

Would that work and be good?

uraza
  • 907
  • 4
  • 12
  • 22
  • If the native query would work on Postgres then it should also work from Hibernate. Watch out for SQL injection though. – Tim Biegeleisen Mar 23 '16 at 10:57
  • Anything that works from the `psql` console should work with native queries. Be aware though, that arrays are still unsupported in Hibernate, so you will have to write the `ARRAY[val1,val2]` syntax manually with escaped values. From what I'm seeing those are only numeric, which should be easy to verify. – coladict Mar 23 '16 at 11:38

0 Answers0