數(shù)據(jù)庫(kù)系統(tǒng)應(yīng)用與開(kāi)發(fā)實(shí)驗(yàn)六

1、實(shí)驗(yàn) JDBC進(jìn)階（2）一、相關(guān)知識(shí)點(diǎn)1、JDBC基本概念2、視圖、索引二、實(shí)驗(yàn)?zāi)康模豪斫庖晥D和索引的概念，并通過(guò)視圖設(shè)計(jì)簡(jiǎn)化程序設(shè)計(jì)，通過(guò)索引設(shè)計(jì)優(yōu)化查詢性能三、實(shí)驗(yàn)內(nèi)容：1、 設(shè)計(jì)讀者視圖view_reader，并修改readerManager類中相關(guān)代碼。第一步：通過(guò)查詢分析器建立讀者視圖，要求視圖中包含讀者類別名稱；第二步：改造ReaderManager類，將其中的連接查詢用視圖代替。第三步：運(yùn)行圖書(shū)管理系統(tǒng)，進(jìn)行各個(gè)功能的測(cè)試（讀者類別管理、讀者管理）【實(shí)驗(yàn)結(jié)果與分析】A、 寫出視圖創(chuàng)建代碼。create view view_reader asselect readerid,read

2、erName,r.readerTypeId,r.lendBookLimitted,createDate,creatorUserId,stopDate,stopUserId,rt.readerTypeName,r.removeDate from BeanReader r,BeanReaderType rt where r.readerTypeId=rt.readerTypeIdB、 給出改造后ReaderManager類的各個(gè)方法的代碼。String sql="select *" +" from view_reader where removeDate is nul

3、l " +" and removeDate is null "String sql="select *" +" from view_reader where readerid=?"2、 設(shè)計(jì)圖書(shū)視圖view_book，并修改BookManager類中相關(guān)代碼。第一步：通過(guò)查詢分析器建立圖書(shū)視圖，要求視圖中包含出版社名稱；第二步：改造BookManager類，將其中的連接查詢用視圖代替。第三步：運(yùn)行圖書(shū)管理系統(tǒng)，進(jìn)行各個(gè)功能的測(cè)試【實(shí)驗(yàn)結(jié)果與分析】A、 寫出視圖創(chuàng)建代碼。create view view_bookasselec

4、t b.barcode,b.bookname,b.pubid,b.price,b.state,p.publishername from beanbook b,beanpublisher p where b.pubid=p.pubidB、 給出改造后ReaderManager類的各個(gè)方法的代碼。public class BookManager public List<BeanBook> searchBook(String keyword,String bookState)throws BaseExceptionList<BeanBook> result=new Array

5、List<BeanBook>();Connection conn=null;try conn=DBUtil.getConnection();/改動(dòng)處String sql="select *" +" from view_book where state='"+bookState+"' "/if(keyword!=null && !"".equals(keyword)sql+=" and (b.bookname like ? or b.barcode like ?)

6、"sql+=" order by b.barcode"java.sql.PreparedStatement pst=conn.prepareStatement(sql);if(keyword!=null && !"".equals(keyword)pst.setString(1, "%"+keyword+"%");pst.setString(2, "%"+keyword+"%");java.sql.ResultSet rs=pst.executeQu

7、ery();while(rs.next()BeanBook b=new BeanBook();b.setBarcode(rs.getString(1);b.setBookname(rs.getString(2);b.setPubid(rs.getString(3);b.setPrice(rs.getDouble(4);b.setState(rs.getString(5);b.setPubName(rs.getString(6);result.add(b); catch (SQLException e) e.printStackTrace();throw new DbException(e);f

8、inallyif(conn!=null)try conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();return result;public BeanBook loadBook(String barcode) throws DbException Connection conn=null;try conn=DBUtil.getConnection();/改動(dòng)處String sql="select *" +" from view_book

9、where barcode=? "/java.sql.PreparedStatement pst=conn.prepareStatement(sql);pst.setString(1,barcode);java.sql.ResultSet rs=pst.executeQuery();if(rs.next()BeanBook b=new BeanBook();b.setBarcode(rs.getString(1);b.setBookname(rs.getString(2);b.setPubid(rs.getString(3);b.setPrice(rs.getDouble(4);b.

10、setState(rs.getString(5);b.setPubName(rs.getString(6);return b; catch (SQLException e) e.printStackTrace();throw new DbException(e);finallyif(conn!=null)try conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();return null;3、 設(shè)計(jì)讀者借閱情況統(tǒng)計(jì)視圖view_reader_static，并在BookL

11、endManager類中添加根據(jù)讀者ID提取其借閱數(shù)量的代碼。第一步：通過(guò)查詢分析器建立讀者統(tǒng)計(jì)視圖，要求視圖中包含讀者ID、讀者姓名、借閱數(shù)量；第二步：在BookLendManager中添加方法 public int loadReaderLendCount(String readerid) throws DbException。并編寫其代碼第三步：在BookLendManager類中添加main函數(shù)，并編寫上述方法的測(cè)試代碼。進(jìn)行功能的測(cè)試【實(shí)驗(yàn)結(jié)果與分析】A、 寫出視圖創(chuàng)建代碼。create view view_reader_staticasselect r.readerid,r.read

12、erName,count(*) AS借閱數(shù)量 from BeanReader r,BeanBookLendRecord rcwhere r.readerid=rc.readerid group by r.readerid,r.readerNameB、 給出改造后BookLendManager類的各個(gè)方法的代碼。public static int loadReaderLendCount(String readerid) throws DbExceptionConnection conn=null;int a=0;try conn=DBUtil.getConnection();String sql

13、="select * from view_reader_static where readerid=?"java.sql.PreparedStatement pst=conn.prepareStatement(sql);pst.setString(1, readerid);java.sql.ResultSet rs=pst.executeQuery();if(rs.next()a=rs.getInt(3); catch (SQLException e) e.printStackTrace();throw new DbException(e);finallyif(conn!=null)try conn.close(); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();return a;4、 索引實(shí)驗(yàn)：第一步：完成IndexTest_initDat