databaseconnection.java

这是一个可以在手机客户端运行的选课系统 这个是客户端

/*
 * 创建日期 2005-3-11
 *
 * 更改所生成文件模板为
 * 窗口 > 首选项 > Java > 代码生成 > 代码和注释
 */
package jdbc;
import java.sql.*;
import java.io.*;
import java.util.*;
import exception.*;
import constants.Constant;

/**
 * @author Owner
 *
 * 更改所生成类型注释的模板为
 * 窗口 > 首选项 > Java > 代码生成 > 代码和注释
 */
public class DatabaseConnection 
{	
	private static Statement statement=null;
	private static StudentTable studentTable=null;
	private static CourseTable courseTable=null;
	private static ChoiceTable choiceTable=null;
	private static ClassCourseTable classcoursetable=null;
	private static NewsTable newsTable=null;
	private static Connection connection=null;
	
	private static String driver=Constant.driver;
	private static String url=Constant.url;
	private static String username=Constant.username;
	private static String password=Constant.password;
	private static int totalCourseNum=-1;
	
	public void connect() throws SQLException,DriverException
	{
		connection=getConnection();
		connection.setAutoCommit(false);
		statement=connection.createStatement();
		studentTable=new StudentTable(statement);
		courseTable=new CourseTable(statement);
		choiceTable=new ChoiceTable(statement);
		classcoursetable=new ClassCourseTable(statement);
		newsTable=new NewsTable(statement);
		scoreTable=new ScoreTable(statement);
	}
	
	public static void setUsername(String u)
	{
		username=u;
	}
	
	public static String getUsername()
	{
		return username;
	}
	
	public static void setPassword(String p)
	{
		password=p;
	}
	
	public static String getPassword()
	{
		return password;
	}
	
	public static void setUrl(String u)
	{
		url=u;
	}
	
	public static String getUrl()
	{
		return url;
	}
	
	public static void setDriver(String d)
	{
		driver=driver;
	}
	
	public static String getDriver()
	{
		return driver;
	}
	
	public Connection getConnection()throws SQLException,DriverException
	{
		try
		{
			Class.forName(driver);
		}
		catch(ClassNotFoundException e)
		{
			throw new DriverException();
		}
		return DriverManager.getConnection(url,username,password);
	}
	
	public String getPassword(String studentID) throws SQLException
	{ 
		String password=null;
	   	ResultSet rs=null;
	   	rs=studentTable.select(studentID);	   
	   	if(rs.next())
	   	{
	   		password=rs.getString("PASSWORD");		 	   
	   	}else
	   	{
	   		password=null;	   		   		   		   	
	   	}
	   	return password;	 	 	
	} 
	
	public void updatePassword(String studentID,String password) throws SQLException
	{ 
		try
		{
			studentTable.updatePassword(studentID,password);
	   		connection.commit();
		}
		catch(SQLException e)
		{
			connection.rollback();
			throw e;
		}
	}
	
	public int [] getAllCourse(String [] allCourse) throws SQLException
	{
	 	ResultSet rs=null;
	 	int rsColmn=7;
	 	int total=getTotalCourseNum();
		rs=courseTable.select(-1);
		int[] courseID=new int[total];
	 	int i=0;
	 	while(rs.next())
	 	{
	 		courseID[i]=rs.getInt(1);
	 		allCourse[i]="";
	 		for(int m=2;m<=rsColmn-2;m++)
	 		{
	 			allCourse[i]=allCourse[i]+rs.getString(m);
	 		}
	 		i++;
	 	}
	 	return courseID;
	}
	
	public void updateSelectedCourse(String studentID,int[] course,int courseNum) throws SQLException,QuotaException//,DB2Exception
	{
	 	try
	 	{
	 		int i;
	 		int num=getSelectedCourseNum(studentID);
			int [] oldCourseID=null;
			oldCourseID=getSelectedCourseID(studentID);
			for(i=0;i<num;i++)
			{
				courseTable.updateDecrease(oldCourseID[i]);
			}
	 		if(num>0)
	 			choiceTable.delete(studentID);
	 		studentTable.updateCourseChosen(studentID,courseNum);
	 		int [] error=new int[courseNum];
	 		for(i=0;i<courseNum;i++)
	 			error[i]=0;
	 		int j=0;
	 		for(i=0;i<courseNum;i++)
	 		{
	 			try
				{
					courseTable.updateIncrease(course[i]);
				}
				catch(SQLException e)
				{
					/*
					error[j]=course[i];
					j++;
					*/
					throw new QuotaException(course[i]);
				}
	 			choiceTable.insert(studentID,course[i]);
	 			//System.out.println(course[i]);	
	 		}
	 		/*
	 		if(j>0)
	 			throw new QuotaException(error);
	 		*/	
	 		connection.commit();
	 	}
	 	catch(SQLException e)
	 	{
	 		connection.rollback();
	 		throw e;
	 	}
	 	catch(QuotaException q)
	 	{
	 		connection.rollback();
	 		throw q;
	 	}
	}

