sqlserver jdbc增删改查

随便个SpringBoot的Maven项目都可以。

pom.xml 配置

    4.0.0
    
        org.springframework.boot
        spring-boot-starter-parent
        2.2.5.RELEASE
         
    
    com.example
    demo
    0.0.1-SNAPSHOT
    demo
    Demo project for Spring Boot

    
        1.8
    

    


        
            org.springframework.boot
            spring-boot-starter
        


        
            org.springframework.boot
            spring-boot-starter-test
            test
            
                
                    org.junit.vintage
                    junit-vintage-engine
                
            
        
        
            org.springframework.boot
            spring-boot-starter-WEB
        

        
            com.microsoft.sqlserver
            sqljdbc4
            4.0
        
    

    
        
            
                org.springframework.boot
                spring-boot-maven-plugin
            
        
    

package com.example.demo.sqlserver;

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/sqlserver")
public class Sqlserver {

    private final String jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    private final String url = "jdbc:sqlserver://10.0.6.224:1433;DatabaseName=school";
    private final String userName = "xxx";
    private final String passWord  = "xxx";

    @GetMapping("/insert")
    public int insertBook(int idcount) {
        System.out.println("开始执行");

        idcount = selectBookByName("test")+1;

        int n = 0;
        String sql = "insert into student (SID,SNAME,MAJOR,BIRTH,SCORE,CID,STATUS) VALUES (?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        java.sql.Date pubDate = null;
        try {
            // 加载驱动
            Class.forName(jdbcDriver);
            // 获取连接
            conn = DriverManager.getConnection(url, userName, password);
            // 创建会话
            ps = conn.prepareStatement(sql);
            // 设置 ? 的值

            ps.setString(1, String.valueOf(idcount));
            ps.setString(2,"stu100" );
            ps.setString(3, "test");
            pubDate = new java.sql.Date(System.currentTimeMillis());
            ps.setDate(4, pubDate);
            ps.setString(5, "23.23");
            ps.setString(6, "1");
            ps.setString(7, "1");

            // 执行sql
            n = ps.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {     // 关闭数据库资源
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return n;
    }

    @GetMapping("/delete")
    public int deleteBook(int id) {
        System.out.println("开始执行");

        id =  selectBookByName("test");
        String sql = "delete from student where SID=?";
        int n = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            // 加载驱动
            Class.forName(jdbcDriver);
            // 获取连接
            conn = DriverManager.getConnection(url, userName, password);
            // 创建会话
            ps = conn.prepareStatement(sql);
            // 设置 ? 的值
            ps.setInt(1, id);
            // 执行sql
            n =  ps.executeUpdate();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return n;
    }

    @GetMapping("/update")
    public int updateBook(int id) {
        System.out.println("开始执行");

        id = selectBookByName("test");
        String sql = "update student set SNAME=?, MAJOR=? where SID=?";
        int n = 0;
        Connection conn =  null;
        PreparedStatement ps = null;
        try {
            // 加载驱动
            Class.forName(jdbcDriver);
            // 获取连接
            conn = DriverManager.getConnection(url, userName, password);
            // 创建会话
            ps = conn.prepareStatement(sql);
            ps.setString(1, "test101");
            ps.setString(2, "aa");
            ps.setInt(3, id);
            n = ps.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return n;
    }

    @GetMapping("/select")
    public int selectBookByName(String bookName) {
        System.out.println("开始执行");

//        String sql = "select MAX(SID) as IDCOUNT from student where MAJOR=?";
        String sql = "select MAX(SID) as IDCOUNT from student";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            // 加载驱动
            Class.forName(jdbcDriver);
            // 获取连接
            conn = DriverManager.getConnection(url, userName, password);
            // 创建会话
            ps = conn.prepareStatement(sql);
//            ps.setString(1, bookName);
            rs = ps.executeQuery();
            if (rs.next()) {
                return Integer.parseInt(rs.getString("IDCOUNT"));
//                int id = rs.getInt("id");
//                String name = rs.getString("bookName");
//                String author = rs.getString("author");
//                java.util.Date pubDate = rs.getDate("pubDate");
//                book = new Book(id, name, author, pubDate);
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != ps) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (null != conn) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @GetMapping("/selectall")
    public List

student.sql

-- ----------------------------
-- Table structure for student
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N"[dbo].[student]") AND type IN ("U"))
	DROP TABLE [dbo].[student]
Go

CREATE TABLE [dbo].[student] (
  [SID] int  NOT NULL,
  [SNAME] varchar(20) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [MAJOR] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [BIRTH] datetime  NULL,
  [SCORE] float(53)  NULL,
  [CID] int  NULL,
  [STATUS] varchar(3) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[student] SET (LOCK_EScalaTION = TABLE)
GO


-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN TRANSACTION
GO

INSERT INTO [dbo].[student]  VALUES (N"3", N"ggg", N"bbbb", N"2020-01-21 00:00:00.000", N"99.900001525878906", N"1", NULL)
GO

INSERT INTO [dbo].[student]  VALUES (N"4", N"stu4", N"cc", N"2019-04-03 16:11:26.130", N"99.900001525878906", N"1", NULL)
GO

COMMIT
GO


-- ----------------------------
-- Primary Key structure for table student
-- ----------------------------
ALTER TABLE [dbo].[student] ADD CONSTRAINT [PK__student__CA19597003317E3D] PRIMARY KEY CLUSTERED ([SID])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

浏览器访问 查询最大id

Http://localhost:8082/sqlserver/select?bookName=2

查询所有

http://localhost:8082/sqlserver/selectall

新增

http://localhost:8082/sqlserver/insert?idcount=2

修改

http://localhost:8082/sqlserver/update?id=1

删除

http://localhost:8082/sqlserver/delete?id=1