database.java

简单的学生管理系统C/S结构的

package stuinfosystem;

import java.sql.*;
import java.io.*;
import java.net.Socket;
import java.util.Vector;
import java.util.Enumeration;

class Database
{
	/*a，连接数据库的方法
	 * b，插入数据/编辑数据/删除数
	 *据/查询数据方法各一个
	 * c，释放数据库资源方法。
	 */
	 private Connection conn=null;
	 private Statement stm = null;
	 private ResultSet rs = null;
	 private Vector v = null;
	 
	 //连接上服务器的客户端用户
	 private Socket s = null;
	 
	 //数据库操作执行结果 
	 String resultString = null;
	 
	 //构造方法
	 public Database(Socket s,Vector v)
	 {
	 	//连接数据库方法
	 	this.s = s;
	 	this.v = v;
	 	dataConnect();
	 }
	 
	 //连接数据库方法
	 private void dataConnect()
	 {
	 	try
	 	{
	 		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
	    	String url="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};"+"DBQ=data.mdb";
	    	conn = DriverManager.getConnection(url);
	    	
	    	//建立查询
	    	stm = conn.createStatement();
	    }
	    catch(Exception e)
	    {
	    	resultString = e.getMessage();
	    	//报告数据库执行错误错误
	    	reportDatabaseResult();
	    }
	    
	 }
	 
	 //关闭数据库方法
	 public void closeDatabase()
	 {
	 	//关闭记录集ResultSet
	 	if(rs!=null)
	 	{
	 		try
	 		{
	 			rs.close();	
	 		}
	 		catch(Exception e_rs)
	 		{
	 			resultString = e_rs.getMessage();
	    		//报告数据库执行错误错误
	    		reportDatabaseResult();
	 		}
	 	}
	 	
	 	//关闭查询Statement
	 	if(stm!=null)
	 	{
	 		try
	 		{
	 			stm.close();	
	 		}
	 		catch(Exception e_stm)
	 		{
	 			resultString = e_stm.getMessage();
	    		//报告数据库执行错误错误
	    		reportDatabaseResult();
	 		}
	 	}
	 	
	 	//关闭查询Connection
	 	if(conn!=null)
	 	{
	 		try
	 		{
	 			conn.close();	
	 		}
	 		catch(Exception e_conn)
	 		{
	 			resultString = e_conn.getMessage();
	    		//报告数据库执行错误错误
	    		reportDatabaseResult();
	 		}
	 	}
	 }
	 
	 //操作数据库指示
	 public void operationDatabase()
	 {
	 		 	
	 	if(v == null)
	 	{
	 		searchData("","");
	 		return;
	 	}
	 	
	 	Enumeration e = v.elements();
	 	String keyStr = (String)e.nextElement();
	 	int key = Integer.parseInt(keyStr);
	 	
	 	switch (key)
	 	{
	 		case MyConstants.INSERT_KEY:
	 		{
			 	String insertStr="";
			 	while(e.hasMoreElements())
			 	{
			 		if(insertStr.equals(""))
			 		{
			 			insertStr="'"+(String) e.nextElement()+"'";
			 		}
			 		else
			 		{
			 			insertStr = insertStr + ",'" + (String) e.nextElement()+"'";
			 		}
			 	}
			 		
			 	insertData(insertStr);
			 	break;
			 }
			 	
			 case  MyConstants.SEARCH_KEY:
			 {
			 	String point="",searchStr="";
			 	
			 	point = (String) e.nextElement();
			 	searchStr = (String) e.nextElement();
			 	searchData(point,searchStr);
			 	break;
			 }
			 
			 case  MyConstants.UPDATE_KEY:
			 {
			 	String updateStr="",updateID="";
			 	updateID = (String) e.nextElement();
			 	String titleArray[] = MyConstants.TABLENAMES.split(",");
			 	while(e.hasMoreElements())
			 	{
			 		for(int i=1;i<titleArray.length;i++)
			 		{
			 			if(updateStr.equals(""))
				 		{
				 			updateStr=titleArray[i]+"='"+(String) e.nextElement()+"'";
				 		}
				 		else
				 		{
				 			updateStr = updateStr + ","+titleArray[i]+"='" + (String) e.nextElement()+"'";
				 		}
			 		}
			 		
			 	}
			 		
			 	updateData(updateID,updateStr);
			 	break;
			 }
			 
			 case  MyConstants.DELETE_KEY:
			 {
			 	String deleteID="";
			 	
			 	deleteID = (String) e.nextElement();
			 	
			 	deleteData(deleteID);
			 	break;
			 }
			 
			 default:
			 	searchData("","");
			 	break;
	 	}
	 	
	 	
	 }
	 //插入数据
	 public void insertData(String insertStr)
	 {
	 	if(stm==null)
	 	{
	 		resultString = "建立数据库查询失败";
	 		reportDatabaseResult();
	 		
	 	}
	 	else
	 	{
	 		try
	 		{
	 			String sql = "insert into " + MyConstants.TABLENAME 
	 			+"(" + MyConstants.TABLENAMES 
	 			+ ") values(" + insertStr +")";
	 				 			
	 			stm.executeUpdate(sql);
	 			
	 			resultString = MyConstants.INSERT;
	 			reportDatabaseResult();
	 			
	 		}
	 		catch(Exception e)
	 		{
	 			resultString = "数据插入失败" + e.getMessage();
	 			reportDatabaseResult();
	 		}
	 		finally
	 		{
	 			//关闭数据库连接
	 			closeDatabase();
	 		}
	 	}
	 }
	 
	 //查询数据,key为查询的类型，keyword为查询的条件
	 public void searchData(String key,String keyword)
	 {
	 	if(stm==null)
		 	{
		 		resultString = "建立数据库查询失败";
		 		reportDatabaseResult();
		 		return;//退出函数
		 	}
		 	
		Vector v = new Vector();
		//v = null;	
		int len = MyConstants.TITLENAMES.length;	 
	 	if(key.equals(MyConstants.KEY_NAME) && (keyword.length()>0))
	 	{
	 		
		 		try
		 		{
		 			rs = stm.executeQuery("select " + MyConstants.TABLENAMES 
		 			+ " from " + MyConstants.TABLENAME + " where " 
		 			+ MyConstants.KEY_NAME_TITLE + " like '%" + keyword + "%'");
		 		
		 			//打包数据，进行网络传输ResultSet对象rs。Vector 是实现了系列化接口的
		 			
		 			while(rs.next())
		 			{
		 				Vector vector = new Vector();
		 				for(int i=1;i<len+1;i++)
		 				{
		 					vector.addElement(rs.getString(i));		 					
		 				}
		 				
		 				v.add(vector);
		 			}
		 			
		 		}
		 		catch(Exception e)
		 		{
		 			resultString = "查询失败!"+e.getMessage();	
		 			reportDatabaseResult();
		 		}
		 		
		 }
	 	
	 	else if(key.equals(MyConstants.KEY_ID) && keyword.length()>0)
	 	{
	 		try
		 		{		 			
		 			rs = stm.executeQuery("SELECT " + MyConstants.TABLENAMES 
		 			+ " FROM " + MyConstants.TABLENAME + " WHERE " 
		 			+ MyConstants.KEY_ID_TITLE + " ='" + keyword +"'");
		 		 		 		 
		 			//打包数据，进行网络传输ResultSet对象rs。Vector 是实现了系列化接口的
		 			
		 			while(rs.next())
		 			{
		 				Vector vector = new Vector();
		 				for(int i=1;i<len+1;i++)
		 				{
		 					vector.addElement(rs.getString(i));		 					
		 				}
		 				
		 				v.add(vector);
		 			}
		 			
		 		}
		 		catch(Exception e)
		 		{
		 			resultString = "查询失败!"+e.getMessage();	
		 			reportDatabaseResult();
		 		}
	 	}
	 	
	 	else
	 	{
	 		try
		 		{
		 			rs = stm.executeQuery("select " + MyConstants.TABLENAMES 
		 			+ " from " + MyConstants.TABLENAME);
		 		
		 			//打包数据，进行网络传输ResultSet对象rs。Vector 是实现了系列化接口的
		 					 			
		 			
		 			while(rs.next())
		 			{
		 				Vector vector = new Vector();
		 				for(int i=1;i<len+1;i++)
		 				{
		 					vector.addElement(rs.getString(i));		 					
		 				}
		 				
		 				v.add(vector);
		 			}
		 			
		 		}
		 		catch(Exception e)
		 		{
		 			resultString = "查询失败!"+e.getMessage();	
		 			reportDatabaseResult();
		 		}
	 	}
	 	
	 	//输出数据
	 	try
	 	{
	 		OutputStream os = s.getOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(os);
			oos.writeObject(v);
			oos.close();
	 	}
	 	catch(Exception e)
		{
			e.printStackTrace();
		 	resultString = "发送数据失败!"+e.getMessage();	
		 	reportDatabaseResult();
		}
	 	
	 }
	 
	//更新数据
	private void updateData(String stuID,String updateStr)
	{
		if(stm==null)
	 	{
	 		resultString = "建立数据库查询失败";
	 		reportDatabaseResult();
	 		
	 	}
	 	else
	 	{
	 		try
	 		{
	 			String SQL = "update "+MyConstants.TABLENAME + " set "+ updateStr +" where "
	 			+ MyConstants.KEY_ID_TITLE + "='" + stuID +"'";
	 			
	 			stm.executeUpdate(SQL);
	 			
	 			resultString = MyConstants.UPDATE;
	 			reportDatabaseResult();
	 			 
	 		}
	 		catch(Exception e)
	 		{
	 			resultString = "更新学号为"+stuID+"的数据失败"+e.getMessage();
	 			reportDatabaseResult();
	 		}
	 	}
	}

	//删除数据
	public void deleteData(String stuID)
	{
		if(stm==null)
	 	{
	 		resultString = "建立数据库查询失败";
	 		reportDatabaseResult();
	 		
	 	}
	 	else
	 	{
	 		try
	 		{
	 			String SQL = "delete from "+MyConstants.TABLENAME + " where "
	 			+ MyConstants.KEY_ID_TITLE + "='" + stuID +"'";
	 			
	 			stm.executeUpdate(SQL);
	 			
	 			resultString = MyConstants.DELETE;
	 			reportDatabaseResult();
	 			 
	 		}
	 		catch(Exception e)
	 		{
	 			resultString = "删除学号为"+stuID+"的数据失败"+e.getMessage();
	 			reportDatabaseResult();
	 		}
	 	}
	}
	 
	 //报告数据库处理结果
	 public void reportDatabaseResult()
	 {
	 	try
	 	{
	 		//把数据库处理结果resultString发送给客户端
	 		OutputStream os = s.getOutputStream();
	 		//发送错误结果长度
	 		os.write(resultString.getBytes().length);
	 		//发送错误结果的字节数组
	 		os.write(resultString.getBytes());
	 		//os.close();
	 	}
	 	catch(Exception e)
	 	{
	 		//报告异常给服务器，可建立日志文件
	 		System.out.println("发送数据库处理结果至客户端失败");
	 		e.printStackTrace();
	 	}
	 }
	 
}