Other than the javadocs, are these documented anywhere?
* public String getSequenceNextValString(String sequenceName)
* public String getSelectSequenceNextValString(String sequenceName)
I have been following their usage throughout the codebase, but it is not easy.
Is getSequenceNextValString only used in place of single values? E.g. INSERT INTO NEW_USER
VALUES (SEQ_USER.NEXTVAL, 'Adams', 'John') ? Or are there other uses?
Is getSelectSequenceNextValString only used to get the "batch" for
Hibernate's future issuance of ids?
The reason I am asking is we would like to leverage the MS SQL Server's
sp_sequence_get_range . By using the approach of using the stored procedure for
Hibernate's allocation and otherwise using an increment by 1 allows higher efficiency
with the Hibernate based application and prevent breaking legacy access to the table/use
of the sequence. This topic was unclearly broached in HHH-10130. Internet research
below...
Take the following example:
CREATE SEQUENCE [dbo].[hibernate_sequence2]
AS [bigint]
START WITH 1
INCREMENT BY 1
CACHE 200
;
CREATE TABLE [MyTable2]
(
[ID] [bigint] PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR
[dbo].[hibernate_sequence2]),
[Title] [nvarchar](64) NOT NULL
);
insert into MyTable2 (Title) values ('test 1');
insert into MyTable2 (Title) values ('test 2');
insert into MyTable2 (Title) values ('test 3');
insert into MyTable2 (Title) values ('test 4');
select * from MyTable2;
--ID Title
--1 test 1
--2 test 2
--3 test 3
--4 test 4
DECLARE @range_first_value_output sql_variant ;
declare @range_size int=50+1;
DECLARE @sequence_increment sql_variant ;
declare @range_last_value sql_variant;
EXEC sys.sp_sequence_get_range
@sequence_name = N'[dbo].[hibernate_sequence2]'
, @range_size = @range_size
, @range_first_value = @range_first_value_output OUTPUT
, @sequence_increment = @sequence_increment OUTPUT
, @range_last_value = @range_last_value OUTPUT ;
SELECT @range_first_value_output AS FirstNumber, @sequence_increment as
sequence_increment, @range_last_value as range_last_value ;
--FirstNumber sequence_increment range_last_value
-- 5 1 55
insert into MyTable2 (Title) values ('test 5');
select * from MyTable2;
--ID Title
--1 test 1
--2 test 2
--3 test 3
--4 test 4
--56 test 5
I know there are issues of "knowing" the @SequenceGenerator(allocationSize), but
I will assume a fixed 50 for now.
Internet research and other useless links...
1: Not hibernate, but same goal -
https://groups.google.com/forum/#!topic/ebean/wG6VyVfEMQk
2: Not helpful -
https://stackoverflow.com/questions/17780394/hibernate-identity-vs-sequen...
3: Closest Hibernate Hit -
https://hibernate.atlassian.net/browse/HHH-10130
4: My google searches -
https://www.google.com/search?safe=off&q=%22sp_sequence_get_range%22+... and
https://www.google.com/search?safe=off&q=%22sp_sequence_get_range%22+jpa
5: An interesting discussion, but not applicable -
https://hibernate.atlassian.net/browse/HHH-10560
6: Sequences Added to SQL Server Dialect -
https://hibernate.atlassian.net/browse/HHH-8440
7: My JIRA search -
https://hibernate.atlassian.net/browse/HHH-6950?jql=(text%20~%20sequence%...
-Jason
--
Jason Pyeron | Architect
PD Inc |
10 w 24th St |
Baltimore, MD |
.mil: jason.j.pyeron.ctr(a)mail.mil
.com: jpyeron(a)pdinc.us
tel : 202-741-9397