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

Source: Internet
Author: User
Tags sql injection stringbuffer

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.)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.