| Title: Generated Columns |
| |
| |
| <H2><A name="GeneratedColumns-WhatIsa%22Generated%22Column"></A>What Is a "Generated" Column</H2> |
| |
| <P>Many databases allow users to define special columns with values that are automatically created by the the database to be unique for each inserted row. Main use if this feature is to generate a primary key. Such columns are often called <B>"autoincrement"</B> or <B>"identity"</B> columns. JDBC3 specification provides Java API to read values of such columns right after an insert is performed. JDBC calls such columns <B>"generated keys"</B>, and Cayenne follows JDBC in that. Namely DbAttribute has a <TT>"generated"</TT> boolean property to indicate that a given column value is provided by the database on insert.</P> |
| |
| <H2><A name="GeneratedColumns-GeneratedColumnsinCayenne"></A>Generated Columns in Cayenne </H2> |
| |
| <P>Primary key generation is the only use of generated columns in Cayenne.</P> |
| |
| <P>If a primary key column is marked as "generated" in CayenneModeler (see below), Cayenne will bypass its default PK generation mechanism and rely on the database to provide a key value. But only if the runtime DbAdapter is configured to allow that (see "Hints and Limitations" section for explanation). Currently only Derby, MySQL and SQLServer adapters allow generated columns by default. For the rest of the adapters, Cayenne will fall back to its default PK generation mechanism.</P> |
| |
| <P>If you know that your driver supports generated keys API, but Cayenne assumes it does not, you can change this setting manually:</P> |
| |
| <DIV class="code panel" style="border-width: 1px;"><DIV class="codeContent panelContent"> |
| <PRE class="code-java">DataNode node = ... |
| JdbcAdapter adapter = (JdbcAdapter) node.getAdapter(); |
| adapter.setSupportsGeneratedKeys(<SPAN class="code-keyword">true</SPAN>); |
| </PRE> |
| </DIV></DIV> |
| |
| <P>Use of generated keys for PK is configured for each DbEntity individually in CayenneModeler:</P> |
| |
| <UL> |
| <LI>Navigate to a DbEntity in question and select <TT>"PK Generation Strategy"</TT> to be <TT>"Database-Generated"</TT>:</LI> |
| </UL> |
| |
| |
| <P><SPAN class="image-wrap" style=""><IMG src="generated-columns.data/select_db_key1.jpg" style="border: 0px solid black"></SPAN></P> |
| |
| |
| <UL> |
| <LI>Select a PK column that will be auto-incremented by the database from the list of existing primary keys:</LI> |
| </UL> |
| |
| |
| <P><SPAN class="image-wrap" style=""><IMG src="generated-columns.data/select_db_key2.jpg" style="border: 0px solid black"></SPAN></P> |
| |
| <H2><A name="GeneratedColumns-HintsandLimitations"></A>Hints and Limitations</H2> |
| |
| <UL> |
| <LI>Only a single DbAttribute can be marked as "generated" in a DbEntity. Most databases only allow a single generated column per table, and Cayenne consistently adheres to the same policy.</LI> |
| <LI>Generated attribute must also be a primary key.</LI> |
| <LI>Database must support this feature.</LI> |
| <LI>JDBC driver must support this feature. Even if database supports identity columns, the driver may not. By default Cayenne assumes that the following drivers support it: Derby, MySQL and SQLServer jTDS (but not the MS) driver.</LI> |
| </UL> |
| |
| |
| <DIV class="panelMacro"><TABLE class="noteMacro"><COLGROUP><COL width="24"><COL></COLGROUP><TR><TD valign="top"><IMG src="http://cayenne.apache.org/docs/3.0/images/icons/emoticons/warning.gif" width="16" height="16" align="absmiddle" alt="" border="0"></TD><TD><B>MS SQLServer Note</B><BR>MS drivers prior to 2005 version do not support generated columns, while newer drivers and <A href="http://jtds.sourceforge.net/" class="external-link" rel="nofollow">jTDS</A> support it just fine. Cayenne can automatically detect the driver type and configure SQLServerAdapter, but only when AutoAdapter is used. To make sure auto-detection works, clear the "Custom Adapter" field for the corresponding DataNode in the Modeler.</TD></TR></TABLE></DIV> |
