Java Code Examples for java.sql.Statement#RETURN_GENERATED_KEYS

/**
 * Insert a new record in the table.
 *
 * @param mailingList
 *            instance of the MailingList object to insert
 */
@Override
public void insert( MailingList mailingList )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
    {
        int nIndex = 1;
        daoUtil.setString( nIndex++, mailingList.getName( ) );
        daoUtil.setString( nIndex++, mailingList.getDescription( ) );
        daoUtil.setString( nIndex, mailingList.getWorkgroup( ) );

        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            mailingList.setId( daoUtil.getGeneratedKeyInt( 1 ) );
        }
    }
}
 

/**
 * Insert a new attribute
 * 
 * @param attribute
 *            the attribute
 * @return new PK
 */
public int insert( IAttribute attribute )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
    {
        int nIndex = 1;
        daoUtil.setString( nIndex++, attribute.getClass( ).getName( ) );
        daoUtil.setString( nIndex++, attribute.getTitle( ) );
        daoUtil.setString( nIndex++, attribute.getHelpMessage( ) );
        daoUtil.setBoolean( nIndex++, attribute.isMandatory( ) );
        daoUtil.setBoolean( nIndex++, attribute.isShownInSearch( ) );
        daoUtil.setBoolean( nIndex++, attribute.isShownInResultList( ) );
        daoUtil.setBoolean( nIndex++, attribute.isFieldInLine( ) );
        daoUtil.setInt( nIndex++, newPosition( ) );

        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            attribute.setIdAttribute( daoUtil.getGeneratedKeyInt( 1 ) );
        }
    }

    return attribute.getIdAttribute( );
}
 

/**
 * {@inheritDoc}
 */
@Override
public int insert( PhysicalFile physicalFile )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
    {
        daoUtil.setBytes( 1, physicalFile.getValue( ) );
        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            physicalFile.setIdPhysicalFile( daoUtil.getGeneratedKeyInt( 1 ) );
        }
    }
    return physicalFile.getIdPhysicalFile( );
}
 

/**
 * {@inheritDoc}
 */
@Override
public void insert( LuteceDefaultAdminUser user )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT_DEFAULT_USER, Statement.RETURN_GENERATED_KEYS ) )
    {
        int nIndex = 1;
        daoUtil.setString( nIndex++, user.getAccessCode( ) );
        daoUtil.setString( nIndex++, user.getLastName( ) );
        daoUtil.setString( nIndex++, user.getFirstName( ) );
        daoUtil.setString( nIndex++, user.getEmail( ) );
        daoUtil.setInt( nIndex++, user.getStatus( ) );
        daoUtil.setString( nIndex++, user.getPassword( ).getStorableRepresentation( ) );
        daoUtil.setString( nIndex++, user.getLocale( ).toString( ) );
        daoUtil.setInt( nIndex++, user.getUserLevel( ) );
        daoUtil.setBoolean( nIndex++, user.getAccessibilityMode( ) );
        daoUtil.setBoolean( nIndex++, user.isPasswordReset( ) );
        daoUtil.setTimestamp( nIndex++, user.getPasswordMaxValidDate( ) );

        if ( user.getAccountMaxValidDate( ) == null )
        {
            daoUtil.setLongNull( nIndex++ );
        }
        else
        {
            daoUtil.setLong( nIndex++, user.getAccountMaxValidDate( ).getTime( ) );
        }

        daoUtil.setTimestamp( nIndex++, user.getDateLastLogin( ) );
        daoUtil.setString( nIndex, user.getWorkgroupKey( ) );
        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            user.setUserId( daoUtil.getGeneratedKeyInt( 1 ) );
        }
    }
}
 

/** {@inheritDoc} */
@Override public boolean execute(String sql, int autoGeneratedKeys) throws SQLException {
    ensureNotClosed();

    switch (autoGeneratedKeys) {
        case Statement.RETURN_GENERATED_KEYS:
            throw new SQLFeatureNotSupportedException("Auto-generated columns are not supported.");

        case Statement.NO_GENERATED_KEYS:
            return execute(sql);

        default:
            throw new SQLException("Invalid autoGeneratedKeys value.");
    }
}
 

/** {@inheritDoc} */
@Override public int executeUpdate(String sql, int autoGeneratedKeys) throws SQLException {
    ensureNotClosed();

    switch (autoGeneratedKeys) {
        case Statement.RETURN_GENERATED_KEYS:
            throw new SQLFeatureNotSupportedException("Auto-generated columns are not supported.");

        case Statement.NO_GENERATED_KEYS:
            return executeUpdate(sql);

        default:
            throw new SQLException("Invalid autoGeneratedKeys value");
    }
}
 

