bookmodel.java

图书管理

package library;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.*;
import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

import java.sql.SQLException;


public class BookModel {

    DB db = new DB();
    PreparedStatement pstmt1,pstmt2;
    CachedRowSet crs;
 //   Connection con;
    private String bookNum;
    private String bookName;
    private String press;
  //  private AuthorInfo author;
    private String authorNum;
    private String authorName;
    private String address;
    private String email;
    private String tel;
    
    public BookModel() {
    //      con = db.getConnection();
    }
    public static void main(String args[]) {
      //  AuthorInfo author = new AuthorInfo("3","aa","33","33","33");
      //  BookInfo book = new BookInfo("003","ww","3","2");
  
        BookModel bookModel = new BookModel();
      //  bookModel.IsInsertBook(book,author);
      // author.setAuthorNum("1");
        AuthorInfo author = new AuthorInfo();
        BookInfo book = new BookInfo();
        author.setAddress("shenyang");
        bookModel.IsUpdateBook(book,author);
     //   bookModel.IsDeleteAuthor(book,author);
      //  bookModel.Query();
      
    }
    public Boolean IsInsertAuthor(AuthorInfo author){
        authorNum = author.getAuthorNum();
        authorName = author.getAuthorName();
        address = author.getAddress();
        email = author.getEmail();
        tel = author.getTel();
        try{
            pstmt1 = db.getConnection().prepareStatement("insert into authorInfo(authorNum,authorName,address,email,tel) values(?,?,?,?,?)");
            pstmt1.setString(1,authorNum);
            pstmt1.setString(2,authorName);
            pstmt1.setString(3,address);
            pstmt1.setString(4,email);
            pstmt1.setString(5,tel);
            pstmt1.executeUpdate();
            pstmt1.close();
            return true;
        }catch(SQLException e){
            System.out.println("IsInsertAuthor"+e);
            return false;
        }
    }
    
    public Boolean IsInsertBook(BookInfo book,AuthorInfo author){
        
        bookNum = book.getBookNum();
        bookName = book.getBookName();
        authorNum = author.getAuthorNum();
        press = book.getPress();
        Boolean b = IsInsertAuthor(author);
        try{      
            if (b==true){
               pstmt2 = db.getConnection().prepareStatement("insert into bookInfo values(?,?,?,?)");
                pstmt2.setString(1,bookNum);
                pstmt2.setString(2,bookName);
                pstmt2.setString(3,authorNum);
                pstmt2.setString(4,press);
                pstmt2.executeUpdate();
                return true;
            }else{
                IsDeleteAuthor(book,author);
                return false;
            }
          
        }catch(SQLException e){
            System.out.println("IsInsert:"+e);
            try{
                pstmt1.close();
                pstmt2.close();
                db.getConnection().close();
            }catch(SQLException e1){
                System.out.println(e1);
            }
            return false;
        }
        
    }
    
    public Boolean IsDeleteAuthor(BookInfo book,AuthorInfo author){
        authorNum = author.getAuthorNum();
        Boolean b = IsDeleteBook(book);
        try{
            if(b==true){
                pstmt1 = db.getConnection().prepareStatement("delete from authorInfo where authorNum =?");
                pstmt1.setString(1,authorNum);
                pstmt1.executeUpdate();
                pstmt1.close();
                db.getConnection();
                return true;
           }else{
                IsDeleteBook(book);
                return false;
           }
             }catch(SQLException e){
                System.out.println("IsInsertBook:"+e);
                try{
                    pstmt1.close();
                    db.getConnection();
                }catch(SQLException e1){
                    System.out.println(e1);
                }
                return false;
         }
 }
        
    public Boolean IsDeleteBook(BookInfo book){
        bookNum = book.getBookNum();
        try{
            pstmt1 = db.getConnection().prepareStatement("delete from bookInfo where bookNum=?");
            pstmt1.setString(1,bookNum);
            pstmt1.executeUpdate();
            pstmt1.close();
            db.getConnection().close();
            return true;
        }catch(SQLException e){
            System.out.println("IsDeleteBook:"+e);
            try{
                pstmt1.close();
                db.getConnection().close();
            }catch(SQLException e1){
                System.out.println(e1);
            }
            return false;
            
        }
    }
    
