admininterface.java

资产管理系统的数据库设计 （一）开发背景 随着我国高等教育事业的迅猛发展

            .add(brwBookPanelLayout.createSequentialGroup()
                .add(brwBookPanelLayout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
                    .add(brwBookPanelLayout.createSequentialGroup()
                        .add(106, 106, 106)
                        .add(brwBookPanelLayout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING, false)
                            .add(brwBookPanelLayout.createSequentialGroup()
                                .add(jLabel19)
                                .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                                .add(BookID, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 176, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                            .add(jPanel4, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                            .add(brwBookPanelLayout.createSequentialGroup()
                                .add(jLabel18)
                                .add(13, 13, 13)
                                .add(UserID, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 129, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                            .add(jPanel1, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)))
                    .add(brwBookPanelLayout.createSequentialGroup()
                        .add(222, 222, 222)
                        .add(AckBorrow)))
                .addContainerGap(340, Short.MAX_VALUE))
        );
        brwBookPanelLayout.setVerticalGroup(
            brwBookPanelLayout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(brwBookPanelLayout.createSequentialGroup()
                .add(30, 30, 30)
                .add(brwBookPanelLayout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
                    .add(jLabel18)
                    .add(UserID, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                .add(jPanel1, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                .add(15, 15, 15)
                .add(brwBookPanelLayout.createParallelGroup(org.jdesktop.layout.GroupLayout.BASELINE)
                    .add(jLabel19)
                    .add(BookID, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(org.jdesktop.layout.LayoutStyle.RELATED)
                .add(jPanel4, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, org.jdesktop.layout.GroupLayout.DEFAULT_SIZE, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                .add(41, 41, 41)
                .add(AckBorrow)
                .addContainerGap(68, Short.MAX_VALUE))
        );
        operatePane.addTab("\u501f\u4e66", brwBookPanel);

        operateMenu.setText("\u64cd\u4f5c");
        queryBook.setText("\u67e5\u8be2\u56fe\u4e66");
        operateMenu.add(queryBook);

        insertBook.setText("\u63d2\u5165\u56fe\u4e66");
        operateMenu.add(insertBook);

        regCard.setText("\u6ce8\u518c\u501f\u4e66\u5361");
        operateMenu.add(regCard);

        exit.setText("\u9000\u51fa");
        exit.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                exitActionPerformed(evt);
            }
        });

        operateMenu.add(exit);

        MB.add(operateMenu);

        helpMenu.setText("\u5e2e\u52a9");
        help.setText("\u663e\u793a\u5e2e\u52a9");
        help.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                helpActionPerformed(evt);
            }
        });

        helpMenu.add(help);

        about.setText("\u5173\u4e8e");
        about.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                aboutActionPerformed(evt);
            }
        });

        helpMenu.add(about);

        MB.add(helpMenu);

        setJMenuBar(MB);

        org.jdesktop.layout.GroupLayout layout = new org.jdesktop.layout.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(layout.createSequentialGroup()
                .addContainerGap()
                .add(operatePane, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 733, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(36, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(org.jdesktop.layout.GroupLayout.LEADING)
            .add(layout.createSequentialGroup()
                .add(operatePane, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE, 478, org.jdesktop.layout.GroupLayout.PREFERRED_SIZE)
                .addContainerGap(35, Short.MAX_VALUE))
        );
        pack();
    }// </editor-fold>//GEN-END:initComponents
    
    private void bookIDActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bookIDActionPerformed
// TODO 将在此处添加您的处理代码：
        this.connect();
        try{
            con=DriverManager.getConnection(conURL);
            PreparedStatement ps=con.prepareStatement("select BookName,Author,PublishCompany,Price from BookInfo where BookInfo.ISBN=(select ISBN from LibBook where BookID=?)");
            PreparedStatement pt=con.prepareStatement("select UserID,UserName,company from UserInfo where UserID=(select UserID from BorrowInfo where BookID=?)");
            String bookid=bookID.getText().toString();
            ps.setString(1,bookid);
            pt.setString(1,bookid);
            ResultSet rs;
            rs=ps.executeQuery();
            while(rs.next()){
                bookName.setText(rs.getString(1));
                author.setText(rs.getString(2));
                publish.setText(rs.getString(3));
                price.setText(new Float(rs.getFloat(4)).toString());
            }
            ps.close();
            rs=pt.executeQuery();
            while(rs.next()){
                userID.setText(rs.getString(1));
                userName.setText(rs.getString(2));
                Institute.setText(rs.getString(3));
            }
            pt.close();
            con.close();
        }catch(SQLException ex){}
    }//GEN-LAST:event_bookIDActionPerformed
    
    private void BookIDActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_BookIDActionPerformed
