ZUCC数据库原理 第二部分开发 实验6

news/2024/11/29 10:58:26/

实验6  JDBC进阶（3）

一、相关知识点

        1、JDBC基本概念

        2、批处理

3、DAO和OR映射

二、实验目的：

        掌握批量SQL语句执行的方法，理解DAO和ORM的基本概念

三、实验内容：

  1. 利用批量SQL语句执行的方法实现实验5中的最后两个方法：
  1. 编写批量借阅读书函数： public void lendbooks(String readerId,Collection<String> barcodes) .... 。其中第二个参数为图书条码集合。

    public void lendbooks(String readerId,Collection<String> barcodes) throws SQLException, BaseException {

            SystemUserManager sum = new SystemUserManager();

            BeanSystemUser admin = sum.loadUser("admin");

            sum.currentUser = admin;

           

            ArrayList<String> list = new ArrayList(barcodes);

     

            Connection conn = null;

        conn = DBUtil.getConnection();

        String sql = "insert into BeanBookLendRecord(readerid,bookBarcode,lendDate,lendOperUserid,penalSum) values(?,?,?,?,0)";

        java.sql.PreparedStatement pst = conn.prepareStatement(sql);

        for(int i=0;i<list.size();i++) {

            pst.setString(1, readerId);

            pst.setString(2, list.get(i));

            pst.setTimestamp(3, new java.sql.Timestamp(System.currentTimeMillis()));

            pst.setString(4, SystemUserManager.currentUser.getUserid());

            pst.addBatch();

        }

        pst.executeBatch();

        String sql2 ="update BeanBook set state='已借出' where barcode=?";

        java.sql.PreparedStatement pst2 = conn.prepareStatement(sql2);

        for(int j=0;j<list.size();j++) {

                 pst2.setString(1, list.get(j));

                 pst2.addBatch();

        }

        pst2.executeBatch();

        conn.close();

    }

    public static void main(String[] args) throws BaseException {

                 BookManager bm = new BookManager();

                 List<String> bc = new ArrayList<>();

                 bc.add("B123");

                 bc.add("B124");

        try {

                 bm.lendbooks("r1", bc);

        } catch (SQLException e) {

                 // TODO Auto-generated catch block

                 e.printStackTrace();

        }

        System.out.println("success");

        System.out.println(bc.get(0));

        System.out.println(bc.size());

        }

B、编写批量设置罚金函数：public void setPenalSum(String readerId,Map<String,Double> penalSums) .... 。其中第二个参数的key为barcode，value为改读者尚未归还图书的罚金（注意，不要设置已经归还图书的罚金）。

                   public void setPenalSum(String readerId,Map<String,Double> penalSums){

              Set<Map.Entry<String, Double>> es = penalSums.entrySet();

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            String sql = "update beanbooklendrecord set penalSum = ? where readerid = ? and bookbarcode = ?";

            PreparedStatement ps = conn.prepareStatement(sql);

            for(Map.Entry<String, Double> entry:es) {

                    String key = entry.getKey();

                    Double value = entry.getValue();

                    ps.setDouble(1, value);

                    ps.setString(2, readerId);

                    ps.setString(3, key);

                    ps.addBatch();

            }

            ps.executeBatch();

        }

            catch (SQLException e) {

            throw new RuntimeException(e);

        }finally {

            if(conn!=null) {

                try {

                    conn.close();

                } catch (SQLException e) {

                    throw new RuntimeException(e);

                }

            }

        }

    }

    public static void main(String[] args) throws BaseException {

                 BookManager bm = new BookManager();            

                 Map<String,Double> hm = new HashMap<>();

                 hm.put("B123", 10.0);

                 hm.put("B124",11.0);

                 bm.setPenalSum("r1", hm);

                 System.out.println("success");

        }

  1. 模仿SystemUserDAO类，实现BookDAO类，并改造BookManager类，使之通过BookDAO操作数据库。

【实验结果与分析】

  1. 给出BookDAO类代码。

package cn.edu.zucc.booklib.control;

import cn.edu.zucc.booklib.model.BeanBook;

import cn.edu.zucc.booklib.model.BeanReader;

