java学生选课系统

//选课规则：1、每个学生可以选多门课程，多次选课的总学分不能超过6学分；2、不能重复选择一门课程；3、每一门课程的选课人数都有数量限制，当某门课程的选课名额满时，则应另选课程。4、凭用户名和密码登录，通过提交某一课程号来选课

//总体设计方案：建立三个类：登录类register，选课类studentChooseCourse,数据库工具类JDBCUtil；一个SQL脚本文件用于生成数据库表结构和初始记录，以搭建数据库环境。

登录类register类，负责对用户的身份进行验证；工具类JDBCUtil用于实现连接，你可调用JDBCUtil的getConnection()方法等到链接。

选课类studentChooseCourse用于，实现选课功能。其中包括几个主要方法：

1、actionPerformed(ActionEvent) 用于监听用户“查询”和“提交”操作，并负责调用各种方法对其进行处理

2、createSearchCourse()用于产生图形用户界面

3、processBeforeCommit()用于对用户的“提交”查找进行验证，剔除无效的用户操作

4、tryCommit()负责对有效的“提交”操作，进一步处理，并将有效的操作结果时时

保存到数据库，并更新数据库原有信息

//本程序用到的知识点：数据库连接JDBC；SQL建表、插入输入、动态查询；图形用户界面的产生以及处理查询结果集并较好显示；程序设计基础知识。

//代码如下：

import java.awt.GridLayout;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.sql.*;

import javax.swing.*;

import javax.swing.table.DefaultTableModel;

import school.schoolPicture.JdbcUtil;

