数据库JDBC 查询sqlserver 2019 利用模板实现输入查询

a1309602336

已于 2024-06-05 10:25:49 修改

阅读量170

点赞数 2

文章标签：数据库

于 2024-06-05 10:03:45 首次发布

本文链接：https://blog.csdn.net/a1309602336/article/details/139462665

版权

数据源

SQLserver2019的数据，在数据库名为 cyz 中创建几个表

create table book
(bno char(10) primary key,
cno char(10),
bname char(20),
bauthor char(20),
bpress varchar(50),
bprice decimal(8,2)
)

create table reader
(rno char(10) primary key,
rdepartment char(20),
rname char(20),
rsex char(2),
rtitle char(10),
raddress varchar(50)
)

create table borrow
(rno char(10),
bno char(10),
rbdate datetime,
primary key(rno,bno,rbdate),
foreign key(bno) references book(bno),
foreign key(rno) references reader(rno)
)

insert into book values('445504','TP3/12','数据库系统原理','王浩','科学出版社',19.9)
insert into book values('445502','TP3/12','数据库实践','王强','科学出版社',17.9)
insert into book values('445503','TP3/12','数据库系统','王强','科学出版社',17.9)
insert into book values('332211','TP5/10','计算机基础','李伟','高等教育出版社',18.00)
insert into book values('112266','TP3/12','FoxBASE','张三','电子工业出版社',23.6)
insert into book values('665544','TS7/21','高等数学','刘明','高等教育出版社',20.00)
insert into book values('114455','TR9/12','线性代数','孙业','北京大学出版社',20.80)
insert into book values('113388','TR7/90','大学英语','胡玲','清华大学出版社',12.5)
insert into book values('446601','TP4/13','数据库基础','马凌云','人民邮电出版社',22.5)
insert into book values('446602','TP4/13','数据库基础','马凌云','人民邮电出版社',22.5)
insert into book values('446603','TP4/13','数据库基础','马凌云','人民邮电出版社',22.5)
insert into book values('449901','TP4/14','FoxPro大全','周虹','科学出版社',32.7)
insert into book values('449902','TP4/14','FoxPro大全','周虹','科学出版社',32.7)
insert into book values('118801','TP4/15','计算机网络','黄力钧','高等教育出版社',21.8)
insert into book values('118802','TP4/15','计算机网络','黄力钧','高等教育出版社',21.8)


insert into reader values('111','信息系','王维利','女','教授','1号楼424')
insert into reader values('112','财会系','李立','男','副教授','2号楼316')
insert into reader values('113','经济系','张三','男','讲师','3号楼105')
insert into reader values('114','信息系','周华发','男','讲师','1号楼316')
insert into reader values('115','信息系','赵正义','男','工程师','1号楼224')
insert into reader values('116','信息系','李明','男','副教授','1号楼318')
insert into reader values('117','计算机系','李小峰','男','助教','1号楼214')
insert into reader values('118','计算机系','许鹏飞','男','助工','1号楼216')
insert into reader values('119','计算机系','刘大龙','男','教授','1号楼318')
insert into reader values('120','国际贸易','李雪','男','副教授','4号楼506')
insert into reader values('121','国际贸易','李爽','女','讲师','4号楼510')
insert into reader values('122','国际贸易','王纯','女','讲师','4号楼512')
insert into reader values('123','财会系','沈小霞','女','助教','2号楼202')
insert into reader values('124','财会系','朱海','男','讲师','2号楼210')
insert into reader values('125','财会系','马英明','男','副教授','2号楼212')


insert into borrow values('112','445502','2017-3-19')
insert into borrow values('111','332211','2017-2-12')
insert into borrow values('111','445502','2017-8-21')
insert into borrow values('112','332211','2017-3-14')
insert into borrow values('112','665544','2017-3-14')
insert into borrow values('114','665544','2018-10-21')
insert into borrow values('120','114455','2018-11-2')
insert into borrow values('120','118801','2018-10-18')
insert into borrow values('119','446603','2018-12-12')
insert into borrow values('113','445502','2019-10-23')
insert into borrow values('115','449902','2019-8-21')
insert into borrow values('118','118801','2019-9-10')

JAVA eclipse

package our2233;

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;



public class 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");
			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

}