Java实验2-数据库编程
目标：掌握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，驱动使用
运行结果：
