文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

sqlserver jdbc增删改查

2014-05-04 22:28

关注

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 selectAllBook() {
        System.out.println("开始执行");

        String sql = "select * from student";
        List list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            // 加载驱动
            Class.forName(jdbcDriver);
            // 获取连接
            conn = DriverManager.getConnection(url, userName, password);
            // 创建会话
            ps = conn.prepareStatement(sql);
            // 获取结果集
            rs = ps.executeQuery();
            // 遍历结果集
            while (rs.next()) {
                Map map = new HashMap<>();

                map.put("SID",rs.getString("SID"));
                map.put("SNAME",rs.getString("SNAME"));
                map.put("MAJOR",rs.getString("MAJOR"));
                map.put("BIRTH",rs.getString("BIRTH"));
                map.put("SCORE",rs.getString("SCORE"));
                map.put("CID",rs.getString("CID"));
                map.put("STATUS",rs.getString("STATUS"));
//                int id = rs.getInt("id");
//                String bookName = rs.getString("bookName");
//                String author = rs.getString("author");
//                Date pubDate = rs.getDate("pubDate");

//                Book book = new Book(id, bookName, author, pubDate);
                list.add(map);
            }


        } 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 list;
    }

//    public static void main(String[] args) {
//        Sqlserver bookDao = new Sqlserver();
//        int idcount = bookDao.selectBookByName("test");
//
//        bookDao.insertBook(idcount+1);
//        bookDao.updateBook(idcount);
//        bookDao.deleteBook(idcount);
//
//        List maps = bookDao.selectAllBook();
//        System.out.println(maps);
//    }
}

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

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