sqloperator.java

学生资料管理系统 基于c/s模式开发 用户可以运行用户端登陆服务器 然后通过客户端来进行服务器端的数据库操作

import java.sql.*;
import java.util.Calendar;


/**
 * author sunringove
 * data 2007-01-17
 * 
 */
public class SqlOperator{
		
	private Connection con = null;
	private Statement stmt=null;
	private ResultSet rs=null;
	public Member member[]=new Member[1024];
	public int index=0;
	public Calendar calendar=Calendar.getInstance();
	public String string;
	
	public SqlOperator() throws SQLException, ClassNotFoundException{
	
			// load driver class
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

			// data source
			String url = "jdbc:odbc:Manager";

			// get connection
			con = DriverManager.getConnection(url, "admin", "000000");

			// create SQL statement
			stmt = con.createStatement();
	}
			
	//	 execute query
	// 查找所有记录
	public Member[] selectAll() throws SQLException{
		
		rs = stmt.executeQuery("select * from Information");
		string=new String(calendar.getTime()+" select * from Information");
		
		System.out.println(calendar.getTime()+" select * from Information");
		
				
		while(rs.next())
		{
			member[index]=new Member();
			
			member[index].setName(rs.getString("姓名"));
			member[index].setID(rs.getString("学号"));
			member[index].setGender(rs.getString("性别"));
			member[index].setYear(rs.getString("入学年份"));
			member[index].setMajor(rs.getString("专业"));
			member[index].setDepartment(rs.getString("系别"));
			member[index].setLeague(rs.getString("所在社团"));
			member[index].setenterTime(rs.getString("加入时间"));
			member[index].setquitTime(rs.getString("退出时间"));
			
			index++;
		}
		return member;
		
	}
	
	//查找指定记录
	public Member[] select(String listName,String value) throws SQLException{
		rs = stmt.executeQuery("select * from Information "+"where ("+listName+"='"+value+"')");
		string=new String(calendar.getTime()+" select * from Information "+"where ("+listName+"='"+value+"')");
		System.out.println(calendar.getTime()+" select * from Information "+"where ("+listName+"='"+value+"')");
		
		while(rs.next())
		{
			member[index]=new Member();
			member[index].setName(rs.getString("姓名"));
			member[index].setID(rs.getString("学号"));
			member[index].setGender(rs.getString("性别"));
			member[index].setYear(rs.getString("入学年份"));
			member[index].setMajor(rs.getString("专业"));
			member[index].setDepartment(rs.getString("系别"));
			member[index].setLeague(rs.getString("所在社团"));
			member[index].setenterTime(rs.getString("加入时间"));
			member[index].setquitTime(rs.getString("退出时间"));
			
			index++;
		}
		return member;
		
	}
	
	//返回数据库中记录的条数
	public int getIndex(){
		return index;
	}
	
	//删除指定记录
	public void Delete(String listName,String value) throws SQLException{
				stmt.executeUpdate("Delete FROM Information WHERE "+listName+"='"+value+"'");
				string=new String(calendar.getTime()+" Delete FROM Information WHERE "+listName+"='"+value+"'");
				System.out.println(calendar.getTime()+" Delete FROM Information WHERE "+listName+"='"+value+"'");
	}
	
	//更新指定记录
	public void Update(String listName,String value,String targetList,String targetValue) throws SQLException{
				stmt.executeUpdate(" UPDATE  Information SET "+targetList+"='"
													+targetValue+"' WHERE "+listName+"='"+value+"'");
	}
	
	//插入记录
	public void Insert(Member member) throws SQLException{
				stmt.executeUpdate("insert into Information values('"+member.getName()+"','"
														+member.getID()+"','"
														+member.getGender()+"','"
														+member.getYear()+"','"
														+member.getMajor()+"','"
														+member.getDepartment()+"' ,'"
														+member.getLeague()+"','"
														+member.getenterTime()+"','"
														+member.getquitTime()+"')");
				
		string=new String(calendar.getTime()+" insert into Information values('"+member.getName()+"','"
				+member.getID()+"','"
				+member.getGender()+"','"
				+member.getYear()+"','"
				+member.getMajor()+"','"
				+member.getDepartment()+"' ,'"
				+member.getLeague()+"','"
				+member.getenterTime()+"','"
				+member.getquitTime()+"')");
		System.out.println(calendar.getTime()+" insert into Information values('"+member.getName()+"','"
				+member.getID()+"','"
				+member.getGender()+"','"
				+member.getYear()+"','"
				+member.getMajor()+"','"
				+member.getDepartment()+"' ,'"
				+member.getLeague()+"','"
				+member.getenterTime()+"','"
				+member.getquitTime()+"')");
																
	}
	
	//断开数据库连接
	public void closeDB() throws SQLException{
		con.close();
	}
	
	public String getQuery(){
		return string;
	}
}