Hi,
Is there any way I can create a Query which includes a SQL "INSERT..."
Or is there any way I execute a SQL statement which is an INSERT?
Rgds
Breako

In Hibernate, that's just creating an object (whose class is appropriately mapped to the database) and persisting it. Am I missing something?

Exactly, you shouldn't have a *need* for an explicit insert SQL statement.

try using the entity manager like so:

Thanks for your replies.
I am using SQLServer and I am using compound primary keys. Compound keys do not support autogeneration so the user has to generate a unique key to do this.
In Oracle I can just use an Oracle Sequence Table to read the next value.

To this in SQL Server I need to fire something like the following statement:

INSERT INTO SPERSON DEFAULT VALUES SELECT @@IDENTITY AS I

Which I can't seem to do using JPA.

Any ideas or help appreciated?

Well, typically when you have composite keys, you know the key values for new records in advance of actually persisting them to the table. I realize there are use-cases where this doesn't apply, I'm speaking in a general sense.

If you've got a sequencing table where you're managing values, you can simply treat it as another entity, like any other table, and pull the values from it to use as your primary key value(s).

You could also consider using a trigger (on the table), if possible. However, that's beyond the scope of this forum and is another story in-and-of itself.

Hi tsar bomba,
I have to find a unique value for the primary key before I persist the record.
To do this in oracle I use a sequence table which I can do no problem.
SQL Server does not use Sequence tables (in the way Oracle does where you simple to do a select to get the next value). SQLServer does something different which involves firing off an INSERT. JPA looks like it does not support any way of firing off an INSERT statement so I am stuck.
Any more ideas appreciated!

I guess what I tried to say before is; build the functionality you don't have. This would not be hard to implement on your own using a plain table and another entity to represent the ID values. Grab a new value from your ID "sequence" table, associate it to your entity in need of an ID, increment it w/ a new value.

I've done this quite successfully before in MSSQL using a hexidecimal value comprised of 2-64-bit numbers in hex format.

Generating keys is a problem that isn't well addressed by any technology I'm aware of. The databases can support this to some extent (not really for composite keys), but generally you can't obtain the generated ids via JDBC.

There are a few approaches, but generally I've had a separate table with one or more rows, each of which generates an id for a separate column. For a composite key, normally all but one of the keys is a foreign key relationship and determined by the parent table.

It is easier and better in Hibernate to not use composite keys, but of course existing schemata require them. So you can either generate a unique key for the part of the composite that isn't a fk, or you can manage it via the object behaviors, i.e. check the other objects in the one to many collection and figure out the next id to put in the composite.

Exactly.

I've done this by creating a table w/ 3 columns. The first two are 64-bit integers (the min and max that a 64-bit int allows) and the third column is the sum of the first two columns - stored in hex format.

Each time a new key is "pulled", the first two columns are incremented by one, and a new value is stored in the third column.

It's a lot more cumbersome than just *not* using composite keys, as you said, but works well and can be easily represented as an entity bean.