import cn.edu.zucc.booklib.model.BeanSystemUser;

import cn.edu.zucc.booklib.util.BaseException;

import cn.edu.zucc.booklib.util.BusinessException;

import cn.edu.zucc.booklib.util.DBUtil;

import cn.edu.zucc.booklib.util.DbException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Collection;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import java.util.Set;

public class BookDAO {

    public List<BeanBook> searchBook(Connection conn,String keyword, String bookState) throws BaseException {

        List<BeanBook> result = new ArrayList<BeanBook>();

        java.sql.PreparedStatement pst =null;

        java.sql.ResultSet rs = null;

        try {

            String sql = "select b.barcode,b.bookname,b.pubid,b.price,b.state,p.publishername " +

                    " from beanbook b left outer join beanpublisher p on (b.pubid=p.pubid)" +

                    " where  b.state='" + bookState + "' ";

            if (keyword != null && !"".equals(keyword))

                sql += " and (b.bookname like ? or b.barcode like ?)";

            sql += " order by b.barcode";

            pst = conn.prepareStatement(sql);

            if (keyword != null && !"".equals(keyword)) {

                pst.setString(1, "%" + keyword + "%");

                pst.setString(2, "%" + keyword + "%");

            }

            rs = pst.executeQuery();

            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);

                 }

                 finally{

                         if(rs!=null) {

                                  try {

                                          rs.close();

                                  } catch (SQLException e) {

                                  }

                         }

                         if(pst!=null) {

                                  try {

                                          pst.close();

                                  } catch (SQLException e) {

                                 }

                         }

                 }

        return result;

    }

    public void createBook(Connection conn,BeanBook b) throws BaseException {

            java.sql.PreparedStatement pst = null;

            java.sql.ResultSet rs = null;

        try {

            String sql = "select * from BeanBook where barcode=?";

            pst = conn.prepareStatement(sql);

            pst.setString(1, b.getBarcode());

            rs = pst.executeQuery();

            rs.close();

            pst.close();

            sql = "insert into BeanBook(barcode,bookname,pubid,price,state) values(?,?,?,?,'在库')";

            pst = conn.prepareStatement(sql);

            pst.setString(1, b.getBarcode());

            pst.setString(2, b.getBookname());

            pst.setString(3, b.getPubid());

            pst.setDouble(4, b.getPrice());

            pst.execute();

        }catch (SQLException e) {

                         e.printStackTrace();

                         throw new DbException(e);

                 }

                 finally{

                         if(rs!=null) {

                                  try {

                                          rs.close();

                                  } catch (SQLException e) {

                                  }

                         }

                         if(pst!=null) {

                                  try {

                                          pst.close();

                                  } catch (SQLException e) {

                                  }

                         }

                 }

    }

    public void modifyBook(Connection conn,BeanBook b) throws BaseException {

            java.sql.PreparedStatement pst = null;

            java.sql.ResultSet rs = null;

        try {

            String sql = "select * from BeanBook where barcode=?";

            pst = conn.prepareStatement(sql);

            pst.setString(1, b.getBarcode());

            java.sql.ResultSet rs = pst.executeQuery();

            rs.close();

            pst.close();

            sql = "update BeanBook set bookname=?,pubid=?,price=?,state=? where barcode=?";

            pst = conn.prepareStatement(sql);

            pst.setString(1, b.getBookname());

            pst.setString(2, b.getPubid());

            pst.setDouble(3, b.getPrice());

            pst.setString(4, b.getState());

            pst.setString(5, b.getBarcode());

            pst.execute();

        }catch (SQLException e) {

                         e.printStackTrace();

                         throw new DbException(e);

                 }

                 finally{

                         if(rs!=null) {

                                  try {

                                          rs.close();

                                  } catch (SQLException e) {

                                  }

                         }

                         if(pst!=null) {

                                  try {

                                          pst.close();

                                  } catch (SQLException e) {

                                  }

                         }

                 }

    }

    public BeanBook loadBook(Connection conn,String barcode) throws DbException {

            java.sql.PreparedStatement pst = null;

            java.sql.ResultSet rs = null;

        try {

            String sql = "select b.barcode,b.bookname,b.pubid,b.price,b.state,p.publishername " +

                    " from beanbook b left outer join beanpublisher p on (b.pubid=p.pubid)" +

                    " where  b.barcode=? ";

            pst = conn.prepareStatement(sql);

            pst.setString(1, barcode);

            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.setState(rs.getString(5));

                b.setPubName(rs.getString(6));

                return b;

            }

        }catch (SQLException e) {

                         e.printStackTrace();

                         throw new DbException(e);

                 }

                 finally{

                         if(rs!=null) {

                                  try {

                                          rs.close();

                                 } catch (SQLException e) {

                                  }

                         }

                         if(pst!=null) {

                                  try {

                                          pst.close();

                                  } catch (SQLException e) {

                                  }

                         }

                 }

        return null;

    }

   

   

   

}

  1. 给出改造后BookManager类的各个方法的代码。

