数据库开发中,如果有主表
T1,子表
T2的情况下,假设
T1中的主键是自动增长的,有很多时候需要在同一个事务中对
T1进行插入操作的同时,对
T2进行插入操作,这是需要
T1的自动增长列,美美遇到此时都感觉非常麻烦。比如
MySQL中有表:
CREATE TABLE `test` (
`tid` int(4) NOT NULL auto_increment,
`tnamve` varchar(20) default NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
如何在插入的同时获取到自动增长列的值呢?
在
JDBC的
3.0中有相关的实现。
Class.forName(
"com.mysql.jdbc.Driver"
);
String url=
"jdbc:mysql://localhost:3306/test";
Connection conn=DriverManager.getConnection(url
,
"root"
,
"root"
);
Statement stmt=conn.createStatement();
String sql=
"insert into test values
(default,'testidentity')"
;
//Statement.RETURN_GENERATED_KEYS指明该常量指示生成的键应该可用于检索
int
rows=stmt.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
ResultSet rs=stmt.getGeneratedKeys();
rs.next();
int
result=rs.getInt(1);
System.
out
.println(obj);
结果就是自动增长列的值,
JDBC3.0中提供的
getGeneratedKeys
()方法就是解决这样问题的,上面的
JDBC
代码在
mysql5
中测试通过。但是在
sqlSQLserver2000
中的驱动没有这样的支持,据说在
sqlSQLserver2005
中有这样的支持。在
SqlServer2000
该如何处理呢?只能借助于函数
SCOPE_IDENTITY
、
IDENT_CURRENT 或者全局变量
@@IDENTITY了。下面看看他们的用法
在
SQLServer中
SCOPE_IDENTITY、
IDENT_CURRENT 和
@@IDENTITY 在功能上相似,它们都返回插入到
IDENTITY 列中的值。
IDENT_CURRENT 不受作用域和会话的限制,而受限于指定的表。
语法:
IDENT_CURRENT(‘表名
’)
使用
IDENT_CURRENT将返回特定表中的最后
IDENTITY的值,该值也许是别的事务插入的,也就是说:不受作用域和会话的限制,仅仅受限于指定的表。
SCOPE_IDENTITY 和
@@IDENTITY 返回在当前会话中的任何表内所生成的最后一个标识值。但是,
SCOPE_IDENTITY 只返回插入到
当前作用域中的值;
@@IDENTITY
不受限于特定的作用域。
例如,有两个表
T1 和
T2,在
T1 上定义了一个
INSERT 触发器。当将某行插入
T1 时,触发器被激发,并在
T2 中插入一行。此例说明了两个作用域:一个是在
T1 上的插入,另一个是作为触发器的结果在
T2 上的插入。
假设
T1 和
T2 都有
IDENTITY 列,
@@IDENTITY 和
SCOPE_IDENTITY 将在
T1 上的
INSERT 语句的最后返回不同的值。
@@IDENTITY 返回插入到当前会话中任何作用域内的最后一个
IDENTITY 列值,该值是插入
T2 中的值。
SCOPE_IDENTITY() 返回插入
T1 中的
IDENTITY 值,该值是发生在相同作用域中的最后一个
INSERT。如果在作用域中发生插入语句到标识列之前唤醒调用
SCOPE_IDENTITY() 函数,则该函数将返回
NULL 值。
在应用软件开发中,如果在同一个事务中需要自动增长列,那么
SCOPE_IDENTITY()是最好的选择,具体用法如下:
String
driver=
"com.microsoft.jdbc.sqlserver.SQLServerDriver"
;
String url=
"jdbc:microsoft:sqlserver://localhost:1433;databaseName=pubs"
;
Class.forName(driver);
Connection conn=DriverManager.getConnection(url,
"sa"
,
""
);
Statement stmt=conn.createStatement();
String sql=
"insert into test values ('testidentity') select scope_identity()"
;
ResultSet rs=stmt.executeQuery(sql);
rs.next();
int
result=rs.getInt(1);
System.
out
.println(result);
在sqlserver 2005中已经支持jdbc3.0了,因此该问题已经解决具体测试如下:
String driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url="jdbc:sqlserver://localhost:1433;databaseName=testDb";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
Connection conn=DriverManager.getConnection(url,"sa", "sa");
Statement stmt=conn.createStatement();
stmt.executeUpdate("insert into ... ",Statement.RETURN_GENERATED_KEYS);
ResultSet rs=stmt.getGeneratedKeys();
rs.next();
int id=rs.getInt(1);
System.out.println(id);
} catch (SQLException e) {
e.printStackTrace();
}
String url="jdbc:sqlserver://localhost:1433;databaseName=testDb";
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
Connection conn=DriverManager.getConnection(url,"sa", "sa");
Statement stmt=conn.createStatement();
stmt.executeUpdate("insert into ... ",Statement.RETURN_GENERATED_KEYS);
ResultSet rs=stmt.getGeneratedKeys();
rs.next();
int id=rs.getInt(1);
System.out.println(id);
} catch (SQLException e) {
e.printStackTrace();
}