package com.example.wfydemo.controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;

@RestController
public class UpdateResultSet {
    @RequestMapping("/UpdateResultSet")
    public String UpdateResultSet() {

        // Create a variable for the connection string.
        String connectionUrl = "jdbc:sqlserver://127.0.0.1:1433;databaseName=springbootTestDB;user=sa;password=6617saSA";//sa身份连接

        try (Connection con = DriverManager.getConnection(connectionUrl);
             Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);) {

            // Create and execute an SQL statement, retrieving an updateable result set.
            String SQL = "SELECT * FROM T_User ;";
            ResultSet rs = stmt.executeQuery(SQL);
            displayRow("原来的内容：", rs);

            // Insert a row of data.
            rs.moveToInsertRow();
            rs.updateString("name", "user10");
            rs.updateString("password", "121212");
            rs.insertRow();

            // Retrieve the inserted row of data and display it.
            SQL = "SELECT * from T_User WHERE name = ‘user10‘;";
            rs = stmt.executeQuery(SQL);
            displayRow("ADDED ROW", rs);

            // Update the row of data.
            rs.first();
            rs.updateString("password", "0123456789");
            rs.updateRow();

            // Retrieve the updated row of data and display it.
            rs = stmt.executeQuery(SQL);
            displayRow("UPDATED ROW", rs);

            // Delete the row of data.
            rs.first();
            rs.deleteRow();
            System.out.println("ROW DELETED");

            //查询结果为空，插入一条记录
            SQL = "SELECT * FROM T_User where 1 = 2;";
            rs = stmt.executeQuery(SQL);
            rs.moveToInsertRow();
            rs.updateString("name", "user插入");
            rs.updateString("password", "123321");
            rs.insertRow();
            SQL = "SELECT * from T_User  ;";
            rs = stmt.executeQuery(SQL);
            displayRow("insert", rs);
        }
        // Handle any errors that may have occurred.
        catch (SQLException e) {
            e.printStackTrace();
        }
        return "UpdateResultSet";
    }

    private static void displayRow(String title,
                                   ResultSet rs) throws SQLException {
        System.out.println(title);
        while (rs.next()) {
            System.out.println(rs.getString("name") + " : " + rs.getString("password"));
        }
    }
}