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