/**
 * Insert a new attribute field
 * 
 * @param attributeField
 *            the attribute field
 * @return new PK
 */
@Override
public int insert( AttributeField attributeField )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
    {
        int nIndex = 1;
        daoUtil.setInt( nIndex++, attributeField.getAttribute( ).getIdAttribute( ) );
        daoUtil.setString( nIndex++, attributeField.getTitle( ) );
        daoUtil.setString( nIndex++, attributeField.getValue( ) );
        daoUtil.setBoolean( nIndex++, attributeField.isDefaultValue( ) );
        daoUtil.setInt( nIndex++, attributeField.getHeight( ) );
        daoUtil.setInt( nIndex++, attributeField.getWidth( ) );
        daoUtil.setInt( nIndex++, attributeField.getMaxSizeEnter( ) );
        daoUtil.setBoolean( nIndex++, attributeField.isMultiple( ) );
        daoUtil.setInt( nIndex, newPosition( ) );

        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            attributeField.setIdField( daoUtil.getGeneratedKeyInt( 1 ) );
        }
    }

    return attributeField.getIdField( );
}
 

public void testUnequalityVarious() {
    String sql = "select * from sys.systables";
    String schema = "APP";
    int rsh = ResultSet.HOLD_CURSORS_OVER_COMMIT;
    int rst = ResultSet.TYPE_SCROLL_INSENSITIVE;
    int rsc = ResultSet.CONCUR_UPDATABLE;
    int auto = Statement.RETURN_GENERATED_KEYS;
    // Create a one key of each type, all different from each other.
    StatementKey[] keys = new StatementKey[] {
        StatementKeyFactory.newPrepared(sql, schema, rsh),
        StatementKeyFactory.newPrepared(sql, schema, rsh, auto),
        StatementKeyFactory.newPrepared(sql, schema, rst, rsc, rsh),
        StatementKeyFactory.newCallable(sql, schema, rsh),
        StatementKeyFactory.newCallable(sql, schema, rst, rsc, rsh)};
    for (int outer=0; outer < keys.length; outer++) {
        StatementKey current = keys[outer];
        for (int inner=0; inner < keys.length; inner++) {
            if (outer != inner) {
                if (current.equals(keys[inner])) {
                    fail("[" + current.toString() + "] should not equal [" +
                            keys[inner].toString() + "]");
                }
            } else {
                // Should equal itself.
                assertTrue(current.equals(keys[inner]));
            }
        }
    }
}
 

/**
 * {@inheritDoc}
 */
@Override
public void insert( XslExport xslExport )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
    {
        int nIndex = 1;
        daoUtil.setString( nIndex++, xslExport.getTitle( ) );
        daoUtil.setString( nIndex++, xslExport.getDescription( ) );
        daoUtil.setString( nIndex++, xslExport.getExtension( ) );

        if ( xslExport.getFile( ) != null )
        {
            daoUtil.setInt( nIndex++, xslExport.getFile( ).getIdFile( ) );
        }
        else
        {
            daoUtil.setIntNull( nIndex++ );
        }

        daoUtil.setString( nIndex, xslExport.getPlugin( ) );

        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            xslExport.setIdXslExport( daoUtil.getGeneratedKeyInt( 1 ) );
        }
    }
}
 

/**
 * Insert a new record in the table.
 *
 * @param file
 *            instance of the File object to insert
 * @return the id of the new file
 */

@Override
public int insert( File file )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
    {
        int nIndex = 1;
        daoUtil.setString( nIndex++, file.getTitle( ) );

        if ( file.getPhysicalFile( ) != null )
        {
            daoUtil.setInt( nIndex++, file.getPhysicalFile( ).getIdPhysicalFile( ) );
        }
        else
        {
            daoUtil.setIntNull( nIndex++ );
        }

        daoUtil.setInt( nIndex++, file.getSize( ) );
        daoUtil.setString( nIndex++, file.getMimeType( ) );
        daoUtil.setTimestamp( nIndex, new Timestamp( new Date( ).getTime( ) ) );
        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            file.setIdFile( daoUtil.getGeneratedKeyInt( 1 ) );
        }

    }

    return file.getIdFile( );
}
 

/**
 * {@inheritDoc}
 */
