bookadd_condb.java~11~

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

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

public class bookadd_condb {
    public bookadd_condb() {
        try {
            jbInit();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    static FrmBook fm;
    static Connection con;
    static Statement st;
    static ResultSet rs;
    public bookadd_condb(FrmBook f) {
        fm=f;
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException ex) {
            JOptionPane.showMessageDialog(fm,"找不到驱动","error",JOptionPane.ERROR_MESSAGE);
        }
        try
        {
            con = DriverManager.getConnection("jdbc:odbc:book");
            //JOptionPane.showMessageDialog(fm,"连接成功","OK",JOptionPane.INFORMATION_MESSAGE);
            st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

        }
        catch (SQLException ex1)
        {
            JOptionPane.showMessageDialog(fm,"连接失败","error",JOptionPane.ERROR_MESSAGE);
        }
    }
    static void initSearch()
    {
        try {
            rs = st.executeQuery("select top 5 booknumber as 图书编号,name as 书名,author as 作者,price as 价格,publish as 出版社,adddate as 入库日期,isexist as 是否在库 from bookinfo");
        } catch (SQLException ex) {
        }
    }

    /**
     * 数据库中的数据显示在表格
     * @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);
    }
   }


   /**
    * 图书入库
    * @param BookNumber String
    * @param Name String
    * @param Author String
    * @param Price int
    * @param Publish String
    * @param AddDate String
    * @param IsExist int
    */

   static void BookAdd(String BookNumber,String Name,String Author,int Price,String Publish,String AddDate,int IsExist)
   {
       String str="insert into bookinfo values('"+BookNumber+"','"+Name+"','"+Author+"',"+Price;
       str=str+",'"+Publish+"','"+AddDate+"',"+IsExist+")";
    try
    {
        st.executeUpdate(str);
        JOptionPane.showMessageDialog(fm,"添加成功","OK",JOptionPane.INFORMATION_MESSAGE);
    } catch (SQLException ex)
    {
        JOptionPane.showMessageDialog(fm,"添加出错","error",JOptionPane.ERROR_MESSAGE);
    }
   }
   /**
    * 图书报损
    * @param BookNumber String
    */
   static void BookDelete(String BookNumber)
   {
       String str="delete from bookinfo where booknumber='"+BookNumber+"'";
    try {
        ResultSet rstemp=st.executeQuery("select * from bookinfo where booknumber='"+BookNumber+"'");
        int i=0;
        while(rstemp.next())
        {
            i++;
        }
        if(i==0)
        {
            JOptionPane.showMessageDialog(fm,"没有这本书","提示",JOptionPane.INFORMATION_MESSAGE);
        }
        else
        {
            st.executeUpdate(str);
            JOptionPane.showMessageDialog(fm, "成功报损", "OK",
                                          JOptionPane.INFORMATION_MESSAGE);
        }
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"报损时有误","error",JOptionPane.ERROR_MESSAGE);
    }
   }

  static ResultSet SearchToModify(String BookName,String Author,String Publish)
   {
       ResultSet rs2=null;
       String str="select * from bookinfo where ";
       if(!BookName.equals(""))
       {
           str=str+"name='"+BookName+"'";
       }
       if(!Author.equals(""))
       {
           if(!BookName.equals(""))
           {
               str=str+" and author='"+Author+"'";
           }
           else
           {
               str=str+"author='"+Author+"'";
           }
       }
       if(!Publish.equals(""))
       {
           if(!BookName.equals("") || !Author.equals(""))
           {
               str=str+" and publish='"+Publish+"'";
           }
           else
           {
               str=str+" publish='"+Publish+"'";
           }
       }
       try{
           rs2 = st.executeQuery(str);
       }catch (SQLException ex) {
           JOptionPane.showMessageDialog(fm,"没有找到","提示",JOptionPane.INFORMATION_MESSAGE);
    }
       return rs2;
   }

   static ArrayList ShowModifyData(String str)
   {
       ArrayList arr=new ArrayList();
       String str1="select * from bookinfo where booknumber='"+str+"'";
    try {
        ResultSet rsMod = st.executeQuery(str1);
        rsMod.next();
            for(int i=1;i<=rsMod.getMetaData().getColumnCount();i++)
            {
                arr.add(rsMod.getString(i));
            }
    } catch (SQLException ex) {
        JOptionPane.showMessageDialog(fm,"没有找到这本书","error",JOptionPane.ERROR_MESSAGE);
    }
    return arr;
   }

   static void Modify(String BookNumber,String Number,String Name,String Author,int Price,String Publish,String AddDate,int IsExist)
   {
       String str="update bookinfo set booknumber='"+Number+"',name='"+Name+"',author='"+Author+"',price="+Price;
       str=str+",publish='"+Publish+"',adddate='"+AddDate+"',isexist="+IsExist+" where booknumber='"+BookNumber+"'";
    try {
        st.executeUpdate(str);
        JOptionPane.showMessageDialog(fm,"成功修改","OK",JOptionPane.INFORMATION_MESSAGE);
    } catch (SQLException ex)
    {
        JOptionPane.showMessageDialog(fm,"修改出错","error",JOptionPane.ERROR_MESSAGE);
    }
   }

