根据mooc 数据库旧代码 实现剥离数据库链接单独成类，并进行测试

数据源详情链接，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

//}