package cn.edu.zucc.booklib.control;

import cn.edu.zucc.booklib.model.BeanBook;

import cn.edu.zucc.booklib.model.BeanReader;

import cn.edu.zucc.booklib.model.BeanSystemUser;

import cn.edu.zucc.booklib.util.BaseException;

import cn.edu.zucc.booklib.util.BusinessException;

import cn.edu.zucc.booklib.util.DBUtil;

import cn.edu.zucc.booklib.util.DbException;

import cn.edu.zucc.booklib.dao.BookDAO;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.Collection;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import java.util.Set;

public class BookManager {

  private BookDAO dao = new BookDAO();

    public List<BeanBook> searchBook(String keyword, String bookState) throws BaseException {

       

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            return dao.searchBook(conn, keyword, bookState);

        } 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();

                }

        }

     

    }

    public void createBook(BeanBook b) throws BaseException {

        if (b.getBarcode() == null || "".equals(b.getBarcode()) || b.getBarcode().length() > 20) {

            throw new BusinessException("条码必须是1-20个字");

        }

        if (b.getBookname() == null || "".equals(b.getBookname()) || b.getBookname().length() > 50) {

            throw new BusinessException("图书名称必须是1-50个字");

        }

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            String sql = "select * from BeanBook where barcode=?";

            java.sql.PreparedStatement pst = conn.prepareStatement(sql);

            pst.setString(1, b.getBarcode());

            java.sql.ResultSet rs = pst.executeQuery();

            if (rs.next()) throw new BusinessException("条码已经被占用");

            pst.close();

            rs.close();

            dao.createBook(conn, b);

           

        } 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();

                }

        }

    }

    public void modifyBook(BeanBook b) throws BaseException {

        if (b.getBookname() == null || "".equals(b.getBookname()) || b.getBookname().length() > 50) {

            throw new BusinessException("图书名称必须是1-50个字");

        }

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            String sql = "select * from BeanBook where barcode=?";

            java.sql.PreparedStatement pst = conn.prepareStatement(sql);

            pst.setString(1, b.getBarcode());

            java.sql.ResultSet rs = pst.executeQuery();

            if (!rs.next()) throw new BusinessException("图书不存在");

            rs.close();

            pst.close();

            dao.modifyBook(conn, b);

        } 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();

                }

        }

    }

    public BeanBook loadBook(String barcode) throws DbException {

        Connection conn = null;

        try {

            conn = DBUtil.getConnection();

            return dao.loadBook(conn, barcode);

        } 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();

                }

        }

   

    }

   

   

   

}

  1. 在BaseDAO中，增加方法，实现根据主码提取对象的方法load。

【实验结果与分析】

  1. 写出函数代码。

    public BeanBook load(Connection conn,String barcode) {

            java.sql.PreparedStatement pst = null;

            java.sql.ResultSet rs = null;

        try {

            String sql = "select * from beanbook where barcode = ?";

            pst = conn.prepareStatement(sql);

            pst.setString(1, barcode);

            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.setState(rs.getString(5));

                b.setPubName(rs.getString(6));

                return b;

            }

        }catch (SQLException e) {

                         e.printStackTrace();

                         throw new DbException(e);

                 }

                 finally{

                         if(rs!=null) {

                                  try {

                                          rs.close();

                                  } catch (SQLException e) {

                                  }

                         }

                         if(pst!=null) {

                                 try {

                                          pst.close();

                                  } catch (SQLException e) {

                                  }

                         }

                 }

        return null;

    }


