通过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();
}
}
}
运行截图: