设为首页收藏本站
开启辅助访问

数据库原理：作业 05

您所在的位置：网站首页 › 查询每个出版社的图书数量的网站 › 数据库原理：作业 05

数据库原理：作业 05

2024-07-12 10:04| 来源: 网络整理| 查看: 265

实验五：JDBC进阶（1） 一、相关知识点 JDBC基本概念 JDBC简单查询、连接查询、嵌套查询、集函数查询等 二、实验目的：

理解Statement对象、ResultSet对象。

三、实验内容：

1、在booklib工程的BookManager类中增加如下函数（要求采用Statement完成相关查询），并在main函数中进行测试，在实验报告中将代码补上:

public int getBookCount(String pubid) throws BaseException{ //要求返回该出版社的图书数量 } public int getPublisherCount() throws BaseException{ //要求返回图书表中出现过的出版社数量 } public int getNoneBookPublisherCount()throws BaseException{ //要求返回没有图书的出版社数量 } public double getBookAvgPrice()throws BaseException{ //要求返回图书的评价价格 } public int getBookCount(String pubid) throws BaseException{ //要求返回该出版社的图书数量 int bookSum = 0; Connection conn = null; try { conn = DBUtil.getConnection(); String sql = "select * from beanpublisher where pubid = ?"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, pubid); java.sql.ResultSet rs = pst.executeQuery(); if (!rs.next()) { throw new BusinessException("出版社不存在"); } else { sql = "select count(1) from beanbook where pubid = ?"; pst = conn.prepareStatement(sql); pst.setString(1, pubid); rs = pst.executeQuery(); rs.next(); bookSum = rs.getInt(1); } rs.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return bookSum; }

image-20220506145306364

image-20220506145356841

public int getPublisherCount() throws BaseException{ //要求返回图书表中出现过的出版社数量 int publishersSum = 0; Connection conn = null; try { conn = DBUtil.getConnection(); String sql = "select beanbook.pubid from beanbook, beanpublisher where beanbook.pubid = beanpublisher.pubid group by beanbook.pubid;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); java.sql.ResultSet rs = pst.executeQuery(); while (rs.next()) { publishersSum++; } rs.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return publishersSum; }

image-20220506145523277

public int getNoneBookPublisherCount()throws BaseException { //要求返回没有图书的出版社数量 int publishersSum = 0; Connection conn = null; try { conn = DBUtil.getConnection(); String sql = "select * from beanpublisher;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); java.sql.ResultSet rs = pst.executeQuery(); while (rs.next()) { publishersSum++; } publishersSum -= getPublisherCount(); rs.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return publishersSum; }

image-20220506145553618

public double getBookAvgPrice()throws BaseException{ //要求返回图书的平均价格 double bookAvgPrice = 0; int bookNumber = 0; Connection conn = null; try { conn = DBUtil.getConnection(); String sql = "select price from beanbook;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); java.sql.ResultSet rs = pst.executeQuery(); while (rs.next()) { bookAvgPrice += rs.getDouble(1); bookNumber++; } bookAvgPrice /= bookNumber; rs.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return bookAvgPrice; }

image-20220506145632012

2、在booklib工程的BookLendManager类中增加如下函数，并在main函数中进行测试，在实验报告中将代码补上:

public String loadBookLendOperator(String barcode)throws BaseException{ //参数为图书条码，返回这本图书最近一次被借出时的操作员姓名，要求采用连接查询实现。难点：如何识别出最近一次？假设不允许用mysql的limit关键字，也不能用嵌套查询，应该如何完成？ } public String loadBookLendOperator(String barcode)throws BaseException{ //参数为图书条码，返回这本图书最近一次被借出时的操作员姓名，要求采用连接查询实现。 // 难点：如何识别出最近一次?假设不允许用mysql的limit关键字，也不能用嵌套查询，应该如何完成? String operatorName = ""; Connection conn = null; try { conn = DBUtil.getConnection(); String sql = "select * from beanbook where barcode = ?;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, barcode); java.sql.ResultSet rs = pst.executeQuery(); if (!rs.next()) { throw new BusinessException("该书不存在"); } else { sql = "select lendOperUserid, lendDate from beanbooklendrecord where bookBarcode = ? order by lendDate desc;"; pst = conn.prepareStatement(sql); pst.setString(1, barcode); rs = pst.executeQuery(); if (!rs.next()) { throw new BusinessException("该书没有借阅记录"); } else { operatorName = rs.getString(1); } } rs.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return operatorName; }

image-20220506150425527

image-20220506145921003

3、完成题2中的功能，要求采用嵌套查询实现。

public String loadBookLendOperator(String barcode)throws BaseException{ //参数为图书条码，返回这本图书最近一次被借出时的操作员姓名，要求采用连接查询实现。 // 难点：如何识别出最近一次?假设不允许用mysql的limit关键字，也不能用嵌套查询，应该如何完成? String operatorName = ""; Connection conn = null; try { conn = DBUtil.getConnection(); String sql = "select * from beanbook where barcode = ?;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, barcode); java.sql.ResultSet rs = pst.executeQuery(); if (!rs.next()) { throw new BusinessException("该书不存在"); } else { sql = "select lendOperUserid from beanbooklendrecord where id = (select id from beanbooklendrecord where bookBarcode = ? order by lendDate desc);"; pst = conn.prepareStatement(sql); pst.setString(1, barcode); rs = pst.executeQuery(); if (!rs.next()) { throw new BusinessException("该书没有借阅记录"); } else { operatorName = rs.getString(1); } } rs.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } return operatorName; }

image-20220507142725986

4、在booklib工程的BookLendManager类中增加如下函数，并在main函数中进行测试，在实验报告中将代码补上:

public void showAllLendRecord(){ //通过System.out.println方法，输出所有借阅记录的明细数据，要求结果中包括读者姓名、图书名称、所属出版社名称、借阅操作员姓名、归还操作员姓名、借阅时间、归还时间等 //注意：需要注意未归还图书的情况 } public void showAllLendRecord(){ //通过System.out.println方法，输出所有借阅记录的明细数据，要求结果中包括读者姓名、图书名称、所属出版社名称、借阅操作员姓名、归还操作员姓名、借阅时间、归还时间等 //注意：需要注意未归还图书的情况 Connection conn = null; String readerId, bookBarcode, pubid; String readerName = null, bookName = null, publisherName = null, lendOperUserid, returnOperUserid; String lendDate, returnDate; try { conn = DBUtil.getConnection(); String sql = "select * from beanbooklendrecord;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); java.sql.ResultSet rs = pst.executeQuery(); while (rs.next()) { readerId = rs.getString(2); bookBarcode = rs.getString(3); lendOperUserid = rs.getString(6); returnOperUserid = rs.getString(7); lendDate = rs.getString(4); returnDate = rs.getString(5); String sqlReader = "select * from beanreader where readerid = ?;"; java.sql.PreparedStatement pstReader = conn.prepareStatement(sqlReader); pstReader.setString(1, readerId); java.sql.ResultSet rsReader = pstReader.executeQuery(); if (rsReader.next()) { readerName = rsReader.getString(2); } pstReader.close(); rsReader.close(); String sqlBook = "select * from beanbook where barcode = ?;"; java.sql.PreparedStatement pstBook = conn.prepareStatement(sqlBook); pstBook.setString(1, bookBarcode); java.sql.ResultSet rsBook = pstBook.executeQuery(); if(rsBook.next()) { bookName = rsBook.getString(2); pubid = rsBook.getString(3); String sqlPublisher = "select * from beanpublisher where pubid = ?;"; java.sql.PreparedStatement pstPublisher = conn.prepareStatement(sqlPublisher); pstPublisher.setString(1, pubid); java.sql.ResultSet rsPublisher = pstPublisher.executeQuery(); if (rsPublisher.next()) { publisherName = rsPublisher.getString(2); } pstPublisher.close(); rsPublisher.close(); } pstBook.close(); rsBook.close(); System.out.println("读者姓名:" + readerName + "\t图书名称:" + bookName + "\t所属出版社名称:" + publisherName + "\t借阅操作员姓名:" + lendOperUserid + "\t归还操作员姓名:" + returnOperUserid + "\t借阅时间:" + lendDate + "\t归还时间:" + returnDate); } rs.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }

image-20220507160507462

5、在booklib工程的BookManager类中增加如下函数，并在main函数中进行测试，在实验报告中将代码补上:

public void showTop5Books(){ //通过System.out.println方法，输出借阅次数最多的5本图书及其借阅次数 } public void showTop5Publisher(){ //通过System.out.println方法，输出被借阅图书次数最多的5个出版名称及其总借阅次数和被借阅过的图书次数 } public void showTop5Books(){ //通过System.out.println方法，输出借阅次数最多的5本图书及其借阅次数 System.out.println("借阅次数最多的5本图书\t借阅次数"); Connection conn = null; try { conn = DBUtil.getConnection(); String sql = "select bookname, count(1) 'c' from beanbooklendrecord, beanbook where barcode = bookBarcode group by bookname order by c DESC limit 5;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); java.sql.ResultSet rs = pst.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1) + " " + rs.getString(2)); } } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } } public void showTop5Publisher(){ //通过System.out.println方法，输出被借阅图书次数最多的5个出版名称及其总借阅次数和被借阅过的图书次数 System.out.println("借阅次数最多的5个出版名称\t借阅次数"); Connection conn = null; try { conn = DBUtil.getConnection(); String sql = "select publisherName, count(1) 'c' from beanbooklendrecord, beanbook, beanpublisher where bookBarcode = barcode && beanbook.pubid = beanpublisher.pubid group by publisherName order by c DESC limit 5;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); java.sql.ResultSet rs = pst.executeQuery(); while (rs.next()) { System.out.println(rs.getString(1) + " " + rs.getString(2)); } } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }

image-20220507162815883

6、在BookLendManager中增加函数public void printDateLendRecord(String date)DbException，并在main函数中调用测试;要求通过该函数输出指定日期的所有借阅记录，，输出格式如下：

readerId=***,bookBarcode=****,lendDate=2020-05-01 15:17:01,returnDate=未归还 readerId=***,bookBarcode=****,lendDate=2020-05-01 15:17:01,returnDate=2020-05-12 12:00:00

说明：每个借阅记录1行输出，如果returnDate为空，则输出：“未归还”

注：时间的输出格式请使用java.text.SimpleDateFormat类实现

public void printDateLendRecord(String date)throws DbException { Connection conn = null; String readerid, bookBarcode; SimpleDateFormat returnDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");; try { conn = DBUtil.getConnection(); String sql = "select readerid, bookBarcode, lendDate, returnDate from beanbooklendrecord where lendDate = ?;"; java.sql.PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, date); java.sql.ResultSet rs = pst.executeQuery(); while (rs.next()) { readerid = rs.getString(1); bookBarcode = rs.getString(2); Date rd = rs.getDate(4); if (rd == null) { System.out.println("readerId="+readerid+",bookBarcode="+bookBarcode+",lendDate="+date+",returnDate=未归还"); } else { System.out.println("readerId="+readerid+",bookBarcode="+bookBarcode+",lendDate="+date+",returnDate="+returnDate.format(rd)); } } rs.close(); pst.close(); } catch (SQLException e) { e.printStackTrace(); throw new DbException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }

image-20220507164428449



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3