Summary of book Management system--database manipulation (II.)

Here, as an example of the most basic DML statements, other complex DML statements can be obtained from these basic JDBC statements.

First, inquiry form

This is an example of a two-table association query, which uses dynamic methods to connect the corresponding conditions behind the where statement according to the input criteria.

/*** Book Information Enquiry *@paramCon *@paramBook *@return     * @throwsException*/     PublicResultSet List (Connection con,book book)throwsexception{stringbuffer SB=NewStringBuffer ("select * from T_book b,t_booktype bt where B.booktypeid=bt.id"); //Two table related queries, Booktypeid to query        if(Stringutil.isnotempty (Book.getbookname ())) {Sb.append ("and b.bookname like '%" +book.getbookname () + "% '"); }//Add query conditions dynamically if(Stringutil.isnotempty (Book.getauthor ())) {Sb.append ("and b.author like '%" +book.getauthor () + "% '"); }        if(Book.getbooktypeid ()! =NULL&& Book.getbooktypeid ()!=-1)//"Please select" The ID is-1{sb.append ("and b.booktypeid=" +Book.getbooktypeid ()); } PreparedStatement pstmt=con.preparestatement (sb.tostring ()); returnPstmt.executequery ();//Execute, return result set}

The returned resultset is a result set, such as a drop-down box that can be initialized. The result set is used by a cursor to bridge the set with a record:

ResultSet rs=booktypedao.list (Con,NewBookType ());//get the book kind result set//Add first item Id=-1Booktype=NewBookType (); Booktype.setbooktypename ("Please select ..."); Booktype.setid (-1);//Please select an ID of-1             This. Booktypejcb.additem (BookType);  while(Rs.next ()) {//RS must first next () once, point to the first recordBooktype=NewBookType (); Booktype.setid (Rs.getint ("id")); Booktype.setbooktypename (Rs.getstring ("Booktypename"));  This. Booktypejcb.additem (BookType);//Add Item

Second, data insertion

To prevent SQL injection, a precompiled approach is used here.

    /*** Book Add *@paramCon *@paramBook *@return     * @throwsException*/     Public intAdd (Connection con,book book)throwsException//return affects several records{String SQL= "INSERT into T_book values (null,?,?,?,?,?,?,?,?)";        //Note corresponds to the table properties that you created PreparedStatement pstmt=con.preparestatement (SQL);//create the object, get the SQL statement, and first feed the parameter statement into the DBMS to prevent SQL injectionPstmt.setstring (1, Book.getbookname ());//pass parameters, fill in the value of the question markPstmt.setstring (2, Book.getauthor ()); Pstmt.setstring (3, Book.getsex ()); Pstmt.setfloat (4, Book.getprice ()); Pstmt.setint (5, Book.getbooktypeid ()); Pstmt.setstring (6, Book.getbookdesc ()); Pstmt.setint (7, Book.getbooknum ()); Pstmt.setstring (8, Book.getpublisher ()); returnPstmt.executeupdate ();//executes the SQL statement, returning the number of records affected}

Third, data modification:

/*** Book Information modification *@paramCon *@paramBook *@return     * @throwsException*/     Public intUpdate (Connection Con,book book)throwsException {String SQL= "Update t_book set bookname=?,author=?,sex=?,price=?,bookdesc=?,booktypeid=?" where id=? ";        //sql Modifying statements PreparedStatement pstmt=con.preparestatement (SQL); Pstmt.setstring (1, Book.getbookname ()); Pstmt.setstring (2, Book.getauthor ()); Pstmt.setstring (3, Book.getsex ()); Pstmt.setfloat (4, Book.getprice ()); Pstmt.setstring (5, Book.getbookdesc ()); Pstmt.setint (6, Book.getbooktypeid ()); Pstmt.setint (7, Book.getid ()); returnpstmt.executeupdate (); //Return value is the number of paths affected }

Iv. Data deletion:

    /*** Book Information deletion *@paramCon *@paramID *@return     * @throwsException*/     Public intDelete (Connection con,intIdthrowsException {String SQL= "Delete from T_book where id=?"; PreparedStatement pstmt=con.preparestatement (SQL); Pstmt.setint (1, id); returnPstmt.executeupdate ();//Return value is the number of deleted routes    }    

Summary of book Management system--database manipulation (II.)