    public Boolean IsUpdateAuthor(AuthorInfo author){
        authorNum = author.getAuthorNum();
        authorName = author.getAuthorName();
        address = author.getAddress();
        email = author.getEmail();
        tel = author.getTel();
        try{
            pstmt1 = db.getConnection().prepareStatement("update authorInfo set authorName=?,address=?,email=?,tel=? where authorNum='"+authorNum+"'");
            pstmt1.setString(1,authorName);
            pstmt1.setString(2,address);
            pstmt1.setString(3,email);
            pstmt1.setString(4,tel);
            //pstmt1.setString(5,authorNum);
            pstmt1.executeUpdate();
            pstmt1.close();
            db.getConnection().close();
            System.out.println("finish IsUpdateAuthor");
            return true;
        }catch(SQLException e){
            System.out.println("ISUpdateAuthor:"+e);
            try{
                pstmt1.close();
                db.getConnection().close();
            }catch(SQLException e1){
                System.out.println(e1);
            }
            return false;
        }
    }
    
    public Boolean IsUpdateBook(BookInfo book,AuthorInfo author){
        bookNum = book.getBookNum();
        bookName = book.getBookName();
       // authorNum = author.getAuthorNum();
        press = book.getPress();
        Boolean b = IsUpdateAuthor(author);
        try{
            if(b==true){
                pstmt1 = db.getConnection().prepareStatement("update bookInfo set bookName=?,press=? where bookNum=?");
                pstmt1.setString(1,bookName);
               // pstmt1.setString(2,authorNum);
                pstmt1.setString(2,press);
                pstmt1.setString(3,bookNum);
                pstmt1.executeUpdate();
                pstmt1.close();
                db.getConnection().close();
                System.out.println("finish IsUpdateBook");
                return true;
            }
            else{
                IsDeleteAuthor(book,author);
                return false;
            }
        }catch(SQLException e){
            System.out.println("IsUpdateBook:"+e);
            try{
                pstmt1.close();
                db.getConnection().close();
            }catch(SQLException e1){
                System.out.println("IsUpdateBook:"+e);
            }
            return false;
        }
    }
    public CachedRowSet Query(){
        return Query(null);
    }
    public CachedRowSet Query(String con){
        try{
            crs = new CachedRowSetImpl();
           
            if(con==null){
                 crs.setCommand("select bookNum,bookName,bookInfo.authorNum,press,authorName,address,email,tel from bookInfo , authorInfo "+
                               
                                "where bookInfo.authorNum = authorInfo.authorNum order by bookNum asc");  
            }else{
                crs.setCommand("select bookNum,bookName,bookInfo.authorNum,press,authorName,address,email,tel from bookInfo , authorInfo "+
                              
                              "where bookInfo.authorNum = authorInfo.authorNum and "+ con +" order by bookNum asc");  
            }
            crs.execute(db.getConnection());
            db.getConnection().close();
           /*while(crs.next()){
               bookNum = crs.getString(1);
               bookName = crs.getString(2);
               address = crs.getString(6);
               System.out.println(bookNum+"--"+bookName+"-"+address+("--"));
           }*/
            return crs;
            
        }catch(SQLException e){
            System.out.println("Query:"+e);
            try{
                db.getConnection().close();
            }catch(SQLException e1){
                System.out.println(e);
            }
            return null;
        }
    }
    public CachedRowSet QueryById(String bookNum,String authorNum){
        try{
            crs = new CachedRowSetImpl();
            crs.setCommand("select bookNum,bookName,bookInfo.authorNum,press,authorName,address,email,tel from bookInfo , authorInfo "+
                               
                           "where bookInfo.authorNum = authorInfo.authorNum and "+" bookNum= "+bookNum+" and "+" bookInfo.authorNum = "+authorNum );
            crs.execute(db.getConnection());
         /*   if(crs!=null){
                System.out.println("QueryId ");
                while(crs.next()){
                    System.out.println("QueryId "+crs.getString(1));
                }
            }*/
            db.getConnection().close();
           // System.out.println(crs);
            return crs;
        }catch(SQLException e){
            System.out.println("QueryById:"+e);
            try{
                db.getConnection().close();
                
            }catch(SQLException e1){
                System.out.println(e);
            }
            return null;
        }
    }
}