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,驱动使用
运行结果:

Java实验2-数据库编程

Java实验2-数据库编程

Java实验2-数据库编程

Java实验2-数据库编程

Java实验2-数据库编程

Java实验2-数据库编程

上一篇:Centos7 防火墙常用配置及说明


下一篇:python numpy sum函数用法