public void insert( Portlet portlet )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
    {
        int nIndex = 1;
        daoUtil.setString( nIndex++, portlet.getPortletTypeId( ) );
        daoUtil.setInt( nIndex++, portlet.getPageId( ) );
        daoUtil.setInt( nIndex++, portlet.getStyleId( ) );
        daoUtil.setString( nIndex++, portlet.getName( ) );
        daoUtil.setTimestamp( nIndex++, new Timestamp( new java.util.Date( ).getTime( ) ) );
        daoUtil.setTimestamp( nIndex++, new Timestamp( new java.util.Date( ).getTime( ) ) );
        daoUtil.setInt( nIndex++, portlet.getStatus( ) );
        daoUtil.setInt( nIndex++, portlet.getColumn( ) );
        daoUtil.setInt( nIndex++, portlet.getOrder( ) );
        daoUtil.setInt( nIndex++, portlet.getAcceptAlias( ) );
        daoUtil.setInt( nIndex++, portlet.getDisplayPortletTitle( ) );
        daoUtil.setString( nIndex++, portlet.getRole( ) );
        daoUtil.setInt( nIndex, portlet.getDeviceDisplayFlags( ) );

        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            portlet.setId( daoUtil.getGeneratedKeyInt( 1 ) );
        }
    }
}
 

public void testDAOUtil_str_int_plugin( )
{
    Plugin p = new PluginDefaultImplementation( );
    p.setName( "core" ); // DAOUtil.DEFAULT_MODULE_NAME
    p.setConnectionService( AppConnectionService.getDefaultConnectionService( ) );

    DAOUtil daoUtil = new DAOUtil( SQL_INSERT, Statement.RETURN_GENERATED_KEYS, p );
    doTest( daoUtil, true );
}
 

public void testUnequalityVarious() {
    String sql = "select * from sys.systables";
    String schema = "APP";
    int rsh = ResultSet.HOLD_CURSORS_OVER_COMMIT;
    int rst = ResultSet.TYPE_SCROLL_INSENSITIVE;
    int rsc = ResultSet.CONCUR_UPDATABLE;
    int auto = Statement.RETURN_GENERATED_KEYS;
    // Create a one key of each type, all different from each other.
    StatementKey[] keys = new StatementKey[] {
        StatementKeyFactory.newPrepared(sql, schema, rsh),
        StatementKeyFactory.newPrepared(sql, schema, rsh, auto),
        StatementKeyFactory.newPrepared(sql, schema, rst, rsc, rsh),
        StatementKeyFactory.newCallable(sql, schema, rsh),
        StatementKeyFactory.newCallable(sql, schema, rst, rsc, rsh)};
    for (int outer=0; outer < keys.length; outer++) {
        StatementKey current = keys[outer];
        for (int inner=0; inner < keys.length; inner++) {
            if (outer != inner) {
                if (current.equals(keys[inner])) {
                    fail("[" + current.toString() + "] should not equal [" +
                            keys[inner].toString() + "]");
                }
            } else {
                // Should equal itself.
                assertTrue(current.equals(keys[inner]));
            }
        }
    }
}
 

/**
 * {@inheritDoc}
 */
@Override
public ClientPreparedStatement prepareStatement(String sql,
    int autoGeneratedKeys) throws SQLException {
  super.lock();
  try {
    checkClosedConnection();
    return new ClientPreparedStatement(this, sql,
        autoGeneratedKeys == Statement.RETURN_GENERATED_KEYS);
  } finally {
    super.unlock();
  }
}
 

@Override
public Query buildQuery(String sql, int autoGeneratedKeys) throws SQLException {
    switch (autoGeneratedKeys) {
    case Statement.NO_GENERATED_KEYS:
        return new Query(false, sql);
    case Statement.RETURN_GENERATED_KEYS:
        throw disabled();
    default:
        throw new FbExceptionBuilder()
                .nonTransientException(JaybirdErrorCodes.jb_invalidGeneratedKeysOption)
                .toFlatSQLException();
    }
}
 

/**
 * Insert a new user field
 * 
 * @param userField
 *            the user field
 */
@Override
public void insert( AdminUserField userField )
{
    try ( DAOUtil daoUtil = new DAOUtil( SQL_QUERY_INSERT, Statement.RETURN_GENERATED_KEYS ) )
    {
        int nIndex = 1;
        daoUtil.setInt( nIndex++, userField.getUser( ).getUserId( ) );
        daoUtil.setInt( nIndex++, userField.getAttribute( ).getIdAttribute( ) );
        daoUtil.setInt( nIndex++, userField.getAttributeField( ).getIdField( ) );

        if ( userField.getFile( ) != null )
        {
            daoUtil.setInt( nIndex++, userField.getFile( ).getIdFile( ) );
        }
        else
        {
            daoUtil.setIntNull( nIndex++ );
        }

        daoUtil.setString( nIndex, userField.getValue( ) );

        daoUtil.executeUpdate( );

        if ( daoUtil.nextGeneratedKey( ) )
        {
            userField.setIdUserField( daoUtil.getGeneratedKeyInt( 1 ) );
        }
    }
}
 

