import java.sql.*;
import java.util.*;
import javax.swing.*;
import java.text.SimpleDateFormat;
public class t
{
public static String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动
public static String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=图书借阅"; //连接服务器和数据库sample
public static String userName = "yyx"; //默认用户名
public static String userPwd = "10"; //密码
public static Connection dbconn;
static boolean m = true;
static int N;
private static Scanner scanner = new Scanner(System.in);
public static void main(String[] srg) //主函数
{
while(m)
{
System.out.println("--------图书借阅系统----------");
System.out.println("--------1:新进图书基本信息的输入-----------");
System.out.println("--------2:图书基本信息的查询-----------");
System.out.println("--------3:为借书人办理注册-------");
System.out.println("--------4:办理借书手续-----------");
System.out.println("--------5:借书信息的浏览-------------");
System.out.println("--------6:借书信息的查询-------------");
System.out.println("--------7:退出系统-------------");
System.out.println("------请输入您要进行的服务------");
N=scanner.nextInt();
switch(N)
{
case 1:
insert();
break;
case 2:
bookSearch();
break;
case 3:
stuRegister();
break;
case 4:
borrowBook();
break;
case 5:
scan();
break;
case 6:
search();
break;
case 7: m =false;
break;
default:System.out.println("请在1--7之间选择");
}
}
}
public static void insert() //新进图书基本信息的输入
{
try{
Class.forName(driverName);//载入驱动
dbconn = DriverManager.getConnection(dbURL,userName,userPwd);//连接数据库
String number=null, bookName=null, identifier=null, writer=null, quantity=null;
System.out.println("请输入您要添加的数据");
System.out.print("ISBN: ");
number = scanner.next();
System.out.print("名称: ");
bookName = scanner.next();
System.out.print("分类号: ");
identifier = scanner.next();
System.out.print("作者: ");
writer = scanner.next();
System.out.print("在馆数: ");
quantity = scanner.next();
String sql = "insert into 图书 (ISBN,名称,分类号,作者,在馆数) values('"+ number +"','"+ bookName +"','"+ identifier +"','"+ writer +"','"+ quantity +"')";
Statement stmt = dbconn.createStatement();
stmt.executeUpdate(sql);
System.out.println("信息已插入!\n\n请重新选择您要进行的服务:");
stmt.close();
}catch(Exception e){}
}
public static void bookSearch()//图书基本信息的查询
{
String inputISBN=null,ISBN = null;
int flags=0;
try{
Class.forName(driverName);//载入驱动
dbconn = DriverManager.getConnection(dbURL,userName,userPwd);//连接数据库
System.out.println("请输入书号ISBN:");
String sql = "select * from 图书 ";
Statement stmt = dbconn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
inputISBN = scanner.next();
while(true)
{
while(rs.next())
{
ISBN = rs.getString("ISBN");
if( inputISBN.equals(ISBN))
{
String number = rs.getString("ISBN");
String bookName = rs.getString("名称");
String identifier = rs.getString("分类号");
String writer = rs.getString("作者");
String quantity = rs.getString("在馆数");
System.out.println("ISBN 名称 分类号 作者 在馆数 ");
System.out.printf("%s %s %s %s %s%n",number,bookName,identifier,writer,quantity);
flags=1;
break;
}
}
if(flags==1)
{
break;
}
else
{
System.out.println("您输入的书号错误或者不存在,请重新输入:");
inputISBN = scanner.next();
rs = stmt.executeQuery(sql);
}
}
rs.close();
stmt.close();
}catch(Exception e){}
}
public static void stuRegister() //注册新的用户
{
try{
Class.forName(driverName);//载入驱动
dbconn = DriverManager.getConnection(dbURL,userName,userPwd);//连接数据库
String number=null, name=null, sex=null, grade=null;
System.out.println("请输入您要添加的数据");
System.out.print("学号: ");
number = scanner.next();
System.out.print("姓名: ");
name = scanner.next();
System.out.print("性别: ");
sex = scanner.next();
System.out.print("班级: ");
grade = scanner.next();
String sql = "insert into 借书人 (学号,姓名,性别,班级) values('"+ number +"','"+ name +"','"+ sex +"','"+ grade +"')";
Statement stmt = dbconn.createStatement();
stmt.executeUpdate(sql);
System.out.println("信息已插入!\n\n请重新选择您要进行的服务:");
stmt.close();
}catch(Exception e){}
}
public static void borrowBook()//办理借书手续
{
try{
Class.forName(driverName);//载入驱动
dbconn = DriverManager.getConnection(dbURL,userName,userPwd);//连接数据库
//java.util.Date d = new java.util.Date();
//SimpleDateFormat sdf = new SimpleDateFormat("yyyy 年 MM 月 dd E");
String number=null, ISBN=null, inputNum=null,inputISBN=null;
int flags1=0,flags2=0;
System.out.print("请输入您的学号: ");
inputNum = scanner.next();
// System.out.println("请输入您所借图书的ISBN号");
// ISBN = scanner.next();
String sql1 = "select * from 借书人";
Statement stmt1 = dbconn.createStatement();
ResultSet rs = stmt1.executeQuery(sql1);
String sql2 = "select * from 图书";
Statement stmt2 = dbconn.createStatement();
ResultSet rs1 = stmt2.executeQuery(sql2);
while(true)//
{
while(rs.next())//此层循环判断
{
number=rs.getString("学号");
if(inputNum.equals(number))
{//d
flags1=1;
System.out.println("请输入您所借图书的ISBN号");
inputISBN = scanner.next();
while(true)
{//c
while(rs1.next())
{//b
ISBN=rs1.getString("ISBN");
if(inputISBN.equals(ISBN))
{//a
flags2=1;
rs.close();
stmt1.close();
rs1.close();
stmt2.close();
System.out.print("请输入借书时间: ");
String time1 =scanner.next();
System.out.print("请输入还书时间: ");
String time2 =scanner.next();
String sql3 = "insert into 借阅 (学号,ISBN,借书时间,还书时间) values('"+ number +"','"+ ISBN +"','"+ time1 +"','"+ time2 +"')";
Statement stmt = dbconn.createStatement();
stmt.executeUpdate(sql3);
System.out.println("数据已更改");
stmt.close();
break;
}//a
}//b
if(flags2==1)
{
break;
}
else
{
System.out.println("您输入的ISBN号有错误或者不存在,请检查后重新输入:");
number = scanner.next();
rs = stmt1.executeQuery(sql1);
}
}//c
break;
}//d
}
if(flags1==1)
{
break;
}
else
{
System.out.println("您输入的学号有错误或者不存在,请检查后重新输入:");
number = scanner.next();
rs = stmt1.executeQuery(sql1);
}
}
}catch(Exception e){}
}
public static void scan()//浏览借书信息
{
//Class.forName(driverName);//载入驱动
// dbconn = DriverManager.getConnection(dbURL,userName,userPwd);//连接数据库
try{
Class.forName(driverName);//载入驱动
dbconn = DriverManager.getConnection(dbURL,userName,userPwd);//连接数据库
String sql = "select * from 借阅 ";
Statement stmt = dbconn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next())
{
String number = rs.getString("学号");
String ISBN = rs.getString("ISBN");
String time1 = rs.getString("借书时间");
String time2 = rs.getString("还书时间");
System.out.println("学号 ISBN 借书时间 还书时间");
System.out.printf("%s %s %s %s%n",number,ISBN,time1,time2);
System.out.println();
}
rs.close();
stmt.close();
}catch(Exception e){}
}
public