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

实验6  JDBC进阶（3）

一、相关知识点

        1、JDBC基本概念

        2、批处理

3、DAO和OR映射

二、实验目的：

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

三、实验内容：

1. 利用批量SQL语句执行的方法实现实验5中的最后两个方法：

1. 编写批量借阅读书函数： public void lendbooks(String readerId,Collection barcodes) .... 。其中第二个参数为图书条码集合。

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

            SystemUserManager sum = new SystemUserManager();

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

            sum.currentUser = admin;

           

            ArrayList 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

            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

                 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 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 penalSums) .... 。其中第二个参数的key为barcode，value为改读者尚未归还图书的罚金（注意，不要设置已经归还图书的罚金）。

                   public void setPenalSum(String readerId,Map penalSums){

              Set> 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 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 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 searchBook(Connection conn,String keyword, String bookState) throws BaseException {

        List result = new ArrayList();

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

    }