数据库获取插入生成的ID

前言：开发中需要获取上一次插入时自动生成的ID，需要各种数据库的实现，参看logback源码，简单记录一下。

1、定义SQLDialect接口。

package ch.qos.logback.core.db.dialect;

/**
 * @author Ceki Gülcü
 *
 */
public interface SQLDialect {
    String getSelectInsertId();
}

2、MySQL的SQLDialect。

package ch.qos.logback.core.db.dialect;

/**
 * 
 * 
 * @author Ceki
 *
 */
public class MySQLDialect implements SQLDialect {
    public static final String SELECT_LAST_INSERT_ID = "SELECT LAST_INSERT_ID()";

    public String getSelectInsertId() {
        return SELECT_LAST_INSERT_ID;
    }
}

3、 H2的SQLDialect。

package ch.qos.logback.core.db.dialect;

/**
 * The H2 dialect.
 * 
 * @author Ceki Gülcü
 */
public class H2Dialect implements SQLDialect {
    public static final String SELECT_CURRVAL = "CALL IDENTITY()";

    public String getSelectInsertId() {
        return SELECT_CURRVAL;
    }

}

3、HSQL的SQLDialect。

package ch.qos.logback.core.db.dialect;

/**
 * The HSQLDB dialect.
 * 
 * @author Ceki Gülcü
 */
public class HSQLDBDialect implements SQLDialect {
    public static final String SELECT_CURRVAL = "CALL IDENTITY()";

    public String getSelectInsertId() {
        return SELECT_CURRVAL;
    }

}

 4、MsSQL的Dialect。

package ch.qos.logback.core.db.dialect;

/** 
* The MS SQL Server dialect is untested. 
* 
* Note that the dialect is not needed if your JDBC driver supports 
* the getGeneratedKeys method introduced in JDBC 3.0 specification.
* 
* @author James Stauffer 
*/
public class MsSQLDialect implements SQLDialect {
    public static final String SELECT_CURRVAL = "SELECT @@identity id";

    public String getSelectInsertId() {
        return SELECT_CURRVAL;
    }
}

5、Oracle的Dialect。

package ch.qos.logback.core.db.dialect;

/**
 * The Oracle dialect. Tested successfully on Oracle9i Release 9.2.0.3.0 by 
 * James Stauffer.
 * 
 * @author Ceki Gülcü
 */
public class OracleDialect implements SQLDialect {
    public static final String SELECT_CURRVAL = "SELECT logging_event_id_seq.currval from dual";

    public String getSelectInsertId() {
        return SELECT_CURRVAL;
    }

}

 6、PostgreSQL的Dialect。

package ch.qos.logback.core.db.dialect;

/**
 * 
 * @author ceki
 *
 */
public class PostgreSQLDialect implements SQLDialect {
    public static final String SELECT_CURRVAL = "SELECT currval('logging_event_id_seq')";

    public String getSelectInsertId() {
        return SELECT_CURRVAL;
    }
}

7、SQLite的Dialect。

package ch.qos.logback.core.db.dialect;

/**
 * SQLite dialect
 *
 * Note that the dialect is not needed if your JDBC driver supports the
 * getGeneratedKeys method introduced in JDBC 3.0 specification.
 *
 * @author Anthony Trinh
 */
public class SQLiteDialect implements SQLDialect {
    public static final String SELECT_CURRVAL = "SELECT last_insert_rowid();";

    public String getSelectInsertId() {
        return SELECT_CURRVAL;
    }
}

8、Sybase的Dialect。

package ch.qos.logback.core.db.dialect;

public class SybaseSqlAnywhereDialect implements SQLDialect {

    /** 
    * The Sybase SQLAnywhere Dialect 
    * 
    * Note that the dialect is not needed if your JDBC driver supports 
    * the getGeneratedKeys method introduced in JDBC 3.0 specification.
    * 
    * @author Michael Lynch 
    */

    public static final String SELECT_CURRVAL = "SELECT @@identity id";

    public String getSelectInsertId() {
        return SELECT_CURRVAL;
    }

}

9、枚举类

package ch.qos.logback.core.db.dialect;

public enum SQLDialectCode {
    UNKNOWN_DIALECT, POSTGRES_DIALECT, MYSQL_DIALECT, ORACLE_DIALECT, MSSQL_DIALECT, HSQL_DIALECT, H2_DIALECT, SYBASE_SQLANYWHERE_DIALECT, SQLITE_DIALECT;
}