http://www.ppmy.cn/news/703676.html

相关文章

实验记录 | 6/29

实验记录 | 6/29

使用scSplitter拆分10X 的数据的时候&#xff0c;出现了问题。 EXITING because of FATAL ERROR in reads input: quality string length is not equal to sequence length ST-K00126:608:HWNLJBBXX:6:2115:23480:3459 ST-K00126:608:HWNLJBBXX:6:1212:4005:42513 2:N:0:GATCTC…
阅读更多...
php code128条码,PHP教程：php实现生成code128条形码的方法详解

php code128条码,PHP教程：php实现生成code128条形码的方法详解

《PHP教程&#xff1a;php实现生成code128条形码的方法详解》要点&#xff1a; 本文介绍了PHP教程&#xff1a;php实现生成code128条形码的方法详解&#xff0c;希望对您有用。如果有疑问&#xff0c;可以联系我们。 本文实例讲述了php实现生成code128条形码的方法.分享给大家供…
阅读更多...
硬件检测信息记录

硬件检测信息记录

硬盘&#xff1a;东芝 THNSNJ128GCSY (固态硬盘 [基础信息] ---------------------------------------------------------------------------------- 名称 东芝 THNSNJ128GCSY (固态硬盘) 容量 128 GB 磁盘已使用 共 1604 …
阅读更多...
Barco无线演示系统受多个漏洞困扰

Barco无线演示系统受多个漏洞困扰

F-Secure的IT安全研究人员在Barco的Clickshare产品&#xff08;一种无线演示系统&#xff09;中发现了一些高危漏洞&#xff0c;可以让攻击者在演示期间窃取到关键信息&#xff0c;甚至还会影响密码等敏感信息。 此外&#xff0c;通过这些漏洞攻击者还可以在目标设备上安装后门…
阅读更多...
盘点6月Sui生态发展，了解Sui的近期成长历程！

盘点6月Sui生态发展，了解Sui的近期成长历程！

自5月Sui主网上线以来&#xff0c;已两月有余&#xff0c;最近一个月Sui网络进行多次迭代更新&#xff0c;生态正在不断稳步发展。为吸引更多的项目或开发者前来构建&#xff0c;Sui基金会推出了黑客松以及多项生态建设活动&#xff0c;进一步助力生态持续发展。 以下是Sui的近…
阅读更多...
MES生产管理系统与ERP系统的集成以及优势

MES生产管理系统与ERP系统的集成以及优势

导言&#xff1a; 在当今数字化转型的浪潮中&#xff0c;企业越来越意识到整合各个部门的数据和流程的重要性。MES生产管理系统和ERP系统是两个关键的管理工具&#xff0c;它们在企业中发挥着不可或缺的作用。本文将探讨企业MES管理系统与ERP系统进行集成&#xff0c;以及这种…
阅读更多...
【linux】一些linux系统下的常用命令（本地使用、远程服务器连接常用）

【linux】一些linux系统下的常用命令（本地使用、远程服务器连接常用）

一些linux常用命令 linux 常用命令打开某个文件夹查看某个目录的所有内容复制文件命令移动命令创建文件夹删除文件或目录当前目录创建文件找文件/文件夹查看目录或文件夹大小更改文件或目录权限查看文件内容之vim查看之前输入的命令行下载某个东西到某个位置解压缩查看CPU占用情…
阅读更多...
棋牌游戏网站分析——远航游戏中心

棋牌游戏网站分析——远航游戏中心

作为行业的分析师&#xff0c;该拿出点东西让市场了解一下环境了&#xff01;今天先拿远航游戏中心开刀讨论一下。 棋牌游戏的成功代表作品包括联众&#xff0c;QQ游戏&#xff0c;边锋等。其中包含游戏典型有升级、梭哈、斗地主、锄大地、四国军棋等。最近一段时间由于棋牌的…
阅读更多...
最新文章

Copyright @ 2022~2023