	public int getTotalCourseNum() throws SQLException
	{
		ResultSet rs=null;
	 	if(totalCourseNum<0)
	 	{
			rs=courseTable.select(-1);
			int temp=0;
			while(rs.next())
				temp++;
			return temp;				
	 	}
	 	else
	 		return totalCourseNum;
	}
	
	//如果studentID不在数据库中，会产生异常么，返回的resultset是什么？
	//待修改
	public int getSelectedCourseNum(String studentID) throws SQLException
	{
	 	ResultSet rs=null;
	 	rs=studentTable.select(studentID);
		if(rs.next())
			return rs.getInt("COURSECHOSEN");
	 	else
	 		return 0;
	}
	
	public int [] getSelectedCourseID(String studentID) throws SQLException
	{
		int num=getSelectedCourseNum(studentID);
		int [] courseID=new int[num];
		ResultSet rs=choiceTable.select(studentID);
		int i=0;
		while(rs.next())
		{
		   	courseID[i]=rs.getInt("CourseID");
			i++;
		}
		rs.close();
		return courseID;
	}
	
	
	public String[] getSelectedCourseName(String studentID) throws SQLException
	{
		ResultSet rs=null;
		int num=getSelectedCourseNum(studentID);
		String [] courseName=new String[num];
		int [] courseID=new int[num];
		courseID=getSelectedCourseID(studentID);
		for(int i=0;i<num;i++)
		{
			rs=courseTable.select(courseID[i]);
			rs.next();
			courseName[i]=rs.getString(2);
			rs.close();	
		}
		return courseName;
	}
	
	public String[][] getClassCourse(String classID) throws SQLException,ClassNotExistException
	{
		ResultSet rs=classcoursetable.select(classID);
		if(!rs.next())
			throw new ClassNotExistException();
		String [][] courses=new String[Constant.days][Constant.coursenum];
		for(int i=0;i<Constant.days;i++)
			for(int j=0;j<Constant.coursenum;j++)
			{
				courses[i][j]=rs.getString(i*5+j+2);
				if(courses[i][j]==null)
					courses[i][j]="";
			}
		return courses;
	}
	
	public int getClassNum(String classID) throws SQLException
	{
		ResultSet rs=classcoursetable.select(classID);
		int i=0;
		while(rs.next())
			i++;
		return i;
	}
	
	public void close() throws SQLException
	{
		statement.close();
		//connection.close();
	}
	
	//新加功能
	
	public int getCategoryNum() throws SQLException
	{
		ResultSet rs=newsTable.selectCategory();
		int i=0;
		while(rs.next())
			i++;
		rs.close();
		return i;
	}
	
	public String[] getCategory() throws SQLException
	{
		int num=getCategoryNum();
		String [] category=new String[num];
		ResultSet rs=newsTable.selectCategory();
		int i=0;
		while(rs.next())
		{
			category[i]=rs.getString(1);
			i++;
		}
		rs.close();
		return category;
	}
	
	public int getTitleNum(String category) throws SQLException
	{
		ResultSet rs=newsTable.selectTitle(category);
		int i=0;
		while(rs.next())
			i++;
		rs.close();
		return i;
	}
	
	public int [] getTitleID(String category) throws SQLException
	{
		int num=getTitleNum(category);
		int [] titleID=new int[num];
		ResultSet rs=newsTable.selectTitle(category);
		int i=0;
		while(rs.next())
		{
			titleID[i]=rs.getInt(1);
			i++;
		}
		rs.close();
		return titleID;
	}
	
	public String[] getTitle(String category) throws SQLException
	{
		int num=getTitleNum(category);
		String []title=new String[num];
		ResultSet rs=newsTable.selectTitle(category);
		int i=0;
		while(rs.next())
		{
			title[i]=rs.getString(4)+rs.getString(3);
			i++;
		}
		rs.close();
		return title;
	}
	
	public String getDetail(int newsID) throws SQLException
	{
		ResultSet rs=newsTable.select(newsID);
		rs.next();
		return rs.getString(5);
	}
	
	//新功能
	
	private ScoreTable scoreTable=null;
	
	public String[] getScore(String studentID,int term) throws SQLException
	{
		int num=scoreTable.selectNum(studentID,term);
		String[] score=new String[num];
		int i=0;
		ResultSet rs=scoreTable.select(studentID,term);
		while(rs.next())
		{
			score[i]=rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4)+" "+rs.getString(5);
			i++;
		}
		return score;
	}

public int getScoreNum(String studentID,int term)
	{
		return scoreTable.selectNum(studentID,term);
	}
}