数据源详情链接，SQLserver 2019 代码复制粘贴可产生数据
数据库JDBC 查询sqlserver 2019 利用模板实现输入查询-CSDN博客
效果如下
剥离的链接模块
Slinkv2.java
package SQLadd;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import com.microsoft.sqlserver.jdbc.ISQLServerPreparedStatement;
import java.sql.PreparedStatement;
//对比给的BaseDao后发现每次增删查改都会进行链接，于是进行剥离，把链接部分摘出，形成Slinkv2类
public class Slinkv2 {
// 分离字符串
private String drivername="com.microsoft.sqlserver.jdbc.SQLServerDriver";
private String url="jdbc:sqlserver://localhost:1433;DatabaseName=cyz;encrypt=true;trustServerCertificate=true";
private String userName="sa";
private String userPassWord="sa";
private Connection conn=null;
// 链接数据库，在每次增删查改时使用
public Connection getConnection() {
try {
// 通过名称获取一个类,注释掉会报错在 catch 部分的链接失败，显示没有定义要抓取的错误
Class.forName(drivername);
// 提取conn
// Connection conn=DriverManager.getConnection(url);
conn=DriverManager.getConnection(url,userName,userPassWord);
}
catch(ClassNotFoundException e){
// syso +alt键+/出自动补全
System.out.println("驱动找不到");
e.printStackTrace();
}
catch(SQLException e) {
System.out.println("数据库链接失败");
e.printStackTrace();
}
return conn;
}
// 关闭数据库链接
public void closeAll(Connection conn,Statement stmt,ResultSet rs) {
try {
if(rs!=null) {
rs.close();
}
if(stmt!=null) {
stmt.close();
}
if(rs!=null) {
rs.close();
}
}catch(Exception e) {
System.out.println("关闭失败");
e.printStackTrace();
}
}
}
//后面的是原版 slink ,在改写过程中挤到下面了
//
// public static void main(String[] args) {
//
//
Scanner scanner=new Scanner(System.in);
String choose = scanner.next();
System.out.println(choose);
//
// try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Class.forName(drivername);
// Connection con =DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=cyz;encrypt=true;trustServerCertificate=true","sa","sa");
System.out.println("数据库链接成功\n");
//
// Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("use cyz SELECT bno, rbdate FROM borrow WHERE bno = 332211 ");
//
//
// String sql="select * from borrow where bno=?";
//
// PreparedStatement pst=con.prepareStatement(sql);
// pst.setString(1,choose);
// ResultSet rsv2=pst.executeQuery();
//
? 问号被设定为参数
String sql="SELECT bno,rbdate FROM borrow WHERE bno =?";
设定为模板，其中(ISQLServerPreparedStatement) 是强制类型转换
ISQLServerPreparedStatement pst=(ISQLServerPreparedStatement) con.prepareStatement(sql);
pst.setString(1,"332211");
第一个问号替换为字符串choose
pst.setString(1,choose);
执行查询
ResultSet rsv2=pst.executeQuery();
//
// while(rsv2.next()) {
// System.out.println(rsv2.getString("bno")+"\t"+rsv2.getString("rbdate")+"\t");
// }
while(rsv2.next()) {
System.out.println(rsv2.getString("bno")+"\t"+rsv2.getString("rbdate")+"\t");
}
//
while(rs.next()) {
System.out.println(rs.getString("bno")+"\t"+rs.getString("rbdate")+"\t");
}
// System.out.println("读取完毕");
// stmt.close();
// con.close();
// }
// catch(ClassNotFoundException e) {
// System.out.println("驱动找不到");
// e.printStackTrace();
// }catch(SQLException e) {
// System.out.println("数据库链接不成功");
// e.printStackTrace();
// }
try {
//加载数据驱动
Class.forName ("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con=DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=cyz;encrypt=true;trustServerCertificate=true","sa","sa");//库名，用户名，密码
System.out.println("连接成功");
Statement stmt = con.createStatement();// 创建SQL命令对象
ResultSet rs = stmt.executeQuery("SELECT * FROM borrow");// 返回SQL语句查询结果集(集合) 表名
// 循环输出每一条记录
while (rs.next()) {
// 输出每个字段
System.out.println(rs.getString("bno") + "\t" + rs.getString("bno")+rs.getString("rbdate"));
}
System.out.println("读取完毕");
// 关闭连接
stmt.close();// 关闭命令对象连接
con.close();// 关闭数据库连接
} catch (ClassNotFoundException e) {
System.out.println("驱动找不到");
e.printStackTrace();
}catch (SQLException e) {
System.out.println("数据库连接不成功");
e.printStackTrace();
}
// }
参考链接
原文链接：https://blog.csdn.net/qq_46110556/article/details/116765473
//
//}
测试主函数
CheckMethod.java
package SQLadd;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class CheckMethod {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
String choose = scanner.next();
System.out.println(choose);
Slinkv2 linkmanager = new Slinkv2();
Connection con = linkmanager.getConnection();
// 自动补齐的
try {
Statement stmt = con.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String sql = "select * from borrow where bno=?";
PreparedStatement pst = null;
try {
pst = con.prepareStatement(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
pst.setString(1, choose);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
ResultSet rsv2 = null;
try {
rsv2 = pst.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
while (rsv2.next()) {
System.out.println(rsv2.getString("bno") + "\t" + rsv2.getString("rbdate") + "\t");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
System.out.println("读取完毕");
// 关闭数据流动，实际是设置为 null 来标记为空
linkmanager.closeAll(con, pst, rsv2);
}
}
// try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection con = DriverManager.getConnection(
"jdbc:sqlserver://localhost:1433;DatabaseName=cyz;encrypt=true;trustServerCertificate=true", "sa",
"sa");
System.out.println("数据库链接成功\n");
新增这一条，发现有数据检测不到exception,于是之间禁用excetion,表现为全部注释掉，重新提取，去除意外检测模块
// Connection con = linkmanager.getConnection();
//
// Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("use cyz SELECT bno, rbdate FROM borrow WHERE bno = 332211 ");
//
// String sql = "select * from borrow where bno=?";
//
// PreparedStatement pst = con.prepareStatement(sql);
// pst.setString(1, choose);
// ResultSet rsv2 = pst.executeQuery();
//
? 问号被设定为参数
String sql="SELECT bno,rbdate FROM borrow WHERE bno =?";
设定为模板，其中(ISQLServerPreparedStatement) 是强制类型转换
ISQLServerPreparedStatement pst=(ISQLServerPreparedStatement) con.prepareStatement(sql);
pst.setString(1,"332211");
第一个问号替换为字符串choose
pst.setString(1,choose);
执行查询
ResultSet rsv2=pst.executeQuery();
//
// while (rsv2.next()) {
// System.out.println(rsv2.getString("bno") + "\t" + rsv2.getString("rbdate") + "\t");
// }
while(rsv2.next()) {
System.out.println(rsv2.getString("bno")+"\t"+rsv2.getString("rbdate")+"\t");
}
//
while(rs.next()) {
System.out.println(rs.getString("bno")+"\t"+rs.getString("rbdate")+"\t");
}
// System.out.println("读取完毕");
stmt.close();
con.close();
// } catch (ClassNotFoundException e) {
// System.out.println("驱动找不到");
// e.printStackTrace();
// } catch (SQLException e) {
// System.out.println("数据库链接不成功");
// e.printStackTrace();
// }
// }
// 参考链接
//原文链接：https://blog.csdn.net/qq_46110556/article/details/116765473
//}
