You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Fay Wang (JIRA)" <ji...@apache.org> on 2008/10/01 18:29:44 UTC
[jira] Created: (OPENJPA-736) Combine insert and select SQL
together when for generated Id with strategy=GenerationType.IDENTITY
Combine insert and select SQL together when for generated Id with strategy=GenerationType.IDENTITY
----------------------------------------------------------------------------------------------------
Key: OPENJPA-736
URL: https://issues.apache.org/jira/browse/OPENJPA-736
Project: OpenJPA
Issue Type: Improvement
Affects Versions: 1.3.0
Reporter: Fay Wang
Fix For: 1.3.0
Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
(1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
[params=(int) 1, (int) 1, (int) 1, (int) 1]
(2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
Re: [jira] Created: (OPENJPA-736) Combine insert and select SQL together when for generated Id with strategy=GenerationType.IDENTITY
Posted by Fay Wang <fy...@yahoo.com>.
Yes, we will look into the getGeneratedKey in jdbc.
--- On Wed, 10/1/08, David Jencks <da...@yahoo.com> wrote:
> From: David Jencks <da...@yahoo.com>
> Subject: Re: [jira] Created: (OPENJPA-736) Combine insert and select SQL together when for generated Id with strategy=GenerationType.IDENTITY
> To: dev@openjpa.apache.org
> Date: Wednesday, October 1, 2008, 10:11 AM
> isn't there a db independent jdbc feature for this now?
>
> thanks
> david jencks
>
> On Oct 1, 2008, at 9:29 AM, Fay Wang (JIRA) wrote:
>
> > Combine insert and select SQL together when for
> generated Id with
> > strategy=GenerationType.IDENTITY
> >
> ----------------------------------------------------------------------------------------------------
> >
> > Key: OPENJPA-736
> > URL:
> https://issues.apache.org/jira/browse/OPENJPA-736
> > Project: OpenJPA
> > Issue Type: Improvement
> > Affects Versions: 1.3.0
> > Reporter: Fay Wang
> > Fix For: 1.3.0
> >
> >
> > Currently if the strategy of the generated id is
> > GenerationType.IDENTITY, when an entity object is
> created, openjpa
> > will generate two SQL statements. The following is an
> example
> > running on DB2:
> >
> > (1) INSERT INTO EntityA (col1, col2, col3, version)
> VALUES
> > (?, ?, ?, ?)
> > [params=(int) 1, (int) 1, (int) 1, (int) 1]
> >
> > (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> >
> > A performance improvement is to take advantage of the
> "select from
> > final table" feature in DB2 to combine the insert
> and select
> > statement into a single SQL statement as shown below:
> >
> > SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1,
> col2, col3,
> > version) VALUES (?, ?, ?, ?) )
> >
> >
> > --
> > This message is automatically generated by JIRA.
> > -
> > You can reply to this email to add a comment to the
> issue online.
> >
Re: [jira] Created: (OPENJPA-736) Combine insert and select SQL together when for generated Id with strategy=GenerationType.IDENTITY
Posted by David Jencks <da...@yahoo.com>.
isn't there a db independent jdbc feature for this now?
thanks
david jencks
On Oct 1, 2008, at 9:29 AM, Fay Wang (JIRA) wrote:
> Combine insert and select SQL together when for generated Id with
> strategy=GenerationType.IDENTITY
> ----------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Fix For: 1.3.0
>
>
> Currently if the strategy of the generated id is
> GenerationType.IDENTITY, when an entity object is created, openjpa
> will generate two SQL statements. The following is an example
> running on DB2:
>
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES
> (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
>
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
>
> A performance improvement is to take advantage of the "select from
> final table" feature in DB2 to combine the insert and select
> statement into a single SQL statement as shown below:
>
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3,
> version) VALUES (?, ?, ?, ?) )
>
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12702866#action_12702866 ]
Milosz Tylenda commented on OPENJPA-736:
----------------------------------------
Omitting an identity column is not the problem. The MySQL issue is that it does not allow the retrieval of generated column by its actual name - coulmn name is always "GENERATED_KEY". The PostgreSQL issue is that it requires exact case in column name passed to Connection.prepareStatement("insert into IdentityGenerationType (someData) values('gktest')", new String[] {"orderid[case matters here]"});
Looks like the solution is to pass identity column name thru DBDictionary case conversion and to get generated column value by using column index. MySQL, PostgreSQL and DB2 accept this. I will see what about MS SQL Server when I manage to install it. The MS JDBC 2.0 driver suggests it behaves much like MySQL [1].
[1] http://msdn.microsoft.com/en-us/library/ms378445.aspx
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636180#action_12636180 ]
Fay Wang commented on OPENJPA-736:
----------------------------------
The jdbc api that I could find for the generatedKey is in the Statement, not in the PreparedStatement:
Statement.executeUpdate(String sql, int autoGeneratedKeys)
Statement.getGeneratedKeys( ) to get the ResultSet for the generated keys
OpenJpa uses PreparedStatement, not the Statement. I don't think we should change from PreparedStatement to Statement just to get the generatedKeys.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12707368#action_12707368 ]
Milosz Tylenda commented on OPENJPA-736:
----------------------------------------
Albert, I can restore the signatures but unfortunately I am out of source access till the end of week. If it is more urgent, you could roll my change back.
Sorry for the problem. My intent was to make signatures simpler - did not know someone might have already subclassed this.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12714711#action_12714711 ]
Milosz Tylenda commented on OPENJPA-736:
----------------------------------------
Points 1, 2 and 3 applied to trunk.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch, supportsGetGeneratedKeys.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-736:
-----------------------------
Summary: Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY (was: Combine insert and select SQL together when for generated Id with strategy=GenerationType.IDENTITY )
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (OPENJPA-736) Combine insert and select SQL
together when for generated Id with strategy=GenerationType.IDENTITY
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang reassigned OPENJPA-736:
--------------------------------
Assignee: Fay Wang
> Combine insert and select SQL together when for generated Id with strategy=GenerationType.IDENTITY
> ----------------------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12708177#action_12708177 ]
Milosz Tylenda commented on OPENJPA-736:
----------------------------------------
I have restored the signatures. Let me know if a problem still exists.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636762#action_12636762 ]
Fay Wang commented on OPENJPA-736:
----------------------------------
Fix some indentation problem under r701555.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12707080#action_12707080 ]
Milosz Tylenda commented on OPENJPA-736:
----------------------------------------
Now when I have finally got SQL Server 2008 at my database pasture (what a glorious day! :), I have successfully tested the proposed solution with DB2, MySQL, PostgreSQL and SQL Server. I have committed the change to the trunk. I have made DBDictionary.convertSchemaCase public as the invocation is needed for PostgreSQL.
A few more things to do:
1. Currently the support for JDBC 3 generated keys retrieval is auto-detected. I think we should also allow users to enable/disable it explicitly. Some people may want the old behaviour (issuing a database-specific query to fetch generated keys) for some reasons. Also, if a bug is lurking somewhere, we have an easy workaround by disabling the feature.
2. TestGenerationType could be extended to test both ways of generated keys retrieval where possible.
3. The user manual might need an update.
4. Apply the changes to 1.3.x.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12721393#action_12721393 ]
Milosz Tylenda commented on OPENJPA-736:
----------------------------------------
For information, Oracle's driver also supports the getGeneratedKeys although Oracle database does not support auto-increment columns. The generated keys retrieval seems to play nicely with our OracleDictionary.useTriggersForAutoAssign feature for emulating auto-increment columns.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0, 2.0.0-M2
>
> Attachments: openjpa-736.patch, supportsGetGeneratedKeys.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Milosz Tylenda updated OPENJPA-736:
-----------------------------------
Attachment: supportsGetGeneratedKeys.patch
The attached patch supportsGetGeneratedKeys.patch addresses point 1. The idea is to auto-detect the property only if user did not specify the property value. In order to do that, I have changed the property type from primitive boolean to Boolean. Let me know if anybody sees this problematic.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch, supportsGetGeneratedKeys.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang updated OPENJPA-736:
-----------------------------
Attachment: openjpa-736.patch
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636135#action_12636135 ]
Catalina Wei commented on OPENJPA-736:
--------------------------------------
An alternative is to use db independent jdbc getGeneratedKeys smethod on Statement interface.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636195#action_12636195 ]
Fay Wang commented on OPENJPA-736:
----------------------------------
I just found that for PreparedStatement, we can call Connection.PreparedStatement(sql, int) to pass in autoGeneratedKeys. I will explore more about this approach.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Michael Dick (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12636147#action_12636147 ]
Michael Dick commented on OPENJPA-736:
--------------------------------------
+1 to use getGeneratedKeys and expand the support to cover all databases.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Fay Wang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Fay Wang resolved OPENJPA-736.
------------------------------
Resolution: Fixed
Fix is committed into trunk r701537.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Fay Wang
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Reopened: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Milosz Tylenda reopened OPENJPA-736:
------------------------------------
Assignee: Milosz Tylenda (was: Fay Wang)
More work is needed. The problem is that when a table has an identity (auto-increment) column, we omit that column in INSERT statement and then we retrieve the generated (and omitted) column by name. DB2 accepts this but MySQL and PostgreSQL go on strike. Testing on other databases may reveal more oddities.
I will look into this.
For those interested, the JDBC driver for PostgreSQL, available in their CVS, supports the getGeneratedKeys variant OpenJPA would use. There is an unofficial build available [1] which contains that feature.
[1] http://ejurka.com/pgsql/jars/genkey/
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Milosz Tylenda resolved OPENJPA-736.
------------------------------------
Resolution: Fixed
Fix Version/s: 2.0.0
Change applied also to 1.3.x branch.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0, 2.0.0
>
> Attachments: openjpa-736.patch, supportsGetGeneratedKeys.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (OPENJPA-736) Combine insert and select SQL
together for generated Id strategy=GenerationType.IDENTITY
Posted by "Albert Lee (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-736?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12707157#action_12707157 ]
Albert Lee commented on OPENJPA-736:
------------------------------------
Signature change in PreparedStatementManagerImpl has broken subclass in (non-OpenJPA) derived product build.
Albert Lee.
> Combine insert and select SQL together for generated Id strategy=GenerationType.IDENTITY
> -----------------------------------------------------------------------------------------
>
> Key: OPENJPA-736
> URL: https://issues.apache.org/jira/browse/OPENJPA-736
> Project: OpenJPA
> Issue Type: Improvement
> Affects Versions: 1.3.0
> Reporter: Fay Wang
> Assignee: Milosz Tylenda
> Fix For: 1.3.0
>
> Attachments: openjpa-736.patch
>
>
> Currently if the strategy of the generated id is GenerationType.IDENTITY, when an entity object is created, openjpa will generate two SQL statements. The following is an example running on DB2:
> (1) INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?)
> [params=(int) 1, (int) 1, (int) 1, (int) 1]
> (2) SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> A performance improvement is to take advantage of the "select from final table" feature in DB2 to combine the insert and select statement into a single SQL statement as shown below:
> SELECT id FROM FINAL TABLE (INSERT INTO EntityA (col1, col2, col3, version) VALUES (?, ?, ?, ?) )
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.