数据库原理：作业 05
实验五：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;
}
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;
}
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;
}
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;
}
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;
}
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;
}
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();
}
}
}
}
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();
}
}
}
}
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();
}
}
}
}
本文是原创文章，采用 CC BY-NC-ND 4.0 协议，完整转载请注明来自 Owen
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果