实验6：JDBC进阶（3）

相关知识点

1. JDBC基本概念
2. 批处理
3. DAO和OR映射

实验目的：

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

实验内容：

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

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

public void lendbooks(String readerId,Collection<String> barcodes) throws BaseException {
    Object[] objects = barcodes.toArray();
    for (int i = 0; i < objects.length; i++) {
        lend((String) objects[i],readerId);
    }
}

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

public void setPenalSum(String readerId,Map<String,Double> penalSums) throws Exception {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DBUtil.getConnection();
        String sql = "update beanbooklendrecord set penalSum = ? where readerid = ? and bookBarcode = ? and returnDate is null";
        ps = conn.prepareStatement(sql);
        ps.setObject(2,readerId);
        for (Map.Entry<String,Double> x : penalSums.entrySet()) {
            String barcode = x.getKey();
            double penal = x.getValue();
            ps.setObject(1,penal);
            ps.setObject(3,barcode);
            ps.execute();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        DBUtil.closeResorce(conn,ps);
    }
}

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

给出BookDAO类代码。

package cn.edu.zucc.booklib.dao;

import cn.edu.zucc.booklib.model.BeanBook;
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.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BookDAO {
    public List<BeanBook> searchBook(String keyword, String bookState) throws BaseException {
        List<BeanBook> result = new ArrayList<BeanBook>();
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from view_book" + " where state='" + bookState + "' ";
            if (keyword != null && !"".equals(keyword))
                sql += " and (bookname like ? or barcode like ?)";
            sql += " order by 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.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 (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
        return result;

    }

    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=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, b.getBarcode());
            ResultSet rs = pst.executeQuery();
            if (rs.next()) throw new BusinessException("条码已经被占用");
            rs.close();
            pst.close();

            sql = "insert into BeanBook(barcode,bookname,pubid,price,state) values(?,?,?,?,'在库')";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setObject(1, b.getBarcode());
            ps.setObject(2, b.getBookname());
            ps.setString(3, b.getPubid());
            ps.setDouble(4, b.getPrice());


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

    }

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

    public BeanBook loadBook(String barcode) throws DbException {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "select * from view_book " + " 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.setState(rs.getString(5));
                b.setPubName(rs.getString(6));
                return 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();
                }
        }
        return null;
    }
}

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

public List<BeanBook> searchBook(String keyword, String bookState) throws BaseException, SQLException {
    Connection conn = null;
    try {
        conn = DBUtil.getConnection();
        return dao.searchBook(keyword, bookState);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (BaseException e) {
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return null;
}

public void createBook(BeanBook b) throws BaseException, SQLException {
    Connection conn = null;
    try {
        conn = DBUtil.getConnection();

        dao.createBook(b);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (BaseException e) {
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public void modifyBook(BeanBook b) throws BaseException, SQLException {
    Connection conn = null;
    try {
        conn = DBUtil.getConnection();

        dao.modifyBook(b);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (BaseException e) {
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

public BeanBook loadBook(String barcode) throws DbException, SQLException {
    Connection conn = null;
    try {
        conn = DBUtil.getConnection();

        dao.loadBook(barcode);
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (DbException e) {
        e.printStackTrace();
    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return null;
}

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

写出函数代码

ublic T load(Connection conn, Class<T> clazz, Map<String,Object> primaryKey) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        //primayKey的key为主码的各个属性名称，value为主码值
        //假设对象的类名和表名一致，属性名和字段名一致

        String tableName = clazz.getSimpleName();

        String sql = "select * from "+tableName+" where"; //动态构建sql
        for (Map.Entry mp : primaryKey.entrySet()) {
            sql += " " + mp.getKey().toString() + " = ? and";
        }
        sql = sql.substring(0,sql.length()-3);

        Object[] parmas = new Object[primaryKey.size()];
        //从map中获取参数值，并写入params
        int pos = 0;
        for (Map.Entry<String, Object> mp : primaryKey.entrySet()) {
            parmas[pos++] = mp.getValue();
        }

        ps = conn.prepareStatement(sql);
        for (int i = 0; i < parmas.length; i++) {
            ps.setObject(i+1,parmas[i]);
        }
        rs = ps.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        if (rs.next()) {
            T t = clazz.getDeclaredConstructor().newInstance();

            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                //获取当前rs指针的i+1字段的值
                Object value = rs.getObject(i+1);

                //获取当前rs指针的i+1字段的列名
                String columnLable = rsmd.getColumnLabel(i+1);

                //将value赋值给对应属性，反射
                Field field = clazz.getDeclaredField(columnLable);
                field.setAccessible(true);
                field.set(t, value);
            }
            return t;
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DBUtil.closeResorce(null,ps,rs);
    }
    return null;
}