第二次冲刺

本次作业是由我和曹敏辉同学共同完成，我们俩主要代码的编写，以下是我们的代码编写：

package project1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Conn {
static Connection conn=null;

public static void ConnectSQL() {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;DatabaseName=Student";
conn=DriverManager.getConnection(url,"sa","123456");
}catch(SQLException e) {
e.printStackTrace();
}catch(ClassNotFoundException e) {
e.printStackTrace();
}
}
}

 

package project1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Course {
Statement stmt=null;
ResultSet rs=null;
String stuNo=null;

public void selectCourse() {
Conn.ConnectSQL();
Scanner in=new Scanner(System.in);
System.out.print("请输入要查找的学号：");
stuNo=in.nextLine();
try {
stmt=Conn.conn.createStatement();
String sql="select stuName,Cname,Caddress,Ctime from student s,course c,SC where \r\n" +
" s.stuNo=SC.stuNo and c.Cno=SC.Cno and s.stuNo='"+stuNo+"'";
rs=stmt.executeQuery(sql);
System.out.print("姓名\t课程名\t上课地点\t上课时间\n");
while(rs.next()) {
System.out.print(rs.getString(1)+"\t");
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getString(3)+"\t");
System.out.print(rs.getString(4)+"\t");
System.out.println();
}
rs.close();
stmt.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}

 

package project1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Grade {
Statement stmt=null;
ResultSet rs=null;
String stuNo=null;

public void selectGrade() {
Conn.ConnectSQL();
Scanner in=new Scanner(System.in);
System.out.print("请输入要查找的学号：");
stuNo=in.nextLine();
try {
stmt=Conn.conn.createStatement();
String sql="select stuName,Cname,Grade from student s,course c,SC where \r\n" +
" s.stuNo=SC.stuNo and c.Cno=SC.Cno and s.stuNo='"+stuNo+"'";
rs=stmt.executeQuery(sql);
System.out.print("姓名\t课程名\t成绩\n");
while(rs.next()) {
System.out.print(rs.getString(1)+"\t");
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getString(3)+"\t");
System.out.println();
}
rs.close();
stmt.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}

 

package project1;
import java.util.Scanner;

public class Menu {
public int menu() {
System.out.println("\n\t\t生活在长大");
System.out.println("\n");
System.out.println("\t******************************");
System.out.println("\t 1.校园卡余额查询 2.校园卡充值");
System.out.println("\t 3.课表查询 4.成绩查询");
System.out.println("\t 0.退出");
System.out.println("\t*******************************");
System.out.print("请选择操作编号：");
Scanner in=new Scanner(System.in);
int choice=in.nextInt();
while(choice<0||choice>4) {
System.out.print("输入无效，请重新输入：");
choice=in.nextInt();
}
return choice;
}

public void selectOperat() {
int choice;
choice=menu();
schoolCard sc=new schoolCard();
Grade g=new Grade();
Course c=new Course();
switch(choice) {
case 1:{
sc.selectCard();
break;
}
case 2:{
sc.addSalary();
break;
}
case 3:{
c.selectCourse();
break;
}
case 4:{
g.selectGrade();
break;
}
case 0:{
System.out.println("感谢使用！");
System.exit(0);
}
}
System.out.println();
System.out.print("按任意键继续：");
Scanner in=new Scanner(System.in);
in.nextLine();
selectOperat();
}
}

 

package project1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Scanner;
import java.sql.*;

public class schoolCard {
Statement stmt=null;
ResultSet rs=null;
String stuNo=null;

public void selectCard() {
Conn.ConnectSQL();
Scanner in=new Scanner(System.in);
System.out.print("请输入要查找的学号：");
stuNo=in.nextLine();
try {
stmt=Conn.conn.createStatement();
String sql="select * from student where stuNo='"+stuNo+"'";
rs=stmt.executeQuery(sql);
System.out.print("姓名\t余额\n");
while(rs.next()) {
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getString(3)+"\t");
}
rs.close();
stmt.close();
}catch(SQLException e) {
e.printStackTrace();
}
}

public void addSalary() {
Conn.ConnectSQL();
Scanner in=new Scanner(System.in);
System.out.print("请输入要查找的学号：");
stuNo=in.nextLine();
try {
stmt=Conn.conn.createStatement();
String sql="select * from student where stuNo='"+stuNo+"'";
rs=stmt.executeQuery(sql);
System.out.print("姓名\t余额\n");
double salary=0;
while(rs.next()) {
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getString(3)+"\t");
salary=rs.getDouble(3);
}
System.out.println();
Scanner inStr=new Scanner(System.in);
System.out.print("输入要充值的金额：");
double s=inStr.nextDouble();
salary=salary+s;
String sql1="update student set cardsalary='"+salary+"' where stuNo='"+stuNo+"'";
int count=stmt.executeUpdate(sql1);
System.out.println("----成功充值----");
rs=stmt.executeQuery(sql);
System.out.print("姓名\t余额\n");
while(rs.next()) {
System.out.print(rs.getString(2)+"\t");
System.out.print(rs.getString(3)+"\t");
}
rs.close();
stmt.close();
}catch(SQLException e) {
e.printStackTrace();
}

}
}

 

package project1;

public class Test {
public static void main(String[] args) {

Menu m=new Menu();
m.selectOperat();
}
}