输入学号和课程号，输出成绩

通过java applet访问数据库

用的是MySql数据库，在数据库中建三张表，课程表Course，学生表Student和选课表Enrollment；

学生表：

create table student(
 ssn char(9),
 firstname varchar(25),
 mi char(1),
 lastname varchar(25),
 birthDate date,
 street varchar(25),
 phone char(11),
 zipcode char(5),
 deptId char(4),
 primary key(ssn)
 );

课程表：

create table course(
  courseId char(5),
  subjectId char(4) not null,
  courseNumber integer,
  title varchar(50) not null,
  numOfCredits integer,
  primary key(courseId)
  );

选课表：

create table Enrollment(
   ssn char(9),
   courseId char(5),
   dateRegistered date,
   grade  char(1),
   primary key(ssn,courseId),
   foreign key(ssn) references
   student(ssn),
   foreign key(courseId) references
   course(courseId)
   );

在课程表中插入数据的代码如下：

insert into course(courseId,subjectId,courseNumber,title,numOfCredits)
  values('11113','CSCI',3720,'Database System',3);

这样数据就准备好了。

FindGrade.java文件如下：

import javax.swing.*;
import java.sql.*;
import java.awt.*;
import java.awt.event.*;
public class FindGrade extends JApplet{
private JTextField jtfSSN=new JTextField(9);
private JTextField jtfCourseId=new JTextField(5);
private JButton jbtShowGrade=new JButton("Show Grade");
//private Statement stmt;
private PreparedStatement pres;
public void init(){
initializeDB();

jbtShowGrade.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent e){
jbtShowGrade_actionPerformed(e);

}
});

JPanel jPanel1=new JPanel();
jPanel1.add(new JLabel("SSN"));
jPanel1.add(jtfSSN);
jPanel1.add(new JLabel("Course ID"));
jPanel1.add(jtfCourseId);
jPanel1.add(jbtShowGrade);

add(jPanel1,BorderLayout.NORTH);
}

private void initializeDB(){
try{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver loaded");

Connection connection=DriverManager.getConnection
("jdbc:mysql://localhost/javabook","scott","tiger");
System.out.println("Datebase connected");

String queryString="select firstname,mi, "+
"lastname,title,grade from student,enrollment,course "+
"where student.ssn= ? and enrollment.courseId = ? "+
" and enrollment.courseId=course.courseId "+
" and enrollment.ssn=student.ssn";

pres=connection.prepareStatement(queryString);
}
catch(Exception ex){
ex.printStackTrace();
}
}

private void jbtShowGrade_actionPerformed(ActionEvent e){
String ssn=jtfSSN.getText();
String courseId=jtfCourseId.getText();
try{
pres.setString(1,ssn);
pres.setString(2,courseId);
/*
String queryString="select firstname,mi, "+
"lastname,title,grade from student,enrollment,course "+
"where student.ssn='"+ssn+"'and enrollment.courseId "+
"='"+courseId+
"' and enrollment.courseId=course.courseId "+
" and enrollment.ssn=student.ssn";
*/
ResultSet rset=pres.executeQuery();

if(rset.next()){
String lastname=rset.getString(1);
String mi=rset.getString(2);
String firstname=rset.getString(3);
String title=rset.getString(4);
   String grade=rset.getString(5);
   
   JOptionPane.showMessageDialog(null, firstname+" "+mi+
    " "+lastname+"'s grade on course "+ title+" is "+
    grade);
   
}
else{
JOptionPane.showMessageDialog(null, "Not found");
}
}
catch(SQLException ex){
ex.printStackTrace();
}
}
}

运行截图：