sqlserverjdbc增删改查
随便个springboot的maven项目都可以。
pom.xml 配置
<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>sqljdbc4</artifactId>
<version>4.0</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
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<Map> selectAllBook() {
System.out.println("开始执行");
String sql = "select * from student";
List<Map> 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<Map> maps = bookDao.selectAllBook();
// System.out.println(maps);
// }
}
student.sql
/* Navicat Premium Data Transfer
Source Server : 10.0.6.224
Source Server Type : SQL Server
Source Server Version : 10001442
Source Host : 10.0.6.224:1433
Source Catalog : school
Source Schema : dbo
Target Server Type : SQL Server
Target Server Version : 10001442
File Encoding : 65001
Date: 09/04/2020 11:42:54
*/
-- ----------------------------
-- 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
以上是 sqlserverjdbc增删改查 的全部内容， 来源链接： utcz.com/z/533064.html
