我想在数据库中插入一个记录(在我的情况下是Microsoft SQL Server)使用Java中的JDBC。同时,我想获取插入ID。我如何使用JDBC API实现这一点?
当前回答
我只是想回答这个帖子,而不是评论。
接口java.sql.PreparedStatement
columnIndexes « You can use prepareStatement function that accepts columnIndexes and SQL statement. Where columnIndexes allowed constant flags are Statement.RETURN_GENERATED_KEYS1 or Statement.NO_GENERATED_KEYS[2], SQL statement that may contain one or more '?' IN parameter placeholders. SYNTAX « Connection.prepareStatement(String sql, int autoGeneratedKeys) Connection.prepareStatement(String sql, int[] columnIndexes) Example: PreparedStatement pstmt = conn.prepareStatement( insertSQL, Statement.RETURN_GENERATED_KEYS );
columnNames«列出columnNames,如'id', 'uniqueID', ....在包含应返回的自动生成键的目标表中。如果SQL语句不是INSERT语句,驱动程序将忽略它们。 语法« 连接。prepareStatement(String sql, String[] columnNames) 例子: String columnNames[] = new String[] {"id"}; PreparedStatement pstmt = conn.prepareStatement(insertSQL, columnNames);
完整的例子:
public static void insertAutoIncrement_SQL(String UserName, String Language, String Message) {
String DB_URL = "jdbc:mysql://localhost:3306/test", DB_User = "root", DB_Password = "";
String insertSQL = "INSERT INTO `unicodeinfo`( `UserName`, `Language`, `Message`) VALUES (?,?,?)";
//"INSERT INTO `unicodeinfo`(`id`, `UserName`, `Language`, `Message`) VALUES (?,?,?,?)";
int primkey = 0 ;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection(DB_URL, DB_User, DB_Password);
String columnNames[] = new String[] { "id" };
PreparedStatement pstmt = conn.prepareStatement( insertSQL, columnNames );
pstmt.setString(1, UserName );
pstmt.setString(2, Language );
pstmt.setString(3, Message );
if (pstmt.executeUpdate() > 0) {
// Retrieves any auto-generated keys created as a result of executing this Statement object
java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys();
if ( generatedKeys.next() ) {
primkey = generatedKeys.getInt(1);
}
}
System.out.println("Record updated with id = "+primkey);
} catch (InstantiationException | IllegalAccessException | ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
其他回答
我正在从一个基于jdbc的单线程应用程序中访问Microsoft SQL Server 2008 R2,并在不使用RETURN_GENERATED_KEYS属性或任何PreparedStatement的情况下拉回最后一个ID。看起来是这样的:
private int insertQueryReturnInt(String SQLQy) {
ResultSet generatedKeys = null;
int generatedKey = -1;
try {
Statement statement = conn.createStatement();
statement.execute(SQLQy);
} catch (Exception e) {
errorDescription = "Failed to insert SQL query: " + SQLQy + "( " + e.toString() + ")";
return -1;
}
try {
generatedKey = Integer.parseInt(readOneValue("SELECT @@IDENTITY"));
} catch (Exception e) {
errorDescription = "Failed to get ID of just-inserted SQL query: " + SQLQy + "( " + e.toString() + ")";
return -1;
}
return generatedKey;
}
这篇博文很好地分离了三个主要的SQL Server“最后ID”选项: http://msjawahar.wordpress.com/2008/01/25/how-to-find-the-last-identity-value-inserted-in-the-sql-server/ -还不需要其他两个。
我只是想回答这个帖子,而不是评论。
接口java.sql.PreparedStatement
columnIndexes « You can use prepareStatement function that accepts columnIndexes and SQL statement. Where columnIndexes allowed constant flags are Statement.RETURN_GENERATED_KEYS1 or Statement.NO_GENERATED_KEYS[2], SQL statement that may contain one or more '?' IN parameter placeholders. SYNTAX « Connection.prepareStatement(String sql, int autoGeneratedKeys) Connection.prepareStatement(String sql, int[] columnIndexes) Example: PreparedStatement pstmt = conn.prepareStatement( insertSQL, Statement.RETURN_GENERATED_KEYS );
columnNames«列出columnNames,如'id', 'uniqueID', ....在包含应返回的自动生成键的目标表中。如果SQL语句不是INSERT语句,驱动程序将忽略它们。 语法« 连接。prepareStatement(String sql, String[] columnNames) 例子: String columnNames[] = new String[] {"id"}; PreparedStatement pstmt = conn.prepareStatement(insertSQL, columnNames);
完整的例子:
public static void insertAutoIncrement_SQL(String UserName, String Language, String Message) {
String DB_URL = "jdbc:mysql://localhost:3306/test", DB_User = "root", DB_Password = "";
String insertSQL = "INSERT INTO `unicodeinfo`( `UserName`, `Language`, `Message`) VALUES (?,?,?)";
//"INSERT INTO `unicodeinfo`(`id`, `UserName`, `Language`, `Message`) VALUES (?,?,?,?)";
int primkey = 0 ;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn = DriverManager.getConnection(DB_URL, DB_User, DB_Password);
String columnNames[] = new String[] { "id" };
PreparedStatement pstmt = conn.prepareStatement( insertSQL, columnNames );
pstmt.setString(1, UserName );
pstmt.setString(2, Language );
pstmt.setString(3, Message );
if (pstmt.executeUpdate() > 0) {
// Retrieves any auto-generated keys created as a result of executing this Statement object
java.sql.ResultSet generatedKeys = pstmt.getGeneratedKeys();
if ( generatedKeys.next() ) {
primkey = generatedKeys.getInt(1);
}
}
System.out.println("Record updated with id = "+primkey);
} catch (InstantiationException | IllegalAccessException | ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
当使用Statement时遇到“不支持的功能”错误。RETURN_GENERATED_KEYS,试试这个:
String[] returnId = { "BATCHID" };
String sql = "INSERT INTO BATCH (BATCHNAME) VALUES ('aaaaaaa')";
PreparedStatement statement = connection.prepareStatement(sql, returnId);
int affectedRows = statement.executeUpdate();
if (affectedRows == 0) {
throw new SQLException("Creating user failed, no rows affected.");
}
try (ResultSet rs = statement.getGeneratedKeys()) {
if (rs.next()) {
System.out.println(rs.getInt(1));
}
rs.close();
}
其中BATCHID是自动生成的id。
您可以使用以下java代码来获取新的插入id。
ps = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, quizid);
ps.setInt(2, userid);
ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
lastInsertId = rs.getInt(1);
}
创建生成的列 String generatedColumns[] = {"ID"}; 将这个生成的列传递给您的语句 PreparedStatement stmtInsert = conn.prepareStatement(insertSQL, generatedColumns); 使用ResultSet对象获取语句上的GeneratedKeys ResultSet rs = stmtInsert.getGeneratedKeys(); If (rs.next()) { id = rs.getLong(1); system . out。println("插入ID -" + ID);//显示插入的记录 }
推荐文章
- 如何格式化Joda-Time DateTime仅为mm/dd/yyyy?
- 如何在POM.xml中引用环境变量?
- 如何在android中复制一个文件?
- 将整数转换为字符串,以逗号表示千
- 接口方法的最终参数-有什么意义?
- GROUP BY with MAX(DATE)
- 删除id与其他表不匹配的sql行
- Java中的@UniqueConstraint注释
- 等价的限制和偏移SQL Server?
- 如何在清洁模式下运行eclipse ?如果我们这样做会发生什么?
- 获取java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory异常
- Java中的正则表达式命名组
- c#和Java的主要区别是什么?
- 什么是NullPointerException,我如何修复它?
- 在Java中使用“final”修饰符