clslendnumber_condb.java~3~

这是我在北大青鸟的第一学期的一个毕业设计

import java.sql.*;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;
import javax.swing.JTable;
import java.util.Vector;
import java.util.ArrayList;

public class clsLendNumber_condb {
    static Connection conLN;
    static Statement stLN;
    static ResultSet rsLN;
    static FrmBook fm;
    public clsLendNumber_condb(FrmBook f) {
        fm=f;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException ex) {
            JOptionPane.showMessageDialog(fm,"找不到驱动","error",JOptionPane.ERROR_MESSAGE);
        }
        try {
            conLN = DriverManager.getConnection("jdbc:odbc:book");
            stLN=conLN.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

        } catch (SQLException ex1) {
            JOptionPane.showMessageDialog(fm,"连接失败","error",JOptionPane.ERROR_MESSAGE);
        }
    }

    static void initSearch()
    {
        try {
            rsLN=stLN.executeQuery("select top 5 lendnumber as 借书证号,name as 姓名,totallend as 可借数量,alreadylend as 已借数量 from lendnumberinfo");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(fm,"连接失败","error",JOptionPane.ERROR_MESSAGE);
        }
    }

    /**
     * 数据库中的数据显示在表格
     * @param tab JTable
     */
   static void initInfo(JTable tab,ResultSet rsparam)
   {

       DefaultTableModel dt;
       Vector vt=new Vector();
       Vector vtTemp;
    try
    {
        while (rsparam.next())
        {
            vtTemp=new Vector();
            for (int i = 1; i <= rsparam.getMetaData().getColumnCount(); i++)
            {
                vtTemp.add(rsparam.getString(i));
            }
            vt.add(vtTemp);
        }
        rsparam.last();
        Vector vtColName=new Vector();
        for(int i=1;i<=rsparam.getMetaData().getColumnCount();i++)
        {
            vtColName.add(rsparam.getMetaData().getColumnName(i));
        }

        dt=new DefaultTableModel(vt,vtColName);
        tab.setModel(dt);
    }
    catch (SQLException ex)
    {
        JOptionPane.showMessageDialog(fm,"有误","error",JOptionPane.ERROR_MESSAGE);
    }
   }

   static void CardAdd(String CardId,String Name,int TotalLend,int AreadyLend)
   {
       String str="insert into lendnumberinfo values('"+CardId+"','"+Name+"',"+TotalLend+","+AreadyLend+")";
    try {
        stLN.executeUpdate(str);
        JOptionPane.showMessageDialog(fm,"成功开证","OK",JOptionPane.INFORMATION_MESSAGE);
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"开证失败","error",JOptionPane.ERROR_MESSAGE);
    }
   }

   /**
    * 销证
    */
   static void CardDelete(String CardId)
   {
       int i=0;
       String str="delete from lendnumberinfo where lendnumber='"+CardId+"'";

       try {
       ResultSet rsCD=stLN.executeQuery("select * from lendnumberinfo where lendnumber='"+CardId+"'");
       while(rsCD.next())
       {
           i++;
       }
       if(i==0)
       {
           JOptionPane.showMessageDialog(fm,"没有这个借书证","提示",JOptionPane.INFORMATION_MESSAGE);
       }
       else
       {
           stLN.executeUpdate(str);
           JOptionPane.showMessageDialog(fm, "成功销证", "OK",
                                         JOptionPane.INFORMATION_MESSAGE);
       }
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"销证失败","error",JOptionPane.ERROR_MESSAGE);
    }
   }


   static ArrayList ModifyData(String CardId)
   {
       ArrayList arr=new ArrayList();
       String str="select * from lendnumberinfo where lendnumber='"+CardId+"'";
    try {
        ResultSet rsMD = stLN.executeQuery(str);
        rsMD.next();
        for(int i=1;i<=rsMD.getMetaData().getColumnCount();i++)
        {
            arr.add(rsMD.getString(i));
        }
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"没有找到借书证","oo",JOptionPane.INFORMATION_MESSAGE);
    }
    return arr;
   }

   static void CardModify(String ModiCardId,String LendNumber,String Name,int TotalLend,int AlreadyLend)
   {
       String str="update lendnumberinfo set lendnumber='"+LendNumber+"',name='"+Name+"'";
       str=str+",totallend="+TotalLend+",alreadylend="+AlreadyLend+" where lendnumber='"+ModiCardId+"'";
    try {
        stLN.executeUpdate(str);
        JOptionPane.showMessageDialog(fm,"成功更新","OK",JOptionPane.INFORMATION_MESSAGE);
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"更新失败","提示",JOptionPane.ERROR_MESSAGE);
    }
   }

   static ResultSet SearchCard(String CardId,String Name)
   {
       int i=0;
       ResultSet rsSearch=null;
       String str="select * from lendnumberinfo where ";
       if(!CardId.equals(""))
       {
           str=str+"lendnumber='"+CardId+"'";
       }
       if(!Name.equals(""))
       {
           if(!CardId.equals(""))
           {
               str=str+" and name='"+Name+"'";
           }
           else
           {
               str=str+"name='"+Name+"'";
           }
       }

    try {
        rsSearch = stLN.executeQuery(str);
        while(rsSearch.next())
        {
            i++;
        }
        if(i==0)
        {
            JOptionPane.showMessageDialog(fm,"没有这个借书证","提示信息",JOptionPane.INFORMATION_MESSAGE);
        }

    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"请输入查询条件","提示",JOptionPane.INFORMATION_MESSAGE);
    }
    return rsSearch;
   }

   /************************************借书*******************************/
   static boolean IsCanLend(String CardId)
   {
      boolean b=false;
       int j=0;
       int total=3,already=0;
       String str="select * from lendnumberinfo where lendnumber='"+CardId+"'";
    try {
        ResultSet rsCan = stLN.executeQuery(str);
        while(rsCan.next())
        {
            j++;
            total=rsCan.getInt(3);
            already=rsCan.getInt(4);
        }
        if(j!=0)
        {
            if(total<=already)
            {
                JOptionPane.showMessageDialog(fm,"此证已借满","Sorry",JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                b=true;
            }
        }
        else
        {
            JOptionPane.showMessageDialog(fm,"没有这个借书证","Sorry",JOptionPane.INFORMATION_MESSAGE);
        }
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"请输入证号","info",JOptionPane.INFORMATION_MESSAGE);
    }
    return b;
   }


   static void SetLendCount(String CardId)
   {
       String str="update lendnumberinfo set alreadylend=alreadylend+1 where lendnumber='"+CardId+"'";
    try {
        stLN.executeUpdate(str);
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"更新失败","Sorry",JOptionPane.INFORMATION_MESSAGE);
    }
   }


   static void SetReLendCount(String CardId)
  {
      String str="";
      try {
      ResultSet rsTem=stLN.executeQuery("select * from lendnumberinfo where lendnumber='"+CardId+"'");
      rsTem.next();
      int alr=rsTem.getInt(4);
      if(alr==0)
      {
          str="update lendnumberinfo set alreadylend=0 where lendnumber='"+CardId+"'";
      }
      else
      {
          str ="update lendnumberinfo set alreadylend=alreadylend-1 where lendnumber='" +CardId + "'";
      }
       stLN.executeUpdate(str);
   } catch (SQLException ex) {
       JOptionPane.showMessageDialog(fm,"更新失败","Sorry",JOptionPane.INFORMATION_MESSAGE);
   }
  }

}