目标：掌握Java数据库编程
内容：
学生选课系统包括如下数据库表
- 学生表：Student(Sno，Sname，Ssex，Sage，Sdept)
- 课程表：Course(Cno，Cname，Ccredit)
- 学生选课表：SC(Sno，Cno，Grade)
在课堂上，老师以学生管理功能为例用Java实现了对学生记录的增、删、改、查操作。请仿照此例，用Java实现对课程和学生选课记录的增、删、改、查操作。
程序实现代码：
Course.java;
CourseManger.java;
CourseTableCreator.java;
DatabaseManager.java;
Student.java;
StudentManager.java;
StudentTableCreator.java;
SelectCourse.java;
SelectCourseManager.java;
scTableCreator.java
代码1：Course.java
public class Course {
private int Cno;
private String Cname;
private int Ccredit;
public void setNo(int Cno){
this.Cno = Cno;
}
public void setName(String Cname){
this.Cname = Cname;
}
public void setCredit(int Ccredit){
this.Ccredit = Ccredit;
}
public int getNo(){
return Cno;
}
public String getName(){
return Cname;
}
public int getCredit(){
return Ccredit;
}
public String toString(){
return "Cno: "+Cno+"\tCname: "+Cname+"\tCcredit: "+Ccredit;
}
}
代码2：CourseManager.java
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.ArrayList; public class CourseManager {
private Connection cn ;
private Statement stmt;
private PreparedStatement prepStmt; public CourseManager(){
cn = new DatabaseManager().openConnection();
} public void addCourse(Course course){
try{
if(cn!=null){
String query = "insert into course(Cno ,Cname ,Ccredit ) values(? ,? ,? )";
prepStmt = cn.prepareStatement(query);
prepStmt.setInt(1 ,course.getNo());
prepStmt.setString(2 ,course.getName());
prepStmt.setInt(3 ,course.getCredit());
prepStmt.executeUpdate();
}
} catch (Exception e){
System.out.println("create CourseManager error:"+e);
}
} public void deleteCourse(Course course){
try{
if(cn!=null){
String query = "delete form course where Cno =?";
prepStmt = cn.prepareStatement(query);
prepStmt.setInt(1 ,course.getNo());
prepStmt.executeUpdate();
}
} catch (Exception e){
System.out.println("delet course error:"+e);
}
} public void updateCourse(Course course){
try{
if(cn!= null){
String query = "update course set Cno=?,Cname=?,Ccredit=? ";
prepStmt = cn.prepareStatement(query);
prepStmt.setInt(1 ,course.getNo());
prepStmt.setString(2 ,course.getName());
prepStmt.setInt(3 ,course.getCredit());
prepStmt.executeUpdate();
}
}catch (Exception e){
System.out.println("update course error:"+e);
}
} public ArrayList<Course> queryCourse(){
ArrayList<Course> courses = new ArrayList<Course>();
Course course;
try{
if(cn!=null){
stmt = cn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM course");
while(rs.next()){
course = new Course();
course.setNo(rs.getInt("Cno"));
course.setName(rs.getString("Cname"));
course.setCredit(rs.getInt("Ccredit"));
courses.add(course);
}
}
} catch (Exception e){
System.out.println("query course error:"+e);
}
return courses;
}
public void closeConnection(){
try{
stmt.close();
prepStmt.close();
cn.close();
} catch (Exception e){
System.out.println("close connection error:"+e);
}
}
}
代码3：CourseTableCreator.java
import java.sql.*;
public class CourseTableCreator {
private Connection cn;
private Statement stmt;
private PreparedStatement prepStmt;
public CourseTableCreator() {
cn = new DatabaseManager().openConnection();
}
public void dropTable() {
try {
if (cn != null) {
stmt = cn.createStatement();
String qurey = "drop table course";
stmt.execute(qurey);
System.out.println("drop table course");
}
} catch (Exception e) {
System.out.println("drop table course error:" + e);
}
}
public void createTable() {
try {
if (cn != null) {
String qurey = "create table course(Cno int PRIMARY KEY,Cname varchar(20),Ccredit int)";
stmt = cn.createStatement();
stmt.execute(qurey);
System.out.println("create table course");
}
} catch (Exception e) {
System.out.println("create table course error: " + e);
}
}
public void insertIntRecords() {
try {
if (cn != null) {
String query = "insert into course(Cno ,Cname ,Ccredit) values(?, ?, ?)";
prepStmt = cn.prepareStatement(query);
prepStmt.setInt(1, 1001);
prepStmt.setString(2, "高等数学");
prepStmt.setInt(3, 6);
prepStmt.executeUpdate();
prepStmt.setInt(1, 1002);
prepStmt.setString(2, "java程序设计");
prepStmt.setInt(3, 3);
prepStmt.executeUpdate();
prepStmt.setInt(1, 1003);
prepStmt.setString(2, "通信原理");
prepStmt.setInt(3, 5);
prepStmt.executeUpdate();
prepStmt.setInt(1, 1004);
prepStmt.setString(2, "DSP 原理及其应用");
prepStmt.setInt(3, 3);
prepStmt.executeUpdate();
System.out.println("4 course records has been inserted");
}
} catch (Exception e) {
System.out.println("create table course error:" + e);
}
}
public void closeConnection() {
try {
stmt.close();
prepStmt.close();
cn.close();
} catch (Exception e) {
System.out.println("table course close conncetion error:" + e);
}
}
public static void main(String[] args) {
// TODO Auto-generated method stub
CourseTableCreator creator = new CourseTableCreator();
creator.dropTable();
creator.createTable();
creator.insertIntRecords();
creator.closeConnection();
}
}
代码4：DatabaseManager.java
import java.sql.DriverManager;
import java.sql.Connection; public class DatabaseManager {
private Connection cn;
public Connection openConnection() {
try{
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/javaCourse";
cn=DriverManager.getConnection(url, "root", "manager"); }catch(Exception e){
System.out.println("open databse connection error:"+e);
} return cn;
}
public void closeConnection(){
try{
cn.close();
}catch (Exception e){
System.out.println("close databse connection error:"+e);
}
} }
SelectCourse.java的源代码如下：
public class SelectCourse {
private int Sno;
private int Cno;
private int Grade; public void setStudentNo(int Sno){
this.Sno = Sno;
}
public int getStudentNo(){
return Sno;
}
public void setCourseNo(int Cno){
this.Cno = Cno;
}
public int getCourseNo(){
return Cno;
}
public void setGrade(int Grade){
this.Grade = Grade;
}
public int getGrade(){
return Grade;
}
}
代码5：SelectCourseManager.java
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.ArrayList; public class SelectCourseManager {
private Connection cn;
private Statement stmt;
private PreparedStatement prepStmt; public SelectCourseManager(){
cn = new DatabaseManager().openConnection();
} public void addRecord(SelectCourse sc){
try{
if(cn!= null){
String query = "insert into selectCourse(Sno ,Cno ,Grade) values(? ,? ,?)";
prepStmt = cn.prepareStatement(query);
prepStmt.setInt(1 , sc.getStudentNo());
prepStmt.setInt(2 , sc.getCourseNo());
prepStmt.setInt(3 , sc.getGrade());
prepStmt.executeUpdate();
}
}catch (Exception e){
System.out.println("add one record to selectCoursee error:"+e);
}
}
public void deleteRecord(SelectCourse sc){
try{
if(cn!= null){
String qurey = "delete from selectCourse where Sno=?";
prepStmt = cn.prepareStatement(qurey);
prepStmt.setInt(1 , sc.getStudentNo());
prepStmt.executeUpdate();
}
}catch (Exception e){
System.out.println("delete one record from selectCourse error:"+e);
}
} public void updateRecord(SelectCourse sc){
try{
if(cn!=null){
String qurey = "update selectCourse Cno=?,Grade=? where Sno=?";
prepStmt = cn.prepareStatement(qurey);
prepStmt.setInt(1 , sc.getCourseNo());
prepStmt.setInt(2 , sc.getGrade());
prepStmt.setInt(3 , sc.getStudentNo());
prepStmt.executeUpdate();
}
}catch(Exception e){
System.out.println("update record form selectCourse error: "+e);
}
}
public ArrayList <SelectCourse> queryRecord(){
ArrayList <SelectCourse> scs = new ArrayList<SelectCourse>();
SelectCourse sc;
try {
if(cn!=null){
stmt = cn.createStatement();
ResultSet rs= stmt.executeQuery("SELECT * FROM selectCourse");
while(rs.next()){
sc = new SelectCourse();
sc.setStudentNo(rs.getInt("Sno"));
sc.setCourseNo(rs.getInt("Cno"));
sc.setGrade(rs.getInt("Grade"));
scs.add(sc);
}
}
}catch(Exception e){
System.out.println("query selectCourse error: "+e);
}
return scs;
}
public void closeConnection(){
try{
stmt.close();
prepStmt.close();
cn.close();
}catch (Exception e){
System.out.println("close connection of selectCourse error:"+e);
}
}
}
代码6：scTableCreator.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement; public class scTableCreator {
private Connection cn;
private Statement stmt;
private PreparedStatement prepStmt; public scTableCreator(){
cn = new DatabaseManager().openConnection();
} public void dropTalbe(){
try{
if(cn!=null){;
stmt = cn.createStatement();
stmt.execute("drop table selectCourse");
System.out.println("drop table selectCourse!");
}
}catch (Exception e){
System.out.println("drop table selectCourse error:"+e);
}
}
public void createTable(){
try{
if(cn!=null){
stmt = cn.createStatement();
stmt.execute("create table selectCourse(Sno int ,Cno int ,Grade int,foreign key(Sno) references Student(Sno),foreign key(Cno) references Course(Cno))");
System.out.println("create table selectCourse");
}
}catch (Exception e){
System.out.println("create table selectCourse error:"+e);
}
} public void insertInitRecords() {
try {
if (cn != null) {
String query = "insert into selectCourse(Sno ,Cno ,Grade ) values(? ,? ,?)";
prepStmt = cn.prepareStatement(query);
prepStmt.setInt(1, 20092725);
prepStmt.setInt(2, 1001);
prepStmt.setInt(3, 90);
prepStmt.executeUpdate(); prepStmt.setInt(1, 20092725);
prepStmt.setInt(2, 1002);
prepStmt.setInt(3, 96);
prepStmt.executeUpdate(); prepStmt.setInt(1, 20092740);
prepStmt.setInt(2, 1004);
prepStmt.setInt(3, 100);
prepStmt.executeUpdate(); System.out.println("some records added to selectCourse"); }
} catch (Exception e) {
System.out
.println("inset inital records to table selectCourse error:"
+ e);
}
}
public void closeConneciton(){
try{
stmt.close();
prepStmt.close();
cn.close();
} catch (Exception e){
System.out.println("close connection of selectCourse error:"+e);
}
} /**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
scTableCreator creator = new scTableCreator();
creator.dropTalbe();
creator.createTable();
creator.insertInitRecords();
creator.closeConneciton();
} }
代码7：Student.java
public class Student {
//fileds of student
private int Sno;
private String Sname;
private String Ssex;
private int Sage;
private String Sdept;
//Methods of student
public int getNo(){
return Sno;
}
public String getName(){
return Sname;
}
public String getSex(){
return Ssex;
}
public int getAge(){
return Sage;
}
public String getDept(){
return Sdept;
}
public void setNo(int Sno){
this.Sno = Sno;
}
public void setName(String Sname){
this.Sname = Sname;
}
public void setSex(String Ssex){
this.Ssex = Ssex;
}
public void setAge(int Sage){
this.Sage = Sage;
}
public void setDept(String Sdept){
this.Sdept = Sdept;
}
public String toString(){
return Sno+" "+Sname+" "+Ssex+" "+Sage+" "+Sdept;
}
}
代码8：StudentManager.java
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.ArrayList; public class StudentManager {
private Connection cn;
private Statement stmt;
private PreparedStatement prepStmt; public StudentManager(){
cn = new DatabaseManager().openConnection();
} public void addStudent(Student student){
try{
if(cn!=null){
String query = "insert into student(Sno ,Sname ,Ssex ,Sage ,Sdept) values(? ,? ,? ,? ,? )";
prepStmt = cn.prepareStatement(query); //create a statement here
prepStmt.setInt(1 ,student.getNo());
prepStmt.setString(2 ,student.getName());
prepStmt.setString(3 ,student.getSex());
prepStmt.setInt(4 ,student.getAge());
prepStmt.setString(5 ,student.getDept());
prepStmt.executeUpdate();
}
} catch (Exception e){
System.out.println("add one student error!");
e.printStackTrace();
} } public void deleteStudent(Student student){
try{
if(cn!=null){
String query = "delete from student where Sno = ?";
prepStmt = cn.prepareStatement(query);
prepStmt.setInt(1 ,student.getNo());
prepStmt.executeUpdate();
}
}catch (Exception e){
System.out.print("delete student error"+e);
}
} public void updateStudent(Student student){
try{
if(cn!= null){
String query = "update student set Sname=?, Ssex=? ,Ssex=?,Sdept = ?"+"where Sno=?";
prepStmt = cn.prepareStatement(query);
prepStmt.setString(1 ,student.getName());
prepStmt.setString(2 ,student.getSex());
prepStmt.setInt(3 ,student.getAge());
prepStmt.setString(4 ,student.getDept());
prepStmt.setInt(5 ,student.getNo());
prepStmt.executeUpdate();
}
} catch (Exception e){
System.out.println("update student error:"+e);
}
} public ArrayList<Student> queryStudent(){
ArrayList<Student> students = new ArrayList<Student>();
Student student; try{
if(cn!=null){
stmt = cn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM student") ;
while(rs.next()){
student = new Student();
student.setNo(rs.getInt("Sno"));
student.setName(rs.getString("Sname"));
student.setSex(rs.getString("Ssex"));
student.setAge(rs.getInt("Sage"));
student.setDept(rs.getString("Sdept"));
students.add(student);
}
}
}catch (Exception e){
System.out.println("query student error:"+e);
}
return students;
} public void closeConnection(){
try{
prepStmt.close();
stmt.close();
cn.close();
} catch (Exception e) {
System.out.println("close connection error:"+e);
}
}
}
代码9：StudentTableCreator.java
import java.util.ArrayList; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement; public class StudentTableCreator {
private Connection cn;
private Statement stmt;
private PreparedStatement prepStmt; public StudentTableCreator(){
cn = new DatabaseManager().openConnection();
} public void dropTable(){
try{
if(cn!=null){
stmt = cn.createStatement();
stmt.execute("drop table student");
System.out.println("drop table student");
}
}catch (Exception e){
System.out.println("drop table student error:"+e);
}
}
public void createTable(){
try{
if(cn!=null){
stmt = cn.createStatement();
stmt.execute("create table student(Sno int PRIMARY KEY,Sname varchar(18),Ssex varchar(4),Sage tinyint,Sdept varchar(20))");
System.out.println("table student created!");
}
}catch (Exception e){
System.out.println("create table student error:"+e);
}
} public void inserInitRecords(){
try{
if(cn!=null){
String query = "insert into student(Sno,Sname,Ssex,Sage,Sdept) values(?,?,?,?,?)";
prepStmt = cn.prepareStatement(query);
prepStmt.setInt(1 ,20092725);
prepStmt.setString(2 ,"张三");
prepStmt.setString(3 ,"男");
prepStmt.setInt(4 , 22);
prepStmt.setString(5 ,"信息学院");
prepStmt.executeUpdate(); prepStmt.setInt(1 ,20092740);
prepStmt.setString(2 ,"李四");
prepStmt.setString(3 ,"男");
prepStmt.setInt(4 ,23);
prepStmt.setString(5 ,"信息学院");
prepStmt.executeUpdate(); prepStmt.setInt(1 ,20092899);
prepStmt.setString(2 ,"王五");
prepStmt.setString(3, "男");
prepStmt.setInt(4 , 22);
prepStmt.setString(5 ,"材冶学院");
prepStmt.executeUpdate(); prepStmt.setInt(1 ,20093506);
prepStmt.setString(2 ,"赵六");
prepStmt.setString(3, "女");
prepStmt.setInt(4 ,21);
prepStmt.setString(5 , "工管学院");
prepStmt.executeUpdate(); prepStmt.setInt(1 , 20101105);
prepStmt.setString(2 , "刘七");
prepStmt.setString(3 , "男");
prepStmt.setInt(4 ,20);
prepStmt.setString(5 ,"软件学院");
prepStmt.executeUpdate(); System.out.println("Insert 5 records");
}
}catch (Exception e){
System.out.println("insert records error :"+e);
}
}
public void closeConnection(){
try{
prepStmt.close();
stmt.close();
cn.close();
System.out.println("student table creator connection !");
}catch (Exception e){
System.out.println(" student table close connection error:"+e);
}
}
public static void main(String[] args){
ArrayList<Student> s = new ArrayList<Student>();
StudentTableCreator creator = new StudentTableCreator();
creator.dropTable();
creator.createTable();
creator.inserInitRecords();
StudentManager sm = new StudentManager();
s = sm.queryStudent();
System.out.print(s);
creator.closeConnection();
}
}
数据库使用：mysql，驱动使用
运行结果：