IDEA中使用JDBC访问SQL SERVER(九)更新大型数据
https://docs.microsoft.com/zh-cn/sql/connect/jdbc/updating-large-data-sample?view=sql-server-ver15
备注
Microsoft JDBC Driver for SQL Server 提供四个类库文件:sqljdbc.jar、sqljdbc4.jar、sqljdbc41.jar 或 sqljdbc42.jar,具体使用哪个文件取决于首选的 Java Runtime Environment (JRE) 设置。 此示例使用 isWrapperFor 和 unwrap 方法,这两个方法是在 JDBC 4.0 API 中引入的,用于访问特定于驱动程序的响应缓冲方法。 为了编译和运行此示例,您需要对 JDBC 4.0 提供支持的 sqljdbc4.jar 类库。 有关选择哪个 JAR 文件的详细信息,请参阅 JDBC 驱动程序的系统要求。
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE 参见:https://www.cnblogs.com/wfy680/p/14970687.html
package com.example.wfydemo.controller; import com.microsoft.sqlserver.jdbc.SQLServerStatement; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import java.io.Reader; import java.sql.*; @RestController public class jdbc_UpdateLargeData { @RequestMapping("/jdbc_UpdateLargeData") public String jdbc_UpdateLargeData ( ) { String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;databaseName=AdventureWorks;user=sa;password=6617saSA";//sa身份连接 try (Connection con = DriverManager.getConnection(connectionUrl);) { Statement stmt = con.createStatement(); //创建Statement对象,向数据库发送SQL语句 Statement stmt1 = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); createTable(stmt); // 因为DocumentSummary可能很大, 必须保证从数据库逐步读取,即使使用服务器游标更新数据 // 访问Microsoft JDBC驱动程序的SQL Server特定方法的推荐方法是使用JDBC 4.0包装器功能。 // 下面的代码语句演示了如何使用语句。isWrapperFor和语句。展开方法来访问特定于驱动程序的响应缓冲方法。 //stmt设置了adaptive , 对stmt1有影响吗? if (stmt.isWrapperFor(com.microsoft.sqlserver.jdbc.SQLServerStatement.class)) { SQLServerStatement SQLstmt = stmt.unwrap(com.microsoft.sqlserver.jdbc.SQLServerStatement.class); SQLstmt.setResponseBuffering("adaptive"); System.out.println("Response buffering mode has been set to " + SQLstmt.getResponseBuffering()); } // Select all of the document summaries. try (ResultSet rs = stmt1.executeQuery("SELECT Title, DocumentSummary FROM Document_JDBC_Sample")) { while (rs.next()) { // Retrieve the original document summary. try (Reader reader = rs.getCharacterStream("DocumentSummary")) { if (reader == null) { // Update the document summary. System.out.println("Updating " + rs.getString("Title")); rs.updateString("DocumentSummary", "Work in progress"); rs.updateRow(); } } } } } // Handle any errors that may have occurred. catch (Exception e) { e.printStackTrace(); } return "jdbc_UpdateLargeData"; } private static void createTable(Statement stmt) throws SQLException { stmt.execute("if exists (select * from sys.objects where name = 'Document_JDBC_Sample')" + "drop table Document_JDBC_Sample"); String sql = "CREATE TABLE Document_JDBC_Sample (" + "[DocumentID] [int] NOT NULL identity," + "[Title] [char](50) NOT NULL," + "[DocumentSummary] [varchar](max) NULL)"; stmt.execute(sql); sql = "INSERT Document_JDBC_Sample VALUES ('title1','summary1') "; stmt.execute(sql); sql = "INSERT Document_JDBC_Sample (title) VALUES ('title2') "; stmt.execute(sql); sql = "INSERT Document_JDBC_Sample (title) VALUES ('title3') "; stmt.execute(sql); sql = "INSERT Document_JDBC_Sample VALUES ('title4','summary3') "; stmt.execute(sql); } }