/**
 * Execute a SQL statement that may return multiple results.
 * Under some (uncommon) situations a single SQL statement may return
 * multiple result sets and/or update counts.  Normally you can ignore
 * this, unless you're executing a stored procedure that you know may
 * return multiple results, or unless you're dynamically executing an
 * unknown SQL string.  The "execute", "getMoreResults", "getResultSet"
 * and "getUpdateCount" methods let you navigate through multiple results.
 * <p/>
 * The "execute" method executes a SQL statement and indicates the
 * form of the first result.  You can then use getResultSet or
 * getUpdateCount to retrieve the result, and getMoreResults to
 * move to any subsequent result(s).
 *
 * @param sql               any SQL statement
 * @param executeQuery      caller is executeQuery()
 * @param executeUpdate     caller is executeUpdate()
 * @return true if the first result is a ResultSet; false if it is an integer
 * @throws SQLException thrown on failure
 * @see #getResultSet
 * @see #getUpdateCount
 * @see #getMoreResults
 */
private boolean execute(String sql, boolean executeQuery, boolean executeUpdate,
                        int autoGeneratedKeys, int[] columnIndexes, String[] columnNames) throws SQLException {
    synchronized (getConnectionSynchronization()) {

        checkExecStatus();
        if (sql == null) {
            throw newSQLException(SQLState.NULL_SQL_TEXT);
        }
        checkIfInMiddleOfBatch();
        clearResultSets(); // release the last statement executed, if any.

        setupContextStack(); // make sure there's context


        // try to remember the SQL statement in case anybody asks for it
        SQLText = sql;

        try {
            Activation activation;
            try {
                PreparedStatement preparedStatement = lcc.prepareInternalStatement
                        (lcc.getDefaultSchema(), sql, resultSetConcurrency ==
                                java.sql.ResultSet.CONCUR_READ_ONLY, false);
                activation =
                        preparedStatement.getActivation(lcc, resultSetType ==
                                java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE);
                checkRequiresCallableStatement(activation);
                InterruptStatus.restoreIntrFlagIfSeen(lcc);
            } catch (Throwable t) {
                throw handleException(t);
            }


            // this is for a Statement execution
            activation.setSingleExecution();

            //bug 4838 - save the auto-generated key information in activation. keeping this
            //information in lcc will not work work it can be tampered by a nested trasaction
            if (autoGeneratedKeys == Statement.RETURN_GENERATED_KEYS)
                activation.setAutoGeneratedKeysResultsetInfo(columnIndexes, columnNames);
            return executeStatement(activation, executeQuery, executeUpdate);
        } finally {
            restoreContextStack();
        }
    }
}
 

public ShardingSpherePreparedStatement(final ShardingSphereConnection connection, final String sql, final int autoGeneratedKeys) throws SQLException {
    this(connection, sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT, Statement.RETURN_GENERATED_KEYS == autoGeneratedKeys);
}
 

public void testDAOUtil_str_int( )
{
    DAOUtil daoUtil = new DAOUtil( SQL_INSERT, Statement.RETURN_GENERATED_KEYS );
    doTest( daoUtil, true );
}
 

/**
 * Send a resultSet that contain auto generated keys. 2 differences :
 *
 * <ol>
 *   <li>Batch will list all insert ids.
 *   <li>in case of multi-query is set, resultSet will be per query.
 * </ol>
 *
 * <p>example "INSERT INTO myTable values ('a'),('b');INSERT INTO myTable values
 * ('c'),('d'),('e')" will have a resultSet of 2 values, and when Statement.getMoreResults() will
 * be called, a Statement.getGeneratedKeys will return a resultset with 3 ids.
 *
 * @param protocol current protocol
 * @return a ResultSet containing generated ids.
 * @throws SQLException if autoGeneratedKeys was not set to Statement.RETURN_GENERATED_KEYS
 */
public ResultSet getGeneratedKeys(Protocol protocol) throws SQLException {
  if (autoGeneratedKeys != Statement.RETURN_GENERATED_KEYS) {
    throw new SQLException(
        "Cannot return generated keys : query was not set with Statement.RETURN_GENERATED_KEYS");
  }
  if (cmdInformation != null) {
    if (batch) {
      return cmdInformation.getBatchGeneratedKeys(protocol);
    }
    return cmdInformation.getGeneratedKeys(protocol, sql);
  }
  return SelectResultSet.createEmptyResultSet();
}