public class StudentChooseCourse extends JFrame implements ActionListener {

JTextField jtfSearch = new JTextField(11);

String[] columnNames = new String[] { \"课程ID\课程名\学时\学分\学期\性质\" };

DefaultTableModel dtmSearch = new DefaultTableModel(columnNames, 27);

JTable jtSearch = new JTable(dtmSearch);

JScrollPane jspSearch = new JScrollPane(jtSearch);

JComboBox jcbSearch = new JComboBox();

JTextField jtfSelectedCourse = new JTextField(10);

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

private static String usrName;

private static String passwrd;

public StudentChooseCourse(String usrName, String passwrd) {

createSearchCourse();

this.usrName = usrName;

this.passwrd = passwrd;

}

public String getUsrName() {

return usrName;

}

public void setUsrName(String usrName) {

this.usrName = usrName;

}

public String getPasswrd() {

return passwrd;

}

public void setPasswrd(String passwrd) {

this.passwrd = passwrd;

}

public void actionPerformed(ActionEvent e) {

String str = e.getActionCommand();

if (\"查询\".trim().equals(str)) {

int k = 1;

while (k < 10) {

for (int i = 1; i < 7; i++) {

jtSearch.setValueAt(null, k - 1, i - 1);

}

k++;

}

searchAvailableCourse();

} else if (\"提交\".equals(str)) {

boolean effect=processBeforeCommit();

if(effect==true){tryCommit();}

}

}

public boolean processBeforeCommit(){

int k = 1;

while (k < 10) {

for (int i = 1; i < 7; i++) {

jtSearch.setValueAt(null, k - 1, i - 1);

}

k++;

}

String userInput = jtfSelectedCourse.getText().toString().trim()

.toLowerCase();

String sql = \"select cno from coursexx where cno=? \";

boolean flagCourseExist = false;

try {

ps = con.prepareStatement(sql);

ps.setString(1, userInput);

rs = ps.executeQuery();

flagCourseExist = rs.next();

} catch (Exception eC) {

eC.printStackTrace();

}

if (!flagCourseExist) {

JOptionPane.showMessageDialog(null, \"该课程不存在，请重新输入\");

return false;

}

PreparedStatement ps = null;

sql = \"select sum(grade) \"

+ \"from (select x.sname , x.cno,k.grade grade \"

+ \"from coursexx k join choicesxx x \"

+ \"on k.cno=x.cno and x.sname=?) result\";

String grade =null;

try {

ps = con.prepareStatement(sql);

ps.setString(1, usrName);

rs = ps.executeQuery();

while (rs.next()) {

grade = rs.getString(1);

if(grade==null){grade=\"0\";}

}

} catch (Exception rrr) {

rrr.printStackTrace();

}

System.out.println(\"总学分：\" + grade);

if (Integer.parseInt(grade) > 6) {

JOptionPane.showMessageDialog(null, \"你已经选满6学分，系统将退出\");

this.setVisible(false);

return false;

}

sql = \"select * from choicesxx where sname=? and cno=?\";

boolean flag = false;

try {

ps = con.prepareStatement(sql);

ps.setString(1, this.getUsrName());

ps.setString(2, userInput);

rs = ps.executeQuery();

flag = rs.next();

} catch (Exception eaa) {

eaa.printStackTrace();

}

if (flag) {

JOptionPane.showMessageDialog(null, \"你已经选择了该课程。请另选课程\");

return false;}

return true;

}

public void tryCommit() {

String userInput = jtfSelectedCourse.getText().toString().trim()

.toLowerCase();

PreparedStatement ps;

String sql = \"select (Max-selectedCount) as RemainedCount \"

+ \"from Coursexx where cno=?\";

try {

ps = con.prepareStatement(sql);

ps.setString(1, userInput);

rs = ps.executeQuery();

ResultSetMetaData meta = rs.getMetaData();

int cols = meta.getColumnCount();

int RemainedCount = -1;

while (rs.next()) {

RemainedCount = rs.getInt(1);

System.out.println(\"RemainedCount:\" + RemainedCount);

}

if (RemainedCount > 0) {

sql = \"insert into choicesxx values(?,?)\";

ps = con.prepareStatement(sql);

ps.setString(1, this.getUsrName());

ps.setString(2, userInput);

ps.executeUpdate();

JOptionPane.showMessageDialog(null, \"选课成功： \" + this.getUsrName()

+ \" 选了\" + userInput + \".\" + \"\" + \" 还有 \" + RemainedCount

+ \" 人可以选该课程。\");

sql = \"update CourseXX set selectedCount=selectedCount+1 where cno=?\";

ps = con.prepareStatement(sql);

ps.setString(1, userInput);

ps.executeUpdate();

con.commit();

}

} catch (Exception es) {

es.printStackTrace();

try {

con.rollback();

} catch (Exception ey) {

ey.printStackTrace();

}

}

}

public void searchAvailableCourse() {

if (JDBCUtil.getConnection() != null) {

System.out.println(JDBCUtil.getConnection());

con = JDBCUtil.getConnection();

} else {

con = JdbcUtil.getConnection();

}

String userInput = jtfSearch.getText().toString().trim().toLowerCase();

String selectedItem = jcbSearch.getSelectedItem().toString().trim();

System.out.println(\"User search:\" + userInput);

System.out.println(\"selectedItem:\" + selectedItem);

String sql = null;

try {

if (\"课程名\".equals(selectedItem)) {

sql = \"select cno,cname,hour,grade,term,isNeed from CourseXX where cname = ?\";

ps = con.prepareStatement(sql);

ps.setString(1, userInput);

} else if (\"课程ID\".equals(selectedItem)) {

sql = \"select cno,cname,hour,grade,term,isNeed from CourseXX where cno = ?\";

ps = con.prepareStatement(sql);

ps.setString(1, userInput);

} else if (\"学时\".equals(selectedItem)) {

sql = \"select cno,cname,hour,grade,term,isNeed from CourseXX where hour = ?\";

ps = con.prepareStatement(sql);

ps.setInt(1, Integer.parseInt(userInput));

} else if (\"学分\".equals(selectedItem)) {

sql = \"select cno,cname,hour,grade,term,isNeed from CourseXX where grade = ?\";

ps = con.prepareStatement(sql);

ps.setInt(1, Integer.parseInt(userInput));

} else if (\"学期\".equals(selectedItem)) {

sql = \"select cno,cname,hour,grade,term,isNeed from CourseXX where term = ?\";

ps = con.prepareStatement(sql);

ps.setString(1, userInput);

}

System.out.println(sql);

rs = ps.executeQuery();

con.commit();

ResultSetMetaData meta = rs.getMetaData();

int cols = meta.getColumnCount();

String result = null;

int k = 1;

boolean flag = false;

while (rs.next()) {

for (int i = 1; i <= cols; i++) {

result = rs.getString(i);

System.out.println(result);

jtSearch.setValueAt(result, k - 1, i - 1);

}

k++;

flag = true;

}

if (flag == false) {

JOptionPane.showMessageDialog(null, \"该课程不存在，请重新输入\");

return;

}} catch (Exception ex) {

ex.printStackTrace();

try {

con.rollback();

} catch (Exception er) {

er.printStackTrace();

}

}

public void createSearchCourse() {

this.setLayout(new GridLayout(3, 1));

JPanel jp1 = new JPanel();

jp1.setLayout(new GridLayout(4, 1));

JPanel jp2 = new JPanel();

JPanel jp3 = new JPanel();

JPanel jp10 = new JPanel();

JPanel jp11 = new JPanel();

JPanel jp12 = new JPanel();

JPanel jp13 = new JPanel();

JLabel jlSearch = new JLabel(\" 学 生 选 课 系 统 \");

jp11.add(jlSearch);

jcbSearch.addItem(new String(\"课程名\"));

jcbSearch.addItem(new String(\"课程ID\"));

jcbSearch.addItem(new String(\"学时\"));

jcbSearch.addItem(new String(\"学分\"));

jcbSearch.addItem(new String(\"学期\"));

jp12.add(jtfSearch);

jp12.add(jcbSearch);

JButton jbOK = new JButton(\"查询\");

jbOK.addActionListener(this);

jbOK.setSize(90, 20);

jp13.add(jbOK);

jp1.add(jp10);

jp1.add(jp11);

jp1.add(jp12);

jp1.add(jp13);

jp2.add(jspSearch);

JLabel jlSelectedCourse = new JLabel(\"请输入课程ID：\");

JButton jbSelectedCourse = new JButton(\"提交\");

jbSelectedCourse.addActionListener(this);

jp3.add(jlSelectedCourse);

jp3.add(jtfSelectedCourse);

jp3.add(jbSelectedCourse);

this.add(jp1);

this.add(jp2);

this.add(jp3);

this.setVisible(true);

this.setSize(485, 600);

}

public static void main(String[] args) {

//String usrName = \"xuliang\";

//String passwrd = \"123\";

new StudentChooseCourse(usrName, passwrd);

}

}

drop table registerXu;

create table registerXu (id varchar(20),userName varchar(20),identify varchar(20));

varchar(20),passWord insert into registerXu values('s001','xuliang','123','学生');

insert into registerXu values('s002','xuliang2','1234','学生');

insert into registerXu values('j001','jack','12345','学生');

insert into registerXu values('001','user','user','学生');

insert into registerXu values('z001','zlm','corejava','老师');

String sql = \"select * from registerXu \"

+ \"where userName=? and passWord=? and identify=?\";

//table 2:Coursexx课程表

drop table Coursexx;

create table Coursexx(cno varchar(20) primary key,cname varchar(20),hour Int,

grade Int,term varchar(20),isNeed varchar(20), selectedCount Int ,Max Int);

insert into Coursexx

values('c001','CoreJava',50,5,'201007','NoNecessary',0,50);

insert into Coursexx values('c002','XML',20,2,'201008','NoNecessary',0,40);

insert into Coursexx

values('c003','HIBERNATE',20,4,'201008','NoNecessary',0,30);

insert into Coursexx values('c004','SQL',20,4,'201006','NoNecessary',0,5);

insert into Coursexx values('c005','JDBC',20,2,'201008','NoNecessary',0,3);

insert into Coursexx values('c006','AJAX',20,2,'201010','NoNecessary',0,1);

insert into Coursexx values('c007','JSP',100,8,'201009','NoNecessary',0,1

sql = \"select cno,cname,hour,grade,term,isNeed from CourseXX where cname = ?\";

//table 3:学生表--

drop table studentxx;

create table studentxx(sid varchar(20),sname varchar(20) primary key,sex varchar(20),birthday varchar(20),

className varchar(20), image varchar(20));

insert into studentxx values('s001','xuliang','male','19901124','sd1003','good');

insert into studentxx

values('s002','xuliang2','male','19851124','sd0910','good');

insert into studentxx values('j001','jack','male','19881124','sd1003','good');

insert into studentxx values('001','user','male','19901124','sd1005','good');

insert into studentxx values('s003','sisi','female','19880124','sd1007','good');

insert into studentxx

values('as003','crystal','female','19880124','asd1007','good');

drop table choicesxx;

create table choicesxx(

sname varchar(20) references studentxx(sname),cno varchar(20) references coursexx(cno));

package school;

import javax.swing.*;

import school.schoolPicture.JdbcUtil;

import java.sql.*;

import java.awt.*;

import java.awt.event.*;

public class Register implements ActionListener {

JFrame jf = new JFrame(\"学生成绩管理与选课系统\");

JTextField jtfUserName = new JTextField(10);

JPasswordField jpfPassWord = new JPasswordField(10);

JComboBox identify = new JComboBox();

public Register() {

CreateRegisterGUI();

}

public void actionPerformed(ActionEvent e) {

String str = e.getActionCommand();

if (str.equalsIgnoreCase(\"登录\")) {

processLogin();

} else if (str.equalsIgnoreCase(\"取消\")) {

jtfUserName.setText(\"\");

jpfPassWord.setText(\"\");

} else if (str.equalsIgnoreCase(\"注册\")) {

new CreateLogin();

}

}

public void processLogin() {

Connection con = null;

if (JdbcUtil.class == null) {

con = JdbcUtil.getConnection();

} else {

con = JDBCUtil.getConnection();

}

String usrName = jtfUserName.getText().trim();

String passwrd = new String(jpfPassWord.getPassword()).trim();

String ident = identify.getSelectedItem().toString().trim();

String sql = \"select * from registerXu \"

+ \"where userName=? and passWord=? and identify=?\";

System.out.println(usrName + \":\" + passwrd + \":\" + ident);

try {

PreparedStatement ps = con.prepareStatement(sql);

ps.setString(1, usrName);

ps.setString(2, passwrd);

ps.setString(3, ident);

ResultSet rs = ps.executeQuery();

StringBuffer sb = new StringBuffer(\"\");

ResultSetMetaData meta = rs.getMetaData();

int cols = meta.getColumnCount();

records in database:

while (rs.next()) {

for (int i = 1; i < cols; i++) {

sb.append(meta.getColumnName(i));

sb.append(rs.getString(i));

}

}if (sb.length() < 1) {

JOptionPane.showMessageDialog(null, \"用户名或密码错误\");

} else if (sb.length() >= 1) {

if (ident.equals(\"student\")) {

new StudentChooseCourse(usrName, passwrd);

jf.setVisible(false);

} else if (ident.equals(\"teacher\")) {

new TeacherEntered();

jf.setVisible(false);

} else if (ident.equals(\"admin\")) {

}

}

} catch (Exception er) {

er.printStackTrace();

}

}

public void CreateRegisterGUI() {jf.setLayout(new GridLayout(5, 1));

JPanel jp1 = new JPanel();

JLabel jl1 = new JLabel(\"学生成绩管理系统\");

jp1.add(jl1);

jf.add(jp1);

JPanel jp2 = new JPanel();

JLabel jl2 = new JLabel(\"用户名:\");

jp2.add(jl2);

jp2.add(jtfUserName);

jf.add(jp2);

JPanel jp3 = new JPanel();

JLabel passWord = new JLabel(\"密 码:\");

jp3.add(passWord);

jp3.add(jpfPassWord);

jf.add(jp3);

JPanel jp4 = new JPanel();

JLabel jl4 = new JLabel(\"身 份:\");

// identify.addItem(new String(\"学生 \"));

identify.addItem(new String(\"student \"));

// identify.addItem(new String(\"老师 \"));

identify.addItem(new String(\"teacher \"));

// identify.addItem(new String(\"管理员 \"));

identify.addItem(new String(\"admin \"));

identify.addActionListener(this);

jp4.add(jl4);

jp4.add(identify);

jf.add(jp4);

JPanel jp5 = new JPanel();

JButton enter = new JButton(\"登录\");

enter.addActionListener(this);

JButton cancel = new JButton(\"取消\");

cancel.addActionListener(this);

JButton regist = new JButton(\"注册\");

regist.addActionListener(this);

jp5.add(enter);

jp5.add(cancel);

jp5.add(regist);

jf.add(jp5);

jf.setSize(400, 250);

jf.setVisible(true);

jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

}

public static void main(String[] args) {

new Register();

}

}

import java.sql.*;

public class JDBCUtil {

public JDBCUtil() {

}

PreparedStatement ps = null;

ResultSet rs = null;

static {

String driverName = \"com.microsoft.sqlserver.jdbc.SQLServerDriver\";

try {

Class.forName(driverName);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public static Connection getConnection() {

Connection connection = null;

String url = \"jdbc:sqlserver://localhost:1433; DatabaseName=master\";

String user = \"sa\";

String password = \"sd100301\";

// String password=\"Foolish004\";

try {

connection = DriverManager.getConnection(url, user, password);

connection.setAutoCommit(false);

System.out.println(connection);

} catch (SQLException e) {

e.printStackTrace();

}

return connection;

}

public static void main(String[] args) {

new JDBCUtil().getConnection();

}

}