sql.java

课程设计当中的一个。希望能帮到大家！~·

import java.sql.*;
class SQL extends MainWindow
{	
	String url="jdbc:odbc:stu";
	static Connection con;
	SQL(){
	}
    MainWindow m;
    SQL(MainWindow m)
    {
    	this.m=m;
    }
	void lianjie()
	{
		try{Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		}catch(java.lang.ClassNotFoundException e){
			System.err.print("ClassNotFoundException:");
			System.err.println(e.getMessage());
		}
		try{
			con=DriverManager.getConnection(url,"system","system");
		}catch(SQLException ee){
			System.err.println("SQLException:"+ee.getMessage());
		}
	}
	void guanbi()
	{
		try{con.close();
		}catch(SQLException eee){
			System.err.println("SQLException:"+eee.getMessage());
		}
	}
	void selectZbName()
	{		
		m.listData.clear();
		String sql="SELECT name FROM zblist";
		
		try{					    
		    Statement stmt=con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
		    while(rs.next())
		    {
			   String s=rs.getString(1);
			   m.listData.addElement(s);
		    }
		    stmt.close();
		    }catch(SQLException ex){
		    	System.err.println("SQLException:"+ex.getMessage());
		    }
	}
    void selectZb()
    {
    	String sql="SELECT * FROM zblist WHERE name="+"'"+m.ZbName+"'";
    	try{
    		Statement stmt=con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);
			while(rs.next())
			{
				m.xl4.setText(rs.getString(1));
				m.xl5.setText(rs.getString(3));
				m.xl6.setText(String.valueOf(rs.getInt(4)));
			}
			stmt.close();
    	}catch(SQLException e){
    		System.err.println("SQLEXCEption:"+e.getMessage());
    	}
    }
	void checkUser()
	{
		String password;
		 
        String n=m.nameText.getText();
        String p=m.passwordText.getText();
        String sql="SELECT password FROM zblist WHERE name=?";
        try{
         	CallableStatement stmt1=con.prepareCall(sql);
            stmt1.setString(1,n);
            ResultSet rs=stmt1.executeQuery();
            while(rs.next())
            {
            	password=rs.getString(1);
            	m.j=p.compareTo(password);            
            }
            stmt1.close();	
            }catch(SQLException e){
            	System.err.println("SQLEXCEption:"+e.getMessage());
            }             
	}
	void deleteCheck()
	{
		String password;
		String n=m.YnameText.getText();
		String p=m.YpasswordText.getText();
		String sql="SELECT password FROM zblist WHERE name=?";
		try{
         	CallableStatement stmt1=con.prepareCall(sql);
            stmt1.setString(1,n);
            ResultSet rs=stmt1.executeQuery();
            while(rs.next())
            {
            	password=rs.getString(1);
            	m.k=p.compareTo(password);            
            }
            stmt1.close();	
            }catch(SQLException e){
            	System.err.println("SQLEXCEption:"+e.getMessage());
            }
	}
	void delete()
	{
		String n=m.YnameText.getText();
		String sql="DELETE FROM zblist WHERE name="+"'"+n+"'";
		String sql2="DELETE FROM zbdata WHERE name="+"'"+n+"'";
		try{
         	Statement stmt=con.createStatement();
            
            stmt.executeUpdate(sql);
            stmt.executeUpdate(sql2);  
            stmt.close();	
            }catch(SQLException e){
            	System.err.println("SQLEXCEption:"+e.getMessage());
            }
	}
	void addZb()
	{
		String zbName=m.NewNameText.getText();
		String zbPassword=m.NewPasswordText.getText();
		String zbUser=m.NewUserText.getText();
		int zbRiqi=Integer.parseInt(m.NewRiqiText.getText());
		String sql="INSERT INTO zblist VALUES(?,?,?,?)";
         
		try{
		   CallableStatement stmt1=con.prepareCall(sql);
		   stmt1.setString(1,zbName);
		   stmt1.setString(2,zbPassword);
		   stmt1.setString(3,zbUser);
		   stmt1.setInt(4,zbRiqi);
		   stmt1.executeUpdate();
		   stmt1.close();
		}catch(SQLException e){
			System.err.println("SQLEXCEption:"+e.getMessage());
		}
	}
	void upDataZbName()
	{
		
		String NewZbName=m.nameDialogText.getText();
		String sql="UPDATE zblist SET name="+"'"+NewZbName+"'"+"WHERE NAME="+"'"+m.ZbName+"'";
        String sql2="UPDATE zbdata SET name="+"'"+NewZbName+"'"+"WHERE NAME="+"'"+m.ZbName+"'";
		try{
			Statement stmt=con.createStatement();
			
		    stmt.executeUpdate(sql);
		    stmt.executeUpdate(sql2);
		    stmt.close();
		    
		}catch(SQLException e){
			System.err.println("SQLException:"+e.getMessage());
		}
	}
	void upDataZbPassword()
	{
		String NewZbPassword=m.pDialogText2.getText();
		String sql="UPDATE zblist SET password="+"'"+NewZbPassword+"'"+"WHERE name="+"'"+m.ZbName+"'";
		try{
			Statement stmt=con.createStatement();
		    stmt.executeUpdate(sql);
		    stmt.close();
		}catch(SQLException e){
			System.err.println("SQLException:"+e.getMessage());
		}
	}
	void ruzhang()
	{
		
		String s1=null;
		String s2=null;
		String shuoming;
		int riqi;
		int money;
		if(m.c1.getState()==true)
		{
			s1="收入";
			s2=m.ch1.getSelectedItem();
		}
		if(m.c2.getState()==true)
		{
			s1="支出";
			s2=m.ch2.getSelectedItem();
		}
		
		riqi=Integer.parseInt(m.t2.getText());
		money=Integer.parseInt(m.t1.getText());
		shuoming=m.t3.getText();
        String sql="INSERT INTO zbdata VALUES(?,?,?,?,?,?)";
        try{
        	CallableStatement stmt1=con.prepareCall(sql);
			stmt1.setString(1,m.ZbName);
			stmt1.setInt(2,money);
			stmt1.setInt(3,riqi);
			stmt1.setString(4,s2);
			stmt1.setString(5,shuoming);
			stmt1.setString(6,s1);
			stmt1.executeUpdate();
			stmt1.close();
        }catch(SQLException e){
        	System.err.println("SQLException:"+e.getMessage());
        }
		
		
		
	}
	void tongji()
	{
		System.out.println(m.ZbName);
		int s1,s2,s3;
		String sql1="SELECT SUM(money) FROM zbdata WHERE shouzhi="+"'"+"收入"+"'"+" "+"AND"+" "+"name="+"'"+m.ZbName+"'";
		String sql2="SELECT SUM(money) FROM zbdata WHERE shouzhi="+"'"+"支出"+"'"+" "+"AND"+" "+"name="+"'"+m.ZbName+"'";
		try{
			Statement stmt=con.createStatement();
			ResultSet rs1=stmt.executeQuery(sql1);
			while(rs1.next())
			{
				s1=rs1.getInt(1);
				m.framel4.setText(String.valueOf(s1));
			}
			ResultSet rs2=stmt.executeQuery(sql2);
			while(rs2.next())
			{
				s2=rs2.getInt(1);
				m.framel5.setText(String.valueOf(s2));
			}
		    String a1=m.framel4.getText();
			String a2=m.framel5.getText();
			s3=Integer.parseInt(a1)-Integer.parseInt(a2);
			
			m.framel6.setText(String.valueOf(s3));
			stmt.close();
			
			
		}catch(SQLException e){
			System.err.println("SQLException:"+e.getMessage());
		}
	}
	void chaxun()
	{
			int x=1;
			int count=0;
		int i=Integer.parseInt(m.selectt1.getText());
		int j=Integer.parseInt(m.selectt2.getText());
		String s1=null;
		String s2=null;
		String sql=null;
		if(m.cc1.getState()==true)
		{
			s2=m.cch1.getSelectedItem();
		}
		if(m.cc2.getState()==true)
		{
			s2=m.cch2.getSelectedItem();
		}
		int t=s2.compareTo("全部");
	    if(t==0)
	    {
	    	if(m.cc1.getState()==true)
	    	{
	    		sql="SELECT * FROM zbdata WHERE shouzhi="+"'"+"收入"+"'"+" "+"AND"+" "+"name="+"'"+m.ZbName+"'";
	    	}
	    	if(m.cc2.getState()==true)
	    	{
	    		sql="SELECT * FROM zbdata WHERE shouzhi="+"'"+"支出"+"'"+" "+"AND"+" "+"name="+"'"+m.ZbName+"'";
	    	}
	    	
	    }
	    if(t!=0)
	    {
	    	sql="SELECT * FROM zbdata WHERE fenlei="+"'"+s2+"'"+" "+"AND"+" "+"riqi BETWEEN"+" "+i+" "+"AND"+" "+j+" "+"AND"+" "+"name="+"'"+m.ZbName+"'";
	    }
				
		
		
		try{
			Statement stmt=con.createStatement();
			ResultSet rs=stmt.executeQuery(sql);

			    for(int k=0;k<100;k++)
				{
					for(int l=0;l<6;l++)
					{
						m.n[k][l]=null;
					}
				}
				while(rs.next())
				{
					m.n[count][0]=String.valueOf(x);	    
		    	    m.n[count][1]=String.valueOf(rs.getInt(2));
		    	    m.n[count][2]=String.valueOf(rs.getInt(3));
		    	    m.n[count][3]=rs.getString(4);
		    	    m.n[count][4]=rs.getString(5);
		    	    m.n[count][5]=rs.getString(6);
		    	   
		    	    x++;
		    	    count++;
				}
		}catch(SQLException e){
			System.err.println("SQLException:"+e.getMessage());
		}
	}
		
	
	
}