   /**
    * 关闭数据库
    */
   void closeConnection()
   {
    try {
        rs.close();
        st.close();
        con.close();
    } catch (SQLException ex) {
    }
   }



     /************************************借书*******************************/

     static boolean IsHaveBook(String BookNumber)
     {
         boolean b=false;
         String str="select * from bookinfo where booknumber='"+BookNumber+"'";
        try {
            ResultSet rsHave = st.executeQuery(str);
            rsHave.next();
           int i=rsHave.getInt(7);
            if(i==0)
            {
                JOptionPane.showMessageDialog(fm,"此书已借出","Sorry",JOptionPane.INFORMATION_MESSAGE);
            }
            else
            {
                b=true;
            }
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(fm,"没有这本书","Sorry",JOptionPane.INFORMATION_MESSAGE);
        }
        return b;
     }

     static void SetZero(String BookId)
     {
         String str="update bookinfo set isexist=0 where booknumber='"+BookId+"'";
        try {
            st.executeUpdate(str);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(fm,"更新失败","Sorry",JOptionPane.INFORMATION_MESSAGE);
        }
     }

     static void SetOne(String BookId)
     {
         String str="update bookinfo set isexist=1 where booknumber='"+BookId+"'";
        try {
            st.executeUpdate(str);
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(fm,"更新失败","Sorry",JOptionPane.INFORMATION_MESSAGE);
        }
     }


     /*******************************查询****************************************/

     /**
      * 按书名精确查询
      * @param BookName String
      * @return ResultSet
      */
     static ResultSet JingName(String BookName)
     {
         ResultSet rsjn=null;
        try {
            rsjn = st.executeQuery("select booknumber as 图书编号,name as 书名,author as 作者,price as 价格,publish as 出版社,adddate as 入库日期,isexist as 是否在库 from bookinfo where name ='" + BookName + "'");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(fm,"查询出错","error",JOptionPane.ERROR_MESSAGE);
        }
        return rsjn;
    }

    /**
     * 按书名模糊查询
     * @param BookName String
     * @return ResultSet
     */

    static ResultSet MoName(String BookName)
     {
         ResultSet rsjn=null;
        try {
            rsjn = st.executeQuery("select booknumber as 图书编号,name as 书名,author as 作者,price as 价格,publish as 出版社,adddate as 入库日期,isexist as 是否在库 from bookinfo where name like '%" + BookName + "%'");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(fm,"查询出错","error",JOptionPane.ERROR_MESSAGE);
        }
        return rsjn;
    }

    /**
     * 按作者查询
     */

    static ResultSet SearchAuthor(String Author)
    {
        ResultSet rsa=null;
        try {
            rsa = st.executeQuery("select booknumber as 图书编号,name as 书名,author as 作者,price as 价格,publish as 出版社,adddate as 入库日期,isexist as 是否在库 from bookinfo where author='" +Author + "'");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(fm,"查询时出错","error",JOptionPane.ERROR_MESSAGE);
        }
        return rsa;
    }

    /**
     * 按出版社查询
     */

    static ResultSet SearchPublish(String Publish)
    {
        ResultSet rsp=null;
        try {
            rsp = st.executeQuery("select booknumber as 图书编号,name as 书名,author as 作者,price as 价格,publish as 出版社,adddate as 入库日期,isexist as 是否在库 from bookinfo where publish='" +Publish + "'");
        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(fm,"查询时出错","error",JOptionPane.ERROR_MESSAGE);
        }
        return rsp;
    }


    /***********************分页显示******************************************/

    static ResultSet splitPage(int page)
    {
        ResultSet rsPage=null;
        String str="select top 5 booknumber as 图书编号,name as 书名,author as 作者,price as 价格,publish as 出版社,adddate as 入库日期,isexist as 是否在库 from bookinfo where booknumber not in (select top "+page*5+" booknumber from bookinfo)";
        try {
            rsPage = st.executeQuery(str);
        } catch (SQLException ex) {
        }
        return rsPage;
    }

    /**
     * 返回图书信息表有多少条5的倍数的记录
     */
    static int TotalRecord()
    {
        int count=0;
        try {
            ResultSet rstotal = st.executeQuery("select * from bookinfo");
            while(rstotal.next())
            {
                count++;
            }
            if(count%5==0)
            {
                count=count/5-1;
            }
            else
            {
                count=count/5;
            }

        } catch (SQLException ex) {
        }
        return count;
    }

    private void jbInit() throws Exception {
    }
}