SQL JDBC solves the problem of automatic automatic growth column Unified processing

sql| Solve | The problem

SQL server2005 JDBC to solve the problem of automatic automatic growth column Unified processing

Author Fbysss
Msn:jameslastchina@hotmail.com
Blog:blog.csdn.net/fbysss
Statement: This article by fbysss Original, reprint please indicate the source
Keywords: automatic growth column, jdbc,sqlserver2005

Background: The system to support a variety of databases, unified insert to get the automatic Growth column processing mode
Problem 1:insert+select Scheme
sqlserver2000 can be obtained by using the Insertsql + SELECT @ @IDENTITY as ' IDENTITY '
But MySQL can only use executeupdate (Insertsql); ExecuteQuery (' SELECT last_insert_id () ') in such a way that otherwise throws an exception: Java.sql.SQLException:Can not issue data manipulation Statements with ExecuteQuery ()
and two words separate treatment, always let people feel uneasy, if more concurrent operation, there is potential danger
Problem 2:getgeneratedkeys Scheme
MySQL Driver support jdbc3.0, implementation of the Getgeneratedkeys ()
But sqlserver2000 JDBC driver does not support Getgeneratedkeys ()
Solution Idea:
Google a bit, it is said that 2005 is supported, so down to see
Download Address: Url:http://download.microsoft.com/download/2/8/9/289dd6a3-eeeb-46dc-9045-d0c6b59bfbc1/sqljdbc_1.1.1501.101_chs.exe
Use summary:
The 1.2005JDBC driver is supported by JDBC 3.0. In other words, it does support the Getgeneratedkeys () method.
2.2005JDBC driver URL and package organization changes greatly.
For example: (please watch carefully, inadvertently see no difference!) ）
================
2000
================
Url= "JDBC:MICROSOFT:SQLSERVER://LOCALHOST:1433;DATABASENAME=MYDB; Selectmethod=cursor "
Driverclassname= "Com.microsoft.sqlserver.jdbc.SQLServerDriver"
================
2005
================
Url= "JDBC:SQLSERVER://LOCALHOST:1433;DATABASENAME=MYDB; Selectmethod=cursor "
Driverclassname= "Com.microsoft.jdbc.sqlserver.SQLServerDriver"
If you do not pay attention to this, there will be an exception: Cannot load JDBC driver class ' Com.microsoft.jdbc.sqlserver.SQLServerDriver '
Take the second step and appear
3. Location of the package: environment variable classpath is not necessary. But Sqljdbc.jar must be placed in the tomcat/common/lib (if it is rented space, may have to discuss with the other side, to see whether the support of the 2005 driver).
Otherwise an exception can occur: Cannot load JDBC driver class ' Com.microsoft.sqlserver.jdbc.SQLServerDriver '

To test the sample method:

public string executeupdate (String sql) throws SQLException ... {
Connection conn = getconnection ();
PreparedStatement pstmt = null;
Try ... {
/**//* Note: jdbc3.0 provides statement.return_generated_keys "to indicate the need to obtain an automatic growth column value from statement,
If you do not set this argument, an exception is thrown when Getgeneratedkeys () is executed: "The generated key is only available if you run the statement." "*/
pstmt = Conn.preparestatement (Sql,statement.return_generated_keys);
Pstmt.executeupdate ();
ResultSet rs = Pstmt.getgeneratedkeys ()//For INSERT preparation
if (Rs.next ()) ... {
return rs.getstring (1);
}
Return "-1";
catch (SQLException ex) ... {
throw New SQLException (
"Failed to execute the SQL statement. " +
Ex.tostring () +
"sql:" +sql);
Finally ... {
Freeconnection (CONN,PSTMT);
}
}