// TODO 将在此处添加您的处理代码：
        this.connect();
        try{
            con=DriverManager.getConnection(conURL);
            PreparedStatement ps=con.prepareStatement("select BookName,Author,PublishCompany from BookInfo where ISBN=(select ISBN from LibBook where BookID=?)");
            String s=BookID.getText().toString();
            ps.setString(1,s);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                BookName.setText(rs.getString(1));
                Author.setText(rs.getString(2));
                Publish.setText(rs.getString(3));
            }
            ps.close();
            con.close();
        }catch(SQLException ex){}
    }//GEN-LAST:event_BookIDActionPerformed
    
    private void UserIDActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_UserIDActionPerformed
// TODO 将在此处添加您的处理代码：
        this.connect();
        try{
            con=DriverManager.getConnection(conURL);
            PreparedStatement ps=con.prepareStatement("select UserName,company from UserInfo where UserID=?");
            String s=UserID.getText().toString();
            ps.setString(1,s);
            ResultSet rs=ps.executeQuery();
            while(rs.next()){
                UserName.setText(rs.getString(1));
                UserInstitute.setText(rs.getString(2));
            }
            ps.close();
            con.close();
        }catch(SQLException ex){}
    }//GEN-LAST:event_UserIDActionPerformed
    
    private void bookIDKeyReleased(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_bookIDKeyReleased
// TODO 回车后显示信息：
//        String JDriver = "sun.jdbc.odbc.JdbcOdbcDriver";
//        String conURL = "jdbc:odbc:pejuwe";
//         try {
//             Class.forName(JDriver);
//              } catch (ClassNotFoundException e) {
//             System.out.println("forname:" + e.getMessage());
//        }
//         try {
//             Connection con = DriverManager.getConnection(conURL);
//             Statement s = con.createStatement();
//          if(evt.getKeyCode()==10){
//             String bookid = bookID.getText().toString();
//             String bookname = bookName.getText().toString();
//             String authors = author.getText().toString();
//             String publisher=publish.getText().toString();
//             String prices=price.getText().toString();
//             String userid=userID.getText().toString();
//             String username=userName.getText().toString();
//             String institute=Institute.getText().toString();
//             String pastday=pastDay.getText().toString();
//             String sel="select BookID,BookName,Author,PublishCompany,Price," +
//                     "UserID,UserName,company,Remain FROM " +
//                     "UserInfo,BorrowInfo,LibBook,BookInfo " +
//                     "WHERE BookID='"+bookID+"'and LibBook.ISBN= BookInfo.ISBN" +
//                     "and BorrowInfo.BookID=LibBook.BookID " +
//                     "and BorrowInfo.UserID=UserInfo.UserID";
//             System.out.println(sel);
//             ResultSet opt=s.executeQuery(sel);
//             if(opt.next()){
//                 bookName.setText(opt.getString("BookName"));
//                 author.setText(opt.getString("Author"));
//                 publish.setText(opt.getString("PublishCompany"));
//                 price.setText(opt.getString("Price"));
//                 userID.setText(opt.getString("UserID"));
//                 userName.setText(opt.getString("UserName"));
//                 Institute.setText(opt.getString("company"));
//                 pastDay.setText(opt.getString("Remain"));
//             }
//          }
//         }catch (SQLException ex) {
//             System.out.println(ex.getMessage());
//             JOptionPane.showMessageDialog(this,"系统正忙，请稍候！");
//
//        }
    }//GEN-LAST:event_bookIDKeyReleased
    
    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed
// TODO 还书：
        this.connect();
        try {
            Connection con = DriverManager.getConnection(conURL);
            PreparedStatement ps=con.prepareStatement("update BookInfo set Remain=? where ISBN=(select ISBN from LibBook where BookID=?)");
            PreparedStatement pt=con.prepareStatement("select Remain from BookInfo where ISBN=(select ISBN from LibBook where BookID=?)");
            String bookid=bookID.getText().toString();
            pt.setString(1,bookid);
            int amout=0;
            ResultSet rs=pt.executeQuery();
            while(rs.next()){
                amout=rs.getInt(1);
            }  
            amout=amout+1;
            ps.setInt(1,amout);
            System.out.println(amout);
            ps.setString(2,bookid);
            boolean rsdel=ps.execute();
            if(!rsdel){
                JOptionPane.showMessageDialog(this,"还书成功！");
                bookID.setText(null);
                bookName.setText(null);