数据库系统应用与开发_实验七

实验 JDBC进阶（3）、相关知识点1、JDBC基本概念2、主从关系，分页查询、实验目的：理解分页查询的概念和处理方法、实验内容:数据准备：用上次实验中的方法，生成1000个读者和图书；1、改造读者模块，在提取读者的同时，提取其未归还的图书信息第一步：通过程序增加一些借阅纪录第二步：改造读者javabean，使之包括借阅的图书信息第三步：改造读者提取方法，同时提取未归还图书；第四步：修改图书借阅ui类并进行测试实验结果与分析】A、javabean 类代码。private ListvBea nBookbea nBookpublic List getBeanBook() retur n bea nBookpublic void setBeanBook(List beanBook) this .beanBook = beanBook;F Jjr.LLli -1L3L epvhXLK eJL*rj* 血fLP.adarprirri*i4dMT iAiLe1-iA4- StcIilq ceflpjefllarepP-dirri4 liM fp-rlFdl uA 3 rufSekLiiu ctadrp-ElrMe W.e/ IflfillH1 tFdrf rt IS4UJ丿径 隽对 酣piva4 SirLMIf tl1 tTlvMeBi&uiBDc4血BookJgt-t&MnE dH.111护Y*|d|車翻血口和口鼻“待鼻耐比* |i4ilP说j I aHJtea2:rjl_ _ _HlwAbe Si:clp feRerTTtefJge卩璋I 存傭三陀血亡fTtf班中住右衰:屯二存當ee堆l|亡:卜程阡列亡；Typtldk* prifalJir Lt LM dvlfevBiEld j| Iratu*1hi. iriW r iirq rvw b i-l VUfclJI-E idr)public- 7aLh4II | 1!Lv rvadviNinJLillie fil flEtwrierJIare r?ii lap EeKlerfluMei th| , raMFVF* - r*4*rN9FPJB、给出改造后ReaderManager类的方法代码List result仁new ArrayList();Stri ng sql1= select a.barcode,a.book name,a.pubid,a.price,a.state from+rs.getStri ng(1)+ andbea nbook a ,bea nbookle ndrecord b where b.readerid=b.bookBarcode=a.barcode and b.returnDate is nulljava.sql.PreparedStatement pst1=conn.prepareStatement(sql1); java.sql.ResultSet rs1=pst1.executeQuery();while (rs1.next()BeanBook b= new BeanBook();b.setBarcode(rs1.getStri ng(1);b.setBook name(rs1.getStri ng(2);b.setPubid(rs1.getStri ng(3);b.setPrice(rs1.getFloat(4);b.setState(rs1.getStri ng(5); resultl.add(b);r.setBea nBook(resultl);result.add(r);JJMFh.EMhEaUtU J WW.J*l *2#todiUM J MH|ftExd.iiq 砂E-a-Kiac 3d.i IV-ia-wd I .i4|i-E La-a IL L5TrEBuuiiwn! lrr4fLlJC-cB4riF,ede-r | i fCaBOMdlDa EocaiHadJ. rIT ccM-DBVtil -a3ir I n$ pqfc*5* I r r*dr、*L *w. ra-sK*! T|p*TH. f “*凉hML innv Mi 3*1, *r ni1*. *piif 1# ff理)#：L3Tk B lAnfaHilji r E-i：Ii3PhhkSi fTtph fe. vhEra e .XMBdiETTpB ld=x . rtHBdflsP/y-Bld + 4u” rei*sl,e ie null *jix |Leaot：Tvj?ei.aaiiAui i ； l 亡白 疋1 下耳丫亡亡IdE Ilfcai-raEd 1 nil 1 6 .mqw.lv |：cnyRr=xd| |flaJ* 44 佰gciEi* XikK- T f rcwierKw ls%r ?)*j flqiiL1* orcfcer fepWEfeHdrm i4L11k nkHTmcd k*U. c a*- CfU*.XH |Juywuii| | ir址rE*r r rhAi , ii-4 | _ IWtIxBM.II trki 1 “ 皿 el Here 4 H tt*ww lesaJijEadtc q ? Jf .：b嘯iirvr11Y ) re 3tc hMrierJbue i ： gtxBr i Log i：S ijt 釘乳9u je .JiBELdnill口口Is-an&TtBd (n -qk Ins 41 | 7KI ft tM-E.bM.4- I 釘 SUiE ./atECreM Dcne-Tld(E9. Bef| ji j匕.口片iti?r BvriEior|4iiF 3d | rr. t*t5v-r a iq i：Qre .sec hMKtefTTpeJSstie :rs,*1藏匸1闻 |9| 11lrrL.!l H-JSer iDfli j-i 21 liw r h . basoziSBj &_bc!a3EiKn h：. piir: dj .ffxcHj . rc-KTia f rmr hHKrJiE3 口 k， a Hnh EHsk LBidfacasd t MtuREn -.fb adar ad -i-cx.gH jffVB-m frerKM-KaEPfiiiit ust电pti eS?*tenr 190.1 u jjvb . 9q,|. fteaujTjiM E9iv3n: i.eM*m:euiierF(l vlUlv I xaL - Mxt j I I1&MrJ：Dk -PHM Be&bel Jb . MKHr S4t IE! L S ItlJ-C I Hq l | I Ib_ micBdnlcLiAn jea 1. o*a_tE ing 21 jh-trriri jrva.尸匸 F 4jb-I E . VW L5L1! 3 阵(3)r hwdHBh ihm-uLv L) jtfeiuli ildCEjF1 ：*!：* t2 = readers .get(i).getBeanBook();String imf=;if(t2!= null )for (int j=0;jki Tb|iD6r4i fl J E-eB*EIt5WUUiJpRftyli | ii|a4a.L斤 Pf 411 Dfr S M E Fpr JhiaLh dTiMbJ.4 X4&dBrTafciln*nn JTj Ladl科加屏冒华ieM袒Itur iIM lt*lLd.尹已累 IrMMIMllllEtei 11 fini xt Id!; 1皿円| I Se :? H-f ft irwr_i li -.rJP- wdri: % ”尸.gcdT:TB 11 - r. aVi.EjE-a ainPH adHrlpn Et LUjt. EHBriHKTn-aflaipnaEXi . at | ejmo):1/ irt1 nuU | r Ed-f i 9tvP4*au7q*A E411 aJ/jii aaMit-E IIfetv.K*sflr! | Id； tr 1 IM L*iJ L*iiMejf9,iIM-CJj i*-p| i rbc t l| Q(|rreafie-c?.典* CL| . Peajie-ridi) j tb aiih I i| lj FiKilfl -2已 C LIBin i |i 丿Eb*wnlidBrTriweTBdRrT|!lpfl4lHp id.vvfra4f-Pqis|l| :e1tbC4I| |irTbO4CBi aJ *c-n*i_iiije-T.e-fiderTbTwflare i；| v晦II加环 hl )|*rt4fiSU iLth4LLiM.tC44ll rri,itb 3Iris T lE t ：I.* tl * imEtet-CI. HLF11 惟-null|Ctr i,la.t jDr.vLxji Q 3-+IIixr*ijini升菲 禺 -+M. 0c Ij 1 ecBucoie，、-t-1 + 世imouhA*亡 11 +* 严 1日啊盘 “z llj -“讣凶biaO卢ilS 4-2.ivi. |j).|beIhtm() +-* 卜禹L*i2 .审u fi $wkzr.bi| i Ihtta STt FT ltd ?1：at Uic-itaDemVwcoE tfc-.J-sccfcTi片ies jihilH.TibJ*-l L k taE | 1Ihi e. FHihdarTliH- =pur匸 11 ;puU. c FE*rfBiH Kkq ah | IT=m fr Ste b 口口，X, dci：Li民取时美別馆电I 別ic* I皀Ftldl Bl L MB* kufl：o-tiw-r&r ed oM*h. ibLgc kvekk I 擁nLu-i；BeBftM4UeLlypejMiii j测试:#Wittfj M| K*1 wmJ 帀凹XMlEiHRrf*11ina I nc、聒 f-Rirr fi-B 1 Q 叶尺 r.Bhiii事书& i trtrrSii i和怙111事耳已出jiHD“1 1LI;kfMBWHD11 rl|l* 1iat-i-、一、11Jt-頁*1.Fl：rHlrf：( riJ rTTTGt T I T -4 tXF正Tlr3lFinSB暫TJi iHJTJI 晋！ l MH mm2、改造读者管理模块，将读者列表页面改造成分页查询方式第一步：第二步：自行设计PageData类，用于存放分页数据第三步：改造ReaderManager类，将其中的查询读者方法改造成分页查询。第四步：修改ui类，增加上一页、下一页按钮，头现读者的分页查询，要求每页20人实验结果与分析】A. PageData 类代码。public class PageData private int totalRecordCountprivate int pageCount ;private int pagesize;private int pageRecordCountprivate ListvBeanReader beanReader;用set与get创建方法也de 1jj-e- M网皆tFie- Ed*Rffifcno 出“。,叶 品irch 申:庐才 Rur- 轉心叶 阳专业资料.ihicluqe KTiBdu.ZMcc.ibocAhb,詔 imMhA1.屮 mdu-suELbaEiLib (B tMlWMIApjM R Ma. Ji曲皿応 P*feC1b|4*|T it冊肛刊暑3凸苦4 M J! Ei0aldUiHuc|M.i)rm 蕾V叶知笙 d.=! 1 d rrVln aqf SyfteraUiehMnfgRiJrZ阳殴0甸4Q frv-iHlwbbLdbcbnlfmri Bfe4J 5 rM dJ Bjarri iiLaw ip al Z 弼kfl应叩*LMIzJ is ie 4 h r R h4pmiH cn edu zirf b)o4ibi 丄 F-FiBDokLmfLwarEhjfln Q F祁*Lt坤kmj和*puf guF，译兀岂轻| pcradt* ant ta pWiEt inc AtCduMi, plin jCA inC gwjQ曲 7牛 祖cifltJiCuurt. 戸士打”电bi!朋piHicanrt gTcfelRacDniCdMnn LpuHirmid rfcT34alRc:cnflCDLnrta-ri tatRKcnlCn-ri1:) rr-iJ|G rimiCgieCKii_ni冋顷 widpfuKw*!)(paoeCunf - pageCfMrrt;puWirit|retvm psgssn;交Otriftw云匚 p*fiMor* 戸* bfrBftRfrlidi!* I* SHTorAfO c却 Gwl seP*aizeiTlAintasta-b耳eP忙占ilWa*旳片呂T 于解心QMbvyiMKB. 给出改造后ReaderManager类的方法代码public PageData searchReaderPage(String keyword, int readerTypeld,PageData page) throws BaseException/加一个page类Connection conn= null;try conn=DBUtil. getConnection ();Stri ng sql= select coun t(*) from Bea nReader r,Bea nReaderType rt where r.readerTypeld=rt.readerTypeld +and removeDate is null ;/ 这个sql是为了记录总记录数java.sql.PreparedStateme nt pst=co nn .prepareStateme nt(sql);java.sql.ResultSet rs=pst.executeQuery();if (rs.n ext()page.setTotalRecordCount(rs.getInt(1);/ 赋值总记录数page.setPageCo un t(page.getTotalRecordCo un t()/page.getPagesize();/赋值总页面数List result仁 new ArrayListvBeanReader();String sql仁 select top +page.getPagesize()+ readerid,readerName,r.readerTypeld,ren dBookLimitted,createDate,creatorU serld,stopDate,stopUserld,rt.readerTypeName + from Bea nReader r,Bea nReaderType rt wherer.readerTypeld=rt.readerTypeld +and removeDate is nu II ;if (page.getPageRecordCo un t()!=1)sql1+= and readerid not in (select top+(page.getPageRecordCo un t()-1)*(page.getPagesize()+ readerid fromBea nReader where removeDate is n ull);if (readerTypeId0) sql1+= and r.readerTypeId= +readerTypeId;if (keyword!= null & ! .equals(keyword)sql1+= and (readerid like ? or readerName like ?);sql1+= order by readerid ;java.sql.PreparedStateme nt pst1=co nn .prepareStateme nt(sqll);if (keyword!= null & ! .equals(keyword)pstl.setString(1, % +keyword+ %);pstl.setString(2, % +keyword+ %);java.sql.ResultSet rs1=pst1.executeQuery();while (rs1.next()Bea nReader r= new Bea nReader();r.setReaderid(rs1.getStri ng(1); r.setReaderName(rs1.getStri ng(2); r.setReaderTypeld(rs1.getl nt(3);r.setLe ndBookLimitted(rs1.getl nt(4);r.setCreateDate(rs1.getDate(5);r.setCreatorUserld(rs1.getStri ng( 6);r.setStopDate(rs1.getDate(7);r.setStopUserld(rs1.getStri ng(8);r.setReaderTypeName(rs1.getStri ng(9);List result2= new ArrayList();Stri ng sql2= selecta. barcode,a.book name,a.pubid,a.price,a.state from bea nbooka ,bea nbookle ndrecord b where b.readerid=+rs1.getStri ng(1)+ andb. bookBarcode=a.barcode and b.retur nDate is n ull;java.sql.PreparedStateme ntpst2=c onn .prepareStateme nt(sql2);java.sql.ResultSet rs2=pst2.executeQuery();while (rs2.next()Bea nBook b= new Bea nBook();b.setBarcode(rs2.getStri ng(1);b.setBook name(rs2.getStri ng(2);b.setPubid(rs2.getStri ng(3);b.setPrice(rs2.getFloat )；b.setState(rs2.getStri ng( 5); result2.add(b);r.setBea nBook(result2);resultl.add(r);page.setBea nReader(resultl); catch (SQLExcepti on e) e.pri ntStackTrace();throw new DbException(e);fin ally if (conn!= null)try conn. close(); catch (SQLException e) / TODO Auto-generated catch blocke.pri ntStackTrace(); return page;给出ui类中的修改部分（注：生成表格的方法需做微调）1 .首先增加2个按钮即上一页，和下一页需要修改3个地方第一个是：private Butt on previous = new Butt on(” 上页);private Butt on n ext = new Butt on(下页);如图:pjt cn.du.fucc.tnooklib.ui!* impcrta awt.Bordervayouc；rpublic class FrniRe ad erMana ge r extend s J Dial eg implemcntsArtiofi Listener 1 piivjlu JPrinul LuoIBdi t im*w JPdii elO;private Bdltcn btnAdd - new Ekittcnf ；岸力丁)；private Bjiton Dtnfvod = new BuTtonffet)：RjtLun btii5J.-i nyw Bu：Lu(壮工7private Button btnKsuie - new Button(IL J?private Briton DtnOelete - newpivju JCchnboBcixcmLiF亠心屮卜 pt-riull；private MapStrin,BeanReadetTyrBadetTpeMap_name-nBHf HdshMdp5tringRDed RedderTypep(); private Map readerTypiMap_id=riewHashMaplntegerJBeanF(;private Button previo-us 二 npw Rli-a -( _行!：i; privzt住甘 jttemi next = nww tjutt门厂 F第二个是:toolBar .add( previous );toolBar .add(next);如图:5trTpasD=UJ;for (int iUj itjp*M.、i如()；*j readerlypeVap_name.put(typeiget(i).gtt -.caderTyp亡fdrr电typei.getii汕： re ade rTp&Ma p_id .put。占 st Reade rTpe ld()r lypa.g at (i);5:rlypes. +2=ty=ei get(i).get :ej cer ypeNamef 1;1ctrioKe?dertype=r?w J(. omboEoxftrl pe引；J catch BsseExceDtion ci) /TOrtC A.4c-genErated catch bicrkeLprintilaclcTrac&);Itoolbar spU ayoiFtfprw Flowl ayQiitfFlow ay out f f 7)； to ol Bar.ad dfbtn Add1-ODlBflr add(htnMQnify);to Dl6a r. a d dfbtn Stop);toclBav ridd(bt nRaute) tooBar add(thi s. btnDe I etc. toalBakaddmbRjdartyp);to alBar.addfedt Keyword).to ol Bw r, m 日 d(b m 弘 srt h);|to olBsr.ad d(previu$);第三个是：增加监听器this .previous .addActionListener( this);this .next .addActionListener( this);tlus.reloddT TEA);thi5.Stiiz?(Koc, tootdouble width = Toolk t.eDefauitTaoZfcftO.getScrenSi26 J.getWidth(); double h&iqhiC =Tcolkit.).geScrecnSize.J.qeHsicht1：；; Lhit ietLocat on (inn i； w dlh -thijqmA/ dth( /耳(I nt) (Ihsigh t - tliis.g etH eiqh tQ) /1)this validate);thh btnAdd addAtionl Etvnerfltik) this =trMod VaddctionLienertthis). this slrStop.pddnH onUtcnGrtthiisi this. DtrRcucra dd Act ioLirt one r(th i s)- thk bti Dfilfltfi addActiocLiitttraarfthis), llik L?ti 5社谢 Ji ddew ButJ;privaf* Button hrnStop - n w Button*ip rivaR Button b:n Reuse new Bynon(勒)；prButton btnDalfltff = naw Button刖闘戏右 J;ip rvvaf MspcString, 0es n R?aderT ype 产亡占占便T ypeMm e-nw Ha? h MapSt ringr 0-eariRea();(U r Hiil 赴 MapclintegrrB eaiR 定 ide rTy pe re a dwriypeMdp.id ri 仑轴 Haih M 日 pdntixjerType(j-prvvat* JCombcBox rm b RMd*d yo p - nyII;prrvnr* Button p忧wiciM nrw Butronf 上一岚H;prvvw Button下一页龙private1 JTetField edtKeyvwd -iwv* JTextFieldf 10);private Button btnSdrch 二 new ButtonpfWprwateObjsatbiT tied斗谕者还寻慕昇丁ISf预5TJ检；净I护图尊ilMJ;ip rivat Ohjct tb I Dat r 口口；PgeDau page-niew PageDataQ；Lii&t readers=null;Defaiu hTablp Mod el tab I rr Ddsrww Defa u lta bk Mod 巴 h；i;p rirwdt 电Tm tj I qe m d eTm bljnra bb(前 blnxW ；p a ge -et Pa geRerordC u吕竝的好是尋一帀paq 已.set Pa qes i zef 2Q)J7li( JCrt-iP.-S AS二jh ii - _i t_ l. i _ rif有些值在ReaderManager类的searchReaderPage方法中已经赋值了如图：Cannection cann=null;tfylconn- BUtil.etConneTticrnl);Strirg sqI-select count(*) from BeanReader iB已anReaderTypert where i.readerTypeldrt.readerTypeld* + andremoveDaU is null;人说卜旳是记录杳佰見远录教ia cl.PreparecStat已Ferrt nst=q-r.preparsS?at亍Fhntfsqljfvd sc I-Re suits st rs-pst cxe c uteQLe ry();ifirEnextO)page s#lTeta I R c ordCourl (n. gstl nt； 11); /馆暄总记录救pmgePageCnunt(psg* gptTot3RpcorclCoL.nrQ/pagA g*tParj?ii7pO);曲就但息药面灯3. 对刷新table的方法进行修改readers = (newReaderManager().searchReaderPage( this .edtKeyword .getText(),rtId, page).getBeanReader();注意需要在：BeanReaderType类中加private PageData page;变量 顺便加 下set与get的方法。不然BeanReaderTypert= this .readerTypeMap_name .get(rtname);这句话会冒错说 searchReaderPage 方法有3个形参而readerType什么只有2个形参不行我也不是很懂pr vpiiltry Eint n=Thk.crnj;iritrtld=o;String rtrLamG=thiG.cmbReadsrtypGetSelectedlt9nni().taStririg(;BeanRaaderT/pe ft =th is.ra=电 rTp-IV a 二 _nanm m e);irt=null) rtld=rt. qetReaderTypeId I；弍npwciparchRparipFrj(lhi*; pdfKfwnrti rjrTpk7( rf d, QPtRpAnS-newO jecTpeaderE1)page .setPageRecordCount( page .getPageRecordCount()-1); this .reloadTable();else if(e.getSource()= this .next)if (page .getPageRecordCount() page .getTotalRecordCount() page .setPageRecordCount( page .getPageRecordCount()+1); this .reloadTable();如图：nryiInpwKparierMJ.Rsdlfr(广*a!p，rgtRpadrid)!弓yHpeLKrManaiqft.Jr.g#t1U5rid()j thkHlsdTablftO;riiTrh aKEKceptiori ei)JC ptionFane.siowMtssaQeOiralgi null. i-getX*能科就),启耳rJ Opti qn* - wWRBX MCSSA Gt);1TiT沁iMe，qetSmj常卽)=-址晶.矶昭 色。忆竹)|rhh np rfpag*1 getPagpReo0rdGui,it)page gctTotlBrcmJCoupdq. setFgeR ecord 匚 du n tp9&.PgR lord Goun t(+i);this rel3adT3bl-:()-测试:第一页：| 土、坯*|脱1輕昊1他1酬睑機着 1国 I尸页| T-5 I昔确秫未归卞酹信見rl:制11亲形陽：1书名门书产rHO11n-R尹刑冯：ID书宕协书HOO拱吉认11止詞rTccm宜苦WL11ikArid曲讪11IMi旳211正itn(i311正益rifl4.iS10411rr忻11nos令佃11i=BH0711止巨ITOB510511rroa.赂価11111SB1111ri1DHiio11ri115S11111rf12311211iT宜ms11正材rii4nlli11正草n 15:躺11寸厂1正苴当按下下一页后当按下上一页后.专业资料.