dbmodel.java

是一个简化的学校教职工管理系统

//import java.util.Date;
import java.sql.*;
import java.util.Observable;
import javax.swing.*;

import java.awt.*;
public class DBModel extends Observable {

	Connection con;
	ResultSet rs;
	boolean running ;
	boolean updating;
	PreparedStatement pre;
	//ResultSetTableModel tr;
	public DBModel(){
		
		try{
			con=DBConnector.getAccessConnection("teacher","","");
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		
		rs=null;
		running=true;
		updating =false;
		pre=null;
		
	}
	
	public void insert(long no,String name,String sex,String dept,int age,int worktime ,String pro,String cla,String expe){
		if(no==-1||age==-1||worktime==-1)return;
		String s="INSERT INTO tinfo VALUES (?,?,?,?,?,?,?,?,?)";
		try{
			
			pre=con.prepareStatement(s);
			pre.setLong(1, no);
			pre.setString(2, name);
			pre.setString(3, sex);
			
			pre.setString(4, dept);
			pre.setInt(5, age);
			pre.setInt(6, worktime);
	        pre.setString(7, pro);
			pre.setString(8, cla);
			pre.setString(9,expe);
			
			pre.executeUpdate();
			
			
			
			
		}catch(SQLException e){
			
			//e.printStackTrace();
			JOptionPane.showMessageDialog(null, e.toString());
			//System.out.println(e.getMessage());
		}
		
		 /////////////////////////////////////////////
		this.allTinfo();
		//showChanged();
	}	
	public void insert(long no,String name,double wage,double othwage,double monwage){
		
		if(no==-1||wage==-1||othwage==-1||monwage==-1)return;
		String s="insert into twage values (?,?,?,?,?)";
		try{
			pre=con.prepareStatement(s);
			pre.setLong(1, no);
			pre.setString(2, name);
			pre.setDouble(3,wage);
			pre.setDouble(4, othwage);
			pre.setDouble(5,monwage);
			pre.executeUpdate();
			
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
			
		}
		 /////////////////////////////////////////////
		this.allTwage();
		//showChanged();
	}
	/*
	 * 
	 * update the tinof data-table
	 * 
	 * 
	 * 
	 * */
	public void update(long oldNo,long no,String name,String sex,String dept,int age,int worktime,String pro,String cla,String expe){
		if(no==-1||age==-1||worktime==-1)return;
		String s="update tinfo set no =?,name=?,sex=?,dept=?,age=?,worktime=?,pro=?,cla=?,expe=? where no=?";
		try{
			pre=con.prepareStatement(s);
			pre.setLong(1, no);
			pre.setString(2, name);
			pre.setString(3, sex);
			
			pre.setString(4, dept);
			pre.setInt(5, age);
			pre.setInt(6, worktime);
			pre.setString(7, pro);
			pre.setString(8, cla);
			pre.setString(9,expe);
			pre.setLong(10,oldNo);
			pre.executeUpdate();
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
			
		}
		 /////////////////////////////////////////////
		allTinfo();
		//showChanged();
	}
	/*
	 * update the twage data-table
	 * 
	 * */
	public void update(long oldNo,long no,String name,double wage,double othwage,double monwage){
		if(no==-1||wage==-1||othwage==-1||monwage==-1)return;
		String s="update twage set no=?,name=?,wage=?,othwage=?,monwage=? where no=?";
		try{
			if(no==-1){
				JOptionPane.showMessageDialog(null, "编号不正确！");
				return;
			}
			pre=con.prepareStatement(s);
			pre=con.prepareStatement(s);
			pre.setLong(1, no);
			pre.setString(2, name);
			pre.setDouble(3,wage);
			pre.setDouble(4, othwage);
			pre.setDouble(5,monwage);
			pre.setLong(6, oldNo);
			pre.executeUpdate();
			
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
			
		}
	    /////////////////////////////////////////////
		allTwage();
		//showChanged();
	}
	/*
	 * 
	 * query the twage data-table
	 * 
	 * */
	public void query(long no,String name,double wage,double othwage,double monwage){
		
		
		String s;
		try{
			
			if(no!=-1){
				s="select * from twage where no=?";
				pre=con.prepareStatement(s);
				pre.setLong(1, no);
				
			}
			else{
				if(!name.equals("")){
					s="select * from twage where name=?";
					pre=con.prepareStatement(s);
					pre.setString(1, name);
					
					
				}
				else
				{
					if(wage!=-1){
						
						s="select * from twage where wage=?";
						pre=con.prepareStatement(s);
						pre.setDouble(1, wage);
					}
					else{
						if(othwage!=-1){
							s="select * from twage where othwage=?";
							pre=con.prepareStatement(s);
							pre.setDouble(1, othwage);
						}
						else{
							if(monwage!=-1){
								
								s="select * from twage where monwage=?";
								pre=con.prepareStatement(s);
								pre.setDouble(1, monwage);
							   
							}
							else{
								pre=null;
							}
						}
					}
				}
			}
			if(pre!=null)
			rs=pre.executeQuery();
			//////////////////////////////////////
			//JOptionPane.showMessageDialog(null, "rs=pre.executeQuery();");
		    //////////////////////////////////////
		}catch(SQLException e){
			
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	///////////////////////////////////////////////////////
		showChanged();
	}
	
	/*
	 * query the tinfo data-table
	 * 
	 * */
	public void query(long no,String name,String sex,String dept,int age,int worktime,String pro,String cla,String expe){
		
		String s;
		try{
			
			if(no!=-1){
				s="select * from tinfo where no=?";
				pre=con.prepareStatement(s);
				pre.setLong(1, no);
			}
			else if(!name.equals("")){
				s="select * from tinfo where name=?";
				pre=con.prepareStatement(s);
				pre.setString(1, name);
			}
			else if(!sex.equals("")){
				s="select * from tinfo where sex=?";
				pre=con.prepareStatement(s);
				pre.setString(1, sex);
			}
			else if(!dept.equals("")){
				
				s="select * from tinfo where dept=?";
				pre=con.prepareStatement(s);
				pre.setString(1, dept);
			}
			else if(age!=-1){
				s="select * from tinfo where age=?";
				pre=con.prepareStatement(s);
				pre.setInt(1, age);
			}
			else if(worktime!=-1){
				s="select * from tinfo where worktime=?";
				pre=con.prepareStatement(s);
				pre.setInt(1,worktime);
			}
			else if(!pro.equals("")){
				s="select * from tinfo where pro=?";
				pre=con.prepareStatement(s);
				pre.setString(1, pro);
			}
			else if(!cla.equals("")){
				s="select * from tinfo where cla=?";
				pre=con.prepareStatement(s);
				pre.setString(1, cla);
			}
			
			else if(!expe.equals("")){
				
				s="select * from tinfo where expe=?";
				pre=con.prepareStatement(s);
				pre.setString(1,expe);
			}
			else{
				pre=null;
			}
			if(pre!=null)
			rs=pre.executeQuery();
		  //JOptionPane.showMessageDialog(null, "rs=pre.executeQuery();");
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	   //////////////////////////////////////////////////////////
		showChanged();
	}
	/*
	 * delete data from TINFO table
	 * */
	public void delete(long no,String name,String sex,String dept,int age,int worktime,String pro,String cla,String expe){
		String s;
		try{
			
			if(no!=-1){
				s="delete from tinfo where no=?";
				pre=con.prepareStatement(s);
				pre.setLong(1, no);
			}
			else if(!name.equals("")){
				s="delete   from tinfo where name=?";
				pre=con.prepareStatement(s);
				pre.setString(1, name);
			}
			else if(!sex.equals("")){
				s="delete from tinfo where sex=?";
				pre=con.prepareStatement(s);
				pre.setString(1, sex);
			}
			else if(!dept.equals("")){
				
				s="delete from tinfo where dept=?";
				pre=con.prepareStatement(s);
				pre.setString(1, dept);
			}
			else if(age!=-1){
				s="delete  from tinfo where age=?";
				pre=con.prepareStatement(s);
				pre.setInt(1, age);
			}
			else if(worktime!=-1){
				s="delete  from tinfo where worktime=?";
				pre=con.prepareStatement(s);
				pre.setInt(1,worktime);
			}
			else if(!cla.equals("")){
				s="delete from tinfo where cla=?";
				pre=con.prepareStatement(s);
				pre.setString(1, cla);
			}
			else if(!pro.equals("")){
				s="delete  from tinfo where pro=?";
				pre=con.prepareStatement(s);
				pre.setString(1, pro);
			}
			else if(!expe.equals("")){
				
				s="delete from tinfo where expe=?";
				pre=con.prepareStatement(s);
				pre.setString(1,expe);
			}
			else{
				pre=null;
			}
			if(pre!=null)
			pre.executeUpdate();
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	   ///////
		this.allTinfo();
		//showChanged();
		
	}
	/*
	 * delete data from TWAGE table
	 * */
	public void delete (long no,String name,double wage,double othwage,double monwage){
		String s;
		//JOptionPane.showMessageDialog(null,"in side"+wage);
		try{
			
			if(no!=-1){
				s="delete  from twage where no=?";
				pre=con.prepareStatement(s);
				pre.setLong(1, no);
				
			}
			else{
				if(!name.equals("")){
					s="delete from twage where name=?";
					pre=con.prepareStatement(s);
					pre.setString(1, name);
					
					
				}
				else
				{
					if(wage!=-1){
						//JOptionPane.showMessageDialog(null,""+wage);
						
						s="delete from twage where wage=?";
						pre=con.prepareStatement(s);
						pre.setDouble(1, wage);
					}
					else{
						if(othwage!=-1){
							s="delete  from twage where othwage=?";
							pre=con.prepareStatement(s);
							pre.setDouble(1, othwage);
						}
						else{
							if(monwage!=-1){
								
								s="delete  from twage where monwage=?";
								pre=con.prepareStatement(s);
								pre.setDouble(1, monwage);
							   
							}
							else
								pre=null;
						}
					}
				}
			}
			if(pre!=null)
			pre.executeUpdate();
		}catch(SQLException e){
			
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	   ///////
		this.allTwage();
		//showChanged();
	}
	public ResultSet getResultSet(){
		
		return rs;
	}
	//##########################
	//####
	//###    检查两个数据表中匹配数据有多少并显示出来
	//###
	//##########################
	public void checkTwoTable(){
		
		String s="select tinfo.no,tinfo.name,sex,dept,age,worktime,pro,cla,expe, "+"" +
				"wage,othwage,monwage from tinfo,twage "+
               "where (tinfo.no=twage.no ) and (tinfo.name=twage.name)";
		try{
			Statement st=con.createStatement();
			rs=st.executeQuery(s);
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	   ///////
		showChanged();
	}
	public void allTinfo(){
		String s="select * from tinfo";
		try{
			Statement st=con.createStatement();
			rs=st.executeQuery(s);
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	   ///////
		showChanged();
	}
	public void allTwage(){
		String s="select * from twage";
		try{
			Statement st=con.createStatement();
			rs=st.executeQuery(s);
		}catch(SQLException e){
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		///////
		showChanged();
		
	}
	public void showChanged() {
		
				this.setChanged();
				this.notifyObservers();
				//this.closeStatement();
				
	}
	public void closeStatement(){
		
		try{
			rs.close();
			pre.close();
			
		}catch(SQLException e){
			
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
	}
	public void selfQuery(String sql){
		
		if(sql==null)
			return;
		try {
			Statement stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			this.showChanged();
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(null, e.getMessage());
		}
		
		
	}
	/*public static void main(String[] args){
		
		
		DBModel model=new DBModel();
		
		model.insert(6, "ygdiopoip", "f", "haian", 45, 55, "student", "student", "studying");
		
	}*/
 
}