DatabaseSequence.java

package org.seqcode.data.connections;

import java.sql.Connection;

/**
 * utility methods to handle database sequences or auto-increment columns 
 */

public class DatabaseSequence {

    /**
     * <code>getInsertSQL<code> returns the SQL snippet that should be
     * included as a value when inserting into a column that should
     * receive a unique, sequential value.  You should use this
     * in your insert statements.
     * 
     * @param c is the database connection through which the SQL will be generated.
     * @param seqname is the name of the database sequence that will be used.  In Oracle,
     *        this is the name of the sequence.  In MySQL, it's ignored.
     */
    public static String getInsertSQL(Connection c,
                                      String seqname) {
        if (DatabaseConnectionManager.isOracle(c)) {
            return seqname + ".nextval";
        } else if (DatabaseConnectionManager.isMySQL(c)) {
            return "NULL";
        } else {
            throw new IllegalArgumentException("Unknown database type " + DatabaseConnectionManager.getType(c));
        }
    }
    
    /** 
     * <code>getLastSQL</code> returns the SQL snippet to include as a value
     * to get the *last* unique, sequential value generated.  You should use this in a
     * select statement immediately following your insert. c and seqname should be the
     * same as in the call to <code>getInsertSQL</code>.
     */
    public static String getLastSQL(Connection c, String seqname) { 
        if (DatabaseConnectionManager.isOracle(c)) {
            return seqname + ".currval";
        } else if (DatabaseConnectionManager.isMySQL(c)) {
            return "last_insert_id()";
        } else {
            throw new IllegalArgumentException("Unknown database type " + DatabaseConnectionManager.getType(c));
        }
    }
    
    /**
     * <code>getLastSQLStatement</code> returns a complete SQL statement that retrieves
     * the unique, sequential value generated by the last use of <code>getInsertSQL</code>.
     * c and seqname should be the
     * same as in the call to <code>getInsertSQL</code>.
     */
    public static String getLastSQLStatement(Connection c,
                                             String seqname) {
        if (DatabaseConnectionManager.isOracle(c)) {
            return "select " + seqname + ".currval from dual";
        } else if (DatabaseConnectionManager.isMySQL(c)) {
            return "select last_insert_id()";
        } else {
            throw new IllegalArgumentException("Unknown database type " + DatabaseConnectionManager.getType(c));
        }
    }
}