使用JSP+SERVLET+JDBC实现对数据库的增删改查

package dao; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Vector; import bean.Book; import bean.Lendbook; /** * 书籍DAO * @author 尹辉 * */ public class BookDAO { DBUtil dbutil=new DBUtil(); /** * 添加书籍 * @param book * @return * @throws SQLException */ public boolean addBook(Book book) throws SQLException{ boolean flag=false; //sql String sql="insert into book values(?,?,?,?,?,?,?,?,?)"; //参数 Object [] parmas=new Object[9]; parmas[0]=book.getBookname(); parmas[1]=book.getBookauthor(); parmas[2]=book.getBookpulish(); parmas[3]=book.getBookJsbn(); parmas[4]=book.getBookpages(); parmas[5]=book.getBookprice(); parmas[6]=book.getBooktype(); parmas[7]=book.getIscdrom(); parmas[8]=book.getStauts(); //调用dbutil flag=dbutil.execUpdate(sql, parmas); return flag; } /** * 查找 * @return * @throws SQLException */ public List<Book> findBook(Book book) throws SQLException{ List<Book> row=new ArrayList<Book>(); Vector<Vector> rows=new Vector<Vector>(); StringBuffer sql2=new StringBuffer(); // System.out.println("找出的类容：书名："+book.getBookname()+",作者："+book.getBookauthor()+",类型："+book.getBooktype()); if(!book.getBookname().equals("") && book.getBookname()!=null){ if(sql2.indexOf("where")>0){//返回指定字符在此字符串中第一次出现处的索引。 sql2.append(" and bookname='"+book.getBookname()+"'"); }else{ sql2.append(" where bookname='"+book.getBookname()+"'"); } } if(!book.getBookauthor().equals("") && book.getBookauthor()!=null){ if(sql2.indexOf("where")>0){ sql2.append(" and bookauthor='"+book.getBookauthor()+"'"); }else{ sql2.append(" where bookauthor='"+book.getBookauthor()+"'"); } } if(!book.getBooktype().equals("") && book.getBooktype()!=null){ if(sql2.indexOf("where")>0){ sql2.append(" and booktype='"+book.getBooktype()+"'"); }else{ sql2.append(" where booktype='"+book.getBooktype()+"'"); } } System.out.println("长度："+sql2.indexOf("where")); StringBuffer sql=new StringBuffer("select * from book "+sql2+" order by bookid desc " ); System.out.println("数据库查找："+sql); rows=dbutil.execQuery(sql.toString(), null, 10); for(Vector v:rows){ Book book2=new Book(); book2.setBookid(Integer.parseInt(v.get(0).toString())); book2.setBookname(v.get(1).toString()); book2.setBookauthor(v.get(2).toString()); book2.setBookpulish(v.get(3).toString()); book2.setBookJsbn(v.get(4).toString()); book2.setBookpages((Integer.parseInt(v.get(5).toString()))); book2.setBookprice(Double.parseDouble(v.get(6).toString())); book2.setBooktype(v.get(7).toString()); book2.setIscdrom(v.get(8).toString()); book2.setStauts(v.get(9).toString()); row.add(book2); } return row; } /** * 查找书籍，按ID * @param id 书籍的id号 * @return * @throws SQLException */ public Book bookID(int id) throws SQLException{ Book book=new Book(); String sql="select * from book where bookid=?"; Object[] params=new Object[1]; params[0]=id; //调用 Vector<Vector> rows=dbutil.execQuery(sql, params, 10); //填充实体 book.setBookid(id); book.setBookname(rows.get(0).get(1).toString()); book.setBookauthor(rows.get(0).get(2).toString()); book.setBookpulish(rows.get(0).get(3).toString()); book.setBookJsbn(rows.get(0).get(4).toString()); book.setBookpages(Integer.parseInt(rows.get(0).get(5).toString())); book.setBookprice(Double.parseDouble(rows.get(0).get(6).toString())); book.setBooktype(rows.get(0).get(7).toString()); book.setIscdrom(rows.get(0).get(8).toString()); book.setStauts(rows.get(0).get(9).toString()); return book; } /** * 修改书籍 * @param book * @return * @throws SQLException */ public boolean revaBook(Book book) throws SQLException{ boolean flag=false; String sql="update book set bookname=?,bookauthor=?,bookpulish=?,bookJsbn=?,bookpages=?,bookprice=?,booktype=?,iscdrom=?,stauts=? where bookid=?"; Object[] params=new Object[10]; params[0]=book.getBookname(); params[1]=book.getBookauthor(); params[2]=book.getBookpulish(); params[3]=book.getBookJsbn(); params[4]=book.getBookpages(); params[5]=book.getBookprice(); params[6]=book.getBooktype(); params[7]=book.getIscdrom(); params[8]=book.getStauts(); params[9]=book.getBookid(); flag=dbutil.execUpdate(sql, params); return flag; } /** * 删除书籍 * @param id * @return * @throws SQLException */ public boolean removeBook(int id) throws SQLException{ boolean flag=false; String sql="delete book where bookid="+id; flag=dbutil.execUpdate(sql, null); return flag; } public boolean xiugaiOp(Lendbook lendbook) throws SQLException{ boolean flag=false; String op=null; if(lendbook.getOp().equals("借书")){ op="借书"; } if(lendbook.getOp().equals("还书")){ op="未借"; } String sql="update book set stauts='"+op+"' where bookid="+lendbook.getBookid(); System.out.println("sql:"+sql); flag=dbutil.execUpdate(sql, null); return flag; } }